Hi Diana, both scripts need to be run on the live server. They try to gather basic statistics and advice for config changes.
There is no use in running them on other machines/vms... kind regards 2016-07-04 11:59 GMT+02:00 Diana Scannicchio <diana.scannicc...@cern.ch>: > Hello Andreas, > unfortunately we cannot upgrade to versions that are not in the official > CERN release of the OS. > No I did not try the tuning advisors yopu suggested > I noticed that there was a bug report for apparently the same issue > https://dev.icinga.org/issues/10731 > and that it was theoretically solved in version 2.4.4. > Could it be that it reappeared or was not completely solved? > > I run the script you suggested on a temporary VM that can be destroyed and > I got the following > > -------- Recommendations > --------------------------------------------------------------------------- > General recommendations: > Add skip-innodb to MySQL configuration to disable InnoDB > Enable the slow query log to troubleshoot bad queries > Configure your accounts with ip or subnets only, then update your > configuration with skip-name-resolve=1 > Variables to adjust: > query_cache_size (>= 8M) > > > Thank you very much, > > Diana > > > On 01 Jul 2016, at 16:42, Andreas Lehr <m...@andreas-lehr.com> wrote: > > > 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/shakalandy > > m...@andreas-lehr.com > > > > > > _______________________________________________ > > 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
