Unique ID"s across multiple databases

--0015174c34ca29a3e004901391cd
Content-Type: text/plain; charset=UTF-8

Hi,

I'm designing a master-to-master replication architecture.
I wonder what the best way is to make sure both databases generate unique
row ID's, so there won't be ID conflicts when replicating both directions.

I read on forums about pro's and con's using UUID's, also about setting the
*auto-increment-increment *and *auto-increment-offset* system variables.
I'm not a fan of UUID's for this purpose. They are two big, slow to
generate, and they don't 100% guarantee global uniqueness anyway.
On the other hand, the *auto-increment-offset *trick limits the number of
possible databases in the system in advance, which is not so nice.

So, I'm thinking about a composite unique ID system, where the value of the
*server-id *system variable is always a part of the ID. The server-id has to
be set uniquely anyway for any replication architecture in MySQL.

A sample would look like this:

CREATE TABLE SampleParents (
ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
SID SMALLINT UNSIGNED NOT NULL,

SampleData VARCHAR(50) NOT NULL,

PRIMARY KEY (ID, SID)) ENGINE=InnoDB;

CREATE TABLE SampleChildren (
ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
SID SMALLINT UNSIGNED NOT NULL,

SampleParentID INT UNSIGNED NOT NULL,
SampleParentSID SMALLINT UNSIGNED NOT NULL,

SampleData VARCHAR(50) NOT NULL,

PRIMARY KEY (ID, SID),
KEY (SampleParentID, SampleParentSID),

CONSTRAINT FOREIGN KEY (SampleParentID, SampleParentSID) REFERENCES
SampleParents (ID, SID)) ENGINE=InnoDB;

Where SID is always filled with the value of the @@server_id global variable
according to the server where the record is created.

How I see it is that it has the following advantages.

- The ID+SID pair guarantees pripary key uniqueness within the
replication array.
- Auto_increment is a magnitude faster than generating UUID's.
- SID adds only 2 bytes in this case to the size of the primary key item.
It can be even 1 byte if I'm sure I'll never exceed maximum 255 servers. But
anyhow, it is still way smaller than the 16 byte of a UUID field, even if
using BIGINT's.
- Keeps the efficiency of the primary key indexing, because the key is
still very close to a strictly incremental value.

Well, you can see what the obvious disadvantage is:

