L2J ID Factory vs. UUID

[benurb]

I checked for <L2j FORK>, what impact/advantages/disadvantages it would have to move from L2J's id factory to UUID for id creation. Maybe it is also interesting for l2j devs or for people here on the forum to use it on other projects, so i share it

MySQL UUIDs vs. Java UUIDs vs. L2J ID Factory

ID Factory - (datatype int(11) -> 4 bytes required):
INSERT INTO (250.000 rows): 29,672 seconds
SELECT FROM (50.000 rows): 5,556 seconds
UPDATE (35.715 rows): 4,353 seconds

Java UUID - (datatype varchar(36) -> 37 bytes required):
INSERT INTO (250.000 rows): 28,606 seconds
SELECT FROM (50.000 rows): 5,586 seconds
UPDATE (35.715 rows): 4,238 seconds

Java UUID - (datatype binary(16) -> 16 bytes required):
INSERT INTO (250.000 rows): 33,760 seconds
SELECT FROM (50.000 rows): 6,214 seconds
UPDATE (35.715 rows): 4,128 seconds

MySQL UUID - (datatype varchar(36) -> 37 bytes required):
Note: This should only be used for data that is just inserted into the db and the calling function doesn't need to know the new assigned id.
INSERT INTO (250.000 rows): 23,844 seconds

MySQL UUID - (datatype binary(16) -> 16 bytes required):
Note: This should only be used for data that is just inserted into the db and the calling function doesn't need to know the new assigned id.
INSERT INTO (250.000 rows): 32,194 seconds

Startup Time:
ID Factory (0 ids used): 0,029 seconds
ID Factory (500.000 ids used): 4,409 seconds
ID Factory (1.000.000 ids used): 8,735 seconds
ID Factory (1.500.000 ids used): 14,210 seconds
ID Factory (2.000.000 ids used): 17,875 seconds
UUID: 0 seconds

Memory usage for 2.000.000 used IDs (whole test environment):
ID Factory: 6 MB
UUID: 4 MB

Database space usage (250.000 IDs (and some test data) - dumped to sql file):
ID Factory: 14 MB
UUID: 18,3 MB

Time needed to generate 1.000.000 new ids (with 1.000.000 already used):
ID Factory: 0,057 seconds
UUID: 0,703 seconds

Max. ID amount:
ID Factory (unsigned): 4.294.967.296
UUID: 340.282.366.920.938.463.463.374.607.431.768.211.456

Memory footprint (500.000 ids):
ID Factory: about 14 mb
UUID (java.util.UUID): about 36 mb
UUID (String): about 71 mb

How do IDs look like:
ID Factory: 269754631
UUID (37 byte): 067e6162-3b6f-4ae2-a171-2470b63dff00
UUID (16 byte): 0xD4D4B0142E874F10BEBA505E59716D59 (only readable in hex - Navicat Screenshot)

Advantages UUID:
- We don't have to mess around with id factory. New ids are always unique => Duplicate ID issue not possible
- Server start time isn't affected by amount of used ids, because there is no need to query the database for used ids on server startup
- Cross insertion possible (for example a web application can insert an item on server runtime)
- Slightly better performance and decreased memory usage (on a clean server)

Disadvantages UUID:
- About 2 times higher memory footprint
- Needs at least 16 bytes per id (instead of 4 bytes for id factory) -> at least 114 MB more spaces needed for 10 million database entries (maximum +314 MB (for varchar(36)))
- A bit slower than ID Factory when it comes to compressing the uuid to 16 bytes
- Higher generation time for ids

[JIV]

so result is : we don't have to change anything, its perfect

[benurb]

well, i think it depends on application, but in general one should always prefer an algorithm with static and more important PREDICTABLE duration. idfactory is far away from both.

another thing: would you please not edit out parts of my "credits" (yes i see it as credits). creating this results took maybe longer than 1/4 of the code contributions here on the forum. next time, when you want to edit out smth (that is compliant with l2j forums rules of conduct afaik) of my thread, better delete my whole post. honestly, i don't care if my post is here and is discussed or if it is deleted. but i do care when parts of my work are changed, without something like "edited by xyz" AND when the same people (speaking of l2j devs in general) complain about not getting contributions from some "non-friendly"-forks. If this would be a code contribution would you also remove the fork name, just to say afterwards, that some forks don't contribute anything to l2j?
if you don't want to have fork names here, then edit them out from ALL threads, not just from the ones that don't fit into your stereotype of the non-friendly-fork.

[_DS_]

Hmm.. interesting who edit and why ?

[benurb]

_DS_ i don't know, but i wrote "*fork*" not "<L2j FORK>"

[_DS_]

I know (readed before edition) but does not see a reason..

[benurb]

So, this should be a topic about id factory and uuid, so back to topic