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.