MySQL DB-Tabellen mittels PHP-Funktion automatisch optimieren

[xchs]

Hallo,

würde es aus Eurer Sicht Sinn machen, in der MySQL-Datenbank die TL-Tabellen (mit Überhang) von Zeit zu Zeit optimieren zu lassen (so wie das auch im phpMyAdmin mit der entsprechenden Funktion möglich ist).
Ich denke dabei an eine PHP-Funktion (z.B. ähnlich wie hier beschrieben: http://www.selfphp.de/code_snippets/...pet.php?id=109 ), welche dann beispielsweise automatisch per TL-Cron-Job (Weekly) ausgeführt werden könnte.
Oder ist so was ähnliches schon implementiert und ich habe es nur noch nicht gesehen?

[xtra]

Siehe hierzu: http://dev.mysql.com/doc/refman/5.1/...ize-table.html

In most setups, you need not run OPTIMIZE TABLE at all. Even if you do a lot of updates to variable-length rows, it is not likely that you need to do this more than once a week or month and only on certain tables.

OPTIMIZE TABLE works only for MyISAM, InnoDB, and ARCHIVE tables. It does not work for tables created using any other storage engine, including NDBCLUSTER Disk Data tables.

Von daher ist es meiner Meinung nach nicht notwendig.

Ich finde viel eher, dass man die Exceptions/Errors von Tabellen bei corruption abfangen sollte und ggf. einen Optimize table hinterherschmeissen und anschliessend das Query wiederholen sollte.

Ansonsten um Ontopic zu schliessen, wenn OPTIMIZE, dann maximal einmal pro Monat per cron oder aber gar nur aus dem BE heraus.

[xchs]

Hallo xtra,

weißt Du vielleicht, ob diese Optimierung der DB-Tabellen auch hinsichtlich Overall-Performance der DB etwas bringt? Wenn ich beispielsweise diese Tabellen-Optimierung händisch im PMA mache, dann ist die DB fast immer einige 100 kB kleiner - ob dabei auch eine bessere Performance erzielt werden kann, weiß ich leider nicht. Auf jeden Fall wird die DB kompakter und es gibt keinen Tabellen-Überhänge, was ja auch nicht schlecht ist.
Ob diese Optimierung dann durch den Monthly- oder den Weekly-Cron-Job ausgeführt wird, wäre mir im Prinzip egal.

[xtra]

Hinsichtlich der Performance sollte sich eigentlich kaum etwas drehen. Es sei denn du machst viele grosse Loesch Operationen wodurch Loecher in der DB entstehen koennen.
Sobald du wieder Daten in die DB eintraegst, werden diese Loecher wieder "gestopft", sofern die Daten hineinpassen (Stichwort BLOB/TEXT mit variabler Laenge).
Es verhaelt sich hierbei also recht analog zur Fragmentierung einer normalen Festplatte.
sobald eine Tabelle keine Blobs bzw. Text Felder enthaelt duerfte die Performance und auch die Dateigroesse keinen Unterschied machen.
Sollte sie solche Felder beinhalten und(!) du hast extrem viele Daten von extrem variabler Groesse, sprich variabel in BLOB-Segmenten (ich glaube bei MySQL war ein Segment in BLOBS 64kb), dann koennte es sein, dass MySQL den fragmentierten jedoch freien Platz in der Tabelle nicht wiederverwenden kann und er so lange frei bleibt, bis du einen Datensatz einfuegen willst, der in diesen Bereich hinein passt.

Abschliessend nach dieser Theorie: Ich glaube nicht dass du dadurch einen merklichen Vorteil erhalten wirst. Ausser oben genannte Szenarien treffen bei dir sehr haeufig zu.

Zu beachten ist noch, dass die Table, je nach DBMS, waehrend der Dauer eines OPTIMIZE entweder gelockt ist (andere Zugriffe werden solange angehalten, also dein Seitenbesucher sieht solange die Tabelle optimiert wird nichts bzw. wartet auf die Seite was fuer einen Webshopbetreiber z.B. inakzeptabel ist da es bei grossen Tabellen von mehreren Mio Datensaetzen doch recht lange dauern kann bis diese optimiert sind) oder aber der OPTIMIZE wird beim ersten konkurrierenden Zugriff abgebrochen, was den Einbau eines solchigen autmatischen Aufrufs per se ad absurdum fuehrt.

Gruss
Chris

[xchs]

Hallo Chris,

danke für die aufschlussreichen Erklärungen.

Abschliessend nach dieser Theorie: Ich glaube nicht dass du dadurch einen merklichen Vorteil erhalten wirst. Ausser oben genannte Szenarien treffen bei dir sehr haeufig zu.

Zu beachten ist noch, dass die Table, je nach DBMS, waehrend der Dauer eines OPTIMIZE entweder gelockt ist (andere Zugriffe werden solange angehalten, also dein Seitenbesucher sieht solange die Tabelle optimiert wird nichts bzw. wartet auf die Seite was fuer einen Webshopbetreiber z.B. inakzeptabel ist da es bei grossen Tabellen von mehreren Mio Datensaetzen doch recht lange dauern kann bis diese optimiert sind) oder aber der OPTIMIZE wird beim ersten konkurrierenden Zugriff abgebrochen, was den Einbau eines solchigen autmatischen Aufrufs per se ad absurdum fuehrt.

Verstehe ich Dich dann richtig, dass Du dieses "Feature" eher nicht in den Core einbauen würdest?

Dass während der Tabellen-Optimierung die betreffende DB-Tabelle für andere Zugriffe gesperrt wird, habe ich in Deinem geposteten Link zum MySQL Reference Manual gesehen - ist natürlich etwas blöd, v.a. für stark frequentierte Seiten (trifft bei mir aber eher nicht zu ). Was ich bei meinen Installationen aber beobachten konnte, werden die entsprechenden Cron-Jobs beim ersten Seitenaufruf des betreffenden Tages angestoßen. Bei meinen Seiten also irgendwann nach Mitternacht und laut Serverlog sind das hauptsächlich irgendwelche, sagen wir mal "ungeliebte" Bots; bei denen wäre es mir dann eigentlich egal, wenn diese etwas länger warten müssten...

[BugBuster]

Bei meinen Seiten also irgendwann nach Mitternacht und laut Serverlog sind das hauptsächlich irgendwelche, sagen wir mal "ungeliebte" Bots; bei denen wäre es mir dann eigentlich egal, wenn diese etwas länger warten müssten...

Hmm, bloß blöd wenn dann die Suchmaschinen "Page not found" als Seiteninhalt speichern, wenn es zu timeouts kommt. (oder ähnliche Exception Meldungen)
Ich bin bei solchen Systemeingriffen, als solches sehe ich das, lieber live dabei, falls was schief geht. (Was in meiner DB Tätigkeiten schon vorgekommen ist)

[xtra]

Verstehe ich Dich dann richtig, dass Du dieses "Feature" eher nicht in den Core einbauen würdest?

Auf jeden Fall nicht als Cronjob, im aeussersten Falle als separaten Punkt in der Systemwartung. Unter anderem auch wegen den Gruenden die Bugbuster angesprochen hat. Ich will sowas, was irgendwie auch schief laufen kann, lieber von Hand anstossen anstatt es optimistisch dem System zu yberlassen, wann es sich pflegen will.

Was ich bei meinen Installationen aber beobachten konnte, werden die entsprechenden Cron-Jobs beim ersten Seitenaufruf des betreffenden Tages angestoßen. Bei meinen Seiten also irgendwann nach Mitternacht und laut Serverlog sind das hauptsächlich irgendwelche, sagen wir mal "ungeliebte" Bots; bei denen wäre es mir dann eigentlich egal, wenn diese etwas länger warten müssten...

Genau diese Bots moechte ich aber sehr schnell und recht zuvorkommend bedienen. Je schneller die meine Seite indizieren koennen, desto geringer ist die Wahrscheinlichkeit, dass sie sich entscheiden die Seite sei yberlastet und brechen dadurch dann die Indizierung ab bzw. verschieben dieselbige auf den naechsten Tag, wo dasselbe Problem wieder zum tragen kommt.

Nicht ausser Acht lassen darf man hierbei auch die Wahrscheinlichkeit, dass manche Suchmaschinen die images yberhaupt nicht herunterladen (cron.php liefert ein 1x1 pixel bild) und somit den cronjpb nicht anstossen. Hierdurch waeren doch wieder deine Kunden betroffen bzw. der erste Seitenbesucher des Tages.

Dies nun in Relation gesetzt zur Wahrscheinlichkeit einer Fragmentierung der Tabellen (Zur Erinnerung, diese tritt nur durch haeufige Loeschvorgaenge und anschliessende Inserts von abweichender Zeilengroesse auf) duerfte der Gewinn gegen Null konvergieren, die Wartezeit des Seitenbesuchers jedoch mit wachsender Datenbankgroesse linear ansteigen.

Dies bin ich in keinster Weise bereit zu akzeptieren.

Von daher:
Als Extension in der Systemwartung: Ja, kann man machen
Per Cronjob: Definitiv nein, das sollte man nicht tun.

Gruss
Chris

[xchs]

Hallo Chris,

Genau diese Bots moechte ich aber sehr schnell und recht zuvorkommend bedienen. Je schneller die meine Seite indizieren koennen, desto geringer ist die Wahrscheinlichkeit, dass sie sich entscheiden die Seite sei yberlastet und brechen dadurch dann die Indizierung ab bzw. verschieben dieselbige auf den naechsten Tag, wo dasselbe Problem wieder zum tragen kommt.

Ich hab mich dabei eigentlich explizit nicht auf die Suchmaschinen-Robots bezogen, sondern auf die "ungeliebten", welche meist nichts Gutes im Schilde führen...
Gegen eine Indizierung der Seiten duch Such-Bots ist ja nichts einzuwenden, gegen Kommentar-, GB-, und E-Mail-Spam/Harvester hingegen schon. Nur gut, dass ich für solche Fälle auch ein entsprechendes Abfangskript eingebaut habe...