There are a couple scripts which can assist with tuning an existing database server, such as
a) Tuning-Primer: http://www.day32.com/MySQL/tuning-primer.sh
which handles recomendations for the following:
b) Then, there is also the mysqltuner.pl [perl script] which essentially does the same thing. I used both with some pretty decent results in tweaking an old server.
The above mentioned scripts only work properly on a currently running setup. It does not really help in tuning a new database server, which I'm in the process of building.
Even the sample mysql config files located in the distro is limited, with the largest (my-huge.cnf) only catering to a database server with 1-2GB of memory.
My intention is to tune a dedicated database server with the following base specifications:
CentOS 5.5 64 bit installed on
HP ProLiant DL380 G7 Server
2 Intel Xeon E5620 Quad-core processors
32GB memory
There is a builtin benchmark tool with MySQL, I'd recommend running the benchmark tests before any changes are made, then run and document the results after each configuration change. Normal elevated privlieges apply - login as root.
0. Run the initial benchmark test - then copy paste (or pipe) the result into a textfile for further analysis.
cd /usr/share/sql-bench
perl run-all-tests --user='yourdbusername' --password='yourdbpassword'
Sample output - not mine, sample taken from here:
alter-table: Total time: 8 wallclock secs ( 0.02 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.03 CPU)
ATIS: Total time: 2 wallclock secs ( 1.20 usr 0.09 sys + 0.00 cusr 0.00 csys = 1.29 CPU)
big-tables: Total time: 5 wallclock secs ( 2.45 usr 0.08 sys + 0.00 cusr 0.00 csys = 2.53 CPU)
connect: Total time: 50 wallclock secs (12.74 usr 4.50 sys + 0.00 cusr 0.00 csys = 17.24 CPU)
create: Total time: 31 wallclock secs ( 1.20 usr 0.44 sys + 0.00 cusr 0.00 csys = 1.64 CPU)
insert: Total time: 397 wallclock secs (97.95 usr 13.61 sys + 0.00 cusr 0.00 csys = 111.56 CPU)
select: Total time: 44 wallclock secs ( 8.71 usr 0.88 sys + 0.00 cusr 0.00 csys = 9.59 CPU)
transactions: Test skipped because the database doesn’t support transactions
wisconsin: Total time: 3 wallclock secs ( 0.91 usr 0.23 sys + 0.00 cusr 0.00 csys = 1.14 CPU)
TOTALS 562.00 123.77 19.82 143.59 3425950
I first started off by keeping a backup of the existing /etc/my.cnf ann then using the distro's my-huge.cnf, making that my baseline config.
1. backup existing config, set new baseline and restart mysql service
mv /etc/my.cnf /etc/my.cnf.bkp
cp /usr/share/doc/mysql-server-5.0.77/my-huge.cnf /etc/my.cnf
service mysqld restart
The MySQL documentation suggests the following server disk parameters:
hdparm - which allows configuration of the disk interface.
MySQL suggests the following configuration:
2. Tuning disk parameters (reboot not necessary, but I did anyway)
hdparm -m 16 -d 1
It should be noted that I mounted the /var partition on it's own disk array to avoid disk contention.
Mysql also suggests the following mount options on the DB disks:
noatime and async
nano /etc/fstab
find your /var partition or whatever partition your db resides on, and append noatime and async after the default option:
LABEL=/var /var ext3 defaults,noatime,async 1 2
Note: differnet filesystems have their own pros and cons for use of a database server. Ext3 is the middle ground in terms of performance and stability.
3. Tune additional my.cnf options (requires a restart of mysql service before changes are applied)
MySQL documentation suggests that the two most important parameters to begin tuning are:
key_buffer_size (or key_buffer on newer versions of MySQL)
table_open_cache
The key_buffer_size allows you to store the MyISAM table indexes in memory. The generally accepted rule of thumb is setting this to 25-50% of server memory on a dedicated database server.
nano /etc/my.cnf
find key_buffer_size (or key_buffer)
adjust to suggested value above with the following syntax
key_buffer_size = 16384M
The table_open_cache (or table_cache) is related to the max_connections configuration. Tuning this parameter relies on obtaining information from the running setup and tweaking accordingly.
Tips on tuning this and other parameters can be found here: http://www.databasejournal.com/features/mysql/article.php/3367871/Optimizing-the-mysqld-variables.htm
Remember to run the benchmark after each change - analysis of the results should be fun.
-n
