Frequent data inflow

Megha

I have a table which is getting populated in real time - getting new data almost every minute or so. Additionally I need to retrieve and process the new data as and when it comes into the system. What kind of structuring you think will be appropriate in this scenario?

Each data rows is limited in size - maximun 40 bytes.

I think of keeping a Recent_Data table that will capture the incoming data and replicating this data to Old_Data table that will get populated simultaneously with Recent_Data table.

As number of rows will grow with time, I will keep data for a certain period in Recent_Data. Though Old_Data will contain past data as well as Recent_Data because I need to have access to both recent and past data.

My Select operations for past data can be directed to Old_Data and for recent data I will access Recent_Data. Do you think this is good to access Recent_Data when at the same time this might be receiving the incoming data (as I said, data inflow is frequent).

Suggestions are invited!
Megha

LordShryku

Tell me you're not using MySQL......

Cuz with a DB like Postgres or Oracle, you could write a trigger to automatically do the datashift for you.

I'm wondering why you don't just put it all in one table though...what sort of "processing" needs to be done?

Megha

Yes I am using MySQL!

I do not want to keep a single table because as the table will grow Inserts and Selects will become slower. And I require consistent speed for these operations.

So I was thinking to separate Recent and Old data so that the more frequent queries concerning Recent data can go to the Recent_Data table and less frequent (relatively) Select queries for Old data can go to Old_Data table.

If I keep Recent_Data smaller then my Inserts can be faster than issuing an Insert against a table containing all old and recent data. Also I might not need to wait longer to retrieve the newly arrived data which will happen in case a single long table.

I am not sure of the efficiency of above structure in MySQL - so any feedback is welcome!

Megha

LordShryku

Well, it will work, but you're going to need to make a cron job to move the data.

chriskl

What you need is TelegraphCQ:

http://telegraph.cs.berkeley.edu/telegraphcq/v0.2/

However, it's complex and probably not production stable yet.

It is a postgresql mod that allows you to run CONTINUOUS queries over data streams.

Chris