Mailing List Archive: DBMail: users

MySQL fail-over with different server-ids

Index | Next | Previous | View Threaded

john at feurix

Sep 28, 2009, 3:02 PM

Post #1 of 8 (1119 views)
Permalink

MySQL fail-over with different server-ids

Hi,

we're using a MySQL 5.1 Multi-Master setup as our DBMail back-end.
Since there is no native support for read/write splitting (and mysql
replication is asynchronous) we're always using only one database node
directly, while the other is intended for fail-over.

The MySQL server configurations include:

# PRIMARY
server-id = 1
auto_increment_increment = 10
auto_increment_offset = 1
replicate-same-server-id = 0

# SECONDARY (fail-over node)
server-id = 2
auto_increment_increment = 10
auto_increment_offset = 2
replicate-same-server-id = 0

This way we get rid of collisions on auto-incrementing columns.

If I recall correctly DBMail uses some auto-incrementing columns to
identify new messages and announce them to IMAP clients in IDLE (and
probably more)?

So, let's suppose we have a sequential auto-incrementing column:
1 11 21 31 41 51

Now we fail-over to the secondary node and the column ends up
non-sequential:
1 2 11 12 21 22 31 32 41 42 51 52 62 72 82

What exacly happens now? Which parts of DBMail will be mislead by this?

In addition, I found an interesting old mail (Jun 2004):

http://www.mail-archive.com/dbmail-dev [at] dbmail/msg01706.html

> The main problem is the requirement of the IMAP protocol to have a unique,
> persistent, incrementing, 32 bit number for each message in each mailbox.
> AFAIK, there's no guaranteed way of doing this in multimaster :-[

After reading this I'm pretty sure something won't work as expected
after fail-over. Is this still the case?

How do you guys handle this problem?

Thanks,
John
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

john at feurix

Sep 28, 2009, 3:16 PM

Post #2 of 8 (1072 views)
Permalink

Re: MySQL fail-over with different server-ids [In reply to]

Eww, let me rephrase that:

> So, let's suppose we have a sequential auto-incrementing column:
> 1 11 21 31 41 51
>
> Now we fail-over to the secondary node and the column ends up
> non-sequential:
> 1 2 11 12 21 22 31 32 41 42 51 52 62 72 82

The resulting column's keys are sequential, but the correct order of
inserted messages can't be derived from this anymore.
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

josh at worldhosting

Sep 28, 2009, 3:41 PM

Post #3 of 8 (1071 views)
Permalink

Re: MySQL fail-over with different server-ids [In reply to]

On Tue, 2009-09-29 at 00:16 +0200, John Feuerstein wrote:
> Eww, let me rephrase that:
>
> > So, let's suppose we have a sequential auto-incrementing column:
> > 1 11 21 31 41 51
> >
> > Now we fail-over to the secondary node and the column ends up
> > non-sequential:
> > 1 2 11 12 21 22 31 32 41 42 51 52 62 72 82
>
> The resulting column's keys are sequential, but the correct order of
> inserted messages can't be derived from this anymore.

Something you've missed is that the mysql will always increment. So it
wont insert like:

1 2 11 12 21 22 31 32 41 42 51 {switchover} 52 62 72 82

But like:

1 11 21 31 41 51 {switchover} 52 62 72 82 92 102 112...

It will only interleave if you have the two masters actively inserting
at the same time in a round-robin fashion. Failover using heartbeat or
ucarp will avoid this.

As long as your db's are in sync you wont get any problems. We've got a
similar setup and have had it change over a number of times with no ill
effects.

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

john at feurix

Sep 28, 2009, 3:59 PM

Post #4 of 8 (1066 views)
Permalink

Re: MySQL fail-over with different server-ids [In reply to]

Josh Marshall wrote:
> It will only interleave if you have the two masters actively inserting
> at the same time in a round-robin fashion. Failover using heartbeat or
> ucarp will avoid this.

