已验证，生产环境测试OK 1 在有些场景下可以明显增加查询性能，特别是对于那些重度使用的表如果是一个单独的分区或者好几个分区就可以明显增加查询性能，因为比起加载整张表的数据到内存，一个分区的数据和索引更容易加载到内存。查看zabbix数据的general日志，可以发现zabbix对于history相关的几张表调用是非常频繁的，所以如果要优化zabbix的数据库重点要优化history这几张大表。
如果查询或者更新主要是使用一个分区，那么性能提升就可以简单地通过顺序访问磁盘上的这个分区而不用使用索引和随机访问整张表。
批量插入和删除执行的时候可以简单地删除或者增加分区，只要当创建分区的时候有计划的创建。ALTER TABLE操作也会很快
脚本起初参考位置：
https://blog.hbis.fr/2013/01/31/zabbix-mysql_partitioning/
脚本下载地址：
https://codeload.github.com/itnihao/zabbixdbpartitioning/zip/master
脚本说明：
作者：
itnihao
脚本针对的是zabbix2,x，我用的是zabbix3.x ，表结构发生了变化
变化如下：
mysql> show create table history_log\G;
*************************** 1. row ***************************
Table: history_log
Create Table: CREATE TABLE `history_log` (
`id` bigint(20) unsigned NOT NULL,
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`timestamp` int(11) NOT NULL DEFAULT '0',
`source` varchar(64) NOT NULL DEFAULT '',
`severity` int(11) NOT NULL DEFAULT '0',
`value` text NOT NULL,
`logeventid` int(11) NOT NULL DEFAULT '0',
`ns` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`), #zabbix3.x 没有这两行了，可是分表的时候，修改的就是这两行
UNIQUE KEY `history_log_2` (`itemid`,`id`),
KEY `history_log_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> show create table history_text \G;
*************************** 1. row ***************************
Table: history_text
Create Table: CREATE TABLE `history_text` (
`id` bigint(20) unsigned NOT NULL,
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` text NOT NULL,
`ns` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`), #zabbix3.x 没有这两行了，可是分表的时候，修改的就是这两行
UNIQUE KEY `history_text_2` (`itemid`,`id`),
KEY `history_text_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
删除这两个表，新建了符合脚本的表，这样搞后，分表成功了，测试也没法问题，但是不知道后续会不会出什么问题，不知道这两行的作用。
脚本如下：
根据自己的实际环境，脚本要修改三个地方
1.数据库的用户名和密码 默认是 zabbix zabbix
2.zabbix_server.conf的路径 默认是 /etc/zabbix/
3.修改数据存放目录
4.. /etc/init.d/zabbix-server 控制文件要存在 并且就是这个名字
中途 切莫轻易中断脚本，容易导致表数据损坏
检查完毕后执行脚本
脚本执行如下：
[root@wang ~]# ./partitiontables.sh
Ready to partition tables.
Ready to update permissions of Zabbix user to create routines
Enter root DB user: zabbix #输入数据库的用户名
Enter zabbix password: 123.com #密码
Warning: Using a password on the command line interface can be insecure.
Do you want to backup the database (recommended) (Y/n): y #是否备份数据库
Enter output file, press return for default of /tmp/zabbix.sql #输入备份完整路径，默认为/tmp/zabbix.sql
Warning: Using a password on the command line interface can be insecure.
ERROR 1410 (42000) at line 1: You are not allowed to create a user with GRANT #不用理会
Warning: Using a password on the command line interface can be insecure.
Mysqldump succeeded!, proceeding with upgrade...
Ready to proceed: #列出history保存的天数
Starting yearly partioning at: 2017
and ending at: 2017
With 90 days of daily history
Ready to proceed (Y/n): #那些表将会被修改
y
Altering table: history
Altering table: history_log
Altering table: history_str
Altering table: history_text
Altering table: history_uint
Altering table: trends
Altering table: trends_uint
Creating monthly partitions for table: trends
Creating monthly partitions for table: trends_uint
Creating daily partitions for table: history
Creating daily partitions for table: history_log
Creating daily partitions for table: history_str
Creating daily partitions for table: history_text
Creating daily partitions for table: history_uint
Ready to apply script to database, this may take a while.(Y/n): #选择是否修改那些表
y
Warning: Using a password on the command line interface can be insecure.
Altering tables
history
history_log
history_str
history_text
history_uint
trends
trends_uint
trends
trends_uint
history
history_log
history_str
history_text
history_uint
Installing procedures
Do you want to update the /usr/local/zabbix-3.2.1/etc/zabbix_server.conf
to disable housekeeping (Y/n): y #选择是否修改配置文件
#会自动重启
Do you want to update the crontab (Y/n): y #选择是否添加计划任务
The crontab entry can be either in /etc/cron.daily, or added
to the crontab for root
Do you want to add this to the /etc/cron.daily directory (Y/n): y
Enter email of who should get the daily housekeeping reports:
执行完毕后测试 是否成功
[root@wang ~]# mysql --skip-column-names -B -h localhost -uzabbix -p123.com zabbix -e "CALL create_zabbix_partitions();"
Warning: Using a password on the command line interface can be insecure.
create_partition(zabbix,history,p20170709,1499616000)
create_partition(zabbix,history,p20170710,1499702400)
create_partition(zabbix,history,p20170711,1499788800)
create_partition(zabbix,history,p20170712,1499875200)
create_partition(zabbix,history,p20170713,1499961600)
create_partition(zabbix,history_log,p20170709,1499616000)
create_partition(zabbix,history_log,p20170710,1499702400)
create_partition(zabbix,history_log,p20170711,1499788800)
create_partition(zabbix,history_log,p20170712,1499875200)
create_partition(zabbix,history_log,p20170713,1499961600)
create_partition(zabbix,history_str,p20170709,1499616000)
create_partition(zabbix,history_str,p20170710,1499702400)
create_partition(zabbix,history_str,p20170711,1499788800)
create_partition(zabbix,history_str,p20170712,1499875200)
create_partition(zabbix,history_str,p20170713,1499961600)
create_partition(zabbix,history_text,p20170709,1499616000)
create_partition(zabbix,history_text,p20170710,1499702400)
create_partition(zabbix,history_text,p20170711,1499788800)
create_partition(zabbix,history_text,p20170712,1499875200)
create_partition(zabbix,history_text,p20170713,1499961600)
create_partition(zabbix,history_uint,p20170709,1499616000)
create_partition(zabbix,history_uint,p20170710,1499702400)
create_partition(zabbix,history_uint,p20170711,1499788800)
create_partition(zabbix,history_uint,p20170712,1499875200)
create_partition(zabbix,history_uint,p20170713,1499961600)
create_partition(zabbix,trends,p201709,1506787200)
create_partition(zabbix,trends,p201710,1509465600)
create_partition(zabbix,trends_uint,p201709,1506787200)
create_partition(zabbix,trends_uint,p201710,1509465600)
以上是针对一个新的zabbix的时候，如果是一个工作有段时间的zabbix，数据库里的数据量较大，执行时间天知道有多长，建议先清空历史数据（情况前完整来个备份吧）
mysql> use zabbix;
mysql> truncate table history;
mysql> optimize table history;
mysql> truncate table history_str;
mysql> optimize table history_str;
mysql> truncate table history_uint;
mysql> optimize table history_uint;
mysql> truncate table trends;
mysql> optimize table trends;
mysql> truncate table trends_uint;
mysql> optimize table trends_uint;
mysql> CREATE TABLE `history_log` ( `id` bigint(20) unsigned NOT NULL, `itemid` bigint(20) unsigned NOT NULL, `clock` int(11) NOT NULL DEFAULT '0', `timestamp` int(11) NOT NULL DEFAULT '0', `source` varchar(64) NOT NULL DEFAULT '', `severity` int(11) NOT NULL DEFAULT '0', `value` text NOT NULL, `logeventid` int(11) NOT NULL DEFAULT '0', `ns` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `history_log_2` (`itemid`,`id`), KEY `history_log_1` (`itemid`,`clock`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE `history_text` ( `id` bigint(20) unsigned NOT NULL, `itemid` bigint(20) unsigned NOT NULL, `clock` int(11) NOT NULL DEFAULT '0', `value` text NOT NULL, `ns` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `history_text_2` (`itemid`,`id`), KEY `history_text_1` (`itemid`,`clock`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
