[MySQL] Query performance vraag

[killingdjef]

Ik heb een InnoDB Table gemaakt in MySQL welke op een server draait. Binnen deze tabel heb ik verschillende kolommen waarvan 1 kolom een zogenaamde "last_sync" kolom is. Als een record wordt toegevoegd is last_sync gelijk aan het mysql commando "CURRENT_TIMESTAMP".

Afin, mijn client software wil dus een zoek query doen op last_sync om te kijken of alles wat hij heeft gesynchronizeerd is. Mijn query hiervoor is de volgende.

code:

SELECT * from city where last_sync > '1900-01-01 00:00:00' AND last_sync < 2009-01-22 00:00:00' limit [aangevuld door app],[aangevuld door app];

Zoals je ziet een vrij normale query waarbij opgemerkt moet worden dat de limit ingevuld wordt door de client. Dit gebeurt in stapjes van 5000 dus:

code:

limit 0, 5000
limit 5000,5000
limit 10000,5000

De tabel heeft ongeveer 300,000 records welk allemaal praktisch tegelijkertijd ingevuld zijn. De query bovenaan zal dus alle 300,000 records doorgeven. Het is logisch om een index op de last_sync te plaatsen, daar ik een where heb die alleen op last_sync filtered.

Een explain ziet er als volgt uit:

code:

+----+-------------+-------+-------+---------------+------------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------------+---------+------+--------+-------------+
| 1 | SIMPLE | city | range | sync_dates | sync_dates | 8 | NULL | 138456 | Using where |
+----+-------------+-------+-------+---------------+------------+---------+------+--------+-------------+

Wat is nu mijn probleem? Als mijn server de opgehaalde records streamt naar de client dan loopt de tijd die de query nodig heeft op. Het begint bij 120ms (limit 0, 5000) en eindigt bij 900 ms(limit 260000,5000). Waarom is dit zo?

Ten tweede, wanneer ik precies dezelfde query toepas en de index verwijder (!) dan begint hij op 30 ms en eindigt bij 500 ms. Waarom is de query zonder index op de table sneller?

Overigens, ik gebruik de limit omdat ik de resultset niet te groot wil hebben. Wanneer meerdere gebruikers met de client 300,000 records op halen (waarbij 300,000 zeker niet het maximale is) kan de server plat gaan. Hierom gebruik ik een limit. Ik nam echter onterrecht aan dat een limit gebruiken in een query het minder intensief maakt voor MySQL aangezien de query zonder limit evenveel tijd nodig heeft.

Samengevat mijn vragen
1) Waarom neemt de query executie tijd toe naarmate de limit op een verder punt in de resultset begint?
2) Waarom is mijn query in dit geval zonder index sneller?
3) Welke mogelijkheden heb ik op de snelheid te verbeteren en een kortere query executie tijd af te dwingen? De index heeft kennelijk gefaald...

Alvast bedankt!

[_JGC_]

Je hebt het hier over een resultset van tig records en zeer hoge limit. Het is redelijk normaal dat het binnenhalen van alle resultaten en hiervan de eerste honderdduizend weggooien langer duurt dan het ophalen van de eerste 100 resultaten en de rest te negeren.

Verder hangt de effectiviteit van een index enorm af van de data in je tabel en de manier waarop je die probeert uit te vragen. Soms is een table scan gewoon sneller dan het gebruiken van een index. In de voorbeeldquery die je geeft haal je een enorme resultset op, waarbij waarschijnlijk nagenoeg alle rijen worden opgehaald. Als je bijna alles ophaalt, is een table scan sneller dan een index. Omdat je bijna alles ophaalt, sleep je bijna de hele tabel toch al van de disk af, dan maakt het ook niet uit of je vantevoren controleert welk stukje je wel of niet moet hebben.

Verder is InnoDB enorm gevoelig voor optimalisaties in de databaseserver zelf. Hoe groot is die database en hoe groot is je innodb_buffer_pool_size (en hoeveel geheugen heeft je server)?

[Gomez12]

