List:General Discussion« Previous MessageNext Message »
From:Luis Motta Campos Date:February 8 2012 2:50pm
Subject:Re: Tuning mysql
View as plain text  
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

Thread
Tuning mysqlGrant6 Feb
  • Re: Tuning mysqlReindl Harald6 Feb
  • Re: Tuning mysqlLuis Motta Campos8 Feb
  • Re: Tuning mysqlGiovanni Bechis9 Feb
    • Re: Tuning mysqlMichael Dykman9 Feb