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