查看实例所有库占用空间
mysql> select table_schema, sum(data_length+index_length)/1024/1024 as total_mb,
sum(data_length)/1024/1024 as data_mb, sum(index_length)/1024/1024 as index_mb,
count(*) as tables, curdate() as today from information_schema.tables group by table_schema order by 2 desc;
+--------------------+--------------+-------------+-------------+--------+------------+
| table_schema | total_mb | data_mb | index_mb | tables | today |
+--------------------+--------------+-------------+-------------+--------+------------+
| zabbix | 102.12500000 | 67.95312500 | 34.17187500 | 129 | 2018-11-29 |
| mysql | 0.67597771 | 0.57539177 | 0.10058594 | 24 | 2018-11-29 |
| information_schema | 0.00878906 | 0.00000000 | 0.00878906 | 40 | 2018-11-29 |
| performance_schema | 0.00000000 | 0.00000000 | 0.00000000 | 17 | 2018-11-29 |
+--------------------+--------------+-------------+-------------+--------+------------+
4 rows in set (0.08 sec)
查看单个库占用空间,如下查询zabbix库占用
mysql> select concat(truncate(sum(data_length)/1024/1024,2),'mb') as data_size,
concat(truncate(sum(max_data_length)/1024/1024,2),'mb') as max_data_size,
concat(truncate(sum(data_free)/1024/1024,2),'mb') as data_free,
concat(truncate(sum(index_length)/1024/1024,2),'mb') as index_size
from information_schema.tables where table_schema = 'zabbix';
+-----------+---------------+------------+------------+
| data_size | max_data_size | data_free | index_size |
+-----------+---------------+------------+------------+
| 68.95mb | 0.00mb | 21156.00mb | 34.17mb |
+-----------+---------------+------------+------------+
1 row in set (0.06 sec)
查看zabbix库下所有表占用空间
mysql> select table_name, (data_length/1024/1024) as data_mb , (index_length/1024/1024)
as index_mb, ((data_length+index_length)/1024/1024) as all_mb, table_rows
from information_schema.tables where table_schema = 'zabbix';
+----------------------------+-------------+-------------+-------------+------------+
| table_name | data_mb | index_mb | all_mb | table_rows |
+----------------------------+-------------+-------------+-------------+------------+
| acknowledges | 0.01562500 | 0.04687500 | 0.06250000 | 20 |
| act_dict | 0.01562500 | 0.00000000 | 0.01562500 | 8 |
| actions | 0.01562500 | 0.03125000 | 0.04687500 | 5 |
| alerts | 1.51562500 | 0.39062500 | 1.90625000 | 1486 |
| application_discovery | 0.01562500 | 0.03125000 | 0.04687500 | 0 |
| application_prototype | 0.01562500 | 0.03125000 | 0.04687500 | 0 |
| application_template | 0.01562500 | 0.03125000 | 0.04687500 | 10 |
| applications | 0.01562500 | 0.01562500 | 0.03125000 | 102 |
| auditlog | 0.28125000 | 0.17187500 | 0.45312500 | 2542 |
| auditlog_details | 0.01562500 | 0.01562500 | 0.03125000 | 1 |
| autoreg_host | 0.01562500 | 0.01562500 | 0.03125000 | 0 |
| conditions | 0.01562500 | 0.01562500 | 0.03125000 | 8 |
| config | 0.01562500 | 0.03125000 | 0.04687500 | 1 |
| corr_condition | 0.01562500 | 0.01562500 | 0.03125000 | 0 |
| corr_condition_group | 0.01562500 | 0.01562500 | 0.03125000 | 0 |
| corr_condition_tag | 0.01562500 | 0.00000000 | 0.01562500 | 0 |
| corr_condition_tagpair | 0.01562500 | 0.00000000 | 0.01562500 | 0 |
| corr_condition_tagvalue | 0.01562500 | 0.00000000 | 0.01562500 | 0 |
| corr_operation | 0.01562500 | 0.01562500 | 0.03125000 | 0 |
| correlation | 0.01562500 | 0.03125000 | 0.04687500 | 0 |
| dbversion | 0.01562500 | 0.00000000 | 0.01562500 | 1 |
| dchecks | 0.01562500 | 0.01562500 | 0.03125000 | 0 |
| dhosts | 0.01562500 | 0.01562500 | 0.03125000 | 0 |
| drules | 0.01562500 | 0.03125000 | 0.04687500 | 0 |
| dservices | 0.01562500 | 0.03125000 | 0.04687500 | 0 |
| escalations | 0.01562500 | 0.01562500 | 0.03125000 | 1 |
| event_recovery | 0.20312500 | 0.23437500 | 0.43750000 | 4814 |
| event_tag | 0.01562500 | 0.01562500 | 0.03125000 | 0 |
| events | 1.51562500 | 0.65625000 | 2.17187500 | 10141 |
| expressions | 0.01562500 | 0.01562500 | 0.03125000 | 6 |
| functions | 0.14062500 | 0.17187500 | 0.31250000 | 1435 |
| globalmacro | 0.01562500 | 0.01562500 | 0.03125000 | 1 |
| globalvars | 0.01562500 | 0.00000000 | 0.01562500 | 0 |
| graph_discovery | 0.01562500 | 0.01562500 | 0.03125000 | 6 |
| graph_theme | 0.01562500 | 0.01562500 | 0.03125000 | 2 |
| graphs | 0.01562500 | 0.06250000 | 0.07812500 | 34 |
| graphs_items | 0.01562500 | 0.03125000 | 0.04687500 | 67 |
| group_discovery | 0.01562500 | 0.01562500 | 0.03125000 | 0 |
| group_prototype | 0.01562500 | 0.04687500 | 0.06250000 | 0 |
| groups | 0.01562500 | 0.01562500 | 0.03125000 | 16 |
| history | 30.56250000 | 16.54687500 | 47.10937500 | 568598 |
| history_log | 0.01562500 | 0.01562500 | 0.03125000 | 0 |
| history_str | 2.51562500 | 1.51562500 | 4.03125000 | 21626 |
| history_text | 3.51562500 | 0.21875000 | 3.73437500 | 6870 |
| history_uint | 20.54687500 | 11.14062500 | 31.68750000 | 391835 |
| host_discovery | 0.01562500 | 0.03125000 | 0.04687500 | 0 |
| host_inventory | 0.01562500 | 0.00000000 | 0.01562500 | 0 |
| hostmacro | 0.01562500 | 0.01562500 | 0.03125000 | 0 |
| hosts | 0.01562500 | 0.09375000 | 0.10937500 | 22 |
| hosts_groups | 0.01562500 | 0.03125000 | 0.04687500 | 23 |
| hosts_templates | 0.01562500 | 0.03125000 | 0.04687500 | 2 |
| housekeeper | 0.01562500 | 0.00000000 | 0.01562500 | 0 |
| httpstep | 0.01562500 | 0.01562500 | 0.03125000 | 0 |
| httpstepitem | 0.01562500 | 0.03125000 | 0.04687500 | 0 |
| httptest | 0.01562500 | 0.06250000 | 0.07812500 | 0 |
| httptestitem | 0.01562500 | 0.03125000 | 0.04687500 | 0 |
| icon_map | 0.01562500 | 0.03125000 | 0.04687500 | 0 |
| icon_mapping | 0.01562500 | 0.03125000 | 0.04687500 | 0 |
| ids | 0.01562500 | 0.00000000 | 0.01562500 | 35 |
| images | 1.51562500 | 0.01562500 | 1.53125000 | 219 |
| interface | 0.01562500 | 0.03125000 | 0.04687500 | 1 |
| interface_discovery | 0.01562500 | 0.01562500 | 0.03125000 | 0 |
| item_application_prototype | 0.01562500 | 0.03125000 | 0.04687500 | 0 |
| item_condition | 0.01562500 | 0.01562500 | 0.03125000 | 14 |
| item_discovery | 0.06250000 | 0.03125000 | 0.09375000 | 123 |
| items | 0.12500000 | 0.07812500 | 0.20312500 | 423 |
| items_applications | 0.07812500 | 0.06250000 | 0.14062500 | 328 |
| maintenances | 0.01562500 | 0.03125000 | 0.04687500 | 0 |
| maintenances_groups | 0.01562500 | 0.03125000 | 0.04687500 | 0 |
| maintenances_hosts | 0.01562500 | 0.03125000 | 0.04687500 | 0 |
| maintenances_windows | 0.01562500 | 0.03125000 | 0.04687500 | 0 |
| mappings | 0.01562500 | 0.01562500 | 0.03125000 | 143 |
| media | 0.01562500 | 0.03125000 | 0.04687500 | 1 |
| media_type | 0.01562500 | 0.01562500 | 0.03125000 | 4 |
| opcommand | 0.01562500 | 0.01562500 | 0.03125000 | 0 |
| opcommand_grp | 0.01562500 | 0.03125000 | 0.04687500 | 0 |
| opcommand_hst | 0.01562500 | 0.03125000 | 0.04687500 | 0 |
| opconditions | 0.01562500 | 0.01562500 | 0.03125000 | 0 |
| operations | 0.01562500 | 0.01562500 | 0.03125000 | 8 |
| opgroup | 0.01562500 | 0.03125000 | 0.04687500 | 0 |
| opinventory | 0.01562500 | 0.00000000 | 0.01562500 | 0 |
| opmessage | 0.01562500 | 0.01562500 | 0.03125000 | 8 |
| opmessage_grp | 0.01562500 | 0.03125000 | 0.04687500 | 3 |
| opmessage_usr | 0.01562500 | 0.03125000 | 0.04687500 | 2 |
| optemplate | 0.01562500 | 0.03125000 | 0.04687500 | 0 |
| problem | 0.07812500 | 0.07812500 | 0.15625000 | 932 |
| problem_tag | 0.01562500 | 0.03125000 | 0.04687500 | 0 |
| profiles | 0.04687500 | 0.03125000 | 0.07812500 | 215 |
| proxy_autoreg_host | 0.01562500 | 0.01562500 | 0.03125000 | 0 |
| proxy_dhistory | 0.01562500 | 0.01562500 | 0.03125000 | 0 |
| proxy_history | 0.01562500 | 0.01562500 | 0.03125000 | 0 |
| regexps | 0.01562500 | 0.01562500 | 0.03125000 | 5 |
| res_dict | 0.01562500 | 0.00000000 | 0.01562500 | 32 |
| rights | 0.01562500 | 0.03125000 | 0.04687500 | 6 |
| screen_user | 0.01562500 | 0.03125000 | 0.04687500 | 0 |
| screen_usrgrp | 0.01562500 | 0.03125000 | 0.04687500 | 0 |
| screens | 0.01562500 | 0.03125000 | 0.04687500 | 1 |
| screens_items | 0.01562500 | 0.01562500 | 0.03125000 | 1 |
| scripts | 0.01562500 | 0.04687500 | 0.06250000 | 3 |
| service_alarms | 0.01562500 | 0.03125000 | 0.04687500 | 0 |
| services | 0.01562500 | 0.01562500 | 0.03125000 | 0 |
| services_links | 0.01562500 | 0.03125000 | 0.04687500 | 0 |
| services_times | 0.01562500 | 0.01562500 | 0.03125000 | 0 |
| sessions | 0.21875000 | 0.14062500 | 0.35937500 | 2048 |
| slides | 0.01562500 | 0.03125000 | 0.04687500 | 0 |
| slideshow_user | 0.01562500 | 0.03125000 | 0.04687500 | 0 |
| slideshow_usrgrp | 0.01562500 | 0.03125000 | 0.04687500 | 0 |
| slideshows | 0.01562500 | 0.03125000 | 0.04687500 | 0 |
| sysmap_element_url | 0.01562500 | 0.01562500 | 0.03125000 | 0 |
| sysmap_url | 0.01562500 | 0.01562500 | 0.03125000 | 0 |
| sysmap_user | 0.01562500 | 0.03125000 | 0.04687500 | 0 |
| sysmap_usrgrp | 0.01562500 | 0.03125000 | 0.04687500 | 0 |
| sysmaps | 0.01562500 | 0.06250000 | 0.07812500 | 1 |
| sysmaps_elements | 0.01562500 | 0.07812500 | 0.09375000 | 1 |
| sysmaps_link_triggers | 0.01562500 | 0.03125000 | 0.04687500 | 0 |
| sysmaps_links | 0.01562500 | 0.04687500 | 0.06250000 | 0 |
| task | 0.01562500 | 0.00000000 | 0.01562500 | 0 |
| task_close_problem | 0.01562500 | 0.00000000 | 0.01562500 | 0 |
| timeperiods | 0.01562500 | 0.00000000 | 0.01562500 | 0 |
| trends | 2.51562500 | 0.00000000 | 2.51562500 | 20892 |
| trends_uint | 1.51562500 | 0.00000000 | 1.51562500 | 2951 |
| trigger_depends | 0.01562500 | 0.03125000 | 0.04687500 | 10 |
| trigger_discovery | 0.01562500 | 0.01562500 | 0.03125000 | 16 |
| trigger_tag | 0.01562500 | 0.01562500 | 0.03125000 | 0 |
| triggers | 0.28125000 | 0.07812500 | 0.35937500 | 717 |
| users | 0.01562500 | 0.01562500 | 0.03125000 | 3 |
| users_groups | 0.01562500 | 0.03125000 | 0.04687500 | 1 |
| usrgrp | 0.01562500 | 0.01562500 | 0.03125000 | 5 |
| valuemaps | 0.01562500 | 0.01562500 | 0.03125000 | 17 |
+----------------------------+-------------+-------------+-------------+------------+
129 rows in set (0.08 sec)
查询单个表状态,如查询zabbix库trends表
mysql> show table status from zabbix where name = 'trends' \G
*************************** 1. row ***************************
Name: trends
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 17558
Avg_row_length: 150
Data_length: 2637824
Max_data_length: 0
Index_length: 0
Data_free: 171966464
Auto_increment: NULL
Create_time: 2018-09-12 19:41:43
Update_time: NULL
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)