本文共 7687 字，大约阅读时间需要 25 分钟。
zabbix数据库优化之数据库优化二
简介
数据库history设置是保存7天。然后如果你没有分区。虽然数据在减少但是表空间不会减少。浪费硬盘空间的同事缓存内的cache部分也没有被释放。分区后可以迁移分区合并分区删除已经没有数据的分区优化表空间优化buffer内存。
一，前期操作
清空表
Truncate table table_name;
导出库
mysqldump -uroot -p -all-databases >zabbix.sql
整理表空间碎片
Alter table tables_name engine=innodb;
二，迁移mysql
1调整升级mysql到5.7 最新的存储过程需要
2调整mysql参数
检测你的参数是不是设置的合理
pt-variable-advisor --source-of-variables vars.vxt
三，分区操作
分区资料链接如下
核心部分
DELIMITER $$
CREATE PROCEDURE `partition_maintenance_all`(SCHEMA_NAME VARCHAR(32))
BEGIN
CALL partition_maintenance(SCHEMA_NAME, 'history', 20, 24, 20);
CALL partition_maintenance(SCHEMA_NAME, 'history_log', 20, 24, 20);
CALL partition_maintenance(SCHEMA_NAME, 'history_str', 20, 24, 20);
CALL partition_maintenance(SCHEMA_NAME, 'history_text', 20, 24, 20);
CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 20, 24, 20);
CALL partition_maintenance(SCHEMA_NAME, 'trends', 20, 24, 20);
CALL partition_maintenance(SCHEMA_NAME, 'trends_uint', 20, 24, 20);
END$$
DELIMITER ;
创建分区的存储过程
CALL partition_maintenance(SCHEMA_NAME, 'history', 20, 24, 20);
保留天数20天
时间间隔24小时
创建分区数 例如 保留20天 创建分区数20
时间间隔1小时
创建分区数量 24*20
CALL partition_maintenance(SCHEMA_NAME, ;hisuozy', 20, 1, 22*24):<'span>
调用存储过程;创建分区;
call maintenance(‘zabbix’);
四，手动操作分区
手动清数据 清除7月30号前分区数据
call partition_drop('zabbix','history','201707300000');
call partition_drop('zabbix','history_log','201707300000');
call partition_drop('zabbix','history_str','201707300000');
call partition_drop('zabbix','history_text','201707300000');
call partition_drop('zabbix','history_uint','201707300000');
call partition_drop('zabbix','trends','201707300000');
call partition_drop('zabbix','trends_uint','201707300000');
五，注意事项
注意 partition_maintenance存储过程假如你设置的是保留20天 那么20天前的分区会被清除
清除实际调用的
call partition_drop('zabbix','history',201707110000’);
分组报错
注意:
5.7 group by需要。默认是不能select 多col9`n6
set sql_mode=NO_ENGINE_SUBSTITUTION;
六，效果展示
七，附:
#########删除201708110000之前的所有数据。同时可以删除已经没有数据的分区
call partition_drop('zabbix','history','201708110000');
call partition_drop('zabbix','history_log','201708110000');
call partition_drop('zabbix','history_str','201708110000');
call partition_drop('zabbix','history_text','201708110000');
call partition_drop('zabbix','history_uint','201708110000');
call partition_drop('zabbix','trends','201708110000'); #建议保留
call partition_drop('zabbix','trends_uint','201708110000'); #建议保留
########## 创建分区
CALL partition_maintenance('zabbix', 'history', 5, 24, 10);
CALL partition_maintenance('zabbix', 'history_log', 5, 24, 10);
CALL partition_maintenance('zabbix', 'history_str', 5, 24, 10);
CALL partition_maintenance('zabbix', 'history_text', 5, 24, 10);
CALL partition_maintenance('zabbix', 'history_uint', 5, 24, 10);
CALL partition_maintenance('zabbix', 'trends', 5, 24, 10);
CALL partition_maintenance('zabbix', 'trends_uint', 5, 24, 10);
保留天数 5天 注意5天前将被删除
创浆蕬间间隔 24小时
创建分区数 10 分区不会自动创建所以可以创建多点一共保留15个分区
附件1
服务器配置24c 64g
[mysqld]
datadir=/database/
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
explicit_defaults_for_timestamp=1
innodb_file_per_table=on
skip_name_resolve=1
#事物提交刷新事物日志
innodb_flush_log_at_trx_commit=2
#Key_read_requests从缓存读取索引的请求次数。
#Key_reads从磁盘读取索引的请求次数。
#自适应hash索引
innodb_adaptive_hash_index=on
#开启临接页刷新 insert多开启。update多不开启
innodb_flush_neighbors=1
#异步io
innodb_use_native_aio=off
#更改时区设置避免system阻塞
#time_zone ='+8:00'
#InnoDB 用于写入磁盘上日志文件 的缓冲区大小innodb_page_size=32k或64k innodb_log_buffer_size至少16M
innodb_page_size=32k
innodb_log_buffer_size=64M
key_buffer_size=128M
##grup by分组必须加大
tmp_table_size=512M
max_heap_table_size=512M
#每个线程为其扫描的每个 表分配一个大小以字节为单位的缓冲区
read_buffer_size=4M
#随机读缓存区大小。注意order by排序顺序读取表
read_rnd_buffer_size=16M
#排序缓冲
sort_buffer_size=32M
max_connections=1024
#开启查询缓存
query_cache_type=1
query_cache_size=64M
innodb_write_io_threads = 24
innodb_read_io_threads = 24
innodb_page_cleaners=4
#设置change_buffer占buffer比例(insert update)
innodb_change_buffer_max_size=50
#预读当你连续读取设定的数量的page后会触发读取这个extent的剩余page。
innkdb_rcntom_read_ahead=on
#并发
innodb_thread_concurrency = 24
#缓存线程
thread_cache_size=500
max_connect_errors=3000
max_connections=3000
#自动递增锁模式
innodb_autoinc_lock_mode=2
#所有的change都缓冲
innodb_change_buffering=all
#缓冲池配置20个池每个1G
innodb_buffer_pool_chunk_size=1G
innodb_buffer_pool_instances=20
innodb_buffer_pool_size=20G
[mysqld_safe]
log-error=/database/error.log
pid-file=/database/mysqld.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
补充自动清除分区py:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | #!/usr/local/python-3.5.0/bin/python3.5 #-*- coding:utf-8 -*- import pymysql,datetime class db_action( object ): def __init__( self ,host,port,user,password,database = "mysql" ,charset = "utf8" ): self .host = host self .port = port self .user = user self .password = password self .database = database self .charset = charset self .__cursor = self .__conn().cursor() def __conn( self ): return pymysql.Connect(host = self .host,port = self .port,user = self .user,\ password = self .password,database = self .database,\ charset = self .charset) def callproc( self ,pl,t): ret = self .__cursor.callproc(pl,args = t) return ret if __name__ = = '__main__' : db = db_action(host = 'xxx.xxx.xxx.xxx' ,port = 3306 ,user = 'xxxx' ,password = "xxxxx" ,database = "zabbix" ,charset = 'utf8' ) rets = [] ret = db.callproc( 'partition_drop' ,( 'zabbix' , 'history' ,(datetime.datetime.now() + datetime.timedelta(days = - 7 )).strftime( '%Y%m%d0000' ),)) rets.append(ret) ret = db.callproc( 'partition_drop' ,( 'zabbix' , 'history_log' ,(datetime.datetime.now() + datetime.timedelta(days = - 7 )).strftime( '%Y%m%d0000' ),)) rets.append(ret) ret = db.callproc( 'partition_drop' ,( 'zabbix' , 'history_str' ,(datetime.datetime.now() + datetime.timedelta(days = - 7 )).strftime( '%Y%m%d0000' ),)) rets.append(ret) ret = db.callproc( 'partition_drop' ,( 'zabbix' , 'history_text' ,(datetime.datetime.now() + datetime.timedelta(days = - 7 )).strftime( '%Y%m%d0000' ),)) rets.append(ret) ret = db.callproc( 'partition_drop' ,( 'zabbix' , 'history_uint' ,(datetime.datetime.now() + datetime.timedelta(days = - 7 )).strftime( '%Y%m%d0000' ),)) rets.append(ret) with open ( '/root/pariton_clear.log' ,encoding = 'utf-8' ,mode = 'a+' ) as f: f.write( str (rets) + '\n' ) |
contable
| 1 | 00 10 * * * /usr/local/python-3 .5.0 /bin/python3 .5 /etc/zabbix/shell/Partition_Clear .py |
补充zabbix server配置文件
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | LogFile= /var/log/zabbix/zabbix_server .log LogFileSize=1024 DebugLevel=3 #日志级别 PidFile= /var/run/zabbix/zabbix_server .pid DBHost=localhost DBName=zabbix DBUser=zabbix DBPassword=zabbix DBSocket= /var/lib/mysql/mysql .sock StartPollers=100 #poller进程 100 StartPollersUnreachable=30 #无法访问的主机轮询进程30 StartPingers=30 #ping轮询数量 StartDiscoverers=30 StartTimers=10 SenderFrequency=30 #发送报警超时 SNMPTrapperFile= /var/log/snmptrap/snmptrap .log CacheSize=4096M #存储主机，项目和触发器数据的共享内存 CacheUpdateFrequency=120 #执行配置缓存的更新频率 StartDBSyncers=24 #数据库同步进程 HistoryCacheSize=2048M HistoryIndexCacheSize=2048M TrendCacheSize=2048M #趋势数据最大2G ValueCacheSize=2048M #缓存项历史数据请求，历史值缓存 Timeout=30 UnreachablePeriod=120 #几秒钟的不可达性将主机视为不可用。 不可用 UnavailableDelay=60 #主机在不可用期间内检查可用性的频率（秒）。 不可用 UnreachableDelay=5 #不可达检测频率 解决wait for 3 seconds AlertScriptsPath= /usr/lib/zabbix/alertscripts ExternalScripts= /usr/lib/zabbix/externalscripts LogSlowQueries=2000 #记录慢查询 HousekeepingFrequency=1 #从历史记录，警报和警报表中删除不必要的信息 不超过4个小时 每隔1小时启动一次，删除过期数据 MaxHousekeeperDelete=1000000 #清除过期数据，超过这个阀值的行都会被清理。 |
补充图片
1，系统任务队列查看
2，mysql status计量分析
3，io吞吐查看
4，my.cnf配置文件检查
5，io buys计量
6，engine计量
本文转自 吃草的青蛙 51CTO博客，原文链接：http://blog.51cto.com/tlinux/1970022，如需转载请自行联系原作者