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
