Hi,
Momenteel ben ik bezig met een SaaS dienst.
Nu gebruik ik nog korte integer primary keys met auto increment maar dat gaat niet werken wanneer we straks gebruik willen gaan maken van clusters (denk aan Galera cluster).
De ID moet 100% uniek en bij voorkeur ook sequentieel zijn (i.v.m. ordering/index) en vooraf te bepalen zijn (scheelt o.a. een roundtrip naar de db).
Het zal dan ook naar alle waarschijnlijkheid gebaseerd worden op tijd en het gegenereerde id moet in een standaard int(11) passen.
Is dit mogelijk en/of heeft iemand een idee hoe dit het beste aangepakt kan worden?
Momenteel zitten we te denken aan jjmmddhhiiss+5 random cijfers (met PHP rand() ) maar hier is een bigint voor nodig.
UUID/GUID is volgens mij niet echt een optie omdat dit vroeg of laat voor performance problemen gaat zorgen.
Hoor graag wat voor ideeën jullie hebben.
- Unieke sequentiële id int(11) zonder auto-increment (voor cluster)
-
16-11-2017, 13:40 #1
- Berichten
- 434
- Lid sinds
- 15 Jaar
Unieke sequentiële id int(11) zonder auto-increment (voor cluster)?
-
In de schijnwerper
-
16-11-2017, 14:17 #264BitsWebhosting.EU
- Berichten
- 2.092
- Lid sinds
- 17 Jaar
Re: Unieke sequentiële id int(11) zonder auto-increment (voor cluster)
Ik zou me eerst eens verdiepen in 'hoe' een galera cluster werkt. Je kunt gewoon integer primary keys gebruiken met autoincrement (aanbevolen zelfs).
Op basis van je aantal nodes in het cluster zal galera hier een offset bij tellen en zo altijd een oplopend id regelen voor nieuwe records.
Hier zitten wel gaten in de id reeksen maar dat maakt voor een 'order by' niks uit. Overigens raad ik je af om de record-id's voor iets anders te gebruiken dan de koppeling tussen records (foreign keys e.d.).
Het kan namelijk best zijn dat 2 gelijktijdige inserts op verschillende galera nodes terechtkomen en dat de laatste insert eerder wordt opgeslagen dan de voorlaatste.
Zelf het id gaan proberen te bepalen moet je echt niet gaan doen (zeker al niet vooraf) omdat je daar bij een beetje load al problemen mee gaat krijgen.
Galera zorgt zelf (bij het joinen van een node) dat de auto_increment_increment en auto_increment_offset variabelen met 1 verhoogt worden. Daarzoor zal node 1 (in een 3 node cluster) records toevoegen met id 1,4,7,10
Node 2 gebruikt dan 2,5,8,11
Node 3 gebuikt dan 3,6,9,12
Daardoor krijg je geen duplicate keys op de auto increment keys.
Ga je dit nu zelf op voorhand proberen te bepalen dan moet je dat voor ELKE tabel gaan doen én zorgen dat je rand() functie uniek is per webserver die gekoppeld is. Met veel records kom je er al snel achter dat rand() niet zo random is als je denkt. Echter het grootste probleem wordt (indien het eens fout gaat) om uit te zoeken wáár en wánneer het fout is gegaan. Dat is vrij ondoenlijk, nog los van de shitload aan extra code je nodig hebt om dit soort fouten op te vangen.
-
16-11-2017, 14:33 #3
- Berichten
- 434
- Lid sinds
- 15 Jaar
Re: Unieke sequentiële id int(11) zonder auto-increment (voor cluster)
Hi John,
Bedankt voor je snelle reactie.
Wat je zegt klopt, maar je mist denk ik de reden waarom ik dit soort id nodig heb (en de reden staat al in je antwoord):
Stel ik heb een accounts tabel.
Bij het aanmaken van een account maak ik gebruik van de auto increment op de primary key en elk nieuw account heeft dus een oplopend ID.
Ben verder niet bang dat men ID's kan gaan raden, men heeft enkel toegang tot hun eigen omgeving en ID's gaan lopen plussen heeft dan ook geen zin.
Dat terzijde, stel ik heb het volgende account ID (wat dus een primaire AI key is): 1119 en ik maak een nieuw account aan, dat ID wordt dus 1120
Dat gaat allemaal prima, wellicht maakt Galera er 1122 van bij meerdere nodes, is ook niet heel erg. Alleen het ophalen van een account is nu met een Galera cluster niet meer mogelijk. Stel ik wil account met ID 1122 ophalen, dat gaat nu niet want dan moet ik vooraf weten welke node dit specifieke ID heeft. Ofwel, een unieke ID zonder de MySQL auto increment is nodig om met clusters te kunnen werken.
Lees ook Galera's docu:
Do not rely on auto-increment values to be sequential. Galera uses a mechanism based on autoincrement increment to produce unique non-conflicting sequences, so on every single node the sequence will have gaps.
Als ik het mis heb hoor ik het heel graag ;)Laatst aangepast door R v/d Sanden : 16-11-2017 om 14:39
-
16-11-2017, 14:53 #464BitsWebhosting.EU
- Berichten
- 2.092
- Lid sinds
- 17 Jaar
Re: Unieke sequentiële id int(11) zonder auto-increment (voor cluster)
Nee, galera repliceert alle records naar alle nodes (dat is het idee achter multimaster tenslotte). Dus je id 1122 kun je vanaf elke node ophalen. Dat hoef je niet apart bij te houden.
Galera zorgt dat elke tabel met een AI bij een insert een id oplevert dat op een andere node nooit aangemaakt kan worden. Daarna wordt dat record via de slavethreads van de andere nodes opgehaald en lokaal toegevoegd.
Daarna kun je het record met een specifiek id vanaf elke node weer ophalen.
-
16-11-2017, 15:37 #5
- Berichten
- 434
- Lid sinds
- 15 Jaar
Re: Unieke sequentiële id int(11) zonder auto-increment (voor cluster)
Ah, helder. Dat scheelt een heel gedoe (en onnodig lange ID's).
Wat bedoelen ze met dat je niet afhankelijk moet zijn dat de id's opeenvolgend zijn, wellicht interpreteer ik dat verkeerd?
-
16-11-2017, 16:05 #664BitsWebhosting.EU
- Berichten
- 2.092
- Lid sinds
- 17 Jaar
Re: Unieke sequentiële id int(11) zonder auto-increment (voor cluster)
Je kunt niet garanderen dat ID's 1,2,3,4,5 zijn. Het kan zijn dat ze 1,2,5,7,8,11 zijn bijvoorbeeld.
Normaal ga je loadbalancen over de ip's van de galeranodes. In een ideaal geval als elke node om en om een insert krijgt dan lopen ze wel door zonder gaten, maar in de praktijk kun je niet voorspellen op welke node een insert gebeurd. Stel die komen allemaal op node 1 binnen dan zijn je id's 1,4,7,10 etc.
Sterker nog je kunt hebben dat het id niet eens in de juiste volgorde wordt toegevoegd. Je kunt er alleen zeker van zijn dat de id's uniek zijn binnen het cluster. Daarom moet je die id's ook nooit gebruiken voor klantnummers, factuurnummers etc.
Het kan zelfs zijn dat een reeks van 1,2,3 technisch in volgorde 1,3,2 wordt toegevoegd, indien 1 van de nodes op dat moment te druk is om insert 2 te doen terwijl die insert wel naar die node is gestuurd. In combinatie met een timestamp zoals NOW() kun je dan een afwijkende volgorde hebben als je query doet op 'order by id' t.o.v. 'order by timestamp'.
-
17-11-2017, 00:15 #764BitsWebhosting.EU
- Berichten
- 2.092
- Lid sinds
- 17 Jaar
Re: Unieke sequentiële id int(11) zonder auto-increment (voor cluster)
Er zijn overigens nog wel een aantal dingen waar je mee moet opletten met galera:
- innodb only, gebruik dit vanaf het begin en test het met grote tabellen om geen 'hey, wtf?!?!' momentjes te krijgen als je van myisam naar innodb over moet.
- select count(*) kun je wel vergeten als je wat grotere db's krijgt
- users toevoegen via dml ( create user blah.... ) en niet direct in de mysql.user tabel
- recovery... maak backups... genoeg backups... elk uur, 2 uur is niet overdreven
- indien mogelijk, gebruik Docker Swarm om je cluster (en de rest van je saas app op te zetten). Dit schaalt een stuk prettiger.
- memcache zoveel je kunt. De schrijfacties gaan altijd naar alle nodes, dus dat schaalt niet echt eigenlijk. Dat io verlies kun je vaak eenvoudig opvangen door de reads uit memcache te halen voor zover mogelijk.
- gebruik transacties (pdo begintransaction, commit, rollback) en hou er rekening mee dat je deadlocks kunt krijgen als je hier niet goed mee omgaat. Galera lost dit 'goed' op door 1 transactie dan te killen, maar je wilt dat mogelijk wel afvangen dan. Belangrijk voor een betaling, minder belangrijk voor een webhit tellertje natuurlijk.
Succes!
-
19-11-2017, 15:38 #8
- Berichten
- 434
- Lid sinds
- 15 Jaar
Re: Unieke sequentiële id int(11) zonder auto-increment (voor cluster)
Thanks John!
Meeste punten zijn al actief, maar is Galera zo onbetrouwbaar?
"recovery... maak backups... genoeg backups... elk uur, 2 uur is niet overdreven"
Het hoeft overigens niet perse GC te zijn, als je andere opties hebt om beter te kunnen schalen, maar makkelijk om mee te beginnen hoor ik het graag.
"gebruik transacties (pdo begintransaction, commit, rollback)"
Momenteel zijn de queries vooral enkele queries (simpele update/insert/select) en geen meerdere queries achter elkaar die van elkaar afhankelijk zijn.
Alle queries lopen via een centrale functie waarin ik via PDO en parameterised queries een connectie maak met de db(s).
Transactions kan ik door deze aanpak ook binnen een paar sec. toevoegen, maar heb dat juist (nog) niet gedaan omdat ik dan juist het idee had dat er meer fout kan gaan (transactions die door plotseling stoppen van script/verbinding gebruiker blijven hangen in het systeem en de boel ophouden).
Of mis ik hier iets en is dit "verplicht" wanneer ik met GC ga werken, omdat dat wellicht ook in verbinding staat met de replicatie naar de nodes?
Weet veel, maar lang niet alles ;)
Hoor het graag.
-
19-11-2017, 16:52 #964BitsWebhosting.EU
- Berichten
- 2.092
- Lid sinds
- 17 Jaar
Re: Unieke sequentiële id int(11) zonder auto-increment (voor cluster)
Nou, onbetrouwbaar wil ik niet zeggen. Complexer eigenlijk meer. Stel je hebt 3 identieke nodes en 1 zit opeens vol om whatever reden, dan zitten die andere 2 dat ook natuurlijk. In een aantal gevallen ben je dan sneller weer in de lucht dan eerst te moeten gaan uitzoeken welke node de meest recente data heeft (en meestal die dat juist díe node die op de bek ging omdat ie als eerst vol zat).
Buiten dat is je backupschema natuurlijk altijd afgestemd op de hoeveelheid data die je bereidt bent te verliezen. Dat kan 1 keer per maand zijn, maar ook elke 5 minuten.
Transacties zijn niet verplicht, zolang je maar goed voor ogen hebt dat verschillende inserts/updates tegelijk op verschillende nodes terecht kunnen komen.
Plaats een
- + Advertentie
- + Onderwerp
Marktplaats
Webmasterforum
- Websites algemeen
- Sitechecks
- Marketing
- Domeinen algemeen
- Waardebepaling
- CMS
- Wordpress
- Joomla
- Magento
- Google algemeen
- SEO
- Analytics
- Adsense
- Adwords
- HTML / XHTML
- CSS
- Programmeren
- PHP
- Javascript
- JQuery
- MySQL
- Ondernemen algemeen
- Belastingen
- Juridisch
- Grafisch ontwerp
- Hosting Algemeen
- Hardware Info
- Offtopic