[Databases] Snelheid en Amount Records

[Zoolander]

Poste deze vraag net in een topic van 3 jaar oud, en toen in verkeerde forum. Gaat lekker. Maar dus, Hehe, dus maar even nieuwe topic aangemaakt.
Heb een MySQL tabel die de komende 3 jaar tot 200 a 300 duizend records gaat oplopen.
Nu verwacht ik geen problemen, maar kan nergens vinden hoeveel records je database kan hebben zonder dat je hele erge traagheid krijgt.

Database bestaat uit 12 tabellen, waarvan er 11 niet boven 1000 a 2000 records uitkomen. Eentje dus waar bijna ALLE data in gaat zitten.

Als ik de tabel goed indexeer, hoe lang doet MySQL en PHP er dan over denk je om alle records in te lezen. Tabel bevat 9 velden met max. 250 tekens in totaal. (alle velden opgeteld)

Weet iemand dit?

[justmental]

Qua volume is het aantal records peanuts voor een database, ik denk dat je eerder aandacht dient te gaan schenken aan wat voor handelingen/bewerkingen je met al die records gaat doen.
Als je alle records in gaat lezen dan heeft een index trouwens geen zin.

[GlowMouse]

In een andere post postte elevator:

Als je alle records in wil gaan lezen, dan hebben indexen volgens mij weinig zin meer. De performance gaat erg afhankelijk zijn van de server (CPU, disksystem, RAM die je aan je MySQL toekent).

Ik denk dat je het vrij gemakkelijk kan testen door gewoon zeg 900k aan records te creeeren, en hier je mogelijke queries op gaat draaien?

