Using the API causing DB to freeze

[sg42]

Hi!

We're using the API to create some simple dashboards.

We use ruby for the connection (https://github.com/Pragmatic-Source/zabby)

A sample script to get trigger status:

serv = Zabby.init do
    set server: ZabbixCredentials::SERVER
    set user: ZabbixCredentials::USERNAME
    set password: ZabbixCredentials::PASSWORD
    login
  end

  env = serv.run do
    Zabby::Trigger.get(
      'filter' => { 'priority' => states.keys },
      'output' => 'extend',
      'only_true' => 'true',
      'monitored' => 1,
      'selectItems' => 1,
      'withUnacknowledgedEvents' => 1,
      'skipDependent' => 1,
      'expandData' => 'host'
    )
  end

Some queries are every 10 seconds the other in larger intervals (10 or 15 minutes).
The generated queries take some time (at the beginning about 10 seconds) to return results. After a while (about 8 hours to one day) the query hangs forever.
So the list of open queries grows and eventualy zabbix gets no free connection to the DB and stops.

The SQL query looks like

SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h WHERE NOT EXISTS (SELECT NULL FROM functions f,items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid='13' WHERE t.triggerid=f.triggerid  AND f.itemid=i.itemid AND i.hostid=hgg.hostid GROUP BY i.hostid HAVING MAX(permission)<2 OR MIN(permission) IS NULL OR MIN(permission)=0) AND NOT EXISTS (SELECT NULL FROM functions f,items i,hosts h WHERE t.triggerid=f.triggerid AND f.itemid=i.itemid AND i.hostid=h.hostid AND (i.status<>0 OR h.status<>0)) AND t.status=0 AND EXISTS (SELECT NULL FROM events e WHERE t.triggerid=e.objectid AND e.source=0 AND e.object=0 AND e.value=1 AND e.acknowledged=0) AND t.priority IN ('2','3','4','5','99') AND t.flags IN ('0','4') AND ((t.value=1) OR ((t.value=0) AND (t.lastchange>1467180927))) AND f.triggerid=t.triggerid AND f.itemid=i.itemid AND h.hostid=i.hostid

[kloczek]

Some queries are every 10 seconds the other in larger intervals (10 or 15 minutes).
The generated queries take some time (at the beginning about 10 seconds) to return results. After a while (about 8 hours to one day) the query hangs forever.
So the list of open queries grows and eventualy zabbix gets no free connection to the DB and stops.

If you are using MySQL change in engine settings:

transaction-isolation = READ-COMMITTED

[sg42]

thanks, I will try it.

[sg42]

Hi!

Setting tx_isolation to READ-COMMITTED doesn't help ;-(
After about 48 hours the mysql-server hangs up, zabbix server dies.

Any other suggestions?

THX,
Stephan

[kloczek]

Hi!

Setting tx_isolation to READ-COMMITTED doesn't help ;-(
After about 48 hours the mysql-server hangs up, zabbix server dies.

Any other suggestions?

Next time try to catch output of "mysqladmin processlist | grep -v Sleep"
Please confirm that you are using innodb storage engine.
Is it any other regularity when it happens like similar time when this issue starts? any other observations?

[sg42]

Here is the output from the last run:

mysql> show processlist;
+--------+------+-----------+--------+---------+-------+--------------+------------------------------------------------------------------------------------------------------+
| Id     | User | Host      | db     | Command | Time  | State        | Info                                                                                                 |
+--------+------+-----------+--------+---------+-------+--------------+------------------------------------------------------------------------------------------------------+
| 201888 | root | localhost | zabbix | Query   | 55970 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201889 | root | localhost | zabbix | Query   | 55960 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201890 | root | localhost | zabbix | Query   | 55950 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201891 | root | localhost | zabbix | Query   | 55940 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201892 | root | localhost | zabbix | Query   | 55930 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201899 | root | localhost | zabbix | Query   | 55920 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201908 | root | localhost | zabbix | Query   | 55910 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201909 | root | localhost | zabbix | Query   | 55900 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201910 | root | localhost | zabbix | Query   | 55890 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201911 | root | localhost | zabbix | Query   | 55880 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201912 | root | localhost | zabbix | Query   | 55869 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201918 | root | localhost | zabbix | Query   | 55860 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201928 | root | localhost | zabbix | Query   | 55850 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201929 | root | localhost | zabbix | Query   | 55840 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201930 | root | localhost | zabbix | Query   | 55830 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201931 | root | localhost | zabbix | Query   | 55820 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201932 | root | localhost | zabbix | Query   | 55810 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201939 | root | localhost | zabbix | Query   | 55800 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201948 | root | localhost | zabbix | Query   | 55790 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201949 | root | localhost | zabbix | Query   | 55780 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201950 | root | localhost | zabbix | Query   | 55770 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201951 | root | localhost | zabbix | Query   | 55760 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201952 | root | localhost | zabbix | Query   | 55750 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201959 | root | localhost | zabbix | Query   | 55740 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201969 | root | localhost | zabbix | Query   | 55730 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201975 | root | localhost | zabbix | Query   | 55720 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201976 | root | localhost | zabbix | Query   | 55710 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201977 | root | localhost | zabbix | Query   | 55700 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201978 | root | localhost | zabbix | Query   | 55690 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201986 | root | localhost | zabbix | Query   | 55679 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201994 | root | localhost | zabbix | Query   | 55670 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201995 | root | localhost | zabbix | Query   | 55660 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201996 | root | localhost | zabbix | Query   | 55650 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201997 | root | localhost | zabbix | Query   | 55640 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 201998 | root | localhost | zabbix | Query   | 55630 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202005 | root | localhost | zabbix | Query   | 55620 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202014 | root | localhost | zabbix | Query   | 55610 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202015 | root | localhost | zabbix | Query   | 55600 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202016 | root | localhost | zabbix | Query   | 55590 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202017 | root | localhost | zabbix | Query   | 55580 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202018 | root | localhost | zabbix | Query   | 55570 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202025 | root | localhost | zabbix | Query   | 55560 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202034 | root | localhost | zabbix | Query   | 55550 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202035 | root | localhost | zabbix | Query   | 55540 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202036 | root | localhost | zabbix | Query   | 55530 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202037 | root | localhost | zabbix | Query   | 55520 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202038 | root | localhost | zabbix | Query   | 55510 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202045 | root | localhost | zabbix | Query   | 55500 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202054 | root | localhost | zabbix | Query   | 55490 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202055 | root | localhost | zabbix | Query   | 55480 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202056 | root | localhost | zabbix | Query   | 55470 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202057 | root | localhost | zabbix | Query   | 55460 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202058 | root | localhost | zabbix | Query   | 55450 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202065 | root | localhost | zabbix | Query   | 55440 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202076 | root | localhost | zabbix | Query   | 55430 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202077 | root | localhost | zabbix | Query   | 55420 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202078 | root | localhost | zabbix | Query   | 55410 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202079 | root | localhost | zabbix | Query   | 55400 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202080 | root | localhost | zabbix | Query   | 55390 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202084 | root | localhost | zabbix | Query   | 55380 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202097 | root | localhost | zabbix | Query   | 55370 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202098 | root | localhost | zabbix | Query   | 55360 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202099 | root | localhost | zabbix | Query   | 55350 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202100 | root | localhost | zabbix | Query   | 55340 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202101 | root | localhost | zabbix | Query   | 55330 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202108 | root | localhost | zabbix | Query   | 55320 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202117 | root | localhost | zabbix | Query   | 55310 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202118 | root | localhost | zabbix | Query   | 55300 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202119 | root | localhost | zabbix | Query   | 55290 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202120 | root | localhost | zabbix | Query   | 55279 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202121 | root | localhost | zabbix | Query   | 55270 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202127 | root | localhost | zabbix | Query   | 55260 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202137 | root | localhost | zabbix | Query   | 55250 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202140 | root | localhost | zabbix | Query   | 55140 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202143 | root | localhost | zabbix | Query   | 55130 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202152 | root | localhost | zabbix | Query   | 55120 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202154 | root | localhost | zabbix | Query   | 55110 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202155 | root | localhost | zabbix | Query   | 55100 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202156 | root | localhost | zabbix | Query   | 55090 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202157 | root | localhost | zabbix | Query   | 55080 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202158 | root | localhost | zabbix | Query   | 55070 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202159 | root | localhost | zabbix | Query   | 55060 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202160 | root | localhost | zabbix | Query   | 55050 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202161 | root | localhost | zabbix | Query   | 55040 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202162 | root | localhost | zabbix | Query   | 55030 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202163 | root | localhost | zabbix | Query   | 55020 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202164 | root | localhost | zabbix | Query   | 55010 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202165 | root | localhost | zabbix | Query   | 55000 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202166 | root | localhost | zabbix | Query   | 54990 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202167 | root | localhost | zabbix | Query   | 54980 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202168 | root | localhost | zabbix | Query   | 54970 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202169 | root | localhost | zabbix | Query   | 54960 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202170 | root | localhost | zabbix | Query   | 54950 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202171 | root | localhost | zabbix | Query   | 54940 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202172 | root | localhost | zabbix | Query   | 54930 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202173 | root | localhost | zabbix | Query   | 54920 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202174 | root | localhost | zabbix | Query   | 54910 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202175 | root | localhost | zabbix | Query   | 54900 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202176 | root | localhost | zabbix | Query   | 54890 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202177 | root | localhost | zabbix | Query   | 54880 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202178 | root | localhost | zabbix | Query   | 54870 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202179 | root | localhost | zabbix | Query   | 54860 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202180 | root | localhost | zabbix | Query   | 54850 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202181 | root | localhost | zabbix | Query   | 54840 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202182 | root | localhost | zabbix | Query   | 54830 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202183 | root | localhost | zabbix | Query   | 54820 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202184 | root | localhost | zabbix | Query   | 54810 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202185 | root | localhost | zabbix | Query   | 54799 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202186 | root | localhost | zabbix | Query   | 54790 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202187 | root | localhost | zabbix | Query   | 54780 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202188 | root | localhost | zabbix | Query   | 54770 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202189 | root | localhost | zabbix | Query   | 54760 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202190 | root | localhost | zabbix | Query   | 54750 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202191 | root | localhost | zabbix | Query   | 54740 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202192 | root | localhost | zabbix | Query   | 54730 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202193 | root | localhost | zabbix | Query   | 54720 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202194 | root | localhost | zabbix | Query   | 54710 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202195 | root | localhost | zabbix | Query   | 54700 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202196 | root | localhost | zabbix | Query   | 54690 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202197 | root | localhost | zabbix | Query   | 54680 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202198 | root | localhost | zabbix | Query   | 54670 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202199 | root | localhost | zabbix | Query   | 54660 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202200 | root | localhost | zabbix | Query   | 54650 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202201 | root | localhost | zabbix | Query   | 54640 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202202 | root | localhost | zabbix | Query   | 54630 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202203 | root | localhost | zabbix | Query   | 54620 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202204 | root | localhost | zabbix | Query   | 54610 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202205 | root | localhost | zabbix | Query   | 54599 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202206 | root | localhost | zabbix | Query   | 54590 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202207 | root | localhost | zabbix | Query   | 54580 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202208 | root | localhost | zabbix | Query   | 54570 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202209 | root | localhost | zabbix | Query   | 54560 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202210 | root | localhost | zabbix | Query   | 54550 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202211 | root | localhost | zabbix | Query   | 54540 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202212 | root | localhost | zabbix | Query   | 54530 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202219 | root | localhost | zabbix | Query   | 54520 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202220 | root | localhost | zabbix | Query   | 54510 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202221 | root | localhost | zabbix | Query   | 54500 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202222 | root | localhost | zabbix | Query   | 54490 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202223 | root | localhost | zabbix | Query   | 54480 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202224 | root | localhost | zabbix | Query   | 54469 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202225 | root | localhost | zabbix | Query   | 54460 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202226 | root | localhost | zabbix | Query   | 54450 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202227 | root | localhost | zabbix | Query   | 54440 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202228 | root | localhost | zabbix | Query   | 54430 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202229 | root | localhost | zabbix | Query   | 54420 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202230 | root | localhost | zabbix | Query   | 54410 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202231 | root | localhost | zabbix | Query   | 54400 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202232 | root | localhost | zabbix | Query   | 54390 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202233 | root | localhost | zabbix | Query   | 54380 | Sending data | SELECT  DISTINCT  t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts h |
| 202237 | root | localhost | NULL   | Query   |     0 | NULL         | show processlist                                                                                     |
+--------+------+-----------+--------+---------+-------+--------------+------------------------------------------------------------------------------------------------------+
151 rows in set (0.00 sec)

And yes, im using innodb:

mysql> show table status from zabbix;
+-----------------------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name                  | Engine | Version | Row_format | Rows      | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free   | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+-----------------------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| acknowledges          | InnoDB |      10 | Compact    |    124383 |             92 |    11550720 |               0 |     11124736 | 36592156672 |           NULL | 2015-07-06 14:58:42 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| actions               | InnoDB |      10 | Compact    |        14 |           1170 |       16384 |               0 |        16384 | 36592156672 |           NULL | 2015-07-06 14:58:42 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
| alerts                | InnoDB |      10 | Compact    |     26838 |            508 |    13647872 |               0 |      8273920 | 36592156672 |           NULL | 2015-07-06 14:58:42 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |

[kloczek]

Could you please show EXPLAIN output on queried tables?
Which one version of MySQL it is?

[kloczek]

And otput of "SHOW ENGINE INNODB STATUS" please as well

[kloczek]

Just realized that all those queries are in "Sending data" status. AFIK it is the phase
of query execution where results are sent back to the client.
Please check "dmesg" output, check status of all connections, check mysqld error log as well. All this on both sides.
Quite possible that it is some network issue.

[sg42]

I just realized that I've not answered Your last questions jet.
I have increased the innodb_buffer_pool_size from 512MB to 2GB and now the problem seems to be solved. The server runs the last seven days without problems.

Perhaps I can now get on with my actual task to migrate the server.

Thanks again for Your help.

[kloczek]

I have increased the innodb_buffer_pool_size from 512MB to 2GB and now the problem seems to be solved. The server runs the last seven days without problems.

Did you found some error messages in mysqld logs?
I do not understand how increase memory pool may solve something here?

[sg42]

Did you found some error messages in mysqld logs?
I do not understand how increase memory pool may solve something here?

No, there weren't any erros in the logs.
I have a bunch of slow queries but the log for these is not active.
After the increase the slow queries drop from about 20k to seven.
Now it's near 450 according to zabbix.

[sg42]

Well perhaps I was a little early to say it's done ;-(
Today we have three times the same effect: processlist grows and never finished the queries.
So, back to debugging ...