InnoDB配置调优

梦里梦外; 2022-07-10 13:00 170阅读 0赞

InnoDB differs from other on-disk storage engines in that it offers row-level  
locking. This primarily differs, in the MySQL world, with the MyISAM storage  
engine which features table-level locking.

\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#Configuration\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
/etc/my.cnf  
default\_storage\_engine = InnoDB  
innodb\_thread\_concurrency = 8

\#if DB server,set 1/4 physical RAM  
\#flush data to disk by innodb flush thread  
innodb\_buffer\_pool\_size = 16G

\#enable tablespace  
innodb\_file\_per\_table = 1

\#  
transaction\_isolation = READ-COMMITTED

innodb\_status\_file = 1  
Innodb\_data\_file\_path=ibdata1:12M:autoextend

innodb\_flush\_method = fdatasync,O\_DSYNC,O\_DIRECT

fdatasync (the default setting): InnoDB uses the fsync() call to flush data  
and logs to disk. Good for most workloads but remains a conservative choice  
and is prone to I/O performance penalty.

O\_DSYNC: This can provide a benefit for SELECT intensive workloads where  
InnoDB files are located on a fiber optic attached DAS or SAN device.  
Load Testing InnoDB for Performance

O\_DIRECT: This can help prevent double-buffering of data between InnoDB  
and the filesystem caches as files can be read via raw I/O.

![image2017-1-9_16-40-8.png_version_1_modificationDate_1483951335000_api_v2][]

innodb\_read\_io\_threads = 4  
innodb\_write\_io\_threads = 4  
MariaDB > show variables like 'innodb\_%io%threads';  
\+---------------------------+-------+  
| Variable\_name | Value |  
\+---------------------------+-------+  
| innodb\_encryption\_threads | 0 |  
| innodb\_read\_io\_threads | 4 |  
| innodb\_write\_io\_threads | 4 |  
\+---------------------------+-------+

\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

InnoDB and input/output resources are determined by:  
Speed of your disks  
RAID architecture and settings  
Underlying filesystem type and behavior  
The method that InnoDB uses to flush logs after a transaction is committed

Test  
1.Execute the test and collect metrics that will be used in the later steps. For  
example, execute SysBench load test on our 8-core xeon server with 1000  
connections per second for one hour of complex transaction queries with  
the innodb\_buffer\_pool=X , and so on.

17/1/10 update

\#INNODB会自动维护后台作业自动从buffer pool当中清除脏数据，当buffer pool中的脏页占用比 达到innodb\_max\_dirty\_pages\_pct\_lwm的设定值的时候，就会自动将脏页清出buffer pool  
innodb\_max\_dirty\_pages\_pct\_lwm=10

\#刷新数据而不让脏数据的百分比超过这个值。  
innodb\_max\_dirty\_pages\_pct参数会让innodb buffer pool

[image2017-1-9_16-40-8.png_version_1_modificationDate_1483951335000_api_v2]: http://10.0.30.107/download/attachments/2523259/image2017-1-9_16-40-8.png?version=1&modificationDate=1483951335000&api=v2