Optimitzar les taules del Mysql

De Wikijoan
Dreceres ràpides: navegació, cerca

Contingut

OPTIMIZE TABLE

He fet una còpia de seguretat, i l'arxiu dmp resultant encara és molt gros, de l'ordre de 200MB i igual de gros que abans de fer tota la neteja. Falta alliberar, reparar i compactar la bd.

DB's with lots of activity should be optimized regularly. Doing so is easy.

OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file. After extensive changes to a table, this statement may also improve performance of statements that use the table, sometimes significantly.

OPTIMIZE TABLE works for MyISAM, InnoDB, and ARCHIVE tables.

LOCK TABLES tablename READ;
CHECK TABLE affils;
UNLOCK TABLES;

When completed,

OPTIMIZE TABLE tablename;

Anem a fer-ho per a la bd wikidb117:

LOCK TABLES archive READ;
LOCK TABLES category READ;
LOCK TABLES categorylinks READ;
LOCK TABLES change_tag READ;
LOCK TABLES external_user READ;
LOCK TABLES externallinks READ;
LOCK TABLES filearchive READ;
LOCK TABLES hitcounter READ;
LOCK TABLES image READ;
LOCK TABLES imagelinks READ;
LOCK TABLES interwiki READ;
LOCK TABLES ipblocks READ;
LOCK TABLES iwlinks READ;
LOCK TABLES job READ;
LOCK TABLES l10n_cache READ;
LOCK TABLES langlinks READ;
LOCK TABLES log_search READ;
LOCK TABLES logging READ;
LOCK TABLES math READ;
LOCK TABLES module_deps READ;
LOCK TABLES msg_resource READ;
LOCK TABLES msg_resource_links READ;
LOCK TABLES objectcache READ;
LOCK TABLES oldimage READ;
LOCK TABLES page READ;
LOCK TABLES page_props READ;
LOCK TABLES page_restrictions READ;
LOCK TABLES pagelinks READ;
LOCK TABLES protected_titles READ;
LOCK TABLES querycache READ;
LOCK TABLES querycache_info READ;
LOCK TABLES querycachetwo READ;
LOCK TABLES recentchanges READ;
LOCK TABLES redirect READ;
LOCK TABLES revision READ;
LOCK TABLES searchindex READ;
LOCK TABLES site_stats READ;
LOCK TABLES tag_summary READ;
LOCK TABLES templatelinks READ;
LOCK TABLES text READ;
LOCK TABLES trackbacks READ;
LOCK TABLES transcache READ;
LOCK TABLES updatelog READ;
LOCK TABLES user READ;
LOCK TABLES user_groups READ;
LOCK TABLES user_newtalk READ;
LOCK TABLES user_properties READ;
LOCK TABLES valid_tag READ;
LOCK TABLES watchlist READ;

fem el chick i mirem que totes les taules tinguin Status=OK

CHECK TABLE archive;
CHECK TABLE category;
CHECK TABLE categorylinks;
CHECK TABLE change_tag;
CHECK TABLE evolucio_tamany_pag;
CHECK TABLE external_user;
CHECK TABLE externallinks;
CHECK TABLE filearchive;
CHECK TABLE hitcounter;
CHECK TABLE image;
CHECK TABLE imagelinks;
CHECK TABLE interwiki;
CHECK TABLE ipblocks;
CHECK TABLE iwlinks;
CHECK TABLE job;
CHECK TABLE l10n_cache;
CHECK TABLE langlinks;
CHECK TABLE log_search;
CHECK TABLE logging;
CHECK TABLE math;
CHECK TABLE module_deps;
CHECK TABLE msg_resource;
CHECK TABLE msg_resource_links;
CHECK TABLE objectcache;
CHECK TABLE oldimage;
CHECK TABLE page;
CHECK TABLE page_props;
CHECK TABLE page_restrictions;
CHECK TABLE pagelinks;
CHECK TABLE protected_titles;
CHECK TABLE querycache;
CHECK TABLE querycache_info;
CHECK TABLE querycachetwo;
CHECK TABLE recentchanges;
CHECK TABLE redirect;
CHECK TABLE revision;
CHECK TABLE searchindex;
CHECK TABLE site_stats;
CHECK TABLE tag_summary;
CHECK TABLE templatelinks;
CHECK TABLE text;
CHECK TABLE trackbacks;
CHECK TABLE transcache;
CHECK TABLE updatelog;
CHECK TABLE user;
CHECK TABLE user_groups;
CHECK TABLE user_newtalk;
CHECK TABLE user_properties;
CHECK TABLE valid_tag;
CHECK TABLE watchlist;
CHECK TABLE wiki_stats;
CHECK TABLE wiki_stats_acum;

