[e][e]Tuning MySql For Speed
Slow Query Log. Can get MySql to log all queries that take more than a certain number of seconds. Set log_slow_queries=ON in your my.cnf & restart the server.
the slow query log [mysql.com]
mysqldumpslow --s=t log-file summarizes the log file according to the total server-time used by that query.
Memory Usage. Good estimate of memory usage:
innodb_buffer_pool_size
+ key_buffer_size
+ query_cache_size
+ number_of_open_sessions * (thread_stack_size + net_buffer_length + read_buffer_size + sort_buffer_size)
You can get the worst-case number_of_open_sessions with Max_used_connections or max_connections.
Under Linux each mysql server process seems to report the same value for memory-usage, & that is (I think) the size of total mysql memory usage. It should be approximately equal to the above calculation. Use: ps aux.
Linux memory usage commands.
* top
* ps -A Shows all running processes. Use "-T" to show every process separately.
* free Shows total usage of memory.
* cat /proc/meminfo Full memory-usage info, which "free" is a summary of. (see http://linuxweblog.com/node/232)
* pstree -c Shows all processes and their parentage.
* vmstat 1 to see the incoming & outgoing virtual memory usage. The 'bo' and 'bi' columns show kilobytes going to & from the disk, *excluding* swap. Turn off all processes (like replication), hopefully disk traffic will stop, then try different processes. (esp: try them multiple times, to see how cache is working).
Miscellaneous Notes.
WITH ROLLUP never observed to add more than 20% to query time.
With large group-by queries, a temporary table will be used, and will be written to disk when it comes large than either of tmp_table_size or max_heap_table_size. So increasing these may speed up large queries.
InnoDB Log Flushing.
innodb_flush_method -- By default is O_SYNC. Usually quicker. But on some filesystems, O_DSYNC works better.
innodb_flush_log_at_trx_commit -- By default is 1: flushes log on every commit. If set to 0, it flushes the log once every second, & should be much quicker (also, much less safe from crashes).
Discussion on fiddling with flush variables [lists.mysql.com] - claim 10-times improvement on out-of-box InnoDB performance, almost as quick as MyISAM when adjusted.
Sort MyISAM records
Haven't tried this, but: myisamchk --sort-index --sort-records=1
Resources.
mytop -- a good little process-monitor for MySql by Jeremy Zawodny.
Optimizing MySql[mysql.com] -- a big list of things to try. Old though: talks about 3.23 as the latest version.
Book: High Performance MySql by J Zawodny & D Balling. Pretty good.
Optimizing Mysql/Innodb Performance [mike.kruckenberg.com]
Optimizing MySQL: Hardware and the Mysqld Variables (Dec, 2001) [databasejournal.com]