- Primary and foreign keys are always double fields. This is not so
convinient when you are joining tables and add the WHERE clauses to your
queries. It might even negatively affect the evaluation speed of join
conditions, although I have no idea yet, how much. (My gut feeling is that
it's not a big issue, due to the good query optimizer of MySQL.)

My question is. Does anyone have any better idea, how to approach this
problem?

Thanks,

--0015174c34ca29a3e004901391cd--

Re: Unique ID"s across multiple databases

--Apple-Mail-38-1069422632
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
charset=iso-8859-1

On 12 Sep 2010, at 19:47, Kiss D=E1niel wrote:

> - SID adds only 2 bytes in this case to the size of the primary key =
item.
> It can be even 1 byte if I'm sure I'll never exceed maximum 255 =
servers. But
> anyhow, it is still way smaller than the 16 byte of a UUID field, =
even if
> using BIGINT's.

You can maintain your own sequence tables a la postgres if you use =
transactions to ensure atomicity, though that doesn't help across =
databases (I suspect the same is true in postgres).

FWIW my auto_increment_offset value is usually the same as my server ID. =
auto_increment_increment also reduces the number of usable values for =
integer keys too; e.g if it's 10, you've reduced your key space by a =
factor of 10 too, meaning you may need to use bigints if you potentially =
have more than 400m records in any given table.

One small observation that I've seen work is to use binary fields for =
UUIDs and always wrap access to them in hex/unhex functions. You halve =
your storage requirements for the same amount of data that way.

Marcus=

--Apple-Mail-38-1069422632
Content-Disposition: attachment;
filename=smime.p7s
Content-Type: application/pkcs7-signature;
name=smime.p7s
Content-Transfer-Encoding: base64

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEH AQAAoIILHzCCBN0w
ggPFoAMCAQICEHGS++YZX6xNEoV0cTSiGKcwDQYJKoZIhvcNAQEFBQAwezEL MAkGA1UEBhMCR0Ix
GzAZBgNVBAgMEkdyZWF0ZXIgTWFuY2hlc3RlcjEQMA4GA1UEBwwHU2FsZm9y ZDEaMBgGA1UECgwR
Q29tb2RvIENBIExpbWl0ZWQxITAfBgNVBAMMGEFBQSBDZXJ0aWZpY2F0ZSBT ZXJ2aWNlczAeFw0w
NDAxMDEwMDAwMDBaFw0yODEyMzEyMzU5NTlaMIGuMQswCQYDVQQGEwJVUzEL MAkGA1UECBMCVVQx
FzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVUaGUgVVNFUlRS VVNUIE5ldHdvcmsx
ITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTE2MDQGA1UEAxMt VVROLVVTRVJGaXJz
dC1DbGllbnQgQXV0aGVudGljYXRpb24gYW5kIEVtYWlsMIIBIjANBgkqhkiG 9w0BAQEFAAOCAQ8A
MIIBCgKCAQEAsjmFpPJ9q0E7YkY3rs3BYHW8OWX5ShpHornMSMxqmNVNNRm5 pELlzkniii8efNIx
B8dOtINknS4p1aJkxIW9hVE1eaROaJB7HHqkkqgX8pgV8pPMyaQylbsMTzC9 mKALi+VuG6JG+ni8
om+rWV6lL8/K2m2qL+usobNqqrcuZzWLeeEeaYji5kbNoKXqvgvOdjp6Dpvq /NonWz1zHyLmSGHG
TPNpsaguG7bUMSAsvIKKjqQOpdeJQ/wWWq8dcdcRWdq6hw2v+vPhwvCkxWeM 1tZUOt4KpLoDd7Nl
yP0e03RiqhjKaJMeoYV+9Udly/hNVyh00jT/MLbu9mIwFIws6wIDAQABo4IB JzCCASMwHwYDVR0j
BBgwFoAUoBEKIz6W8Qfs4q8p74Klf9AwpLQwHQYDVR0OBBYEFImCZ33EnSZw AEu0UEh83j2uBG59
MA4GA1UdDwEB/wQEAwIBBjAPBgNVHRMBAf8EBTADAQH/MB0GA1UdJQQWMBQG CCsGAQUFBwMCBggr
BgEFBQcDBDARBgNVHSAECjAIMAYGBFUdIAAwewYDVR0fBHQwcjA4oDagNIYy aHR0cDovL2NybC5j
b21vZG9jYS5jb20vQUFBQ2VydGlmaWNhdGVTZXJ2aWNlcy5jcmwwNqA0oDKG MGh0dHA6Ly9jcmwu
Y29tb2RvLm5ldC9BQUFDZXJ0aWZpY2F0ZVNlcnZpY2VzLmNybDARBglghkgB hvhCAQEEBAMCAQYw
DQYJKoZIhvcNAQEFBQADggEBAJ2Vyzy4fqUJxB6/C8LHdo45PJTGEKpPDMng q4RdiVTgZTvzbRx8
NywlVF+WIfw3hJGdFdwUT4HPVB1rbEVgxy35l1FM+WbKPKCCjKbI8OLp1Er5 7D9Wyd12jMOCAU9s
APMeGmF0BEcDqcZAV5G8ZSLFJ2dPV9tkWtmNH7qGL/QGrpxp7en0zykX2OBK nxogL5dMUbtGB8SK
N04g4wkxaMeexIud6H4RvDJoEJYRmETYKlFgTYjrdDrfQwYyyDlWjDoRUtNB pEMD9O3vMyfbOeAU
TibJ2PU54om4k123KSZB6rObroP8d3XK6Mq1/uJlSmM+RMTQw16Hc6mYHK9/ FX8wggY6MIIFIqAD
AgECAhBBV9gXbkp6FaM+2kR6WxnRMA0GCSqGSIb3DQEBBQUAMIGuMQswCQYD VQQGEwJVUzELMAkG
A1UECBMCVVQxFzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVU aGUgVVNFUlRSVVNU
IE5ldHdvcmsxITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTE2 MDQGA1UEAxMtVVRO
LVVTRVJGaXJzdC1DbGllbnQgQXV0aGVudGljYXRpb24gYW5kIEVtYWlsMB4X DTA5MTEzMDAwMDAw
MFoXDTEwMTEzMDIzNTk1OVowgeMxNTAzBgNVBAsTLENvbW9kbyBUcnVzdCBO ZXR3b3JrIC0gUEVS
U09OQSBOT1QgVkFMSURBVEVEMUYwRAYDVQQLEz1UZXJtcyBhbmQgQ29uZGl0 aW9ucyBvZiB1c2U6
IGh0dHA6Ly93d3cuY29tb2RvLm5ldC9yZXBvc2l0b3J5MR8wHQYDVQQLExYo YykyMDAzIENvbW9k
byBMaW1pdGVkMRcwFQYDVQQDEw5NYXJjdXMgQm9pbnRvbjEoMCYGCSqGSIb3 DQEJARYZbWFyY3Vz
QHN5bmNocm9tZWRpYS5jby51azCCASIwDQYJKoZIhvcNAQEBBQADggEPADCC AQoCggEBAL5L7Qr7
suUjTu4DkUPXc5tpggz2j6On8SrX7OaPGnhpu4SNHSGshoRULXA7Qa8mMgl8 zscAP+I1OC2OXqD7
KtJ3HmuP5CxgmF3XPP07+mAwPAxUrXOxRIefdzTvWDdPidPEINa29a6NtRfk 3amsznkAboPQvz4V
dWgsL40JPMpI5fDrKrG2ipqks7IUiMLF1mlcQ8iViCL7J6hCIaNSfNhGr60x mjDnIj4XdJsCnI6l
iH5/W8LRMLVq6L1rO7huVj8o/WmBI2yvAk2yxrB1E8LFDJc9TTzTo6/6vf5s WDy1MwW24UR6jlgQ
Dzl949nw7n2KlhjyMECpJSN9fnYIAPECAwEAAaOCAhswggIXMB8GA1UdIwQY MBaAFImCZ33EnSZw
AEu0UEh83j2uBG59MB0GA1UdDgQWBBTYpSNB8HEGPyg8Ac2Qy3kyXWkz2DAO BgNVHQ8BAf8EBAMC
BaAwDAYDVR0TAQH/BAIwADAgBgNVHSUEGTAXBggrBgEFBQcDBAYLKwYBBAGy MQEDBQIwEQYJYIZI
AYb4QgEBBAQDAgUgMEYGA1UdIAQ/MD0wOwYMKwYBBAGyMQECAQEBMCswKQYI KwYBBQUHAgEWHWh0
dHBzOi8vc2VjdXJlLmNvbW9kby5uZXQvQ1BTMIGlBgNVHR8EgZ0wgZowTKBK oEiGRmh0dHA6Ly9j
cmwuY29tb2RvY2EuY29tL1VUTi1VU0VSRmlyc3QtQ2xpZW50QXV0aGVudGlj YXRpb25hbmRFbWFp
bC5jcmwwSqBIoEaGRGh0dHA6Ly9jcmwuY29tb2RvLm5ldC9VVE4tVVNFUkZp cnN0LUNsaWVudEF1
dGhlbnRpY2F0aW9uYW5kRW1haWwuY3JsMGwGCCsGAQUFBwEBBGAwXjA2Bggr BgEFBQcwAoYqaHR0
cDovL2NydC5jb21vZG9jYS5jb20vVVROQUFBQ2xpZW50Q0EuY3J0MCQGCCsG AQUFBzABhhhodHRw
Oi8vb2NzcC5jb21vZG9jYS5jb20wJAYDVR0RBB0wG4EZbWFyY3VzQHN5bmNo cm9tZWRpYS5jby51
azANBgkqhkiG9w0BAQUFAAOCAQEAnn9qxZUoFHNbuhRW9wFZVdb3x/L6vPCB 2Fawl4shKA2qYj7D
piHV8cvPHeA3Le12hAVP+pX3mWhbOt0bluX2Wug7lOeDu43KWisUwSu9qAna hSgOCX7bHUORN6k3
xdVrGryS++/OmS5HGkjnNfNnlZEsn6BLNH0fgJPkgWIQePaoEyY1zRzi+GQk trNsNBW8nvAHDnfz
BHxN0eI0FgIbeASEBoSuVk+tudjljaVeSsHJH1bLhFiBzIDO++j0yY5S4DY5 MRQH+eHra7TV2hjG
JiiApZUYhm+ROEa6Gxkj23/cuLaaHoL+KzcWbK0s7hqGXKI+4sD8mUNeHpVO VAq76TGCA/wwggP4
AgEBMIHDMIGuMQswCQYDVQQGEwJVUzELMAkGA1UECBMCVVQxFzAVBgNVBAcT DlNhbHQgTGFrZSBD
aXR5MR4wHAYDVQQKExVUaGUgVVNFUlRSVVNUIE5ldHdvcmsxITAfBgNVBAsT GGh0dHA6Ly93d3cu
dXNlcnRydXN0LmNvbTE2MDQGA1UEAxMtVVROLVVTRVJGaXJzdC1DbGllbnQg QXV0aGVudGljYXRp
b24gYW5kIEVtYWlsAhBBV9gXbkp6FaM+2kR6WxnRMAkGBSsOAwIaBQCgggIN MBgGCSqGSIb3DQEJ
AzELBgkqhkiG9w0BBwEwHAYJKoZIhvcNAQkFMQ8XDTEwMDkxMjE5MjE0MFow IwYJKoZIhvcNAQkE
MRYEFHpND3cGNpNk77IJpFcm9MFcB+0lMIHUBgkrBgEEAYI3EAQxgcYwgcMw ga4xCzAJBgNVBAYT
AlVTMQswCQYDVQQIEwJVVDEXMBUGA1UEBxMOU2FsdCBMYWtlIENpdHkxHjAc BgNVBAoTFVRoZSBV
U0VSVFJVU1QgTmV0d29yazEhMB8GA1UECxMYaHR0cDovL3d3dy51c2VydHJ1 c3QuY29tMTYwNAYD
VQQDEy1VVE4tVVNFUkZpcnN0LUNsaWVudCBBdXRoZW50aWNhdGlvbiBhbmQg RW1haWwCEEFX2Bdu
SnoVoz7aRHpbGdEwgdYGCyqGSIb3DQEJEAILMYHGoIHDMIGuMQswCQYDVQQG EwJVUzELMAkGA1UE
CBMCVVQxFzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVUaGUg VVNFUlRSVVNUIE5l
dHdvcmsxITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTE2MDQG A1UEAxMtVVROLVVT
RVJGaXJzdC1DbGllbnQgQXV0aGVudGljYXRpb24gYW5kIEVtYWlsAhBBV9gX bkp6FaM+2kR6WxnR
MA0GCSqGSIb3DQEBAQUABIIBAIYnTK985mTNsHLCEbSYxW8aP8PD2l678hZ0 Be3OSU9xF90ey65s
/cI7gRk8kM4gszTo6hVPrfy7RFzgoruLKCe3mcH9faG+Y1WoW0KmcGEG9Vpi lzXdNhBHvmUOG102
cg6zicX0XWBLdzy3FFbOoM1H9jteOOmVdTcIHTo1cT3xzPq8ainnqjZquJ4l NgfiS0VXQDXourRg
+ZW5BaeGSgV9aATCpZYTyo+BLoRqiQbtlxAe2n4x0aorbk/mGTV82XMwskoD yXlpt0SC4EJCqOG3
hTG+Sp1SlBeLI8n3piDOkh1pf/dlOhkJZJ92Jk1df+TzgzVgQuB4qdnYRi9e hbkAAAAAAAA=

--Apple-Mail-38-1069422632--

Re: Unique ID"s across multiple databases

Server offset + increment works really well, is simple, and well
documented and reliable - not sure why you would want to re-invent
something that works so well :).

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Unique ID"s across multiple databases

