List:General Discussion« Previous MessageNext Message »
From:Adam Nelson Date:April 25 2003 2:16pm
Subject:RE: performance
View as plain text  
Also,

I didn't see what the indexes are, but be very wary of indexing columns
that are in an update.  You may be speeding up the select queries at the
expense of the updates.  Be rigorous about trying to index only columns
that are in the where clauses of select list and look at each case where
there is an update on a column that is also in the where clause of a
select statement.  When you see what these columns are, you may see that
you can break some data into a separate table.  You can then keep the
data in the old table (just until things are working), but optimize the
new table for SELECT or UPDATE whichever the case may be.  Denormalizing
and having redundant data tables can be very beneficial to performance.

Secondly,

I don't know where FreeBSD is now, but I was not finding the performance
on FreeBSD and MySQL that I had hoped for (this was freebsd 4.3).
Moving to Linux worked really well.  I'm not trying to say one is better
than the other, but I think the MySQL developers/users are spending more
time on Linux and consequently, more effort is put into improvements
specifically on Linux.

Lastly,

The 15k SCSI drives are very fast and worth the bucks in this case

> -----Original Message-----
> From: colbey@stripped [mailto:colbey@stripped] 
> Sent: Thursday, April 24, 2003 7:47 PM
> To: mysql@stripped
> Cc: Ryan R. Tharp
> Subject: Re: performance
> 
> 
> 
> You have a high amount of updates.. depending on what those 
> are updating..
> you might want to consider moving to a small load balanced 
> environment..
> setup this 1 box as the master, 2+ slaves off it..
> 
> Slightly modify your code with a database wrapper that routes all
> inserts/delete/updates to the master, and all selects 
> randomly to any of
> the 3 servers).. (optionally hardware load balancer)
> 
> I've only done this once with such a high amount of updates..  and the
> updates were mostly generated by database session information which we
> configued to a dedicated database that handled just that
> to avoid massive replication of the "master" database of 
> relatively less
> important data to slaves..
> 
> It all depends on your app.. what it does, etc..
> 
> 
> On Thu, 24 Apr 2003, Ryan R. Tharp wrote:
> 
> > Good idea, only a few selected columns are closer to "real" 
> queries, I'll
> > trying adding some few columns queries to the benchmark.
> >
> > Our production server is:
> > 44% select
> > 4% insert
> > .02% delete
> > 24% update
> > ?? other (gathered from phpmyadmin 2.4's runtime info/status tab)
> > but that should give you enough to see the ratio between each query.
> >
> > Our FreeBSD is not our production server just a development 
> server we're
> > making plans with.
> >
> > -Ryan.
> >
> > ----- Original Message -----
> > From: <colbey@stripped>
> > To: <mysql@stripped>
> > Cc: "Ryan R. Tharp" <mysql@stripped>
> > Sent: Thursday, April 24, 2003 4:29 PM
> > Subject: Re: performance
> >
> >
> > >
> > > "select * from <table> limit 1000"
> > >
> > > Are you sure you need all the column data? Also if the webserver
> > > calling this data isn't local, you've got to push it over 
> the network..
> > might want
> > > to take a lookse at that..  Could turn out to be a large 
> amount of data
> > > depending on actual row size...
> > >
> > > Any chance you could estimate a query load percentages?
> > >
> > > amount select: %
> > > amount update: %
> > > amount delete: %
> > > amount insert: %
> > >
> > > I've got a php script that does this.. you just feed it a 
> mysql text query
> > > log if you need one...
> > >
> > >
> > > On Thu, 24 Apr 2003, Ryan R. Tharp wrote:
> > >
> > > > IO bound I believe, I've seen the controller hit 74MBps 
> (averages around
> > > > 30MBps) where the load average is always under 1.
> > > >
> > > > Here the queries from the quick benchmark test I put together:
> > > >
> > > > "seq 1000 row Select"=>"select * from <table> limit 1000",
> > > > "seq 1000 row Select starting at random point"=>"select 
> * from <table>
> > limit
> > > > $rand,1000",
> > > > "ran 1000 row Select"=>"select * from <table> order by 
> rand() limit
> > 1000",
> > > > "seq 1 row Select"=>"select * from <table> limit 1",
> > > > "starting at ran 1 row Select"=>"select * from <table> 
> limit $rand,1",
> > > > "ran 1 row Select"=>"select * from <table> order by 
> rand() limit 1",
> > > > "1 row Insert Indexed columns"=>"insert into <table>
> > > > (encCC,username,hitreferrerid) values
> > > > ('deleteme','deleteme',".rand(1,100).")",
> > > > "1 row Insert NonIndexed columns"=>"insert into <table>
> > > > (city,state,intoprice) values 
> ('deleteme','deleteme',".rand(1,100).")",
> > > > "1 row Update Indexed columns"=>"update <table> set 
> encCC='DELETME',
> > > > username='USERNAME', hitreferrerid=".rand(1,100)." where
> > encCC='deleteme'",
> > > > "1 row Update NonIndexed columns"=>"update <table> set 
> city='DELETME',
> > > > state='STATE', intoprice=".rand(1,100)." where 
> city='deleteme' and
> > > > state='deleteme'",
> > > > "1 row Delete Indexed columns"=>"delete from <table> where
> > encCC='DELETME'
> > > > and username='USERNAME' limit 1",
> > > > "1 row Delete NonIndexed columns"=>"delete from <table>
> where
> > city='DELETME'
> > > > and state='USERNAME' limit 1"
> > > >
> > > > On the tables that have indexes, we have 9 single 
> column indexes, and 2
> > > > multicolumn indexes (one is comprised of 3 fields, the 
> other is 2
> > fields).
> > > >
> > > > -Ryan.
> > > >
> > > > ----- Original Message -----
> > > > From: "Dan Nelson" <dnelson@stripped>
> > > > To: "Ryan R. Tharp" <mysql@stripped>
> > > > Cc: <mysql@stripped>
> > > > Sent: Thursday, April 24, 2003 4:14 PM
> > > > Subject: Re: performance
> > > >
> > > >
> > > > > In the last episode (Apr 24), Ryan R. Tharp said:
> > > > > > Running MySQL 4.0.12 on a dual Xeon 2.8ghz with 2gb 
> memory running
> > > > > > FreeBSD 4.8 on a Hardware RAID 0+1 SCSI HDDs
> > > > > >
> > > > > > Have this table that I'm trying to optimize for. It 
> has 1.1million
> > > > > > rows with 29 fields averaging 506 Bytes per row. 
> We've considered
> > > > > > splitting the table but we don't have enough developer time
> > available
> > > > > > to convert all the code that uses the table. So 
> we'd like just to
> > > > > > throw hardware/memory at it for the time being.
> > > > > >
> > > > > > Some software tricks I've tried are playing with:
> > > > > > Dynamic sized rows and static sized rows
> > > > > > InnoDB and MyISAM
> > > > > > Indexes and No Indexes
> > > > > > Primary Keys and No Primary Key (Just an Index for the
> > auto-increment)
> > > > > >
> > > > > > I wrote a benchmark that tests most of these 
> scenarios and then I
> > > > > > began to adjust the various mysql buffers but to my 
> amazement the
> > > > > > benchmarks ran best when I didn't set any buffers 
> in the my.cnf at
> > > > > > all.
> > > > > >
> > > > > > Also the fastest benchmark numbers aren't that 
> fast. Is this table
> > > > > > too big? Any other optimization tips to try? Any 
> one have any ideas
> > > > > > or suggestions?
> > > > >
> > > > > We need more data.  Are you currently CPU bound?  I/O 
> bound?  "top"
> > and
> > > > > "systat -v"  will tell you that.  Are you doing many 
> small queries, or
> > > > > mainly complicated selects?  Lots of updates, or lots 
> of selects?  How
> > > > > many indexes, and are your queries using them?
> > > > >
> > > > > --
> > > > > Dan Nelson
> > > > > dnelson@stripped
> > > > >
> > > >
> > > >
> > > >
> > > > --
> > > > MySQL General Mailing List
> > > > For list archives: http://lists.mysql.com/mysql
> > > > To unsubscribe:
> > http://lists.mysql.com/mysql?unsub=1
> > > >
> > >
> >
> >
> 



Thread
performanceRyan R. Tharp25 Apr
  • Re: performanceDan Nelson25 Apr
  • Re: performanceRyan R. Tharp25 Apr
    • Re: performancecolbey25 Apr
    • Re: performanceDan Nelson25 Apr
  • Re: performancecolbey25 Apr
  • Re: performanceRyan R. Tharp25 Apr
  • Re: performanceRyan R. Tharp25 Apr
    • Re: performancecolbey25 Apr
      • RE: performanceAdam Nelson25 Apr
        • Re: performanceJeremy Zawodny15 May
  • Re: performanceJonathan Disher25 Apr
  • Re: performanceRyan R. Tharp25 Apr
    • Re: performancecolbey25 Apr
    • Re: performanceChristian Jaeger28 Apr
  • Re: performanceRyan R. Tharp25 Apr
    • RE: performanceAdam Nelson28 Apr