[php/mysql] master-slave incrementele waardes gegroepeerd

[Saven]

Ik ben bezig met een hobbyproject om daarin vooral meer te leren over grote webapplicaties die server scaling vereisen. Niet voor productie, puur zelfeducatie.

Lang verhaal kort. Stel ik heb een app waar mensen een online shop kunnen beginnen (SaaS) en waarin dus orders worden geplaatst. Elke ordertabel heeft natuurlijk een autoincrement voor order.ID. Stel dat er meerdere shops draaien, dan moet elke shop order ook zijn eigen order.number hebben. Incrementeel per order.shop_id dus. Zodat shop A ordernummers 1.2.3.4 etc. heeft, maar shop B ook deze 1,2,3,4,5.. waardes.

Nu kun je bij het maken van een nieuwe order normaliter gewoon de laatste shop order ophalen, daar +1 bij optellen, en de waarde in order.number van de nieuwe record zetten.

Maarr, in een master-slave geval zit er natuurlijk lag tussen de write en read server. Stel dat er via een API tientallen orders tegelijkertijd worden aangemaakt (bijv. wanneer iemand een import draait). Hoe zou je dit dan op kunnen lossen? Rekening houdend met dat er tijdens de import ook orders door klanten kunnen worden geplaatst.

Standaard leeglaten van order.number en via een cron/task achteraf invullen lijkt me ook niet sjiek, en weinig haalbaar aangezien orders bijvoorbeeld direct gemailt moeten worden naar de klant, en daarin ook het nummer aanwezig moet zijn.

Je zou die query om het laatste ordernummer op te halen kunnen uitvoeren op de master server, mja dan ga je misschien tientallen queries per seconde uitvoeren op die server terwijl die daar eigenlijk niet voor bedoeld is.

Suggesties?

[Merethil]

Wat is er mis met auto_increment? Die doet het automatisch juist, ook in een master-slave-opstelling. Of eventueel UUIDs, dan heb je "nooit" problemen hiermee maar zijn ze ook niet oplopend.

[RobIII]

Saven schreef op zaterdag 29 februari 2020 @ 12:37:
Stel dat er meerdere shops draaien, dan moet elke shop order ook zijn eigen order.number hebben. Incrementeel per order.shop_id dus. Zodat shop A ordernummers 1.2.3.4 etc. heeft, maar shop B ook deze 1,2,3,4,5.. waardes.

Want? Wat is er mis met één "globale" auto-increment voor die tabel? Een order-id is niet 'tzelfde als een ordernummer.

Saven schreef op zaterdag 29 februari 2020 @ 12:37:
Nu kun je bij het maken van een nieuwe order normaliter gewoon de laatste shop order ophalen, daar +1 bij optellen, en de waarde in order.number van de nieuwe record zetten.

Oh, nee, nóóit doen! Dat geeft alleen maar race conditions. Laat 't over aan je RDBMS alsjeblieft. Of gebruik iets als een GUID/UUID, ULID of snowflake ID o.i.d. als je toch bezig bent horizontaal te schalen.

Saven schreef op zaterdag 29 februari 2020 @ 12:37:
Maarr, in een master-slave geval zit er natuurlijk lag tussen de write en read server. Stel dat er via een API tientallen orders tegelijkertijd worden aangemaakt (bijv. wanneer iemand een import draait). Hoe zou je dit dan op kunnen lossen? Rekening houdend met dat er tijdens de import ook orders door klanten kunnen worden geplaatst.

Daar heb je toch juist een master en een zwik slaves voor? De slaves gebruik je gewoon read-only en de master voor de writes en dan laat je 't ID over aan de auto-increment die je RDBMS biedt.

Saven schreef op zaterdag 29 februari 2020 @ 12:37:
Je zou die query om het laatste ordernummer op te halen kunnen uitvoeren op de master server, mja dan ga je misschien tientallen queries per seconde uitvoeren op die server terwijl die daar eigenlijk niet voor bedoeld is.

Again, dit ga je niet werkend krijgen en al helemaal niet op schaal; dit is een disaster waiting to happen. Want race-conditions.

Merethil schreef op zaterdag 29 februari 2020 @ 12:44:
Of eventueel UUIDs, dan heb je "nooit" problemen hiermee maar zijn ze ook niet oplopend.

