Zabbix大表有哪些

这是一个测试zabbix，运行3个月，监控项：2723，每秒新值：38

查询语句，注意修改DB名称

 SELECT 
    table_name AS 'Tables',
    ROUND(((data_length + index_length) / 1024 / 1024),
            2) 'Size in MB'
FROM
    information_schema.TABLES
WHERE
    table_schema = 'testdb_monitor'
ORDER BY (data_length + index_length) DESC;

分表前提

• 按时间范围分表（字段clock，字段无索引）
• MySQL分区表要求范围字段是唯一索引或主键索引，或者是其中一部分，需要修改前核实clock是否在索引中

实际操作

 ALTER TABLE `history_str` PARTITION BY RANGE ( clock)(
PARTITION p20171 VALUES LESS THAN (UNIX_TIMESTAMP("2017-01-01 00:00:00")),
PARTITION p20172 VALUES LESS THAN (UNIX_TIMESTAMP("2017-02-01 00:00:00")),
PARTITION p20173 VALUES LESS THAN (UNIX_TIMESTAMP("2017-03-01 00:00:00")),
PARTITION p20174 VALUES LESS THAN (UNIX_TIMESTAMP("2017-04-01 00:00:00")),
PARTITION p20175 VALUES LESS THAN (UNIX_TIMESTAMP("2017-05-01 00:00:00")),
PARTITION p20176 VALUES LESS THAN (UNIX_TIMESTAMP("2017-06-01 00:00:00")),
PARTITION p20177 VALUES LESS THAN (UNIX_TIMESTAMP("2017-07-01 00:00:00")),
PARTITION p20178 VALUES LESS THAN (UNIX_TIMESTAMP("2017-08-01 00:00:00")),
PARTITION p20179 VALUES LESS THAN (UNIX_TIMESTAMP("2017-09-01 00:00:00")),
PARTITION p201710 VALUES LESS THAN (UNIX_TIMESTAMP("2017-10-01 00:00:00")),
PARTITION p201711 VALUES LESS THAN (UNIX_TIMESTAMP("2017-11-01 00:00:00")),
PARTITION p201712 VALUES LESS THAN (UNIX_TIMESTAMP("2017-12-01 00:00:00")),
PARTITION p20181 VALUES LESS THAN (UNIX_TIMESTAMP("2018-01-01 00:00:00")),
PARTITION p20182 VALUES LESS THAN (UNIX_TIMESTAMP("2018-02-01 00:00:00")),
PARTITION p20183 VALUES LESS THAN (UNIX_TIMESTAMP("2018-03-01 00:00:00")),
PARTITION p20184 VALUES LESS THAN (UNIX_TIMESTAMP("2018-04-01 00:00:00")),
PARTITION p20185 VALUES LESS THAN (UNIX_TIMESTAMP("2018-05-01 00:00:00")),
PARTITION p20186 VALUES LESS THAN (UNIX_TIMESTAMP("2018-06-01 00:00:00")),
PARTITION p20187 VALUES LESS THAN (UNIX_TIMESTAMP("2018-07-01 00:00:00")),
PARTITION p20188 VALUES LESS THAN (UNIX_TIMESTAMP("2018-08-01 00:00:00")),
PARTITION p20189 VALUES LESS THAN (UNIX_TIMESTAMP("2018-09-01 00:00:00")),
PARTITION p201810 VALUES LESS THAN (UNIX_TIMESTAMP("2018-10-01 00:00:00")),
PARTITION p201811 VALUES LESS THAN (UNIX_TIMESTAMP("2018-11-01 00:00:00")),
PARTITION p201812 VALUES LESS THAN (UNIX_TIMESTAMP("2018-12-01 00:00:00")),
PARTITION p20191 VALUES LESS THAN (UNIX_TIMESTAMP("2019-01-01 00:00:00")),
PARTITION p20192 VALUES LESS THAN (UNIX_TIMESTAMP("2019-02-01 00:00:00")),
PARTITION p20193 VALUES LESS THAN (UNIX_TIMESTAMP("2019-03-01 00:00:00")),
PARTITION p20194 VALUES LESS THAN (UNIX_TIMESTAMP("2019-04-01 00:00:00")),
PARTITION p20195 VALUES LESS THAN (UNIX_TIMESTAMP("2019-05-01 00:00:00")),
PARTITION p20196 VALUES LESS THAN (UNIX_TIMESTAMP("2019-06-01 00:00:00")),
PARTITION p20197 VALUES LESS THAN (UNIX_TIMESTAMP("2019-07-01 00:00:00")),
PARTITION p20198 VALUES LESS THAN (UNIX_TIMESTAMP("2019-08-01 00:00:00")),
PARTITION p20199 VALUES LESS THAN (UNIX_TIMESTAMP("2019-09-01 00:00:00")),
PARTITION p201910 VALUES LESS THAN (UNIX_TIMESTAMP("2019-10-01 00:00:00")),
PARTITION p201911 VALUES LESS THAN (UNIX_TIMESTAMP("2019-11-01 00:00:00")),
PARTITION p201912 VALUES LESS THAN (UNIX_TIMESTAMP("2019-12-01 00:00:00")),
PARTITION p20201 VALUES LESS THAN (UNIX_TIMESTAMP("2020-01-01 00:00:00")),
PARTITION p20202 VALUES LESS THAN (UNIX_TIMESTAMP("2020-02-01 00:00:00")),
PARTITION p20203 VALUES LESS THAN (UNIX_TIMESTAMP("2020-03-01 00:00:00")),
PARTITION p20204 VALUES LESS THAN (UNIX_TIMESTAMP("2020-04-01 00:00:00")),
PARTITION p20205 VALUES LESS THAN (UNIX_TIMESTAMP("2020-05-01 00:00:00")),
PARTITION p20206 VALUES LESS THAN (UNIX_TIMESTAMP("2020-06-01 00:00:00")),
PARTITION p20207 VALUES LESS THAN (UNIX_TIMESTAMP("2020-07-01 00:00:00")),
PARTITION p20208 VALUES LESS THAN (UNIX_TIMESTAMP("2020-08-01 00:00:00")),
PARTITION p20209 VALUES LESS THAN (UNIX_TIMESTAMP("2020-09-01 00:00:00")),
PARTITION p202010 VALUES LESS THAN (UNIX_TIMESTAMP("2020-10-01 00:00:00")),
PARTITION p202011 VALUES LESS THAN (UNIX_TIMESTAMP("2020-11-01 00:00:00")),
PARTITION p202012 VALUES LESS THAN (UNIX_TIMESTAMP("2020-12-01 00:00:00")),
PARTITION p20211 VALUES LESS THAN (UNIX_TIMESTAMP("2021-01-01 00:00:00")),
PARTITION p20212 VALUES LESS THAN (UNIX_TIMESTAMP("2021-02-01 00:00:00")),
PARTITION p20213 VALUES LESS THAN (UNIX_TIMESTAMP("2021-03-01 00:00:00")),
PARTITION p20214 VALUES LESS THAN (UNIX_TIMESTAMP("2021-04-01 00:00:00")),
PARTITION p20215 VALUES LESS THAN (UNIX_TIMESTAMP("2021-05-01 00:00:00")),
PARTITION p20216 VALUES LESS THAN (UNIX_TIMESTAMP("2021-06-01 00:00:00")),
PARTITION p20217 VALUES LESS THAN (UNIX_TIMESTAMP("2021-07-01 00:00:00")),
PARTITION p20218 VALUES LESS THAN (UNIX_TIMESTAMP("2021-08-01 00:00:00")),
PARTITION p20219 VALUES LESS THAN (UNIX_TIMESTAMP("2021-09-01 00:00:00")),
PARTITION p202110 VALUES LESS THAN (UNIX_TIMESTAMP("2021-10-01 00:00:00")),
PARTITION p202111 VALUES LESS THAN (UNIX_TIMESTAMP("2021-11-01 00:00:00")),
PARTITION p202112 VALUES LESS THAN (UNIX_TIMESTAMP("2021-12-01 00:00:00")),
PARTITION p9999 VALUES LESS VALUES LESS THAN  MAXVALUE);

参考文章：http://www.tuicool.com/articles/F3yMreb