MySQL optimization post 1

Well recently after moving MySQL to a remote server – we noticed it simply just locked up.

After peeking I noticed someone changed the my.cnf file around -and thus this is my own quite little slap on the head to that person

The MySQL server has a ton of variables – and depending on how they are adjusted MySQL will work well – or …. just stop all together.

One of the largest issues w/ a bad my.cnf configuration will be seen in I/O workload.  If you are running, like we are, InnoDB and MyISAM (as well as I guess I should include Berkeley DB (DBD) and also HEAP for that matter) – there are some variables that will impact memory and I/O.

for InnoDB – these generally do not matter as much:

·         bulk_insert_buffer_size

·         key_buffer_size

·         key_cache_age_threshold, key_cache_block_size, key_cache_division_limit

·         read_buffer_size, read_rnd_buffer_size

however – for the case of this writeup – I will list the many that apply to virturally all storage engines.

·         join_buffer_size – A buffer used for full join. If you have  large joins without indexes, increase this buffer size to improve the efficiency.

·         sort_buffer_size – A buffer used for the sort result set allocated by each thread. This can speed up GROUP BY as well as ORDER BY queries.

·         query_cache_size – Simply set this variable to a nonzero value to enable query caching and change as needed.

·         query_cache_limit – The maximum size of the cached result set.  Keep in mind – the larger result set won’t be cached .

·         query_cache_min_res_unit –This allocates query cache memory blocks with the minimum size set by this variable.  Sadly – when the application has a lot of small query results – the default block size of 4K can lead to memory fragmentation…  So – with small result sets simply decreasing it to 1024 or 2048 bytes may improve performance.   Conversely – with large query result sets increasing it to 8192, 16384 or more may improve performance.

More to follow later

Looking into testing mysql on a SDD drive soon enough