mySQL veritabanında veritabanı ve tabloların ne kadar yer kapladığını görmek.

Veritabanlarının diskte kapladığı boyutu görmek için :

mysql> SELECT table_schema 'Veritabanı Adı', sum( data_length + index_length ) / 1024 / 1024 'Veritabanı Boyutu (MB)', sum( data_free )/ 1024 / 1024 'Boş Alan (MB)' FROM information_schema.TABLES GROUP BY table_schema;
+--------------------------+------------------------+------------------+
| Veritabanı Adı             | Veritabanı Boyutu (MB)  | Boş Alan (MB) |
+--------------------------+------------------------+------------------+
| info                     |             0.25235367 |   24540.01247025 |
| information_schema       |             0.00878906 |       0.00000000 |
| kursad_test              |          3379.31728363 |       0.00000000 |
| mysql                    |             0.68885231 |       0.00037384 |
| performance_schema       |             0.00000000 |       0.00000000 |
+--------------------------+------------------------+------------------+
5 rows in set, 0 warnings (3.33 sec)

Tabloların diskte kapladığı boyutu görmek için :

mysql> SELECT concat(table_schema,'.',table_name), concat(round(table_rows/1000000,2),'M') rows, concat(round(data_length/(1024*1024*1024),2),'G') DATA, concat(round(index_length/(1024*1024*1024),2),'G') idx, concat(round((data_length+index_length)/(1024*1024*1024),2),'G') total_size, round(index_length/data_length,2) idxfrac FROM information_schema.TABLES ORDER BY data_length+index_length DESC LIMIT 20;                  
+-------------------------------------+-------+-------+-------+------------+---------+
| concat(table_schema,'.',table_name) | rows  | DATA  | idx   | total_size | idxfrac |
+-------------------------------------+-------+-------+-------+------------+---------+
| mysql.help_topic                    | 0.00M | 0.00G | 0.00G | 0.00G      |    0.05 |
| mysql.help_keyword                  | 0.00M | 0.00G | 0.00G | 0.00G      |    0.18 |
| mysql.db                            | 0.00M | 0.00G | 0.00G | 0.00G      |    0.26 |
| mysql.help_relation                 | 0.00M | 0.00G | 0.00G | 0.00G      |    1.95 |
| mysql.help_category                 | 0.00M | 0.00G | 0.00G | 0.00G      |    0.14 |
| mysql.tables_priv                   | 0.00M | 0.00G | 0.00G | 0.00G      |    4.81 |
| mysql.proxies_priv                  | 0.00M | 0.00G | 0.00G | 0.00G      |    7.39 |
| mysql.user                          | 0.00M | 0.00G | 0.00G | 0.00G      |    0.75 |
| mysql.proc                          | 0.00M | 0.00G | 0.00G | 0.00G      |   13.13 |
| mysql.columns_priv                  | 0.00M | 0.00G | 0.00G | 0.00G      |    NULL |
| mysql.procs_priv                    | 0.00M | 0.00G | 0.00G | 0.00G      |    NULL |
| mysql.event                         | 0.00M | 0.00G | 0.00G | 0.00G      |    NULL |
| mysql.host                          | 0.00M | 0.00G | 0.00G | 0.00G      |    NULL |
| mysql.func                          | 0.00M | 0.00G | 0.00G | 0.00G      |    NULL |
| mysql.time_zone_transition_type     | 0.00M | 0.00G | 0.00G | 0.00G      |    NULL |
| mysql.time_zone_transition          | 0.00M | 0.00G | 0.00G | 0.00G      |    NULL |
| mysql.time_zone_name                | 0.00M | 0.00G | 0.00G | 0.00G      |    NULL |
| mysql.time_zone_leap_second         | 0.00M | 0.00G | 0.00G | 0.00G      |    NULL |
| mysql.time_zone                     | 0.00M | 0.00G | 0.00G | 0.00G      |    NULL |
| mysql.plugin                        | 0.00M | 0.00G | 0.00G | 0.00G      |    NULL |
+-------------------------------------+-------+-------+-------+------------+---------+
20 rows in set (0.00 sec)

Bu sorguları değiştirip veritabanı ve tablo bazlı sonuçlarda elde edebilirsiniz.

Posted in Genel, MySQL on March 19th, 2012 by Kürşad DARA | | 0 Comments

Leave a reply