How to handle large volume of Inserts between two DB's

[n1concepts]

Hi,

 

I got a requirement where I need to define a MySQL database setup (using two databases if best which is reason for question).

Here's the objective:

 

1st (primary task): setup a MySQL db that will allow multiple websites (email capture forms) to log or save emails to that database (with minimum latency).

Note: form validation, per website, will ensure the emails are properly formatted so only MySQL INSERT required for this (1st) db - no other processing in this regard.

 

2nd (secondary task): That 1st MySQL db which will be under constant load - receiving emails from various sites (this hourly number could average between 10 to twenty-five thousand which is why I need to offline this task solely to one db <I think>). However, as new emails are logged to this (1st) db, I need to then batch those saved records over to a 2nd db which will be performing a series of functions - processing the leads along with number crunching (data mining, etc...)

 

Illustration the setup below: once record logged in DB1 (which is required to house ALL data - long-term; DB2 only housing a short-interval of incoming records - this to reduce processing on new entries).

 

<Objective: get records over to DB2 without hampering DB1 on incoming INSERTS from websites>

DB1 ===> DB2

SQL INSERT

 

(Objective: DB1 needs to pass records over to DB2 while still capturing incoming emails with the lowest possible latency for end-user).

-----

 

Q: Should I setup transactions between DB1 & DB2 to pass the records into DB2 which - i think would induce latency back on DB1 b/c it will pause taking new emails while waiting for DB2 to accept or ignore new entries to that DB (FYI: DB2 will 'ignore' duplicate email entries).

 

Or

 

Is there a better way to batch the records from DB1 over to DB2 in a way DB1 won't get bogged down with that process so it continues to receive new emails - whcih will be constant and in high volume.

 

Comments welcome and I will elaborate more if/when required to help understand the objective.

Thanks!

[n1concepts]

FYI: I just had a thought - still welcome comments and/or sugggestions - but think i see a way to make this less complicated:

 

Instead of haveing two (separate) Db's, being the 1st DB is only capturing incoming emails ONLY; then passing them to the 2nd DB which will be the actual work-horse.

I'm thinking only have one DB (period) and create a specific table that will simply log all incoming entries then I can query that table to process those records - marking each with a flag as the server process each.

 

This will cut out alot of 'transit' latency and reduce CPU processing right off the bat!

Let me know thoughts and if there is a better solution.

 

Thx!