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:
?? 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.
----- Original Message -----
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..
> 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>
> > $rand,1000",
> > "ran 1000 row Select"=>"select * from <table> order by rand() limit
> > "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
> > "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
> > and username='USERNAME' limit 1",
> > "1 row Delete NonIndexed columns"=>"delete from <table> where
> > 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
> > -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
> > > > 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
> > > >
> > > > 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"
> > > "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: