schlechter Einfluss von mySQL Query Cache

Beim Optimieren unserer Webauftritte (Webseite, Shoplösungen usw) wurden neben Optimierungen am Webserver natürlich auch Optimierungen am mySQL Server durchgeführt. 

Wir hatten das Problem, daß immer mal wieder es zu plötzlichen Aussetzern des Webserver kam, deren Ursache in blockierten Abfragen  des mySQL Servers lagen. Hier war der State zwar auf „end“, dennoch lief die Abfrage bis zu 3-4 Minuten weiter. In dieser Zeit führte der Webserver keine weiteren Anfragen aus und alles hing.

Dieses Verhalten konnten wir an Testsystemen, bei denen wir entweder apache oder MySQL Lasttests durchführten nie wirklich reproduzieren und wir standen vor einem Rätsel.

Letztendlich lag die Lösung in einem zu hohen Query Cache. Nach dem Motto „Viel hilft viel“ lag der auf unseren Systemen (mit bis zu 64GB Ram) bei 2GB, weil wir davon ausgingen, daß diese Einstellung keine negativen Nebenwirkungen haben dürfte. Leider war das nicht der Fall. Eine genaue Erklärung dazu findet man bei http://www.docplanet.org/mysql/mysql-query-cache-in-depth/. MySQL braucht bei großen Cache relativ viel Zeit zum bearbeiten und sortieren der Cache Anfragen. Ein Schreiben (also Update, Insert usw) auf eine Tabelle führte dazu, daß diese  im Cache gelöscht wurde und dieses dauerte trotz sehr leistungsfähiger Hardware bis zu mehreren Minuten! In dieser Zeit fährt  MySQL keine Anfragen aus, die diese Tabelle betreffen und führte so letztendlich zu unseren Aussetzern.

Da wir bei unseren Testsystem trotz Lasttests nie eine echte Last erzeugen konnten wie es 20.000 Besucher am Tag erzielen und deshalb unsere Query Caches mehr oder minder leer waren, konnten wir dieses auch nie wirklich simulieren. Zumal nach dem erfolgten Schreibzugriff auf die Tabelle ja alles wieder in Ordnung schien.

Fazit: Query_cache auch bei sehr performanten System immer im kleinen Megabyte Bereich halten, sofern auf die zu cachenen Tabellen schreibende Zugriffe (Session Variablen, Änderungen usw) durchgeführt werden. In diesem Fall sind 32 MB bis 256MB vollkommen ausreichend, wir arbeiten aktuell mit 64MB und konnen einen ca. 20% schnelleren Seitenaufbau feststellen und keine mysteriösen Hänger mehr.

Als Alternative empfiehlt sich, den Query Cache so zu setzen, daß nur Abfragen mit expliziter „SELECT SQL_CACHE“ Angabe gecached werden. So kann man auf Anwendungsebene das sehr detailliert steuern. Siehe auch http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_query_cache_size