0

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

cvetan
  • 101
  • 2
  • How large is your database? Do you log slow queries? How much traffic do you see? What does mariadb logs show in general? – vidarlo Feb 13 '22 at 10:16
  • 1
    SHOW PROCESSLIST; and enable slow_log, also, dmesg look for any harddrive related issus. Anything interesting? – Petr Chloupek Feb 13 '22 at 10:49
  • 1
    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. – cvetan Feb 13 '22 at 11:39
  • Can you add more detail based on what you found to your question? – vidarlo Feb 13 '22 at 13:30
  • 1
    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. – cvetan Feb 13 '22 at 13:32
  • 1
    if its solved please remind that you should add an answer – djdomi Feb 13 '22 at 16:48
  • 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. – Wilson Hauck Feb 13 '22 at 21:57
  • (Alas, process states, such as "sending data", are usually not helpful.) We need to see the full query, plus `SHOW CREATE TABLE`. – Rick James Feb 14 '22 at 02:59
  • 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. – cvetan Feb 14 '22 at 11:00

1 Answers1

0

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.

Rick James
  • 2,463
  • 1
  • 6
  • 13