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_processbleek 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_orgduurde 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
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