0

Hello I have installed a Zabbix Server 3 months ago with 32GB space disk, I have 200 hosts supervised on this server.

And I got this message from the "system information" part :

  Error in query:

SELECT table_name AS table_name FROM information_schema.columns WHERE table_schema='zabbix' 
AND (table_name IN 

('role','users','maintenances','hosts','hstgrp','group_prototype','group_discovery','drules','dchecks',
'httptest','httpstep','interface','valuemap','items','httpstepitem','httptestitem','media_type','media_type_param','media_type_message','usrgrp','users_groups','scripts','script_param','actions','operations','opmessage','opmessage_grp','opmessage_usr','opcommand','opcommand_hst','opcommand_grp','opgroup','optemplate','opconditions','conditions','config','triggers','trigger_depends','functions','graphs','graphs_items','graph_theme','globalmacro','hostmacro','hosts_groups','hosts_templates','valuemap_mapping','media','rights','services','services_links','icon_map','icon_mapping','sysmaps','sysmaps_elements','sysmaps_links','sysmaps_link_triggers','sysmap_element_url','sysmap_url','sysmap_user','sysmap_usrgrp','maintenances_hosts','maintenances_groups','timeperiods','maintenances_windows','regexps','expressions','ids','alerts','history','history_uint','history_str','history_log','history_text','proxy_history','proxy_dhistory','events','trends','trends_uint','acknowledges','auditlog','service_alarms','autoreg_host','proxy_autoreg_host','dhosts','dservices','escalations','globalvars','graph_discovery','host_inventory','housekeeper','images','item_discovery','host_discovery','interface_discovery','profiles','sessions','trigger_discovery','item_condition','item_rtdata','opinventory','trigger_tag','event_tag','problem','problem_tag','tag_filter','event_recovery','correlation','corr_condition','corr_condition_tag','corr_condition_group','corr_condition_tagpair','corr_condition_tagvalue','corr_operation','task','task_close_problem','item_preproc','task_remote_command','task_remote_command_result','task_data','task_result','task_acknowledge','sysmap_shape','sysmap_element_trigger','httptest_field','httpstep_field','dashboard','dashboard_user','dashboard_usrgrp','dashboard_page','widget','widget_field','task_check_now','event_suppress','maintenance_tag','lld_macro_path','host_tag','config_autoreg_tls','module','interface_snmp','lld_override','lld_override_condition','lld_override_operation','lld_override_opstatus','lld_override_opdiscover','lld_override_opperiod','lld_override_ophistory','lld_override_optrends','lld_override_opseverity','lld_override_optag','lld_override_optemplate','lld_override_opinventory','trigger_queue','item_parameter','role_rule','token','item_tag','httptest_tag','sysmaps_element_tag','report','report_param','report_user','report_usrgrp','service_problem_tag','service_problem','service_tag','service_status_rule','ha_node','sla','sla_schedule','sla_excluded_downtime',
'sla_service_tag','dbversion'))

AND (data_type IN ('text','varchar','longtext')) 

AND ((UPPER(character_set_name)

NOT IN ('UTF8','UTF8MB3','UTF8MB4')) 

OR (collation_name 

NOT IN ('utf8_bin','utf8mb3_bin','utf8mb4_bin')))

Disk full (/tmp/#sql-temptable-1df-1eb4e-5558.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")

My disk is 100% used :

enter image description here

I got these files which are really huge :

enter image description here

auditlog.ibd 21.4GB

history_uint.ibd 3.25GB

trends_uint.ibd 1.99GB

I know that I should have create this server with more drive space but I followed the zabbix recommendation at the beginning.

What should I do with those files? Do i have to deactivate the partition to be able to enlarge it?

Im not really used to database things, I read that idb files were really important and that just removing/recreating these files was a really bad idea.

Thank you in advance for your answers :)

6
  • Good move not touching the ibd files. Is there an additional space on /dev/sda in a second partition? Is this a VM? What filesystem is on /?
    – danblack
    Jul 12, 2022 at 11:13
  • There is just SDA1 32GB et SDA2 4GB, yes it's a VM, debian one with Ext4 as file system if that's what you wanted to know ^^
    – ramaccess
    Jul 12, 2022 at 11:20
  • The upgrade notes seem to indicate that the audit log is truncated on update and is a record of changes. This looks like it can be deleted (from zabbix or the db via truncate table auditlog). Read manuals a bit more or wait for someone experienced in zabbix to check.
    – danblack
    Jul 12, 2022 at 11:27
  • Thanks a lot, I'll wait a little to have many as possible informations :)
    – ramaccess
    Jul 12, 2022 at 11:40
  • (a AND b AND c OR d) means ``((a AND b AND c) OR d); I hink you wanted (a AND b AND (c OR d))`
    – Rick James
    Jul 12, 2022 at 15:05

1 Answer 1

1

I read that idb files were really important and that just removing/recreating these files was a really bad idea.

If you move them with care so that they appear at the same paths, you should be all good:

  1. Take a full backup, or verify your current backup if you have automated backups properly configured.
  2. Add a new virtual drive to your VM
  3. In the VM partition it and create a filesystem on it, and mount at /var/lib/zabbix-tmp (with something like mkdir /var/lib/zabbix-tmp; mount /dev/sdb1 /var/lib/zabbix-tmp)
  4. Stop mysql. Be sure it is stopped and nothing will restart it until the following steps are complete.
  5. cp -a /var/lib/zabbix/* /var/lib/zabbix-tmp/ or rsync --archive --progress /var/lib/zabbix/* /var/lib/zabbix-tmp/
  6. Verify to your satisfaction that the old and new files are exactly the same.
  7. rm -rv /var/lib/zabbix/*
  8. Unmount the new filesystem from the temporary directory and mount it at /var/lib/zabbix/
  9. Add it to /etc/fstab so it gets mounted properly on boot
  10. Restart mysql
  11. Test all is well.

The files are now on the new filesystem, with plenty of room to expand if you created them large enough, but appear at the same paths so mysql is non the wiser about the change.

If using dynamically sizing disks for your VM you might need to follow some procedure to reclaim the now free space in the host filesystem (sometimes zero-filling, cat /dev/zero > /zero.file; rm /zero.file, will do this but consult your virtualisation documentation for specific instructions).

2
  • Well finally I used danblack's technique, I had 0 bytes free on the partition so I wasn't able to execute any sql query on the database. I deleted a file in /var/cache/apt/archive to get more space And then Ive launched the truncate query for auditlog table, ive seen guys doing this also for other tables in zabbix database so maybe I will do the same with history_uint and trends_uinttables because they are quite bulky. I keep the snapshot in case something changes, and I have also changed the default settings of Zabbix Housekeeper (365days by default for the storage period) Thanks ! :)
    – ramaccess
    Jul 12, 2022 at 12:52
  • The other way to temporary "steal" space from full partitions is to temporary reduce the reserved space (defaults ~5%) (tune2fs). A monitoring and alerting tool like zabbix is good for giving notice if space gets tight :smile:.
    – danblack
    Jul 13, 2022 at 6:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.