--0015174c172233c86f049015386d
Content-Type: text/plain; charset=UTF-8

You may be right. I'm not arguing that offset + increment is working.

I'm just wondering if that's the optimal solution when you do not know how
many servers you will have in your array in the future. In my view, the
offset + increment thingy is good if you know in advance that you'll have a
limited number of servers. But if you have no idea that you will have 2, 20,
or 200 servers in your array in the future, you just can't pick an optimal
increment value. It just doesn't scale well enough to me.
If you go with BIGINT ID's, you may have a big enough interval to be
generous and pick a big increment value and allow 200 or even 2000 to make
sure you cover worst case scenarios. I'm just not sure if it's worth it to
use up 8 bytes for a primary key, when in general 4/5/6 is more than enough.

Any thoughts on this?

On Sun, Sep 12, 2010 at 9:32 PM, Max Schubert wrote:

> Server offset + increment works really well, is simple, and well
> documented and reliable - not sure why you would want to re-invent
> something that works so well :).
>
> --
> MySQL Replication Mailing List
> For list archives: http://lists.mysql.com/replication
> To unsubscribe:
> http://lists.mysql.com/replication?unsub=niel@dinagon.com
>
>

--0015174c172233c86f049015386d--

Re: Unique ID"s across multiple databases

--002215046ae318801c04902135b8
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Sun, Sep 12, 2010 at 9:45 PM, Kiss D=E1niel wrote:

> offset + increment thingy is good if you know in advance that you'll have=
a
> limited number of servers. But if you have no idea that you will have 2,
> 20,
> or 200 servers in your array in the future, you just can't pick an optima=
l
>

What benefit do you think you will reap from that many masters ? Don't
forget that every write still has to be done on every server, so you're not
actually distributing that load; while for reads you only need simple
slaves.


--=20
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--002215046ae318801c04902135b8--

Re: Unique ID"s across multiple databases

--0016e6d50b424a06d50490218f41
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

This is actually more for failover scenarios where databases are spread in
multiple locations with unreliable internet connections. But you want to
keep every single location working even when they are cut off from the othe=
r
databases. The primary purpose is not load distribution.

On Mon, Sep 13, 2010 at 12:03 PM, Johan De Meersman wro=
te:

>
>
> On Sun, Sep 12, 2010 at 9:45 PM, Kiss Dániel wrot=
e:
>
>> offset + increment thingy is good if you know in advance that you'll hav=
e
>> a
>> limited number of servers. But if you have no idea that you will have 2,
>> 20,
>> or 200 servers in your array in the future, you just can't pick an optim=
al
>>
>
> What benefit do you think you will reap from that many masters ? Don't
> forget that every write still has to be done on every server, so you're n=
ot
> actually distributing that load; while for reads you only need simple
> slaves.
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>

--0016e6d50b424a06d50490218f41--

Re: Unique ID"s across multiple databases

--005045013c64e316d00490225ec0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hmm, that's a very interesting scenario, indeed.

One bad connection will break the chain, though, so in effect you'll be
multiplying the disconnecting rate...

I think you'd be better of with a star topology, but MySQL unfortunately
only allows ring-types. This is gonna require some good thinking on your
part :-)

On Mon, Sep 13, 2010 at 12:28 PM, Kiss D=E1niel wrote:

> This is actually more for failover scenarios where databases are spread i=
n
> multiple locations with unreliable internet connections. But you want to
> keep every single location working even when they are cut off from the
> other
> databases. The primary purpose is not load distribution.
>
> On Mon, Sep 13, 2010 at 12:03 PM, Johan De Meersman > >wrote:
>
> >
> >
> > On Sun, Sep 12, 2010 at 9:45 PM, Kiss D=E1niel wrote=
:
> >
> >> offset + increment thingy is good if you know in advance that you'll
> have
> >> a
> >> limited number of servers. But if you have no idea that you will have =
2,
> >> 20,
> >> or 200 servers in your array in the future, you just can't pick an
> optimal
> >>
> >
> > What benefit do you think you will reap from that many masters ? Don't
> > forget that every write still has to be done on every server, so you're
> not
> > actually distributing that load; while for reads you only need simple
> > slaves.
> >
> >
> > --
> > Bier met grenadyn
> > Is als mosterd by den wyn
> > Sy die't drinkt, is eene kwezel
> > Hy die't drinkt, is ras een ezel
> >
>



--=20
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--005045013c64e316d00490225ec0--

Re: Unique ID"s across multiple databases

--000e0cd594e4048c9f0490226ede
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

I could be way off here, but how about letting your unique id be a
calculated column of the the server's MAC address concatenated with an
auto-increment id column?

I hope this helps...
~~Fish~~


On Mon, Sep 13, 2010 at 7:26 AM, Johan De Meersman wrot=
e:

> Hmm, that's a very interesting scenario, indeed.
>
> One bad connection will break the chain, though, so in effect you'll be
> multiplying the disconnecting rate...
>
> I think you'd be better of with a star topology, but MySQL unfortunately
> only allows ring-types. This is gonna require some good thinking on your
> part :-)
>
> On Mon, Sep 13, 2010 at 12:28 PM, Kiss D=E1niel wrote:
>
> > This is actually more for failover scenarios where databases are spread
> in
> > multiple locations with unreliable internet connections. But you want t=
o
> > keep every single location working even when they are cut off from the
> > other
> > databases. The primary purpose is not load distribution.
> >
> > On Mon, Sep 13, 2010 at 12:03 PM, Johan De Meersman > > >wrote:
> >
> > >
> > >
> > > On Sun, Sep 12, 2010 at 9:45 PM, Kiss D=E1niel wro=
te:
> > >
> > >> offset + increment thingy is good if you know in advance that you'll
> > have
> > >> a
> > >> limited number of servers. But if you have no idea that you will hav=
e
> 2,
> > >> 20,
> > >> or 200 servers in your array in the future, you just can't pick an
> > optimal
> > >>
> > >
> > > What benefit do you think you will reap from that many masters ? Don'=
t
> > > forget that every write still has to be done on every server, so you'=
re
> > not
> > > actually distributing that load; while for reads you only need simple
> > > slaves.
> > >
> > >
> > > --
> > > Bier met grenadyn
> > > Is als mosterd by den wyn
> > > Sy die't drinkt, is eene kwezel
> > > Hy die't drinkt, is ras een ezel
> > >
> >
>
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>

--000e0cd594e4048c9f0490226ede--

Re: Unique ID"s across multiple databases

--000e0cd48b425e6772049022e9b9
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

I had some coffee and realized that actually, using a UUID might be
something to look at. There have been quite a few discussions about using =
a
UUID as a unique id and it does have some gotchas. Just Google: mysql uuid
Have a great day....
~~Fish~~




On Mon, Sep 13, 2010 at 7:30 AM, Fish Kungfu wrote:

