MySQL 'deadlock'

[Xandrios]

Hoi

Enkele weken geleden heb ik een aantal sites van een CentOS webserver met MySQL 4.1 verhuisd naar een Debian Etch machine met MySQL 5.0.23. De content van de sites is hetzelfde gebleven, en zo ook de database inhoud (Opgeslagen in MyISAM tables)

Sinds de 'verhuizing' heb ik het probleem dat, random, er een 'deadlock' ontstaat. Soms eens per week, soms twee keer per dag. Compleet onvoorspelbaar.

Het resultaat is dat je nog wel verbinding kunt maken met de database, een 'show processlist;' werkt ook nog, maar een query komt er niet door. Die blijft eeuwig hangen.

Het eerste punt is dat ik altijd meende dat MySQL alleen een enkele table lockte? En dus niet alle databases op de server?

Een aantal keer heb ik met 'show processlist' gekeken waar hij op hing. Hier zijn een tweetal voorbeelden. Het valt me op dat elke keer de 'probleem query' er een is van een standaard pakket: Coppermine gallery.

SQL:

| 5828 | gallery | localhost | gallery | Query   |  679 | Sorting result | SELECT * FROM cpg_pictures WHERE approved = 'YES'AND hits > 0 AND aid IN (1,2,3,5,6,7,8,9,10,11, | 
| 5836 | gallery | localhost | gallery | Query   |  675 | Locked         | UPDATE cpg_pictures SET hits=hits+1, lasthit_ip='xxxxxx', mtime=CURRENT_TIMESTAMP WHERE p | 

SQL:

| 5519 | admin   | localhost | gallery | Query   |  972 | Sending data | SELECT `user_id`, `user_name`, `user_group`, `user_profile1` , `user_group_list`
        FROM `cpg_user | 
| 5607 | gallery | localhost | gallery | Query   |  923 | Locked       | UPDATE `gallery`.cpg_users SET user_lastvisit = NOW() WHERE user_name = 'xxxx' AND BINARY | 

Het 'vreemde' is dat deze queries altijd goed hebben gewerkt op de oude installatie, MySQL 4.1. Ik vraag me dus ook een beetje af of het probleem daar wel in zit, en niet zozeer bij de MySQL installatie. Welke overigens een standaard apt-get verpakte versie is.

De logs laten absoluut niets zien overigens. Het enige dat ze tonen is het 'repairen' van tables nadat ik de server een kill -9 heb gegeven om 'm weer werkende te krijgen.

Heeft iemand tips om hoe dit probleem aan te pakken? Eventueel zou ik wel willen proberen MySQL 4.1 te installeren, maar gezien deze niet meer aanwezig is in de apt repository weet ik niet of dat zo'n goed idee is...

[zeroxcool]

Je zou het verschil in isolation level eens moeten bekijken van beide versies (in phpMyAdmin bij de 'variables' listing onder de noemer: tx isolation).

En in principe niet van de apt library uitgaan is niet erg. Zolang je maar een stable binary distribution pakt van MySQL.org (http://mysql.org/downloads/mysql/4.1.html).

Maar bekijk eerst eens of er een verschil in je isolation level is.

[ Voor 44% gewijzigd door zeroxcool op 13-07-2007 12:57 ]

[Xandrios]

Thanks

Helaas is de oude server niet meer in mijn bezit, ik kan dus niet controleren wat de waarde daar was. Tenzij het in een mysqldump wordt meegenomen, maar ik ga ervan uit dat dit niet het geval is?

De MySQL 5 install heeft deze waarde: REPEATABLE-READ

Qua config heb ik zowel een tweaked versie, alsmede de default my.cnf gebruikt oveirgens. Beide geven hetzelfde weer. Ik heb echter wel het idee dat het minder is geworden nu ik 'thread_cache_size' op 8 heb gezet.

Mischien gerelateerd, van het ene op het andere moment verbruikt mysql 100% cpu op 1 core. Het draait bijvoorbeeld na opstarten een paar uur normaal, lage load, en dan vanuit het niets gaat hij over op 100% load op 1 core. Die 100% load blijft daar ook als er geen/nauwelijks queries worden afgevuurd. Dit was op de vorige server ook al het geval. Ik heb de CPU power wel 'over' dus het is geen directe ramp, maar vreemd is het wel...

Als ik zelf MySQL 4.1 installeer, zal dat geen issues geven met alle packages waar het op depends? Ik meen me te herinneren dat MySQL 4.1 een aantal specifieke versies nodig heeft die op debian Etch al flink opgehoogd zijn..

[ Voor 15% gewijzigd door Xandrios op 13-07-2007 13:35 ]

[ph0t0nix]

Misschien kun je in een virutal machine (VMWare, QEMU, ...) je oude install weer tot leven wekken om zo uit te vinden wat het isolation level was.

[zeroxcool]

Xandrios schreef op vrijdag 13 juli 2007 @ 13:33:
Thanks

Helaas is de oude server niet meer in mijn bezit, ik kan dus niet controleren wat de waarde daar was. Tenzij het in een mysqldump wordt meegenomen, maar ik ga ervan uit dat dit niet het geval is?

De MySQL 5 install heeft deze waarde: REPEATABLE-READ

Dat is in ieder geval de standaard waarde, dat zou in principe geen problemen moeten geven.

Qua config heb ik zowel een tweaked versie, alsmede de default my.cnf gebruikt oveirgens. Beide geven hetzelfde weer. Ik heb echter wel het idee dat het minder is geworden nu ik 'thread_cache_size' op 8 heb gezet.

Dit zegt de doc over thread_cache_size:

How many threads we should keep in a cache for reuse. When a client disconnects, the client's threads are put in the cache if there aren't more than thread_cache_size threads from before. All new threads are first taken from the cache, and only when the cache is empty is a new thread created. This variable can be increased to improve performance if you have a lot of new connections. (Normally this doesn't give a notable performance improvement if you have a good thread implementation.) By examing the difference between the Connections and Threads_created you can see how efficient the current thread cache is for you.

Ik weet niet of je grote aantallen nieuwe connecties ontvangt, anders kun je dit sowieso wel omhoog zetten (threads_created / connections is goede indicatie lees ik)...

Mischien gerelateerd, van het ene op het andere moment verbruikt mysql 100% cpu op 1 core. Het draait bijvoorbeeld na opstarten een paar uur normaal, lage load, en dan vanuit het niets gaat hij over op 100% load op 1 core. Die 100% load blijft daar ook als er geen/nauwelijks queries worden afgevuurd. Dit was op de vorige server ook al het geval. Ik heb de CPU power wel 'over' dus het is geen directe ramp, maar vreemd is het wel...

Draaien er dan ook specifieke queries (grote joins oid)?

En wat voor een machine en kernel heb je draaien?

Als ik zelf MySQL 4.1 installeer, zal dat geen issues geven met alle packages waar het op depends? Ik meen me te herinneren dat MySQL 4.1 een aantal specifieke versies nodig heeft die op debian Etch al flink opgehoogd zijn..

Zorg sowieso dat de oude MySQL versie geremoved of gepurged is. Dan (bij wijze van spreke) gewoon dit doen:

code:

cd /usr/local
wget blabla.net/mysql-4.1.tgz
tar -zxvf mysql-4.1.tgz
ln -s mysql-4.1.x mysql

Ik kopiëer normaliter de datadir naar /var/mysql oid. Zodat je tijdens een upgrade enkel de symbolic link hoeft te vervangen. Zorg uiteraard ook voor een init script in /etc/init.d/mysql (zie daarvoor het filetje mysql.server ik denk uit de contrib dir).

[Xandrios]

zeroxcool schreef op vrijdag 13 juli 2007 @ 19:15:
Ik weet niet of je grote aantallen nieuwe connecties ontvangt, anders kun je dit sowieso wel omhoog zetten (threads_created / connections is goede indicatie lees ik)...

Max connections over de afgelopen 6 uur is 25. Threads_created is 118, en Threads_cache_hitrate_% is 99.93%. Met die waarden is 8 nog vrij ruim gezien je formule.

Draaien er dan ook specifieke queries (grote joins oid)?

Er worden wel enkele flinke joins gebruikt. Er zijn een aantal 'smerige' queries. Maar dat zou hoogstens tijdelijk de load mogen verhogen, en niet hoog mogen blijven als de query afgelopen is?

En wat voor een machine en kernel heb je draaien?

Pentium D 2.8 / 2MB cache per core / 1GB DDR en 'n SATA drive op een Intel mainboard.
$ uname -a
Linux raz1.xandrios.net 2.6.18-4-686 #1 SMP Wed May 9 23:03:12 UTC 2007 i686 GNU/Linux

Zorg sowieso dat de oude MySQL versie geremoved of gepurged is. Dan (bij wijze van spreke) gewoon dit doen:

code:

cd /usr/local
wget blabla.net/mysql-4.1.tgz
tar -zxvf mysql-4.1.tgz
ln -s mysql-4.1.x mysql

Ik kopiëer normaliter de datadir naar /var/mysql oid. Zodat je tijdens een upgrade enkel de symbolic link hoeft te vervangen. Zorg uiteraard ook voor een init script in /etc/init.d/mysql (zie daarvoor het filetje mysql.server ik denk uit de contrib dir).

Dat is wel een beetje risky, maar ik zou het inderdaad eens kunnen proberen. Wel eerst even op een testmachine, zou anders best wel veel downtime kunnen opleveren ben ik bang

[Xandrios]

Goed, ik heb het een-en-ander geprobeerd

MySQL heb ik gedowngrade naar 4.11. Tables waren niet compatible dus dmv een dump importeren heb ik de content overgezet. Je raad het al: Hetzelfde probleem.

Nu echter gebeurde het ook op een Forum tabel, namelijk de tabel van een Invision powerboard installatie. Invision is zeer veel gebruikt, en het kan dus eigenlijk niet daar aan liggen. En dan krijg je dit probleem:

code:

| 175585 | forums | localhost | forums | Query   |  1630 | Sending data | SELECT pid,topic_id FROM ibf_posts WHERE topic_id=5421 and queued=0 ORDER BY pid asc LIMIT 17310,30  | 
| 175790 | forums | localhost | forums | Query   |  1495 | update       | INSERT INTO ibf_posts (author_id,use_sig,use_emo,ip_address,post_date,icon_id,post,author_name,topic | 
| 176079 | forums | localhost | forums | Query   |  1311 | Locked       | INSERT INTO ibf_posts (author_id,use_sig,use_emo,ip_address,post_date,icon_id,post,author_name,topic | 
| 176127 | forums | localhost | forums | Query   |  1270 | Sending data | SELECT pid,topic_id FROM ibf_posts WHERE topic_id=5421 and queued=0 ORDER BY pid asc LIMIT 17310,30  | 
| 176196 | forums | localhost | forums | Query   |  1228 | Locked       | INSERT INTO ibf_posts (author_id,use_sig,use_emo,ip_address,post_date,icon_id,post,author_name,topic | 
| 176290 | forums | localhost | forums | Query   |  1170 | Locked       | INSERT INTO ibf_posts (author_id,use_sig,use_emo,ip_address,post_date,icon_id,post,author_name,topic |                                                                                               
| 176438 | forums | localhost | forums | Query   |  1102 | Locked       | INSERT INTO ibf_posts (author_id,use_sig,use_emo,ip_address,post_date,icon_id,post,author_name,topic | 
| 176569 | forums | localhost | forums | Query   |  1016 | Locked       | INSERT INTO ibf_posts (author_id,use_sig,use_emo,ip_address,post_date,icon_id,post,author_name,topic | 
| 176613 | forums | localhost | forums | Query   |   991 | Locked       | INSERT INTO ibf_posts (author_id,use_sig,use_emo,ip_address,post_date,icon_id,post,author_name,topic | 
| 176665 | forums | localhost | forums | Query   |   966 | Locked       | INSERT INTO ibf_posts (author_id,use_sig,use_emo,ip_address,post_date,icon_id,post,author_name,topic |

Een 'wachtrij' van updates. Het browsen van diezelfde tabellen gaat nog prima overigens.

Het meest vervelende in deze situatie is dat bovenstaande niet te detecteren is. Als SELECT's niet meer werken wordt ik direct gewaarschuwd aangezien er downtime is. Maar als alleen inserts niet werken is het een beetje een ander verhaal...

Heeft iemand een idee wat dit zou kunnen veroorzaken? Het gebeurd op mySQL 5.0 en 4.11. Gebeurd op verschillende tabellen, onder andere die van professionele forum software die gewoon goed moet werken.

Wat ik begin te verdenken is PHP<>webserver. Ik draai Lighttpd met PHP 4.4.7 in fast-cgi mode. Af en toe krijg ik meldingen dat een van de PHP backends afgestorven is en opnieuw gecreerd. Kan het zijn dat die, als dat tijdens een INSERT of UPDATE gebeurd de database connectie met lock laten staan?

Voor nu heb ik wait_timeout en interactive_timeout maar gedefinieerd en op 30 sec gezet. Nu *hoop* ik dat deze ervoor zorgen dat de hangende queries na de ingestelde periode worden afgekapt. Het zal me benieuwen...

[ Voor 3% gewijzigd door Xandrios op 25-07-2007 13:04 ]

[zeroxcool]

Heel rigoreus misschien... Maar zet locking eens uit. Voeg onder [mysqld] in je my.cnf 'skip-locking' toe. Kijk of het dan nog aanwezig is.

[Xandrios]

Heeft dat geen verdere gevolgen? 't klinkt gevaarlijk namelijk

[kmf]

skip-locking heeft niet echt veel gevolgen... aangezien het niet veel invloed heeft.

Dus... die threads locken omdat de queries niet al te efficient zijn. Je gebruikt zo te zien coppermine. Deez is nou niet het meest efficient (voor 1 page view worden tig queries gebruikt, en neemt exponentieel toe met het aantal albums/pics/categories dat je toont...)

Maar goed... table locking is altijd al een probleem geweest met MyISAM, en hoe groter je DB wordt en hoe meer hits je krijgt, hoe erger het wordt... Misschien dat je toch wilt stappen naar innoDB? Deze kan je op de tabellen pictures en albums zetten.

En als je dit wilt kunnen checken, kan je een scriptje schrijven om show processlist te pakken
mysql_query("SHOW FULL PROCESSLIST");

Dan kan je gewoon uitlezen en filteren op het aantal locked records. Dan heb je tenminste een idee als er wat mis gaat.