Hi Andreas,
thank you, I just run it also on what you call the live server and this is the
output I got
-------- Recommendations
---------------------------------------------------------------------------
General recommendations:
Remove Anonymous User accounts - there are 2 anonymous accounts.
Set up a Secure Password for user@host ( SET PASSWORD FOR
'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
Configure your accounts with ip or subnets only, then update your
configuration with skip-name-resolve=1
Variables to adjust:
query_cache_size (>= 8M)
Best regards,
Diana
P.S. the VM I used before is showing exactly the same issues.
On 05 Jul 2016, at 13:27, Andreas Lehr <[email protected]> wrote:
> 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 <[email protected]>:
> 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 <[email protected]> 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 <[email protected]>:
> > 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 <[email protected]>
> > 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
> > > <[email protected]> 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
> > >> [email protected]
> > >> 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
> > > [email protected]
> > > 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
> > [email protected]
> > https://lists.icinga.org/mailman/listinfo/icinga-users
> >
> >
> >
> > --
> > Andreas Lehr
> > twitter.com/shakalandy
> > [email protected]
> >
> >
> > _______________________________________________
> > icinga-users mailing list
> > [email protected]
> > 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
> [email protected]
> https://lists.icinga.org/mailman/listinfo/icinga-users
>
>
>
> --
> Andreas Lehr
> twitter.com/shakalandy
> [email protected]
>
>
> _______________________________________________
> icinga-users mailing list
> [email protected]
> 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
[email protected]
https://lists.icinga.org/mailman/listinfo/icinga-users
