MySQL, anyone?
I need a little bit of help from a MySQL-savvy person, so please forgive me the technical mumbo-jumbo following below.
The database update which is necessary for the Antville 1.2 upgrade takes long. Very long. Too long. I started it yesterday at 1:30 in the night and it is still running.
Well, you might say, haven't you written the update script yourself? Aren't you the guy who should know why it's running so slowly?
But I am really pretty clueless right now. Because all I am doing is issueing quite simple select, update and insert statements.
The problem might be, though, that it's Millions of them. During the first few hours everything goes quite smoothly, but after that each bulk of inserts or updates to a table (especially the one containing all the lovely postings of the Antville.org community) is interrupted with a break of a few minutes at first which in the meantime even exceeds ten minutes.
Thus, the update is taking longer and longer and longer. And of course I don't want Antville.org to be down for such a long time.
I already tried different things I found on various websites and in the depths of the MySQL documentation, even with some success in speeding up things a little bit.
But it's still not working out...
So my urgent request is: if you know how to optimize MySQL performance pretty good or you know someone else who does, please please please get in touch with me as soon as possible.
seewolf
von Inserts bzw. Updates sollten Sie vorher alle Indexe, die nicht unbedingt gebraucht werden, löschen.
Zwar keine Erfahrung mit MySQL, aber sowas ist allgemein. Aber das wußten Sie sicherlich schon, ich wollts aber trotzdem gesagt haben, weil es genau danach klang.
tobi Verwaltung
Ich lege daher bei der problematischsten Tabelle schon gleich eine neue Tabelle ohne Index-Keys an, in der die Daten reinkopiert werden. Das hat die Sache zwar etwas beschleunigt, am Schluss warte ich trotzdem mehr als 10 Minuten zwischen zwei Update-Bulks (10.000 Records).
robert
not that i would like to apply for this job (i'm not more than an ambitious amateur in this area, and certainly there are people out there knowing much more), but i guess that upgrading to innodb would probably be a good move before doing the update. mainly because afaik innodb doesn't lock the whole table during inserts/updates/deletes (as myisam does). in addition iirc antville uses a "text" column in AV_TEXT, which might prevent mysql using a much faster in-memory table (more on this).
tobi Verwaltung
is a good idea, I guess – one I will try out for sure. However, shouldn't it be okay if the tables are locked during the update, ie. as long as there is no other thread trying to access the same table?
I will read into the "text" column thingy, maybe this could bring some improvements, too.
phoque
Was genau hast du denn vor? Kann man irgendwo dein derzeitiges Update-Skript/Query einsehen?
tobi Verwaltung
Hier der problematische Teil vom Update-Skript.
Zunächst wird eine neue Tabelle "content" ohne Index-Keys angelegt, in die mittels "insert into ... select" die Records überführt werden.
Zäh wird es dann ab Z. 336: der Platzhalter "#!content" wird mit folgendem Pseudo-Code ersetzt:
phoque
Ich verstehe zwar nicht ganz, was du da mit #AV_TEXT und #content usw. machst...
Aber kannst du nicht einfach die benötigten Spalten in die Tabelle einfügen und dann mit Werten füllen (und nicht eine neue Tabelle anlegen und alles kopieren)?
Mehrere tausend Zeilen kopieren dauert halt seine Zeit... :-)
tobi Verwaltung
Könnte man meinen, aber im Gegenteil: das Kopieren in eine neue Tabelle hat einen ziemlichen Geschwindigkeitsschub gebracht. Ist sozusagen eine frische Tabelle, ohne Altlasten.
Außerdem kann ich so von der einen Tabelle lesen und in die andere schreiben, was von Vorteil zu sein scheint, da andernfalls immer eine gewisse Latenz zu beobachten ist, bis MySQL die soeben beschriebene Tabelle wieder lesen kann.
Zudem hab ich nun einige Index-Keys während des Updates deaktiviert, damit die nicht zuviel Zeit in Anspruch nehmen.
Sehr geholfen hat dann auch das Schrauben an einigen MySQL-Settings.
Jetzt könnte ein komplettes Update in 2 bis 3 Stunden zu schaffen sein...
tobi Verwaltung
Die ersten 700.000 Records der Content-Table gingen wirklich flott, in dem Tempo wäre das Update sogar unter 2 Stunden durchgelaufen.
Leider wurde dann aber jedes Update zunehmend langsamer; um kurz nach 19 Uhr ging's los, und es ist immer noch am Laufen.
Derzeit liegen die Pausen zwischen zwei Updates von 5000 Records wieder bei unsäglichen 5 bis 10 Minuten... :(
robert
versuch doch mal das slow query log aufzudrehen (setz halt die minimalzeit auf 1 minute oder so), und lass dann das update laufen. vielleicht gibt das einen hinweis auf fehlende/falsch gesetzte indexes.
tobi Verwaltung
Mir ist dabei nur aufgefallen, dass die Select-Statements immer langsamer werden, je höher "offset" im statement wird:
select id, xml, metadata from content order by id limit 5000 offset 2300002609 millisselect id, xml, metadata from content order by id limit 5000 offset 1225000428386 millisDaher versuch ich gerade, ob ich einen Geschwindigkeitszuwachs erreichen kann, wenn ich den AV_TEXT-Table immer ohne Offset lese, und stattdessen jeden konvertierten Record dort lösche.
Dadurch benötigen die Updates zwar etwas länger, aber vielleicht macht sich der immer kleiner werdende AV_TEXT-Table bezahlt...
tobi Verwaltung
Beim Konvertieren der Topics zu Tags werden die Selects interessanterweise auch wieder schneller:
select topic, tag.id, content.id as tagged_id, modifier_id, creator_id, content.site_id from content, tag where topic is not null and topic = tag.name and tag.type = 'Story' limit 5000 offset 1355000272085 millisselect topic, tag.id, content.id as tagged_id, modifier_id, creator_id, content.site_id from content, tag where topic is not null and topic = tag.name and tag.type = 'Story' limit 5000 offset 13600004583 millisFaszinierend...
robert
du könntest noch probieren die records im content table nach id zu sortieren (siehe myisamck (optionen --sort-index und --sort-records, --analyze kann auch nicht schaden), das könnte beim offset/limit problem helfen. oder auch das update-script so umzuschreiben dass du dir immer die letzte behandelte id merkst und mit "where id > lastId order by id limit 5000" arbeitest.
tobi Verwaltung
...die womöglich zur endlich erlangten Geschwindigkeitssteigerung noch beitragen könnten.
Die letzten Änderungen (Records komplett auslesen, konvertieren, in neue Table schreiben und dann gleich im alten Table löschen) haben die Dauer des ganzen Vorgangs auf unter 4 Stunden reduziert.
Das Sortieren werd ich einfach noch vor der Konvertierung durchführen, kann ja nix schaden.
Was Deinen Vorschlag ("where id > lastId") angeht, so bin ich ziemlich sicher, dass es ein Fehler von mir war, auf "limit n offset m" umzusteigen.
Diese Variante benötigt wohl mehr Zeit; auch wenn das wohl nicht der einzige Grund war.