Quick MySQL performance boost

Few domains on the server, such as Istvan's ESN-ASK depend on MySQL-database, which has partly Finnish roots due to lead developer Michael Widenius. MySQL's future is a bit unclear for now because it was first bought by Sun Microsystems and later on by Oracle, which is one of the largest software companies in the world and it has its own relational database application. In the worst case Oracle will end the MySQL project and popular internet sites such as Google, Facebook and Wikipedia have to search for alternatives. This test server has started to run PostgreSQL in addition to MySQL for migration and testing purposes.

After running the new server for a few days it was time to analyze MySQL-performance with mysqltuner command-line application. It recommended to add the following lines to my.cnf:

thread_cache_size = 4
query_cache_size = 12M
table_cache = 256

The application generates recommendations based on the db usage statistics so it takes again few days to see if the new configuration still needs further optimization.

[root@lnx ~]# mysqltuner

 >>  MySQLTuner 1.0.0-rc1 - 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:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.37
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 52M (Tables: 188)
[--] Data in MEMORY tables: 0B (Tables: 1)
[!!] InnoDB is enabled but isn't being used
[!!] Total fragmented tables: 16

-------- Performance Metrics -------------------------------------------------
[--] Up for: 4h 2m 19s (6K q [0.476 qps], 880 conn, TX: 42M, RX: 1M)
[--] Reads / Writes: 74% / 26%
[--] Total buffers: 46.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 461.2M (11% of installed RAM)
[OK] Slow queries: 0% (0/6K)
[OK] Highest usage of available connections: 3% (5/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/9.1M
[OK] Key buffer hit rate: 97.7% (13K cached / 309 reads)
[OK] Query cache efficiency: 75.8% (3K cached / 4K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 316 sorts)
[OK] Temporary tables created on disk: 7% (21 on disk / 295 total)
[OK] Thread cache hit rate: 99% (8 created / 880 connections)
[OK] Table cache hit rate: 92% (85 open / 92 opened)
[OK] Open file limit used: 16% (170/1K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries

[root@lnx ~]#