Ad Widget

Collapse

zabbix proxy mysql db

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • royalliege
    Junior Member
    • Dec 2018
    • 6

    #1

    zabbix proxy mysql db

    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?

    Code:
    [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.
    Code:
    [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
    Junior Member
    • Dec 2018
    • 6

    #2
    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.

    Code:
    # grep -i innodb /etc/mysql/mariadb.conf.d/50-server.cnf 
    innodb_buffer_pool_size=1G
    Code:
     [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:
    Code:
    [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]

    Comment

    • zux
      Member
      • Sep 2018
      • 93

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


      Or just truncate the table.

      Comment


      • royalliege
        royalliege commented
        Editing a comment
        I did optimize table later on, thanks
    Working...