List:General Discussion« Previous MessageNext Message »
From:Eamon Daly Date:November 10 2004 7:24pm
Subject:Re: Best Practices
View as plain text  
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" <techsoldaten@stripped>
To: <mysql@stripped>
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 <edaly@stripped> 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" <techsoldaten@stripped>
>> To: <mysql@stripped>
>> 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/mysql?unsub=1
> 

Thread
Best PracticesMichael Haggerty10 Nov
  • Re: Best PracticesGary Richardson10 Nov
  • Re: Best PracticesKarma Dorji10 Nov
  • Re: Best PracticesSGreen10 Nov
  • Re: Best PracticesEamon Daly10 Nov
    • Re: Best PracticesMichael Haggerty10 Nov
      • Re: Best PracticesEamon Daly10 Nov