Mysql – increasing thesql table open cache

innodbMySQLmysql-5.5

I often read that it is best to increase this variable slowly. Can someone explain why? My status indicates that I should increase it… What is best practice / "slowly"?

Thanks!

Best Answer

Since you are using InnoDB with innodb_file_per_table enabled, you need to raise innodb_open_files because this dictates the number of open file handles on .ibd files.

In MySQL 5.6, this setting is treated a little different.

This variable is relevant only if you use multiple InnoDB tablespaces. It specifies the maximum number of .ibd files that MySQL can keep open at one time. The minimum value is 10. As of MySQL 5.6.6, the default value is 300 if innodb_file_per_table is not enabled, and the higher of 300 and table_open_cache otherwise. Before 5.6.6, the default value is 300.

The file descriptors used for .ibd files are for InnoDB tables only. They are independent of those specified by the --open-files-limit server option, and do not affect the operation of the table cache. For general I/O tuning advice, see Section 8.5.7, “Optimizing InnoDB Disk I/O”.

Here is an additional reference from MySQL Performance Blog about setting innodb_open_files.

Raising innodb_open_files to a big number should not be that all that troublesome, but please be sensible about the number you choose.

Related Solutions

Mysql – Queries getting stuck on very simple COUNT queries

I know this is just a guess but here it goes...

I think your InnoDB is underconfigured. Please run the follolwing:

SHOW VARIABLES LIKE 'innodb%';

and post the output into the question.

Since you are using Percona Server, you must raise the following:

innodb_read_io_threads
- Default for MySQL is 4
- Default for Percona Server is 8
- Please raise it to the max value, 64
innodb_write_io_threads
- Default for MySQL is 4
- Default for Percona Server is 8
- Please raise it to the max value, 64
innodb_io_capacity (raise it to 2000 for starters)
innodb_thread_concurrency
- Default for MySQL 5.5 is 0
- Default for Percona Server is 0
- Default for MySQL 5.1 is 8
- Default for MySQL 5.0 is 8
- Since you are using Percona Server 5.5, leave it at zero ( 0 )

Concerning the counts being held up, please take into consideration the MVCC that is going on. Each individual connection running a SELECT query is monitoring what the count was before any attempted UPDATEs. UPDATE queries may perform page locks in the Clustered Index. This could easily hold up SELECT queries that pass through the PRIMARY KEY. Even though the secondary index LastUpdated is being used, each secondary key entry has the Clustered Key entry right along with it. Therefore, the PRIMARY KEY may be accessed as a point of reference and it taking a backseat to UPDATEs. You should be able to see this back logging in SHOW ENGINE INNODB STATUS\G. This may explain slow counts.

This may still occur even if you raise the values I just mentioned given enough UPDATE traffic.

Mysql – Should I increase max_connections in AWS RDS t1-micro for MySQL

Each connection carries the load of per-connection buffers as set by these parameters

Changing the number of connections increases the amount of memory each connection can demand to this : (join_buffer_size+sort_buffer_size+read_buffer_size +read_rnd buffer_size) X max_connections

I have written about these before

Jan 03, 2012 : Understanding mysqltuner Recomendations w/ Unused Catalogs
Apr 24, 2012 : How costly is opening and closing of a DB connection?
Feb 01, 2013 : Show processlist / Max concurrent connections seem to max out at 131

ANALYSIS

Amazon has to set the number of connections based on each model's right to demand a certain amount of memory and connections

MODEL      max_connections innodb_buffer_pool_size
---------  --------------- -----------------------
t1.micro   34                326107136 (  311M)
m1-small   125              1179648000 ( 1125M,  1.097G)
m1-large   623              5882511360 ( 5610M,  5.479G)
m1-xlarge  1263            11922309120 (11370M, 11.103G)
m2-xlarge  1441            13605273600 (12975M, 12.671G)
m2-2xlarge 2900            27367833600 (26100M, 25.488G)
m2-4xlarge 5816            54892953600 (52350M, 51.123G)

I wrote about this as well : When should I think about upgrading our RDS MySQL instance based on memory usage?

This allows Amazon to do the following:

Charge you for each memory model based on seamless MySQL usage
Fairly Apportion Resources for MySQL RDS per region
Shoot yourself in the foot for tampering with per-connection settings

RECOMMENDATION

Perhaps you should try useing Amazon EC2 where you have no restrictions on access to my.cnf