Ja podem desbloquejar les taules:

UNLOCK TABLES;

i ara ja podem optimitzar l'espai de les taules, sobretot aquelles que han tingut més actualitzacions: revision, text, recentchanges. Per exemple:

mysql> OPTIMIZE TABLE text;
Table	Op	Msg_type	Msg_text
wikidb117.text	optimize	note	Table does not support optimize, doing recreate + analyze instead
wikidb117.text	optimize	status	OK
OPTIMIZE TABLE archive;
OPTIMIZE TABLE category;
OPTIMIZE TABLE categorylinks;
OPTIMIZE TABLE change_tag;
OPTIMIZE TABLE external_user;
OPTIMIZE TABLE externallinks;
OPTIMIZE TABLE filearchive;
OPTIMIZE TABLE hitcounter;
OPTIMIZE TABLE image;
OPTIMIZE TABLE imagelinks;
OPTIMIZE TABLE interwiki;
OPTIMIZE TABLE ipblocks;
OPTIMIZE TABLE iwlinks;
OPTIMIZE TABLE job;
OPTIMIZE TABLE l10n_cache;
OPTIMIZE TABLE langlinks;
OPTIMIZE TABLE log_search;
OPTIMIZE TABLE logging;
OPTIMIZE TABLE math;
OPTIMIZE TABLE module_deps;
OPTIMIZE TABLE msg_resource;
OPTIMIZE TABLE msg_resource_links;
OPTIMIZE TABLE objectcache;
OPTIMIZE TABLE oldimage;
OPTIMIZE TABLE page;
OPTIMIZE TABLE page_props;
OPTIMIZE TABLE page_restrictions;
OPTIMIZE TABLE pagelinks;
OPTIMIZE TABLE protected_titles;
OPTIMIZE TABLE querycache;
OPTIMIZE TABLE querycache_info;
OPTIMIZE TABLE querycachetwo;
OPTIMIZE TABLE recentchanges;
OPTIMIZE TABLE redirect;
OPTIMIZE TABLE revision;
OPTIMIZE TABLE searchindex;
OPTIMIZE TABLE site_stats;
OPTIMIZE TABLE tag_summary;
OPTIMIZE TABLE templatelinks;
OPTIMIZE TABLE text;
OPTIMIZE TABLE trackbacks;
OPTIMIZE TABLE transcache;
OPTIMIZE TABLE updatelog;
OPTIMIZE TABLE user;
OPTIMIZE TABLE user_groups;
OPTIMIZE TABLE user_newtalk;
OPTIMIZE TABLE user_properties;
OPTIMIZE TABLE valid_tag;
OPTIMIZE TABLE watchlist;

I ara tornem a fer una còpia de seguretat, a veure quant ocupa:

$ mysqldump --add-drop-table -i -C -u root -p -r /home/joan/copia_seguretat/mysql_backup/copia_wikidb117_181010b.dmp -v wikidb117

$ ls -la /home/joan/copia_seguretat/mysql_backup/copia_wikidb117_181010*.dmp
-rw-r--r-- 1 joan joan 203130733 2011-10-18 16:29 /home/joan/copia_seguretat/mysql_backup/copia_wikidb117_181010b.dmp
-rw-r--r-- 1 joan joan 203055652 2011-10-18 13:59 /home/joan/copia_seguretat/mysql_backup/copia_wikidb117_181010.dmp

doncs no es veu a penes diferència.

Anem a comparar el tamany de la bd actual amb la bd del servidor antic, abans d'eliminar tot el spam.

El servidor actual, amb spam netejat:

mysql> SELECT TABLE_NAME, table_rows, data_length, index_length, 
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES WHERE table_schema = 'wikidb117';

