How-To Geek
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:
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 = 1Server 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.
Got Feedback? Join the discussion at discuss.howtogeek.com
Comments (13)
Programmer by day, geek by night, The Geek, also known as Lowell Heddings, spends all his free time bringing you fresh geekery on a daily basis. You can follow him on Google+ if you'd like.
- Published 10/4/07
./tuning-primer.sh: line 1164: bc: command not found
./tuning-primer.sh: line 1197: [: -gt: unary operator expected
./tuning-primer.sh: line 350: [: max_memoryHR: integer expression expected
./tuning-primer.sh: line 356: [: max_memoryHR: integer expression expected
----------- [ chopped by editor for length] —————
TABLE SCANS
./tuning-primer.sh: line 331: bc: command not found
Current read_buffer_size = K
Current table scan ratio = 7 : 1
read_buffer_size seems to be fine
plese help.
bushido:
The problem you are having is that you are missing some of the utilities required by the script. You’ll have to install the missing utilities, such as bc.
Thanks for using my script :-)
If you do see any bugs or recommendations that seem ill advised please let me know. My e-mail address is found inside the script header.
Thanks
Matt
Check also MysqlTunner :
http://rackerhacker.com/mysqltuner/
Antoher good script for auto tunning your mysql server
Is there anything like this for windows users?
I made a slight modification to the cecho function so the output can be optionally directed to a file:
[ -t 1 ] && echo -e “$color”
echo -e “$message”
[ -t 1 ] && tput sgr0 # Reset to normal.
[ -t 1 ] && echo -e “$black”
return
(I also changed the yellow and green settings because I can never read yellow on my xterm window.)
– seb
Anyone have a windows equivalent?
For anyone getting a “unexpected operator” error on startup please try changing the default shell
from:
#!/bin/sh
to:
#!/bin/bash
this fixed the problem for me on my Ubuntu 8.04 box
I don’t think there’s a windows equivalent..
For those with the BC error…
If you’re on an Ubuntu server:
SSH to the server as root and run the command:
aptitude install bc
If you’re on a CentOS server:
SSH to the server as root and run the command:
yum install bc
Then you can run the script afterwards with the command:
./tuning-primer.sh
If you’re using a Windows server, then MySQL performance is the least of your problems.
@Anna
Get your head out of your ass and or the sand, you obviously have little to no real knowledge of windows server environments, but thanks for the useless troll, you are a credit to the linux community.
You have been such a help, I’ll remember recommend you to……well….NO ONE!!
Nowt but a grade A TWAT!!!
Anna is correct, if you can’t run a perl script or compile bc for your OS then you should think about using a server OS for your server. I was building huge Windows servers when you were licking jelly off of your little sticky fingers, so don’t tell me I know nothing about rebooting Windows servers.