Using a MySQL Performance Tuning Analyzer Script

When you are working on increasing the speed of your website, a very important piece is making sure you get every last drop of performance out of your database server. Unfortunately, for most of us that aren’t normally database administrators this can be a difficult proposition.

There’s a number of performance tuning scripts that will analyze your server settings and current status and give you information on recommended changes that you should make. You shouldn’t necessarily follow all of the suggestions, but it’s worthwhile to take a look at anyway.

The script I’ve been using gives you recommendations 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
  • Once you download the script, you’ll need to make it executable with the following command:

    chmod u+x tuning-primer.sh

    If you run this script as a regular user, it will prompt you for your password, so you’ll have to make sure to set access accordingly. If you run it as root it’ll pick up the mysql password from Plesk if you have that installed.

    I’ve cut out a lot of the output, which had a lot more recommendations, but was just too long to fit on the page.

    ./tuning-primer.sh

            — MYSQL PERFORMANCE TUNING PRIMER —
                 – By: Matthew Montgomery –

    MySQL Version 4.1.20 i686

    Uptime = 5 days 10 hrs 46 min 5 sec
    Avg. qps = 4
    Total Questions = 2020809
    Threads Connected = 1

    Server has been running for over 48hrs.
    It should be safe to follow these recommendations

    ———– snipped ————–

    QUERY CACHE
    Query cache is enabled
    Current query_cache_size = 8 M
    Current query_cache_used = 7 M
    Current query_cach_limit = 1 M
    Current Query cache fill ratio = 89.38 %
    However, 254246 queries have been removed from the query cache due to lack of memory
    Perhaps you should raise query_cache_size
    MySQL won’t cache query results that are larger than query_cache_limit in size

    ———– snipped ————–

    Looks like I need to increase my query cache… I set it to only 8MB but it’s cleaning out the cache far too often.

    ———– snipped ————–

    TEMP TABLES
    Current max_heap_table_size = 16 M
    Current tmp_table_size = 32 M
    Of 35170 temp tables, 74% were created on disk
    Effective in-memory tmp_table_size is limited to max_heap_table_size.
    Perhaps you should increase your tmp_table_size and/or max_heap_table_size
    to reduce the number of disk-based temporary tables
    Note! BLOB and TEXT columns are not allow in memory tables.
    If you are using these columns raising these values might not impact your
    ratio of on disk temp tables.

    ———– snipped ————–

    This type of information is just invaluable when you are trying to tune the performance of your website.

    Download MySQL Performance Tuning Primer Script

    Lowell Heddings, better known online as the How-To Geek, spends all his free time bringing you fresh geekery on a daily basis. You can follow him on if you'd like.