Database-onderhoud: verschil tussen versies

Uit De Vliegende Brigade
Naar navigatie springen Naar zoeken springen
 
(23 tussenliggende versies door dezelfde gebruiker niet weergegeven)
Regel 1: Regel 1:
 
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.
 
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 alleen databases en tabellen in InnoDB-formaat.
+
Dit artikel behandelt voornamelijk databases en tabellen in InnoDB-formaat.
  
−
== Inventaris ==
+
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?
 
Waar moet je aan denken rondom database-onderhoud?
Regel 10: Regel 12:
 
* Optimize tables
 
* Optimize tables
 
* Analyze???
 
* 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 <code>mysqlcheck</code>-commando, maar dan één tabel per keer.
 +
 +
=== Syntaxis ===
 +
 +
<pre>
 +
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
 +
}
 +
</pre>
 +
 +
=== Voorbeeld bal_dwh_org ===
 +
 +
Toegepast op database <code>bal_dwh_org</code> (die ik net daarvoor al had onderhouden via <code>mysqlcheck</code>) op een tabel met 100.000 rijen :
 +
 +
<pre>
 +
check table ean fast quick;
 +
check table ean extended;
 +
</pre>
 +
 +
Executie was vrijwel instantaan. Output was twee keer zoiets als dit:
 +
 +
<pre>
 +
Table            Op      Msg_type  Msg_text
 +
bal_dwh_org.ean  check  status    OK
 +
</pre>
 +
 +
== OPTIMIZE TABLE-statement (SQL) ==
 +
 +
<code>optimize table</code> werkt voor elk database-bestandstype. De acties verschillen wel van type tot type.
 +
 +
Voorbeeld:
 +
 +
<pre>
 +
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)
 +
</pre>
 +
 +
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) ==
 +
 +
<code>mysqlcheck</code> is een command-line routine voor db-onderhoud. Voorbeeld met een WordPress-db:
 +
 +
=== --check ===
 +
 +
<pre>
 +
$ 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
 +
</pre>
 +
 +
In dec. 2021 heb ik dit toegepast op uitgebreide databases die ik al jaren gebruik zonder enig onderhoud (<code>bal_dwh_org</code>), en ik kreeg zonder uitzondering alleen maar <code>OK</code>. Misschien heb ik nog nooit een écht gecrashte db meegemaakt.
 +
 +
=== --optimize ===
 +
 +
<pre>
 +
$ 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
 +
</pre>
 +
 +
* Tabel <code>wp_wtc_curren_process</code> 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 <code>bal_dwh_org</code> duurde dit ook een paar minuten.
 +
 +
=== --analyze ===
 +
 +
Met dezelfde WordPress-db als hiervoor:
 +
 +
<pre>
 +
$ 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
 +
</pre>
 +
 +
Executie is een kwestie van secondes, ook voor <code>bal_dwh_org</code>.
  
 
== Zie ook ==
 
== Zie ook ==
  
 
* [[Database repareren (MySQL)]]
 
* [[Database repareren (MySQL)]]
 +
* [[WP-DBManager-plugin]]
  
 
== Bronnen ==
 
== Bronnen ==
Regel 19: Regel 147:
 
* https://stackoverflow.com/questions/7478849/general-mysql-database-maintenance-advice
 
* 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://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

Huidige versie van 6 dec 2021 om 17:51

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

Bronnen

Overgenomen van "https://wiki.devliegendebrigade.nl/index.php?title=Database-onderhoud&oldid=27969"