如何免费创建个人网站,南平高速建设有限公司网站,无货源一件代发平台,厦门微网站制作文章目录 1.从哪些角度去考虑MySQL的优化2.数据库服务器的选型3.从操作系统层面去优化MySQL数据库3.1.关于CPU方面的优化3.2.关于内存方面的优化3.3.关于磁盘IO方面 4.应用端的优化5.数据库系统优化工具6.数据库系统参数优化6.1.最大连接数的优化#xff08;max_connectionsmax_connections6.2.暂存连接数back_log6.3.非交互等待时间wait_timeout和交互等待时间interactive_timeout6.5.索引缓冲区大小key_buffer_size6.6.查询缓存大小query_cache_size6.7.安全计数器max_connect_errors 6.8.排序线程缓冲区大小sort_buffer_size6.9.最大的接收数据包大小max_allowed_packet6.10.多表联查缓冲区大小join_buffer_size6.11.服务器线程缓存数量thread_cache_size6.12.InnoDB存缓冲区大小innodb_buffer_pool_size6.13.InnoDB线程的并发数量innodb_thread_concurrency6.13.Log Buffer数据写入日志并刷新到磁盘的时间点innodb_flush_log_at_trx_commit6.14.数据日志缓冲区的大小innodb_log_buffer_size6.15.数据日志文件大小innodb_log_file_size6.16.数据日志文件数量innodb_log_files_in_group6.17.读缓冲区的大小read_buffer_size6.18.随机读缓冲区的大小read_rnd_buffer_size6.19.批量插入数据的缓冲区大小bulk_insert_buffer_size6.20.Binlog日志的优化6.21.安全参数的优化6.22.主从优化 7.优化后的配置文件内容 1.从哪些角度去考虑MySQL的优化
MySQL的优化主要从硬件层面、应用程序层面、数据库层面等三个方面进行优化。
存储、主机和操作系统 从主机架构的稳定性、IO的规划和配置、存储使用SSD硬盘Swap方面的优化、OS内核参数等角度考虑优化。 应用程序 从应用程序的稳定性、索引优化、性能优化、SQL语句检索、并发串行读取数据等角度优化。 数据库优化 从内存、数据库结构、多实例角度优化。
2.数据库服务器的选型
1主机方面
真实的硬件PC Server: DELL R系列 华为浪潮HP联想。 云产品ECS、数据库RDS、DRDS。 IBM 小型机 P6 570 595 P7 720 750 780 P8 。
2CPU根据数据库类型
OLTP、OLAP IO密集型线上系统OLTP主要是IO密集型的业务高并发。 CPU密集型数据分析数据处理OLAPcpu密集型的需要CPU高计算能力i系列IBM power系列。 CPU密集型 I 系列的主频很高核心少。 IO密集型 E系列至强主频相对低核心数量多。
3内存
建议2-3倍cpu核心数量。
4磁盘选择
SATA-III SAS Fc SSDsata pci-e ssd Flash。 主机 RAID卡的BBU(Battery Backup Unit)关闭。
5存储
根据存储数据种类的不同选择不同的存储设备配置合理的RAID级别(raid5、raid10、热备盘) 。 r0 :条带化 ,性能高 r1 :镜像安全 r5 :校验条带化安全较高性能较高读写性能较低 适合于读多写少 r10安全性能都很高最少四块盘浪费一半的空间高IO要求
6网络
1、硬件买好的单卡单口 2、网卡绑定bonding交换机堆叠
3.从操作系统层面去优化MySQL数据库
我们优化数据库时首先从操作系统层面去优化MySQL可以根据CPU、内存、IO进行分析从而优化MySQL
3.1.关于CPU方面的优化
系统为每个程序分配CPU时是以时间来片划分的MySQL运行过程中我们可以通过Top命令观察CPU的平均使用情况。 id空闲的CPU时间片占比。waCPU用来等待的时间片占比。 MySQL服务器wa占比较高很有可能是有大并发事务在运行、全表扫描、锁等因为wa状态是等待的时间片占比MySQL服务器是在内存中操作数据的从磁盘读取数据到内存如果一次性读取大量的数据CPU可能就会处于等待中。由MySQL引起的wa高的原因可能是锁、IO、索引。 us用户程序工作所占用的时间片占比这个值越大说明都是服务再使用CPU并没有造成CPU的浪费。sy内核工作花费的CPU时间片占比当系统版本越可靠只有在系统启动的时候才会消耗CPU占比系统启动完成后几乎不会占用CPU当sy占比很高时就可能由系统存在Bug、中病毒、高并发连接、锁造成。
也可以按1查看每个CPU核心的分别使用情况。 系统中的计算程序运行数据处理和控制申请资源释放资源属于有效的CPU工作时间片等待IO属于无效的CPU工作时间片。
在生产环境中我们要判断CPU的多核心有没有被充分的利用当并发参数设置不合理时就会导致CPU的核心使用不均匀。
3.2.关于内存方面的优化
关于内存方面的优化也是很重要的关于系统的内存使用我们还可以在Top命令中看到对于数据库服务器主要关注availMem剩余内存和buff/cache的内存使用。 由于MySQL的数据处理都是在内存中进行的因此MySQL自己就对内存方面做出了优化开启了回收策略。
但是在操作系统层面CentOS系统会开启Swap交换分区当CentOS7系统的内存使用率达到70%的时候就会使用Swap分区但是Swap分区非常慢性能很差Swap本身就是硬盘中的空间因此优化MySQL服务器时建议将Swap关闭不使用Swap。
echo 0 /proc/sys/vm/swappinessvim /etc/sysctl.conf
vm.swappiness0sysctl -p 这个参数决定了Linux是倾向于使用swap还是倾向于释放文件系统cache。在内存紧张的情况下数值越低越倾向于释放文件系统cache。
当然这个参数只能减少使用swap的概率并不能避免Linux使用swap。
修改MySQL的配置参数innodb_flush_method开启O_DIRECT模式这种情况下InnoDB的buffer pool会直接绕过文件系统cache来访问磁盘但是redo log依旧会使用文件系统cache。值得注意的是Redo log是覆写模式的即使使用了文件系统的cache也不会占用太多3.3.关于磁盘IO方面
通过以下命令可以分析磁盘IO的性能首先写入一个1G的文件然后观察IO的状态。
# dd if/dev/zero of/tmp/bigfile bs1M count1024
记录了10240 的读入
记录了10240 的写出
1073741824字节(1.1 GB)已复制1.06451 秒1.0 GB/秒# iostat -dm 1
Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
sda 104.00 0.00 52.00 0 52
dm-0 104.00 0.00 52.00 0 52
dm-1 0.00 0.00 0.00 0 0#获取IO的使用率时以M为单位显示每秒刷新1次一般情况下IO要和CPU参照对比分析CPU高的情况下IO也会很高如果CPU的wait很高IO很低那么有可能就是磁盘出问题如果CPU的sys很高IO很低那么可能就是数据库层面出问题可能是锁需要进一步的分析和判断。
也可以使用vmsta命令分析系统的内存、swap、io、system、cpu的使用情况。 IO调度策略的优化
centos 7 默认是deadline
cat /sys/block/sda/queue/scheduler#临时修改为deadline(centos6)
echo deadline /sys/block/sda/queue/scheduler
vi /boot/grub/grub.conf
更改到如下内容:
kernel /boot/vmlinuz-2.6.18-8.el5 ro rootLABEL/ elevatordeadline rhgb quietIO raidno lvmlvm对于数据库安全性略低ext4或xfsssdIO调度策略
提前规划好以上所有问题减轻MySQL优化的难度。4.应用端的优化
开发很有可能会写一些烂SQL作为运维一定要注意开发写的烂SQL可以上线SQL审核平台严格审核开发提交的SQL。
避免业务逻辑错误避免锁争用需要我们DBA深入业务或者要和开发人员\业务人员配合实现。
5.数据库系统优化工具
工具作用show status查看MySQL服务器状态信息show variables查看MySQL系统参数show index查看索引信息show processlist查看当前数据库中的线程信息show slave status查看从库的状态show engine innodb status查看InnoDB的状态desc /explain分析SQL的执行过程slowlog慢日志pt系列pt-query-digest、pt-osc、pt-index监控mysql服务器mysqlslap基准测试工具sysbench多线程性能测试工具information_schema通过MySQL自带的视图分析performance_schema通过MySQL自带的视图分析sys通过MySQL自带的视图分析
6.数据库系统参数优化
6.1.最大连接数的优化max_connections
MySQL配置最大连接数的参数是max_connections如果服务器的并发请求量比较大可以去调高这个值当然是要在服务器能够承受的压力下去调整这个参数的值随着连接数越来越多MySQL会为每个连接提供缓冲区就会导致开销越来越多的内存对于连接数这个值不能随便去调高。
再调整最大连接数之前先使用压测工具测试一下MySQL服务器可以承载多少个并发连接
mysqlslap --defaults-file/etc/my.cnf \
--concurrency100 --iterations1 --create-schemadb_1 \
--queryselect * from db_1.t1000w where k2FGCD engineinnodb \
--number-of-queries200 -uroot -p123 -verbose--concurrency100并发连接数
--number-of-queries200请求次数压测工具在使用过程中如果并发连接数达到了服务器的极限就会报错to manay connection此时就需要对连接数进行优化。设置最大连接数的依据我们可以观察当前数据库中的连接数然后在观察系统设置的最大连接数从而进行合理的调整。
#默认情况下数据库的连接数是151个生产环境中建议调整到1k-2k当然也要根据服务器硬件资源去调整。
mysqlselect max_connections;
-------------------
| max_connections |
-------------------
| 151 |
-------------------
1 row in set (0.00 sec)#当前数据库的连接数
rootlocalhost localhost 14:42:34 (none)show status like Max_used_connections;
-----------------------------
| Variable_name | Value |
-----------------------------
| Max_used_connections | 1 |
-----------------------------设置最大连接数的方法
修改/etc/my.cnf文件在[mysqld]下面添加如下
Max_connections1024补充:1.开启数据库时,我们可以临时设置一个比较大的测试值2.观察show status like Max_used_connections;变化3.如果max_used_connections跟max_connections相同,那么就是max_connections设置过低或者超过服务器的负载上限了低于10%则设置过大. 6.2.暂存连接数back_log
暂存连接数的配置参数是back_log也是与连接数相关的一个参数主要的功能是当数据库的连接数达到max_connections时再提供一些额外的连接数例如当并发连接数达到了1024暂存连接数设置了100个当达到了1024时会再分配100个连接不至于在客户端报错。
不过这个参数一般很少设置主要都在最大连接数中设置。
修改/etc/my.cnf文件在[mysqld]下面添加如下
back_log10246.3.非交互等待时间wait_timeout和交互等待时间interactive_timeout
非交互等待时间的配置参数是wait_timeout指的是MySQL在关闭一个非交互式的连接之前所需要等待的时间也就是说一个连接多长时间内不操作就断开。
交互等待时间的配置参数是interactive_timeout指的是交互模式下多长时间不操作就断开。
这两个时间都不建议设置的太长或者太短太长造成一个连接存在的时间太长太短会导致频繁的断开。
修改/etc/my.cnf文件在[mysqld]下面添加如下
interactive_timeout120
wait_timeout3600 6.5.索引缓冲区大小key_buffer_size
索引缓冲区大小的参数是key_buffer_size通过这个参数可以决定索引处理的速度尤其是索引读的速度主要是在内存中加大索引的缓冲区大小。
这个参数与myisam表有点关系不过最主要的还是在InnoDB引擎下当使用多表联查、子查询、union时此参数会在内存中创建临时表而不会去磁盘中创建临时表当SQL执行完毕后自动清理临时表。
对于临时表有两种创建方式一种是在内存中创建就与key_buffer_size参数有关另一种是在磁盘中创建在磁盘中创建效率低因此在优化MySQL时一定要设置这个参数。
设置key_buffer_size参数时我们可以先看一下当前数据库中临时表都是在哪里创建的
mysql show status like created_tmp%;
--------------------------------
| Variable_name | Value |
--------------------------------
| Created_tmp_disk_tables | 0 | #磁盘中创建的临时表个数
| Created_tmp_files | 6 | #临时文件个数无关
| Created_tmp_tables | 1 | #内存中创建的临时表个数
--------------------------------key_buffer_size参数的大小到底设置成多少合适呢其实也有有依据的我们按照一个公式来计算 计算内存临时表的占比 Created_tmp_tables/(Created_tmp_disk_tables Created_tmp_tables)X100% 1/(10)*100100% 用内存临时表除内存临时表磁盘临时表最终就可以得到内存临时表的占比按照我们的环境内存临时表的占比是100%占比越高性能越强。 计算磁盘临时表的占比 Created_tmp_disk_tables/(Created_tmp_disk_tables Created_tmp_tables) X100% 0/(01)*100%0% 用磁盘临时表除磁盘临时表内存临时表最终就可以得到磁盘临时表的占比按照我们的环境内存临时表的占比是0%占比越低性能越强。 当我们再设置key_buffer_size参数时可以进行微调一次加2M观察内存临时表的占比一般占比在5%~10%以内就说明我们key_buffer_size这个参数的值给到位了。
另外我们可以对内存临时表的占比进行zabbix监控当占比超过10%时是就告警然后我们再去微调key_buffer_size参数的大小需要注意的是mysqldump在备份的时候会在磁盘中创建很多临时表一定要避开备份时刻的监控否则就会频繁告警说内存临时表的占比超过了10%。
设置key_buffer_size参数
修改/etc/my.cnf文件在[mysqld]下面添加如下
key_buffer_size64M6.6.查询缓存大小query_cache_size
查询缓存的配置参数是query_cache_size作用就是对于同样的查询语句完整一次查询后第二次查询直接从缓冲区中读取结果目前已经不怎么使用了因为有缓存数据库redis可以简单了解一下。
查询缓存参数设置的依据主要判断下面几个参数的状态值
mysql show status like %Qcache%;
----------------------------------
| Variable_name | Value |
----------------------------------
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031360 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2002 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
-------------------------------------------------------状态说明--------------------
Qcache_free_blocks缓存中相邻内存块的个数。如果该值显示较大则说明Query Cache 中的内存碎片较多了FLUSH QUERY CACHE会对缓存中的碎片进行整理从而得到一个空闲块。
注当一个表被更新之后和它相关的cache
blocks将被free。但是这个block依然可能存在队列中除非是在队列的尾部。可以用FLUSH QUERY CACHE语句来清空free blocksQcache_free_memoryQuery Cache 中目前剩余的内存大小。通过这个参数我们可以较为准确的观察出当前系统中的Query Cache 内存大小是否足够是需要增加还是过多了。Qcache_hits表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大缓存效果越理想。Qcache_inserts表示多少次未命中然后插入意思是新来的SQL请求在缓存中未找到不得不执行查询处理执行查询处理后把结果insert到查询缓存中。这样的情况的次数越多表示查询缓存应用到的比较少效果也就不理想。当然系统刚启动后查询缓存是空的这很正常。Qcache_lowmem_prunes
多少条Query因为内存不足而被清除出QueryCache。通过“Qcache_lowmem_prunes”和“Qcache_free_memory”相互结合能够更清楚的了解到我们系统中Query Cache 的内存大小是否真的足够是否非常频繁的出现因为内存不足而有Query 被换出。这个数字最好长时间来看如果这个数字在不断增长就表示可能碎片非常严重或者内存很少。上面的free_blocks和free_memory可以告诉您属于哪种情况Qcache_not_cached不适合进行缓存的查询的数量通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。Qcache_queries_in_cache当前Query Cache 中cache 的Query 数量
Qcache_total_blocks当前Query Cache 中的block 数量。
Qcache_hits / (Qcache_insertsQcache_not_cachedQcache_hits) 90/ 10000 0 90如果出现hits比例过低其实就可以关闭查询缓存了。使用redis专门缓存数据库Qcache_free_blocks 来判断碎片
Qcache_free_memory Qcache_lowmem_prunes 来判断内存够不够
Qcache_hits 多少次命中 Qcache_hits / (Qcache_insertsQcache_not_cachedQcache_hits) 此参数的设置方法
修改/etc/my.cnf文件在[mysqld]下面添加如下
query_cache_type1
query_cache_size256M
query_cache_limit32M6.7.安全计数器max_connect_errors
安全计数器的参数是max_connect_errors 它负责阻止过多尝试失败的客户端以防止暴力破解密码等情况当超过指定次数mysql服务器将禁止host的连接请求直到mysql服务器重启或通过flush hosts命令清空此host的相关信息 max_connect_errors的值与性能并无太大关系。
修改/etc/my.cnf文件在[mysqld]下面添加如下内容
max_connect_errors20006.8.排序线程缓冲区大小sort_buffer_size
排序线程缓冲区大小的参数是sort_buffer_size 当数据库中有很多的排序SQL时建议设置此参数加大排序缓冲区的大小。
排序缓冲区的大小并不是越大越好这个参数是会话级别的参数一个请求进来就会占用一个排序缓冲区此参数的值设置的过大可能会消耗系统的内存资源例如500个请求排序缓冲区的大小为20M那么就会消耗500*2010G的内存。
修改/etc/my.cnf文件在[mysqld]下面添加如下
sort_buffer_size2 0M6.9.最大的接收数据包大小max_allowed_packet
最大接收数据包大小的参数是max_allowed_packet这个参数非常重要在服务器端和备份端都需要配置这个参数值给不到位当有大数据量、大数据包的情况下就会无法写入当然也要根据实际情况进行设置。
该参数值的大小必须设置成1024的倍数。
修改/etc/my.cnf文件在[mysqld]下面添加如下
max_allowed_packet32M6.10.多表联查缓冲区大小join_buffer_size
多表联查缓冲区的大小设置参数是join_buffer_size和sort_buffer_size参数的概念差不多join_buffer_size参数是给多表联查设置缓冲区大小此参数也是会话级别一个会话进来就会占用一定的缓冲区大小根据需求按需设置。
修改/etc/my.cnf文件在[mysqld]下面添加如下
join_buffer_size2M6.11.服务器线程缓存数量thread_cache_size
服务器线程缓存数量的参数是thread_cache_size通过这个参数可以在缓存中保存线程的数量。
默认情况下当连接断开后客户端启动的线程会在缓存中释放新的客户端连接后再启动新的线程这样一来会消耗一定的CPU资源。
通过thread_cache_size参数可以设置在缓存中保留多少个客户端启动的线程数量当客户端断开连接后不会再将启动的线程销毁而是缓存在内存中下一个客户端连接后直接使用缓存中的线程通过这个值可以来改善系统的性能。
对于thread_cache_size参数的设置规则官方给出的建议是1G内存配置8个2G配置16个3G配置32个以此类推但是也不建议使用官方给出的规则还需要根据自己的数据库服务器进行配置。
设置thread_cache_size参数时可以将以下几个指标的值作为参考依据从而分析此参数设置多少个缓存的线程合适。
mysql show status like threads_%;
--------------------------
| Variable_name | Value |
--------------------------
| Threads_cached | 8 |
| Threads_connected | 2 |
| Threads_created | 4783 |
| Threads_running | 1 |
--------------------------Threads_cached代表当前此时此刻线程缓存中有多少空闲线程。
Threads_connected代表当前已建立连接的数量因为一个连接就需要一个线程所以也可以看成当前被使用的线程数。
Threads_created代表从最近一次服务启动已创建线程的数量如果发现Threads_created值过大的话表明MySQL服务器一直在创建线程这也是比较耗cpu SYS资源可以适当增加配置文件中thread_cache_size值。
Threads_running :代表当前激活的非睡眠状态线程数。并不是代表正在使用的线程数有时候连接已建立但是连接处于sleep状态。当我们对thread_cache_size参数设置了个数后可以通过zabbix监控观察一段时间内Threads_created指标的值如果很稳定的情况下就说明我们设置的参数是没问题的如果Threads_created指标的值很不稳定那我们就需要再调整thread_cache_size参数的值。
修改/etc/my.cnf文件在[mysqld]下面添加如下
thread_cache_size326.12.InnoDB存缓冲区大小innodb_buffer_pool_size
设置InnoDB缓冲区大小的参数是innodb_buffer_pool_size指定InnoDB使用的缓冲区大小在这个缓冲区中会包含数据和索引。
这个参数是非常重要的可以将物理内存的70%都分片给InnoDB缓冲区最大到80%不使用官方的90%因为数据库中还有其他的东西会用到缓冲区对于一个新的业务来说这个缓冲区大小可以设置为50%的物理内存后期数据量增大后再扩容。
修改/etc/my.cnf文件在[mysqld]下面添加如下
innodb_buffer_pool_size2048M
或者
innodb_buffer_pool_size8G6.13.InnoDB线程的并发数量innodb_thread_concurrency
设置InnoDB线程并发数量的参数是innodb_thread_concurrency默认值为0表示不限制此参数只在大事务并发的场景下才应用。
在官方文档中对于innodb_thread_concurrency参数的使用也给出了一些建议在一个MySQL服务器中如果用户并发的线程数量小于64建议设置innodb_thread_concurrency参数的值为0如果在数据库中工作负载一直较为严重甚至偶尔达到顶峰建议设置innodb_thread_concurrency值为128并且持续观察不断降低这个参数值直到发现最佳性能的并发数。 例如假设系统通常有40到50个用户但定期的数量增加至6070甚至200。你会发现性能在80个并发用户设置时表现稳定如果高于这个数性能反而下降。在这种情况下建议设置innodb_thread_concurrency参数为80以避免影响性能。如果你不希望InnoDB使用的虚拟CPU数量比用户线程使用的虚拟CPU更多比如20个虚拟CPU建议通过设置innodb_thread_concurrency 参数为这个值也可能更低这取决于性能体现如果你的目标是将MySQL与其他应用隔离你可以l考虑绑定mysqld进程到专有的虚拟CPU。但是需要注意的是这种绑定在myslqd进程一直不是很忙的情况下可能会导致非最优的硬件使用率。在这种情况下你可能会设置mysqld进程绑定的虚拟 CPU允许其他应用程序使用虚拟CPU的一部分或全部。在某些情况下最佳的innodb_thread_concurrency参数设置可以比虚拟CPU的数量小。定期检测和分析系统负载量、用户数或者工作环境的改变可能都需要对innodb_thread_concurrency参数的设置进行调整。 个人建议设置innodb_thread_concurrency参数的依据当前CPU使用均匀的情况下不需要调整此参数值观察连接数有没有达到顶峰根据情况从少到多进行调整比如先设置并发数为8持续观察后再进行调整。
修改/etc/my.cnf文件在[mysqld]下面添加如下
innodb_thread_concurrency06.13.Log Buffer数据写入日志并刷新到磁盘的时间点innodb_flush_log_at_trx_commit
innodb_flush_log_at_trx_commit 参数主要控制InnoDB将Log Buffer中的数据写入日志文件并刷新到磁盘的时间点取值分别是0/1/2三个。
取值0表示当前事务提交时不写入日志文件而是每秒钟将Log Buffer中的数据写入到日志文件并刷新到磁盘一次。取值1每次事务的提交都写入到日志文件一次并刷新到磁盘确保事务落盘。取值2每次事务提交触发写入日志文件的动作但每秒完成一次刷新磁盘的操作。 实际测试发现该值对插入数据的速度影响非常大设置为2时插入10000条记录只需要2秒设置为0时只需要1秒而设置为1时则需要229秒。因此MySQL手册也建议尽量将插入操作合并成一个事务这样可以大幅提高速度。 根据MySQL官方文档在允许丢失最近部分事务的危险的前提下可以把该值设为0或2。 修改/etc/my.cnf文件在[mysqld]下面添加如下
innodb_flush_log_at_trx_commit16.14.数据日志缓冲区的大小innodb_log_buffer_size
设置日志缓冲区的大小的参数是innodb_log_buffer_size此参数主要设置日志文件占用的内存大小以M为单位缓冲区大能提高性能对于较大的事务可以增大缓冲区大小。
修改/etc/my.cnf文件在[mysqld]下面添加如下
innodb_log_buffer_size128M6.15.数据日志文件大小innodb_log_file_size
innodb_log_file_size此参数是设置数据日志文件的大小以M为单位更大的设置可以提高性能。
修改/etc/my.cnf文件在[mysqld]下面添加如下
innodb_log_file_size100M6.16.数据日志文件数量innodb_log_files_in_group
为了提高性能MySQL可以循环方式将日志文件写入到多个文件推荐设置为3。
修改/etc/my.cnf文件在[mysqld]下面添加如下
innodb_log_files_in_group36.17.读缓冲区的大小read_buffer_size
读缓冲区大小的设置参数是read_buffer_size对表进行顺序扫描的请求将分配一个读入缓冲区MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁并且你认为频繁扫描进行得太慢可以通过增加该变量值以及内存缓冲区大小提高其性能。和 sort_buffer_size一样该参数对应的分配内存也是每个连接独享。
修改/etc/my.cnf文件在[mysqld]下面添加如下
read_buffer_size1M6.18.随机读缓冲区的大小read_rnd_buffer_size
随机读查询操作缓冲区大小的设置参数是read_rnd_buffer_size。
当按任意顺序读取行时(例如按照排序顺序)将分配一个随机读缓存区。进行排序查询时MySql会首先扫描一遍该缓冲以避免磁盘搜索提高查询速度如果需要排序大量数据可适当调高该值。但MySql会为每个客户连接发放该缓冲空间所以应尽量适当设置该值以避免内存开销过大。 注顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据而辅助索引和主键所在的数据段不同因此访问方式是随机的。
修改/etc/my.cnf文件在[mysqld]下面添加如下
read_rnd_buffer_size1M6.19.批量插入数据的缓冲区大小bulk_insert_buffer_size
批量插入数据缓冲区大小是参数是bulk_insert_buffer_size可以有效提高插入效率。
修改/etc/my.cnf文件在[mysqld]下面添加如下
bulk_insert_buffer_size8M6.20.Binlog日志的优化
对于Binlog日志的优化主要在于以下几个参数
log-bin/data/mysql-bin
binlog_cache_size2M #为每个session分配的内存在事务过程中用来存储二进制日志的缓存, 提高记录bin-log的效率。没有什么大事务dml也不是很频繁的情况下可以设置小一点如果事务大而且多dml操作也频繁则可以适当的调大一点。前者建议是--1M后者建议是即 2--4M
max_binlog_cache_size8M #表示的是binlog能够使用的最大cache内存大小
max_binlog_size512M #指定binlog日志文件的大小如果当前的日志大小达到max_binlog_size还会自动创建新的二进制日志。你不能将该变量设置为大于1GB或小于4096字节。默认值是1GB。在导入大容量的sql文件时建议关闭sql_log_bin否则硬盘扛不住而且建议定期做删除。
binlog_formatrow #binlog日志的格式
#双1标准基于安全的机制
sync_binlog1 #什么时候刷新binlog到磁盘每次事务commit都刷新日志到磁盘
innodb_flush_log_at_trx_commit1 #事务提交写入日志并刷新到磁盘
expire_logs_days7 #定义了mysql清除过期日志的时间。6.21.安全参数的优化
对于MySQL数据库的安全参数主要有Innodb_flush_method参数这个参数控制InnoDB数据文件以及Redo log的打开、刷写模式该参数有2个取值
fync 在数据页需要持久化时首先将数据写入到OS Buffer中然后由OS Buffer决定什么时候写入磁盘。在Redo Buffer需要持久时首先将数据写入OS Buffer中然后由OS Buffer决定什么时候写入磁盘。但是当innodb_flush_log_at_trx_commit这个参数设置为1后日志依旧会在commit后直接写入磁盘。 O_DIRECT 在数据页需要持久化时直接写入磁盘。在Redo Buffer需要持久时首先将数据写入OS Buffer中然后由OS Buffer决定什么时候写入磁盘。但是当innodb_flush_log_at_trx_commit这个参数设置为1后日志依旧会在commit后直接写入磁盘。
最安全的模式
修改/etc/my.cnf文件在[mysqld]下面添加如下
innodb_flush_log_at_trx_commit1
innodb_flush_methodO_DIRECT最高性能的模式
修改/etc/my.cnf文件在[mysqld]下面添加如下
innodb_flush_log_at_trx_commit0
innodb_flush_methodfsync一般情况下我们都会选择最安全的模式基于双1标准 修改/etc/my.cnf文件在[mysqld]下面添加如下
sync_binlog1
innodb_flush_log_at_trx_commit1
innodb_flush_methodO_DIRECT6.22.主从优化
#必须开启gtid
gtid_modeON
enforce_gtid_consistencyON
log_slave_updatesONslave-parallel-typeLOGICAL_CLOCK #并行复制方式
slave-parallel-workers16 #并行的线程个数
master_info_repositoryTABLE #主从复制集群中主库的配置是在磁盘中的一个文件里master.info通过此参数可以将其放在表中使用数据表在一定程度上可以提高性能。
relay_log_info_repositoryTABLE #将relaylog的信息记录到表中默认情况下载磁盘的文件里relay-log.info
relay_log_recoveryON #在数据7.优化后的配置文件内容
[mysqld]
basedir/data/mysql
datadir/data/mysql/data
socket/tmp/mysql.sock
log-error/data/mysql/mysql-err.log
log_bin/data/mysql/mysql-bin
binlog_formatrow
skip-name-resolve
server-id52
gtid-modeon
enforce-gtid-consistencytrue
log-slave-updates1
relay_log_purge0
max_connections1024
back_log128
wait_timeout60
interactive_timeout7200
key_buffer_size16M
query_cache_size64M
query_cache_type1
query_cache_limit50M
max_connect_errors20
sort_buffer_size2M
max_allowed_packet32M
join_buffer_size2M
thread_cache_size200
innodb_buffer_pool_size1024M
innodb_flush_log_at_trx_commit1
innodb_log_buffer_size32M
innodb_log_file_size128M
innodb_log_files_in_group3
binlog_cache_size2M
max_binlog_cache_size8M
max_binlog_size512M
expire_logs_days7
read_buffer_size2M
read_rnd_buffer_size2M
bulk_insert_buffer_size8M
[client]
socket/tmp/mysql.sock
