1

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.

1
  • I would enable slow query logging and look at what queries are eating CPU. I would then look at how I could fix that bottleneck - possibly with indexes, rewriting query, finding ways to do it less frequently.. Using "explain query" can provide guidance on how SQL is handling the query. Maybe look at something like redis is or memcache to reduce numbet of queries being run frequently.
    – davidgo
    Apr 16 '20 at 19:19
2

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...

6
  • And LOG_OUTPUT = FILE. And pt-query-digest is arguably better than mysqldumpslow.
    – Rick James
    Apr 17 '20 at 5:26
  • log_output=file is default, and pt-query-digest doesn't ship with the database. :-) Apr 17 '20 at 9:34
  • Does this log every slow individual query? If so then I already have one enabled and I have just checked it and there are a few odd ones that are taking > 1000 ms to complete. Even though they are not too common, can they be the culprit? Apr 17 '20 at 13:00
  • @slothinspace - The slowlog, with long_query_time = 0 may record all queries. And it will fill up the disk rapidly. So, be cautious. The "General log" is another way of capturing all queries. The mydumpslow suggested above presents the "worst" queries -- these are the ones most likely to be a burden on the system (high CPU, etc). Show us the first two.
    – Rick James
    Apr 17 '20 at 15:57
  • @Wilson Hauck it doesn't matter how many times you repeat it, it's not true. I just tested it this morning on MySQL 5.7 and 8.0 and MariaDB 10.3 and 10.4 and long_query_time=0 records every query. Apr 21 '20 at 20:52
2

I cannot comment due to lack of reputation. I'm new here! But consider this as a comment.

Some things to consider. Inno_buffer_pool_size seems excessive to and according to docs; *"Buffer pool size must always be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances"*

I had a similar issue a few months back and after modifying everything I could think of I finally solved it by making a backup copy of the config file (in my case /etc/my.cnf.d/server.cnf) and removing everything except the essentials like bind ip address and port.

After reloading mysql the issue had gone away so I new it was the combination of changes I had made. I reintroduced each change, restarting each time until the original problem reappeared. I can't remember which option it was but I fiddled around and tuned it better.

It doesn't appear that swapping is an issue for you, but keep an eye on any disk swapping which may be happening.

Again, this is a comment. :)

3
  • 1
    Spot on for not changing configuration away from defaults unless you really know what you are doing and have a very specific reason to deviate from defaults. The only exception to this is innodb_buffer_pool_size. Have an upvote to help you out with your reputation. :-) Apr 16 '20 at 21:34
  • @powerload79 thanks for the up vote, not being able to comment is a real pain. MySQL can be particularly difficult to diagnose issues imho. Setting back to defaults and starting again is always a good start and will often sort the problem and allow fine tuning to continue. Apr 16 '20 at 21:42
  • The formula with "chunk_size" is quite irrelevant. Probably you have less than 22GB of data and indexes, hence not filling up the buffer_pool. Not a problem.
    – Rick James
    Apr 17 '20 at 5:24
1
innodb_read_io_threads=8G
innodb_write_io_threads=8G

NO!!

Each io_thread takes some amount of RAM, CPU, System, etc. "8" is reasonable; "8G" is very unreasonable. I'm surprised the system did not crash.

Did you change any other settings?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.