> I could be way off here, but how about letting your unique id be a
> calculated column of the the server's MAC address concatenated with an
> auto-increment id column?
>
> I hope this helps...
> ~~Fish~~
>
>
> On Mon, Sep 13, 2010 at 7:26 AM, Johan De Meersman wr=
ote:
>
>> Hmm, that's a very interesting scenario, indeed.
>>
>> One bad connection will break the chain, though, so in effect you'll be
>> multiplying the disconnecting rate...
>>
>> I think you'd be better of with a star topology, but MySQL unfortunately
>> only allows ring-types. This is gonna require some good thinking on your
>> part :-)
>>
>> On Mon, Sep 13, 2010 at 12:28 PM, Kiss D=E1niel wrote=
:
>>
>> > This is actually more for failover scenarios where databases are sprea=
d
>> in
>> > multiple locations with unreliable internet connections. But you want =
to
>> > keep every single location working even when they are cut off from the
>> > other
>> > databases. The primary purpose is not load distribution.
>> >
>> > On Mon, Sep 13, 2010 at 12:03 PM, Johan De Meersman e
>> > >wrote:
>> >
>> > >
>> > >
>> > > On Sun, Sep 12, 2010 at 9:45 PM, Kiss D=E1niel
>> wrote:
>> > >
>> > >> offset + increment thingy is good if you know in advance that you'l=
l
>> > have
>> > >> a
>> > >> limited number of servers. But if you have no idea that you will ha=
ve
>> 2,
>> > >> 20,
>> > >> or 200 servers in your array in the future, you just can't pick an
>> > optimal
>> > >>
>> > >
>> > > What benefit do you think you will reap from that many masters ? Don=
't
>> > > forget that every write still has to be done on every server, so
>> you're
>> > not
>> > > actually distributing that load; while for reads you only need simpl=
e
>> > > slaves.
>> > >
>> > >
>> > > --
>> > > Bier met grenadyn
>> > > Is als mosterd by den wyn
>> > > Sy die't drinkt, is eene kwezel
>> > > Hy die't drinkt, is ras een ezel
>> > >
>> >
>>
>>
>>
>> --
>> Bier met grenadyn
>> Is als mosterd by den wyn
>> Sy die't drinkt, is eene kwezel
>> Hy die't drinkt, is ras een ezel
>>
>
>

--000e0cd48b425e6772049022e9b9--

Re: Unique ID"s across multiple databases

--001636c5b456f3522b0490234637
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Hell, yeah. :)

Actually, the ID system I described below works quite well according to my
tests. I feel very comfortable with it both from primary key size and
dynamically increasable database number point of views.
What I actually don't like in it is the concatenated unique ID (ID + SID)
pairs. To use two fields for primary and foreign keys is not the most
convenient to say the least. :)
I am just wondering if anyone has any better idea to fulfill the
requirements (small index size, dynamically increasable numbe of databases
in the array, incremental-like ID's are optimal for the MySQL indexing
engine) and avoid this silly drawback. :)

On Mon, Sep 13, 2010 at 1:26 PM, Johan De Meersman wrot=
e:

> Hmm, that's a very interesting scenario, indeed.
>
> One bad connection will break the chain, though, so in effect you'll be
> multiplying the disconnecting rate...
>
> I think you'd be better of with a star topology, but MySQL unfortunately
> only allows ring-types. This is gonna require some good thinking on your
> part :-)
>
>
> On Mon, Sep 13, 2010 at 12:28 PM, Kiss Dániel wro=
te:
>
>> This is actually more for failover scenarios where databases are spread =
in
>> multiple locations with unreliable internet connections. But you want to
>> keep every single location working even when they are cut off from the
>> other
>> databases. The primary purpose is not load distribution.
>>
>> On Mon, Sep 13, 2010 at 12:03 PM, Johan De Meersman >> >wrote:
>>
>> >
>> >
>> > On Sun, Sep 12, 2010 at 9:45 PM, Kiss Dániel w=
rote:
>> >
>> >> offset + increment thingy is good if you know in advance that you'll
>> have
>> >> a
>> >> limited number of servers. But if you have no idea that you will have
>> 2,
>> >> 20,
>> >> or 200 servers in your array in the future, you just can't pick an
>> optimal
>> >>
>> >
>> > What benefit do you think you will reap from that many masters ? Don't
>> > forget that every write still has to be done on every server, so you'r=
e
>> not
>> > actually distributing that load; while for reads you only need simple
>> > slaves.
>> >
>> >
>> > --
>> > Bier met grenadyn
>> > Is als mosterd by den wyn
>> > Sy die't drinkt, is eene kwezel
>> > Hy die't drinkt, is ras een ezel
>> >
>>
>
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>

--001636c5b456f3522b0490234637--

Re: Unique ID"s across multiple databases

--0015174c103854bb71049023a767
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Well, thanks, but I'm afraid using UUID's (even with hex compression) is
kind of a suicide, when it comes to performance.
This is a good summary about the issues:
http://www.mysqlperformanceblog.com/2007/03/13/to-uuid-or-no t-to-uuid/

So, some kind of auto_increment or sequencing must be the optimal solution
here.

On Mon, Sep 13, 2010 at 2:05 PM, Fish Kungfu wrote:

> I had some coffee and realized that actually, using a UUID might be
> something to look at. There have been quite a few discussions about usin=
g
> a
> UUID as a unique id and it does have some gotchas. Just Google: mysql uu=
id
> Have a great day....
> ~~Fish~~
>
>
>
>
> On Mon, Sep 13, 2010 at 7:30 AM, Fish Kungfu
> wrote:
>
> > I could be way off here, but how about letting your unique id be a
> > calculated column of the the server's MAC address concatenated with an
> > auto-increment id column?
> >
> > I hope this helps...
> > ~~Fish~~
> >
> >
> > On Mon, Sep 13, 2010 at 7:26 AM, Johan De Meersman > >wrote:
> >
> >> Hmm, that's a very interesting scenario, indeed.
> >>
> >> One bad connection will break the chain, though, so in effect you'll b=
e
> >> multiplying the disconnecting rate...
> >>
> >> I think you'd be better of with a star topology, but MySQL unfortunate=
ly
> >> only allows ring-types. This is gonna require some good thinking on yo=
ur
> >> part :-)
> >>
> >> On Mon, Sep 13, 2010 at 12:28 PM, Kiss Dániel =
wrote:
> >>
> >> > This is actually more for failover scenarios where databases are
> spread
> >> in
> >> > multiple locations with unreliable internet connections. But you wan=
t
> to
> >> > keep every single location working even when they are cut off from t=
he
> >> > other
> >> > databases. The primary purpose is not load distribution.
> >> >
> >> > On Mon, Sep 13, 2010 at 12:03 PM, Johan De Meersman <
> vegivamp@tuxera.be
> >> > >wrote:
> >> >
> >> > >
> >> > >
> >> > > On Sun, Sep 12, 2010 at 9:45 PM, Kiss Dániel m>
> >> wrote:
> >> > >
> >> > >> offset + increment thingy is good if you know in advance that
> you'll
> >> > have
> >> > >> a
> >> > >> limited number of servers. But if you have no idea that you will
> have
> >> 2,
> >> > >> 20,
> >> > >> or 200 servers in your array in the future, you just can't pick a=
n
> >> > optimal
> >> > >>
> >> > >
> >> > > What benefit do you think you will reap from that many masters ?
> Don't
> >> > > forget that every write still has to be done on every server, so
> >> you're
> >> > not
> >> > > actually distributing that load; while for reads you only need
> simple
> >> > > slaves.
> >> > >
> >> > >
> >> > > --
> >> > > Bier met grenadyn
> >> > > Is als mosterd by den wyn
> >> > > Sy die't drinkt, is eene kwezel
> >> > > Hy die't drinkt, is ras een ezel
> >> > >
> >> >
> >>
> >>
> >>
> >> --
> >> Bier met grenadyn
> >> Is als mosterd by den wyn
> >> Sy die't drinkt, is eene kwezel
> >> Hy die't drinkt, is ras een ezel
> >>
> >
> >
>

--0015174c103854bb71049023a767--

RE: Unique ID"s across multiple databases

>-----Original Message-----
>From: Kiss Dániel [mailto:niel@dinagon.com]
>Sent: Sunday, September 12, 2010 1:47 PM
>To: mysql@lists.mysql.com; replication@lists.mysql.com
>Subject: Unique ID's across multiple databases
>
>Hi,
>
>I'm designing a master-to-master replication architecture.
>I wonder what the best way is to make sure both databases generate unique
>row ID's, so there won't be ID conflicts when replicating both directions.
>
>I read on forums about pro's and con's using UUID's, also about setting the
>*auto-increment-increment *and *auto-increment-offset* system variables.
>I'm not a fan of UUID's for this purpose. They are two big, slow to
>generate, and they don't 100% guarantee global uniqueness anyway.
>On the other hand, the *auto-increment-offset *trick limits the number of
>possible databases in the system in advance, which is not so nice.
>
[JS] UUIDs are likely enough to be unique that you don't have to worry about
it.

I can't speak to the speed.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com




