Starting with 4.0, when you do a LOAD DATA INFILE on the master, it actually writes the full insert in the binary log, which the slave then reproduces.
And if any gurus are listening, I /believe/ that setting max_allowed_packet on the master and slave to the same value prevents any "Packet too large" problems, but I couldn't find confirmation in the docs. If I set max_allowed_packet to, say, 16M on the master, does it write the data from a LOAD DATA INFILE command in 16M chunks to the binary log?
____________________________________________________________ Eamon Daly
----- Original Message ----- From: "Michael Haggerty" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, November 10, 2004 11:27 AM
Subject: Re: Best Practices
Yes, there can be a small lag in data updates, in fact I believe the lag time will be less than a second considering our architecture.
We have been considering replication as a solution but have been hesitant to do so because I have heard there are problems with data inserted through a LOAD DATA INFILE command. We regularly import csv data from spreadsheets from people working offline and have some pretty sophisticated processes built around this requirement.
Has anyone run into this problem, and are there any solutions?
Thanks, Michael Haggerty
--- Eamon Daly <[EMAIL PROTECTED]> wrote:
____________________________________________________________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]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
