- Kerlinux.org - http://kerlinux.org -
MySQL: Calcul des volumes de données avec la base information_schema
Posted By SLiX On 26 juillet 2010 @ 22 h 05 min In Informatique | 1 Comment
MySQL (depuis la version 5.0.2) fournit une base de données « virtuelle » contenant des informations intéressantes sur les bases existantes (définitions, tailles, statistiques, …): information_schema.
Son schéma est dénormalisé; cette base permet donc de trouver facilement les informations, sans jointure.
Voici quelques requêtes en rapport avec la taille des données et indexes que j’utilise régulièrement pour identifier ce qui consomme de la place.
select ENGINE, sum(DATA_LENGTH)/(1024*1024) as "Données (Mo)", sum(INDEX_LENGTH)/(1024*1024) as "Indexes (Mo)", sum(DATA_LENGTH+INDEX_LENGTH)/(1024*1024) as "Total (Mo)" from information_schema.TABLES group by ENGINE;
Résultat:
+--------+---------------+--------------+------------+ | ENGINE | Données (Mo) | Indexes (Mo) | Total (Mo) | +--------+---------------+--------------+------------+ | InnoDB | 9.9531 | 2.9219 | 12.8750 | | MEMORY | 0.0000 | 0.0000 | 0.0000 | | MyISAM | 55.0762 | 4.9922 | 60.0683 | +--------+---------------+--------------+------------+
select TABLE_SCHEMA, sum(DATA_LENGTH)/(1024*1024) as "Données (Mo)", sum(INDEX_LENGTH)/(1024*1024) as "Indexes (Mo)", sum(DATA_LENGTH+INDEX_LENGTH)/(1024*1024) as "Total (Mo)" from information_schema.TABLES group by TABLE_SCHEMA;
Résultat:
+--------------------+---------------+--------------+------------+ | TABLE_SCHEMA | Données (Mo) | Indexes (Mo) | Total (Mo) | +--------------------+---------------+--------------+------------+ | ampache | 51.8759 | 4.7305 | 56.6064 | | blogs | 0.8438 | 0.7344 | 1.5781 | | cdarchive | 0.2568 | 0.0664 | 0.3232 | | site1 | 5.5625 | 0.7500 | 6.3125 | | gallery2 | 0.9375 | 0.7344 | 1.6719 | | information_schema | 0.0000 | 0.0039 | 0.0039 | | site2 | 2.3750 | 0.5000 | 2.8750 | | mysql | 0.4392 | 0.0654 | 0.5046 | | phpinv | 0.1150 | 0.0488 | 0.1638 | | piwigo | 0.0102 | 0.0557 | 0.0659 | | zenphoto | 0.2344 | 0.2031 | 0.4375 | +--------------------+---------------+--------------+------------+
select TABLE_NAME, DATA_LENGTH/(1024*1024) as Datas, INDEX_LENGTH/(1024*1024) as Indexes, TABLE_ROWS as Rows from information_schema.TABLES where TABLE_SCHEMA='ampache' order by Datas DESC limit 10;
Résultat:
+-------------------+---------+---------+-------+ | TABLE_NAME | Datas | Indexes | Rows | +-------------------+---------+---------+-------+ | album_data | 47.7389 | 0.0176 | 868 | | song | 3.0678 | 3.4121 | 18086 | | song_data | 0.4783 | 0.1787 | 18086 | | tag_map | 0.3905 | 0.7305 | 17059 | | artist | 0.0943 | 0.1865 | 3732 | | tmp_browse | 0.0385 | 0.0029 | 41 | | album | 0.0352 | 0.0781 | 935 | | tmp_playlist_data | 0.0103 | 0.0127 | 447 | | session | 0.0048 | 0.0059 | 29 | | object_count | 0.0042 | 0.0215 | 243 | +-------------------+---------+---------+-------+
select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE from information_schema.COLUMNS where DATA_TYPE like '%blob%' and TABLE_SCHEMA != 'mysql';
Résultat:
+--------------+-------------+-------------+------------+ | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | DATA_TYPE | +--------------+-------------+-------------+------------+ | ampache | album_data | art | mediumblob | | ampache | album_data | thumb | mediumblob | | ampache | artist_data | art | mediumblob | | ampache | artist_data | thumb | mediumblob | +--------------+-------------+-------------+------------+
Référence: http://dev.mysql.com/doc/refman/5.1/en/information-schema.html [1]
Article printed from Kerlinux.org: http://kerlinux.org
URL to article: http://kerlinux.org/2010/07/mysql-calcul-des-volumes-de-donnees-avec-la-base-information_schema/
URLs in this post:
[1] http://dev.mysql.com/doc/refman/5.1/en/information-schema.html: http://dev.mysql.com/doc/refman/5.1/en/information-schema.html
Click here to print.
Copyright © 2010 Kerlinux.org. Tous droits réservés.