VPS, SQL Performance en Memory

[Concepts]

Om de herkomst van bezoekers nog beter te bepalen, heb ik voor de bezoekersteller op http://www.adpeople.nl een database aangekocht met 5 miljoen ip-locaties.

Probleem is nu echter dat ik de database nog niet kan implementeren wegens performance problemen met deze database.

De grootte is 484Mb, de index is op IP-range.

Het opvragen van een IP-adres duurt zo'n 20 seconden. Hak ik de database echter in stukjes van 50Mb, dan is een lookup in minder dan 50ms gedaan.

Ik zou de huidige methode van ip-locatie informatie van de bezoekersteller graag willen vervangen met deze database. Maar, wat valt er te doen om deze database in zijn geheel te kunnen doorzoeken binnen (max) 100ms?

Is dit een kwestie van het VPS geheugen uitbreiden naar 768Mb? Of levert een database met deze grootte altijd problemen op?

Wie heeft er tips?

PS; Ik heb de SQL instellingen in my.cnf al zoveel mogelijk getuned (query cache, table count, etc) maar dat mocht niet baten)

[Frijswijk01]

Staan je index'en goed in je tabel...

Maar hoe zoek je het op (query) en hoe ziet je tabel eruit?

[systemdeveloper]

Als een 10 keer zo grote database zulke (logaritmische) performance degradatie laat zien, kan dat vrijwel alleen door verkeerd index gebruik komen. Ik zou daar eens kijken om te beginnen.

[Concepts]

De index bestaat uit de IP-range. Veld-1 = ipFrom en veld-2= ipTo. Dit is een gezamelijke index. Het ip-adres is 'vertaald' naar een nummer.

De lookup is simpel:
SELECT country, city FROM ipLOCS WHERE ipFrom<=$iplookup AND ipTO>=$iplookup

Database en index is zoals geleverd door leverancier. Lookup query is zoals leverancier aangeeft.

Zonder de groter-dan en kleiner-dan vergelijking is de lookup inderdaad een kwestie van milliseconden, bijvoorbeeld, WHERE ipFrom=$iplookup.

Maar dat kan ik helaas niet gebruiken omdat een ip-adres altijd binnen een bepaalde range valt. En de range bepaalt weer de locatie (land, stad).

[systemdeveloper]

De index bestaat uit de IP-range. Veld-1 = ipFrom en veld-2= ipTo. Dit is een gezamelijke index. Het ip-adres is 'vertaald' naar een nummer.

De lookup is simpel:
SELECT country, city FROM ipLOCS WHERE ipFrom<=$iplookup AND ipTO>=$iplookup

Database en index is zoals geleverd door leverancier. Lookup query is zoals leverancier aangeeft.

Zonder de groter-dan en kleiner-dan vergelijking is de lookup inderdaad een kwestie van milliseconden, bijvoorbeeld, WHERE ipFrom=$iplookup.

Maar dat kan ik helaas niet gebruiken omdat een ip-adres altijd binnen een bepaalde range valt. En de range bepaalt weer de locatie (land, stad).

Hoe groot zijn de ranges waar je op zoekt? Zelfs als ze ter grootte van een b-class zijn, dan zal eens index van 64K keys nog geen 20 seconden hoeven te duren. Misschien kun je in je /etc/my.cnf nog wat meer geheugen vrijmaken voor mysql index space?

[Concepts]

Het zijn totaal bijna 5 miljoen records.

Wat is de parameter voor de index-space in MySQL? Zie die er niet bij staan in my.cnf. En op welke waarde moet deze tenminste staan?

[systemdeveloper]

Het zijn totaal bijna 5 miljoen records.

Wat is de parameter voor de index-space in MySQL? Zie die er niet bij staan in my.cnf. En op welke waarde moet deze tenminste staan?

Het aantal records is niet belangrijk. Wel de range waarop je zoekt. (min / max ip, aantal records daarbinnen dus). Dat gaat ie namelijk record voor record scannen en dat kost tijd.

