InnoDB配置调优

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

MariaDB > show variables like 'innodb_da%';
+-----------------------+------------------------+
| Variable_name | Value |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir | |
+-----------------------+------------------------+

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.

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