- 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.

Taille des données et indexes par moteur

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 |
+--------+---------------+--------------+------------+

Taille des données et indexes par base

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 |
+--------------------+---------------+--------------+------------+

Taille des données et indexes des tables d’une base

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 |
+-------------------+---------+---------+-------+

Recherche des colonnes de type ‘blob’

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

Copyright © 2010 Kerlinux.org. Tous droits réservés.