Het harde limiet van MySQL ligt overigens veel hoger (kijk naar GoT welke zo'n 11 miljoen posts bevat en ook MySQL draaien)

en Zoolander:

Laten we zeggen: 2600 Mhz, 1000MB RAM, 7200RPM HDD dedicated?

[whoami]

Sterker zelfs, indexen versnellen het ophalen van data, maar zorgen voor extra overhead bij het inserten/updaten van data. De indexen moeten dan immers aangepast worden.

[Zoolander]

Alle records inlezen zou enkel een test zijn om te kijken hoe mijn databaseje zich houdt onder de zwaarste query binnen de DB.
In de praktijk zullen er niet meer dan 100 records per keer worden opgehaald door max 3 personen tegelijk.

Alle tabellen zijn INNO DB btw.
Maar,hoef me dus geen zorgen te maken dat mijn ontwerp niet goed is ofzo.

hehe, mooi

[whoami]

Zoolander schreef op 02 november 2003 @ 22:11:

Maar,hoef me dus geen zorgen te maken dat mijn ontwerp niet goed is ofzo.

hehe, mooi

Ehm, toch wel. Het DB ontwerp is het belangrijkste van je database. Daar moet je zeker goed over nadenken. Als dat niet goed is, kan dat heel wat problemen veroorzaken.

[Zoolander]

ja, dat weet ik. Maar omdat in dit ontwerp 1 tabel mega groot wordt twijfelde ik.
Geen zorgen hoor, met normaliseren kwam ik hier toch echt op uit. (na een paar keer terugdraaien, praktische wijzignen, enzo9

Maar ben blij dat MySQL 300.000 records makkelijk aannkan als er per keer max. 500 records opgehaald worden.

[Zoolander]

Even terugkomend.
Ben toch nog een beetje ongerust.
Hoe lang duurt een query die alle 300.000 records of laten we voor het gemak: 750.000 queries moet inlezen?
Aan de hand van die info moet hij een grafiek in SVG maken.

Nog steeds: MySQL4, PHP4, 2600MHZ 1000MB 7200RPM dedicated.

Tabel is INNODB met 7 velden waarvan er een join wordt gedaan op een tabel met 2 die ongeveer even groot is als de tabel met 7 velden.

Kan namelijk nergens goede benchmark vinden waar ik die info vandaan kan halen en anders MOET ik of mijn applicaties in andere DB schrijven of ander ontwerp.

Maar laten we er maar even vanuit gaan dat het ontwerp OK is en DB ook.

Weet iemand dit of weet iemand waar ik dit soort snelheidsvragen kan vinden in een grote tabel ergen of het net?

DANK!

[gorgi_19]

Maak zelf een test? Er is al eerder wat verteld over het hoe en wat van MySQL; dit forum draait er op en er is al gezegd dat er tabellen zijn met 11 miljoen records. Verder zijn de statistieken na te kijken op de frontpage.

750.000 records is wel in te voegen in een database met dummy data.

En 750.000 queries 'ophalen' (of bedoel je uitvoeren?) kan hij wel even mee bezig zijn. 750.000 records ophalen duurt ook wel even om dat door te sturen en te verwerken.

[ Voor 99% gewijzigd door gorgi_19 op 23-11-2003 13:45 ]

[djc]

Zoolander schreef op 23 november 2003 @ 13:35:
Aan de hand van die info moet hij een grafiek in SVG maken.

Ik hoop voor je dat er vooral aggregate data in beeld moeten komen, anders gaat het renderen van die SVG ook nog redelijk wat tijd kosten.

[Varienaja]

Voor een database maakt het niet erg veel uit of je nu 100.000 of 10.000.000 records in een tabel hebt staan. (Zolang er maar gebruik gemaakt kan worden van indices).

Je moet je een telefoonboek voorstellen. Het zoeken naar 1 persoon in een telefoonboek met 10.000.000 nummers gaat niet noemenswaardig veel langzamer dan in een telefoonboek met 100.000 nummers.

[klinz]

whoami schreef op 02 november 2003 @ 22:06:
Sterker zelfs, indexen versnellen het ophalen van data, maar zorgen voor extra overhead bij het inserten/updaten van data. De indexen moeten dan immers aangepast worden.

Ook het inserteren van data kan sneller verlopen. Het controleren op duplicaten en foreign keys gaat immers sneller.

[Eelke Spaak]

Ik zou best een oordeel over je databaseontwerp willen vellen (waar je min of meer naar vraagt), maar dat wordt wat moeilijk op basis van alleen het aantal tabellen en het feit dat ééntje daarvan toevallig veel groter gaat worden dan de rest.

Kortom, als je je ontwerp beoordeeld wil hebben moet je even de structuur van alle tabellen (liefst met wat uitleg) hier posten.

[Zoolander]

Hmm, Vladimir: Zal ik doen als ik er niet meer uitkom.
Maar ik keek idd net bij zoeken in GOT en daar zie je: Zoek op het wordt ja:

"Er zijn ongeveer 167158 resultaten, verdeeld over 5572 pagina's, op deze pagina zijn 167128 niet weergegeven vanwege rechtenconflicten.
Er is gezocht op: ja
Woord frequenties: ja: 273541
Database van 811491 documenten doorzocht in 0,417s."

Dus dus 1.000.000 records doorzoeken lijkt me dan ook penuts: 4 of 5 seconden!
Displayen is alles wat we nodig hebben.

maar wat is Aggregate data? De SVG data is 1 woord + 1 int en dat 8 keer. Echter, per 1 int moeten ongeveer 150.000 int worden opgeteld.

Als GOT het kan met 11.000.000 moet ik met 750.000 wel aardig uit de voeten komen...... TOCH?

[raoulduke]

Aggregates zijn functies met een enkel resultaat op basis van meerdere tupels. Bijvoorbeeld SUM(), AVG() en COUNT().

[gorgi_19]

Misschien een idee dan om een 'sommatietabel' te gaan maken, met zulke hoeveelheden records, waarbij de waarden al opgeteld zijn?

[Zoolander]

Hmm, probeerde even met een for-lus 1.000.000 records in te voegen. DUURDE LANG. na 30 kapte ie ermee en had hij pas 650 records ge-add! OK, hoe kan dat nu weer? WAAROM ZO TRAAG!? SNIK SNIK

Fatal error: Maximum execution time of 30 seconds exceeded in E:\Projects\test.php on line 24

[gorgi_19]

Zoolander schreef op 23 november 2003 @ 21:16:
Hmm, probeerde even met een for-lus 1.000.000 records in te voegen. DUURDE LANG. na 30 kapte ie ermee en had hij pas 650 records ge-add! OK, hoe kan dat nu weer? WAAROM ZO TRAAG!? SNIK SNIK

Fatal error: Maximum execution time of 30 seconds exceeded in E:\Projects\test.php on line 24

Misschien beter om een .sql bestand te laten generen en dit in te laten voegen via een commandline tool?

[Zoolander]

maar waarom na 30 seconden al een timeout? En waarom doet ie 30 seconden over 500 inserts? Dat is toch wel een beeeetje traag? Had wat meer van MySQL verwacht. (Draai het overigens wel op 900Mhz Athlon met 500MB)

[klinz]

Zoolander schreef op 23 november 2003 @ 21:31:
maar waarom na 30 seconden al een timeout? En waarom doet ie 30 seconden over 500 inserts? Dat is toch wel een beeeetje traag? Had wat meer van MySQL verwacht. (Draai het overigens wel op 900Mhz Athlon met 500MB)

Dat kan alles met je tabeldefinitie te maken hebben. Post die eens hier (inclusief evt. indices).

[Zoolander]

Dit is mijn code:

code:

<?php

  // MySQL setup
  $host = "localhost";
  $user = "";
  $password = "";
  $table = "test";
  $database = "test";
  $connect = mysql_connect ($host, $user, $password);

  // PHP will parse queries to perform MySQL actions. 
  mysql_select_db ($database); 
  
  //$perform = mysql_query ("SELECT * from $table", $connect);
  
  // Connect to MySQL
  $connect;

  for ($i = 1; $i < 1000; $i++) {
  
      mysql_query ("INSERT INTO test values('snelheidinsec', 'mijnnaam', 'jouwnaam', '$i')");
      //$fetch = mysql_fetch_array ($perform);
      //print ("$fetch[3] $fetch[1] $fetch[2] $fetch[0] <br>");

      print "$i <br>";

  }

  // End of query, PHP will close connection.
  mysql_close ($connect);

?>

Ergens een fout?

[ Voor 42% gewijzigd door Zoolander op 23-11-2003 21:36 ]

[gorgi_19]

Zoolander schreef op 23 november 2003 @ 21:31:
maar waarom na 30 seconden al een timeout? En waarom doet ie 30 seconden over 500 inserts? Dat is toch wel een beeeetje traag? Had wat meer van MySQL verwacht. (Draai het overigens wel op 900Mhz Athlon met 500MB)

Kan aan van alles liggen; kan ook zijn dat PHP het niet leuk vindt, random records genereren zwaar is, etc.

[Zoolander]

En via mijn code? Zie je dan wat verkeerds?

[klinz]

Zoolander schreef op 23 november 2003 @ 21:34:
Dit is mijn code:
<snip code>
Ergens een fout?

Ennuh, primary key en indices? Waar zitten die op? Of heb je die helemaal niet?

[Zoolander]

Ze zijn allemaal pri-key en ook allemaal een index/ indices.
Trouwens,

Over een for-lus waarbij PHP int 1 t/m 1.000.000 moet printen onder elkaar

1
2
3
4
5
etc.

doet ie 3.5 minuut. Is dat ook wel normaal?

Ohw, en 500.000 records = helft heeft ie maar 40 seconden voor nodig. en
100.000 records maar 5. Hoe kan dat? Intern geheugen ofzo?

[ Voor 81% gewijzigd door Zoolander op 23-11-2003 21:54 ]

[ixi]

Zoolander schreef op 23 november 2003 @ 21:43:
Ze zijn allemaal pri-key en ook allemaal een index/ indices.
Trouwens,

Over een for-lus waarbij PHP int 1 t/m 1.000.000 moet printen onder elkaar
doet ie 3.5 minuut. Is dat ook wel normaal?

1 t/m 1.000.000 echo'en = ruim 10 MB aan karakters (incl de enters). Wellicht dat PHP moeite heeft met zoveel echo's, of dat je een trage verbinding met de server hebt. [edit: ik zie dat je print gebruikt, volgens mij werkt print sowieso trager dan echo, dus kan je beter echo gebruiken :)]

Is elk veld een primarykey, en heeft elk veld een index? Lijkt me niet echt een slim plan Is volgens mij ook niet mogelijk met het php script van je hierboven. Elk veld zou dan immers uniek moeten zijn, toch?

Die 30 seconde timeout is in te stellen in php.ini geloof ik. Zet deze op 0 voor oneindig. Is verder makkelijk op de php site te vinden.

[ Voor 29% gewijzigd door ixi op 24-11-2003 03:11 ]

[Zoolander]

Is een samengestelde sleutel van 4 keys. Maar dat maakt niet uit. Ik zie op het net benchmarks van lui die 150.000 records updaten in 4 seconden. Waarom doet ie van mij er maar 600 in 30?

Waar ik op zoek naar ben: Hoe werkt een databasequerie, welke criterea spelen er mee bij de speed van een query? En hoe leer ik in te schatten wanneer een query snel of niet is?

Een boek zou helpen!

Er zijn volgens mij maar weinig mensen die dit weten of niet?

[PrisonerOfPain]

Zoolander schreef op 24 november 2003 @ 10:42:
Een boek zou helpen!

Is de manual niks?

[whoami]

Zoolander schreef op 24 november 2003 @ 10:42:
Is een samengestelde sleutel van 4 keys. Maar dat maakt niet uit. Ik zie op het net benchmarks van lui die 150.000 records updaten in 4 seconden. Waarom doet ie van mij er maar 600 in 30?

De manier (volgorde) waarop die samengestelde sleutel is gemaakt kan er wel mee te maken hebben.
Stel, je hebt deze samengestelde sleutel (let op de volgorde):

code:

naam / voornaam / postcode

Als je dan een select doet , met een filter op 'naam', dan kan die samengestelde sleutel gebruikt worden. Ook als je een filter doet op 'naam' en 'voornaam', dan kan die sleutel ook gebruikt worden.
Echter, als je slechts een filter doet op voornaam en postcode, kan die samengestelde sleutel niet gebruikt worden, omdat je niet selecteert op naam.

Als je dus filtert op een bepaald veld (en je hebt een samengestelde query), dan moet je dus ook filteren op alle velden die links van dat veld liggen in die composite key, anders wordt je index niet gebruikt.

Waar ik op zoek naar ben: Hoe werkt een databasequerie, welke criterea spelen er mee bij de speed van een query? En hoe leer ik in te schatten wanneer een query snel of niet is?

Je kan altijd eens het executieplan van je query bekijken, en kijken of de indexen wel gebruikt worden, waar de bottlenecks zitten en wat je er kunt aan doen om die bottlenecks te verhelpen (statistieken vernieuwen, indexen bijmaken, bestaande indexen anders creeëren, ....)
Je moet er ook voor zorgen dat je index 'uniek' genoeg is, anders gaat de optimizer die index gewoon buiten beschouwing laten bij het bepalen van het optimale execution - plan.
Stel bv dat je een tabel hebt met 10.000 records, en dat je een index op een bepaald veld legt. Stel dat de verschillende waarden van dat veld binnen die tabel enkel 'A', 'B' en 'C' zijn.
Dan heb je bv 5000 records waarvoor de waarde van dat veld 'A' is, 3000 records met de waarde 'B' voor dat veld en 2000 records die 'C' hebben.
Die index is dus niet uniek genoeg, en zal hoogstwaarschijnlijk niet gebruikt worden.

[ Voor 25% gewijzigd door whoami op 24-11-2003 11:16 ]

[Zoolander]

Ik ben er uit.
Het ligt aan mijn PC.
Op school konden er in 30 seconden 70.000 records worden geinsert en bij een select met 2 where statements droeg hij er zo 150.000 records naar voren in 30 sec. En dat uiteraard in PHP.
PRIMA!

Nu, voor de lezers:
Laat je niet gek maken: MySQL trekt het wel!
Voor mensen die dit al wisten: nu weet ik het ook.

Dank voor alle reacties!

Nu nog tot de bodem uitzoeken hoe ze dat GOT forrum zo snel hebben gekregen dat die per seconde 200.000 records doorzoekt!

Groet

[whoami]

Zoolander schreef op 24 november 2003 @ 15:55:
Ik ben er uit.
Het ligt aan mijn PC.
Op school konden er in 30 seconden 70.000 records worden geinsert en bij een select met 2 where statements droeg hij er zo 150.000 records naar voren in 30 sec. En dat uiteraard in PHP.

Niet noodzakelijk. Misschien kan je je query wel zodanig optimalizeren dat ie er bij jou op school slechts 5 seconden overdoet.
Een snellere PC gebruiken is niet altijd een oplossing voor het probleem, eerder een verdoezeling van het probleem.

[snoopy]

Zoolander schreef op 24 november 2003 @ 15:55:
...

Nu nog tot de bodem uitzoeken hoe ze dat GOT forrum zo snel hebben gekregen dat die per seconde 200.000 records doorzoekt!

De zoekmachine van GoT (omega/xapian) werkt niet met een MySQL database, deze werkt uberhaupt niet met sql. Voor meer informatie hierover: Omega search manual