Hoge load door MySQL [Archief]

bami82

06/02/14, 02:53

Spyder01

06/02/14, 09:40

Je my.cnf is wel erg basic. Kijk eens met mysqltuner of deze suggesties heeft (MySQL moet daarvoor 24+ uur niet herstart zijn). Want zonder de omgeving verder te kennen is er zo te zien nog heel wat te optimaliseren.

chielsen

06/02/14, 10:33

Er wordt normaliter geadviseerd een thread_concurrency van 8 te gebruiken. Kijk ook eens naar:
http://dimitrik.free.fr/blog/archives/2010/11/mysql-performance-55-and-innodb-thread-concurrency.html

Verder zie ik in je my.cnf alleen INNODB dingen staan, maar tabel level locks komen normaliter alleen bij myisam voor. Loop eens na of je niet gemixed storage engines gebruikt.

-Edit Ik zie dat je ook memory engine gebruikt. Dat verklaart de locks dan inderdaad. Kijk ook eens of de maximale grootte niet wordt overschreven?

Verder is de MEMORY engine niet echt bedoeld voor veel writes:

Performance Characteristics

MEMORY performance is constrained by contention resulting from single-thread execution and table lock overhead when processing updates. This limits scalability when load increases, particularly for statement mixes that include writes.

Despite the in-memory processing for MEMORY tables, they are not necessarily faster than InnoDB tables on a busy server, for general-purpose queries, or under a read/write workload. In particular, the table locking involved with performing updates can slow down concurrent usage of MEMORY tables from multiple sessions.

Depending on the kinds of queries performed on a MEMORY table, you might create indexes as either the default hash data structure (for looking up single values based on a unique key), or a general-purpose B-tree data structure (for all kinds of queries involving equality, inequality, or range operators such as less than or greater than). The following sections illustrate the syntax for creating both kinds of indexes. A common performance issue is using the default hash indexes in workloads where B-tree indexes are more efficient.

http://dev.mysql.com/doc/refman/5.6/en/memory-storage-engine.html

bami82

06/02/14, 11:07

Bedankt voor de reacties tot dusver. Er zal zeker het een en ander veel beter te tunen zijn, ik ga intern op een test machine proberen de load te simuleren en dan is met mysqltuner kijken.

De memory tabellen lijken juist super snel, er staan niet veel records in trouwens dus hij zal niet uit de max grote lopen. Er zitten max 100 records in, wel veel updates en selects. Wij merken geen performance problemen alleen de hoge load average valt op waardoor ik me afvraag of er nou direct een probleem is of niet, omdat ik het niet volledig snap.

systemdeveloper

06/02/14, 12:12

Als jij ZIET dat 200 queries op een lock staan te wachten, dan héb je een performance/schalings probleem, of je dat nu zo ervaart of niet.
Normaal zie je een lock niet eens langskomen namelijk.
De grootte van de mem tabel maakt niet veel uit, het is de table lock die nodig is die ervoor zorgt dat het trager wordt.
Mogelijk ben je beter af door een temptable te maken ipv van die mem table en dat /tmp te mounten als een ramdisk. Dan heb je wel de ram based opslag voor de tabel, maar ben je waarschijnlijk van de locks af. (Tenzij dezelfde records worden gelocked natuurlijk).

bami82

06/02/14, 13:06

Als jij ZIET dat 200 queries op een lock staan te wachten, dan héb je een performance/schalings probleem, of je dat nu zo ervaart of niet.
Normaal zie je een lock niet eens langskomen namelijk.
De grootte van de mem tabel maakt niet veel uit, het is de table lock die nodig is die ervoor zorgt dat het trager wordt.
Mogelijk ben je beter af door een temptable te maken ipv van die mem table en dat /tmp te mounten als een ramdisk. Dan heb je wel de ram based opslag voor de tabel, maar ben je waarschijnlijk van de locks af. (Tenzij dezelfde records worden gelocked natuurlijk).

