运维视点浅谈MySQL数据库优化51CTO博客 - 众发娱乐

运维视点浅谈MySQL数据库优化51CTO博客

2019年03月01日14时23分06秒 | 作者: 寄松 | 标签: 数据库,数据,优化 | 浏览: 139

  一个老练的数据库架构并不是一开始规划就具有高可用、高弹性等特性的,它是跟着用户量的增加,根底架构才逐步完善。这篇博文首要谈MySQL数据库开展周期中所面临的问题及优化计划,暂时抛开前端运用不说,大致分为以下五个阶段:

1、数据库表规划

  项目立项后,开发部依据产品部需求开发项目,开发工程师作业其间一部分就是对表结构规划。关于数据库来说,这点很重要,假如规划不妥,会直接影响拜访速度和用户体会。影响的要素许多,比方慢查询、低效的查询句子、没有恰当树立索引、数据库阻塞(死锁)等。当然,有测验工程师的团队,会做压力测验,找bug。关于没有测验工程师的团队来说,大多数开发工程师初期不会太多考虑数据库规划是否合理,而是赶快完结功用完成和交给,等项目有必定拜访量后,躲藏的问题就会露出,这时再去修正就不是这么简略的事了。

2、数据库布置

  该运维工程师进场了,项目初期拜访量不会很大,所以单台布置足以应对在1500左右的QPS(每秒查询率)。考虑到高可用性,可选用MySQL主从仿制+Keepalived做双击热备,常见集群软件有Keepalived、Heartbeat。

双机热备博文:数据库功用优化

  假如将MySQL布置到一般的X86效劳器上,在不经过任何优化情况下,MySQL理论值正常可以处理2000左右QPS,经过优化后,有或许会提升到2500左右QPS,不然,拜访量当抵达1500左右并发衔接时,数据库处理功用就会变慢,并且硬件资源还很殷实,这时就该考虑软件问题了。那么怎样让数据库最大化发挥功用呢?一方面可以单台运转多个MySQL实例让效劳器功用发挥到最大化,另一方面是对数据库进行优化,往往操作体系和数据库默许装备都比较保存,会对数据库发挥有必定约束,可对这些装备进行恰当的调整,尽或许的处理更多衔接数。

详细优化有以下三个层面:

  3.1 数据库装备优化

  MySQL常用有两种存储引擎,一个是MyISAM,不支撑事务处理,读功用处理快,表等级锁。另一个是InnoDB,支撑事务处理(ACID),规划方针是为处理大容量数据发挥最大化功用,行等级锁。

  表锁:开支小,确定粒度大,发作死锁概率高,相对并发也低。

  行锁:开支大,确定粒度小,发作死锁概率低,相对并发也高。

  为什么会呈现表锁和行锁呢?首要是为了确保数据的完整性,举个比方,一个用户在操作一张表,其他用户也想操作这张表,那么就要等第一个用户操作完,其他用户才干操作,表锁和行锁就是这个作用。不然多个用户一同操作一张表,肯定会数据发生抵触或许反常。

  依据以上看来,运用InnoDB存储引擎是最好的挑选,也是MySQL5.5今后版别中默许存储引擎。每个存储引擎相相关参数比较多,以下列出首要影响数据库功用的参数。

  公共参数默许值:

max_connections = 151
#一同处理最大衔接数,引荐设置最大衔接数是上限衔接数的80%左右   
sort_buffer_size = 2M
#查询排序时缓冲区巨细,只对order by和group by起作用,可增大此值为16M
open_files_limit = 1024 
#翻开文件数约束,假如show global status like open_files检查的值等于或许大于open_files_limit值时,程序会无法衔接数据库或卡死

  MyISAM参数默许值:

key_buffer_size = 16M
#索引缓存区巨细,一般设置物理内存的30-40%
read_buffer_size = 128K  
#读操作缓冲区巨细,引荐设置16M或32M
query_cache_type = ON
#翻开查询缓存功用
query_cache_limit = 1M  
#查询缓存约束,只要1M以下查询成果才会被缓存,防止成果数据较大把缓存池掩盖
query_cache_size = 16M  
#检查缓冲区巨细,用于缓存SELECT查询成果,下一次有相同SELECT查询将直接从缓存池回来成果,可恰当成倍增加此值

  InnoDB参数默许值:

