MySQL database optimisation
Originally published at Moishe Beshkin. You can comment here or there.
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 mysqltuner.pl
Get the script and run
$ wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl $ chmod +x mysqltuner.pl $ ./mysqltuner.pl
I got the following output:
>> MySQLTuner 1.4.2 - Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> 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 ------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM [!!] 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.