Database-onderhoud

Net als toendertijd met Microsoft Access, kent MySQL zoiets als database-onderhoud. Dat begint met een handjevol standaard SQL-commando's, maar kan ongetwijfeld ook geavanceerder. WordPress heeft hier een plugin voor: WP-DBManager. Goede kans dat er meer van dit soort plugins bestaan.

Dit artikel behandelt voornamelijk databases en tabellen in InnoDB-formaat.

Voor enige achtergrondinformatie, zie https://www.laurencegellert.com/2011/07/mysql-maintenance-tasks-for-innodb-with-mysql-5-1/

Overzicht

Waar moet je aan denken rondom database-onderhoud?

• Check table integrity for errors
• Optimize tables
• Analyze???

CHECK TABLE-statement (SQL)

Het MySQL statement CHECK TABLE verifiëert en repareert tabellen. Ik heb de indruk dat het hetzelfde doet als het vergelijkbare mysqlcheck-commando, maar dan één tabel per keer.

Syntaxis

CHECK TABLE tbl_name [, tbl_name]... [option]...

option:
{
   FOR UPGRADE
   CHANGED       # Ignored for InnoDB
   EXTENDED      # Ignored for InnoDB
   FAST          # Ignored for InnoDB
   MEDIUM        # Ignored for InnoDB
   QUICK         # Both for MyISAM & InnoDB
}

Voorbeeld bal_dwh_org

Toegepast op database bal_dwh_org (die ik net daarvoor al had onderhouden via mysqlcheck) op een tabel met 100.000 rijen :

check table ean fast quick;
check table ean extended;

Executie was vrijwel instantaan. Output was twee keer zoiets als dit:

Table             Op      Msg_type   Msg_text
bal_dwh_org.ean   check   status     OK

OPTIMIZE TABLE-statement (SQL)

optimize table werkt voor elk database-bestandstype. De acties verschillen wel van type tot type.

Voorbeeld:

mysql> use bal_dwh_org;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed


mysql> optimize table ean;
+-----------------+----------+----------+-------------------------------------------------------------------+
| Table           | Op       | Msg_type | Msg_text                                                          |
+-----------------+----------+----------+-------------------------------------------------------------------+
| bal_dwh_org.ean | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| bal_dwh_org.ean | optimize | status   | OK                                                                |
+-----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (18,00 sec)

Het lijkt erop, dat je dit voor elke tabel afzonderlijk moet doen. Wat dat betreft is de command line-alternatief (zie elders in dit artikel) wel zo handig, want die kan alle tabellen in een database in één keer afhandelen.

mysqlcheck (Bash)

mysqlcheck is een command-line routine voor db-onderhoud. Voorbeeld met een WordPress-db:

--check

$ mysqlcheck --check knl

knl.wp_actionscheduler_actions                     OK
knl.wp_actionscheduler_claims                      OK
knl.wp_actionscheduler_groups                      OK
knl.wp_actionscheduler_logs                        OK
knl.wp_aws_cache                                   OK
...
knl.wp_yoast_primary_term                          OK
knl.wp_yoast_seo_links                             OK
knl.wp_yoast_seo_meta                              OK

In dec. 2021 heb ik dit toegepast op uitgebreide databases die ik al jaren gebruik zonder enig onderhoud (bal_dwh_org), en ik kreeg zonder uitzondering alleen maar OK. Misschien heb ik nog nooit een écht gecrashte db meegemaakt.

--optimize

$ mysqlcheck --optimize knl

knl.wp_actionscheduler_actions
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
knl.wp_actionscheduler_claims
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
...
knl.wp_wptc_current_process                        OK
...
knl.wp_yoast_seo_meta
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK

• Tabel wp_wtc_curren_process bleek als enige van het type MyISAM te zijn. Dat was daarom de enige database die gewoon geoptimaliseerd kon worden. Blijkbaar werkt dit niet voor InnoDB
• Executie kostte een paar minuten voor een WordPress-db
• Voor bal_dwh_org duurde dit ook een paar minuten.

--analyze

Met dezelfde WordPress-db als hiervoor:

$ mysqlcheck --analyze knl

knl.wp_actionscheduler_actions                     OK
knl.wp_actionscheduler_claims                      OK
knl.wp_actionscheduler_groups                      OK
...
knl.wp_wptc_current_process                        Table is already up to date
...
knl.wp_yoast_seo_meta                              OK

Executie is een kwestie van secondes, ook voor bal_dwh_org.

Zie ook

• Database repareren (MySQL)
• WP-DBManager-plugin

Bronnen

• https://stackoverflow.com/questions/7478849/general-mysql-database-maintenance-advice
• https://www.laurencegellert.com/2011/07/mysql-maintenance-tasks-for-innodb-with-mysql-5-1/
• https://dev.mysql.com/doc/refman/8.0/en/check-table.html
• http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html
• https://wordpress.org/plugins/wp-dbmanager/
• https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html