On 7 Feb 2012, at 00:55, Grant wrote: > I'm running mysql on Gentoo with 4GB RAM and I'm wondering if I should > change any settings. I'm using mysql with a website on the same > server so I have skip-networking, and I increased key_buffer and > innodb_buffer_pool_size from 16M to 256M. Everything else is default. > Should I consider changing these or any other settings?
Grant, What I usually recommend about database tuning: 1. Visibility. There's no way you can see if your changes are good or bad for performance if you don't have a waterline and visibility. I recommend you to pick your favourite performance graphing tool and ensure we have good graphs available for all the most important key performance indicators that you can think of. The more graphs, the easier it gets to spot bottlenecks and understand root-causes of performance issues. 2. Monogamy Tuning MySQL databases that mix MyISAM and InnoDB storage engines is hard and tricky. If you have the chance, I strongly recommend you choosing one storage engine and sticking to it above everything else. This might sound like a hard choice to make in the beginning but it will pay you back with good dividends later on, both in improved performance and lower resource consumption than the equivalent databases with hybrid storage engine options. 3. Control Before you start making changes to your configuration, I strongly recommend you to version-control it. I used to use and recommend RCS for this, but Git is a more modern, yet flexible and powerful version control to which I've feel in love. Version controlling your changes gives you control over your configuration and helps determining what changes happened in which order and when. This helps you pin-pointing results of your changes on your graphs and correlating what you do with how the server reacts. 4. Logs Make sure you enable all the information you can about your slow-query log, this is an essential part of detecting query and schema related bottlenecks. Here at the office we use Percona Patched MySQL databases, and I usually enable all the Percona-provided extensions to the slow query log. 5. Work Holistically Database Tuning isn't an isolated process. You should start from a "best guess" configuration and work in cycles towards a configuration that satisfies your needs without compromising more resources than you have available. Remember, there's no point in adjusting a couple of database variables without understanding what it means for the application and for the queries / schema. Make sure you include your developers (if you're not one of them) and take into consideration all the information you have at hand. I hope this helps with getting started. Good luck, and kind regards. -- Luis Motta Campos is a DBA, Foodie, and Photographer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
