Can there be a small lag between servers? If a second or two is acceptable, this sounds like a perfect environment for replication:
http://dev.mysql.com/doc/mysql/en/Replication.html
Basically, when the master writes something to the database, it also logs the transaction to a log file. The slave simply reads that log file and executes the same transaction locally. The additional load is very very small, your tables will all be consistent, and you can index the reporting database six ways from Sunday without touching the master.
____________________________________________________________ Eamon Daly
----- Original Message ----- From: "Michael Haggerty" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, November 09, 2004 6:06 PM
Subject: Best Practices
I am working on a data warehousing solution involving mysql and have a question about best practices. We are standardized on mysql 4.1, and this is for a rather picky client.
We have a relational transaction database that stores the results of customer calls and a dimensional reporting database used as a data mart by several applications. Each night, we run a process that aggregates the number of calls, the subjects of each call, and various other data to populate the reporting database. We would like to move to a real time solution, and are struggling with the best way to implment it.
What we are considering is a solution where we mirror the transactional database and repopulate key tables in the reporting database every minute or few minutes. I am loathe to do this, mainly because it would add to our server load and could possibly lead to 'dirty reads' (i.e. where one table in the reporting database is populated with fresh data but others are not). At the same time, the client is demanding we implement something.
Does anyone have any war stories or suggestions for how to accomplish this?
Thank You, M
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
