On Tue, Sep 23, 2008 at 01:05:15AM -0400, Jim Starkey wrote: > Giuseppe Maxia wrote: >> On Tue, Sep 23, 2008 at 6:20 AM, Baron Schwartz <[EMAIL PROTECTED] >> <mailto:[EMAIL PROTECTED]>> wrote: >> >> On Mon, Sep 22, 2008 at 8:19 AM, Jim Starkey >> <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>> wrote: >> > Stewart Smith wrote: >> >> >> >> On Mon, Sep 22, 2008 at 02:36:00AM -0400, Jim Starkey wrote: >> >> >> >>> >> >>> Auto increment is feasible only if the database engine runs on >> a single >> >>> computer. Any possible distributed database requires a full >> round trip >> >>> between servers to implement auto-increment, making their >> performance >> >>> untenable in future database systems. This isn't problem in >> MySQL or >> >>> Drizzle, but will be in future database systems, big time. >> Sequences >> >>> don't necessarily have this problem since they can be >> preallocated in >> >>> blocks, but auto-increment semantics make this impossible. >> >>> >> >> >> >> We pre-allocate auto increment in blocks today in ndb (per >> thread and >> >> per sql node) and nobody really complains (they go 'huh' and >> are then >> >> okay with it). >> >> >> >> >> > >> > That's not an unreasonable approach. Nimbus does a similar >> thing with >> > sequences. But it ain't auto-increment; anyone who depends on >> > auto-increment and time to be co-dependent monotonically increasing >> > dependent behavior (i.e. works) is going to be very disappointed >> when his or >> > her basic, intuitive assumptions fail. And if he or she happens >> to be a >> > bean counter, Sorbanes Oxley hell is going to break out as it >> will appear >> > that the books have been cooked. >> > >> > I suggest the following to be a sound rule for guidance: If >> something can't >> > be implemented (or implemented efficiently on future platforms), >> don't do >> > it. >> > >> > Timestamps and auto-increment must sort identically or bad >> things will >> > happen. We can't live without timestamps, so auto-increment has >> to go. >> > Sorry. >> >> I'm cool with preallocated batching, etc. >> >> I'm not cool with UUID if it's the ONLY choice I have. I want to be >> able to pick auto-inc (even if it has wacky behavior) for some jobs. >> I do not necessarily need for there to be gapless auto-inc, and I >> don't need them to be meaningful; they are surrogate keys, they're a >> meaningless autogenerated value that I choose (against my deepest core >> of my being, which doesn't like surrogate keys) because the appeal of >> small/monotonically increasing is too good to pass up. If you take >> that away and give me only UUID, I get a huge globally unique >> identifier that's nonsequential as my only choice of surrogate key -- >> it has neither of the properties I desire. Surely there's a better >> way to do this. I mean, maybe there isn't, but surely there is? >> >> >> ++ >> auto_increment is evil, but having only UUID as a replacement is even >> more evil. >> UUID may also affect replication. Every conflict can be solved, but in >> some cases, using UUID for surrogate keys looks like shooting sparrows >> with a cannon. > > It isn't the alternative to UUID. A formal sequence object is more > general and needn't implement the overly restrictive auto-increment > semantics. It does, however, fit into the DDL differently.
One data type I've used in the past for unique, ordered ids in distributed systems is a 64bit event ID: * 32 bit epoch time stamp - this make the system good until 2106. * 16 bit node unique stamp - this allows for 65536 events per second per node. This value will be taken from the node micro second / 16. There will also be a check to ensure this was not the last event ID given, and if so, will increment the value (taking into account 16 bit rollover). * 16 bit node number - This will allow for a total of 65536 nodes (in drizzle/mysql, server_id probably?). This is really simple, but works nice since you have unique ordering, timestamp can be pulled out for other uses, and ties are broken by ms/server id (assuming time is mostly in sync). This doesn't guarantee reality due to time skew, but it at least gives you something that's pretty close and deterministic on all nodes. You can of course give more bits to the node-unique number and less to the node if you need more events/second, or just make it 96 or 128 bits. While this could be done by an application, having the server generate it is nice so the app doesn't need to track node-unique numbers. I'm not necessarily suggesting this as a new type, I just wanted to throw the thought out for discussion. -Eric _______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