>So, I'm thinking about a composite unique ID system, where the value of the
>*server-id *system variable is always a part of the ID. The server-id has to
>be set uniquely anyway for any replication architecture in MySQL.
>
>A sample would look like this:
>
>CREATE TABLE SampleParents (
>ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
>SID SMALLINT UNSIGNED NOT NULL,
>
>SampleData VARCHAR(50) NOT NULL,
>
>PRIMARY KEY (ID, SID)) ENGINE=InnoDB;
>
>CREATE TABLE SampleChildren (
>ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
>SID SMALLINT UNSIGNED NOT NULL,
>
>SampleParentID INT UNSIGNED NOT NULL,
>SampleParentSID SMALLINT UNSIGNED NOT NULL,
>
>SampleData VARCHAR(50) NOT NULL,
>
>PRIMARY KEY (ID, SID),
>KEY (SampleParentID, SampleParentSID),
>
>CONSTRAINT FOREIGN KEY (SampleParentID, SampleParentSID) REFERENCES
>SampleParents (ID, SID)) ENGINE=InnoDB;
>
>Where SID is always filled with the value of the @@server_id global variable
>according to the server where the record is created.
>
>How I see it is that it has the following advantages.
>
> - The ID+SID pair guarantees pripary key uniqueness within the
> replication array.
> - Auto_increment is a magnitude faster than generating UUID's.
> - SID adds only 2 bytes in this case to the size of the primary key item.
> It can be even 1 byte if I'm sure I'll never exceed maximum 255 servers.
> But
> anyhow, it is still way smaller than the 16 byte of a UUID field, even if
> using BIGINT's.
> - Keeps the efficiency of the primary key indexing, because the key is
> still very close to a strictly incremental value.
>
>Well, you can see what the obvious disadvantage is:
>
> - Primary and foreign keys are always double fields. This is not so
> convinient when you are joining tables and add the WHERE clauses to your
> queries. It might even negatively affect the evaluation speed of join
> conditions, although I have no idea yet, how much. (My gut feeling is that
> it's not a big issue, due to the good query optimizer of MySQL.)
>
>My question is. Does anyone have any better idea, how to approach this
>problem?
>
>Thanks,




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Unique ID"s across multiple databases

>-----Original Message-----
>From: vegivamp@gmail.com [mailto:vegivamp@gmail.com] On Behalf Of Johan De
>Meersman
>Sent: Monday, September 13, 2010 7:27 AM
>To: Kiss Dániel
>Cc: Max Schubert; mysql@lists.mysql.com; replication@lists.mysql.com
>Subject: Re: Unique ID's across multiple databases
>
>Hmm, that's a very interesting scenario, indeed.
>
>One bad connection will break the chain, though, so in effect you'll be
>multiplying the disconnecting rate...
>
>I think you'd be better of with a star topology, but MySQL unfortunately
>only allows ring-types. This is gonna require some good thinking on your
>part :-)
>
[JS] It sounds like you are trying to protect against a regional disaster.

This is precisely the type of scenario for which NAS or FibreChannel is used.
You let the storage medium take care of replication. Typically you'd only need
two units, perhaps on opposite sides of the country, using FibreChannel over
IP.

I've been out of this market (sales/support side) for many years, so I don't
know what the current technology costs, but if you can afford it that is the
way to go. It will make your life much simpler.


Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com



>On Mon, Sep 13, 2010 at 12:28 PM, Kiss Dániel wrote:
>
>> This is actually more for failover scenarios where databases are spread in
>> multiple locations with unreliable internet connections. But you want to
>> keep every single location working even when they are cut off from the
>> other
>> databases. The primary purpose is not load distribution.
>>
>> On Mon, Sep 13, 2010 at 12:03 PM, Johan De Meersman >> >wrote:
>>
>> >
>> >
>> > On Sun, Sep 12, 2010 at 9:45 PM, Kiss Dániel wrote:
>> >
>> >> offset + increment thingy is good if you know in advance that you'll
>> have
>> >> a
>> >> limited number of servers. But if you have no idea that you will have 2,
>> >> 20,
>> >> or 200 servers in your array in the future, you just can't pick an
>> optimal
>> >>
>> >
>> > What benefit do you think you will reap from that many masters ? Don't
>> > forget that every write still has to be done on every server, so you're
>> not
>> > actually distributing that load; while for reads you only need simple
>> > slaves.
>> >
>> >
>> > --
>> > Bier met grenadyn
>> > Is als mosterd by den wyn
>> > Sy die't drinkt, is eene kwezel
>> > Hy die't drinkt, is ras een ezel
>> >
>>
>
>
>
>--
>Bier met grenadyn
>Is als mosterd by den wyn
>Sy die't drinkt, is eene kwezel
>Hy die't drinkt, is ras een ezel




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Unique ID"s across multiple databases

--0016e6de1462d1fc010490260867
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Well, not exactly.

I do not own all the databases. Some of them are placed at customers, some
of them are at my data warehouse. So, neither NAS or Fibre Channel is a
solution in this case.

On Mon, Sep 13, 2010 at 4:30 PM, Jerry Schwartz wrote:

> >-----Original Message-----
> >From: vegivamp@gmail.com [mailto:vegivamp@gmail.com] On Behalf Of Johan
> De
> >Meersman
> >Sent: Monday, September 13, 2010 7:27 AM
> >To: Kiss Dániel
> >Cc: Max Schubert; mysql@lists.mysql.com; replication@lists.mysql.com
> >Subject: Re: Unique ID's across multiple databases
> >
> >Hmm, that's a very interesting scenario, indeed.
> >
> >One bad connection will break the chain, though, so in effect you'll be
> >multiplying the disconnecting rate...
> >
> >I think you'd be better of with a star topology, but MySQL unfortunately
> >only allows ring-types. This is gonna require some good thinking on your
> >part :-)
> >
> [JS] It sounds like you are trying to protect against a regional disaster=
..
>
> This is precisely the type of scenario for which NAS or FibreChannel is
> used.
> You let the storage medium take care of replication. Typically you'd only
> need
> two units, perhaps on opposite sides of the country, using FibreChannel
> over
> IP.
>
> I've been out of this market (sales/support side) for many years, so I
> don't
> know what the current technology costs, but if you can afford it that is
> the
> way to go. It will make your life much simpler.
>
>
> Regards,
>
> Jerry Schwartz
> Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
> E-mail: jerry@gii.co.jp
> Web site: www.the-infoshop.com
>
>
>
> >On Mon, Sep 13, 2010 at 12:28 PM, Kiss Dániel wr=
ote:
> >
> >> This is actually more for failover scenarios where databases are sprea=
d
> in
> >> multiple locations with unreliable internet connections. But you want =
to
> >> keep every single location working even when they are cut off from the
> >> other
> >> databases. The primary purpose is not load distribution.
> >>
> >> On Mon, Sep 13, 2010 at 12:03 PM, Johan De Meersman e
> >> >wrote:
> >>
> >> >
> >> >
> >> > On Sun, Sep 12, 2010 at 9:45 PM, Kiss Dániel
> wrote:
> >> >
> >> >> offset + increment thingy is good if you know in advance that you'l=
l
> >> have
> >> >> a
> >> >> limited number of servers. But if you have no idea that you will ha=
ve
> 2,
> >> >> 20,
> >> >> or 200 servers in your array in the future, you just can't pick an
> >> optimal
> >> >>
> >> >
> >> > What benefit do you think you will reap from that many masters ? Don=
't
> >> > forget that every write still has to be done on every server, so
> you're
> >> not
> >> > actually distributing that load; while for reads you only need simpl=
e
> >> > slaves.
> >> >
> >> >
> >> > --
> >> > Bier met grenadyn
> >> > Is als mosterd by den wyn
> >> > Sy die't drinkt, is eene kwezel
> >> > Hy die't drinkt, is ras een ezel
> >> >
> >>
> >
> >
> >
> >--
> >Bier met grenadyn
> >Is als mosterd by den wyn
> >Sy die't drinkt, is eene kwezel
> >Hy die't drinkt, is ras een ezel
>
>
>
>

