728x90
반응형
개요
MySQL을 사용하다 보면 ibdata1이 용량이 커져 있을 때가 있다.
비단 Zabbix를 사용 할 뿐만 아니라, MySQL을 사용하면 발생 할 수 있는 문제이다.
ibdata1에 대해 용량을 정리 해줘야 하는데 명령어 하나로 간단하게 정리 할 수가 없다.
데이터베이스를 Dump 후 Drop 하고 다시 Import 해줘야 한다.
1. DB 접속
mysql -uroot -p2. DB 확인
show databases;+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| zabbix |
+--------------------+3. 해당 DB의 테이블 사용용량 확인
select table_name, (data_length + index_length)/1024/1024 as total_mb, table_rows from information_sc
hema.tables where table_schema = 'zabbix';728x90
+----------------------------+--------------+------------+
| table_name | total_mb | table_rows |
+----------------------------+--------------+------------+
| acknowledges | 0.06250000 | 0 |
| actions | 0.04687500 | 8 |
| alerts | 0.17187500 | 45 |
| application_discovery | 0.04687500 | 72 |
| application_prototype | 0.07812500 | 351 |
| application_template | 0.04687500 | 353 |
| applications | 0.17187500 | 1529 |
| auditlog | 0.06250000 | 2 |
| auditlog_details | 0.03125000 | 0 |
| autoreg_host | 0.04687500 | 0 |
| conditions | 0.03125000 | 24 |
| config | 0.04687500 | 1 |
| config_autoreg_tls | 0.03125000 | 1 |
| corr_condition | 0.03125000 | 0 |
| corr_condition_group | 0.03125000 | 0 |
| corr_condition_tag | 0.01562500 | 0 |
| corr_condition_tagpair | 0.01562500 | 0 |
| corr_condition_tagvalue | 0.01562500 | 0 |
| corr_operation | 0.03125000 | 0 |
| correlation | 0.04687500 | 0 |
| dashboard | 0.03125000 | 2 |
| dashboard_user | 0.04687500 | 0 |
| dashboard_usrgrp | 0.04687500 | 1 |
| dbversion | 0.01562500 | 1 |
| dchecks | 0.03125000 | 1 |
| dhosts | 0.03125000 | 0 |
| drules | 0.04687500 | 1 |
| dservices | 0.04687500 | 0 |
| escalations | 0.06250000 | 0 |
| event_recovery | 0.04687500 | 6 |
| event_suppress | 0.06250000 | 0 |
| event_tag | 0.03125000 | 0 |
| events | 0.04687500 | 23 |
| expressions | 0.03125000 | 10 |
| functions | 0.87500000 | 6328 |
| globalmacro | 0.03125000 | 5 |
| globalvars | 0.01562500 | 1 |
| graph_discovery | 0.06250000 | 343 |
| graph_theme | 0.03125000 | 4 |
| graphs | 0.68750000 | 1846 |
| graphs_items | 0.71875000 | 3930 |
| group_discovery | 0.03125000 | 0 |
| group_prototype | 0.06250000 | 11 |
| history | 20.03125000 | 216292 |
| history_log | 0.03125000 | 0 |
| history_str | 1.03125000 | 427 |
| history_text | 1.06250000 | 679 |
| history_uint | 244.03125000 | 2100865 |
| host_discovery | 0.04687500 | 4 |
| host_inventory | 0.01562500 | 0 |
| host_tag | 0.03125000 | 0 |
| hostmacro | 0.42187500 | 2036 |
| hosts | 0.25000000 | 287 |
| hosts_groups | 0.04687500 | 258 |
| hosts_templates | 0.04687500 | 166 |
| housekeeper | 0.01562500 | 0 |
| hstgrp | 0.03125000 | 26 |
| httpstep | 0.03125000 | 0 |
| httpstep_field | 0.03125000 | 0 |
| httpstepitem | 0.04687500 | 0 |
| httptest | 0.07812500 | 0 |
| httptest_field | 0.03125000 | 0 |
| httptestitem | 0.04687500 | 0 |
| icon_map | 0.04687500 | 0 |
| icon_mapping | 0.04687500 | 0 |
| ids | 0.01562500 | 50 |
| images | 1.53125000 | 157 |
| interface | 0.04687500 | 15 |
| interface_discovery | 0.03125000 | 0 |
| interface_snmp | 0.01562500 | 8 |
| item_application_prototype | 0.35937500 | 2787 |
| item_condition | 0.20312500 | 1329 |
| item_discovery | 0.75000000 | 5365 |
| item_preproc | 1.89062500 | 8038 |
| item_rtdata | 0.15625000 | 2324 |
| items | 9.34375000 | 10853 |
| items_applications | 1.07812500 | 7690 |
| lld_macro_path | 0.03125000 | 91 |
| lld_override | 0.03125000 | 112 |
| lld_override_condition | 0.03125000 | 108 |
| lld_override_opdiscover | 0.01562500 | 115 |
| lld_override_operation | 0.03125000 | 115 |
| lld_override_ophistory | 0.01562500 | 0 |
| lld_override_opinventory | 0.01562500 | 0 |
| lld_override_opperiod | 0.01562500 | 0 |
| lld_override_opseverity | 0.01562500 | 0 |
| lld_override_opstatus | 0.01562500 | 115 |
| lld_override_optag | 0.03125000 | 0 |
| lld_override_optemplate | 0.04687500 | 0 |
| lld_override_optrends | 0.01562500 | 0 |
| maintenance_tag | 0.03125000 | 0 |
| maintenances | 0.04687500 | 0 |
| maintenances_groups | 0.04687500 | 0 |
| maintenances_hosts | 0.04687500 | 0 |
| maintenances_windows | 0.04687500 | 0 |
| mappings | 0.21875000 | 1934 |
| media | 0.04687500 | 1 |
| media_type | 0.39062500 | 33 |
| media_type_message | 0.09375000 | 135 |
| media_type_param | 0.07812500 | 505 |
| module | 0.01562500 | 0 |
| opcommand | 0.03125000 | 0 |
| opcommand_grp | 0.04687500 | 0 |
| opcommand_hst | 0.04687500 | 0 |
| opconditions | 0.03125000 | 0 |
| operations | 0.03125000 | 15 |
| opgroup | 0.04687500 | 0 |
| opinventory | 0.01562500 | 0 |
| opmessage | 0.03125000 | 14 |
| opmessage_grp | 0.04687500 | 7 |
| opmessage_usr | 0.04687500 | 0 |
| optemplate | 0.04687500 | 1 |
| problem | 0.06250000 | 17 |
| problem_tag | 0.03125000 | 0 |
| profiles | 0.12500000 | 456 |
| proxy_autoreg_host | 0.03125000 | 0 |
| proxy_dhistory | 0.04687500 | 0 |
| proxy_history | 0.03125000 | 0 |
| regexps | 0.03125000 | 5 |
| rights | 0.04687500 | 7 |
| screen_user | 0.04687500 | 0 |
| screen_usrgrp | 0.04687500 | 1 |
| screens | 0.04687500 | 75 |
| screens_items | 0.09375000 | 252 |
| scripts | 0.06250000 | 3 |
| service_alarms | 0.04687500 | 0 |
| services | 0.03125000 | 0 |
| services_links | 0.04687500 | 0 |
| services_times | 0.03125000 | 0 |
| sessions | 0.03125000 | 0 |
| slides | 0.04687500 | 0 |
| slideshow_user | 0.04687500 | 0 |
| slideshow_usrgrp | 0.04687500 | 0 |
| slideshows | 0.04687500 | 0 |
| sysmap_element_trigger | 0.04687500 | 0 |
| sysmap_element_url | 0.03125000 | 0 |
| sysmap_shape | 0.03125000 | 5 |
| sysmap_url | 0.03125000 | 0 |
| sysmap_user | 0.04687500 | 0 |
| sysmap_usrgrp | 0.04687500 | 0 |
| sysmaps | 0.07812500 | 1 |
| sysmaps_elements | 0.09375000 | 12 |
| sysmaps_link_triggers | 0.04687500 | 0 |
| sysmaps_links | 0.06250000 | 0 |
| tag_filter | 0.04687500 | 0 |
| task | 0.04687500 | 0 |
| task_acknowledge | 0.01562500 | 0 |
| task_check_now | 0.01562500 | 0 |
| task_close_problem | 0.01562500 | 0 |
| task_data | 0.01562500 | 0 |
| task_remote_command | 0.01562500 | 0 |
| task_remote_command_result | 0.01562500 | 0 |
| task_result | 0.03125000 | 0 |
| timeperiods | 0.01562500 | 0 |
| trends | 8.01562500 | 3638 |
| trends_uint | 60.01562500 | 32084 |
| trigger_depends | 0.21875000 | 1349 |
| trigger_discovery | 0.10937500 | 652 |
| trigger_tag | 0.03125000 | 2 |
| triggers | 2.89062500 | 4426 |
| users | 0.03125000 | 8 |
| users_groups | 0.04687500 | 9 |
| usrgrp | 0.03125000 | 7 |
| valuemaps | 0.03125000 | 253 |
| widget | 0.03125000 | 17 |
| widget_field | 0.10937500 | 152 |
+----------------------------+--------------+------------+한 번 정리 된 적이 있기 때문에 많지는 않다.
4. Zabbix DB 접근
MariaDB [(none)]> use zabbix;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A5. 덤프 용량을 줄이기 위해 테이블 truncate
MariaDB [zabbix]> truncate table history;
Query OK, 0 rows affected (0.01 sec)
MariaDB [zabbix]> optimize table history;
+----------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------------+----------+----------+-------------------------------------------------------------------+
| zabbix.history | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| zabbix.history | optimize | status | OK |
+----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.02 sec)
MariaDB [zabbix]> truncate table history_uint;
Query OK, 0 rows affected (0.02 sec)
MariaDB [zabbix]> optimize table history_uint;
+---------------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------------------+----------+----------+-------------------------------------------------------------------+
| zabbix.history_uint | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| zabbix.history_uint | optimize | status | OK |
+---------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.01 sec)4. 용량 확인
select table_name, (data_length + index_length)/1024/1024 as total_mb, table_rows from information_schema.tables where table_schema = 'zabbix';5. 로컬 공간이 적을 시 gzip으로 압축하여 DB 덤프 (os cli에서 진행)
mysqldump -uroot -p zabbix | gzip > /tmp/zabbix.sql.gz
6. 데이터베이스 Drop 하기
MariaDB [NONE]> drop database zabbix;
7. MySQL 나가기
MariaDB [NONE]> quit
Bye8. MYsql 서비스 중지
systemctl stop mariadb9. ibdta*, ib_logfile* 파일 삭제
rm ibdta*, ib_logfile*10. 필요시 my.cnf 또는 my.ini에서 해당 부분 조정
[mysqld]
innodb_file_per_table #테이블 별 테이블스페이스 사용
innodb_flush_method=O_DIRECT #O/S캐시를 사용하지 않음, O/S와 InnoDB에 이중으로 퍼버링하지 않겠다는 의미, Windows는 무관
innodb_buffer_pool_size=8G #물리 메모리의 50% 정도, InnoDB 버퍼 크기
innodb_log_file_size=2G #위 버퍼 크기의 1/4 정도, ib_logifle 크기11. MySql 시작
systemctl start mariadb12. DB 접속하여 Drop한 데이터베이스 생성
mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.68-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> create database zabbix character set utf8 collate utf8_bin;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> quit
Bye13. 압축된 덤프파일 Import
gunzip < /tmp/zabbix.sql.gz | mysql -uroot -p zabbix14. 확인
mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 5.5.68-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| zabbix |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> use zabbix
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [zabbix]> select table_name, (data_length + index_length)/1024/1024 as total_mb, table_rows from information_schema.tables where table_schema = 'zabbix';
+----------------------------+------------+------------+
| table_name | total_mb | table_rows |
+----------------------------+------------+------------+
| acknowledges | 0.06250000 | 0 |
| actions | 0.04687500 | 8 |
| alerts | 0.17187500 | 33 |
| application_discovery | 0.04687500 | 72 |
| application_prototype | 0.07812500 | 237 |
| application_template | 0.04687500 | 353 |
| applications | 0.17187500 | 1026 |
| auditlog | 0.06250000 | 2 |
| auditlog_details | 0.03125000 | 0 |
| autoreg_host | 0.04687500 | 0 |
| conditions | 0.03125000 | 24 |
| config | 0.04687500 | 1 |
| config_autoreg_tls | 0.03125000 | 1 |
| corr_condition | 0.03125000 | 0 |
| corr_condition_group | 0.03125000 | 0 |
| corr_condition_tag | 0.01562500 | 0 |
| corr_condition_tagpair | 0.01562500 | 0 |
| corr_condition_tagvalue | 0.01562500 | 0 |
| corr_operation | 0.03125000 | 0 |
| correlation | 0.04687500 | 0 |
| dashboard | 0.03125000 | 2 |
| dashboard_user | 0.04687500 | 0 |
| dashboard_usrgrp | 0.04687500 | 1 |
| dbversion | 0.01562500 | 1 |
| dchecks | 0.03125000 | 1 |
| dhosts | 0.03125000 | 0 |
| drules | 0.04687500 | 1 |
| dservices | 0.04687500 | 0 |
| escalations | 0.06250000 | 0 |
| event_recovery | 0.04687500 | 6 |
| event_suppress | 0.06250000 | 0 |
| event_tag | 0.03125000 | 0 |
| events | 0.04687500 | 23 |
| expressions | 0.03125000 | 10 |
| functions | 0.90625000 | 5756 |
| globalmacro | 0.03125000 | 5 |
| globalvars | 0.01562500 | 1 |
| graph_discovery | 0.06250000 | 359 |
| graph_theme | 0.03125000 | 4 |
| graphs | 0.54687500 | 1651 |
| graphs_items | 0.71875000 | 3891 |
| group_discovery | 0.03125000 | 0 |
| group_prototype | 0.06250000 | 11 |
| history | 0.03125000 | 0 |
| history_log | 0.03125000 | 0 |
| history_str | 0.07812500 | 586 |
| history_text | 0.20312500 | 1033 |
| history_uint | 0.03125000 | 0 |
| host_discovery | 0.04687500 | 4 |
| host_inventory | 0.01562500 | 0 |
| host_tag | 0.03125000 | 0 |
| hostmacro | 0.42187500 | 1947 |
| hosts | 0.25000000 | 294 |
| hosts_groups | 0.04687500 | 258 |
| hosts_templates | 0.04687500 | 166 |
| housekeeper | 0.01562500 | 0 |
| hstgrp | 0.03125000 | 26 |
| httpstep | 0.03125000 | 0 |
| httpstep_field | 0.03125000 | 0 |
| httpstepitem | 0.04687500 | 0 |
| httptest | 0.07812500 | 0 |
| httptest_field | 0.03125000 | 0 |
| httptestitem | 0.04687500 | 0 |
| icon_map | 0.04687500 | 0 |
| icon_mapping | 0.04687500 | 0 |
| ids | 0.01562500 | 50 |
| images | 1.53125000 | 155 |
| interface | 0.04687500 | 15 |
| interface_discovery | 0.03125000 | 0 |
| interface_snmp | 0.01562500 | 8 |
| item_application_prototype | 0.39062500 | 2408 |
| item_condition | 0.20312500 | 1291 |
| item_discovery | 0.75000000 | 5693 |
| item_preproc | 1.89062500 | 11196 |
| item_rtdata | 0.14062500 | 2027 |
| items | 7.17187500 | 11186 |
| items_applications | 2.06250000 | 7859 |
| lld_macro_path | 0.03125000 | 91 |
| lld_override | 0.03125000 | 112 |
| lld_override_condition | 0.03125000 | 108 |
| lld_override_opdiscover | 0.01562500 | 115 |
| lld_override_operation | 0.03125000 | 115 |
| lld_override_ophistory | 0.01562500 | 0 |
| lld_override_opinventory | 0.01562500 | 0 |
| lld_override_opperiod | 0.01562500 | 0 |
| lld_override_opseverity | 0.01562500 | 0 |
| lld_override_opstatus | 0.01562500 | 115 |
| lld_override_optag | 0.03125000 | 0 |
| lld_override_optemplate | 0.04687500 | 0 |
| lld_override_optrends | 0.01562500 | 0 |
| maintenance_tag | 0.03125000 | 0 |
| maintenances | 0.04687500 | 0 |
| maintenances_groups | 0.04687500 | 0 |
| maintenances_hosts | 0.04687500 | 0 |
| maintenances_windows | 0.04687500 | 0 |
| mappings | 0.21875000 | 1795 |
| media | 0.04687500 | 1 |
| media_type | 0.40625000 | 31 |
| media_type_message | 0.09375000 | 158 |
| media_type_param | 0.07812500 | 588 |
| module | 0.01562500 | 0 |
| opcommand | 0.03125000 | 0 |
| opcommand_grp | 0.04687500 | 0 |
| opcommand_hst | 0.04687500 | 0 |
| opconditions | 0.03125000 | 0 |
| operations | 0.03125000 | 15 |
| opgroup | 0.04687500 | 0 |
| opinventory | 0.01562500 | 0 |
| opmessage | 0.03125000 | 14 |
| opmessage_grp | 0.04687500 | 7 |
| opmessage_usr | 0.04687500 | 0 |
| optemplate | 0.04687500 | 1 |
| problem | 0.06250000 | 17 |
| problem_tag | 0.03125000 | 0 |
| profiles | 0.12500000 | 430 |
| proxy_autoreg_host | 0.03125000 | 0 |
| proxy_dhistory | 0.04687500 | 0 |
| proxy_history | 0.03125000 | 0 |
| regexps | 0.03125000 | 5 |
| rights | 0.04687500 | 7 |
| screen_user | 0.04687500 | 0 |
| screen_usrgrp | 0.04687500 | 1 |
| screens | 0.04687500 | 75 |
| screens_items | 0.09375000 | 369 |
| scripts | 0.06250000 | 3 |
| service_alarms | 0.04687500 | 0 |
| services | 0.03125000 | 0 |
| services_links | 0.04687500 | 0 |
| services_times | 0.03125000 | 0 |
| sessions | 0.03125000 | 0 |
| slides | 0.04687500 | 0 |
| slideshow_user | 0.04687500 | 0 |
| slideshow_usrgrp | 0.04687500 | 0 |
| slideshows | 0.04687500 | 0 |
| sysmap_element_trigger | 0.04687500 | 0 |
| sysmap_element_url | 0.03125000 | 0 |
| sysmap_shape | 0.03125000 | 5 |
| sysmap_url | 0.03125000 | 0 |
| sysmap_user | 0.04687500 | 0 |
| sysmap_usrgrp | 0.04687500 | 0 |
| sysmaps | 0.07812500 | 1 |
| sysmaps_elements | 0.09375000 | 12 |
| sysmaps_link_triggers | 0.04687500 | 0 |
| sysmaps_links | 0.06250000 | 0 |
| tag_filter | 0.04687500 | 0 |
| task | 0.04687500 | 0 |
| task_acknowledge | 0.01562500 | 0 |
| task_check_now | 0.01562500 | 0 |
| task_close_problem | 0.01562500 | 0 |
| task_data | 0.01562500 | 0 |
| task_remote_command | 0.01562500 | 0 |
| task_remote_command_result | 0.01562500 | 0 |
| task_result | 0.03125000 | 0 |
| timeperiods | 0.01562500 | 0 |
| trends | 0.26562500 | 3773 |
| trends_uint | 2.51562500 | 34842 |
| trigger_depends | 0.26562500 | 1106 |
| trigger_discovery | 0.10937500 | 577 |
| trigger_tag | 0.03125000 | 2 |
| triggers | 1.92187500 | 4460 |
| users | 0.03125000 | 8 |
| users_groups | 0.04687500 | 9 |
| usrgrp | 0.03125000 | 7 |
| valuemaps | 0.03125000 | 253 |
| widget | 0.03125000 | 17 |
| widget_field | 0.10937500 | 152 |
+----------------------------+------------+------------+
166 rows in set (0.01 sec)
MariaDB [zabbix]>
MariaDB [zabbix]> quit
Bye
ls -ltrh
total 1019M
drwx------ 2 mysql mysql 4.0K Sep 27 17:05 mysql
drwx------ 2 mysql mysql 4.0K Sep 27 17:05 performance_schema
-rw-rw---- 1 mysql mysql 52 Feb 18 15:34 aria_log_control
-rw-rw---- 1 mysql mysql 16K Feb 18 15:34 aria_log.00000001
-rw-rw---- 1 mysql mysql 500M Feb 18 15:38 ib_logfile1
srwxrwxrwx 1 mysql mysql 0 Feb 18 15:38 mysql.sock
drwx------ 2 mysql mysql 12K Feb 18 15:48 zabbix
-rw-rw---- 1 mysql mysql 18M Feb 18 15:48 ibdata1
-rw-rw---- 1 mysql mysql 500M Feb 18 15:48 ib_logfile0이렇게 ibdata1 파일이 클리어 된 모습을 볼 수 있다.
728x90
300x250
'IT > Zabbix' 카테고리의 다른 글
| Zabbix + VMware 모니터링 설정 (3) | 2022.04.12 |
|---|---|
| (최신) Amazon Linux 2 Zabbix Server 5.0 설치 (0) | 2022.01.17 |
| Zabbix agent on Windows Server (0) | 2021.08.17 |
| Zabbix 감시 설정 (0) | 2021.07.29 |
| Zabbix Template 설정 (0) | 2021.07.29 |