开发者社区> 技术小阿哥> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

zabbix数据库优化之数据库优化(二)

简介:
+关注继续查看

zabbix数据库优化之数据库优化二

简介

    数据库history设置是保存7天。然后如果你没有分区。虽然数据在减少但是表空间不会减少。浪费硬盘空间的同事缓存内的cache部分也没有被释放。分区后可以迁移分区合并分区删除已经没有数据的分区优化表空间优化buffer内存。 

  

一,前期操作

清空表

Truncate table table_name;

 

导出库

mysqldump -uroot -p -all-databases >zabbix.sql

 

 

整理表空间碎片

Alter table tables_name engine=innodb;

 

 

 

 

二,迁移mysql

1调整升级mysql5.7  最新的存储过程需要

 

2调整mysql参数

wKioL1nPTqvhtXnQAAAKByRU5lA600.png-wh_50

 

 

wKioL1nPTrXgPceyAAAKv5CxYHg878.png-wh_50

检测你的参数是不是设置的合理

 

pt-variable-advisor --source-of-variables vars.vxt


三,分区操作

 

分区资料链接如下

https://www.zabbix.org/wiki/Docs/howto/mysql_partition

 

核心部分 

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);


wKiom1nPUBeDPPsxAAAKBeFT_tM517.png-wh_50

保留天数20

时间间隔24小时   

创建分区数   例如  保留20天  创建分区数20

 

时间间隔1小时

创建分区数量 24*20

 

 

CALL partition_maintenance(SCHEMA_NAME, ;hisuozy', 20, 1, 22*24):<'span>

 

 

调用存储过程;创建分区;

call maintenance(zabbix);

 

 

 

 

 

 

 

四,手动操作分区

手动清数据   清除730号前分区数据

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天前的分区会被清除

wKiom1nPUCOB4gp1AAANxLzZhYo543.png-wh_50

wKiom1nPUC3g24cnAAAMZaFQ3cA812.png-wh_50

 

 

wKioL1nPT_HCAdd-AAAWo_8OFA4192.png-wh_50

 

清除实际调用的

call partition_drop('zabbix','history',201707110000);

 

 

 

 

 

分组报错

wKioL1nPT_qCK5q6AAAr_EEjAIY225.png-wh_50

 

注意:

5.7  group by需要。默认是不能select col9`n6

set sql_mode=NO_ENGINE_SUBSTITUTION;

 


 

 

 

 

六,效果展示

wKioL1nPUCXwUjLWAAD_kjJJ3Fk096.png-wh_50

 

 

 

七,附:

 


#########删除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,系统任务队列查看

wKiom1nbIveTlQZNAAB_972xito782.png-wh_50

2,mysql status计量分析

wKioL1nbIqbgVsKMAAAPDQ70Z7Y793.png-wh_50

wKiom1nbIveyU4lKAACT7wFo2q0487.png-wh_50

wKioL1nbIqaSS5qvAAAj7_zFEDs267.png-wh_50

wKiom1nbIvijgrxOAACPNfV7epc494.png-wh_50


3,io吞吐查看

wKiom1nbIvjQ9djmAAAVTphmDg0272.png-wh_50

wKioL1nbIqfRijGWAAGTT80f3Dk609.png-wh_50

4,my.cnf配置文件检查

wKiom1nbIvigDWc8AAC7Vq-acGw775.png-wh_50

5,io buys计量

wKioL1nbIqeQrOHYAACEGpRHCBs820.png-wh_50


6,engine计量

wKioL1nbJ9DieL2SAAARw9Ay_rA448.png-wh_50

wKiom1nbKCGCKpkvAABv5TMPUow820.png-wh_50



本文转自 吃草的青蛙 51CTO博客,原文链接:http://blog.51cto.com/tlinux/1970022,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
文章
问答
文章排行榜
最热
最新
相关电子书
更多
PostgreSQL高并发数据库应用数据
立即下载
MySQL 5.7优化不求人
立即下载
MySQL 技术大全:开发、优化与运维实战
立即下载