Have you tried to usual MySQL tuning advisors? http://mysqltuner.pl https://github.com/RootService/tuning-primer
Is it possible to upgrade to a more recent MySQL version? Maybe 5.5 or better 5.6 or even 5.7? What about Disk I/O on the host? do you have I/O waits? 2016-07-01 15:45 GMT+02:00 Diana Scannicchio <diana.scannicc...@cern.ch>: > Hello Antony, > so this is interesting… I stopped icinga2 service and mysqld, I added in > the [mysqld] section of /etc/my.cnf the following two lines > > slow-query-log = 1 > slow-query-log-file = /var/log/mysql-slow-query.log > > Then I touched /var/log/mysql-slow-query.log and change the ownership to > mysqld > > Finally I restarted mysqld and then icinga2 at 15.00. > > The file /var/log/mysql-slow-query.log after more than 20 minutes was > still “empty”, so I executed > > [root log]# tail -f mysql-slow-query.log > mysql> SELECT SLEEP(10); > +-----------+ > | SLEEP(10) | > +-----------+ > | 0 | > +-----------+ > 1 row in set (10.01 sec) > > which ended up into the log: > > /usr/libexec/mysqld, Version: 5.1.73-log (Source distribution). started > with: > Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock > Time Id Command Argument > > > # Time: 160701 15:27:27 > # User@Host: root[root] @ localhost [] > # Query_time: 10.000245 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 > SET timestamp=1467379647; > SELECT SLEEP(10); > > Please note that I am not (yet ;) ) a DB expert > > The icinga2.log contains the following… > > [2016-07-01 15:03:51 +0200] information/IdoMysqlConnection: Query queue > items: 505748, query rate: 0.683333/s (41/min 41/5min 41/15min); empty in > infinite time, your database isn't able to keep up > [2016-07-01 15:04:06 +0200] information/IdoMysqlConnection: Query queue > items: 593751, query rate: 0.683333/s (41/min 41/5min 41/15min); empty in > infinite time, your database isn't able to keep up > [2016-07-01 15:04:21 +0200] information/IdoMysqlConnection: Query queue > items: 645002, query rate: 0.683333/s (41/min 41/5min 41/15min); empty in > infinite time, your database isn't able to keep up > [2016-07-01 15:04:27 +0200] information/Checkable: Notifications are > disabled for service 'vs-atlas-cr-52!pcoip/pingtime'. > [2016-07-01 15:04:36 +0200] information/IdoMysqlConnection: Query queue > items: 713414, query rate: 0.683333/s (41/min 41/5min 41/15min); empty in > infinite time, your database isn't able to keep up > [2016-07-01 15:04:51 +0200] information/IdoMysqlConnection: Query queue > items: 802373, query rate: 2027.68/s (121661/min 121686/5min 121686/15min); > empty in infinite time, your database isn't able to keep up > [2016-07-01 15:05:06 +0200] information/IdoMysqlConnection: Query queue > items: 853117, query rate: 2027.68/s (121661/min 121686/5min 121686/15min); > empty in infinite time, your database isn't able to keep up > [2016-07-01 15:05:21 +0200] information/IdoMysqlConnection: Query queue > items: 886383, query rate: 2027.68/s (121661/min 121686/5min 121686/15min); > empty in infinite time, your database isn't able to keep up > [2016-07-01 15:05:27 +0200] information/Checkable: Notifications are > disabled for service 'vs-atlas-cr-52!pcoip/pingtime'. > [2016-07-01 15:05:36 +0200] information/IdoMysqlConnection: Query queue > items: 955106, query rate: 2027.68/s (121661/min 121686/5min 121686/15min); > empty in infinite time, your database isn't able to keep up > > Do you have any further suggestion ? am I doing something wrong? > Thank you very much, > > Diana > > > > On 01 Jul 2016, at 11:29, Diana Scannicchio <diana.scannicc...@cern.ch> > wrote: > > > Hello Antony, > > so both the machines (they are identical) have 2 CPU (CPU E5-2620 v3 @ > 2.40GHz) with 6 core per processor, so a total of 12 CPU cores. > > I assumed that the test machine is swapping a lot due to MySQL not able > to cope. > > The majority of the process are run by root and they are on both nodes. > > Please note that the production node does not have the issue > > > > The icinga2 database has been created in MySQL following the > instructions found in > > > http://docs.icinga.org/icinga2/latest/doc/module/icinga2/toc#!/icinga2/latest/doc/module/icinga2/chapter/getting-started#installing-icingaweb2 > > section "2.5.1.3. Setting up the MySQL database" > > > > The content of /etc/my.cnf is pasted below. > > I will try to enable the slow query log and see if I spot something as > you suggested. > > Still there should be some difference between v2.3.11 and v2.4.* that is > preventing the to run with the latter or something to be modified when > updating. > > > > Thank you, > > > > DIana > > > > > > # cat /etc/my.cnf > > [mysqld] > > datadir=/var/lib/mysql > > socket=/var/lib/mysql/mysql.sock > > user=mysql > > # Disabling symbolic-links is recommended to prevent assorted security > risks > > symbolic-links=0 > > > > > > ## Other tuning, from standard setupi added by Diana - 26 Apr 2015, 1 > June 2016 on this node > > ## http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html > > # Set buffer pool size to 50-80% of your computer's memory > > # This was already set 8 GB > > # show variables like 'innodb_%' : innodb_buffer_pool_size 8388608 > > innodb_buffer_pool_size = 8G > > # Set the log file size to about 25% of the buffer pool size > > innodb_log_file_size = 512M > > innodb_log_buffer_size = 128M > > > > innodb_file_per_table = 1 > > max_allowed_packet = 32M > > > > [mysqld_safe] > > log-error=/var/log/mysqld.log > > pid-file=/var/run/mysqld/mysqld.pid > > > > > > > > On 30 Jun 2016, at 16:46, Antony Stone < > antony.st...@icinga.open.source.it> wrote: > > > >> On Thursday 30 June 2016 at 15:23:19, Diana Scannicchio wrote: > >> > >>> below you can see what I get by connecting to MySQL as a privileged > user and > >>> asking it to "show processlist” on both the nodes (one running v2.4.10 > and > >>> the second one running v2.3.11. And also the output of top. > >> > >> Well, the first interestng bit is on the test machine, where MySQL is > busy > >> "logging slow query", so I do think that turning on the slow query log > in your > >> MySQL my.cnf should shed some further light on this problem. > >> > >> Secondly I see from 'top' that mysqld is using 67.4% CPU and 70.4% > memory, > >> which is certainly excessive for this activity. > >> > >> The test machine is also using ~128Mbytes swap space, which is not > excessive, > >> but interesting given that the production machine is using none. > >> > >> I see a load average of 7.74 - how many CPU cores are there on the test > >> machine? > >> > >> Finally, you have 964 processes running (again, on the test machine - > it seems > >> to show the problem more extremely than the production machine does) - > can you > >> identify from ps what the majority of these are? > >> > >> The only other question I have is "how was the icinga2 database created > in > >> MySQL on these machines?" It does look to me rather as though missing > indexes > >> could be the problem, but I think the output of the slow query log > should help > >> with that. > >> > >> > >> Regards, > >> > >> > >> Antony. > >> > >> -- > >> #define SIX 1+5 > >> #define NINE 8+1 > >> > >> int main() { > >> printf("%d\n", SIX * NINE); > >> } > >> - thanks to ECB for bringing this to my attention > >> > >> Please reply to the > list; > >> please *don't* > CC me. > >> _______________________________________________ > >> icinga-users mailing list > >> icinga-users@lists.icinga.org > >> https://lists.icinga.org/mailman/listinfo/icinga-users > > > > - > > Diana Scannicchio > > University of California, Irvine > > ATLAS TDAQ SysAdmin group > > Office: +41 22 76 75240 > > OnCall: 164851 > > > > > > > > > > > > > > _______________________________________________ > > icinga-users mailing list > > icinga-users@lists.icinga.org > > https://lists.icinga.org/mailman/listinfo/icinga-users > > - > Diana Scannicchio > University of California, Irvine > ATLAS TDAQ SysAdmin group > Office: +41 22 76 75240 > OnCall: 164851 > > > > > > > _______________________________________________ > icinga-users mailing list > icinga-users@lists.icinga.org > https://lists.icinga.org/mailman/listinfo/icinga-users > -- Andreas Lehr twitter.com/shakalandym...@andreas-lehr.com
_______________________________________________ icinga-users mailing list icinga-users@lists.icinga.org https://lists.icinga.org/mailman/listinfo/icinga-users
