浅谈innodb_buffer_pool_size的设置

        设置innodb_buffer_pool_size为可用内存的50-60%.注意可用内存应该是linux命令free下的-/+ buffers/cache行的free值；innodb_buffer_pool_size的值应该是在mysql实例没有启动情况下的值，而不是实例启动后的值。见示例。

示例：

（1）在没有对innodb_buffer_pool_size参数优化前：

SELECT sql_no_cache

N1.N_NAME AS SUPP_NATION,

N2.N_NAME AS CUST_NATION,

EXTRACT(YEAR FROM L_SHIPDATE) AS L_YEAR,

SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT)) AS REVENUE

FROM    SUPPLIER,

LINEITEM,

ORDERS,

CUSTOMER,

NATION N1,

NATION N2

WHERE

        S_SUPPKEY = L_SUPPKEY

    AND O_ORDERKEY = L_ORDERKEY

    AND C_CUSTKEY = O_CUSTKEY

    AND S_NATIONKEY = N1.N_NATIONKEY

    AND C_NATIONKEY = N2.N_NATIONKEY

    AND (N1.N_NAME = ‘FRANCE‘ AND N2.N_NAME = ‘GERMANY‘ OR N1.N_NAME = ‘GERMANY‘ AND N2.N_NAME = ‘FRANCE‘)

    AND L_SHIPDATE >= DATE ‘1995-01-01‘

    AND L_SHIPDATE <= DATE ‘1996-12-31‘

GROUP BY

SUPP_NATION,

CUST_NATION,

L_YEAR

ORDER BY

SUPP_NATION,

CUST_NATION,

L_YEAR;

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

| SUPP_NATION | CUST_NATION | L_YEAR | REVENUE            |

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

| FRANCE      | GERMANY     |   1995 |      54639732.7336 |

| FRANCE      | GERMANY     |   1996 |  54633083.30760003 |

| GERMANY     | FRANCE      |   1995 |  52531746.66970005 |

| GERMANY     | FRANCE      |   1996 | 52520549.022399954 |

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

4 rows in set (2 min 0.90 sec)

查询耗费2 min 0.90 sec

（2）数据库实例启动情况下对可用内存的调整

[root@gflinux ~]# free

             total       used       free     shared    buffers     cached

Mem:       1034708     999860      34848          0       2852     161448

-/+ buffers/cache:     835560     199148

Swap:      4192924        152    4192772

设置innodb_buffer_pool_size值为可用内存的60%

199148*60%=119M，取128M

重启数据库后再次运行上述查询语句，查看执行时间：

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

| SUPP_NATION | CUST_NATION | L_YEAR | REVENUE            |

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

| FRANCE      | GERMANY     |   1995 |      54639732.7336 |

| FRANCE      | GERMANY     |   1996 |  54633083.30760003 |

| GERMANY     | FRANCE      |   1995 |  52531746.66970005 |

| GERMANY     | FRANCE      |   1996 | 52520549.022399954 |

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

4 rows in set (7.55 sec)

（3）数据库实例没有启动情况下对可用内存的调整

[root@gflinux ~]# service mysql stop

Shutting down MySQL....                                    [  OK  ]

[root@gflinux ~]# free

             total       used       free     shared    buffers     cached

Mem:       1034708     430908     603800          0       4396     353292

-/+ buffers/cache:      73220     961488

Swap:      4192924        152    4192772

设置innodb_buffer_pool_size值为可用内存的60%

961488*60%=577M，取580M

重启数据库后再次运行上述查询语句，查看执行时间：

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

| SUPP_NATION | CUST_NATION | L_YEAR | REVENUE            |

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

| FRANCE      | GERMANY     |   1995 |      54639732.7336 |

| FRANCE      | GERMANY     |   1996 |  54633083.30760003 |

| GERMANY     | FRANCE      |   1995 |  52531746.66970005 |

| GERMANY     | FRANCE      |   1996 | 52520549.022399954 |

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

4 rows in set (1.90 sec)

查询时间缩短为2s左右。