mysql server optimization

[bad_brain]

alrighty, I thought it might be handy for you server admins out there (or users simply interested in servers) to give some hints about database server optimization, works for mysql/mariadb.

let's first have a look at the variables available, there are a LOT, but don't worry, there are just a couple that you have to focus on.
to display the variables (and a bunch of statistics):

Code: Select all

###enter mysql console###
mysql
###then###
mysql> show status;

now there are 2 ways you can go for analyzing and optimizing. you can either spend a lot of time by comparing the returned status values with your settings in /etc/mysql/my.cnf, for example:

Code: Select all

###taken from status report###
| Max_used_connections           | 30     |
###compare to value set in my.cnf###
max_connections        = 50
###looking good! never went close to the limit###

or you can use scripts that do the work for you.
I can highly recommend the "MySQL tuning primer" available at https://launchpad.net/mysql-tuning-primer" onclick="window.open(this.href);return false;
notice that you will need to have bc installed (an algebraic package needed to do the math stuff). I stumbled into a little problem when using it (the script) for mariadb though, it fails at analyzing the JOIN statistics which ends the script execution for me.
the displayed info is still valuable, just not complete, so if you are using mariadb I recommend using the "mysqltuner" Perl script available at https://raw.github.com/major/MySQLTuner ... qltuner.pl" onclick="window.open(this.href);return false; together with the tuning-primer script.
the displayed info is not as extensive as the tuning-primer one, but it's a good addition.

both scripts will make suggestions about what values could/should be optimized in my.cnf.
notice that your database service needs to run for some days before the analysis can return reliable results, so always check after a couple of days (or maybe once a week), adjust settings if needed, and then do it again. after a while you will have found the values which work best for your system in context with performance and available resources (RAM).

alrighty, now you have a well configured database service running, but you can (and should) optimize the databases itself too. this procedure is a must after a database crash (through a bad had reboot by power outage for example), but I recommend to do it every now and then to milk some more performance out of it. sounds complicated, but it actually isn't, as server admin you should stay away from messing around with user databases directly, instead you can optimize the tables on server-level.
with this command you can repair and optimize all tables of all databases on the server at once:

Code: Select all

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

make sure to add your mysql/mariadb password after the -p switch.
this method is guaranteed not to cause any damage (nothing worse than customer mails flying in about the site being broken because you meant it TOO good with actually doing something that is supposed to help, right?), and you still can get some performance out of it.

enjoy...