01 mysql 重要的系统参数

mysql 中几个重要的系统参数

 
一、双1配置  重要指数 满天星
（set global innodb_flush_log_at_trx_commit=1;
set global sync_binlog=1;）

说明： innodb_flush_log_at_trx_commit 控制log buffer中的数据写入到文件，并且通知文件系统进行文件同步的flush到磁盘操作
0：每隔1秒刷新一次，最极端时丢失1秒数据（MySQL Crash 和OS Crash或者主机断电），不安全，会丢失数据
1：每次事务提交时刷新一次，最安全，能够保证不论是MySQL Crash 还是OS Crash 或者是主机断电都不会丢失任何已经提交的数据，但是性能较差。
2：不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新，
   只有在（OS crash）时，才可能丢失数据，比较安全，但是会对写入性能有一定影响。

 
sync_binlog 控制数据库的binlog刷到磁盘上去，
0 :不控制binlog的刷新,性能最好，但风险最大
1 :每次事务提交都刷新binlog到磁盘，性能最差，单风险最低
>1 : 可根据实际情况调整，兼顾性能和风险


1. 重要指数 5颗星
innodb_buffer_pool_size = 系统内存75% 左右
# InnoDB使用一个缓冲池来保存索引和原始数据, 不像MyISAM.
# 这里你设置越大,你在存取表里面数据时所需要的磁盘IO越少.
# 在一个独立使用的数据库服务器上,你可以设置这个变量为服务器物理内存大小的80%
# 不要设置过大,否则,由于物理内存的竞争可能导致操作系统的换页颠簸.
# 注意在32位系统上你每个进程可能被限制在 2-3.5G 用户层面内存限制,
# 所以不要设置的太高.

用于缓存索引和数据的内存大小，这个当然是越多越好， 数据读写在内存中非常快， 减少了对磁盘的读写。
 当数据提交或满足检查点条件后才一次性将内存数据刷新到磁盘中。然而内存还有操作系统或数据库其他进程使用， 
 根据经验，推荐设置innodb-buffer-pool-size为服务器总可用内存的75%。 若设置不当， 内存使用可能浪费或者使用过多。 
 对于繁忙的服务器， buffer pool 将划分为多个实例以提高系统并发性， 
 减少线程间读写缓存的争用。buffer pool 的大小首先受 innodb_buffer_pool_instances 影响， 当然影响较小。

Innodb_buffer_pool_pages_data
Innodb buffer pool缓存池中包含数据的页的数目，包括脏页。单位是page。
eg、show global status like 'Innodb_buffer_pool_pages_data';

  
Innodb_buffer_pool_pages_total
innodb buffer pool的页总数目。单位是page。
eg：show global status like 'Innodb_buffer_pool_pages_total';

show global status like 'Innodb_page_size';

查看@@innodb_buffer_pool_size大小，单位字节
SELECT @@innodb_buffer_pool_size/1024/1024/1024; #字节转为G

在线调整InnoDB缓冲池大小，如果不设置，默认为128M
set global innodb_buffer_pool_size = 4227858432; ##单位字节

计算Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100%
当结果 > 95% 则增加 innodb_buffer_pool_size， 建议使用物理内存的 75%
当结果 < 95% 则减少 innodb_buffer_pool_size， 
建议设置大小为： Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (1024*1024*1024)

2.重要指数 2颗星
innodb_additional_mem_pool_size = 16M
设置了InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，
所以当我们一个MySQL Instance中的数据库对象非常多的时候，是需要适当调整该参数的大小
以确保所有数据都能存放在内存中提高访问效率的。


3.innodb_log_file_size 建议=innodb_buffer_pool_size/innodb_log_files_in_group（二进制日志文件的大小） 重要指数5颗星
# 此参数确定数据日志文件的大小，更大的设置可以提高性能，但也会增加恢复故障数据库所需的时间
 #在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高，但是要注意到可能会增加恢复时间。
我经常设置为64-512MB，根据服务器大小而异。
生产环境建议设置1G或者 2G

