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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介:

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,如需转载请自行联系原作者

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2月前
|
存储 缓存 监控
数据库优化技术:提升性能与效率的关键策略
【10月更文挑战第15天】数据库优化技术:提升性能与效率的关键策略
99 8
|
7天前
|
SQL Oracle 数据库
使用访问指导(SQL Access Advisor)优化数据库业务负载
本文介绍了Oracle的SQL访问指导(SQL Access Advisor)的应用场景及其使用方法。访问指导通过分析给定的工作负载,提供索引、物化视图和分区等方面的优化建议,帮助DBA提升数据库性能。具体步骤包括创建访问指导任务、创建工作负载、连接工作负载至访问指导、设置任务参数、运行访问指导、查看和应用优化建议。访问指导不仅针对单条SQL语句,还能综合考虑多条SQL语句的优化效果,为DBA提供全面的决策支持。
28 11
|
27天前
|
SQL 存储 BI
gbase 8a 数据库 SQL合并类优化——不同数据统计周期合并为一条SQL语句
gbase 8a 数据库 SQL合并类优化——不同数据统计周期合并为一条SQL语句
|
27天前
|
SQL 数据库
gbase 8a 数据库 SQL优化案例-关联顺序优化
gbase 8a 数据库 SQL优化案例-关联顺序优化
|
1月前
|
存储 NoSQL 分布式数据库
微服务架构下的数据库设计与优化策略####
本文深入探讨了在微服务架构下,如何进行高效的数据库设计与优化,以确保系统的可扩展性、低延迟与高并发处理能力。不同于传统单一数据库模式,微服务架构要求更细粒度的服务划分,这对数据库设计提出了新的挑战。本文将从数据库分片、复制、事务管理及性能调优等方面阐述最佳实践,旨在为开发者提供一套系统性的解决方案框架。 ####
|
1月前
|
存储 SQL 数据库
深入浅出后端开发之数据库优化实战
【10月更文挑战第35天】在软件开发的世界里,数据库性能直接关系到应用的响应速度和用户体验。本文将带你了解如何通过合理的索引设计、查询优化以及恰当的数据存储策略来提升数据库性能。我们将一起探索这些技巧背后的原理,并通过实际案例感受优化带来的显著效果。
49 4
|
1月前
|
SQL druid 数据库
如何进行数据库连接池的参数优化?
数据库连接池参数优化包括:1) 确定合适的初始连接数,考虑数据库规模和应用需求;2) 调整最大连接数,依据并发量和资源状况;3) 设置最小空闲连接数,平衡资源利用和响应速度;4) 优化连接超时时间,确保系统响应和资源利用合理;5) 配置连接有效性检测,定期检查连接状态;6) 调整空闲连接回收时间,适应访问模式并配合数据库超时设置。
|
1月前
|
SQL Oracle 关系型数据库
Oracle数据库优化方法
【10月更文挑战第25天】Oracle数据库优化方法
54 7
|
1月前
|
SQL 缓存 监控
数据库优化
【10月更文挑战第29天】数据库优化
40 1
|
1月前
|
存储 Oracle 关系型数据库
Oracle数据库优化策略
【10月更文挑战第25天】Oracle数据库优化策略
31 5

推荐镜像

更多