MySQL Forums
Forum List  »  Performance

Re: Optimizing table_cache - Open files?
Posted by: paul ernest
Date: August 04, 2011 09:04AM

Hi I asked a very similar question just a couple of post's ago http://forums.mysql.com/read.php?24,429013,429013#msg-429013.

I can't answer you question, but I hope I can add one or two pieces of helpful information that I've picked up along the way. A lot of what follows is educated guesswork and I'm hoping that other people will correct my mistakes.

If I am correct, then any given table can be simultaneously open multiple times. Each connection to the mysql server opens tables independently, so the table my_table could be open 100 times, one for each connection to the database.

The number of tables that need to be simultaneously open for a connection is determined by the queries run through that connection. If the connection is running a query with a join, then that connection will need to open all tables referenced in the join.

With max_connection=100 and 200 tables, then (apart from temporary tables), you will never need a table cache more than 100*200=20000. I think this is a massive overestimate, as it assumes that each of the 100 connections has a join query with all tables in the join, which is very improbable. If you were using joins referencing a maximum of say 10 tables, then a table cache of 100*10=1000 would be more than sufficient.

When I do the following query on the database
SHOW GLOBAL STATUS LIKE '%max_used_connections%';
showing that the maximum number of simultaneous connections to the database was 8. So I guess if you had a similar number, then the maximum number of tables needed for the table cache would be 8*10 = 80

The way I understand it, the max_used_connections*[maximum number of joins in queries] is an absolute lower limit to the size of the table_cache, because if the database is attempting to join tables and doesn't have the table cache to have all the tables open in memory, then the query is going to be slow, if not impossible.

For an upper limit on table_cache, if over time each connection opens all tables, then you will need max_used_connections*[number of tables] to prevent any tables being loaded to and from disk. I guess the actual number doesn't need to be this high as it doesn't matter if some tables are loaded from disk now and then.

I'm going to check the number of open tables on my server every 24 hours, and calculate the average number of tables opened per minute. When I know this, I will increase the table_cache and see what difference it make.

I hope that there was some useful information in this post.

Options: ReplyQuote


Subject
Views
Written By
Posted
9474
August 04, 2011 05:08AM
Re: Optimizing table_cache - Open files?
3489
August 04, 2011 09:04AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.