We a number of log tables on an Infobright server that is being used for providing near real-time analytics to our end users. When attempting to write directly to Infobright log tables, we immediately start noticing a significant number of queries stacking up in our mysql-ib processlist, lock times of 100+ seconds, ultimately resulting in deadlocks and exhausting client connections.
We currently have the same data structure using InnoDB tables on a separate set of servers (MySQL replication Master / Slave) where our application is inserting the data.

What are some best practices for possibly writing this data to an outfile (at a set interval) from each log table on our MySQL replication environment and then transferring / loading the corresponding table data on our Inforbright server? We only want to temporarily stage the data in our replication environment and bulk load on the Infobright side.

Any implementation suggestions or even another approach would be greatly appreciated.

Hi,

You can stage the data into a MyISAM table (let’s call it live_tbl).

At periodic intervals, freeze the MyISAM table and then archive the snapshot to Infobright: Like this:

Hope this helps,

Geoffrey

Hi Geoffrey,

We are staging all of our data on a separate MySQL server and all of our log/staging tables are using InnoDB. There are cases where our application collects various types of data and then stores it across multiple tables within a single transaction and some tables have foreign key dependencies.

The following statement is an interesting approach.

Since you are stating “atomic operation”, this means that the entire statement must be completed in its entirety and that there would be no chance that other clients performing inserts to ‘live_tbl’ would fail?

Since we have 2 separate MySQL environments…
1) MySQL Replication (1 Master and 1 Slave)
2) Dedicated Inforbright Server
What is the best way/approach/script for transferring the archived data from the (1) MySQL server to the (2) Dedicated Inforbright Server where we would then load the data into its corresponding ‘infobright_tbl’

Thank you for your insight!

Regards,
Scott

Scott W - 13 August 2010 03:59 PM

some tables have foreign key dependencies.

It should not be a problem to drop the temporary fact table ‘live_tbl’, since the FK dependencies all point in the other direction.

Since you are stating “atomic operation,” this means that the entire statement must be completed in its entirety and that there would be no chance that other clients performing inserts to ‘live_tbl’ would fail?

Yes, that’s what I’m saying. According to the MySQL documentation here, the multi-table rename is atomic for MyISAM and InnoDB tables. (It’s not atomic for Infobright tables, however.)

If you have multiple tables that collect data, then rotate them all in the single RENAME statement in one go. My example was only for a single table.

Since we have 2 separate MySQL environments…
1) MySQL Replication (1 Master and 1 Slave)
2) Dedicated Infobright Server
What is the best way/approach/script for transferring the archived data from the (1) MySQL server to the (2) Dedicated Infobright Server where we would then load the data into its corresponding ‘infobright_tbl’

You need to script the file transfer of the extract file over to the Infobright server, then execute the load there.

Geoffrey

Thank you Geoffrey for you feedback / input… it is greatly appreciated!

Scott