codemash pt 1

January 18, 2007

I’ll detail how I got here later, but I’m now listening to Jay Pipes talk about mysql benchmarking and optimization techniques. Initial tools talked about include

sysbench - sysbench.sf.net
mysqlslap - throws in randomized data
apache bench
supersmack - www.vegan.net/tony/supersmack
mybench - jeremy.zawaodny.com/mysql/mybench

profiling

diagnose a running system
identify performance bottlenecks - memory/cpu/disk/network/os

show processlist / status / innodb status (http://dev.mysql.com/show)

NOTE: when ‘using index’ is in ‘extra’, means there’s a covering index (all columns needed in the query are in the index).

USE smallest index column width - BIGINT v INT - twice as much information taken up in BIGINT, meaning half as many can fit in disk reads and indexed memory blocks.

MySQL has to lock table on key inserts unless it’s an autoincrement. If using (example) SSN as a key, table has to be locked for inserts - autoincrement it doesn’t. If there’s a natural key, put a UNIQUE index on it and still use an autoincrement key.

homegrown vertical partitioning - godaddy used first 3 chars of account name to then decide which table to query for info. ???

To read up on - multiple myisam key caches

select count(*) from innodb - why is it not accurate? - is accurate when hitting an index, but not on ‘full table scan’ (no index used in select count(*)). Use a ‘counter table’. Basically, manually create a HEAP (MEMORY) table with just a count of items from a table.

init_file - give a path to a file to run on server startup (neat)

KEEP IN MIND - indexing strategy that was initially set up may not apply later in the project lifetime.