innodb_buffer_pool_size = 128M
#索引和数据缓冲区巨细,一般设置物理内存的60%-70%
innodb_buffer_pool_instances = 1    
#缓冲池实例个数,引荐设置4个或8个
innodb_flush_log_at_trx_commit = 1  
#要害参数,0代表大约每秒写入到日志并同步到磁盘,数据库毛病会丢掉1秒左右事务数据。1为每履行一条SQL后写入到日志并同步到磁盘,I/O开支大,履行完SQL要等候日志读写,功率低。2代表只把日志写入到体系缓存区,再每秒同步到磁盘,功率很高,假如效劳器毛病,才会丢掉事务数据。对数据安全性要求不是很高的引荐设置2,功用高,修正后作用显着。
innodb_file_per_table = OFF  
#默许是同享表空间,同享表空间idbdata文件不断增大,影响必定的I/O功用。引荐敞开独立表空间形式,每个表的索引和数据都存在自己独立的表空间中,可以完成单表在不同数据库中移动。
innodb_log_buffer_size = 8M  
#日志缓冲区巨细,因为日志最长每秒钟改写一次,所以一般不必超越16M

  3.2 体系内核优化

  大多数MySQL都布置在linux体系上,所以操作体系的一些参数也会影响到MySQL功用,以下对linux内核进行恰当优化。

net.ipv4.tcp_fin_timeout = 30
#TIME_WAIT超时时刻,默许是60s
net.ipv4.tcp_tw_reuse = 1    
#1表明敞开复用,答应TIME_WAIT socket从头用于新的TCP衔接,0表明封闭
net.ipv4.tcp_tw_recycle = 1  
#1表明敞开TIME_WAIT socket快速收回,0表明封闭
net.ipv4.tcp_max_tw_buckets = 4096   
#体系坚持TIME_WAIT socket最大数量,假如超出这个数,体系将随机铲除一些TIME_WAIT并打印正告信息
net.ipv4.tcp_max_syn_backlog = 4096
#进入SYN行列最大长度,加大行列长度可包容更多的等候衔接

  在linux体系中,假如进程翻开的文件句柄数量超越体系默许值1024,就会提示“too many files open”信息,所以要调整翻开文件句柄约束。

# vi /etc/security/limits.conf  #参加以下装备,*代表一切用户,也可以指定用户,重启体系收效
* soft nofile 65535
* hard nofile 65535
# ulimit -SHn 65535   #马上收效

  3.3 硬件装备

  加大物理内存,进步文件体系功用。linux内核会从内存平分配出缓存区(体系缓存和数据缓存)来寄存热数据,经过文件体系推迟写入机制,等满意条件时(如缓存区巨细抵达必定百分比或许履行sync指令)才会同步到磁盘。也就是说物理内存越大,分配缓存区越大,缓存数据越多。当然,效劳器毛病会丢掉必定的缓存数据。

  SSD硬盘替代SAS硬盘,将RAID等级调整为RAID1+0,相关于RAID1和RAID5有更好的读写功用(IOPS),究竟数据库的压力首要来自磁盘I/O方面。