这个选项决定着性能，要慎重设置。默认设置为5M，难以满足生产环境下的需求。日志文件在mysql实例第一次启动时初始化，
该文件是旋转的，因此可以根据文件修改时间来判断日志文件的旋转频率，旋转频率太频繁，说明日志文件太小了，要扩大。
innodb_log_file_size设置大小通常视innodb_buffer_pool_size而定。
影响日志文件性能的变量是innodb_log_buffer_size，确保有足够大的日志缓冲区来保存脏数据在被写入到日志文件之前。
对于比较小的innodb_buffer_pool_size，建议是设置一样大。 但是，对于比较大的innodb_buffer_pool_size，不建议这么设置，
这会存在一个潜在的问题，那就是当mysql挂掉时，恢复数据需要很久，造成大量的停机时间。
官方文档的建议设置是innodb_buffer_pool_size/innodb_log_files_in_group


4.innodb_flush_log_at_trx_commit 重要指数5颗星
控制log的刷新到磁盘的方式，这个参数只有３个值（０，１，２）默认为１，性能更高的可以设置为０或是２，这样可以适当的减少磁盘IO，
游戏库的MySQL建议设置为0。

innodb_flush_log_at_trx_commit = 0
Innodb 中的Log Thread 每隔1 秒钟会将log buffer中的数据写入到文件，同时还会通知文件系统进行文件同步的flush
操作，保证数据确实已经写入到磁盘上面的物理文件。但是，每次事务的结束（commit 或者是rollback）并不会触发Log Thread
将log buffer 中的数据写入文件。所以，当设置为0 的时候，当MySQL Crash 和OS Crash
或者主机断电之后，最极端的情况是丢失1 秒时间的数据变更。

innodb_flush_log_at_trx_commit = 1
这也是Innodb 的默认设置。我们每次事务的结束都会触发Log Thread 将log buffer
中的数据写入文件并通知文件系统同步文件。**这个设置是最安全的设置，能够保证不论是MySQL Crash 还是OS Crash
或者是主机断电都不会丢失任何已经提交的数据。**

innodb_flush_log_at_trx_commit = 2
抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了调整这个值。默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，
这是很费时的。特别是使用电池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，
它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，
即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统挂了时才可能丢数据。

根据上面三种参数值的说明，0的时候，如果mysql crash可能会丢失数据，可靠性不高。我们着重测试1和2两种情况。1的时候会影响数据库写入性能，
相对2而言写入速度会慢。这只能根据实际情况来决定吧。

5.sync_binlog  （重要指数 5颗星）

MySQL提供一个sync_binlog参数来控制数据库的binlog刷到磁盘上去。
默认，sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。
这时候的性能是最好的，但是风险也是最大的。
因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。
如果sync_binlog>0，表示每sync_binlog次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去。
最安全的就是sync_binlog=1了，表示每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。
这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失1个事务的数据。
但是binlog虽然是顺序IO，但是设置sync_binlog=1，多个事务同时提交，同样很大的影响MySQL和IO性能。
虽然可以通过group commit的补丁缓解，但是刷新的频率过高对IO的影响也非常大。
对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。
所以很多MySQL DBA设置的sync_binlog并不是最安全的1，而是100或者是0。这样牺牲一定的一致性，可以获得更高的并发和性能。



6.innodb_file_per_table
使每个Innodb的表，有自已独立的表空间。如删除表后可以回收那部分空间。
默认是关闭的，建议打开（innodb_file_per_table=1）。
MySQL默认配置文件my-innodb-heavy-4G（无相关设置）

# InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间
# 独立表空间优点：
# 1．每个表都有自已独立的表空间。
# 2．每个表的数据和索引都会存在自已的表空间中。
# 3．可以实现单表在不同的数据库中移动。
# 4．空间可以回收（除drop table操作处，表空不能自已回收）
# 缺点：
# 单表增加过大，如超过100G
# 结论：
# 共享表空间在Insert操作上少有优势。其它都没独立表空间表现好。当启用独立表空间时，请合理调整：innodb_open_files



7.innodb_data_file_path 

指定表数据和索引存储的空间，可以是一个或者多个文件。最后一个数据文件必须是自动扩充的，
也只有最后一个文件允许自动扩充。这样，当空间用完后，自动扩充数据文件就会自动增长（以8MB为单位）
以容纳额外的数据。
例如： innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend
两个数据文件放在不同的磁盘上。数据首先放在ibdata1 中，当达到900M以后，数据就放在ibdata2中。

生产配置：innodb_data_file_path = ibdata1:1G:autoextend

　　持续更新中。。。。。