--0016e6de1462d1fc010490260867--

RE: Unique ID"s across multiple databases

>-----Original Message-----
>From: Kiss Dániel [mailto:niel@dinagon.com]
>Sent: Monday, September 13, 2010 11:49 AM
>To: Jerry Schwartz
>Cc: Johan De Meersman; Max Schubert; mysql@lists.mysql.com;
>replication@lists.mysql.com
>Subject: Re: Unique ID's across multiple databases
>
>Well, not exactly.
>
>I do not own all the databases. Some of them are placed at customers, some
>of them are at my data warehouse. So, neither NAS or Fibre Channel is a
>solution in this case.
>
[JS] Then you have a mess on your hands.

Are you going to be mirroring these databases separately for each customer?

I wish you well.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com




>On Mon, Sep 13, 2010 at 4:30 PM, Jerry Schwartz wrote:
>
>> >-----Original Message-----
>> >From: vegivamp@gmail.com [mailto:vegivamp@gmail.com] On Behalf Of Johan
>> De
>> >Meersman
>> >Sent: Monday, September 13, 2010 7:27 AM
>> >To: Kiss Dániel
>> >Cc: Max Schubert; mysql@lists.mysql.com; replication@lists.mysql.com
>> >Subject: Re: Unique ID's across multiple databases
>> >
>> >Hmm, that's a very interesting scenario, indeed.
>> >
>> >One bad connection will break the chain, though, so in effect you'll be
>> >multiplying the disconnecting rate...
>> >
>> >I think you'd be better of with a star topology, but MySQL unfortunately
>> >only allows ring-types. This is gonna require some good thinking on your
>> >part :-)
>> >
>> [JS] It sounds like you are trying to protect against a regional disaster.
>>
>> This is precisely the type of scenario for which NAS or FibreChannel is
>> used.
>> You let the storage medium take care of replication. Typically you'd only
>> need
>> two units, perhaps on opposite sides of the country, using FibreChannel
>> over
>> IP.
>>
>> I've been out of this market (sales/support side) for many years, so I
>> don't
>> know what the current technology costs, but if you can afford it that is
>> the
>> way to go. It will make your life much simpler.
>>
>>
>> Regards,
>>
>> Jerry Schwartz
>> Global Information Incorporated
>> 195 Farmington Ave.
>> Farmington, CT 06032
>>
>> 860.674.8796 / FAX: 860.674.8341
>> E-mail: jerry@gii.co.jp
>> Web site: www.the-infoshop.com
>>
>>
>>
>> >On Mon, Sep 13, 2010 at 12:28 PM, Kiss Dániel wrote:
>> >
>> >> This is actually more for failover scenarios where databases are spread
>> in
>> >> multiple locations with unreliable internet connections. But you want to
>> >> keep every single location working even when they are cut off from the
>> >> other
>> >> databases. The primary purpose is not load distribution.
>> >>
>> >> On Mon, Sep 13, 2010 at 12:03 PM, Johan De Meersman >> >> >wrote:
>> >>
>> >> >
>> >> >
>> >> > On Sun, Sep 12, 2010 at 9:45 PM, Kiss Dániel
>> wrote:
>> >> >
>> >> >> offset + increment thingy is good if you know in advance that you'll
>> >> have
>> >> >> a
>> >> >> limited number of servers. But if you have no idea that you will have
>> 2,
>> >> >> 20,
>> >> >> or 200 servers in your array in the future, you just can't pick an
>> >> optimal
>> >> >>
>> >> >
>> >> > What benefit do you think you will reap from that many masters ? Don't
>> >> > forget that every write still has to be done on every server, so
>> you're
>> >> not
>> >> > actually distributing that load; while for reads you only need simple
>> >> > slaves.
>> >> >
>> >> >
>> >> > --
>> >> > Bier met grenadyn
>> >> > Is als mosterd by den wyn
>> >> > Sy die't drinkt, is eene kwezel
>> >> > Hy die't drinkt, is ras een ezel
>> >> >
>> >>
>> >
>> >
>> >
>> >--
>> >Bier met grenadyn
>> >Is als mosterd by den wyn
>> >Sy die't drinkt, is eene kwezel
>> >Hy die't drinkt, is ras een ezel
>>
>>
>>
>>




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Unique ID"s across multiple databases

--001485f92218141a06049028b187
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

This sounds like a good job for a 'NoSQL' system. Maybe?

JW


On Mon, Sep 13, 2010 at 1:46 PM, Jerry Schwartz wrote:

> >-----Original Message-----
> >From: Kiss D=E1niel [mailto:niel@dinagon.com]
> >Sent: Monday, September 13, 2010 11:49 AM
> >To: Jerry Schwartz
> >Cc: Johan De Meersman; Max Schubert; mysql@lists.mysql.com;
> >replication@lists.mysql.com
> >Subject: Re: Unique ID's across multiple databases
> >
> >Well, not exactly.
> >
> >I do not own all the databases. Some of them are placed at customers, so=
me
> >of them are at my data warehouse. So, neither NAS or Fibre Channel is a
> >solution in this case.
> >
> [JS] Then you have a mess on your hands.
>
> Are you going to be mirroring these databases separately for each custome=
r?
>
> I wish you well.
>
> Regards,
>
> Jerry Schwartz
> Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
> E-mail: jerry@gii.co.jp
> Web site: www.the-infoshop.com
>
>
>
>
> >On Mon, Sep 13, 2010 at 4:30 PM, Jerry Schwartz wrote:
> >
> >> >-----Original Message-----
> >> >From: vegivamp@gmail.com [mailto:vegivamp@gmail.com] On Behalf Of
> Johan
> >> De
> >> >Meersman
> >> >Sent: Monday, September 13, 2010 7:27 AM
> >> >To: Kiss D=E1niel
> >> >Cc: Max Schubert; mysql@lists.mysql.com; replication@lists.mysql.com
> >> >Subject: Re: Unique ID's across multiple databases
> >> >
> >> >Hmm, that's a very interesting scenario, indeed.
> >> >
> >> >One bad connection will break the chain, though, so in effect you'll =
be
> >> >multiplying the disconnecting rate...
> >> >
> >> >I think you'd be better of with a star topology, but MySQL
> unfortunately
> >> >only allows ring-types. This is gonna require some good thinking on
> your
> >> >part :-)
> >> >
> >> [JS] It sounds like you are trying to protect against a regional
> disaster.
> >>
> >> This is precisely the type of scenario for which NAS or FibreChannel i=
s
> >> used.
> >> You let the storage medium take care of replication. Typically you'd
> only
> >> need
> >> two units, perhaps on opposite sides of the country, using FibreChanne=
l
> >> over
> >> IP.
> >>
> >> I've been out of this market (sales/support side) for many years, so I
> >> don't
> >> know what the current technology costs, but if you can afford it that =
is
> >> the
> >> way to go. It will make your life much simpler.
> >>
> >>
> >> Regards,
> >>
> >> Jerry Schwartz
> >> Global Information Incorporated
> >> 195 Farmington Ave.
> >> Farmington, CT 06032
> >>
> >> 860.674.8796 / FAX: 860.674.8341
> >> E-mail: jerry@gii.co.jp
> >> Web site: www.the-infoshop.com
> >>
> >>
> >>
> >> >On Mon, Sep 13, 2010 at 12:28 PM, Kiss D=E1niel
> wrote:
> >> >
> >> >> This is actually more for failover scenarios where databases are
> spread
> >> in
> >> >> multiple locations with unreliable internet connections. But you wa=
nt
> to
> >> >> keep every single location working even when they are cut off from
> the
> >> >> other
> >> >> databases. The primary purpose is not load distribution.
> >> >>
> >> >> On Mon, Sep 13, 2010 at 12:03 PM, Johan De Meersman <
> vegivamp@tuxera.be
> >> >> >wrote:
> >> >>
> >> >> >
> >> >> >
> >> >> > On Sun, Sep 12, 2010 at 9:45 PM, Kiss D=E1niel
> >> wrote:
> >> >> >
> >> >> >> offset + increment thingy is good if you know in advance that
> you'll
> >> >> have
> >> >> >> a
> >> >> >> limited number of servers. But if you have no idea that you will
> have
> >> 2,
> >> >> >> 20,
> >> >> >> or 200 servers in your array in the future, you just can't pick =
an
> >> >> optimal
> >> >> >>
> >> >> >
> >> >> > What benefit do you think you will reap from that many masters ?
> Don't
> >> >> > forget that every write still has to be done on every server, so
> >> you're
> >> >> not
> >> >> > actually distributing that load; while for reads you only need
> simple
> >> >> > slaves.
> >> >> >
> >> >> >
> >> >> > --
> >> >> > Bier met grenadyn
> >> >> > Is als mosterd by den wyn
> >> >> > Sy die't drinkt, is eene kwezel
> >> >> > Hy die't drinkt, is ras een ezel
> >> >> >
> >> >>
> >> >
> >> >
> >> >
> >> >--
> >> >Bier met grenadyn
> >> >Is als mosterd by den wyn
> >> >Sy die't drinkt, is eene kwezel
> >> >Hy die't drinkt, is ras een ezel
> >>
> >>
> >>
> >>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Djohnny@pixelated.=
net
>
>