Great! I must have missed that... :/

Thanks.

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

josh at worldhosting

Sep 28, 2009, 5:32 PM

Post #5 of 8 (1064 views)
Permalink

Re: MySQL fail-over with different server-ids [In reply to]

On Mon, 2009-09-28 at 17:05 -0700, tabris wrote:
> Additionally, we do (at my employer) have master<->master replication
> setup with both sides able to insert, and afaict everything works fine
> except for the occasional clash over the headername table.
It's been advised against doing this, do a search through the mailing
list archives. There is an issue with IMAP message id's (not
auto-increment fields) so some new messages wont be shown on the
logged-in imap clients. Might be ok for mostly POP3 installs or for low
usage. How many messages per day is this setup storing? What happens
when one server starts to lag with the updates?

The clashes in the headername table rings alarm bells for me. If your
setup starts to get under stress or has a lot of concurrent requests
these problems will escalate. If you're going to use master-master
replication setup, just use ucarp or heartbeat and only have one server
active. It will save you headaches down the track.

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

tabris at tabris

Sep 28, 2009, 7:05 PM

Post #6 of 8 (1074 views)
Permalink

Re: MySQL fail-over with different server-ids [In reply to]

Josh Marshall wrote:
> It will only interleave if you have the two masters actively inserting
> at the same time in a round-robin fashion. Failover using heartbeat or
> ucarp will avoid this.
>
> As long as your db's are in sync you wont get any problems. We've got a
> similar setup and have had it change over a number of times with no ill
> effects.
Additionally, we do (at my employer) have master<->master replication
setup with both sides able to insert, and afaict everything works fine
except for the occasional clash over the headername table.

Attachments:

signature.asc (0.25 KB)

tabris at tabris

Sep 28, 2009, 7:07 PM

Post #7 of 8 (1063 views)
Permalink

Re: MySQL fail-over with different server-ids [In reply to]

Josh Marshall wrote:
> On Mon, 2009-09-28 at 17:05 -0700, tabris wrote:
>
>> Additionally, we do (at my employer) have master<->master replication
>> setup with both sides able to insert, and afaict everything works fine
>> except for the occasional clash over the headername table.
>>
> It's been advised against doing this, do a search through the mailing
> list archives. There is an issue with IMAP message id's (not
> auto-increment fields) so some new messages wont be shown on the
> logged-in imap clients.

So far this has been minimal, albeit probably not nonexistent.

> Might be ok for mostly POP3 installs or for low
> usage. How many messages per day is this setup storing? What happens
> when one server starts to lag with the updates?
>

Don't know how many messages per day (don't have a quick way to
measure it at the moment) but it's just ~60users or so. And actually,
the lag hasn't been too bad, esp given the note below. I expect we can
find ways around that problem eventually [programmatically].

> The clashes in the headername table rings alarm bells for me. If your
> setup starts to get under stress or has a lot of concurrent requests
> these problems will escalate. If you're going to use master-master
> replication setup, just use ucarp or heartbeat and only have one server
> active. It will save you headaches down the track.
>

I'm sure that what I'm doing is undocumented. And no, we can't use
heartbeat for this, it's being done across the Atlantic, so that any
user can login to either server at either site and still retrieve their
email. The only rule is they're not supposed to access both servers from
IMAP at the same time.

Attachments:

signature.asc (0.25 KB)

paul at nfg

Sep 29, 2009, 2:37 AM

Post #8 of 8 (1065 views)
Permalink

Re: MySQL fail-over with different server-ids [In reply to]

Josh Marshall wrote:

> As long as your db's are in sync you wont get any problems. We've got a
> similar setup and have had it change over a number of times with no ill
> effects.

Basically, mysql failover is ok, but multi-master insertion is not.

--
________________________________________________________________
Paul Stevens paul at nfg.nl
NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31
The Netherlands________________________________http://www.nfg.nl
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads