mysql 参数调优（11）之innodb_buffer_pool_instances设置多个缓冲池实例

MySQL 5.5引入了缓冲实例作为减小内部锁争用来提高MySQL吞吐量的手段。在5.5版本这个对提升吞吐量帮助很小，然后在MySQL 5.6版本这个提升就非常大了，所以在MySQL5.5中你可能会保守地设置innodb_buffer_pool_instances=4，在MySQL 5.6和5.7中你可以设置为8-16个缓冲池实例。设置后观察会觉得性能提高不大，但在大多数高负载情况下，它应该会有不错的表现。对了，不要指望这个设置能减少你单个查询的响应时间。这个是在高并发负载的服务器上才看得出区别。比如多个线程同时做许多事情。

5.7、8.0 下INNODB_BUFFER_POOL_INSTANCES默认为1，若mysql存在高并发和高负载访问，设置为1则会造成大量线程对BUFFER_POOL的单实例互斥锁竞争，这样会消耗一定量的性能的。

pool_instances 可以设置为cpu核心数，它的作用是：

1）对于缓冲池在数千兆字节范围内的系统，通过减少争用不同线程对缓存页面进行读写的争用，将缓冲池划分为多个单独的实例可以提高并发性。可以类比为 java中的 ThreadLocal 线程本地变量 就是为每个线程维护一个buffer pool实例，这样就不用去争用同一个实例了。相当于减少高并发下mysql对INNODB_BUFFER缓冲池的争用。

2）使用散列函数将存储在缓冲池中或从缓冲池读取的每个页面随机分配给其中一个缓冲池实例。每个缓冲池管理自己的空闲列表， 刷新列表， LRU和连接到缓冲池的所有其他数据结构，并受其自己的缓冲池互斥量保护。

MySQL系统变量(system variables)实际上是一些系统参数，用于初始化或设定数据库对系统资源的占用，文件存放位置等等。

mysql>show variables like "log"

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

| Variable_name | Value |

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

| log | OFF |

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

1 row in set (0.00 sec)

mysql>set @log=1

Query OK, 0 rows affected (0.00 sec)

mysql>show variables like "log"

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

| Variable_name | Value |

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

| log | OFF |

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

1 row in set (0.00 sec)

mysql>set @log="ON"

Query OK, 0 rows affected (0.00 sec)

mysql>show variables like "log"

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

| Variable_name | Value |

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

| log | OFF |

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

1 row in set (0.00 sec)

MySQL 里设置或修改系统变量的几种方法

-- 设置或修改系统日志有效期

SET GLOBAL expire_logs_days=8

SHOW VARIABLES LIKE '%expire_logs_days%'

-- 设置或修改系统最大连接数

SET GLOBAL max_connections = 2648

SHOW VARIABLES LIKE '%max_connections%'

-- 修改MYSQL自动编号步长

SHOW VARIABLES LIKE '%auto_increment%'

SET GLOBAL auto_increment_offset = 1

SET GLOBAL auto_increment_increment = 1

比如设置MySQL实例参数wait_timeout为10秒.

1) 设置全局变量方法1(不推荐): 修改参数文件, 然后重启mysqld

# vi /etc/my.cnf

[mysqld]

wait_timeout=10

# service mysqld restart

不过这个方法太生硬了, 线上服务重启无论如何都应该尽可能避免.

2) 设置全局变量方法2(推荐): 在命令行里通过SET来设置, 然后再修改参数文件

如果要修改全局变量, 必须要显示指定"GLOBAL"或者"@@global.", 同时必须要有SUPER权限.

mysql>set global wait_timeout=10

or

mysql>set @@global.wait_timeout=10

然后查看设置是否成功:

mysql>select @@global.wait_timeout=10

or

mysql>show global variables like 'wait_timeout'

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

| Variable_name | Value |

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

| wait_timeout | 10|

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

如果查询时使用的是show variables的话, 会发现设置并没有生效, 除非重新登录再查看. 这是因为使用show variables的话就等同于使用show session variables, 查询的是会话变量, 只有使用show global variables查询的才是全局变量. 如果仅仅想修改会话变量的话, 可以使用类似set wait_timeout=10或者set session wait_timeout=10这样的语法.

当前只修改了正在运行的MySQL实例参数, 但下次重启mysqld又会回到默认值, 所以别忘了修改参数文件:

# vi /etc/my.cnf

[mysqld]

wait_timeout=10

3) 设置会话变量方法: 在命令行里通过SET来设置

如果要修改会话变量值, 可以指定"SESSION"或者"@@session."或者"@@"或者"LOCAL"或者"@@local.", 或者什么都不使用.

mysql>set wait_timeout=10

or

mysql>set session wait_timeout=10

or

mysql>set local wait_timeout=10

or

mysql>set @@wait_timeout=10

or

mysql>set @@session.wait_timeout=10

or

mysql>set @@local.wait_timeout=10

然后查看设置是否成功:

mysql>select @@wait_timeout

or

mysql>select @@session.wait_timeout

or

mysql>select @@local.wait_timeout

or

mysql>show variables like 'wait_timeout'

or

mysql>show local variables like 'wait_timeout'

or

mysql>show session variables like 'wait_timeout'

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

| Variable_name | Value |

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

| wait_timeout | 10|

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

4) 会话变量和全局变量转换方法: 在命令行里通过SET来设置

将会话变量值设置为对应的全局变量值呢:

mysql>set @@session.wait_timeout=@@global.wait_timeout

将会话变量值设置为MySQL编译时的默认值(wait_timeout=28800):

mysql>set wait_timeout=DEFAULT

这里要注意的是, 并不是所有的系统变量都能被设置为DEFAULT, 如果设置这些变量为DEFAULT则会返回错误.

参数化查询是指在设计与数据库连结并访问数据时，在需要填入数值或数据的地方，使用参数 来给值，这个方法目前已被视为最有效可预防SQL注入攻击的攻击手法的防御方式。

$query=sprintf("SELECT * FROM Users where UserName='%s' and Password='%s'",

mysql_real_escape_string($Username),

mysql_real_escape_string($Password))

mysql_query($query)

或是

$db=new mysqli("localhost","user","pass","database")

$stmt=$mysqli->prepare("SELECT priv FROM testUsers WHERE username=? AND password=?")

$stmt->bind_param("ss",$user,$pass)

$stmt->execute()

---