BrnDmp

MySQL performance tuning

Some mySQL performance and tuning tips from linux format (LXF89).

>> max_connections: number should be the same as Apache child processes

>> query_cache_size: amount of RAM used to store results of queries

>> table_cache: check open tables; they should all fit in cache

>> key_buffer_size: try to put all your keys in this buffer (I think that’s not the problem with Moodle)

>> read_buffer_size: used for speeding up reading tables with difficult searches; allocated per connection. usually 128K but if enough RAM you can push it to 1M

>> read_rnd_buffer_size: used when randomly reading table; usually 4x read_buffer_size

To check whether you allocated enough RAM to key_buffers you can calculate the cache hit rate:

Cache hit rate = 100 – ((key_reads/key_read_requests)*100)

Compare:

Hit Rate Comment

0-80% Awful! buy more RAM, or increase the buffer size massively

81-95% Fair; you could achieve big performance boosts by increasing the buffer size

96-98%  Good; if site is busy, this might be as good as it gets

99%+      Great!