mysql performance tuning_Performance tuning

InnoDB buffer pool instances:

Tune innodb_buffer_pool_instances (InnoDB Disk I/O) to increase read/write threads, and divide InnoDB buffer pool in regions for more performance

The number of regions that the InnoDB buffer pool is divided into. For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency, by reducing contention as different threads read and write to cached pages. Each page that is stored in or read from the buffer pool is assigned to one of the buffer pool instances randomly, using a hashing function. Each buffer pool manages its own free lists, flush lists, LRUs, and all other data structures connected to a buffer pool, and is protected by its own buffer pool mutex.

This option takes effect only when you set the innodb_buffer_pool_size to a size of1 gigabyte or more. The total size you specify is divided among all the buffer pools. For best efficiency, specify a combination of innodb_buffer_pool_instances and innodb_buffer_pool_size so that each buffer pool instance is at least 1 gigabyte.

https://www.saotn.org/mysql-innodb-performance-improvement/

Increase table_open_cache gradually to avoid file descriptor limits

Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C

Beware that open_files_limit (65535) variable

should be greater than table_open_cache ( 2000)

Variables to adjust:

table_open_cache (> 2000)

http://www.techinfobest.com/optimize-mysql-table_open_cache/

https://www.percona.com/blog/2009/11/16/table_cache-negative-scalability/