Goede database structuur?
Ik ben op dit moment bezig met het coden van 1 van de grootste projecten van mezelf tot nu toe. Het gaat om community site's, zoals http://www.apeldoorner.com. Deze draaien op dit moment op een systeem wat niet langer kan. Met ongeveer 1000 gebruikers online krijgt de database het behoorlijk zwaar en valt de site geregeld uit.
Ik ben begonnen met de database structuur, maar nu zit ik met een probleem hoe ik het beste de leden tabellen kan maken. Op het profiel kan aardig wat info ingevuld worden, maar moet ik dat allemaal in 1 tabel stoppen, of is het beter om het in meerdere kleine tabellen te stoppen?
Dus eigenlijk is mijn vraag: wat is in dit geval beter? Alles in 1 tabel stoppen en met 1 query er uit halen, of alles in veel tabellen stoppen en het er met meerdere query's en/of joins uit halen?
TJVB schreef op 09.10.2007 13:58:
het moet op zich gewoon in 1 tabel kunnen alleen moet je in je query's alleen selecteren wat je nodig hebt.
Bovendien is normaliseren zeker bij dit soort grotere sites heel belangrijk, en Zal je waarschijnlijk veel koppeltabellen moeten gaan gebruiken.
Gewijzigd op 01/01/1970 01:00:00 door Robert Deiman
Quote:
Rare vraag, blijkbaar heb je geen idee wat een database (DBMS) is.wat is in dit geval beter? Alles in 1 tabel stoppen en met 1 query er uit halen, of alles in veel tabellen stoppen en het er met meerdere query's en/of joins uit halen?
Wanneer je alle data in 1 tabel stopt, gooi je direct het hele idee achter een DBMS weg.
Wanneer jouw server geen 1000 bezoekers aankan, dan zul je moeten uitzoeken wat daarvan de oorzaak is. Gewoon roepen dat de database dat niet aankan, slaat nergens op, een slecht script kan iedere database onderuit halen. Het kan ook zijn dat de server het gewoon niet aankan of dat er problemen zijn met 1 van de duizenden andere mogelijkheden.
Oplossing:
- Leer eerst eens wat een DBMS is en wat deze voor je kan doen
- Ga normaliseren
- Ga een stabiele server opzetten
- Zoek de juiste DBMS (MySQL valt eigenlijk af, die kan slecht uit de voeten met veel gelijktijdige gebruikers)
- Ga de juiste queries opzetten
- Ga benchmarken, meten = weten
Dus het is het beste om alles op te splitsen in meerdere tabellen, en dan alles aan elkaar te linken?
Een voorbeeld:
Users
userID
userName
wachtwoord
users__personalia
lidID
voornaam
achternaam
geslacht
geboorteDatum
users__geografisch
lidID
land
plaats
straat
huisnummer
en zo verder..
Is dit een goed begin of moet ik het nog verder splitsen?
We weten op dit moment dat de code gewoon baggerslecht in elkaar zit. En waarschijnlijk komt het ook daardoor dat de database het niet aan kan. En we weten dat het de database is, omdat de database een load van 30 heeft, en alle andere webservers enz. hebben het rustig.
We zijn ook bezig met het nieuwe systeem om het script te verbeteren, maar we willen vanaf het begin met alles rekening houden. En door hier dingen te vragen kunnen we met zoveel mogelijk rekening houden.
Wat zou in dit geval een goed alternatief zijn v oor MySql? ( Ik heb zelf alleen met mysql gewerkt, ik weet wel dat er varianten als postgreSQL zijn, maar wat het verschil precies is weet ik niet.
Micha schreef op 09.10.2007 14:17:
Ik had die normalisatie tutorials al wel doorgelezen. Daar wordt alles wel opgeslagen in verschillende tabellen. Maar ik wist niet of dat voor een profiel ook handig was, waar per rij alleen info over die gebruiker staat.
Met die opmerking begrijp je blijkbaar niet waarom dingen in verschillende tabellen worden opgeslagen of wel? Je gebruikt alleen eigenlijk meerdere tabellen als het om anderssoortige gegevens gaat, of wanneer er als je dat niet doet redundante informatie ontstaat.
Bijv. als je in je tabel "profiel" ook de provincie volledig opslaat van een bezoeker (ik snap dat het om apeldoorn gaat, maar het gaat even om een voorbeeld) en je doet dat voor alle 1000 dan heb je veel dubbele gegevens, er zijn namelijk maar een paar provincies hier, en die zou je in een aparte tabel op moeten slaan en slechts via het id van een provincie met je profieltabel koppelen.
Micha schreef op 09.10.2007 14:17:
Een voorbeeld:
Users
userID
userName
email
wachtwoord
users__personalia
lidID
voornaam
achternaam
geslacht
geboorteDatum
users__geografisch
lidID
land
plaats
straat
huisnummer
en zo verder..
Is dit een goed begin of moet ik het nog verder splitsen?
Users
userID
userName
wachtwoord
users__personalia
lidID
voornaam
achternaam
geslacht
geboorteDatum
users__geografisch
lidID
land
plaats
straat
huisnummer
en zo verder..
Is dit een goed begin of moet ik het nog verder splitsen?
Waarom zou je het zo opsplitsen denk je? Plaats kan zo Nog steeds vaker voorkomen, stel dat je van de 1000 leden er 400 uit apeldoorn zelf hebt. Dan heb je dus 400keer Apeldoorn volledig uitgeschreven in je tabel staan.
Schrijf zonder daarbij aan tabellen te denken eens alle gegevens op die je van een user hebt (gewoon simpel onder elkaar) en ga dan eens die tutorial doorlopen met deze gegevens.
Micha schreef op 09.10.2007 14:23:
@pgFrank
We weten op dit moment dat de code gewoon baggerslecht in elkaar zit. En waarschijnlijk komt het ook daardoor dat de database het niet aan kan. En we weten dat het de database is, omdat de database een load van 30 heeft, en alle andere webservers enz. hebben het rustig.
We zijn ook bezig met het nieuwe systeem om het script te verbeteren, maar we willen vanaf het begin met alles rekening houden. En door hier dingen te vragen kunnen we met zoveel mogelijk rekening houden.
Wat zou in dit geval een goed alternatief zijn v oor MySql? ( Ik heb zelf alleen met mysql gewerkt, ik weet wel dat er varianten als postgreSQL zijn, maar wat het verschil precies is weet ik niet.
We weten op dit moment dat de code gewoon baggerslecht in elkaar zit. En waarschijnlijk komt het ook daardoor dat de database het niet aan kan. En we weten dat het de database is, omdat de database een load van 30 heeft, en alle andere webservers enz. hebben het rustig.
We zijn ook bezig met het nieuwe systeem om het script te verbeteren, maar we willen vanaf het begin met alles rekening houden. En door hier dingen te vragen kunnen we met zoveel mogelijk rekening houden.
Wat zou in dit geval een goed alternatief zijn v oor MySql? ( Ik heb zelf alleen met mysql gewerkt, ik weet wel dat er varianten als postgreSQL zijn, maar wat het verschil precies is weet ik niet.
Als je de database goed opbouwt maakt het (met MySQL5) niet zoveel meer uit of je MySQL of Postgre gebruikt. Als ik zo jou idee zie voor het splitsen van de tabellen, dan snap ik ook dat het door een heel slechte normalisatie komt dat de database nu niet zoveel tegelijk aankan.
Ik kan niet anders zeggen dan dat het belang van Normaliseren maar weer eens blijkt. Als je dat goed hebt gedaan, dan moet het al een stuk soepeler draaien. Maar blijkbaar ken jij normaliseren niet zo en kennen diegenen die dit gemaakt hebben (in eerste instantie) het ook niet.
Gewijzigd op 01/01/1970 01:00:00 door Robert Deiman
Quote:
Helaas klopt dit niet, MySQL kan ook met versie 5 nog steeds niet goed uit de voeten met veel gelijktijdige gebruikers. De performance kakt gigantisch in, met bv. 25 gelijktijdige gebruikers worden er nog 300 queries per secondes uitgevoerd, met 50 gelijktijdige gebruikers blijven er slechts 100 queries per seconde over (zijn slechts voorbeelden, maar niet zomaar uit de lucht gegrepen). Als je de database goed opbouwt maakt het (met MySQL5) niet zoveel meer uit of je MySQL of Postgre gebruikt.
Op exact dezelfde hardware kan pgSQL al een hogere piek neerzetten, bv. 400 queries per seconde, maar houdt dit ook vol bij grotere aantallen gebruikers. Het aantal queries per seconde neemt wel iets af, maar dat zijn slechts enkele procenten en niet vele tientallen procenten zoals bij MySQL.
Maar goed, gezien de vraagstelling zijn er grotere problemen dan de keuze MySQL/pgSQL.
Ik ging er hierbij wel vanuit dat het aantal gebruikers van "slechts" 1000 niet zoveel uitmaakte, en ging niet uit van nog veel grotere systemen. -> Aan de naam van de comunity te zien zal het hoofdzakelijk voor mensen uit omgeving apeldoorn gebruikt worden,en niet door heel nederland (zoals hyves)
Volgens mij zou het dan niet zoveel uit maken of pgSQL of MySQL gebruikt wordt, maar wordt het verschil pas merkbaar bij veel grotere aantallen, of zit ik hier mis mee?
Ik werk samen met Micha aan dit project (tenminste, sinds we zijn begonnen aan een nieuwe versie). Het dilemma zit hem er onder andere ook in het feit dat het schijnbaar nodig is om random waardes toe te staan bij velden als adres, etc. Dit om de huidige breezergeneratie tegemoet te komen; daar deze schijnbaar vaak wonen "op een rotonde", "in d3 w0lk3n", etc. Spaties in nicknames zijn ook tof. Met andere woorden; de database zal voor zo'n 50% informatie bevatten die absoluut niet gerelateerd is aan de geplande inhoud.
Het idee wat ons voornamelijk bezig houd momenteel; is in hoeverre normalisatie daadwerkelijk zal leiden tot een betere load balance. Dit omdat we gezien de huidige situatie, er substantieel veel meel tabellen zullen worden aangemaakt.
Enige suggesties / feedback ?
Loadballance word verbeterd omdat je door de vele koppeltabelen gerichter en specifiekere informatie kunt ophalen.
Dat is het voordeel van normaliseren.
Dat in combinatie met "SELECT veld1,veld2 FROM table" ipv "SELECT * (10 velden) FROM table".
:)
het forum heeft momenteel een gemiddelde van 1000000 records.
berichten / admin / overige is momenteel meer dan 250000 records.
Dus we spreken hier niet over enkel 1000 records. per community loopt het op den duur gewoon tot ruim over een miljoen, soms ver daar boven afhankelijk van het aantal leden. met ver daarboven bedoel ik dan meerdere miljoenen records in zijn geheel voor 1 community.
met een gemiddelde van ik schat zo'n 5000 leden online op alle communities in de piek (avond) uren.
Gewijzigd op 01/01/1970 01:00:00 door rob
rob schreef op 09.10.2007 15:00:
Heren,
Ik werk samen met Micha aan dit project (tenminste, sinds we zijn begonnen aan een nieuwe versie). Het dilemma zit hem er onder andere ook in het feit dat het schijnbaar nodig is om random waardes toe te staan bij velden als adres, etc. Dit om de huidige breezergeneratie tegemoet te komen; daar deze schijnbaar vaak wonen "op een rotonde", "in d3 w0lk3n", etc. Spaties in nicknames zijn ook tof. Met andere woorden; de database zal voor zo'n 50% informatie bevatten die absoluut niet gerelateerd is aan de geplande inhoud.
Het idee wat ons voornamelijk bezig houd momenteel; is in hoeverre normalisatie daadwerkelijk zal leiden tot een betere load balance. Dit omdat we gezien de huidige situatie, er substantieel veel meel tabellen zullen worden aangemaakt.
Enige suggesties / feedback ?
Ik werk samen met Micha aan dit project (tenminste, sinds we zijn begonnen aan een nieuwe versie). Het dilemma zit hem er onder andere ook in het feit dat het schijnbaar nodig is om random waardes toe te staan bij velden als adres, etc. Dit om de huidige breezergeneratie tegemoet te komen; daar deze schijnbaar vaak wonen "op een rotonde", "in d3 w0lk3n", etc. Spaties in nicknames zijn ook tof. Met andere woorden; de database zal voor zo'n 50% informatie bevatten die absoluut niet gerelateerd is aan de geplande inhoud.
Het idee wat ons voornamelijk bezig houd momenteel; is in hoeverre normalisatie daadwerkelijk zal leiden tot een betere load balance. Dit omdat we gezien de huidige situatie, er substantieel veel meel tabellen zullen worden aangemaakt.
Enige suggesties / feedback ?
Zoals ik al eerder aangaf, gebruik dan wel een losse tabel voor de plaatsnamen. -> Dit scheelt al capaciteiten. De naam, moet je alleen ophalen, loginnaam en dergelijke 1 keer vergelijken is ruim voldoende, voor de rest gebruik je het id om de naam ergens aan te koppelen.
Verder wil het gebruiken van de indexen op de juiste (veelgebruikte) kolommen ook al een enorme snelheidswinst opleveren.
“Met andere woorden; de database zal voor zo’n 50% informatie bevatten die absoluut niet gerelateerd is aan de geplande inhoud.”  Hou dit ook zo, en zorg ervoor dat informatie waar je (op dat moment) niets mee doet niet wordt opgehaald in de query, door alleen de benodigde kolomnamen op te geven.
In hoeverre normalisatie zal leiden tot een betere load balance is afhankelijk van de huidige situatie, maar als ik de reacties zo zie van eerder in dit topic, is de data nog totaal niet genormaliseerd, en zal daar al een enorme snelheidswinst te behalen zijn. Daar komt nog bij dat door het gebruik van indexen en (indien MySQL5, of PostgreSQL) ook nog regels te maken voor de sleutelindexen van de verschillende tabellen (InnoDB engine bij MySQL5, Postgre zit dit al ingebakken) zal je dat ook niet met PHP moeten afvangen, waardoor er ook een snelheidswinst te behalen is.
Wat ook nog zal kunnen schelen (weet niets van jullie huidige datamodel, maar vermoedens zijn er dat ze niet goed zijn) is de juiste datatypen te gebruiken, en bijvoorbeeld data niet naar Nederlands zetten met PHP, maar juist al in de query. Dat is sneller dan het eerst ophalen, vervolgens een stuk php code overheen halen en dan weer te laten geven (1 stap minder).
Kortom, ik denk dat er voor jullie mogelijkheden te over zijn om de boel een heel stuk te optimaliseren.
Er rest mij echter nog de vraag wat je bedoelt met 'regels voor sleutelindexen' ? Zou je hier wellicht iets dieper op in willen gaan; of heb je eventueel een source met info beschikbaar ?
Klik
De InnoDB engine en pgSQL bieden namelijk de mogelijkheid om referentiële integriteit af te dwingen door het koppelen van verschillende tabellen via een key.
Bovendien kan je een index toewijzen aan een kolom, waardoor zoeken in die kolom veel gemakkelijker/ sneller gaat. Dit scheelt veel tijd en ook veel capaciteit.
Edit:
Zo kan je op deze site ook wel een tutorial vinden over pgSQL
Zo kan je op deze site ook wel een tutorial vinden over pgSQL
Gewijzigd op 01/01/1970 01:00:00 door Robert Deiman
Robert_Deiman schreef op 09.10.2007 14:45:
Tot zo'n 25 gelijktijdige bezoekers (25 bezoekers die in dezelfde seconde een database call doen) kan MySQL het allemaal nog wel bolwerken. Daarna zakt de performance enorm in, je verliest wel tot zo'n 70%!@pgFrank
Ik ging er hierbij wel vanuit dat het aantal gebruikers van "slechts" 1000 niet zoveel uitmaakte, en ging niet uit van nog veel grotere systemen. -> Aan de naam van de comunity te zien zal het hoofdzakelijk voor mensen uit omgeving apeldoorn gebruikt worden,en niet door heel nederland (zoals hyves)
Volgens mij zou het dan niet zoveel uit maken of pgSQL of MySQL gebruikt wordt, maar wordt het verschil pas merkbaar bij veel grotere aantallen, of zit ik hier mis mee?
Ik ging er hierbij wel vanuit dat het aantal gebruikers van "slechts" 1000 niet zoveel uitmaakte, en ging niet uit van nog veel grotere systemen. -> Aan de naam van de comunity te zien zal het hoofdzakelijk voor mensen uit omgeving apeldoorn gebruikt worden,en niet door heel nederland (zoals hyves)
Volgens mij zou het dan niet zoveel uit maken of pgSQL of MySQL gebruikt wordt, maar wordt het verschil pas merkbaar bij veel grotere aantallen, of zit ik hier mis mee?
Maar, 25 bezoekers per seconde, is dat is wel redelijk wat. Hou echter wel in de gaten dat je met een slecht script misschien wel 10-20 queries voor 1 pagina nodig hebt. Dat zijn dan als 250 tot 500 queries om die 25 bezoekers van data te voorzien. Je voelt hem al aankomen, je hebt dan wel een dedicated (database-) server nodig om dit in goede banen te leiden.
Maar ik zou beginnen met normaliseren, de database vervolgens gaan opzetten, de queries inelkaar steken en daarna met de indexen aan de slag gaan. Het gebruik van de juiste indexen kan een query wel een factor 1000 versnellen. Zorg wel dat je een database hebt die echt wat met indexen kan doen, MySQL had hier tot versie 5 ook flink wat problemen mee. Versie 5 doet het al beter, maar ook hier is het weer pgSQL die de meeste mogelijkheden biedt.
Gewijzigd op 01/01/1970 01:00:00 door Frank -
Mee eens, pgSQL biedt meer mogelijkheden, maargoed daar is niet altijd de beschikking over (of de kennis voor). Ben benieuwd wat de TS met alle tips doet, zoals al blijkt is er echt ontzettend veel te verbeteren aan de oude opzet :)
Processor: ...
RAM: ...
Schijven: ...
En hoe groot (in Gb's) is jouw database?
Maar met 600 tot 1400 queries per seconde (van hoeveel gelijktijdige users?), zit je mogelijk al tegen het plafond van wat deze server aankan. Zie dit artikel voor een test die Tweakers heeft uitgevoerd op een goed uitgeruste database server. Let op: Deze test gaat over hun database en kan met jouw database (hele?) andere cijfers opleveren.
ToDo:
- normaliseren (heb je toch nodig)
- goede queries opstellen (dus nergens een * gebruiken!)
- opsporen van de knelpunten, de meest langzame queries.
- opstellen goede indexen (MySQL heeft dan zijn beperkingen, ook versie 5)
- optimaliseren van de serverinstellingen (zowel het OS als de database-configuratie)
- stored procedures kunnen wellicht helpen
- optimaliseren van de PHP-code
- etc.
Edit:
Met dank aan Yapf.net hier 2 voorbeelden van indexen waar je heel veel voordeel van zou kunnen hebben, maar die in MySQL niet beschikbaar zijn. Vooral een partial index lijkt mij handig, gezien het forum.
Quote:
5.20 Geen partial indexes (alle versies)
Geen partial-index support; de mogelijkheid om een index te maken op records met een bepaalde inhoud. Bv een aparte index voor elk jaar van de datum. Als je een query draait die alle gegevens voor een bepaald jaar moet ophalen dan heeft het geen zin om gegevens te lezen over de andere jaren. Met een partial index kun je een index maken die alleen de gewensde jaargang beschrijft zodat de rest niet eens wordt bekeken.
Geen partial-index support; de mogelijkheid om een index te maken op records met een bepaalde inhoud. Bv een aparte index voor elk jaar van de datum. Als je een query draait die alle gegevens voor een bepaald jaar moet ophalen dan heeft het geen zin om gegevens te lezen over de andere jaren. Met een partial index kun je een index maken die alleen de gewensde jaargang beschrijft zodat de rest niet eens wordt bekeken.
en
Quote:
5.22 Geen functionele indexes (alle versies)
Geen functionele indexes, de mogelijkheid om indexes te maken op bewerkingen van data, bv op een substring.
Bij MySQL kun je geen indexes gebruiken als je zoekt op bijvoorbeeld SUBSTR(filenaam FROM 10 FOR 3)
Dat betekent dat dit altijd een sequential scan zal opleveren.
Geen functionele indexes, de mogelijkheid om indexes te maken op bewerkingen van data, bv op een substring.
Bij MySQL kun je geen indexes gebruiken als je zoekt op bijvoorbeeld SUBSTR(filenaam FROM 10 FOR 3)
Dat betekent dat dit altijd een sequential scan zal opleveren.
Gewijzigd op 01/01/1970 01:00:00 door Frank -