« Will Google Make Me Change How I think about Myself? | Main | Pixies Concert on DVD from Fellow OSCON Attendee »

July 27, 2004

MySQL Performance Workshop

Jeremy Zawodny is doing a tutorial about tuning MySQL at OSCON 2004. Kind of a cool approach to the tutorial, Jeremy puts up a list of Topics and is asking for audience input on how to proceed. I saw got quite a bit of information on tuning at MySQL 2004, but I'm still convinced there's a part of our system somewhere that needs tweaked. I haven't noticed any performance issues on our installation, but want to be educated about how to solve them should they arise.

Jeremy starts with a slew of questions to get familiar with the audience and then gets into the "how MySQL is used at Yahoo." 500-1000 installations, mostly run on FreeBSD 4.x on 32bit Intel (commodity hardware), anywhere from 2-30G of data. Lots of replication, load balancing. Alteon and Foundry load balancers used. Leaned in Alteon direction because you could have it execute a script which requested a custom php page from the database server which would run some checks and return a more sensible health check compared to a TCP/IP check on port 3306.

What things might affect performance

Things that don't matter

Schemas, Indexes, Table Type

Review of storage engines and features (MyISAM, HEAP, BDB, InnoDB, NDB, Archive). Archive table type is a new type in 4.1.

Queries

Memory

Disks and I/O

Application Design Issues

Query Optimization

What tools does Jeremy use to find problems?

First thing is to determine where the bottleneck is. Start with using vmstat, sar, top, taskmanager to find CPU, memory, disk etc. Sometimes getting I/O can be difficult, iostat is a good start. Once beyond the OS level uses mytop to dig into MySQL.

Replication

Is hard to imagine replication being a burden on the master, reading the binlog is easy because it's typically in memory, so the only real threat for replication is network bottleneck. Important to realize that the slaves have to execute the same queries as the master.

Posted by mike at July 27, 2004 2:48 PM