From: Luis Motta Campos Date: February 8 2012 2:50pm Subject: Re: Tuning mysql List-Archive: http://lists.mysql.com/mysql/226758 Message-Id: MIME-Version: 1.0 (Apple Message framework v1084) Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable 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,=20 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.=20 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.=20 Good luck, and kind regards. -- Luis Motta Campos is a DBA, Foodie, and Photographer