Random MySQL performance tuning stuff

1. If you’re using InnoDB, ensure innodb_buffer_pool_size is set to a decent value – I choose about 25% of physical memory… ideally this is larger than your dataset size, but obviously may not be posible, and the server may have to do other stuff….
2. If you’re using InnoDB stop the O/S from also trying to cache ‘stuff’ in the buffer cache – using innodb_flush_method=O_DIRECT
3. Download the MySQL Tuner perl script (wget -O http://mysqltuner.pl > mysqltuner.pl) and run (e.g. perl mysqltuner.pl –user root –pass blahblah; it might point out a few variables to change; node the ‘maximum possible memory usage – you don’t want this to exceed 50% for a normal LAMP server).
4. I use something like the below to optimize all tables, beware this will cause MyISAM tables to lock up … so you really need to run it in a quiet period.

SQL="select concat(TABLE_SCHEMA, '.', TABLE_NAME) from
    information_schema.TABLES where TABLE_SCHEMA IN
    ('database1','database2', 'databaseN') and Data_free > 500
    AND Engine = 'MyISAM' "

for table in $(mysql --skip-column-names  --batch -u root -prahrah -e "$SQL")
do
    mysql --batch -u root -pxxxxx -e "optimize table $table"
    sleep 10
done

See also PHP Conference InnoDB talk and MySQL Tuner