+---------------------+------------+-------------+--------------+------------+
| TABLE_NAME          | table_rows | data_length | index_length | Size in MB |
+---------------------+------------+-------------+--------------+------------+
| archive             |      14965 |     4734976 |      3670016 |       8.02 |
| category            |        113 |       16384 |        32768 |       0.05 |
| categorylinks       |        491 |       81920 |       180224 |       0.25 |
| change_tag          |          0 |       16384 |        65536 |       0.08 |
| evolucio_tamany_pag |       NULL |        NULL |         NULL |       NULL |
| external_user       |          0 |       16384 |        16384 |       0.03 |
| externallinks       |       4727 |     1589248 |      1212416 |       2.67 |
| filearchive         |          0 |       16384 |        65536 |       0.08 |
| hitcounter          |          0 |           0 |            0 |       0.00 |
| image               |        397 |       65536 |        65536 |       0.13 |
| imagelinks          |        251 |       16384 |        16384 |       0.03 |
| interwiki           |        146 |       65536 |            0 |       0.06 |
| ipblocks            |          9 |       16384 |        81920 |       0.09 |
| iwlinks             |          0 |       16384 |        16384 |       0.03 |
| job                 |          0 |       16384 |        16384 |       0.03 |
| l10n_cache          |       7161 |     1589248 |       344064 |       1.84 |
| langlinks           |          0 |       16384 |        16384 |       0.03 |
| log_search          |          0 |       16384 |        16384 |       0.03 |
| logging             |        421 |       81920 |       131072 |       0.20 |
| math                |          0 |       16384 |            0 |       0.02 |
| module_deps         |          3 |       16384 |            0 |       0.02 |
| msg_resource        |         20 |       16384 |            0 |       0.02 |
| msg_resource_links  |         12 |       16384 |            0 |       0.02 |
| objectcache         |       1057 |      458752 |       294912 |       0.72 |
| oldimage            |         13 |       16384 |        65536 |       0.08 |
| page                |        839 |      163840 |       147456 |       0.30 |
| page_props          |          0 |       16384 |            0 |       0.02 |
| page_restrictions   |          0 |       16384 |        65536 |       0.08 |
| pagelinks           |       1615 |      114688 |        98304 |       0.20 |
| protected_titles    |          0 |       16384 |        16384 |       0.03 |
| querycache          |          0 |       16384 |        16384 |       0.03 |
| querycache_info     |          0 |       16384 |            0 |       0.02 |
| querycachetwo       |          0 |       16384 |        49152 |       0.06 |
| recentchanges       |      26944 |     5783552 |     11059200 |      16.06 |
| redirect            |          7 |       16384 |        16384 |       0.03 |
| revision            |      53049 |    11026432 |     13189120 |      23.09 |
| searchindex         |       1239 |     5193816 |      3116032 |       7.92 |
| site_stats          |          1 |       16384 |            0 |       0.02 |
| tag_summary         |          0 |       16384 |        49152 |       0.06 |
| templatelinks       |        402 |       16384 |        16384 |       0.03 |
| text                |     228687 |   247070720 |            0 |     235.63 |
| trackbacks          |          0 |       16384 |        16384 |       0.03 |
| transcache          |          0 |       16384 |            0 |       0.02 |
| updatelog           |          5 |       32768 |            0 |       0.03 |
| user                |          1 |       16384 |        32768 |       0.05 |
| user_groups         |          2 |       16384 |        16384 |       0.03 |
| user_newtalk        |          0 |       16384 |        32768 |       0.05 |
| user_properties     |          6 |       16384 |        16384 |       0.03 |
| valid_tag           |          0 |       16384 |            0 |       0.02 |
| watchlist           |          2 |       16384 |        16384 |       0.03 |
| wiki_stats          |      34918 |     1589248 |            0 |       1.52 |
| wiki_stats_acum     |         25 |       16384 |            0 |       0.02 |
+---------------------+------------+-------------+--------------+------------+
52 rows in set (0.22 sec)

El servidor antic (amb spam):

mysql> SELECT TABLE_NAME, table_rows, data_length, index_length, 
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES WHERE table_schema = 'wikidb';

