文档章节

性能优化(数据库配置)

这很耳东先生
 这很耳东先生
发布于 01/11 22:24
字数 2422
阅读 18
收藏 0

SQL优化

索引

数据库等待一个页从磁盘读取到缓存池的所需要的成本巨大的,无论我们是想要读取一个页面上的多条数据还是一条数据,都需要消耗约 10ms 左右的时间:

SQL优化顺序

 

一些关键配置

配置文件的位置

在哪里保存你的数据

查询缓存要不要开

写入频繁的数据库,不要开查询缓存

query_cache_size
Query_cache里的数据又怎么处理呢?首先要把Query_cache和该表相关的语句全部置为失效,然后在写入更新。那么如果Query_cache非常大,该表的查询结构又比较多,查询语句失效也慢,一个更新或是Insert就会很慢,这样看到的就是Update或是Insert怎么这么慢了。所以在数据库写入量或是更新量也比较大的系统,该参数不适合分配过大。而且在高并发,写入量大的系统,建议把该功能禁掉。
query_cache_limit
指定单个查询能够使用的缓冲区大小,缺省为1M
query_cache_min_res_unit
默认是4KB,设置值大对大数据查询有好处,但如果你的查询都是小数据查询,就容易造成内存碎片和浪费

其他缓存怎么开

读缓存,线程缓存,排序缓存

sort_buffer_size = 2M
connection级参数。太大将导致在连接数增高时,内存不足。
max_allowed_packet = 32M
网络传输中一次消息传输量的最大值。系统默认值 为1MB,最大值是1GB,必须设置1024的倍数。
join_buffer_size = 2M
和sort_buffer_size一样,该参数对应的分配内存也是每个连接独享
tmp_table_size = 256M
默认大小是 32M。GROUP BY 多不多的问题
max_heap_table_size = 256M
key_buffer_size = 2048M

索引的缓冲区大小,对于内存在4GB左右的服务器来说,该参数可设置为256MB或384MB。
read_buffer_size = 1M
read_rnd_buffer_size = 16M

进行排序查询时,MySql会首先扫描一遍该缓冲,以避免磁盘搜索
bulk_insert_buffer_size = 64M
批量插入数据缓存大小,可以有效提高插入效率,默认为8M

Innodb缓存

innodb_buffer_pool_size = 2048M
只需要用Innodb的话则可以设置它高达 70-80% 的可用内存。一些应用于 key_buffer 的规则有 — 如果你的数据量不大,并且不会暴增,那么无需把innodb_buffer_pool_size 设置的太大了。
innodb_additional_mem_pool_size = 16M
网络传输中一次消息传输量的最大值。系统默认值为1MB,最大值是1GB,必须设置1024的倍数。
innodb_log_files_in_group = 3
循环方式将日志文件写到多个文件。推荐设置为3
innodb_lock_wait_timeout = 120
InnoDB 有其内置的死锁检测机制,能导致未完成的事务回滚。innodb_file_per_table = 0独享表空间,关闭

连接数要多少

open_files_limit = 10240
允许打开的文件数
back_log = 600
短时间内的多少个请求可以被存在堆栈中
max_connections = 3000
MySQL允许最大的进程连接数,MySQL默认的最大连接数为100,MySQL服务器允许的最大连接数16384
max_connect_errors = 6000
设置每个主机的连接请求异常中断的最大次数,当超过该次数,MYSQL服务器将禁止host的连接请求
thread_cache_size = 300
重新利用保存在缓存中线程的数量
thread_concurrency = 8
thread_concurrency应设为总CPU核数的2倍
thread_stack = 192K
每个线程的堆栈大小,默认值足够大,可满足普通操作。可设置范围为128K至4GB,默认为192KB。

线程池很少配

线程池有关参数

thread_handling
表示线程池模型。
thread_pool_size
表示线程池的group个数,一般设置为当前CPU核心数目。理想情况下,一个group一个活跃的工作线程,达到充分利用CPU的目的。
thread_pool_stall_limit
用于timer线程定期检查group是否“停滞”,参数表示检测的间隔。
thread_pool_idle_timeout
当一个worker空闲一段时间后会自动退出,保证线程池中的工作线程在满足请求的情况下,保持比较低的水平。60秒
thread_pool_oversubscribe
该参数用于控制CPU核心上“超频”的线程数。这个参数设置值不含listen线程计数。
threadpool_high_prio_mode
表示优先队列的模式。
thread_pool_max_threads
限制线程池最大的线程数,超过将无法再创建更多的线程,默认为100000。
thread_pool_high_prio_tickets
最多语序多少次被放入高优先级队列中,默认为4294967295。只有在thread_pool_high_prio_mode为transactions的时候才有效果