Tsja, een limit is geen hogere wiskunde ofzo.
Een limit 0,5000 stopt gewoon de query nadat hij 5000 resultaten heeft bereikt, niets meer en niets minder.
Een limit 5000,10000 stopt na het ophalen van 10.000 records en gooit ergens de 1e 5000 weg ( maar hij haalt ze in 1e instantie wel op, anders weet hij niet wat record 5000 is ), dit duurt dus langer.
Dus bij een limit loopt de tijd gewoon op, omdat hij steeds meer records moet ophalen.

Een index is een manier om sneller te zoeken, alleen introduceert het wel 1 kleine extra stap ( namelijk het zoeken in de index en daarna pas het resultaat ophalen ).
Normaal gesproken is via een index zoeken en daarna pas de resultaten ophalen vele malen efficienter dan 1 voor 1 langs alle complete records te lopen ( hij zoekt dan in een kleine georganiseerde subset ). Alleen als je toch langs alle records wilt lopen dan is het natuurlijk efficienter om het zonder index te doen, dat scheelt je die extra stap.

De index faalt opzich niet, voeg nog maar eens 30 miljoen records ( met last_sync data buiten de where ) aan het bestand toe en zorg ervoor dat het geheel redelijk random verdeeld wordt ( dus dat niet meer de 1e 5000 records in het bestand ook de 1e uitkomst zijn ), de tijd die het kost om via de index te zoeken zal niet navenant stijgen terwijl hij via een tablescan wel steeds langzamer gaat worden.

Het punt is voornamelijk dat je een gigantische resultset krijgt ( wat gewoon tijd kost ) gecombineerd met het feit dat je op dit moment gewoon bijna alle records wilt hebben ( waardoor een tablescan gewoon efficienter is dan wat dan ook ). Initieel is een tablescan uus gewoon sneller.

Wat ik persoonlijk zou adviseren is gewoon je situatie omdraaien ipv dat je altijd bijna alles wilt gaan ophalen ( >1-1-1900 and <currentdate ) houd je op de client gewoon een enkele last_sync date bij en vervang je die 1-1-1900 door die last_sync van de client, dan haal je bij 1e sync alsnog alle records op, maar de vervolg syncs maken wel gebruik van de indexen etc.

Zit je alleen nog met het praktische probleem dat een last_sync date op de client niet altijd hoeft te kloppen waardoor je inconsistenties kan krijgen, maar door de server de last_sync date te laten bepalen die de client opslaat en door deze ook op de server op te slaan krijg je het redelijk dicht, indien last_sync date van de client niet overeenkomt met last_sync date op de server ( voor die client ) dan weer initiele sync doen.

Je initiele syncs zullen ten allen tijde gewoon veel tijd kosten door de hoeveelheid data die opgehaald moet worden, alleen je vervolg syncs zullen wel sneller zijn

[stimpie79]

geen idee of dit in MySQL ook zo is, maar in DB2 is een BETWEEN(x,y) veel performanter dan > x AND < y,
misschien ook eens het proberen waard (los van het limit probleem dan)

[killingdjef]

_JGC_ schreef op donderdag 22 januari 2009 @ 23:51:
Verder is InnoDB enorm gevoelig voor optimalisaties in de databaseserver zelf. Hoe groot is die database en hoe groot is je innodb_buffer_pool_size (en hoeveel geheugen heeft je server)?

Ik kan hier nog geen antwoord opgeven (behalve dat het nu een vrij lege DB is op die 300K na). Maar ik heb wel dit artikel gevonden n.a.v. je post:
http://www.mysqlperforman...-innodb_buffer_pool_size/
Ik denk dat hier een hoop instaat waar jij naar suggereerd?

Gomez12 schreef op vrijdag 23 januari 2009 @ 00:26:
Tsja, een limit is geen hogere wiskunde ofzo.
Een limit 0,5000 stopt gewoon de query nadat hij 5000 resultaten heeft bereikt, niets meer en niets minder.
Een limit 5000,10000 stopt na het ophalen van 10.000 records en gooit ergens de 1e 5000 weg ( maar hij haalt ze in 1e instantie wel op, anders weet hij niet wat record 5000 is ), dit duurt dus langer.
Dus bij een limit loopt de tijd gewoon op, omdat hij steeds meer records moet ophalen.