--=20
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--001485f92218141a06049028b187--

Re: Unique ID"s across multiple databases

--0015174c3f80388a63049028f081
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Well, that would be the plan, yes. :-)
Anyway, I'll get over the problem sooner or later. :-)

On Mon, Sep 13, 2010 at 8:46 PM, Jerry Schwartz wrote:

> >-----Original Message-----
> >From: Kiss Dániel [mailto:niel@dinagon.com]
> >Sent: Monday, September 13, 2010 11:49 AM
> >To: Jerry Schwartz
> >Cc: Johan De Meersman; Max Schubert; mysql@lists.mysql.com;
> >replication@lists.mysql.com
> >Subject: Re: Unique ID's across multiple databases
> >
> >Well, not exactly.
> >
> >I do not own all the databases. Some of them are placed at customers, so=
me
> >of them are at my data warehouse. So, neither NAS or Fibre Channel is a
> >solution in this case.
> >
> [JS] Then you have a mess on your hands.
>
> Are you going to be mirroring these databases separately for each custome=
r?
>
> I wish you well.
>
> Regards,
>
> Jerry Schwartz
> Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
> E-mail: jerry@gii.co.jp
> Web site: www.the-infoshop.com
>
>
>
>
> >On Mon, Sep 13, 2010 at 4:30 PM, Jerry Schwartz wrote:
> >
> >> >-----Original Message-----
> >> >From: vegivamp@gmail.com [mailto:vegivamp@gmail.com] On Behalf Of
> Johan
> >> De
> >> >Meersman
> >> >Sent: Monday, September 13, 2010 7:27 AM
> >> >To: Kiss Dániel
> >> >Cc: Max Schubert; mysql@lists.mysql.com; replication@lists.mysql.com
> >> >Subject: Re: Unique ID's across multiple databases
> >> >
> >> >Hmm, that's a very interesting scenario, indeed.
> >> >
> >> >One bad connection will break the chain, though, so in effect you'll =
be
> >> >multiplying the disconnecting rate...
> >> >
> >> >I think you'd be better of with a star topology, but MySQL
> unfortunately
> >> >only allows ring-types. This is gonna require some good thinking on
> your
> >> >part :-)
> >> >
> >> [JS] It sounds like you are trying to protect against a regional
> disaster.
> >>
> >> This is precisely the type of scenario for which NAS or FibreChannel i=
s
> >> used.
> >> You let the storage medium take care of replication. Typically you'd
> only
> >> need
> >> two units, perhaps on opposite sides of the country, using FibreChanne=
l
> >> over
> >> IP.
> >>
> >> I've been out of this market (sales/support side) for many years, so I
> >> don't
> >> know what the current technology costs, but if you can afford it that =
is
> >> the
> >> way to go. It will make your life much simpler.
> >>
> >>
> >> Regards,
> >>
> >> Jerry Schwartz
> >> Global Information Incorporated
> >> 195 Farmington Ave.
> >> Farmington, CT 06032
> >>
> >> 860.674.8796 / FAX: 860.674.8341
> >> E-mail: jerry@gii.co.jp
> >> Web site: www.the-infoshop.com
> >>
> >>
> >>
> >> >On Mon, Sep 13, 2010 at 12:28 PM, Kiss Dániel
> wrote:
> >> >
> >> >> This is actually more for failover scenarios where databases are
> spread
> >> in
> >> >> multiple locations with unreliable internet connections. But you wa=
nt
> to
> >> >> keep every single location working even when they are cut off from
> the
> >> >> other
> >> >> databases. The primary purpose is not load distribution.
> >> >>
> >> >> On Mon, Sep 13, 2010 at 12:03 PM, Johan De Meersman <
> vegivamp@tuxera.be
> >> >> >wrote:
> >> >>
> >> >> >
> >> >> >
> >> >> > On Sun, Sep 12, 2010 at 9:45 PM, Kiss Dániel om>
> >> wrote:
> >> >> >
> >> >> >> offset + increment thingy is good if you know in advance that
> you'll
> >> >> have
> >> >> >> a
> >> >> >> limited number of servers. But if you have no idea that you will
> have
> >> 2,
> >> >> >> 20,
> >> >> >> or 200 servers in your array in the future, you just can't pick =
an
> >> >> optimal
> >> >> >>
> >> >> >
> >> >> > What benefit do you think you will reap from that many masters ?
> Don't
> >> >> > forget that every write still has to be done on every server, so
> >> you're
> >> >> not
> >> >> > actually distributing that load; while for reads you only need
> simple
> >> >> > slaves.
> >> >> >
> >> >> >
> >> >> > --
> >> >> > Bier met grenadyn
> >> >> > Is als mosterd by den wyn
> >> >> > Sy die't drinkt, is eene kwezel
> >> >> > Hy die't drinkt, is ras een ezel
> >> >> >
> >> >>
> >> >
> >> >
> >> >
> >> >--
> >> >Bier met grenadyn
> >> >Is als mosterd by den wyn
> >> >Sy die't drinkt, is eene kwezel
> >> >Hy die't drinkt, is ras een ezel
> >>
> >>
> >>
> >>
>
>
>
>

--0015174c3f80388a63049028f081--

RE: Unique ID"s across multiple databases

From: Kiss Dániel [mailto:niel@dinagon.com]
Sent: Monday, September 13, 2010 3:17 PM
To: Jerry Schwartz
Cc: Johan De Meersman; Max Schubert; mysql@lists.mysql.com;
replication@lists.mysql.com
Subject: Re: Unique ID's across multiple databases

Well, that would be the plan, yes. :-)
Anyway, I'll get over the problem sooner or later. :-)

[JS] I guess I don't grasp your topology at all. If you are going to be
replicating each database separately, who cares whether or not the IDs are
unique across databases? Were you thinking of commingling the data from all of
these individual databases into one big database? That doesn't make sense to
me from a fail-over standpoint.

Depending upon the industry and their level of paranoia (by inclination or by
regulation), you might have to have a separate remote SYSTEM (not database)
per customer.


Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com




On Mon, Sep 13, 2010 at 8:46 PM, Jerry Schwartz wrote:
>-----Original Message-----
>From: Kiss Dániel [mailto:niel@dinagon.com]
>Sent: Monday, September 13, 2010 11:49 AM
>To: Jerry Schwartz
>Cc: Johan De Meersman; Max Schubert; mysql@lists.mysql.com;
>replication@lists.mysql.com
>Subject: Re: Unique ID's across multiple databases
>
>Well, not exactly.
>
>I do not own all the databases. Some of them are placed at customers, some
>of them are at my data warehouse. So, neither NAS or Fibre Channel is a
>solution in this case.
>
[JS] Then you have a mess on your hands.

Are you going to be mirroring these databases separately for each customer?

I wish you well.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com