线程处理的最小单位是statement(语句)

线程池实现在server端,通过创建一定数量的线程服务DB请求,相对于one-conection-per-thread的一个线程服务一个连接的方式,线程池服务的最小单位是语句,即一个线程可以对应多个活跃的连接。

慢查询日志

slow_query_log
是否开启慢查询日志,1表示开启,0表示关闭。
log-slow-queries
旧版(5.6以下版本)MySQL数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件host_name-slow.log
slow-query-log-file
新版(5.6及以上版本)MySQL数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件host_name-slow.log
long_query_time
慢查询阈值,当查询时间多于设定的阈值时,记录日志。
log_queries_not_using_indexes
未使用索引的查询也被记录到慢查询日志中(可选项)。
log_output
日志存储方式。log_output='FILE'表示将日志存入文件,默认值是'FILE'。log_output='TABLE'表示将日志存入数据库,这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式,配置的时候以逗号隔开即可,如:log_output='FILE,TABLE'。日志记录到系统的专用日志表中,要比记录到文件耗费更多的系统资源,因此对于需要启用慢查询日志,又需要能够获得更高的系统性能,那么建议优先记录到文件。

 

监控工具

innotop

安装:yum install innotop
启动:innotop -u root -p ‘123’
帮助:?

查询列表:Q

缓冲区:B

命令统计:C

lepus  http://www.lepus.cc/page/product

主从复制

主从复制配置

log-bin=mysql-bin
[必须]启用二进制日志
server-id=222
[必须]服务器唯一ID,默认是1,一般取IP最后一段
binlog-do-db=DB1
binlog-do-db=DB2 #如果备份多个数据库,重复设置这个选项即可
binlog-do-db=DB3 #需要同步的数据库,如果没有本行,即表示同步所有的数据库
binlog-ignore-db=mysql #被忽略的数据库 
rpl_semi_sync_slave_enabled =1 #启用半同步复制
rpl_semi_sync_master_timeout=milliseconds
设置此参数值(ms),为了防止半同步复制在没有收到确认的情况下发生堵塞,如果Master在超时之前没有收到任何确认,将恢复到正常的异步复制,并继续执行没有半同步的复制操作。
rpl_semi_sync_master_wait_no_slave={ON|OFF}
如果一个事务被提交,但Master没有任何Slave的连接。默认情况下,Master会在时间限制范围内继续等待Slave的连接,并确认该事务已经被正确的写到磁盘上。可以使用此参数选项关闭这种行为,在这种情况下,如果没有Slave连接,Master就会恢复到异步复制。
master
GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by 'q123456'; //一般不用root帐号,“%”表示所有客户端都可能连,只要帐号,密码正确,此处可用具体客户端IP代替,如192.168.145.226,加强安全。
slave
change master to master_host='192.168.145.222',master_user='mysync',master_password='q123456',
master_log_file='mysql-bin.000004',master_log_pos=308;

当有一个Slave失去连接后,不影响主库执行操作的时间,建库同样很快;但当所有Slave都关闭后,Master上建库时产生等待,当等待1s后才会执行库建操作;一旦超时,它会自动降级为异步,再执行不会再产生等待。--此时的客户端已经不存在了

INSTALL PLUGIN rpl_semi_sync_slave SONAME'semisync_slave.so';

Rpl_semi_sync_master_clients
此状态变量报告了支持和注册的半同步复制已连接的Slave数量

Rpl_semi_sync_master_status
Master半同步复制的状态,1是活动状态,0是非活动状态--要么是因为他没有被启用,或是因为它已经恢复到异步复制。

Rpl_semi_sync_slave_status
Slave半同步复制的状态,1是已经被启用,且IO线程正在运行,0是非活动状态

