Score:0

Mariadb using all cpu on Ubuntu VPS and makes server unresponsive

bd flag

I have couple of ecommerce websites on one Ubuntu VPS with 4 CPU cores and 16GB of RAM.

Nothing really intensive it ran perfectly fine even on shared hosting.

However now I am having issue with mariadb. Once mysql service is started it begins poping more and more processes until it takes whole CPU and then web page is taking forever to open.

I haven't touched anything in configuration, I am backend developer and not really into server administration so any advice would be much appreciated.

top usage

enter image description here

vidarlo avatar
ar flag
How large is your database? Do you log slow queries? How much traffic do you see? What does mariadb logs show in general?
Petr Chloupek avatar
gb flag
SHOW PROCESSLIST; and enable slow_log, also, dmesg look for any harddrive related issus. Anything interesting?
cvetan avatar
bd flag
It shows bunch of processes with single query which are stucked in sending data state. I haven't changed that query for some time. It may not be the best in the world, but I am wondering why it started now.
vidarlo avatar
ar flag
Can you add more detail based on what you found to your question?
cvetan avatar
bd flag
Yes. Thanks for the help. Looks like the traffic in website increased for the past few days, hence made the problem in question. The query had GROUP_CONCAT clause and some COUNT, so not really light weight. I switched that to separate query and water started flowing again. :) Thanks again.
djdomi avatar
za flag
if its solved please remind that you should add an answer
Wilson Hauck avatar
jp flag
Additional information request, please. Any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: A) SELECT COUNT(*) FROM information_schema.tables; B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) STATUS; not SHOW STATUS, just STATUS; AND very helpful OS information, includes - ulimit -a for list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions.
ua flag
(Alas, process states, such as "sending data", are usually not helpful.) We need to see the full query, plus `SHOW CREATE TABLE`.
cvetan avatar
bd flag
For now the issue seems to be gone, to some extent at least, with fixed query in question. I will take your suggestions, and inspect it further, and will get back to you with information.
Score:0
ua flag

You have 16GB of RAM? But innodb_buffer_pool_size is only 128M? Change that to 8G. (I can't be sure that it will help for CPU.)

Look in the SlowLog for the 'worst' query; then ask us for help in speeding it up.

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.