Mysql server optimization for InnoDB and MyISAM
Mysql server optimization
In my other articles I showed you how to install a mysql server on Centos or on Debian / Ubuntu and the process wasn’t that hard right? Well that’s how it should be, installing things on a server is a basic thing really, configuring and optimizing it for your needs its the hardest thing. Not that its not possible for anyone, but there are just so many options available currently that it is hard to keep track of what you need to change or set for your own server. In this article I’m going to show you some nice little tricks how you can quickly optimize your mysql server using custom values or predefined ones from the example configuration.
Mysql example configuration
I’m going to start with the predefined server configuration examples, in most mysql server installations these should come with a basic example configuration for small, medium or huge server resources. You can find these configuration files in the location /usr/share/mysql/ on centos, unfortunately I could not find them on my Debian test box so I assume these are not installed by default:
1 2 3 4 5 6 |
# ls /usr/share/mysql | grep my- my-huge.cnf* my-innodb-heavy-4G.cnf* my-large.cnf* my-medium.cnf* my-small.cnf* |
For those who did not find the sample configuration I thought of adding these for you and link them here. Please use below links to get to your exact page:
- my-huge.cnf — For servers with 1GB – 2GB ram
- my-innodb-heavy-4G.cnf — Specifically for InnoDB only with 4GB ram
- my-large.cnf — For servers with 512MB ram
- my-medium.cnf — For servers with less than 128MB ram
- my-small.cnf — For server with less than 64MB ram
To use either of the above sample configuration you only need to backup the current configuration and copy the contents from the above files to your own:
1 |
/etc/my.cnf |
After changing the mysql configuration file, you always need to restart mysql, so make sure you do this to apply your new settings:
1 2 3 4 5 |
# Debian restart mysql /etc/init.d/mysql restart # Centos restart mysqld /etc/init.d/mysqld restart |
Then you are all done. That’s basically it with how working with a sample mysql configuration files is, these are already made to work.
Using Mysqltunner.pl for tunning mysql performance
This could not have been missing from this article, this piece of script is not just useful, but also great! It reads the current settings, checks your server usage and recommends what you need to do, to optimize and speed mysql queries. I’ve been using this one for years now with great support overall.
You can download the script from this page:
http://mysqltuner.com
Currently this script is being hosted by github.com here:
https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
But anyway, use either links and copy the content to your server, I usually download it in /root/
1 2 |
cd /root/ wget http://mysqltuner.com |
This will download the mysqltunner.pl file. Now you just need to run the file using command:
1 |
perl mysqltunner.pl |
You may be asked to enter your root password, you just need to allow the script to connect to your mysql server for inspection. The result should be similar as bellow:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
vps:~# 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 [OK] Logged in using credentials from debian maintenance account. -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.5.31-0+wheezy1 [OK] Operating on 32-bit architecture with less than 2GB RAM -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 141M (Tables: 211) [!!] Total fragmented tables: 1 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 1d 15h 36m 6s (48K q [0.338 qps], 3K conn, TX: 348M, RX: 4M) [--] Reads / Writes: 50% / 50% [--] Total buffers: 56.0M global + 832.0K per thread (151 max threads) [OK] Maximum possible memory usage: 178.7M (46% of installed RAM) [OK] Slow queries: 0% (0/48K) [OK] Highest usage of available connections: 2% (4/151) [OK] Key buffer size / total MyISAM indexes: 16.0M/2.2M [OK] Key buffer hit rate: 99.3% (38K cached / 267 reads) [OK] Query cache efficiency: 84.1% (20K cached / 24K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (1 temp sorts / 456 sorts) [!!] Temporary tables created on disk: 38% (1K on disk / 3K total) [OK] Thread cache hit rate: 99% (4 created / 3K connections) [OK] Table cache hit rate: 34% (471 open / 1K opened) [OK] Open file limit used: 3% (706/20K) [OK] Table locks acquired immediately: 100% (8K immediate / 8K locks) -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Enable the slow query log to troubleshoot bad queries When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Variables to adjust: tmp_table_size (> 24M) max_heap_table_size (> 24M) |
You need to be wary about the points before the “Recomandation” section, for example in my case it sees:
[!!] Temporary tables created on disk: 38% (1K on disk / 3K total)
is a bit low, we have 3K temporary tables and there is 1k on disk. We increase the values of:
1 2 |
tmp_table_size max_heap_table_size |
to an acceptable value for best performance. Please do not increase it too much. You need to keep track of your actual mysql memory usage so that you don’t go over your system limit.
1 |
[OK] Maximum possible memory usage: 178.7M (46% of installed RAM) |
Also keep in mind that on some systems it is not just your mysql server that is running, you may have a web server or a another service running there. That too will need ram and CPU to run! So don’t be hasty when adjusting the limits.
? Optimizing mysql for InnoDB storage engine
Pages: 1 2