UUID's zijn inderdaad een bitch voor je indexen, maar dat is vooral afhankelijk van welke versie je gebruikt; elke versie heeft weer z'n eigen voor/nadelen (V4 is bijvoorbeeld aardig funest doorgaans). Maar er zijn ook, zoals ik hierboven al linkte, nog alternatieven die bijv. efficiënter zijn qua opslag (64 bits vs 128 bits bijv), toch (voornamelijk) oplopend of op andere manieren voordelen bieden. Ik zou ze in ieder geval eens overwegen

[ Voor 14% gewijzigd door RobIII op 29-02-2020 13:56 ]

[Saven]

Merethil schreef op zaterdag 29 februari 2020 @ 12:44:
Wat is er mis met auto_increment? Die doet het automatisch juist, ook in een master-slave-opstelling. Of eventueel UUIDs, dan heb je "nooit" problemen hiermee maar zijn ze ook niet oplopend.

RobIII schreef op zaterdag 29 februari 2020 @ 13:50:
[...]

Want? Wat is er mis met één "globale" auto-increment voor die tabel? Een order-id is niet 'tzelfde als een ordernummer.


[...]

Oh, nee, nóóit doen! Dat geeft alleen maar race conditions. Laat 't over aan je RDBMS alsjeblieft. Of gebruik iets als een GUID/UUID, ULID of snowflake ID o.i.d. als je toch bezig bent horizontaal te schalen.


[...]

Daar heb je toch juist een master en een zwik slaves voor? De slaves gebruik je gewoon read-only en de master voor de writes en dan laat je 't ID over aan de auto-increment die je RDBMS biedt.


[...]

Again, dit ga je niet werkend krijgen en al helemaal niet op schaal; dit is een disaster waiting to happen. Want race-conditions.


[...]

UUID's zijn inderdaad een bitch voor je indexen, maar dat is vooral afhankelijk van welke versie je gebruikt; elke versie heeft weer z'n eigen voor/nadelen (V4 is bijvoorbeeld aardig funest doorgaans). Maar er zijn ook, zoals ik hierboven al linkte, nog alternatieven die bijv. efficiënter zijn qua opslag (64 bits vs 128 bits bijv), toch (voornamelijk) oplopend of op andere manieren voordelen bieden. Ik zou ze in ieder geval eens overwegen

Aah thanks voor de replies jongens, maar ik denk dat jullie me niet goed hebben begrepen.

Uiteraard is de primary key (id) gewoon big unsigned autoincrement. Maar je moet het zo zien, elke shop plaatst orders in de database. Maar voor 'hun referentie' hebben ze een 'meer leesbaar' nummer (met bijv. prefix ORD) in plaats van de database ID.

Dus bijv:
ORD00001
ORD00002
ORD00003
ORD00004
.....
ORD00365

En dezelfde reeks geldt dus ook voor alle andere shops. Die beginnen met ordernummer ORD00001 voor hun eerste order. Op die manier kunnen zij en hun klanten refereren naar een ordernummer in plaats van het ID in de database.

Zowel shop A als shop B hebben dus ordernummers beginnend bij ORD00001 t/m ORD99999 en verder.

Hoop dat het zo wat duidelijker is Iets dergelijks is vrij veelvoorkomend bij dergelijke software.

Dat als ze een lijst met orders opvragen, ze hun eigen ordernummers te zien krijgen (opeenvolgend zonder duplicates)

Voorbeeld tabel in de front-end

code:

order   |   klant   |   datum      |
--------------------------------------
ORD001  | Jansen    |  01-01-2010  |
ORD002  | Peters    |  02-01-2010  |
ORD003  | de Jong   |  03-01-2010  |

[ Voor 6% gewijzigd door Saven op 29-02-2020 14:10 ]

[Kalentum]

Als je dat wilt (unieke order nummers op applicatieniveau afhandelen) moet je nooit die data lezen van de slave. Gaat gewoon niet werken. Om allerlei redenen kan je af en toe replication lag hebben. Op mijn werk hebben per applicatie 1 mysql master (en een standby master) en alle eindgebruikers zitten dus altijd op 1 master die altijd up-to-date is.

[Postman]

Zie eerlijk gezegd niet in waarom je met aparte order nummer wil gaan werken. Je hebt immers al een uniek nummer (je PK) en dan wil je vervolgens een extra oplossing verzinnen voor nog een uniek nummer. Iets met het wiel opnieuw uitvinden.
Dat het aan de voorkant ORD001 is, zegt niet dat het in de database gewoon 1 kan zijn. De rest van de letters en cijfers voeg je toe en verwijder je in de applicatielaag.

