[MySQL] Thread blijft zeer lang hangen op end-state

[G F0rce 1]

Ik heb op één van mijn productie MySQL servers erg last van het volgende probleem en ben benieuwd of er tweakers zijn die mij kunnen helpen met een mogelijke oplossing.

Op de server in kwestie blijven sommige threads random hangen op status end. Wanneer dit gebeurd zijn de threads altijd bezig met een update of een delete query. Op het moment dat er een thread blijft steken op deze status krijgen alle threads die later nog queries proberen uit te voeren de status locked. Dit gebeurd ongeacht op welke database of tabel de overige threads een query proberen uit te voeren.

Het gevolg van dit probleem is dat binnen korte tijd alle beschikbare connecties verbruikt zijn en nieuwe connecties niet meer worden geaccepteerd tot het moment dat de query die op end-status staat verdwijnt. Soms kan dit wel langer dan 10 minuten duren. De load en I/O activiteit op de server tijdens het hangen van de thread zijn minimaal.

De database en/of tabellen waarop de hangende thread zijn bewerkingen uitvoert is puur willekeurig. De enige overeenkomst is dat het alle MyISAM tabellen zijn en dat op het moment dat het probleem zich voordoet deze tabellen vrij heftig worden bewerkt (circat 2 updates / inserts / deletes per seconde en dat een aantal uur achter elkaar). De tabellen zijn relatief bescheiden in afmeting, tussen de 2 en 20 MB.

Ter ondersteuning even wat gegevens over de server in kwestie. De server draait CentOS 5.4 en MySQL 5.0.77-log. De processor is een AMD Opteron 2378, de harde schijven zijn een RAID 1+0 array van Corsair X32 32GB SSDs.

Ik denk dat het laatste mogelijk te maken heeft met het probleem. Ik zou echter niet weten hoe ik dit precies zou kunnen vast stellen. Behalve dit probleem draait deze al geruime tijd snel en stabiel. De schijven lijken verder niet voor problemen te zorgen in deze configuratie.

Ik heb zo ongeveer overal gezocht maar niets anders dan een paar bug reports op mysql.com gevonden. In de documentatie van MySQL is de status ´end´ heel vluchtig gedocumenteerd. Ik ben op dit moment bezig de broncode van de versie in kwestie door te nemen om te kijken of dit me misschien op ideeën brengt.

Ik hoop dat er een hier iemand is die een idee heeft wat een mogelijke oorzaak is, want mijn inspiratie is zo ongeveer wel op . Alvast bedankt voor de moeite!

