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
