Re: MySql table disappears at random after multiple TRUNCATE queries
Posted by:
Rick James
Date: September 24, 2014 12:13AM
Perhaps you are looking for a similar trick...
This is where data is coming in fast, but it needs to be staged in a temp table and processed, then tossed
Two tables:
* One (or more) threads are inserting into the `Staging` table.
* Another table, `Process`, is being processed by a single thread.
This thread is in a tight loop:
1. Swap Staging and Process, using this fast, atomic, statement:
RENAME TABLE Staging TO Tmp, Process TO Staging, Tmp TO Process;
2. Munch on whatever is in Process.
3. TRUNCATE TABLE Process. (Since only this thread cares about `Process`, your original problem will not arise.)
This is self-regulating:
When data is coming in faster than usual, the tables get bigger, but the processing thread is more efficient.
When data is coming in slower, then the tables are smaller, and the thread is less efficient.
I have used this technique in a Data Warehousing situation where the data was coming in too fast to go directly into the Fact table. This technique worked out beautifully. The "munching" included normalization (before inserting into Fact) and summarization into summary tables. More rows == more efficiency in munching.