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.

Improve this question
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. Commented Apr 16, 2020 at 19:19

3 Answers 3

Reset to default
3

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

Improve this answer
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. :-) Commented Apr 16, 2020 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. Commented Apr 16, 2020 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. Commented Apr 17, 2020 at 5:24
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...

Improve this answer
6
  • And LOG_OUTPUT = FILE. And pt-query-digest is arguably better than mysqldumpslow. Commented Apr 17, 2020 at 5:26
  • log_output=file is default, and pt-query-digest doesn't ship with the database. :-) Commented Apr 17, 2020 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? Commented Apr 17, 2020 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. Commented Apr 17, 2020 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. Commented Apr 21, 2020 at 20:52
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?

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.