MySQL在加载并开启Semi-sync插件后,每一个事务需等待备库接收日志后才返回给客户端。如果做的是小事务,两台主机的延迟又较小,则Semi-sync可以实现在性能很小损失的情况下的零数据丢失。所以我们可以做多个备库,任何一个备库接收完成日志后,主库就可以返回给客户端了。

几个忠告

1、拓宽知识面(天文,地理,历史,文化,金融)
2、深入的了解行业知识(IT行业最牛逼的是别人只需学一个专业)
3、逻辑优化同样是优化(新补充的功能考虑普适性)
4、协作还是独行,这是个问题
5、品性的升华,远胜于技能的掌握

 

 

© 著作权归作者所有

共有 人打赏支持
这很耳东先生
粉丝 2
博文 58
码字总数 183253
作品 0
广州
私信 提问
MySQL数据库的性能的影响分析及其优化

MySQL数据库的性能的影响 一. 服务器的硬件的限制 二. 服务器所使用的操作系统 三. 服务器的所配置的参数设置不同 四. 数据库存储引擎的选择 五. 数据库的参数配置的不同 六. (重点)数据库的...

Panda_Jerry
2017/11/04
0
0
不再治标不治本,性能优化苦手们的良方来了!

作者介绍 吴虞,SQL专家云团队成员,擅长解决SQL Server数据库性能、高可用、负载均衡等问题。 性能优化是数据库运维人员和中、高级软件开发人员的必备技能,很多时候老司机和新司机的区别就...

吴虞
2017/01/17
0
0
DBA要失业了?看ML如何自动优化数据库

译者介绍 杨志洪,DBAplus社群联合发起人,新炬网络首席布道师。Oracle ACE、OCM、《Oracle核心技术》译者。数据管理专家,拥有十余年电信、银行、保险等大型行业核心系统Oracle数据库运维支...

杨志洪 译
2017/06/04
0
0
Mysql:优化分析(值得深思)

Mysql优化分析 Mysql作为现在的主流关系型数据库,在优化上值得深思啊! 不能只停留在如何使用Mysql,而不去思考如何优化的问题啊。 在谈到Mysql优化方面,Mysql在这方面有专门的文档说明这个...

王木东
2018/07/13
0
0
Mysql 性能优化——必胜之道

mysql的性能优化是运维和DBA们常常面对的问题,也是各大公司招聘人才时看中的要点之一。性能优化听上去很难,似乎只有大神才能做,然而,mysql的性能优化绝不是运维独自一个能完成的,DBA、开...

32氪
2017/07/31
0
0

没有更多内容

加载失败,请刷新页面

加载更多

发布xxl-job executor dotnet core 执行器的实现

DotXxlJob [github][https://github.com/xuanye/DotXxlJob] xxl-job的dotnet core 执行器实现,支持XXL-JOB 2.0+ 1 XXL-JOB概述 [XXL-JOB][1]是一个轻量级分布式任务调度平台,其核心设计目标...

假正经哥哥
今天
2
0
mysql 查询当天、本周,本月,上一个月的数据

今天 select * from 表名 where to_days(时间字段名) = to_days(now()); 昨天 SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1 近7天 SELECT * FROM 表名 wher......

BraveLN
今天
3
0
Spring 事务初始化源码分析

相关文章 Spring 事务使用详解 Spring AOP 创建代理的源码解析 Spring AOP 注解方式源码解析 Spring AOP 功能使用详解 Spring 的 getBean 方法源码解析 Spring bean 创建过程源码解析 Spring...

TSMYK
今天
1
0
Android Multimedia框架总结(六)C++中MediaPlayer的C/S架构

前面几节中,都是通过java层调用到jni中,jni向下到c++层并未介绍 看下Java层一个方法在c++层 MediaPlayer后续过程 frameworks/av/media/libmedia/MediaPlayer.cpp 找一个我们之前熟悉的setDa...

天王盖地虎626
今天
3
0
【Linux】【MySQL】CentOS7安装最新版MySQL8.0.13(最新版MySQL从安装到运行)

1、前言   框框博客在线报时:2018-11-07 19:31:06   当前MySQL最新版本:8.0.13 (听说比5.7快2倍)   官方之前表示:MySQL 8.0 正式版 8.0.11 已发布,MySQL 8 要比 MySQL 5.7 快 2 ...

Code辉
今天
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部