[ Voor 2% gewijzigd door G F0rce 1 op 30-08-2010 16:03 . Reden: typo's + extra informatie ]

[KabouterSuper]

Ik heb geen specifieke ervaring met MySQL, maar in Oracle worden dit soort problemen normaliter veroorzaakt door de harde schijven. Op het einde van een transactie werd de boel gecommit en gelogd, en op dat moment krijg je veel fysieke schrijfacties, en dus veel last van trage harde schijven. Nu weet ik dat MySQL anders omgaat met commits dan Oracle, maar wellicht worden er wel soortgelijke schrijfacties uitgevoerd.

[Voutloos]

G F0rce 1 schreef op maandag 30 augustus 2010 @ 15:56:
De enige overeenkomst is dat het alle MyISAM tabellen zijn en dat op het moment dat het probleem zich voordoet deze tabellen vrij heftig worden bewerkt (circat 2 updates / inserts / deletes per seconde en dat een aantal uur achter elkaar).

MyISAM heeft uberhaupt enkel table level locks, dus met veel updates/deletes wil je doorgaans InnoDB.

Sterker nog, tenzij je fulltext nodig hebt, zou InnoDB de default moeten zijn. Row level locks, transaction support, veel grotere kans op behoud data bij failures, foreign keys support etc etc. MyISAM is een oud en simpel gedrocht. Won het vroeger nog wel in suffe benchmarks, maar wint het nu veel minder vaak en bovendien is dat alles zelden doorslaggevend tov de zojuist genoemde voordelen van inno.

O en over 'relatief bescheiden': 20 MB is helemaal niets. Dat is een lege tabel.

[ Voor 17% gewijzigd door Voutloos op 30-08-2010 19:19 ]

[G F0rce 1]

Bedankt voor je reactie. Verhoudingsgewijs zal er in de tabellen die in gebruik zijn wanneer het probleem zich voordoet meer worden gelezen dan geschreven. Echter wanneer er wordt geschreven gebeurd dit zeer intensief. Ik begrijp je neiging naar InnoDB, ik ga in ieder geval proberen de tabellen waar de problemen zich het meest voordoen om te zetten naar deze moderne storage engine.

Toch vraag ik me af of de storage engine nog werk verricht wanneer een thread bij status end is aangekomen. Ook is het bijzonder dat alle queries locken, ook de queries op tabellen die reeds InnoDB gebruiken.

[frickY]

Pin me er niet op vast, maar volgens mij schrijft MySQL de indexen weg gedurende de End State;
Na elke INSERT/UPDATE moeten die worden bijgewerkt, wat een aardige hit kan geven. Alle SELECT zullen hier ook op locken.

Je kunt je queries herschrijven om INSERT DELAYED te gebruiken om te testen of dit het probleem verminderd of oplost. Het is in ieder geval iets eenvoudiger om te testen dan je tabellen naar InnoDb converteren.

[Creepy]

Aangezien we het hier over MySQL configuratie en installatie hebben even een kleine move naar Serversoftware en Windows Servers. Met SEA heeft dit topic weinig te maken.

[cariolive23]

G F0rce 1 schreef op maandag 30 augustus 2010 @ 15:56:
De enige overeenkomst is dat het alle MyISAM tabellen zijn en dat op het moment dat het probleem zich voordoet deze tabellen vrij heftig worden bewerkt (circat 2 updates / inserts / deletes per seconde en dat een aantal uur achter elkaar). De tabellen zijn relatief bescheiden in afmeting, tussen de 2 en 20 MB.

Heftig? De database staat vrijwel niets te doen, die paar queries per seconde, dat stelt echt 10x niks voor. Ik mag toch hopen dat je hiervoor niet de SSD's hebt aangeschaft, dat zou echt zonde van het geld zijn. Een enkele 15k toeren schijfje kan dit al verwerken. Met de SSD's zou je toch zeker een 1000 inserts/updates/deletes per seconden moeten kunnen verwerken, ietsjes meer dus.

MyISAM is wel een probleem, doet aan tablelocks. Maar wanneer je maar met één thread werkt, is dat geen probleem. Ik zou je wel aanraden om te kijken naar innoDB, kan een stuk beter werken. Ook omdat deze transactioneel is, daar valt ook nog performance winst mee te behalen. Voor zover je dat nodig hebt, jouw database doet nu al niets...

Ter ondersteuning even wat gegevens over de server in kwestie. De server draait CentOS 5.4 en MySQL 5.0.77-log. De processor is een AMD Opteron 2378, de harde schijven zijn een RAID 1+0 array van Corsair X32 32GB SSDs.

Versie 5.0 heeft al sinds eind vorige jaar geen active support meer. Ga upgraden naar 5.1, dan heb je nog tot eind dit jaar support. Daarna is ook dat over, maar dat is niet anders. Daarna is er niets meer, er is nog steeds geen nieuwere GA versie beschikbaar. Migreren naar een ander merk database is een mogelijkheid.

[G F0rce 1]

frickY schreef op maandag 30 augustus 2010 @ 19:42:
Pin me er niet op vast, maar volgens mij schrijft MySQL de indexen weg gedurende de End State;
Na elke INSERT/UPDATE moeten die worden bijgewerkt, wat een aardige hit kan geven. Alle SELECT zullen hier ook op locken.

Die theorie heb ik ook getest. Het vreemde is dus dat alles locked. Niet alleen de Theads die verband houden met de problematische query op end-state.

In reactie op cariolive23, maak je geen zorgen de server doet zo'n 400 queries per seconde met tijdens piek belasting wel 1500. De SSD's zijn weloverwogen asngeschaft echter niet specifiek voor de paar tabelletjes die nu voor hele grote problemen zorgen.

Zoals gezegd ga ik testen met InnoDB maar ik heb er een hard hoofd in dat het verband houd met de storage engine. Wat betreft de MySQL versie, dit is (nog steeds!) de meest recente versie in de standaard CentOS repositories. In het bug report waar ik in mijn TS naar link doet het probleem zich met recentere versies nog steeds voor. Echter een upgrade lijkt onvermijdelijk om uitsluitsel te geven, dat is duidelijk.

Waar ik me zo over verbaas is dat nergens gedocumenteerd staat wat MySQL doet wanneer een thread zich in state end bevind.

[ Voor 45% gewijzigd door G F0rce 1 op 30-08-2010 22:45 . Reden: Reactie op cariolive23 toegevoegd ]

[GlowMouse]

Kun je niet met oprofile aan de gang? Ik vermoed dat het met de query cache te maken heeft; die moet bij een update/delete deels geleegd worden en daar zit een global mutex op.