+---------------------+------------+-------------+--------------+------------+
| TABLE_NAME          | table_rows | data_length | index_length | Size in MB |
+---------------------+------------+-------------+--------------+------------+
| archive             |      15328 |     4734976 |      1589248 |       6.03 | 
| categorylinks       |        743 |       81920 |       131072 |       0.20 | 
| evolucio_tamany_pag |       NULL |        NULL |         NULL |       NULL | 
| externallinks       |       4330 |     1425408 |      1130496 |       2.44 | 
| filearchive         |          0 |       16384 |        65536 |       0.08 | 
| hitcounter          |          0 |           0 |            0 |       0.00 | 
| image               |        233 |       49152 |        32768 |       0.08 | 
| imagelinks          |        249 |       16384 |        16384 |       0.03 | 
| interwiki           |        148 |       65536 |            0 |       0.06 | 
| ipblocks            |          9 |       16384 |        81920 |       0.09 | 
| job                 |          0 |       16384 |        16384 |       0.03 | 
| langlinks           |          0 |       16384 |        16384 |       0.03 | 
| logging             |        211 |       65536 |        98304 |       0.16 | 
| math                |          0 |       16384 |            0 |       0.02 | 
| objectcache         |          2 |       16384 |        16384 |       0.03 | 
| oldimage            |         13 |       16384 |        16384 |       0.03 | 
| page                |       1003 |      245760 |       147456 |       0.38 | 
| pagelinks           |       1315 |      114688 |        98304 |       0.20 | 
| querycache          |          0 |       16384 |        16384 |       0.03 | 
| querycache_info     |          0 |       16384 |            0 |       0.02 | 
| querycachetwo       |          0 |       16384 |        49152 |       0.06 | 
| recentchanges       |      27133 |     6307840 |     12697600 |      18.13 | 
| redirect            |          7 |       16384 |        16384 |       0.03 | 
| revision            |     207405 |    38305792 |     44613632 |      79.08 | 
| searchindex         |       1248 |     5220028 |      4190208 |       8.97 | 
| site_stats          |          1 |       16384 |            0 |       0.02 | 
| templatelinks       |        400 |       16384 |        16384 |       0.03 | 
| text                |     324705 |   329957376 |            0 |     314.67 | 
| trackbacks          |          0 |       16384 |        16384 |       0.03 | 
| transcache          |          0 |       16384 |            0 |       0.02 | 
| user                |        129 |      114688 |        32768 |       0.14 | 
| user_groups         |          2 |       16384 |        16384 |       0.03 | 
| user_newtalk        |          0 |       16384 |        32768 |       0.05 | 
| watchlist           |          6 |       16384 |        16384 |       0.03 | 
| wiki_stats          |          0 |       16384 |            0 |       0.02 | 
| wiki_stats_acum     |          0 |       16384 |            0 |       0.02 | 
+---------------------+------------+-------------+--------------+------------+
36 rows in set (3.43 sec)

Veig que en les taules revision, recentchanges i text hi ha una reducció significativa. Així i tot, la base de dades ocupa més de 200MB. Però clar que això no m'hauria d'estranyar si veig que la taula text ja ocupa 235MB. Per tant, si vull una reducció definitiva de la base de dades, el que hauria de fer és carregar-me la majoria de les revisions (eliminar files en la taula revision i sobretot en la taula text).

Recrear el fitxer ibdata1 i la base de dades

How to shrink a mysql database:

This is a limitation of InnoDB's shared tablespace file. It would be a very expensive operation for the MySQL Server to compact the used space in the tablespace file and shrink the file. Imagine if you have a 100GB tablespace file, and you want to perform a rebuild to compact it. Typically, MySQL table rebuilds require both the old and the new storage to exist simultaneously on disk during the operation.

One solution to shrinking a shared InnoDB tablespace is:

/var/lib/mysql# ls -la
total 741408
drwx------  5 mysql mysql      4096 2011-10-18 16:38 .
drwxr-xr-x 66 root  root       4096 2011-09-26 23:11 ..
-rw-r--r--  1 mysql mysql         0 2011-09-26 23:11 debian-5.1.flag
-rw-rw----  1 mysql mysql 748683264 2011-10-18 17:57 ibdata1
-rw-rw----  1 mysql mysql   5242880 2011-10-18 17:57 ib_logfile0
-rw-rw----  1 mysql mysql   5242880 2011-10-18 17:57 ib_logfile1
-rw-rw----  1 mysql mysql         5 2011-10-18 16:38 joan-servidor.pid
drwx------  2 mysql mysql      4096 2011-10-13 22:17 langtrainer
drwx------  2 mysql mysql      4096 2011-09-26 23:11 mysql
-rw-rw----  1 mysql mysql         6 2010-12-11 00:45 mysql_upgrade_info
drwx------  2 mysql mysql      4096 2011-10-18 16:27 wikidb117

Fixem-nos que el fitxer ibdata1 ocupa 748Mb (una barbaritat).

S'hauria de provar. Estem parlant de què una còpia de seguretat ocupi menys espai, i que l'espai de taules associat també ocupi menys espai.

Anem a fer-ho amb un servidor mysql de prova:

