[MySQL5] Timeout op query forceren

[JeRa]

In MySQL 5 is het mogelijk om diverse timeouts te definiëren, zoals wait_timeout en interactive_timeout. Deze bepalen onder andere of de verbinding tussen de client en de server wordt verbroken na een bepaald aantal seconden.

Ik heb een database voor een zoekmachine met vele miljoenen rows verspreid in een aantal tabellen waar goede (maar niet altijd voldoende) indices op staan. Door bepaalde paramaters voor de zoekmachine te kiezen is het mogelijk om queries héél lang te laten duren doordat de MySQL-server temporary tables gaat aanmaken. Dit is niet simpel op te lossen door andere of meer indices aan te maken.

Ik wil dat queries die langer dan bijvoorbeeld 15 seconden worden afgekapt, en dat in dit geval de juiste error code terug wordt gestuurd naar de client. Hoe doe ik dit? Een externe script die de processlist bijhoudt is geen optie, evenals een disconnect zonder dat de daadwerkelijke query wordt gestopt.

[smesjz]

Het enige wat ik me kan bedenken is locking maar dat kan natuurlijk andere problemen opleveren:

In mysqld_error is ERR_LOCK_WAIT_TIMEOUT gedefinieerd. Via get_lock() kan je een lock plaatsen met een eigen string met een timeout ->

GET_LOCK(str,timeout)

Tries to obtain a lock with a name given by the string str, using a timeout of timeout seconds. Returns 1 if the lock was obtained successfully, 0 if the attempt timed out (for example, because another client has previously locked the name), or NULL if an error occurred (such as running out of memory or the thread was killed with mysqladmin kill). If you have a lock obtained with GET_LOCK(), it is released when you execute RELEASE_LOCK(), execute a new GET_LOCK(), or your connection terminates (either normally or abnormally). Locks obtained with GET_LOCK() do not interact with transactions. That is, committing a transaction does not release any such locks obtained during the transaction.

Een QUERY_INTERRUPTED kan je zo te zien ook niet zelf forceren en zelfs met een stored procedure heb je hetzelfde probleem omdat die ook geen limiet kent zo te zien.

Mooiste zou zijn als je in PHP een thread kan aanmaken en die query daarin uit te voeren, maar helaas...

Bij het toevoegen van privileges zou je normaal het limiteren van een query verwachten, maar de mysql manual geeft aan dat je alleen queries per uur kan beperken: http://mysql.com/doc/refman/5.0/en/user-resources.html

Server-side limiteren lijkt dus niet te mogelijk te zijn.

[JeRa]

Zelfs al zou het in PHP mogelijk zijn om threads te creëeren (wat lastig gaat, vanwege het niet thread-safe zijn van de libraries) dan is het nog niet gemakkelijk om de juiste query uit te schakelen. Dat doe je namelijk door 'KILL QUERY #id' te gebruiken, waar #id de unieke identifier van de query is. Die moet je achterhalen door SHOW PROCESSLIST te gebruiken, maar het is vrij lastig om in dat geval de goede query te achterhalen in het geval dat er twee queries tegelijkertijd draaien. Ook met eigen unieke identifiers in comments in de query plaatsen werkt niet, omdat MySQL5 deze vantevoren stript.

GET_LOCK() lijkt niet de oplossing omdat ik dan nooit twee queries tegelijkertijd kan draaien? Of wel, maar dan heb ik alsnog een extern proces nodig dat de locks opvraagt.

Misschien heeft iemand nog een idee voor MySQL, maar ik had al langer het idee om over te stappen op een andere DBMS zoals PostgreSQL in verband met performance redenen (MySQL faalt erg vaak in het optimaliseren van queries waardoor ik genoodzaakt wordt lelijke queries te schrijven) - weet iemand een goede DBMS die query interruption/timeout goed ondersteunt?

[smesjz]

JeRa schreef op zondag 20 augustus 2006 @ 19:58:
Zelfs al zou het in PHP mogelijk zijn om threads te creëeren (wat lastig gaat, vanwege het niet thread-safe zijn van de libraries) dan is het nog niet gemakkelijk om de juiste query uit te schakelen. Dat doe je namelijk door 'KILL QUERY #id' te gebruiken, waar #id de unieke identifier van de query is. Die moet je achterhalen door SHOW PROCESSLIST te gebruiken, maar het is vrij lastig om in dat geval de goede query te achterhalen in het geval dat er twee queries tegelijkertijd draaien. Ook met eigen unieke identifiers in comments in de query plaatsen werkt niet, omdat MySQL5 deze vantevoren stript.