[DJMaze]

Dat nummers oplopend moeten zijn is onzin.
Er is maar 1 uitzondering waar je je druk om moet maken: factuurnummer
Doe ja dat fout, kan je ophouden

[Kalentum]

Als ik zo'n systeem zou ontwerpen, zou ik ook de ordernummertjes laten op lopen per shop. Het is ook niet dat het juridisch moet. Maar het lijkt me gewoon een productkeuze. En als die ordernummertjes zo belangrijk zijn dan moet de code gewoon de master raadplegen om 'het hoogste nummertje per shop' op te vragen.

Je kan het veilig krijgen door een unieke index op shop_id en ordernummer aan te maken. Je code doet dan ongeveer dit:
- Start een database transactie
- Vraag het hoogste nummertje voor een gegeven shop op
- Maak een nieuwe order aan
- UNIQUE_KEY violation exception? rollback de transactie en probeer het nog een keer
- Anders commit de transactie

[RobIII]

Postman schreef op zaterdag 29 februari 2020 @ 15:05:
Zie eerlijk gezegd niet in waarom je met aparte order nummer wil gaan werken.

Omdat elke shop/klant natuurlijk zijn/haar eigen factuurreeks heeft.

Kalentum schreef op zaterdag 29 februari 2020 @ 15:41:
Het is ook niet dat het juridisch moet.

Jawel

quote: https://www.belastingdien...turen_maken/factuureisen/

een opeenvolgend nummer

Kalentum schreef op zaterdag 29 februari 2020 @ 15:41:
Je kan het veilig krijgen door een unieke index op shop_id en ordernummer aan te maken. Je code doet dan ongeveer dit:
- Start een database transactie
- Vraag het hoogste nummertje voor een gegeven shop op
- Maak een nieuwe order aan
- UNIQUE_KEY violation exception? rollback de transactie en probeer het nog een keer
- Anders commit de transactie

Klopt, maar dat wordt een heel ander verhaal als je 't hebt over "grote webapplicaties die server scaling vereisen" (zie topicstart). Dan heb je telkens hele dure locks op je tabel voor het bepalen van een factuurnummertje...
Having said that: zolang je geen Facebook / Google / Twitter / whatever bent werkt dat natuurlijk prima. Maar dat was de insteek van 't topic niet. En on that note: ik heb daar inhoudelijk ook nog niet op geantwoord want ik las 't in eerste instantie als 't bepalen van PK ID's maar het gaat hier om (verschillende reeksen) factuurnummers. In dat geval zou ik een generator per webshop (of per "tenant") hanteren. Oracle biedt zoiets als sequence aan volgens mij maar je zou ook een service kunnen maken met als enig doel verschillende (tenant) reeksen bij te houden en delegeren/orchestreren.

[ Voor 53% gewijzigd door RobIII op 29-02-2020 16:46 ]

[DJMaze]

RobIII schreef op zaterdag 29 februari 2020 @ 16:27:
Omdat elke shop/klant natuurlijk zijn/haar eigen factuurreeks heeft.

Factuurnummer != ordernummer

Ik kaarte factuurnummer wel aan, omdat daarmee duidelijk wordt dat jouw eerdere opmerkingen essentieel zijn, maar dan om de reden van factuurnummering.

[RobIII]

DJMaze schreef op zaterdag 29 februari 2020 @ 16:49:
Factuurnummer != ordernummer

Ik geloof dat in dit topic factuur en order vrij 'synoniem' gebruikt worden. Maar goed...

[DJMaze]

Ik begrijp dat, totdat facturering los staat van orders (wat hoort).
Zat "shops" om moeten bouwen omdat initieel het 'synoniem' was 😂

[DataGhost]

RobIII schreef op zaterdag 29 februari 2020 @ 16:50:
[...]

Ik geloof dat in dit topic factuur en order vrij 'synoniem' gebruikt worden. Maar goed...

Als het goed is heeft een geretourneerde order minimaal twee verschillende facturen en bijbehorende factuurnummers, dus dat gaat al mis.

[RobIII]

