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

Read Complete Post From Source: BLog.CripperZ.SG

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

[mysqld] #skip-locking local-infile=0 key_buffer = 16K max_allowed_packet = 1M table_cache = 4 sort_buffer_size = 64K read_buffer_size = 256K read_rnd_buffer_size = 256K net_buffer_length = 2K thread_stack = 64K key_buffer_size = 12M thread_cache_size = 4 tmp_table_size = 16M query_cache_size = 8M max_heap_table_size = 16M   [mysqldump] quick max_allowed_packet = 16M   [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates   [isamchk] net_buffer_length = 2K thread_stack = 64K key_buffer_size = 12M thread_cache_size = 4 tmp_table_size = 16M query_cache_size = 8M max_heap_table_size = 16M   [mysqldump] quick max_allowed_packet = 16M   [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates   [isamchk] key_buffer = 8M sort_buffer_size = 8M   [myisamchk] key_buffer = 8M sort_buffer_size = 8M   [mysqlhotcopy] interactive-timeout

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 <major@mhtx.net>

>>  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.

Good Mysql Server Configuration for Low End VPS / Servers – 1GB Memory / 1GB Swap
Read Complete Post From Source: BLog.CripperZ.SG