Het piefje heet 'key_buffer' in my.cnf (voor isam dan). In principe zou die groot genoeg moeten zijn om al je gebruikte indexruimte in op te slaan.
Wat je ook eens kunt doen is de tabel optimaliseren/analysereb via phpmyadmin of de ssh prompt (beter).
Dit zal lang duren met 5M records op weinig geheugen. Het resultaat kan echter heel goed zijn. Het initeel laden van een groot gesorteerd bestand kan funest zijn voor de interne opbouw van je indexen. (Als je de indexen al op de tabel hebt aangemaakt, voordat ie gevuld werd).

Als je er niet uitkomt zou ik er iemand naar laten kijken aangezien het vaak puzzelwerk is dat via een forum zelden snel wordt opgelost.

Succes

[Concepts]

key_buffer staat op 128M, moet voldoende zijn, lijkt mij.

Ik ga het eens proberen met die optimize omdat het bestand inderdaad is ingeladen nadat de database met index was aangemaakt.

Vermoed toch zelf dat het te maken heeft met mijn toegewezen VPS memory en dat er geswapped wordt naar disk waardoor er 20 seconden I/O vertraging ontstaat. Database is immers bijna 500Mb.

[Concepts]

Nop, ook geen verschil met optimaliseren, etc.

[systemdeveloper]

Nop, ook geen verschil met optimaliseren, etc.

google even naar tuning_primer.sh en run het eens als je mysql een tijdje draait. Daar komt vaak nog wel wat leuke info uit zodat je wat beter in de juiste richting kunt zoeken

[xabin]

Hoeveel heb je er voor betaald?
Lijkt mij best interessant eigelijk.

[gjtje]

Een explain kan aangeven of er een index gebruikt wordt, echter wanneer het met een = wel snel is dan kan je dat zonder explain ook wel aangeven. Dat lijkt mij iets dat de leverancier maar moet oplossen.

Sowieso kan je het beste een limit 0,1 of top 1 meegeven aan een query wanneer je maar 1 resultaat verwacht.

[Apoc]

Is dit een kwestie van het VPS geheugen uitbreiden naar 768Mb? Of levert een database met deze grootte altijd problemen op?

Er zijn twee factoren die hiervoor het belangrijkste zijn:

- Disk I/O en access time. Dit is feitelijk de maximale lees en schrijf snelheid van de harde schijven. Als je bijvoorbeeld 15.000RPM SAS schijven of SSD gebruikt, zul je een lagere access time hebben dan met bijvoorbeeld 7.200RPM SATA schijven. Hoe lager de access time, hoe beter. En voor de disk I/O geldt; hoe hoger hoe beter. Om je een technisch verhaal te besparen: vooral de snelheid van de harde schijven zal in dit geval veel impact hebben op je queries. Het upgraden van je RAM alleen zal hoogstwaarschijnlijk geen noemenswaardige snelheidsverbetering opleveren.

- De efficiëntie van je queries. Zoals je zelf al aangaf, is de query veel sneller wanneer je de database opsplitst. Ik zou dit dan ook zeker doen. Je zou bijvoorbeeld de hele database kunnen opslitsen in aparte tabellen, gebaseerd op het eerste getal van het IP adres. Op deze manier zou je de database (theoretisch) opsplitsen in 256 delen, hetgeen de prestaties zeker ten goede zou moeten komen (en ik kan me ook niet echt een nadeel hiervan bedenken, afgezien van het feit dat het opsplitsen wellicht wat tijd kost, maar dat zou je door een scriptje kunnen laten doen natuurlijk).

Succes!

[wonko]

het nadeel is dat je ofwel meerdere database-instances moet draaien, of dat je database meer tabellen moet open houden. In het laatste geval weegt dit echter niet op tegen de voordelen.

Ga hier echter niet te ver in, denk aan het splitsen in 4 of zo om te beginnen.