FLUSH_TABLE Statement

[sterma]

hi guys,

does it make sense to flush all open tables in scheduled times? (maybe crontab)

the reason:
our database got much statements from our CMS portal every 10min. the open_tables value (512) inreases very fast in a few days finally until to the limit. so mysql starting to use tmp. tables.

I understand the mysql “open_table_cache” as follows:
mysql try to hold tables which are opened by any statement in the cache to save time by reopen it, right? so tehe open_table value normally is increasing an mysql keep the tables in cache for lifetime or until reboot…

but maybe this isnt´n nessecary. for an application (intranet) and CMS (database-syncs) which are sending a lot queries on different tables with differnt users, the table cache will reach the limit very fast. so what is better, to increase the open table limit or sending a flush table ever week?

thanks for every idea!!!

i hope you understand my english - its not the best confused:

greetz markus

[sterma]

well, no answer yet confused:

do I mispresent something? should I give more information?

thanks,

markus

[artur8ur]

Hi,

[B]Quote:[/B]

our database got much statements from our CMS portal every 10min. the open_tables value (512) inreases very fast in a few days finally until to the limit. so mysql starting to use tmp. tables.

This has nothing to do with the table cache.
The Cache is used to “cache” the open Filehandler and some metainformations about the open table-file. So the same table-file has not to be reopend, for each query which uses this table.
You can find more informations in the doc under: “7.4.8. How MySQL Opens and Closes Tables”

tmp Tables are used to store intermediate data, while processing a query. So each query might use a tmp(disk) table, to calculate the result. Longer runtime -> more queries -> more tmp tables.
But this has nothing todo with the table cache.
“7.5.9. How MySQL Uses Internal Temporary Tables”

To answer your question: No, there should be no reason to flush the open tables periodicity.

[sterma]

hi artur8ur,

thanks for your reply. thinks are clear now =)