[arch-general] Mariadb eats system ressources
Hi, after struggling with this problem for one week I have finally decided to post on the arch list for getting some help. I have a very strange problem with mariadb from the official repos. I run a usual LAMP stack (apache, php-fpm, mariadb) on archlinux. Since one week (or so) I have realized a very strange behavior: One mariadb process stucks on 100 percent CPU usage. I am not able to figure out what's going on. Restarting mariadb fixes the problem for a few hours. Here is a list of a few debug outputs [1]; I have put it in my pastebin because my email client would wrap the text at 80 chars. If you need even more information I can update the paste. The mariadb status page shows about 6,5 queries per second (!) and I don't know where this comes from. I have disabled remote access with "skip-networking" (mysql port is closed) and the PROCESSLIST does only show one process. I thought it might be an DDoS attack on a website but no apache access-log grows unexpectedly. Even after stopping apache mariadb stucks on 100 percent cpu usage. I figured out that there are listed a lot of connections in the output of "show status like 'Con%';". Last hour I even had about 5000 connections but there are no appropriate queries visible in the general log. The general log lists the wordpress queries which are executed when I click on my homepage. Everything looks fine; the cpu usage does not! Do you have any ideas what I can do to figure out what's going on with my mariadb? Thanks in advance! Stefan [1]: http://snippets.sevenbyte.org/8/raw/
Hi,
Have you an phpMyAdmin installed on your apache server ?
If yes log in as privileges user, and go to section Status, then Query
statistics and you will able to see what kind of queries is executed. Maybe
you will find the guilty request ?
Best regards
2014-10-09 16:47 GMT+02:00
Hi,
after struggling with this problem for one week I have finally decided to post on the arch list for getting some help. I have a very strange problem with mariadb from the official repos.
I run a usual LAMP stack (apache, php-fpm, mariadb) on archlinux. Since one week (or so) I have realized a very strange behavior: One mariadb process stucks on 100 percent CPU usage. I am not able to figure out what's going on. Restarting mariadb fixes the problem for a few hours. Here is a list of a few debug outputs [1]; I have put it in my pastebin because my email client would wrap the text at 80 chars. If you need even more information I can update the paste.
The mariadb status page shows about 6,5 queries per second (!) and I don't know where this comes from. I have disabled remote access with "skip-networking" (mysql port is closed) and the PROCESSLIST does only show one process. I thought it might be an DDoS attack on a website but no apache access-log grows unexpectedly. Even after stopping apache mariadb stucks on 100 percent cpu usage.
I figured out that there are listed a lot of connections in the output of "show status like 'Con%';". Last hour I even had about 5000 connections but there are no appropriate queries visible in the general log. The general log lists the wordpress queries which are executed when I click on my homepage. Everything looks fine; the cpu usage does not! Do you have any ideas what I can do to figure out what's going on with my mariadb?
Thanks in advance!
Stefan
-- Pierre GINDRAUD pgindraud@gmail.com
On 09/10/14, rumpelsepp@sevenbyte.org wrote:
Hi,
after struggling with this problem for one week I have finally decided to post on the arch list for getting some help. I have a very strange problem with mariadb from the official repos.
I run a usual LAMP stack (apache, php-fpm, mariadb) on archlinux. Since one week (or so) I have realized a very strange behavior: One mariadb process stucks on 100 percent CPU usage. I am not able to figure out what's going on. Restarting mariadb fixes the problem for a few hours. Here is a list of a few debug outputs [1]; I have put it in my pastebin because my email client would wrap the text at 80 chars. If you need even more information I can update the paste.
The mariadb status page shows about 6,5 queries per second (!) and I don't know where this comes from. I have disabled remote access with "skip-networking" (mysql port is closed) and the PROCESSLIST does only show one process. I thought it might be an DDoS attack on a website but no apache access-log grows unexpectedly. Even after stopping apache mariadb stucks on 100 percent cpu usage.
I figured out that there are listed a lot of connections in the output of "show status like 'Con%';". Last hour I even had about 5000 connections but there are no appropriate queries visible in the general log. The general log lists the wordpress queries which are executed when I click on my homepage. Everything looks fine; the cpu usage does not! Do you have any ideas what I can do to figure out what's going on with my mariadb?
Thanks in advance!
Stefan
Can you share your my.cnf and a few information on your databases you host on the mariadb server? --
On 2014-10-09 17:48, Leonidas Spyropoulos wrote:
Can you share your my.cnf and a few information on your databases you host on the mariadb server?
The my.cnf is just the default one from the repo, I have enabled "skip-network". My setup is not that big. I host 5 wordpress sites and one piwik instance on it. There are daily about 50 visitors maximum. Apache is most of the time in idle state and -- as I mentioned before -- even disabling apache does not fix the cpu usage of mariadb. Stefan
On 2014-10-09 17:28, Pierre GINDRAUD wrote:
If yes log in as privileges user, and go to section Status, then Query statistics and you will able to see what kind of queries is executed. Maybe you will find the guilty request ?
I had a look. About 80 percent of all requests are SELECT statements. I have monitored the general query log and there are request every few seconds. So mariadb idles most of the time. I see no reason why it stucks on 100 percent cpu usage... Stefan
El 09/10/2014 18:24, "Stefan Tatschner"
On 2014-10-09 17:48, Leonidas Spyropoulos wrote:
Can you share your my.cnf and a few information on your databases you host on the mariadb server?
The my.cnf is just the default one from the repo, I have enabled "skip-network". My setup is not that big. I host 5 wordpress sites and one piwik instance on it. There are daily about 50 visitors maximum. Apache is most of the time in idle state and -- as I mentioned before -- even disabling apache does not fix the cpu usage of mariadb.
Stefan
Hi, when MySQL is high, go to into cmd line MySQL command and exec: show peocesslist; What's result? You use raíd por single disk?? The state disk health is ok?
On 2014-10-09 18:35, Maykel Franco wrote:
Hi, when MySQL is high, go to into cmd line MySQL command and exec: show peocesslist;
What's result?
see here: http://snippets.sevenbyte.org/8/raw/
You use raíd por single disk?? The state disk health is ok?
I have a virtual server. I suppose the diskstate is ok... Stefan
El 09/10/2014 18:37, "Stefan Tatschner"
On 2014-10-09 18:35, Maykel Franco wrote:
Hi, when MySQL is high, go to into cmd line MySQL command and exec: show peocesslist;
What's result?
see here: http://snippets.sevenbyte.org/8/raw/
You use raíd por single disk?? The state disk health is ok?
I have a virtual server. I suppose the diskstate is ok...
Stefan
Hace you a latest versión mariadb?
On 2014-10-09 18:39, Maykel Franco wrote:
Hace you a latest versión mariadb?
Yes, the version number is even in the paste from my email before...^^
participants (5)
-
Leonidas Spyropoulos
-
Maykel Franco
-
Pierre GINDRAUD
-
rumpelsepp＠sevenbyte.org
-
Stefan Tatschner