Locking bei Schreibzugriffen, gibt es sowas und wozu

MySQL, PostgreSQL, SQLite
theomega
Userprojekt
Userprojekt
Posts: 704
Joined: 2003-01-27 14:36

Locking bei Schreibzugriffen, gibt es sowas und wozu

Post by theomega » 2006-05-10 15:37

Hallo Leute,
ist es möglich dass mein MySQL-Server beim lesenden Zugriff auf eine MyISAM-Tabelle per Select ein Lock erstellt und damit alle weiteren Schreib- und Lesezugriffe auf diese Tabelle verhindert. Wenn ja: wiso?

Hintergrund:
Mein Server hängt sich ständig weg: Ein simples Query (dauert normalerweise keine Zeit) dauert ewig (über eine Minute) um benatwortet zu werden und sollange wird die Tabelle gelockt und es sind keine weiteren Zugriffe möglich. Bis jetzt ist es mir nur bei Schreibzugriffen aufgefallen, jetzt aber auch bei lesendem Zugriff. Das erste Query hängt bei "Copying to tmp table" fest und alle anderen dann auf "Locked".

Wer kann mir helfen?

Danke
TO

User avatar
Joe User
Project Manager
Project Manager
Posts: 11604
Joined: 2003-02-27 01:00
Location: Hamburg

Re: Locking bei Schreibzugriffen, gibt es sowas und wozu

Post by Joe User » 2006-05-10 16:05

PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.

theomega
Userprojekt
Userprojekt
Posts: 704
Joined: 2003-01-27 14:36

Re: Locking bei Schreibzugriffen, gibt es sowas und wozu

Post by theomega » 2006-05-10 17:01

Danke, mist, heißt wohl er lockt auch beim schreiben. Hatte gehoft dadurch eine Erklärung für den Fehler zu finden. Die Tabellen sind teilweise ziemlich riesig (2 Millionen Zeilen), allerdings gehen die Queries normalerweise extrem schnell nur ganz plötzlich dauern die sehr lange und dann kommt es zu beschriebenen Probleme. Eine Minute später geht genau das Problem-Query wieder rasend schnell.

Gruß
TO

braindead
RSAC
Posts: 257
Joined: 2002-10-22 09:49
Location: vorm Rechner

Re: Locking bei Schreibzugriffen, gibt es sowas und wozu

Post by braindead » 2006-05-10 17:09

Warum stellst du net auf InnoDB um oder versuchs mal mit low-priority-updates.

theomega
Userprojekt
Userprojekt
Posts: 704
Joined: 2003-01-27 14:36

Re: Locking bei Schreibzugriffen, gibt es sowas und wozu

Post by theomega » 2006-05-10 17:12

Hy,
eine konvertierung auf Innodb ist leider nicht möglich weil ich die FULLTEXT-Suche im Moment noch brauche, ich bin schon dabei einen Ersatz auf die Beine zu stellen, das kann aber dauern.

Ich denke schon über die DELAYED Updates oder Inserts nach: Wie lange muss ein WRITE den warten wenn viel auf dem Server los ist und sehr oft ein READ auf die gleiche Tabelle kommt.

Gruß
TO

braindead
RSAC
Posts: 257
Joined: 2002-10-22 09:49
Location: vorm Rechner

Re: Locking bei Schreibzugriffen, gibt es sowas und wozu

Post by braindead » 2006-05-10 17:14

lies mal hier (http://dev.mysql.com/doc/refman/5.0/en/ ... cking.html) zu dem Thema, das sollte alles klären ;)

User avatar
isotopp
RSAC
Posts: 482
Joined: 2003-08-21 10:21
Location: Berlin

Re: Locking bei Schreibzugriffen, gibt es sowas und wozu

Post by isotopp » 2006-05-20 19:47

theomega wrote:ist es möglich dass mein MySQL-Server beim lesenden Zugriff auf eine MyISAM-Tabelle per Select ein Lock erstellt und damit alle weiteren Schreib- und Lesezugriffe auf diese Tabelle verhindert.
Nein. Es wird stattdessen ein Read-Lock (Shared-Lock) erstellt, das alle weiteren Schreibzugriffe für die Dauer des Select verhindert. Andere Lesezugriffe werden nicht verhindert.

Ein Insert, Delete, Update, oder Replace sowie jede DDL-Operation erstellt stattdessen ein Write-Lock (Exclusive-Lock), das alle anderen Writes und alle anderen Reads verhindert für die Dauer der Schreiboperation.

User avatar
isotopp
RSAC
Posts: 482
Joined: 2003-08-21 10:21
Location: Berlin

Re: Locking bei Schreibzugriffen, gibt es sowas und wozu

Post by isotopp » 2006-05-20 19:51

theomega wrote:Ich denke schon über die DELAYED Updates oder Inserts nach: Wie lange muss ein WRITE den warten wenn viel auf dem Server los ist und sehr oft ein READ auf die gleiche Tabelle kommt
Wieviele Queries pro Sekunde und wie viele Writes pro Sekunde hast Du eigentlich?


Man kann das leicht entzerren, indem man einen oder mehrere Replication Slaves einrichtet und seine Anwendungen Writes auf den Master machen läßt, die Reads aber gegen einen der Slaves fahren läßt.



Schreiboperationen haben eine geringere Priorität als Read-Operationen und warten so lange bis sie eine Lücke in den Reads finden. Erst wenn keine Read-Operation ein Lock haben will, gibt MyISAM dem schreibenden Thread ein Writelock. Während das Writelock besteht müssen auflaufende Read-Operationen und andere Schreiboperationen warten.

Wenn concurrent_inserts auf ON stehen und die MyISAM-Tabelle in SHOW TABLE STATUS LIKE "tabellenname" mit "Data_free: 0" gezeigt wird (und nur dann), finden Schreiboperationen am Ende der MYD-Datei der MyISAM-Tabelle statt und können lockfrei mit Leseoperationen überlappen.

theomega
Userprojekt
Userprojekt
Posts: 704
Joined: 2003-01-27 14:36

Re: Locking bei Schreibzugriffen, gibt es sowas und wozu

Post by theomega » 2006-05-20 20:59

Hallo,
danke für die Antwort. Ich habe das Problem mit einigen Tricks wohl wegbekommen, ich weiß aber nicht so genau wie. Allerdings glaube ich habe ich nur die Auswirkungen beseitigt, nicht das Problem. Denn mein Status-Log sagt mir:

Table_locks_immediate 39000000
Table_locks_waited 122000

die waited sind eigentlich doch ein bischen hoch. Ich komm aber nicht dahinter wo das Problem ist. Ich habe den Slow-Query-Log angemacht und festgestellt, dass zwar meine Suchfunktion total unperformant ist, aber die sollte doch nicht den ganzen Betrieb aufhalten, oder etwa doch? Es handelt sich dabei um eine SELECT-Funktion mit einem Fulltext-Index, die Antworten darauf dauern ca. 5 Sekunden. Ich halte das im Falle der Suche für akzeptabel, wenn es aber die ganze Seite in Mitleidenschaft zieht, dann stört es mich. Ich muss mal testen ob die waited auf einem greingen Wert bleiben wenn ich die Suche deaktiviere.

Ich habe auf den DB-Server ca. 100 Requests pro Sekunde. Davon sind (laut pma) 20% SELECT, 8% UPDATE, 3% INSERT Statements.

Evtl hast du mir noch einen Tipp! Ich weiß es ist schwer das ganze zu analysieren ohne die ganze eingesetzte Software zu kenne, aber evtl geht es auch ohne!

Danke auf jeden Fall
TO