>On Mon, Sep 13, 2010 at 4:30 PM, Jerry Schwartz wrote:
>
>> >-----Original Message-----
>> >From: vegivamp@gmail.com [mailto:vegivamp@gmail.com] On Behalf Of Johan
>> De
>> >Meersman
>> >Sent: Monday, September 13, 2010 7:27 AM
>> >To: Kiss Dániel
>> >Cc: Max Schubert; mysql@lists.mysql.com; replication@lists.mysql.com
>> >Subject: Re: Unique ID's across multiple databases
>> >
>> >Hmm, that's a very interesting scenario, indeed.
>> >
>> >One bad connection will break the chain, though, so in effect you'll be
>> >multiplying the disconnecting rate...
>> >
>> >I think you'd be better of with a star topology, but MySQL unfortunately
>> >only allows ring-types. This is gonna require some good thinking on your
>> >part :-)
>> >
>> [JS] It sounds like you are trying to protect against a regional disaster.
>>
>> This is precisely the type of scenario for which NAS or FibreChannel is
>> used.
>> You let the storage medium take care of replication. Typically you'd only
>> need
>> two units, perhaps on opposite sides of the country, using FibreChannel
>> over
>> IP.
>>
>> I've been out of this market (sales/support side) for many years, so I
>> don't
>> know what the current technology costs, but if you can afford it that is
>> the
>> way to go. It will make your life much simpler.
>>
>>
>> Regards,
>>
>> Jerry Schwartz
>> Global Information Incorporated
>> 195 Farmington Ave.
>> Farmington, CT 06032
>>
>> 860.674.8796 / FAX: 860.674.8341
>> E-mail: jerry@gii.co.jp
>> Web site: www.the-infoshop.com
>>
>>
>>
>> >On Mon, Sep 13, 2010 at 12:28 PM, Kiss Dániel wrote:
>> >
>> >> This is actually more for failover scenarios where databases are spread
>> in
>> >> multiple locations with unreliable internet connections. But you want to
>> >> keep every single location working even when they are cut off from the
>> >> other
>> >> databases. The primary purpose is not load distribution.
>> >>
>> >> On Mon, Sep 13, 2010 at 12:03 PM, Johan De Meersman >> >> >wrote:
>> >>
>> >> >
>> >> >
>> >> > On Sun, Sep 12, 2010 at 9:45 PM, Kiss Dániel
>> wrote:
>> >> >
>> >> >> offset + increment thingy is good if you know in advance that you'll
>> >> have
>> >> >> a
>> >> >> limited number of servers. But if you have no idea that you will have
>> 2,
>> >> >> 20,
>> >> >> or 200 servers in your array in the future, you just can't pick an
>> >> optimal
>> >> >>
>> >> >
>> >> > What benefit do you think you will reap from that many masters ? Don't
>> >> > forget that every write still has to be done on every server, so
>> you're
>> >> not
>> >> > actually distributing that load; while for reads you only need simple
>> >> > slaves.
>> >> >
>> >> >
>> >> > --
>> >> > Bier met grenadyn
>> >> > Is als mosterd by den wyn
>> >> > Sy die't drinkt, is eene kwezel
>> >> > Hy die't drinkt, is ras een ezel
>> >> >
>> >>
>> >
>> >
>> >
>> >--
>> >Bier met grenadyn
>> >Is als mosterd by den wyn
>> >Sy die't drinkt, is eene kwezel
>> >Hy die't drinkt, is ras een ezel
>>
>>
>>
>>







--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Unique ID"s across multiple databases

> -----Original Message-----
> From: Kiss D=E1niel [mailto:niel@dinagon.com]=20
> Sent: Monday, September 13, 2010 5:59 AM
>
> Well, thanks, but I'm afraid using UUID's (even with hex=20
> compression) is
> kind of a suicide, when it comes to performance.
> This is a good summary about the issues:
> http://www.mysqlperformanceblog.com/2007/03/13/to-uuid-or-no t-to-uuid/

Is this UUID issue unique to mySQL or are there other RDBMS's that =
handle
it better (Postgress, Oracle, SQL Server, etc?)

I too have a need for a unique identifier that will "mesh" with other
databases periodically. So that a user in one "local" DB/server will get
migrated to a master DB which in turn will sync up with remote sites so
that all sites will have all users in it each night (for example).

Having a mapping of UUID to local ID seems one way, but I feel there is =
a
lot of room for collisions and integrity issues that way no?

There are some solutions at the bottom of that blog post. Are those not
good then? They seem interesting to me.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Unique ID"s across multiple databases

------=_20100913154849_88025
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 8bit



On Mon, September 13, 2010 15:37, Daevid Vincent wrote:
>>
-----Original Message-----
>>
From: Kiss D�niel
[mailto:niel@dinagon.com]
>> Sent: Monday, September 13, 2010
5:59 AM
>>
>> Well, thanks, but I'm afraid using
UUID's (even with hex
>> compression) is
>> kind of
a suicide, when it comes to performance.
>> This is a good
summary about the issues:
>>
http://www.mysqlperformanceblog.com/2007/03/13/to-uuid-or-no t-to-uuid/
>
> Is this UUID issue unique to mySQL or are there other
RDBMS's that handle
> it better (Postgress, Oracle, SQL Server,
etc?)
>
> I too have a need for a unique identifier that
will "mesh" with other
> databases periodically. So that
a user in one "local" DB/server will get
> migrated to a
master DB which in turn will sync up with remote sites so
> that
all sites will have all users in it each night (for example).
>

> Having a mapping of UUID to local ID seems one way, but I feel
there is a
> lot of room for collisions and integrity issues that
way no?
>
> There are some solutions at the bottom of
that blog post. Are those not
> good then? They seem interesting
to me.
Why does it have to be one field.  Two fields: ServerID
and the SequenceID
Across servers the pair would be unique and within
a given server the Sequence ID is the equivalent of a "manual
auto-increment fields"   Set it via Max(SequenceID)+1 where
ServerID is the local serverID.   Have an index set for the
combined fields as well as the Sequence ID field perhaps. 

SOURCE IP FROM HEADER:
************************************************
*Please block this
account's access to the *
*internet until its cleaned up. We are
basing *
*this on an analysis of the header NOT the FROM*
*address. *
************************************************
------
William R. Mussatto
Systems Engineer
http://www.csz.com
909-920-9154

------=_20100913154849_88025--

Re: Unique ID"s across multiple databases

--000325576116eb7103049031e31f
Content-Type: text/plain; charset=ISO-8859-1

On Mon, Sep 13, 2010 at 8:59 PM, Johnny Withers wrote:

>
> This sounds like a good job for a 'NoSQL' system. Maybe?
>

I can't help but blink at that. How exactly is NoSQL going to fix issues
that are related to topology, not inherent SQL limitations ? Which
particular incarnation of NoSQL are you thinking of ?

Also, I suspect rewriting all 200 clients' services is not going to be an
option :-)

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--000325576116eb7103049031e31f--

Re: Unique ID"s across multiple databases

--00163649a1a3970401049037d1d9
Content-Type: text/plain; charset=ISO-8859-1

I may have missed what you are trying to do here. NoSQL is really a bad name
and should really be renamed to NoREL instead. NoSQL implementations are not
used just because of limitations of traditional RDBMS when it comes to sheer
traffic volume, they are also used because they scale horizontally very
well. When I was reading though all these emails it seems to be you would
have 200+ nodes here with the SAME data. Maybe that's not what you are
trying to do?

I also did not know the software for maintaining the data was already in
place.


JW

On Tue, Sep 14, 2010 at 12:57 AM, Johan De Meersman wrote:

>
>
> On Mon, Sep 13, 2010 at 8:59 PM, Johnny Withers wrote:
>
>>
>> This sounds like a good job for a 'NoSQL' system. Maybe?
>>
>
> I can't help but blink at that. How exactly is NoSQL going to fix issues
> that are related to topology, not inherent SQL limitations ? Which
> particular incarnation of NoSQL are you thinking of ?
>
> Also, I suspect rewriting all 200 clients' services is not going to be an
> option :-)
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>



--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--00163649a1a3970401049037d1d9--