Okay bedankt voor die info, ik zal met development gaan praten, zal lastig worden :-(.

chielsen

06/02/14, 18:07

Geen idee hoe die queries zijn, maar misschien anders gewoon memcache gebruiken al dan niet vanuit mysql: https://dev.mysql.com/doc/refman/5.6/en/innodb-memcached.html

bami82

07/02/14, 02:23

Geen idee hoe die queries zijn, maar misschien anders gewoon memcache gebruiken al dan niet vanuit mysql: https://dev.mysql.com/doc/refman/5.6/en/innodb-memcached.html

Ik weet niet of dat goed gaat werken omdat de data dynamisch is en continue veranderd, het zijn eigenlijk alleen select en update queries die allemaal vanuit SPC's worden aangeroepen.

systemdeveloper

07/02/14, 12:31

Je kunt ook eens kijken naar een (kleine) ndb cluster setupje. Ook daar heb je het ram voordeel, maar zit je niet vast aan de table locks. Als je de tabel dan nog zonder logging aanmaakt heb je niet de overhead van de redo log (wat gewoon een killer is bij veel table io) maar wel gewoon rowlocking. Plus natuurlijk de mogelijkheid om te schalen naar zoveel servers als je wilt zodat je toch erg veel moeite moet gaan doen om die load boven de 50% te krijgen.

SF-Jeroen

07/02/14, 13:48

Huh? Waarom heeft iedereen het over TMP tabellen. Er staat nergens dat MySQL bezig is met het schrijven naar temporary tables. Daarnaast zit de server maar op 0.5% WA. Het lijkt me dus niet echt een i/o issue. Normaal gezien betekend "waiting for table level lock" gewoon dat je MyISAM tabellen gebruikt. MyISAM heeft alleen table level locking. De tabellen omzetten naar InnoDB (row locking) zou het dan moeten oplossen. Dan maak je ook beter gebruik van je 16GB innodb_buffer_pool. De CPU loopt op omdat er veel processen zijn, maar dat valt dus makkelijk op te lossen.

systemdeveloper

07/02/14, 13:58

Huh? Waarom heeft iedereen het over TMP tabellen. Er staat nergens dat MySQL bezig is met het schrijven naar temporary tables. Daarnaast zit de server maar op 0.5% WA. Het lijkt me dus niet echt een i/o issue. Normaal gezien betekend "waiting for table level lock" gewoon dat je MyISAM tabellen gebruikt. MyISAM heeft alleen table level locking. De tabellen omzetten naar InnoDB (row locking) zou het dan moeten oplossen. Dan maak je ook beter gebruik van je 16GB innodb_buffer_pool. De CPU loopt op omdat er veel processen zijn, maar dat valt dus makkelijk op te lossen.

Beter lezen wat ts zegt. Hij zegt dat de locks komen door zijn memory tables. Deze zijn per definitie volatile a.k.a. voor tijdelijke resultaten aangezien ze een server restart niet overleven.
Op memory tables heb je altijd een table lock.
Daarnaast worden locks hierop eerst vrijgegeven aan de write-queue, dus met veel writes op die tabellen, blijven je reads langer op de lock wachten wat een hogere load tot gevolg heeft.
Dit kun je natuurlijk oplossen door innodb te gebruiken voor die tabellen maar dan offer je redelijk wat performance op t.o.v. data in ram.

Edit: je kunt een (set van) 'persistent' tabellen gewoon moven naar een andere db en die db directory mounten op een tmpfs deel om de boel in ram te hebben. Dan gebruik je toch alleen ram terwijl je tabellen hebt die door meerdere connecties gebruikt kunnen worden. (Moet je wel even de ram db/tabellen aanmaken als je de server reboot maar dat kan met een eenvoudig start scriptje.

bami82

08/02/14, 16:49

Er was nu wel duidelijk een performance issue merkbaar. Merkwaardig genoeg heeft een mysql restart de load enorm verlaagd (factor 10). Kan het zijn dat het systeem gewoon out of memory is vanwege verkeerde tuning parameters?

Dit soort artikelen zijn ook erg intressant, ik vraag me af of dat het hele issue kan zijn:

http://blog.jcole.us/2010/09/28/mysql-swap-insanity-and-the-numa-architecture/
http://blog.jcole.us/2012/04/16/a-brief-update-on-numa-and-mysql/

bami82

12/02/14, 09:47

Inmiddels intern een test server opgetuigt en ik kan het probleem volledig simmuleren. Bij veel verkeer wordt mysql na 1.5 dag langszaam. Als ik het verkeer dan stop en opnieuw aanzet blijft hij langszaam, alsof een of andere buffer volzit of iets. Bij MySQL restart is het probleem direct weg.

Iemand enig idee wat dit zou kunnen zijn?