数据库参数优化

1.最大连接数max_connections

如果给了一台服务器，内存2G，cpu2核 如何设置数据库最大连接数

可以这样设置，先查看show global status like "%used%"

再查看最大线程连接数

然后用最大曾经使用连接数除以设置的最大连接数 ≈ 85% 证明设置的最合理

 

2.buffer相关的一些设置

2.1InnoDB引擎 设置索引和数据缓冲区大小

一般是物理内存的60%~70%

如果你是64G的内存的话，设置的参数应该是44G

innodb_buffer_pool_size = 44G

 

2.2 InnoDB设置缓冲池实例个数

  innodb_buffer_pool_instances 可以开启多个内存缓冲池，把需要缓冲的数据 hash 到不同的缓冲池中，这样可以并行的内存读写。该参数值默认为 1，推荐设置 4 个或 8 个。

innodb_buffer_pool_instances = 8

 

2.3设置日志同步到磁盘的方式

 innodb_flush_log_at_trx_commit 有三种可选值

• 设置为 0 代表大跃每秒写入到日志并同步到磁盘，数据库故障会丢失1s左右事务数据
• 设置为 1 为每执行一条SQL后写入到日志，并同步到磁盘，I/O开销大，执行完SQL要等待日志读写，这种方式是最安全不会丢失任何数据，但是开销大，效率低
• 设置为 2 代表现将日志写入到系统缓存区，再每秒同步到磁盘，效率高，但是服务器故障，内存中的数据遗失，但效率明显

 

如果对数据没特别要求的话，设置2效果明显，如果有数据完整有要求，设置为1即可

 innodb_flush_log_at_trx_commit =2

 

2.4 设置独享表空间

默认共享表空间的值是0，共享表空间idbdata文件不断增大，影响一定的I/O性能，推荐开启后独立表空间模式，参数为1，每个表和数据都存在自己独立的表空间中，可以实现单表在不同的数据库中移动

innodb_file_per_table = 1

 

3.log相关

show global variables like "%binlog%";

设置binlog大小

binlog_cache_size

默认是33554432 也就是32k  这个参数一般不会调整

 

binlog_format = mixed    这个具体查看这个文章binlog的三种格式对比

max_binlog_cache_size = 128M

max_binlog_size = 500M  一版设置这两个值 ，调整binlog的最大值，一般是512M或者1G

sync_binlog = 0   这个参数有讲究，如下：

等于0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。完全根据系统的filesystem来决定

 

等于n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

 

 

查看下面两个状态参数

show global status like "binlog_cache%";

+-----------------------+----------+

| Variable_name         | Value    |

+-----------------------+----------+

| Binlog_cache_disk_use | 0        |

| Binlog_cache_use      | 38175900 |

+-----------------------+----------+

Binlog_cache_use    代表binlog_cache内存方式被用了多少次、

Binlog_cache_disk_use  代表binlog_cache临时文件的方式被用了多少次

如果Binlog_cache_disk_use=0 证明内存的cache是够用的，不需要用到临时文件。

当我们执行多语句事务的时候，max_binlog_cache_size如果不够大的话，系统可能会报出“Multi-statementtransactionrequiredmorethan'max_binlog_cache_size'bytesofstorage”的错误。

默认的max_binlog_cache_size= 18446744073709547520

 

如果想要最安全 ，数据不丢失的情况的话，设置为双1 也就是

 innodb_flush_log_at_trx_commit =1

sync_binlog = 1

每执行1条sql，都要从内存中将sql同步到磁盘的binlog中，数据最安全，但开销最大，同时打开效果最好

 

如果不提倡安全性，只要求速度的话，可以设置为

 innodb_flush_log_at_trx_commit =2

sync_binlog = 1000

现将数据刷到cache中。每执行1000个sql刷一次磁盘

对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

 

 

参考:

ps:极客时间丁奇《mysql实战45讲》 这里面也讲了很多关于mysql优化的技巧，很受用