Good Mysql Server Configuration for Low End VPS / Servers – 1GB Memory / 1GB Swap

I have previously shared my prefork Apache MPM settings for low end VPS. Here is the good / tested / working Mysql server configurations / settings to go along with it.

Edit /etc/my.cnf with configurations below and restart mysql server

 

/etc/my.cnf

You also use mysql tuner perl script to help you / suggest you mysql settings on your VPS. Scripts are generally to help admin to figure out possible optimization, scripts should never be the answer to optimized configurations. Experience and monitoring would still be required for your final tuning configurations.

MySQLTuner is a Perl script that analyzes your MySQL performance and, based on the statistics it gathers, gives recommendations which variables you should adjust in order to increase performance. That way, you can tune your my.cnf file to tease out the last bit of performance from your MySQL server and make it work more efficiently.

This document comes without warranty of any kind! I do not issue any guarantee that this will work for you!

 

1 Using MySQLTuner

You can download the MySQLTuner script as follows:

wget http://mysqltuner.com/mysqltuner.pl

In order to run it, we must make it executable:

chmod +x mysqltuner.pl

Afterwards, we can run it. You need your MySQL root password for it:

./mysqltuner.pl

server1:~# ./mysqltuner.pl

>>  MySQLTuner 0.9.8 – Major Hayden <[email protected]>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with ‘–help’ for additional options and output filtering
Please enter your MySQL administrative login: <– root
Please enter your MySQL administrative password: <– yourrootsqlpassword

——– General Statistics ————————————————–
[–] Skipped version check for MySQLTuner script
[!!] Your MySQL version 4.1.11-Debian_etch1-log is EOL software!  Upgrade soon!
[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: 301M (Tables: 2074)
[–] Data in HEAP tables: 379K (Tables: 9)
[!!] InnoDB is enabled but isn’t being used
[!!] ISAM is enabled but isn’t being used
[!!] Total fragmented tables: 215

——– Performance Metrics ————————————————-
[–] Up for: 12d 18h 33m 30s (1B q [1K qps], 185K conn, TX: 3B, RX: 377M)
[–] Reads / Writes: 78% / 22%
[–] Total buffers: 2.6M per thread and 58.0M global
[OK] Maximum possible memory usage: 320.5M (20% of installed RAM)
[OK] Slow queries: 0% (17/1B)
[OK] Highest usage of available connections: 32% (32/100)
[OK] Key buffer size / total MyISAM indexes: 16.0M/72.3M
[OK] Key buffer hit rate: 99.9%
[OK] Query cache efficiency: 99.9%
[!!] Query cache prunes per day: 47549
[OK] Sorts requiring temporary tables: 0%
[!!] Temporary tables created on disk: 28%
[OK] Thread cache hit rate: 99%
[!!] Table cache hit rate: 0%
[OK] Open file limit used: 12%
[OK] Table locks acquired immediately: 99%
[!!] Connections aborted: 20%

——– Recommendations —————————————————–
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
Add skip-isam to MySQL configuration to disable ISAM
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
Increase table_cache gradually to avoid file descriptor limits
Your applications are not closing MySQL connections properly
Variables to adjust:
query_cache_size (> 16M)
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
table_cache (> 64)

server1:~#

You should carefully read the output, especially the recommendations at the end. It shows exactly which variables you should adjust in the [mysqld] section of your my.cnf (on Debian and Ubuntu the full path is /etc/mysql/my.cnf). Whenever you change your my.cnf, make sure that you restart MySQL. You can then run MySQLTuner again to see if it has further recommendations to improve the MySQL performance. This way, you can optimize MySQL step by step.

 

*** Update 10 October 2013 ***

I have made further tweaks to my Xen virtual server which has 2 cpu cores, 1GB ram allocated / 2GB swap with 40G hard disk space. It appears that the tweaks i made helps to improve the stability of the server running Directadmin hosting my wordpress websites and able to handle more than 100 visitors at any given point of time without freezing like the previous settings i have. Have a look at the Apache and Mysql settings respectively to help improve your settings. See the respective link and configurations below.

Tuning Your Mysql Settings / Conf

Tuning Your Apache Settings / Conf

 

 



Comments

comments

Leave a Reply