Aggregating data with help tables will raise open table cache - solution?
Hello, I need some inspiration :-)
Currently we have 10 tables, each around 3 million rows and about 100 columns in MyISAM format. These table are only for writing data. A cronjob will aggregate data from these tables to time defined tables, e.g. min, hour, day, etc. And here is the problem:
With CREATE TABLE and CREATE TEMPORARY TABLE there are difficulties. We create a table, read in data from main table, calculating values and write new data to time table. After that we delete the help table. But the table open cache gets bigger and bigger (>4000 in a day).
So how can we avoid this? Switching to InnoDB or use another schema of aggregating values?
Answer Answered by Ian Gilfillan in this comment.
The problem isn't explained clearly. table_open_cache is a system variable that you can set. It will not change. opened_tables is the status variable that will increase over time, but preferably slowly - see Optimizing table_open_cache. But you don't indicate if it's just this that is the problem, or if you are experiencing performance problems, and if so, where exactly.
