MySQL - Performance Misc

There is a limit however for particular key_buffer variable which should not be over 4GB. There are some code fixes needed to make it work. See http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/

Consider multiple key buffers to get around the 4GB limit.

I do not generally like using MyISAM in cases when more than 4GB of buffer may be required. Tables with such large indexes may take a while to recover. See http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/

sort_buffer_size

You would normally look at sort_merge_passes status variable. If this variable is increased sort_buffer was not enough to accommodate all data to perform the sort and file based sort was used. Note you do not always have to increase global sort_buffer value. In many cases setting per connection variable before running long queries would be good enough.

If we run lots of complex queries using just a few tables, it does NOT make sense to have a large table cache. We are likely better off increasing the key buffer size. On the other hand, if we run simple queries from many different tables, a large table cache will be much more valuable.

Keep in mind that increasing the value of server parameters increase system resource consumption by the server. We should not increase parameter values beyond what is available, and we should not allocate so much memory to MySQL that the operating system suffers in its own performance.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License