$ mysqldump --add-drop-table -i -C -u root -p -r /home/joan/copia_joanillofotos_181011.dmp -v joanillofotos
$ mysqldump --add-drop-table -i -C -u root -p -r /home/joan/copia_langtrainer_181011.dmp -v langtrainer
root@joan-laptop:/var/lib/mysql# ls -la
total 28700
drwx------  5 mysql mysql     4096 2011-10-18 22:57 .
drwxr-xr-x 69 root  root      4096 2011-10-10 12:44 ..
-rw-r--r--  1 root  root         0 2011-09-28 13:20 debian-5.1.flag
-rw-rw----  1 mysql mysql 18874368 2011-10-18 23:14 ibdata1
-rw-rw----  1 mysql mysql  5242880 2011-10-18 23:14 ib_logfile0
-rw-rw----  1 mysql mysql  5242880 2011-10-06 00:27 ib_logfile1
drwx------  2 mysql mysql     4096 2011-10-06 00:20 joanillofotos
-rw-rw----  1 mysql mysql        4 2011-10-18 22:57 joan-laptop.pid
drwx------  2 mysql mysql     4096 2011-10-10 10:13 langtrainer
drwx------  2 mysql root      4096 2011-09-28 13:20 mysql
-rw-rw----  1 root  root         6 2011-09-28 13:20 mysql_upgrade_info

Elimino les bases de dades

mysql> drop database langtrainer;
Query OK, 6 rows affected (0.30 sec)

mysql> drop database joanillofotos;
Query OK, 11 rows affected (0.49 sec)

/var/lib/mysql# sudo mv ibdata1 /home/joan (el movem, és com si l'eliminéssim)

# /etc/init.d/mysql restart

es torna a crear el fitxer ibdata1, ara més petit:

-rw-rw----  1 mysql mysql 10485760 2011-10-18 23:21 ibdata1

Faig la importació de les dues bases de dades.

CREATE DATABASE langtrainer DEFAULT CHARACTER SET utf8;
CREATE DATABASE joanillofotos DEFAULT CHARACTER SET utf8;

mysql -u root -p langtrainer < /home/joan/copia_langtrainer_181011.dmp
mysql -u root -p joanillofotos < /home/joan/copia_joanillo_181011.dmp
root@joan-laptop:/var/lib/mysql# ls -la
total 20508
drwx------  5 mysql mysql     4096 2011-10-18 23:26 .
drwxr-xr-x 69 root  root      4096 2011-10-10 12:44 ..
-rw-r--r--  1 root  root         0 2011-09-28 13:20 debian-5.1.flag
-rw-rw----  1 mysql mysql 10485760 2011-10-18 23:21 ibdata1
-rw-rw----  1 mysql mysql  5242880 2011-10-18 23:21 ib_logfile0
-rw-rw----  1 mysql mysql  5242880 2011-10-06 00:27 ib_logfile1
drwx------  2 mysql mysql     4096 2011-10-18 23:27 joanillofotos
-rw-rw----  1 mysql mysql        5 2011-10-18 23:21 joan-laptop.pid
drwx------  2 mysql mysql     4096 2011-10-18 23:26 langtrainer
drwx------  2 mysql root      4096 2011-09-28 13:20 mysql
-rw-rw----  1 root  root         6 2011-09-28 13:20 mysql_upgrade_info

i el fitxer ibdata1 continua sent petit. Ara torno a fer una còpia de la base de dades:

$ mysqldump --add-drop-table -i -C -u root -p -r /home/joan/copia_joanillofotos_181011b.dmp -v joanillofotos
$ mysqldump --add-drop-table -i -C -u root -p -r /home/joan/copia_langtrainer_181011b.dmp -v langtrainer

-rw-r--r-- 1 joan joan 2003506 2011-10-18 23:14 copia_joanillofotos_181011.dmp
-rw-r--r-- 1 joan joan 2002605 2011-10-18 23:35 copia_joanillofotos_181011b.dmp
-rw-r--r-- 1 joan joan    7828 2011-10-18 23:35 copia_langtrainer_181011b.dmp
-rw-r--r-- 1 joan joan    8818 2011-10-18 23:17 copia_langtrainer_181011.dmp

i és cert que les noves còpies són més petites, però no és significatiu.

idees per a fer una còpia de wikidb117

Tornant a l'origen, a mi el que em preocupa és que la còpia de la wikidb117 són més de 200MB, i no té massa sentit generar fitxers de 200MB cada setmana. Per tant, allò lògic seria fer una còpia sencera cada mes (i guardar els dos mesos anteriors, anar eliminant, no té cap sentit anar acumulant còpies). Aleshores, cada setmana fer una còpia de l'activitat que hi ha hagut (per exemple, copiar les instruccions SQL per tal de regenerar les últimes files de les taules pages, revision i text).


creat per Joan Quintana Compte, octubre 2011

Eines de l'usuari
Espais de noms
Variants
Accions
Navegació
IES Jaume Balmes
Màquines recreatives
CNC
Informàtica musical
joanillo.org Planet
Eines