# table\_cache on db with many tables

**URL:** <https://forums.percona.com/t/table-cache-on-db-with-many-tables/558>\
**Category:** Other MySQL® Questions\
**Created:** [December 7, 2007, 10:03am UTC](https://forums.percona.com/t/table-cache-on-db-with-many-tables/558 "2007-12-07T10:03:31Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![alst74](https://avatars.discourse-cdn.com/v4/letter/a/ecae2f/32.png) [@alst74](https://forums.percona.com/u/alst74)\
**Post date:** [December 7, 2007, 10:03am UTC](https://forums.percona.com/t/table-cache-on-db-with-many-tables/558/1 "2007-12-07T10:03:31Z")

</div>

Intro:  
I do have a db with pretty many tables, from 5k-20k. Each table has 20k rows.  
Tables contain info from a certain date and time.

I’m not sure exactly where to start tuning, but I did run tuning-primer script which among a couple of other parameters did recommend me to change “table\_cache”.

It gave me this recommendation for table\_cache:  
TABLE CACHE  
Current table\_cache value = 1024 tables  
You have a total of 6551 tables  
You have 1024 open tables.  
Current table\_cache hit rate is 2%, while 100% of your table cache is in use  
You should probably increase your table\_cache

Not sure I do understand this table\_cache parameter. I mean, if I understood correctly I do cache tables that is not in use. And what does table\_cache really contain, does it only cache the table’s physical location?

Also, I guess on systems like this there’s only waste with memory to try to apply it to the query cache since no query’s look the same. They are very often with different timestamps.  
I guess it would be better to try to make it fast to read from disk instead?

Here’s some more info and would be happy if someone could give me some advice eek:

Uptime: 19056 Threads: 29 Questions: 6044010 Slow queries: 6 Opens: 52758 Flush tables: 4 Open tables: 6 Queries per second avg: 317.171

mysql\> show global status  
 → ;  
±----------------------------------±-----------+  
| Variable\_name | Value |  
±----------------------------------±-----------+  
| Aborted\_clients | 4 |  
| Aborted\_connects | 0 |  
| Binlog\_cache\_disk\_use | 0 |  
| Binlog\_cache\_use | 0 |  
| Bytes\_received | 2764303709 |  
| Bytes\_sent | 109773773 |  
| Com\_admin\_commands | 92 |  
| Com\_alter\_db | 0 |  
| Com\_alter\_table | 0 |  
| Com\_analyze | 0 |  
| Com\_backup\_table | 0 |  
| Com\_begin | 0 |  
| Com\_change\_db | 15 |  
| Com\_change\_master | 0 |  
| Com\_check | 0 |  
| Com\_checksum | 0 |  
| Com\_commit | 0 |  
| Com\_create\_db | 0 |  
| Com\_create\_function | 0 |  
| Com\_create\_index | 0 |  
| Com\_create\_table | 311 |  
| Com\_dealloc\_sql | 0 |  
| Com\_delete | 240 |  
| Com\_delete\_multi | 0 |  
| Com\_do | 0 |  
| Com\_drop\_db | 0 |  
| Com\_drop\_function | 0 |  
| Com\_drop\_index | 0 |  
| Com\_drop\_table | 240 |  
| Com\_drop\_user | 0 |  
| Com\_execute\_sql | 0 |  
| Com\_flush | 3 |  
| Com\_grant | 0 |  
| Com\_ha\_close | 0 |  
| Com\_ha\_open | 0 |  
| Com\_ha\_read | 0 |  
| Com\_help | 0 |  
| Com\_insert | 6042636 |  
| Com\_insert\_select | 0 |  
| Com\_kill | 0 |  
| Com\_load | 0 |  
| Com\_load\_master\_data | 0 |  
| Com\_load\_master\_table | 0 |  
| Com\_lock\_tables | 0 |  
| Com\_optimize | 0 |  
| Com\_preload\_keys | 0 |  
| Com\_prepare\_sql | 0 |  
| Com\_purge | 0 |  
| Com\_purge\_before\_date | 0 |  
| Com\_rename\_table | 0 |  
| Com\_repair | 0 |  
| Com\_replace | 0 |  
| Com\_replace\_select | 0 |  
| Com\_reset | 0 |  
| Com\_restore\_table | 0 |  
| Com\_revoke | 0 |  
| Com\_revoke\_all | 0 |  
| Com\_rollback | 0 |  
| Com\_savepoint | 0 |  
| Com\_select | 7723 |  
| Com\_set\_option | 264 |  
| Com\_show\_binlog\_events | 0 |  
| Com\_show\_binlogs | 0 |  
| Com\_show\_charsets | 0 |  
| Com\_show\_collations | 88 |  
| Com\_show\_column\_types | 0 |  
| Com\_show\_create\_db | 0 |  
| Com\_show\_create\_table | 0 |  
| Com\_show\_databases | 11 |  
| Com\_show\_errors | 0 |  
| Com\_show\_fields | 0 |  
| Com\_show\_grants | 0 |  
| Com\_show\_innodb\_status | 0 |  
| Com\_show\_keys | 0 |  
| Com\_show\_logs | 0 |  
| Com\_show\_master\_status | 0 |  
| Com\_show\_ndb\_status | 0 |  
| Com\_show\_new\_master | 0 |  
| Com\_show\_open\_tables | 1 |  
| Com\_show\_privileges | 0 |  
| Com\_show\_processlist | 0 |  
| Com\_show\_slave\_hosts | 0 |  
| Com\_show\_slave\_status | 0 |  
| Com\_show\_status | 150 |  
| Com\_show\_storage\_engines | 0 |  
| Com\_show\_tables | 2523 |  
| Com\_show\_triggers | 0 |  
| Com\_show\_variables | 240 |  
| Com\_show\_warnings | 0 |  
| Com\_slave\_start | 0 |  
| Com\_slave\_stop | 0 |  
| Com\_stmt\_close | 616 |  
| Com\_stmt\_execute | 6047898 |  
| Com\_stmt\_fetch | 0 |  
| Com\_stmt\_prepare | 684 |  
| Com\_stmt\_reset | 0 |  
| Com\_stmt\_send\_long\_data | 0 |  
| Com\_truncate | 0 |  
| Com\_unlock\_tables | 0 |  
| Com\_update | 308 |  
| Com\_update\_multi | 0 |  
| Com\_xa\_commit | 0 |  
| Com\_xa\_end | 0 |  
| Com\_xa\_prepare | 0 |  
| Com\_xa\_recover | 0 |  
| Com\_xa\_rollback | 0 |  
| Com\_xa\_start | 0 |  
| Compression | OFF |  
| Connections | 448 |  
| Created\_tmp\_disk\_tables | 40 |  
| Created\_tmp\_files | 0 |  
| Created\_tmp\_tables | 3148 |  
| Delayed\_errors | 0 |  
| Delayed\_insert\_threads | 0 |  
| Delayed\_writes | 0 |  
| Flush\_commands | 4 |  
| Handler\_commit | 0 |  
| Handler\_delete | 240 |  
| Handler\_discover | 0 |  
| Handler\_prepare | 0 |  
| Handler\_read\_first | 3 |  
| Handler\_read\_key | 445 |  
| Handler\_read\_next | 108 |  
| Handler\_read\_prev | 0 |  
| Handler\_read\_rnd | 113999 |  
| Handler\_read\_rnd\_next | 73083021 |  
| Handler\_rollback | 0 |  
| Handler\_savepoint | 0 |  
| Handler\_savepoint\_rollback | 0 |  
| Handler\_update | 308 |  
| Handler\_write | 6168493 |  
| Innodb\_buffer\_pool\_pages\_data | 0 |  
| Innodb\_buffer\_pool\_pages\_dirty | 0 |  
| Innodb\_buffer\_pool\_pages\_flushed | 0 |  
| Innodb\_buffer\_pool\_pages\_free | 0 |  
| Innodb\_buffer\_pool\_pages\_latched | 0 |  
| Innodb\_buffer\_pool\_pages\_misc | 0 |  
| Innodb\_buffer\_pool\_pages\_total | 0 |  
| Innodb\_buffer\_pool\_read\_ahead\_rnd | 0 |  
| Innodb\_buffer\_pool\_read\_ahead\_seq | 0 |  
| Innodb\_buffer\_pool\_read\_requests | 0 |  
| Innodb\_buffer\_pool\_reads | 0 |  
| Innodb\_buffer\_pool\_wait\_free | 0 |  
| Innodb\_buffer\_pool\_write\_requests | 0 |  
| Innodb\_data\_fsyncs | 0 |  
| Innodb\_data\_pending\_fsyncs | 0 |  
| Innodb\_data\_pending\_reads | 0 |  
| Innodb\_data\_pending\_writes | 0 |  
| Innodb\_data\_read | 0 |  
| Innodb\_data\_reads | 0 |  
| Innodb\_data\_writes | 0 |  
| Innodb\_data\_written | 0 |  
| Innodb\_dblwr\_pages\_written | 0 |  
| Innodb\_dblwr\_writes | 0 |  
| Innodb\_log\_waits | 0 |  
| Innodb\_log\_write\_requests | 0 |  
| Innodb\_log\_writes | 0 |  
| Innodb\_os\_log\_fsyncs | 0 |  
| Innodb\_os\_log\_pending\_fsyncs | 0 |  
| Innodb\_os\_log\_pending\_writes | 0 |  
| Innodb\_os\_log\_written | 0 |  
| Innodb\_page\_size | 0 |  
| Innodb\_pages\_created | 0 |  
| Innodb\_pages\_read | 0 |  
| Innodb\_pages\_written | 0 |  
| Innodb\_row\_lock\_current\_waits | 0 |  
| Innodb\_row\_lock\_time | 0 |  
| Innodb\_row\_lock\_time\_avg | 0 |  
| Innodb\_row\_lock\_time\_max | 0 |  
| Innodb\_row\_lock\_waits | 0 |  
| Innodb\_rows\_deleted | 0 |  
| Innodb\_rows\_inserted | 0 |  
| Innodb\_rows\_read | 0 |  
| Innodb\_rows\_updated | 0 |  
| Key\_blocks\_not\_flushed | 0 |  
| Key\_blocks\_unused | 211665 |  
| Key\_blocks\_used | 172847 |  
| Key\_read\_requests | 49317023 |  
| Key\_reads | 220681 |  
| Key\_write\_requests | 18672989 |  
| Key\_writes | 18672989 |  
| Last\_query\_cost | 0.000000 |  
| Max\_used\_connections | 32 |  
| Not\_flushed\_delayed\_rows | 0 |  
| Open\_files | 14 |  
| Open\_streams | 0 |  
| Open\_tables | 7 |  
| Opened\_tables | 52760 |  
| Qcache\_free\_blocks | 1 |  
| Qcache\_free\_memory | 16759744 |  
| Qcache\_hits | 163 |  
| Qcache\_inserts | 2715 |  
| Qcache\_lowmem\_prunes | 1648 |  
| Qcache\_not\_cached | 8020 |  
| Qcache\_queries\_in\_cache | 0 |  
| Qcache\_total\_blocks | 1 |  
| Questions | 6056630 |  
| Rpl\_status | NULL |  
| Select\_full\_join | 0 |  
| Select\_full\_range\_join | 0 |  
| Select\_range | 445 |  
| Select\_range\_check | 0 |  
| Select\_scan | 9959 |  
| Slave\_open\_temp\_tables | 0 |  
| Slave\_retried\_transactions | 0 |  
| Slave\_running | OFF |  
| Slow\_launch\_threads | 0 |  
| Slow\_queries | 6 |  
| Sort\_merge\_passes | 0 |  
| Sort\_range | 445 |  
| Sort\_rows | 119980 |  
| Sort\_scan | 1980 |  
| Ssl\_accept\_renegotiates | 0 |  
| Ssl\_accepts | 0 |  
| Ssl\_callback\_cache\_hits | 0 |  
| Ssl\_cipher | |  
| Ssl\_cipher\_list | |  
| Ssl\_client\_connects | 0 |  
| Ssl\_connect\_renegotiates | 0 |  
| Ssl\_ctx\_verify\_depth | 0 |  
| Ssl\_ctx\_verify\_mode | 0 |  
| Ssl\_default\_timeout | 0 |  
| Ssl\_finished\_accepts | 0 |  
| Ssl\_finished\_connects | 0 |  
| Ssl\_session\_cache\_hits | 0 |  
| Ssl\_session\_cache\_misses | 0 |  
| Ssl\_session\_cache\_mode | NONE |  
| Ssl\_session\_cache\_overflows | 0 |  
| Ssl\_session\_cache\_size | 0 |  
| Ssl\_session\_cache\_timeouts | 0 |  
| Ssl\_sessions\_reused | 0 |  
| Ssl\_used\_session\_cache\_entries | 0 |  
| Ssl\_verify\_depth | 0 |  
| Ssl\_verify\_mode | 0 |  
| Ssl\_version | |  
| Table\_locks\_immediate | 6051191 |  
| Table\_locks\_waited | 28 |  
| Tc\_log\_max\_pages\_used | 0 |  
| Tc\_log\_page\_size | 0 |  
| Tc\_log\_page\_waits | 0 |  
| Threads\_cached | 3 |  
| Threads\_connected | 29 |  
| Threads\_created | 33 |  
| Threads\_running | 1 |  
| Uptime | 19092 |  
±----------------------------------±-----------+  
245 rows in set (0.00 sec)

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [December 7, 2007, 5:03pm UTC](https://forums.percona.com/t/table-cache-on-db-with-many-tables/558/2 "2007-12-07T17:03:07Z")

</div>

table\_cache doesn’t really have anything to do with caching of the table itself.

The value defines how many tables MySQL can hold open at the same time.  
For each opened table MySQL requires a couple of file descriptors from the OS. And since some OS’s put a limit on how many file descriptors a process are allowed to open you have a limit for this in MySQL.  
Each file descriptor takes actually a pretty small amount of memory so you can usually safely increase this.

But the real question is why you have so many small tables?  
Are all these tables identical in design it’s just that they store data from different times or?  
Because whenever I hear about someone having so many small tables I associate it with an overly partitioned table due to a poor design.

---

<div class="post-metadata">

**Author:** ![erkules](https://avatars.discourse-cdn.com/v4/letter/e/db5fbb/32.png) [@erkules](https://forums.percona.com/u/erkules)\
**Post date:** [December 7, 2007, 7:35pm UTC](https://forums.percona.com/t/table-cache-on-db-with-many-tables/558/3 "2007-12-07T19:35:17Z")

</div>

| [B]Quote:[/B] |
| For each opened table MySQL requires a couple of file descriptors from the OS. |

Im courios. On Linux/UNIX you open a File and get 3FD (STDIN,STDOUT and STDERR). So if you open a Table in MySQL you (if we count the Indexfile also) have 6 FD?

So if open tables means FDs it seems to be smaler as you would expect…  
Or is MySQL openening the file with 2 FD?

couriositiy killed the cat:-)