Some notes from Peter's MySQL/InnoDB Performance talk

Some notes from Peter's MySQL/InnoDB Performance talk at the 2004 MySQL User's Conference...

DBT2 Benchmark, http://sf.net/projects/osdldbt.

He uses 2.4.21 kernel, no swap, and the ext3 filesystem. Runs benchmark, checks status output (SHOW STATUS or using mysqladmin), then begins tuning. First thing to do is enable the slow query log. Find queries in slow query log, run EXPLAIN on the slow queries, converting non-SELECTs to SEELCTs when needed. Index tweaking often follows. Check SHOW PROCESSLIST for slow queries too (I'd recommend using mytop for that, of course).

Adding many indexes is best done with ALTER TABLE rather than several ADD INDEX statements.

SHOW INNODB STATUS will give some insight into your disk I/O performance and then for tweaking the size of InnoDB's buffer pool. Then we turn to looking at the size of InnoDB's transaction log file. Increasing it will reduce the writes in many cases.

Audience question about why there are writes at all. Answer: the benchmark (TPC-like) simulates order processing, so there are writes happening. It's just that the test is supposed to have more reads than writes.

Next we're looking at Opened_tables to make sure the table_cache is large enough.

Set InnoDB's thread concurrency to (num_disks + num_cpus) * 2. Also look at increasing the size of the log buffer.

Now we're back to more index tuning--replacing an older primary key with a new one having re-ordered the columns in the index. Updating primary keys is bad because of record shuffling during those updates. And keep those PKs small!

Damn. That's it for now. I have to leave early for a meeting, so can't take notes for the full session.