I have the following specs:
8vCPUS / 32 GB Memory / 160 GB Disk hosted on Digital Ocean
The web application is built upon Laravel (PHP) and is is currently serving 550 concurrent users at this time.
These are the processes:
17767 mysql 20 0 29.160g 4.160g 18804 S 214.3 13.2 25:55.25 mysqld
20455 www-data 20 0 496504 45364 31252 S 19.9 0.1 0:11.90 apache2
21849 www-data 20 0 496420 44828 30868 S 10.4 0.1 0:08.25 apache2
20470 www-data 20 0 494500 43232 31188 S 8.8 0.1 0:09.81 apache2
2422 www-data 20 0 496436 41656 27660 R 8.5 0.1 0:02.39 apache2
29369 www-data 20 0 494324 42960 31048 R 8.5 0.1 0:04.87 apache2
28830 www-data 20 0 494320 41632 29700 S 8.1 0.1 0:02.57 apache2
21160 www-data 20 0 496392 44796 30804 S 7.8 0.1 0:08.95 apache2
20899 www-data 20 0 494424 42572 30552 R 7.2 0.1 0:07.29 apache2
20971 www-data 20 0 496432 45092 31060 S 6.8 0.1 0:07.21 apache2
21589 www-data 20 0 496468 44692 30612 S 6.5 0.1 0:06.98 apache2
32660 www-data 20 0 496520 44816 30796 R 6.5 0.1 0:03.80 apache2
21650 www-data 20 0 494460 42984 30996 S 5.5 0.1 0:06.84 apache2
...
...
...
The CPU usage from MYSQL is 214% and it seems none of my efforts have helped reduce that number.
Looking at the graphs provided by Digital Ocean, the current overall CPU usage is at 80% total and the RAM is at a measly 25%. Is that odd? I always had the impression that RAM is usually the bottleneck when it comes to performance, not CPU.
Here are my MYSQL settings
key_buffer_size = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 16
myisam-recover-options = BACKUP
max_connections = 500
wait_timeout = 20000
query_cache_limit = 2M
query_cache_size=0
query_cache_type=0
tmp_table_size = 320M
max_heap_table_size = 320M
log_error = /var/log/mysql/error.log
expire_logs_days = 10
max_binlog_size = 100M
innodb_buffer_pool_size=22G
innodb_buffer_pool_instances=22
innodb_log_file_size=5G
innodb_read_io_threads=8G
innodb_write_io_threads=8G
I feel like I've exhausted all options. I've trawled though many internet posts, I've adjusted many variables such as the innodb_buffer_pool_size, innodb_buffer_pool_instance, etc to better represent the hardware, use mysql tuner and followed all of its recommendations, I've spent many many hours going over every single bit of the code, logging every query and request that is slow and optimising the living hell out of the application and that has also made minimum difference. Is there something I am missing? Or am I at a point where I need to just beef up the server again? The 25% ram usage is unusually low….
Any suggestion will be a massive help. Cheers.
Best Answer
You need to post at least the output of
SHOW FULL PROCESSLIST;
And from there possibly enable slow query logging:
slow_query_log=1
long_query_time=0
And then after a while post the output of:
mysqldumpslow -s t /path/to/slow.log | head -100
Then we can look at what queries are eating your CPU and if they can be made to eat less CPU.
Database performance optimisation is 5% configuration and 95% query optimisation, unless the configuration is truly pathologically wrong. Then again, pathologically wrong configuration is plausible if you believed anything mysqltuner told you, e.g. 8bn io threads...