MySQL consumes too much disk space if Zabbix plugin is enabled

[Alexander Rubtsov]

MOS: 7.0
Plugins: Zabbix

Database size is quickly growing on environments with Zabbix

1) The most of disk space consumes Zabbix database
Here is a sample:
http://paste.openstack.org/show/ZOplF9Eu7GwYFc776Hlz/

2) MySQL stores a lot of binary logs
Here is a sample (from another environment):
http://paste.openstack.org/show/XPVrkj8fqSZsvFOGqXpu/

These 2 factors cause overfilling MySQL partition
It makes MySQL inoperable that in turn leads to inoperable OpenStack environment

[Alexander Rubtsov (arubtsov)]

sla2 for 7.0-updates

[Olivier Bourdon (olivierbourdon38)]

This will be addressed in the forthcoming Zabbix 2.6 release but may be not as a code fix but also as a deployment option

We might also want to revisit all the default parameters for retention as well as all metrics gathered by Zabbix as they are quite numerous and most of them do not even have alarms associated (therefore limiting their potential interest)

This will of course be done in concordance with field/customers inputs

[Olivier Bourdon (olivierbourdon38)]

Changing the priority of this issue according to latest discussions.
However is it possible to get the following command output from the system where samples were taken initially please ?

mysql -e "SELECT table_name AS 'Table', table_rows AS '#Rows', ROUND(data_length/1024/104,2) AS dataMB, ROUND(index_length/1024/104,2) AS idxMB, ROUND(((data_length + index_length)/1024/1024),2) AS 'SizeMB' FROM information_schema.TABL
ES WHERE table_schema='zabbix' AND table_rows>1000 ORDER BY SizeMB"

Right now the HA proxy data with its 147 items x 3 (frontend, backend, server) x # of exposed entries and this again crossed with a sampling each and every 30 seconds and retention of 1 year with 1+ year trends is the one which is causing this

We will produce a new version of the Zabbix plugin package (2.5.0) with adjusted values so that this gets fixed

[Dmitry Kalashnik (dkalashnik)]

After ~12h

+--------------------+---------+---------+---------+--------+
| Table | #Rows | dataMB | idxMB | SizeMB |
+--------------------+---------+---------+---------+--------+
| graphs_items | 1306 | 1.23 | 1.23 | 0.25 |
| events | 4317 | 2.77 | 4.77 | 0.77 |
| trends | 12780 | 14.92 | 0.00 | 1.52 |
| item_discovery | 14497 | 14.92 | 7.85 | 2.31 |
| items_applications | 15336 | 14.92 | 18.15 | 3.36 |
| items | 15227 | 54.31 | 26.15 | 8.17 |
| trends_uint | 109140 | 113.38 | 0.00 | 11.52 |
| history_text | 99637 | 44.46 | 98.77 | 14.55 |
| history | 394599 | 182.62 | 182.62 | 37.09 |
| history_str | 432500 | 182.62 | 192.62 | 38.11 |
| history_uint | 3330001 | 1592.00 | 1465.54 | 310.53 |
+--------------------+---------+---------+---------+--------+

[Piotr Bogdan (pbogdan-x)]

The mysql output requested by Olivier Bourdon:

# mysql --execute="SELECT table_name AS 'Table', table_rows AS '#Rows', ROUND(data_length/1024/104,2) AS dataMB, ROUND(index_length/1024/104,2) AS idxMB, ROUND(((data_length + index_length)/1024/1024),2) AS 'SizeMB' FROM information_schema.TABLES WHERE table_schema='zabbix' AND table_rows>1000 ORDER BY SizeMB;"
+--------------------+----------+----------+----------+---------+
| Table | #Rows | dataMB | idxMB | SizeMB |
+--------------------+----------+----------+----------+---------+
| sessions | 1010 | 1.38 | 0.92 | 0.23 |
| graphs_items | 1591 | 1.38 | 1.08 | 0.25 |
| item_discovery | 14766 | 14.92 | 9.23 | 2.45 |
| items_applications | 15438 | 14.15 | 16.92 | 3.16 |
| events | 12337 | 14.92 | 19.54 | 3.50 |
| items | 17594 | 64.00 | 26.77 | 9.22 |
| trends | 199658 | 172.77 | 0.00 | 17.55 |
| trends_uint | 1525605 | 1444.92 | 0.00 | 146.75 |
| history_text | 1702141 | 764.15 | 957.85 | 174.89 |
| history_str | 6785654 | 2833.69 | 1910.15 | 481.80 |
| history | 8460741 | 3898.15 | 2313.85 | 630.91 |
| history_uint | 76951829 | 35859.69 | 20519.38 | 5726.00 |
+--------------------+----------+----------+----------+---------+

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.openstack.org/272441

[Olivier Bourdon (olivierbourdon38)]

FIx has been commited for review

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/272441
Committed: https://git.openstack.org/cgit/openstack/fuel-plugin-external-zabbix/commit/?id=f9fe212fcee934553a37fc1a310dc1a01806a5f3
Submitter: Jenkins
Branch: master

commit f9fe212fcee934553a37fc1a310dc1a01806a5f3
Author: Olivier Bourdon <email address hidden>
Date: Tue Jan 26 10:31:10 2016 +0100

    MySQL consumes too much disk space if Zabbix plugin is enabled

    Reduce drastically the collected set of HAProxy metrics

    This patch remove useless metrics and allow to reduce the corresponding
    HAProxy MySQL data volume by at least 50%.

    Change-Id: I5327a1a37adb2d5e6d3b3d2f5be9737b87663d8a
    Closes-Bug: #1531834

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: 2.5
Review: https://review.openstack.org/272530

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/272530
Committed: https://git.openstack.org/cgit/openstack/fuel-plugin-external-zabbix/commit/?id=2e15045c9c7de15f0eeb5b88876cd8b06b3c84a0
Submitter: Jenkins
Branch: 2.5

commit 2e15045c9c7de15f0eeb5b88876cd8b06b3c84a0
Author: Olivier Bourdon <email address hidden>
Date: Tue Jan 26 10:31:10 2016 +0100

    MySQL consumes too much disk space if Zabbix plugin is enabled

    Reduce drastically the collected set of HAProxy metrics

    This patch remove useless metrics and allow to reduce the corresponding
    HAProxy MySQL data volume by at least 50%.

    Change-Id: I5327a1a37adb2d5e6d3b3d2f5be9737b87663d8a
    Closes-Bug: #1531834
    (cherry picked from commit f9fe212fcee934553a37fc1a310dc1a01806a5f3)

[Anton Matveev (amatveev)]

Changes were required to be implemented only in Zabbix plugin

Fuel related changes tracked under https://bugs.launchpad.net/fuel/+bug/1533805 and were merged already

[Roman Rufanov (rrufanov)]

Please include fix in Maintenance Updates (MUs) so we can rollout fix to existing customers so they no longer run into that.