Have you thought about locking the reporting database for write? You could eliminate the dirty reads.
If you are using InnoDB on the reporting tables, you could use a transaction for the update operation. That would accomplish the same thing. You could use replication to move the load to another server all together. On Tue, 9 Nov 2004 16:06:18 -0800 (PST), Michael Haggerty <[EMAIL PROTECTED]> wrote: > 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] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