4、数据库架构扩展

  跟着事务量越来越大,单台数据库效劳器功用已无法满意事务需求,该考虑加机器了,该做集群了~~~。首要思维是分化单台数据库负载,打破磁盘I/O功用,热数据寄存缓存中,下降磁盘I/O拜访频率。

  4.1 主从仿制与读写别离

  因为出产环境中,数据库大多都是读操作,所以布置一主多从架构,主数据库担任写操作,并做双击热备,多台从数据库做负载均衡,担任读操作,干流的负载均衡器有LVS、HAProxy、Nginx。

  怎样来完成读写别离呢?大多数企业是在代码层面完成读写别离,功率比较高。另一个种办法经过署理程序完成读写别离,企业中运用较少,常见署理程序有MySQL Proxy、Amoeba。在这样数据库集群架构中,大大增加数据库高并发才能,处理单台功用瓶颈问题。假如从数据库一台从库能处理2000 QPS,那么5台就能处理1w QPS,数据库横向扩展性也很简略。

  有时,面临许多写操作的运用时,单台写功用达不到事务需求。假如做双主,就会遇到数据库数据不一致现象,发生这个原因是在运用程序不同的用户会有或许操作两台数据库,一同的更新操作形成两台数据库数据库数据发作抵触或许不一致。在单库时MySQL运用存储引擎机制表锁和行锁来确保数据完整性,怎样在多台主库时处理这个问题呢?有一套依据perl言语开发的主从仿制管理东西,叫MySQL-MMM(Master-Master replication managerfor Mysql,Mysql主主仿制管理器),这个东西最大的长处是在同一时刻只提供一台数据库写操作,有用确保数据一致性。

  主从仿制博文: 读写别离博文:

 MySQL-MMM博文: 4.2 增加缓存

  给数据库增加缓存体系,把热数据缓存到内存中,假如缓存中有要恳求的数据就不再去数据库中回来成果,进步读功用。缓存完成有本地缓存和分布式缓存,本地缓存是将数据缓存到本地效劳器内存中或许文件中。分布式缓存可以缓存海量数据,扩展性好,干流的分布式缓存体系有memcached、redis,memcached功用安稳,数据缓存在内存中,速度很快,QPS可达8w左右。假如想数据耐久化就挑选用redis,功用不低于memcached。

  作业进程:

  

  4.3 分库

  分库是依据事务不同把相关的表切分到不同的数据库中,比方web、bbs、blog等库。假如事务量很大,还可将切分后的库做主从架构,进一步防止单个库压力过大。

  4.4 分表

  数据量的日剧增加,数据库中某个表有几百万条数据,导致查询和刺进耗时太长,怎样能处理单表压力呢?你就该考虑是否把这个表拆分红多个小表,来减轻单个表的压力,进步处理功率,此办法称为分表。

  分表技能比较费事,要修正程序代码里的SQL句子,还要手动去创立其他表,也可以用merge存储引擎完成分表,相对简略许多。分表后,程序是对一个总表进行操作,这个总表不寄存数据,只要一些分表的联系,以及更新数据的办法,总表会依据不同的查询,将压力分到不同的小表上,因而进步并发才能和磁盘I/O功用。

  分表分为笔直拆分和水平拆分:

  笔直拆分:把本来的一个许多字段的表拆分多个表,处理表的宽度问题。你可以把不常用的字段独自放到一个表中,也可以把大字段独立放一个表中,或许把相关亲近的字段放一个表中。

  水平拆分:把本来一个表拆分红多个表,每个表的结构都相同,处理单表数据量大的问题。

  4.5 分区

  分区就是把一张表的数据依据表结构中的字段(如range、list、hash等)分红多个区块,这些区块可以在一个磁盘上,也可以在不同的磁盘上,分区后,表面上仍是一张表,但数据散列在多个方位,这样一来,多块硬盘一同处理不同的恳求,然后进步磁盘I/O读写功用,完成比较简略。

注:增加缓存、分库、分表和分区首要由程序猿来完成。

5、数据库保护

  数据库保护是运维工程师或许DBA首要作业,包含功用监控、功用剖析、功用调优、数据库备份和康复等。

  5.1 功用状况要害目标

  QPS,Queries Per Second:每秒查询数,一台数据库每秒可以处理的查询次数

  TPS,Transactions Per Second:每秒处理事务数

  经过show status检查运转状况,会有300多条状况信息记载,其间有几个值帮可以咱们核算出QPS和TPS,如下:

  Uptime:效劳器现已运转的实践,单位秒

  Questions:现已发送给数据库查询数

  Com_select:查询次数,实践操作数据库的

  Com_insert:刺进次数

  Com_delete:删去次数

  Com_update:更新次数

  Com_commit:事务次数

  Com_rollback:回滚次数

  那么,核算办法来了,依据Questions核算出QPS:

  mysql> show global status like Questions;
  mysql> show global status like Uptime;

  QPS = Questions / Uptime

  依据Com_commit和Com_rollback核算出TPS:

  mysql> show global status like Com_commit;
  mysql> show global status like Com_rollback;
  mysql> show global status like Uptime;

  TPS = (Com_commit + Com_rollback) / Uptime

  另一核算办法:依据Com_select、Com_insert、Com_delete、Com_update核算出QPS

  mysql> show global status where Variable_name in(com_select,com_insert,com_delete,com_update);

  等候1秒再履行,获取距离差值,第2次每个变量值减去第一次对应的变量值,就是QPS

  TPS核算办法:

  mysql> show global status where Variable_name in(com_insert,com_delete,com_update);

  核算TPS,就不算查询操作了,核算出刺进、删去、更新四个值即可。

  经网友对这两个核算办法的测验得出,当数据库中myisam表比较多时,运用Questions核算比较精确。当数据库中innodb表比较多时,则以Com_*核算比较精确。

  5.2 敞开慢查询日志

  MySQL敞开慢查询日志,剖分出哪条SQL句子比较慢,运用set设置变量,重启效劳失效,可以在my.cnf增加参数永久收效。

