Tuesday, April 5, 2011

Some MySQL database tuning - from a non-DBA perspective.

A DBA I'm not, but that does not mean I have to accept the default configuration options for Mysql.

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:

  • Slow Query Log

  • Max Connections

  • Worker Threads

  • Key Buffer

  • Query Cache

  • Sort Buffer

  • Joins

  • Temp Tables

  • Table (Open & Definition) Cache

  • Table Locking

  • Table Scans (read_buffer)

  • Innodb Status

    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)

    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.