zabbix proxy mysql db

[royalliege]

Hello,

I have installed a zabbix-proxy appliance (Ubuntu (Zabbix) 4.0.5) to cover the office site; this proxy seem to have database with history table; don't know how to delete unnecessary records and solve this problem. COuld you assist me?

[SIZE=12px][FONT=courier new]MariaDB [zabbix]> SELECT TABLE_SCHEMA, TABLE_NAME,(INDEX_LENGTH+DATA_LENGTH)/(1024*1024) AS SIZE_MB, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN ("m
+--------------+--------------------+------------+------------+
| TABLE_SCHEMA | TABLE_NAME         | SIZE_MB    | TABLE_ROWS |
+--------------+--------------------+------------+------------+
| zabbix       | proxy_history      | 27826.5000 |   52051231 |
| zabbix       | items              |     6.1250 |      11213 |
| zabbix       | alerts             |     0.1406 |          0 |
| zabbix       | proxy_autoreg_host |     0.1250 |        679 |
| zabbix       | hosts              |     0.1094 |         47 |
| zabbix       | widget_field       |     0.1094 |          0 |
| zabbix       | sysmaps_elements   |     0.0938 |          0 |
| zabbix       | httptest           |     0.0781 |          0 |
| zabbix       | graphs             |     0.0781 |          0 |
| zabbix       | sysmaps            |     0.0781 |          0 |
+--------------+--------------------+------------+------------+
10 rows in set (0.16 sec)[/FONT][/SIZE]

The housekeeper process seem to delete 6-7k records daily but it doesn't seem enough. And it seem to run for hours to delete such record.

[SIZE=12px][FONT=courier new]  2311:20190608:015955.010 housekeeper [deleted 1394 records in 9759.727025 sec, idle for 1 hour(s)]
  2311:20190608:054207.190 housekeeper [deleted 1406 records in 9731.732996 sec, idle for 1 hour(s)]
  2311:20190608:092447.432 housekeeper [deleted 1341 records in 9759.827711 sec, idle for 1 hour(s)]
  2311:20190608:130835.527 housekeeper [deleted 1289 records in 9827.690288 sec, idle for 1 hour(s)]
  2311:20190608:165157.807 housekeeper [deleted 1289 records in 9801.816238 sec, idle for 1 hour(s)]
  2311:20190608:203326.335 housekeeper [deleted 1289 records in 9688.106500 sec, idle for 1 hour(s)]
  2311:20190609:001715.711 housekeeper [deleted 1289 records in 9828.918889 sec, idle for 1 hour(s)]
  2311:20190609:040056.861 housekeeper [deleted 1290 records in 9820.720547 sec, idle for 1 hour(s)]
  2311:20190609:074336.917 housekeeper [deleted 1289 records in 9759.620598 sec, idle for 1 hour(s)]
  2311:20190609:112655.308 housekeeper [deleted 1290 records in 9797.926156 sec, idle for 1 hour(s)]
  2311:20190609:150955.894 housekeeper [deleted 1289 records in 9780.141759 sec, idle for 1 hour(s)]
  2311:20190609:185140.002 housekeeper [deleted 1289 records in 9703.672561 sec, idle for 1 hour(s)]
  2311:20190609:223448.291 housekeeper [deleted 1289 records in 9787.850875 sec, idle for 1 hour(s)]
  2311:20190610:021943.594 housekeeper [deleted 1290 records in 9894.855478 sec, idle for 1 hour(s)]
  2311:20190610:060300.553 housekeeper [deleted 1290 records in 9796.468281 sec, idle for 1 hour(s)][/FONT][/SIZE]

Thanks,

[royalliege]

So, I have added buffer pool size for the issue (appliance didn't have such setting which I think default size is too low); and housekeeper worked like magic.

Yet, df -h output shows that it holds even higher amount than before, I will ask around mysql forums to find the problem of disk space. If you have a solution for that (what mysqld do after housekeeper deletes the records) please share with me.

# grep -i innodb /etc/mysql/mariadb.conf.d/50-server.cnf 
innodb_buffer_pool_size=1G

 [SIZE=12px][FONT=courier new]36926:20190610:183207.247 housekeeper [deleted 210920076 records in 10131.970016 sec, idle for 1 hour(s)]
 36926:20190610:193539.881 housekeeper [deleted 933386 records in 212.189556 sec, idle for 1 hour(s)]
 36926:20190610:203549.239 housekeeper [deleted 432230 records in 8.988758 sec, idle for 1 hour(s)]
 36926:20190610:213551.508 housekeeper [deleted 252112 records in 1.869515 sec, idle for 1 hour(s)]
 36926:20190610:223554.027 housekeeper [deleted 252155 records in 2.128232 sec, idle for 1 hour(s)][/FONT][/SIZE]

And the database rows and size_mb come to a fair size:

[SIZE=12px][FONT=courier new]MariaDB [zabbix]> SELECT TABLE_SCHEMA, TABLE_NAME,(INDEX_LENGTH+DATA_LENGTH)/(1024*1024) AS SIZE_MB, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN ("mysql", "information_schema") ORDER BY SIZE_MB DESC LIMIT 10;
+--------------+------------------+---------+------------+
| TABLE_SCHEMA | TABLE_NAME       | SIZE_MB | TABLE_ROWS |
+--------------+------------------+---------+------------+
| zabbix       | items            |  6.2344 |      11868 |
| zabbix       | proxy_history    |  5.0313 |      30199 |
| zabbix       | alerts           |  0.1406 |          0 |
| zabbix       | hosts            |  0.1094 |         47 |
| zabbix       | widget_field     |  0.1094 |          0 |
| zabbix       | sysmaps_elements |  0.0938 |          0 |
| zabbix       | httptest         |  0.0781 |          0 |
| zabbix       | graphs           |  0.0781 |          0 |
| zabbix       | sysmaps          |  0.0781 |          0 |
| zabbix       | scripts          |  0.0625 |          0 |
+--------------+------------------+---------+------------+
10 rows in set (0.01 sec)[/FONT][/SIZE]

[zux]

That's how DB's work. You should use optimize to clear up the space on disk:

https://mariadb.com/kb/en/library/optimize-table/

Or just truncate the table.

[royalliege]

I did optimize table later on, thanks