mysql> set global slow-query-log=on  #敞开慢查询功用
mysql> set global slow_query_log_file=/var/log/mysql/mysql-slow.log;  #指定慢查询日志文件方位
mysql> set global log_queries_not_using_indexes=on;   #记载没有运用索引的查询
mysql> set global long_query_time=1;   #只记载处理时刻1s以上的慢查询

  剖析慢查询日志,可以运用MySQL自带的mysqldumpslow东西,剖析的日志较为简略。

  # mysqldumpslow -t 3 /var/log/mysql/mysql-slow.log    #检查最慢的前三个查询

  也可以运用percona公司的pt-query-digest东西,日志剖析功用全面,可剖析slow log、binlog、general log。

  剖析慢查询日志:pt-query-digest /var/log/mysql/mysql-slow.log

  剖析binlog日志:mysqlbinlog mysql-bin.000001 >mysql-bin.000001.sql 

  pt-query-digest type=binlog mysql-bin.000001.sql 

  剖析一般日志:pt-query-digest type=genlog localhost.log

  5.3 数据库备份

  备份数据库是最根本的作业,也是最重要的,不然结果很严重,你懂得!但因为数据库比较大,上百G,往往备份都很消耗时刻,所以就该挑选一个功率高的备份战略,关于数据量大的数据库,一般都选用增量备份。常用的备份东西有mysqldump、mysqlhotcopy、xtrabackup等,mysqldump比较适用于小的数据库,因为是逻辑备份,所以备份和康复耗时都比较长。mysqlhotcopy和xtrabackup是物理备份,备份和康复速度快,不影响数据库效劳情况下进行热复制,主张运用xtrabackup,支撑增量备份。

  Xtrabackup备份东西运用博文: 5.4 数据库修正

  有时候MySQL效劳器俄然断电、反常封闭,会导致表损坏,无法读取表数据。这时就可以用到MySQL自带的两个东西进行修正,myisamchk和mysqlcheck。

  myisamchk:只能修正myisam表,需求中止数据库

  常用参数:

  -f force    强制修正,掩盖老的临时文件,一般不运用

  -r recover  康复形式

  -q quik     快速康复

  -a analyze  剖析表

  -o safe-recover 老的康复形式,假如-r无法修正,可以运用此参数试试

  -F fast     只检查没有正常封闭的表

  快速修正weibo数据库:

  # cd /var/lib/mysql/weibo 

  # myisamchk -r -q *.MYI

  mysqlcheck:myisam和innodb表都可以用,不需求中止数据库,如修正单个表,可在数据库后边增加表名,以空格切割

  常用参数:

  -a  all-databases  检查一切的库

  -r  repair   修正表

  -c  check    检查表,默许选项

  -a  analyze  剖析表

  -o  optimize 优化表

  -q  quik   最快检查或修正表

  -F  fast   只检查没有正常封闭的表

  快速修正weibo数据库:

  mysqlcheck -r -q -uroot -p123 weibo 

  5.5 别的,检查CPU和I/O功用办法

  #检查CPU功用

  #参数-P是显现CPU数,ALL为一切,也可以只显现第几颗CPU

  #检查I/O功用

  #参数-m是以M单位显现,默许K

  #%util:当抵达100%时,阐明I/O很忙。

  #await:恳求在行列中等候时刻,直接影响read时刻。

  I/O极限:IOPS(r/s+w/s),一般RAID0/10在1200左右。(IOPS,每秒进行读写(I/O)操作次数)

  I/O带宽:在次序读写形式下SAS硬盘理论值在300M/s左右,SSD硬盘理论值在600M/s左右。


  以上是自己运用MySQL三年来总结的一些首要优化计划,才能有限,有些不太全面,但这些根本可以满意中小型企业数据库需求。

  因为联系型数据库初衷规划约束,一些BAT公司海量数据放到联系型数据库中,在海量数据查询和剖析方面现已达不到更好的功用。因而NoSQL火起来了,非联系型数据库,大数据量,具有高功用,一同也弥补了联系型数据库某方面缺乏,逐渐大多数公司现已将部分事务数据库寄存到NoSQL中,如MongoDB、HBase等。数据存储方面选用分布式文件体系,如HDFS、GFS等。海量数据核算剖析选用Hadoop、Spark、Storm等。这些都是与运维相关的前沿技能,也是在存储方面首要学习目标,小伙伴们一同加油吧!哪位博友有更好的优化计划,欢迎沟通哦。

欢迎你参加技能群一同沟通:323779636(Shell/Python运维开发群)


版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表众发娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章