DJMaze schreef op zaterdag 29 februari 2020 @ 16:54:
Ik begrijp dat, totdat facturering los staat van orders (wat hoort).
Zat "shops" om moeten bouwen omdat initieel het 'synoniem' was 😂

DataGhost schreef op zaterdag 29 februari 2020 @ 17:11:
[...]

Als het goed is heeft een geretourneerde order minimaal twee verschillende facturen en bijbehorende factuurnummers, dus dat gaat al mis.

Ik snap en weet dat factuur en ordernummers iets anders zijn en los staan van elkaar. Punt was dat waar men "ordernummer" schreef men waarschijnlijk gewoon "factuurnummer" bedoelde. Misschien ook niet. Boeie; is verder nu wel duidelijk Laten we terug ontopic gaan.

[Kalentum]

RobIII schreef op zaterdag 29 februari 2020 @ 16:27:
[...]
Klopt, maar dat wordt een heel ander verhaal als je 't hebt over "grote webapplicaties die server scaling vereisen" (zie topicstart). Dan heb je telkens hele dure locks op je tabel voor het bepalen van een factuurnummertje...

Het is MySQL dus ik ga ervan uit dat het over InnoDB gaat. Maar ik kan in mijn scenario (SELECT, INSERT, retry indien ordernummertje al bestaat) geen scenario bedenken waarin er een dure table lock wordt gezet. Zelfs row locks worden niet gezet. En zolang je voor je bedrijfskritische dingen maar gewoon tegen de master aanpraat gaat dat prima.

[Saven]

Kalentum schreef op zaterdag 29 februari 2020 @ 15:41:
Als ik zo'n systeem zou ontwerpen, zou ik ook de ordernummertjes laten op lopen per shop. Het is ook niet dat het juridisch moet. Maar het lijkt me gewoon een productkeuze. En als die ordernummertjes zo belangrijk zijn dan moet de code gewoon de master raadplegen om 'het hoogste nummertje per shop' op te vragen.

Je kan het veilig krijgen door een unieke index op shop_id en ordernummer aan te maken. Je code doet dan ongeveer dit:
- Start een database transactie
- Vraag het hoogste nummertje voor een gegeven shop op
- Maak een nieuwe order aan
- UNIQUE_KEY violation exception? rollback de transactie en probeer het nog een keer
- Anders commit de transactie

Wat ik dus idd al dacht Dan is't niet anders.

Maar stapje verder dan: wat als je twee masters(?) naast elkaar hebt draaien als je bijv. aan het limiet van diskspace zit. Op een gegeven moment heb je niet genoeg ruimte meer. Of ga je dan meer richting sharding.

Wellicht allemaal niet aan de order van de dag, maar vind 't wel interessant om erachter te komen

RobIII schreef op zaterdag 29 februari 2020 @ 16:27:
[...]


Omdat elke shop/klant natuurlijk zijn/haar eigen factuurreeks heeft.


[...]


Jawel

[...]


[...]

Klopt, maar dat wordt een heel ander verhaal als je 't hebt over "grote webapplicaties die server scaling vereisen" (zie topicstart). Dan heb je telkens hele dure locks op je tabel voor het bepalen van een factuurnummertje...
Having said that: zolang je geen Facebook / Google / Twitter / whatever bent werkt dat natuurlijk prima. Maar dat was de insteek van 't topic niet. En on that note: ik heb daar inhoudelijk ook nog niet op geantwoord want ik las 't in eerste instantie als 't bepalen van PK ID's maar het gaat hier om (verschillende reeksen) factuurnummers. In dat geval zou ik een generator per webshop (of per "tenant") hanteren. Oracle biedt zoiets als sequence aan volgens mij maar je zou ook een service kunnen maken met als enig doel verschillende (tenant) reeksen bij te houden en delegeren/orchestreren.

Haha. Zeker geen FB of Twitter nee Hoe zie je zo'n generator voor je als je niet zuiver kunt bepalen wat het laatste nummer uit de reeks is? In dit geval 't liefst allemaal mysql georiënteerd.

[ Voor 35% gewijzigd door Saven op 29-02-2020 17:23 ]

[Kalentum]

Saven schreef op zaterdag 29 februari 2020 @ 17:21:
[...]

Wat ik dus idd al dacht Dan is't niet anders.