Een index is een manier om sneller te zoeken, alleen introduceert het wel 1 kleine extra stap ( namelijk het zoeken in de index en daarna pas het resultaat ophalen ).
Normaal gesproken is via een index zoeken en daarna pas de resultaten ophalen vele malen efficienter dan 1 voor 1 langs alle complete records te lopen ( hij zoekt dan in een kleine georganiseerde subset ). Alleen als je toch langs alle records wilt lopen dan is het natuurlijk efficienter om het zonder index te doen, dat scheelt je die extra stap.

De index faalt opzich niet, voeg nog maar eens 30 miljoen records ( met last_sync data buiten de where ) aan het bestand toe en zorg ervoor dat het geheel redelijk random verdeeld wordt ( dus dat niet meer de 1e 5000 records in het bestand ook de 1e uitkomst zijn ), de tijd die het kost om via de index te zoeken zal niet navenant stijgen terwijl hij via een tablescan wel steeds langzamer gaat worden.

Het punt is voornamelijk dat je een gigantische resultset krijgt ( wat gewoon tijd kost ) gecombineerd met het feit dat je op dit moment gewoon bijna alle records wilt hebben ( waardoor een tablescan gewoon efficienter is dan wat dan ook ). Initieel is een tablescan uus gewoon sneller.

Ergens vermoedde ik dit al, alleen het feit dat een tablescan sneller kan zijn wist ik niet. Dankzij je goede uitleg is dat wel duidelijk geworden.

Wat ik persoonlijk zou adviseren is gewoon je situatie omdraaien ipv dat je altijd bijna alles wilt gaan ophalen ( >1-1-1900 and <currentdate ) houd je op de client gewoon een enkele last_sync date bij en vervang je die 1-1-1900 door die last_sync van de client, dan haal je bij 1e sync alsnog alle records op, maar de vervolg syncs maken wel gebruik van de indexen etc.

Zit je alleen nog met het praktische probleem dat een last_sync date op de client niet altijd hoeft te kloppen waardoor je inconsistenties kan krijgen, maar door de server de last_sync date te laten bepalen die de client opslaat en door deze ook op de server op te slaan krijg je het redelijk dicht, indien last_sync date van de client niet overeenkomt met last_sync date op de server ( voor die client ) dan weer initiele sync doen.

Je initiele syncs zullen ten allen tijde gewoon veel tijd kosten door de hoeveelheid data die opgehaald moet worden, alleen je vervolg syncs zullen wel sneller zijn

Dit is echt precies wat ik heb gedaan en wat het gaat doen.

stimpie79 schreef op vrijdag 23 januari 2009 @ 00:31:
geen idee of dit in MySQL ook zo is, maar in DB2 is een BETWEEN(x,y) veel performanter dan > x AND < y,
misschien ook eens het proberen waard (los van het limit probleem dan)

Getest en resultaten zijn hetzelfde. Blijkt ook dat MySQL een between gewoon omzet naar
"column < X and column > Y"

[zeroxcool]

Welk type heeft je last_sync kolom?

[killingdjef]

DATETIME

[_JGC_]

killingdjef schreef op vrijdag 23 januari 2009 @ 01:20:
[...]


Ik kan hier nog geen antwoord opgeven (behalve dat het nu een vrij lege DB is op die 300K na). Maar ik heb wel dit artikel gevonden n.a.v. je post:
http://www.mysqlperforman...-innodb_buffer_pool_size/
Ik denk dat hier een hoop instaat waar jij naar suggereerd?

Staan wel nuttige dingen in dat artikel ja. Verder is InnoDB tuning niet alleen het instellen van de juiste buffer_pool_size, maar komt er iets meer bij kijken. De grootste slag kan je echter met deze optie slaan, aangezien standaard deze optie ergens rond de 8MB ligt, wat extreem laag is voor een beetje database (zelfs met een database van 25MB merk je al dat 8MB buffer_pool_size traag is)