De manual zegt dit:

'KILL QUERY terminates the statement that the connection is currently executing, but leaves the connection itself intact.'

Je hebt dus geen query id nodig omdat het de huidige query killt.

GET_LOCK() lijkt niet de oplossing omdat ik dan nooit twee queries tegelijkertijd kan draaien? Of wel, maar dan heb ik alsnog een extern proces nodig dat de locks opvraagt.

Een lock krijgt zijn eigen unieke naam geloof ik. Althans, get_lock() krijgt gewoon een string als parameter mee en dat is geen probleem.

Dus als je je locks maar unieke namen geeft, krijg je een locking timeout als die lock na bijv. 15 seconden nog niet is vrij gegeven. Natuurlijk ruim je zelf locks op via release_lock.

[JeRa]

smesjz schreef op zondag 20 augustus 2006 @ 20:56:
[...]


De manual zegt dit:

'KILL QUERY terminates the statement that the connection is currently executing, but leaves the connection itself intact.'

Je hebt dus geen query id nodig omdat het de huidige query killt.

Ah juist, dat zou dus heel erg handig kunnen zijn - maar hoe moet ik dan een KILL QUERY uitvoeren als diezelfde connection geblocked is vanwege de query zelf?

[...]

Een lock krijgt zijn eigen unieke naam geloof ik. Althans, get_lock() krijgt gewoon een string als parameter mee en dat is geen probleem.

Dus als je je locks maar unieke namen geeft, krijg je een locking timeout als die lock na bijv. 15 seconden nog niet is vrij gegeven. Natuurlijk ruim je zelf locks op via release_lock.

Dat snap ik wel, maar een lock zorgt er toch niet voor dat een query wordt geterminated? Ik quote van dev.mysql.com:

Tries to obtain a lock with a name given by the string str, using a timeout of timeout seconds. Returns 1 if the lock was obtained successfully, 0 if the attempt timed out (for example, because another client has previously locked the name)
[...]
Note: If a client attempts to acquire a lock that is already held by another client, it blocks according to the timeout argument.

Dus die timeout slaat op het verkrijgen van de lock, en niet de duratie van de lock

[smesjz]

JeRa schreef op zondag 20 augustus 2006 @ 21:22:
[...]

Ah juist, dat zou dus heel erg handig kunnen zijn - maar hoe moet ik dan een KILL QUERY uitvoeren als diezelfde connection geblocked is vanwege de query zelf?

Dat gaat dus niet lukken zonder een extra thread die .e.e.a in de gaten houdt. Of je zou iets als sqlrelay er tussen moeten hangen. Maar dat is wel erg omslachtig.


Dat snap ik wel, maar een lock zorgt er toch niet voor dat een query wordt geterminated? Ik quote van dev.mysql.com:

[...]

Dus die timeout slaat op het verkrijgen van de lock, en niet de duratie van de lock :)[/quote]

My bad, iets te enthousiast er over heen gelezen denk ik. Ik dacht dat die timeout betrekking had op lock periode en niet op de periode van aanvraag tot verkrijgen.

Kortom: het gaat niet lukken. Misschien dat je in de source er iets over kan vinden? Mogelik zit er ergens een harde limiet in of word je wat wijzer uit de source code.

[JeRa]

Ik ga het doen door middel van twee scriptjes

Script 1: krijgt een request voor een query door en start 'script 2' en houdt dit proces in de gaten
Script 2: voert de query uit, en slaat het resultaat op in een resultatendatabase
Script 1: indien na 15 seconden script 2 nog niet klaar is, wordt de bijbehorende query gezocht en geterminated
Script 1: als script 2 wél klaar is binnen 15 seconden, haalt ie de resultaten op uit de database, stuurt ze terug naar de gebruiker en verwijdert de resultaten vervolgens weer uit de database

Nog belangrijke op- of aanmerkingen? Hartelijk dank alvast