MySQL database optimisation

Our database is loaded by full-text search requests. The problem was that MySQL server CPU usage was constantly about 100% with close to 0% of Memory usage.
research brought me to the article Mysql full text search cause high usage CPU
Among answers I found an interesting tool

Get the script and run

$ wget
$ chmod +x
$ ./

I got the following output:

 >>  MySQLTuner 1.4.2 - Major Hayden 
 >>  Bug reports, feature requests, and downloads at
 >>  Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.5.42
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[!!] InnoDB is enabled but isn't being used
[OK] Total fragmented tables: 0

-------- Security Recommendations  -------------------------------------------

-------- Performance Metrics -------------------------------------------------
[--] Up for: 24d 11h 3m 36s (8M q [4.132 qps], 551K conn, TX: 4B, RX: 3B)
[--] Reads / Writes: 55% / 45%
[--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
[OK] Maximum possible memory usage: 583.2M (35% of installed RAM)
[OK] Slow queries: 0% (8K/8M)
[OK] Highest usage of available connections: 52% (79/151)
[!!] Cannot calculate MyISAM index size - re-run script as root user
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (194 temp sorts / 594K sorts)
[OK] Temporary tables created on disk: 4% (23K on disk / 540K total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 91% (267 open / 292 opened)
[OK] Open file limit used: 18% (192/1K)
[OK] Table locks acquired immediately: 99% (5M immediate / 5M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    Enable the slow query log to troubleshoot bad queries
    Set thread_cache_size to 4 as a starting value
Variables to adjust:
    query_cache_size (>= 8M)
    thread_cache_size (start at 4)

So, I found that I need to increase query_cache_size and thread_cache_size values.
I executed the following commands in mysql:

mysql> SET GLOBAL query_cache_size = 4000000;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SET GLOBAL thread_cache_size = 5;
Query OK, 0 rows affected (0.00 sec)

Now, MySQL server consumes about 4% of memory and occasionally raises CPU usage up to 100% for a very short time, so it doesn’t influence the whole server performance.

Leave a Reply

%d bloggers like this: