maria-discuss team mailing list archive

Thread
Date

New Question: Aggregating data with help tables will raise open table cache - solution?

To: maria-discuss@xxxxxxxxxxxxxxxxxxx
From: AskMonty KB <noreply@xxxxxxxxxxxx>
Date: Mon, 04 Jul 2016 08:40:03 -0000

Hello,

A new question has been asked in "MariaDB Community" by TDS. Please answer it at http://mariadb.com/kb/en/aggregating-data-with-help-tables-will-raise-open-table-cache-solution/ as the person asking the question may not be subscribed to the mailing list.

--------------------------------
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?
--------------------------------

To view or answer this question please visit: http://mariadb.com/kb/en/aggregating-data-with-help-tables-will-raise-open-table-cache-solution/