-
Type:
Incident report
-
Resolution: Duplicate
-
Priority:
Major
-
None
-
Affects Version/s: 1.8.14
-
Component/s: Frontend (F), Installation (I), Server (S)
-
Environment:
Number of hosts (monitored/not monitored/templates) 512 415 / 2 / 95
Number of items (monitored/disabled/not supported) 27126 21352 / 5305 / 469
Number of triggers (enabled/disabled)[problem/unknown/ok] 4394 4249 / 145 [3 / 517 / 3729]
Number of users (online) 75 2
Required server performance, new values per second 93.71 -
Database MySQL 5.5
OS Ubuntu 11.10
{code}
mysql> SELECT concat(table_schema,'.',table_name) Tablename, concat(round(table_rows/1000000,2),'M') "Rows", concat(round(data_length/(1024*1024*1024),2),'G') "Data", concat(round(index_length/(1024*1024*1024),2),'G') "Indexes", concat(round((data_length+index_length)/(1024*1024*1024),2),'G') "Table usage", round(index_length/data_length,2) "Index Fragmentation", concat(round(data_free/(1024*1024*1024),2),'G') "Free Space", concat(round((data_free+data_length+index_length)/(1024*1024*1024),2),'G') "Size on Disk", ENGINE "DB Engine", TABLE_COMMENT "Comment" FROM information_schema.TABLES ORDER BY data_free+data_length+index_length DESC LIMIT 50;
+-----------------------------+---------+--------+---------+-------------+---------------------+------------+--------------+-----------+---------------+
| Tablename | Rows | Data | Indexes | Table usage | Index Fragmentation | Free Space | Size on Disk | DB Engine | Comment |
+-----------------------------+---------+--------+---------+-------------+---------------------+------------+--------------+-----------+---------------+
| zabbix18.history_uint | 175.61M | 11.18G | 5.09G | 16.27G | 0.46 | 0.98G | 17.25G | InnoDB | |
| zabbix18.history | 52.34M | 4.25G | 1.84G | 6.09G | 0.43 | 0.24G | 6.33G | InnoDB | |
| zabbix18.trends_uint | 37.70M | 2.94G | 0.00G | 2.94G | 0.00 | 0.29G | 3.23G | InnoDB | |
| zabbix18.trends | 11.15M | 0.84G | 0.00G | 0.84G | 0.00 | 0.01G | 0.85G | InnoDB | |
| zabbix18.node_cksum | 0.04M | 0.03G | 0.00G | 0.04G | 0.10 | 0.46G | 0.49G | InnoDB | |
| zabbix18.history_text | 1.85M | 0.13G | 0.05G | 0.18G | 0.39 | 0.00G | 0.19G | InnoDB | |
| zabbix18.history_str | 0.66M | 0.05G | 0.02G | 0.08G | 0.41 | 0.09G | 0.17G | InnoDB | |
| zabbix18.events | 1.25M | 0.07G | 0.08G | 0.15G | 1.08 | 0.01G | 0.16G | InnoDB | |
| zabbix18.alerts | 0.15M | 0.05G | 0.03G | 0.08G | 0.52 | 0.02G | 0.10G | InnoDB | |
| zabbix18.history_log | 0.56M | 0.04G | 0.02G | 0.06G | 0.43 | 0.02G | 0.07G | InnoDB | |
| zabbix18.auditlog | 0.23M | 0.03G | 0.01G | 0.04G | 0.47 | 0.00G | 0.04G | InnoDB | |
| zabbix18.items | 0.03M | 0.01G | 0.01G | 0.02G | 0.77 | 0.00G | 0.02G | InnoDB | |
| zabbix18.auditlog_details | 0.10M | 0.01G | 0.00G | 0.01G | 0.26 | 0.00G | 0.01G | InnoDB | |
| zabbix18.items_applications | 0.03M | 0.00G | 0.00G | 0.01G | 1.20 | 0.00G | 0.01G | InnoDB | |
| zabbix18.triggers | 0.01M | 0.00G | 0.00G | 0.00G | 0.14 | 0.00G | 0.01G | InnoDB | |
| zabbix18.acknowledges | 0.01M | 0.00G | 0.00G | 0.00G | 0.47 | 0.00G | 0.01G | InnoDB | |
| zabbix18.functions | 0.01M | 0.00G | 0.00G | 0.00G | 0.40 | 0.00G | 0.01G | InnoDB | |
| zabbix18.graphs_items | 0.01M | 0.00G | 0.00G | 0.00G | 0.12 | 0.00G | 0.01G | InnoDB | |
| zabbix18.profiles | 0.00M | 0.00G | 0.00G | 0.00G | 1.04 | 0.00G | 0.00G | InnoDB | |
{code}Number of hosts (monitored/not monitored/templates) 512 415 / 2 / 95 Number of items (monitored/disabled/not supported) 27126 21352 / 5305 / 469 Number of triggers (enabled/disabled)[problem/unknown/ok] 4394 4249 / 145 [3 / 517 / 3729] Number of users (online) 75 2 Required server performance, new values per second 93.71 - Database MySQL 5.5 OS Ubuntu 11.10 {code} mysql> SELECT concat(table_schema,'.',table_name) Tablename, concat(round(table_rows/1000000,2),'M') "Rows", concat(round(data_length/(1024*1024*1024),2),'G') "Data", concat(round(index_length/(1024*1024*1024),2),'G') "Indexes", concat(round((data_length+index_length)/(1024*1024*1024),2),'G') "Table usage", round(index_length/data_length,2) "Index Fragmentation", concat(round(data_free/(1024*1024*1024),2),'G') "Free Space", concat(round((data_free+data_length+index_length)/(1024*1024*1024),2),'G') "Size on Disk", ENGINE "DB Engine", TABLE_COMMENT "Comment" FROM information_schema.TABLES ORDER BY data_free+data_length+index_length DESC LIMIT 50; +-----------------------------+---------+--------+---------+-------------+---------------------+------------+--------------+-----------+---------------+ | Tablename | Rows | Data | Indexes | Table usage | Index Fragmentation | Free Space | Size on Disk | DB Engine | Comment | +-----------------------------+---------+--------+---------+-------------+---------------------+------------+--------------+-----------+---------------+ | zabbix18.history_uint | 175.61M | 11.18G | 5.09G | 16.27G | 0.46 | 0.98G | 17.25G | InnoDB | | | zabbix18.history | 52.34M | 4.25G | 1.84G | 6.09G | 0.43 | 0.24G | 6.33G | InnoDB | | | zabbix18.trends_uint | 37.70M | 2.94G | 0.00G | 2.94G | 0.00 | 0.29G | 3.23G | InnoDB | | | zabbix18.trends | 11.15M | 0.84G | 0.00G | 0.84G | 0.00 | 0.01G | 0.85G | InnoDB | | | zabbix18.node_cksum | 0.04M | 0.03G | 0.00G | 0.04G | 0.10 | 0.46G | 0.49G | InnoDB | | | zabbix18.history_text | 1.85M | 0.13G | 0.05G | 0.18G | 0.39 | 0.00G | 0.19G | InnoDB | | | zabbix18.history_str | 0.66M | 0.05G | 0.02G | 0.08G | 0.41 | 0.09G | 0.17G | InnoDB | | | zabbix18.events | 1.25M | 0.07G | 0.08G | 0.15G | 1.08 | 0.01G | 0.16G | InnoDB | | | zabbix18.alerts | 0.15M | 0.05G | 0.03G | 0.08G | 0.52 | 0.02G | 0.10G | InnoDB | | | zabbix18.history_log | 0.56M | 0.04G | 0.02G | 0.06G | 0.43 | 0.02G | 0.07G | InnoDB | | | zabbix18.auditlog | 0.23M | 0.03G | 0.01G | 0.04G | 0.47 | 0.00G | 0.04G | InnoDB | | | zabbix18.items | 0.03M | 0.01G | 0.01G | 0.02G | 0.77 | 0.00G | 0.02G | InnoDB | | | zabbix18.auditlog_details | 0.10M | 0.01G | 0.00G | 0.01G | 0.26 | 0.00G | 0.01G | InnoDB | | | zabbix18.items_applications | 0.03M | 0.00G | 0.00G | 0.01G | 1.20 | 0.00G | 0.01G | InnoDB | | | zabbix18.triggers | 0.01M | 0.00G | 0.00G | 0.00G | 0.14 | 0.00G | 0.01G | InnoDB | | | zabbix18.acknowledges | 0.01M | 0.00G | 0.00G | 0.00G | 0.47 | 0.00G | 0.01G | InnoDB | | | zabbix18.functions | 0.01M | 0.00G | 0.00G | 0.00G | 0.40 | 0.00G | 0.01G | InnoDB | | | zabbix18.graphs_items | 0.01M | 0.00G | 0.00G | 0.00G | 0.12 | 0.00G | 0.01G | InnoDB | | | zabbix18.profiles | 0.00M | 0.00G | 0.00G | 0.00G | 1.04 | 0.00G | 0.00G | InnoDB | | {code}
The dropdown menus in "Monitoring" => "Graphs" were pretty slow.
I executed "show full processlist" while the menu loads an got the following statement:
SELECT DISTINCT g.* FROM groups g,hosts_groups hg,hosts h WHERE ((g.groupid BETWEEN 100000000000000 AND 199999999999999) OR (g.groupid BETWEEN 200000000000000 AND 299999999999999)) AND hg.groupid=g.groupid AND h.hostid=hg.hostid AND h.status=0 AND EXISTS( SELECT DISTINCT i.itemid FROM items i, graphs_items gi WHERE i.hostid=hg.hostid AND i.itemid=gi.itemid);
I added a index:
CREATE INDEX graphs_items_1 ON graphs_items(itemid) USING btree;
After creating a index execution time was reduced from 5 seconds to 0.03 seconds.
This problem is also described in the following closed bug https://support.zabbix.com/browse/ZBX-569.
- duplicates
-
-
- Closed
-