Maar stapje verder dan: wat als je twee masters(?) naast elkaar hebt draaien als je bijv. aan het limiet van diskspace zit. Op een gegeven moment heb je niet genoeg ruimte meer. Of ga je dan meer richting sharding.

Ik werk bij een bedrijf dat master-master replicatie toepast en het was inderdaad niet wenselijk om continue maar diskspace aan MySQL servers toe te voegen. In ons geval is er gekozen om naar micro-service architectuur om te schakelen. Dat wil zeggen dat, in plaats van 1 grote applicatie, je verschillende kleine applicaties hebt die elk hun eigen performance eisen hebben. En ook hun eigen MySQL servers hebben. De data raakt dan verspreid over verschillende MySQL servers, wat niet altijd handig is (geen JOINS meer) maar wel goed schaalt.

In jouw geval: Je hebt bv een applicatie voor het tonen van de shop producten, voor het verwerken van bestellingen, voor de user accounts etcetera.

Concreet voorbeeld: de user 'table' kunnen we migreren naar een Accounts microservice die single source of truth is voor alle data die direct bij een user hoort (username, password, adres gegevens etcetera)

[RobIII]

Kalentum schreef op zaterdag 29 februari 2020 @ 17:19:
En zolang je voor je bedrijfskritische dingen maar gewoon tegen de master aanpraat gaat dat prima.

Je hebt dan wel een ontzettende SPOF. Maar goed, ik denk dat we het wel eens zijn dat het op "Google schaal" een ander verhaal is dan voor de gemiddelde webshop.

[Kalentum]

RobIII schreef op zaterdag 29 februari 2020 @ 20:36:
[...]

Je hebt dan wel een ontzettende SPOF. Maar goed, ik denk dat we het wel eens zijn dat het op "Google schaal" een ander verhaal is dan voor de gemiddelde webshop.

@Saven zit Shopify opnieuw uit te vinden. Dat hangt er tussen in. Overigens is een database als SPOF op te vangen met master-master replicatie. Master 1 is live en Master 2 is een hotstandby die functioneert als een slave totdat master1 omvalt.

[DJMaze]

@Kalentum kies dan voor een Galera cluster
En Percona misschien ook.

[switchboy]

Laat maar probleem verkeerd begrepen.

[ Voor 169% gewijzigd door switchboy op 01-03-2020 03:52 ]

[Wim-Bart]

Schaal kan je oplossen door gewoon voor iedere "omgeving" een eigen database te hebben waardoor je eenvoudig een master-master of master-slave kan neerzetten. Met Postgress, SQL Server, Maria DB, MySQL kan je gewoon multi instance draaien op een enkele machine. Voor multi tennant omgevingen kan je dus meerdere masters draaien op een host en meerdere slaves op een host. Stel je hebt 9 omgevingen. Dan zet je de volgende structuur op:

Host 1: master-A, master-B, master-C, Slave-G, Slave-H, Slave-I
Host 2: slave-A, Slave-B, slave-C, Master-D, Master-E, Master-F
Host 3: Slave D,. Slave-E,. Slave-F, Master G, Master-H, Master-I

Wanneer er een host uit valt dan heb je altijd een Database beschikbaar, en read-only requests balancen tussen master en slave.

Daarnaast kan je iedere applicatie lekker zijn eigen Master/Slave geven, kunnen er door bugs in code geen gegevens lekken, kan je onbeperkt schalen per klant omgeving, heb je geen last van run conditions omdat iedere DB zijn eigen logica heeft (eenvoudig gewoon iedere DB zijn eigen stored procedures met business logica) en hoef je geen rekening te houden met rare voorvoegsels enzo omdat dat in een config tabel zit die gebruikt wordt op frontend applicatie of binnen de views en stored prodedures.

Tevens heb je ook geen last van locking doorat meerdere applicaties in de zelfde tabellen zitten te klooien.

En wanneer je verder kijkt, zou je zelfs kunnen gaan containeren met bijvoorbeeld Docker en persistent containers.

[Saven]

Kalentum schreef op zaterdag 29 februari 2020 @ 22:15:
[...]


@Saven zit Shopify opnieuw uit te vinden. Dat hangt er tussen in. Overigens is een database als SPOF op te vangen met master-master replicatie. Master 1 is live en Master 2 is een hotstandby die functioneert als een slave totdat master1 omvalt.

Hehe Shopify was inderdaad een goed voorbeeld geweest Thanks voor alle replies iedereen!