List:General Discussion« Previous MessageNext Message »
From:colbey Date:April 25 2003 12:23am
Subject:Re: performance
View as plain text  
Hrm.. just a quick thought.. Have you looked at HEAP table type?
http://www.mysql.com/doc/en/HEAP.html

Just a thought.. power up the database, create and load the heap table
up.. and run some tests on it?  Just make sure your power doesn't go out?

Perhaps 2 tables:

table_myisam
table_heap

All queries from webservers go against table_heap...  inserts, updates and
deletes go against both?



On Thu, 24 Apr 2003, Ryan R. Tharp wrote:

> We're actually already doing that on the production server. That's why
> there's a lot of updates. Though we do still have a lot of scripts reading
> from our master copy. So we're at a point where the master is slammed. I
> guess I can hold out for the next version of replication (drool =O~~)
>
> I was just hoping there was something with the buffers. I was trying to get
> more of the db into memory so it would be faster.
>
> -Ryan.
>
> ----- Original Message -----
> From: <colbey@stripped>
> To: <mysql@stripped>
> Cc: "Ryan R. Tharp" <mysql@stripped>
> Sent: Thursday, April 24, 2003 4:47 PM
> 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