Please I need some little help from you guys.

I have a VPS I bought since 2018 and since then one of the most difficult thing i have been facing is high load on my SQL, but i guess that was due to my big database of 1GB size. my vps spec is 16GB RAM & 6 cores.

Recently I have sold the site and deleted the 1GB database which was causing me issues on mysql server. Ever since then I have little wp sites on the server with No much traffic, but I still have mysql hitting up to 59% sometimes. I have check to see if its long query that is taking it but nothing at all.

One day I even switch off my apeche just to make sure I see the sql server uses low cpu process but to my greatest surprise I am getting at least 5-15%

I thought since no apache is connected the sql server has to fall to 0.2 or i don't even want to see it among my list of processes when i run top command.

Please Guru in the house is it okay to get this limit while no website is running on the server. and what are the optimization needed to stop it from reaching such percentage even with no long query running on the websites. or is there an evil spirit from the old 1gb deleted database that is still disturbing the mysql server. help me out please.

  • Connect to MySQL as 'root' and do SHOW PROCESSLIST; High CPU or "Load" implies poorly indexes and/or written query -- find it, then let's discuss. – Rick James May 20 at 23:24
  • here is what i get as from show processlist:HERE – XATA May 21 at 1:14
  • If this is WordPress, see mysql.rjweb.org/doc.php/… – Rick James May 21 at 22:36
  • i dont know more about database sql but do i have to drop the wp_postmeta table before creating another one based on the solution you specified on the link? and wont i lose my files if i drop them? – XATA May 21 at 22:58
  • Dropping wp_postmeta will lose important data; don't do it!. See ALTER TABLE. I don't think you showed enough of the General log to provide clues. (Except that WP is involved.) – Rick James May 21 at 23:00

With nothing running, mysqld will use 0% of CPU, therefore you must have something running. Enable general log, that will show you exactly what it's doing.

And 1GB isn't a particularly big database.

| improve this answer | |
  • I already have log-error automatically server by the hosting provider. or is there any other log i should set? – XATA May 18 at 1:16
  • You need general_log. – Gordan Bobic May 18 at 8:43
  • Alright u will create one now and give feed back in some hours time – XATA May 18 at 9:12
  • here is what I am seeing after enabling general log HERE – XATA May 18 at 9:52
  • I guess that disproves your hypothesis that the server isn't doing anything when it is using up 59% of CPU. – Gordan Bobic May 18 at 11:35

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