MySQL en threads die hangen op end-state

Dat MySQL zich soms eigenaardig gedraagt is geen verassing. De database server geniet als gevolg van zijn brede inzet blijkbaar een onuitputtelijk respect van de ‘community’ en lijkt weinig te lijden te hebben onder steeds serieuzer worden prestatie- en schaalbaarheid-problematiek.

Soms lijkt het alsof de MySQL het niet voor elkaar krijgt om volwassen te worden, iets dat misschien veranderd nu moeder bedrijf Sun is ingelijfd door Oracle. Een onderbouwing voor deze stelling blijkt uit een probleem waarmee ik en een aantal van mijn collegae de afgelopen weken mee hebben geworsteld.

De MySQL server waar de problemen in kwestie zich voor deden wordt intensief belast en gebruikt maar is hiervoor hadwarematig ook ter degen uitgerust. De server beschikt bijvoorbeeld over 16 GB geheugen, verschillende processors en zeer snelle SSD schijven.

Na enkele maanden zonder problemen in productie te hebben gedraaid begon de server in kwestie kuren te vertonen. De log-gegevens en analytische statistieken van de server vertoonde geen afwijkingen. De load was stabiel net zoals het geheugen gebruik en I/O gebruik. Na een diepere analyse kwam naar voren dat er threads bleven steken op de state ‘end’. De threads in kwestie waren altijd bezig met simpele UPDATE en INSERT queries op een variatie aan tabellen en databases. Echter zodra een thread in de state ‘end’ kwam kregen alle  andere threads die bewerkingen of selecties op tabellen en databases uitvoerden de status ‘locked’ . Gezien de intensieve belasting van de server zorgde dit er voor dat binnen een korte periode alle threads bezet waren, allen met de status ‘locked’.

Het zoeken van de oorzaak in zo’n geval is relatief complex aangezien het vaststellen van een eenduidige oorzaak niet voor de hand licht gezien de wisselende databases en tabellen waarop de problemen zich voordoen. Na het door lezen van de MySQL documentatie met betrekking tot General Thread States werd het in ieder geval duidelijker waarmee een thread bezig was tijdens de ‘end’ state.

Gezien het feit dat de server in kwestie een master servers in een replicatie proces sprong de activiteit ‘writing to binary log’ in het bijzonder in het oog. Was er dan toch een mogelijk probleem met de configuratie en / of opstelling van de SSD schijven? I/O prestaties waren echter dik in orde, daarbij wanneer een thread hing op end state was er geen enkele I/O activiteit waar te nemen. Misschien zat het dan toch in de cache van de schijven of  RAID Controller?

Cache was inderdaad de oorzaak van het probleem, echter niet de cache waar we in eerste instantie aan dachten. Een tweede activiteit die MySQL uitvoert gedurende de end-state van een thread is het bijwerken van de query-cache. Als gevolg van zeer diepgaande technische problemen, hier uitgelegd door Lutz Euler, heeft MySQL last van het bijhouden van (zeer) grote query-caches. Na het verkleinen van de query-cache van 4 GB naar 512 MB is het probleem als sneeuw voor de zon verdwenen. Meer achtergrond informatie vind je hier.

Ondanks dat de melding van de bug door de MySQL developers is gesloten doet het probleem zich in de meest recente versie nog steeds voor. Opmerkelijk want in een omgeving die veel van een server vraagt kan een degelijke query-cache het verschil maken!