How to tune MySQL Server to increase MySQL
performance using mysqltuner.pl and Tuning-primer.sh
Improving MySQL performance is crucial for improving a website
responce times, reduce server load and improve overall work
efficiency of a mysql database server.
I've seen however many Linux System administrators who does
belittle or completely miss the significance of tuning a newly
installed MySQL server installation.
The reason behind that is probably caused by fact that many people
think MySQL config variables, would not significantly improve
performance and does not pay back for optimization efforts.
Moreover there are a bunch of system admins who has to take care
for numerous services so they don't have time to get good knowledge
to optimize MySQL servers.
Thus many admins and webmasters nowdays, think optimizations depend
mostly on the side of the website programmers.
It's also sometimes falsely believed that optimizing a MySQL server
could reduce the overall server stability.
With the boom of Internet website building and internet marketing,
many webmasters emerged and almost anybody with almost no knowledge
on GNU/Linux or minimal or no knowledge on PHP can start his Online
store, open a blog or create a website powered by some CMS like
joomla.
Thus nowdays many servers even doesn't have a hired system
administrators but are managed by people whose knowledge on *Nix is
almost next to zero, this is another reason why dozens of MySQL
installations online are a default ones and are not taking a good
advantage of the server hardware.
The incrase of website visitors leads people servers expectations
for hardware also to grow, thus many companies simply buy a new
hardware instead of taking the few time to investigate on how
current server hardware can be utilized better.
In that manner of thought I though it will be a good idea to write
this small article on Tuning mysql servers with two scripts
Tuning-primer.sh and
mysqltuner.pl.
The scripts are ultra easy to use and does not require only a
minimal knowledge on MySQL, Linux or (*BSD *nix if sql is running
on BSD).
Tuning-primer.sh
and
mysqltuner.pl are
therefore suitable for a quick MySQL server optimizations to even
people who are no computer experts.
I use this two scripts for MySQL server optimizations on almost
every new configured GNU/Linux with a MySQL backend.
Use of the script comes to simply download with
wget, lynx,
curl or some other web client and execute it on the server host
which is already running the MySQL server.
Here is an example of how simple it is to run the scripts to
Optimize MySQL:
debian:~# perl mysqltuner.pl
>> MySQLTuner 1.2.0 - Major Hayden
>major@mhtx.net<
>> Bug reports, feature requests, and downloads at
http://mysqltuner.com/
>> Run with '--help' for additional options and output
filtering
-------- General Statistics
--------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.49-3
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics
-------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM
-NDBCluster
[--] Data in MyISAM tables: 6G (Tables: 952)
[!!] InnoDB is enabled but isn't being used
[!!] Total fragmented tables: 12
-------- Security Recommendations
-------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics
-------------------------------------------------
[--] Up for: 1d 2h 3m 35s (68M q [732.193 qps], 610K conn, TX: 49B,
RX: 11B)
[--] Reads / Writes: 76% / 24%
[--] Total buffers: 512.0M global + 2.8M per thread (2000 max
threads)
[OK] Maximum possible memory usage: 6.0G (25% of installed
RAM)
[OK] Slow queries: 0% (3K/68M)
[OK] Highest usage of available connections: 7% (159/2000)
[OK] Key buffer size / total MyISAM indexes: 230.0M/1.7G
[OK] Key buffer hit rate: 97.8% (11B cached / 257M reads)
[OK] Query cache efficiency: 76.6% (46M cached / 61M selects)
[!!] Query cache prunes per day: 1822075
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 2M
sorts)
[!!] Joins performed without indexes: 63635
[OK] Temporary tables created on disk: 1% (26K on disk / 2M
total)
[OK] Thread cache hit rate: 99% (159 created / 610K
connections)
[!!] Table cache hit rate: 4% (1K open / 43K opened)
[OK] Open file limit used: 17% (2K/16K)
[OK] Table locks acquired immediately: 99% (36M immediate / 36M
locks)
-------- Recommendations
-----------------------------------------------------
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
Run OPTIMIZE TABLE to defragment tables for better
performance
Enable the slow query log to troubleshoot bad queries
Increasing the query_cache size over 128M may reduce
performance
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor
limits
Variables to adjust:
query_cache_size (> 256M) [see warning above] join_buffer_size
(> 256.0K, or always use indexes with joins) table_cache (>
7200)
You see there are plenty of things, the script reports, for the
unexperienced most of the information can be happily skipped
without need to know the cryptic output, the section of importance
here is
Recommendations for some clarity, I've made this
section to show up bold.
The most imporant things from the
Recommendations script
output is actually the lines who give suggestions for incrase of
certain variables for MySQL.In this example case this are the last
three variables:
query_cache_size,
join_buffer_size and
table_cache
All of these variables are tuned from
/etc/mysql/my.cnf (on
Debian) and derivatives distros and from
/etc/my.cnf on
RHEL, CentOS, Fedora and the other RPM based Linux
distributions.
On some custom server installs my.cnf is also located in
/usr/local/mysql/etc/ or some other a bit more unstandard
location ;)
Anyways now having the
Recommendation from the script, it's
necessery to edit
my.cnf and try to double the values for
the suggested variables.
First, I check if all the suggested variables are existent in my
config with
grep , if they're not then I'll simply add the
variable with doubled size of the suggested values.
P.S: One note here is sometimes some values which are configured,
are the default value for the MySQL server and does not have a
record in
my.cnf
debian:~# grep -E
'query_cache_size|join_buffer_size|table_cache' /etc/mysql/my.cnf
table_cache = 7200
query_cache_size = 256M
join_buffer_size = 262144
All of my variables are in the config so, now edit my.cnf and set
values to:
table_cache = 14400
query_cache_size = 512M
join_buffer_size = 524288
I always, however preserve the old variable's value, because
sometimes raising the value might create problem and the MySql
server might be unable to restart properly.
Thus before going with adding the new values make sure the old ones
are commented with
# , e.g.:
#table_cache = 7200
#query_cache_size = 256M
#join_buffer_size = 262144
I would recommend
vim as editor of choice while editing
my.cnf as vim completely rox
If you're not
acquainted to vim use nano or mcedit or your editor of choice
:
debian:~# vim /etc/mysql/my.cnf
...
Assuming that the changes are made, it's time to restart MySQL to
make sure the new values are read by the SQL server.
debian:~# /etc/init.d/mysql restart
* Stopping MySQL database server mysqld [ OK ]
* Starting MySQL database server mysqld [ OK ]
Checking for tables which need an upgrade, are corrupt or were not
closed cleanly.
If mysql server fails, however to restart, make sure immediately
you reverse back the changed variables to the commented values and
restart once again via mysql init script to make server load.
Afterwards start adding the values one by one until find out which
one is causing the mysqld to fail.
Now the second script
(Tuning-primer.sh) is also really nice
for MySQL performance optimizations are necessery. However it's
less portable (as it's written in bash scripting language).
Consider running this script among different GNU/Linux
distributious (especially the newer ones) might produce
errors.
Tuning-primer.sh requires some minor code changes to be able
to run on FreeBSD, NetBSD and OpenBSD *nices.
The way
Tuning-primer.sh works is precisely like
mysqltuner.pl , one runs it it gives some info about current
running MySQL server and based on certain factors gives suggestions
on how increasing or decresing certain
my.cnf variables
could reduce sql query bottlenecks, solve table locking issues as
well as generally improve
INSERT, UPDATE query times.
Here is an example output from
tuning-primer.sh run on
another server:
server:~# wget
http://pc-freak.net/files/Tuning-primer.sh
...
server:~# sh Tuning-primer.sh
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -
MySQL Version 5.0.51a-24+lenny5 x86_64
Uptime = 8 days 10 hrs 19 min 8 sec
Avg. qps = 179
Total Questions = 130851322
Threads Connected = 1
Server has been running for over 48hrs.
It should be safe to follow these recommendations
To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
SLOW QUERIES
Current long_query_time = 1 sec.
You have 16498 out of 130851322 that take longer than 1 sec. to
complete
The slow query log is NOT enabled.
Your long_query_time seems to be fine
MAX CONNECTIONS
Current max_connections = 2000
Current threads_connected = 1
Historic max_used_connections = 85
The number of used connections is 4% of the configured
maximum.
Your max_connections variable seems to be fine.
WORKER THREADS
Current thread_cache_size = 128
Current threads_cached = 84
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine
MEMORY USAGE
Tuning-primer.sh: line 994: let: expression expected
Max Memory Ever Allocated : 741 M
Configured Max Memory Limit : 5049 M
Total System Memory : 23640 M
KEY BUFFER
Current MyISAM index space = 1646 M
Current key_buffer_size = 476 M
Key cache miss rate is 1 / 56
Key buffer fill ratio = 90.00 %
You could increase key_buffer_size
It is safe to raise this up to 1/4 of total system memory;
assuming this is a dedicated database server.
QUERY CACHE
Query cache is enabled
Current query_cache_size = 64 M
Current query_cache_used = 38 M
Current Query cache fill ratio = 59.90 %
SORT OPERATIONS
Current sort_buffer_size = 2 M
Current record/read_rnd_buffer_size = 256.00 K
Sort buffer seems to be fine
JOINS
Current join_buffer_size = 128.00 K
You have had 111560 queries where a join could not use an index
properly
You have had 91 joins without keys that check for key usage after
each row
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase
your
join_buffer_size to accommodate larger joins in one pass.
TABLE CACHE
Current table_cache value = 3600 tables
You have a total of 798 tables
You have 1904 open tables.
The table_cache value seems to be fine
TEMP TABLES
Current tmp_table_size = 128 M
1% of tmp tables created were disk based
Created disk tmp tables ratio seems fine
TABLE SCANS
Current read_buffer_size = 128.00 K
Current table scan ratio = 797 : 1
read_buffer_size seems to be fine
TABLE LOCKING
Current Lock Wait ratio = 1 : 1782
You may benefit from selective use of InnoDB.
As seen from script output, there are certain variables which might
be increased a bit for better SQL performance, one such variable as
suggested is
key_buffer_size-
(You could increase
key_buffer_size)
Now the steps to make the tunings to my.cnf are precisely the same
as with
mysqltuner.pl, e.g.:
1. Preserve old config variables which will be changed by
commenting them
2. Double value of current variables in my.cnf suggested by
script
3. Restart Mysql server via
/etc/init.d/mysql restart
cmd.
4. If mysql runs fine monitor mysql performance with
mtop or
mytop for at least 15 mins / half an hour.
if all is fine run once again the tuning scripts to see if there
are no further improvement suggestions, if there are more follow
the 4 steps described procedure once again.
It's also a good idea that these scripts are periodically re-run on
the server like once per few months as changes in SQL queries
amounts and types will require changes in MySQL operational
variables.
The authors of these nice scripts has done great job and have saved
us a tons of nerves time, downtimes and money spend on meaningless
hardware. So big thanks for the awesome scripts guys ;)
Finally after hopefully succesful deployment of changes, enjoy the
incresed SQL server performance ;)