Hello,
I’m new to the forums, so if this is in the wrong place please let me know and I’ll post in the correct place.
Our current system is set up in AWS with a Centos 6 OS 196 GB RAM and 64 CPU’s. We are running Percona MySQL 5.6.32-78.0.
Our system was performing well, until it started eating memory on the system, it was at this point where we made several changes. We increased the instance size to what it currently is from Centos 6 OS 60 GB RAM and 30 CPU’s. We also make a few other changes. We changed the table_open_cache from 100k to 400k and we changed the table_open_cache_instances from 8 to 16. We have an inherited database (through purchase of company) that is really in bad shape, a re-write of the application and the database is really what is needed, but it isn’t viable from a business standpoint right now. In the database we have 115 schemas, 76 of those are single tenant schemas with over 2100 tables per schema. The other 39 schema are multi-tenant schemas. When we started working with the system everything was MyISAM and at MySQL 5.1. It is now at 5.6 and we have performed a conversion of 23 of the schemas into a single partitioned table that is InnoDB. Each of those tables have roughly 2100 partitions to them. The performance has increased due to this. Yes, I understand this is not an optimal solution, but the DB needs to be rebuilt as relational and the app needs to be re-written, that just can’t happen now.
On to the real question. When we made our changes to the new server size in AWS we also made the following changes.
- table_open_cache from 100k to 400k
- table_open_cache_instance from 8 to 16
I would have thought that with over 180k tables that this change would have improved performance. Here’s the question, we also have the innodb_open_files parameter set to 110k. Is there a correlation between the table_open_cache and innodb_open_files parameters that would have caused serious issues within the DB if the table_open_cache is raised and the innodb_open_files paramter is not increase? When we had this configuration:
table_open_cache 400k
table_open_cache_instances 16
innodb_open_files = 100k
We were seeing incredible wait times for the opening_tables wait event. When we reverted our changes to:
table_open_cache 100k
table_open_cache_instance 8
Our performance increased to where is was before the degradation and the opening_tables wait event disappeared.
Any insight into a relationship or not would be helpful. Also, any insight into why we saw the degradation would also be helpful. The only variables that changed through this process are as indicated above.
Thanks,
Erik