Главная // Блоги // Блог muff

MySQL - калькуляция и отображение размера баз данных

  • user warning: Table './muffsql1/cache_filter' is marked as crashed and should be repaired query: SELECT data, created, headers, expire, serialized FROM cache_filter WHERE cid = '2:cb33b51a04a98b41ad35b0d4fef81a93' in /usr/local/www/muff.kiev.ua/includes/cache.inc on line 27.
  • user warning: Table './muffsql1/cache_filter' is marked as crashed and should be repaired query: UPDATE cache_filter SET data = '<p class=\"rtecenter\"><img alt=\"MySQL\" width=\"140\" height=\"102\" src=\"http://muff.kiev.ua/files/mysql-logo.jpg\" /></p>\n<p class=\"rtejustify\">Не так давно,&nbsp;рыская по просторам Интернета,&nbsp;набрел на мудреный SQL-запрос, который позволяет подсчитать размер баз данных и вывести результат по каждой БД. Соответственно делюсь на страницах ресурса.</p>\n<p class=\"rtejustify\">Листинг запроса:</p>\n<table border=\"1\" cellspacing=\"1\" cellpadding=\"1\" width=\"100%\">\n<tbody>\n<tr>\n<td>SELECT table_schema &quot;database_name&quot;, sum( data_length + index_length )/1024/1024 &quot;Database size in MB&quot; FROM information_schema. TABLES GROUP BY table_schema;</td>\n</tr>\n</tbody>\n</table>\n<p class=\"rtejustify\">Пример выполнения запроса:</p>\n<table border=\"1\" cellspacing=\"1\" cellpadding=\"1\" width=\"100%\">\n<tbody>\n<tr>\n<td>\n<pre>\nmysql&gt; <strong>SELECT table_schema &quot;database_name&quot;,\n</strong> -&gt; <strong>sum( data_length + index_length )/1024/1024<br /></strong>&nbsp;&nbsp;&nbsp; -&gt; <strong>&quot;Database size in MB&quot; FROM information_schema.<br /></strong>&nbsp;&nbsp;&nbsp; -&gt; <strong>TABLES GROUP BY table_schema;<br /></strong>+--------------------+---------------------+<br />| database_name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Database size in MB |<br />+--------------------+---------------------+<br />| cacti&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.30696487 |<br />| exim&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.01589584 |<br />| information_schema |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.00878906 |<br />| mysql&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.63503933 |<br />| performance_schema |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.00000000 |<br />| roundcube&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.48437500 |<br />+--------------------+---------------------+<br />6 rows in set (0.01 sec)<br /></pre></td>\n</tr>\n</tbody>\n</table>\n<p class=\"rtejustify\">&nbsp;</p>\n', created = 1767333213, expire = 1767419613, headers = '', serialized = 0 WHERE cid = '2:cb33b51a04a98b41ad35b0d4fef81a93' in /usr/local/www/muff.kiev.ua/includes/cache.inc on line 112.
  • user warning: Table './muffsql1/cache_filter' is marked as crashed and should be repaired query: SELECT data, created, headers, expire, serialized FROM cache_filter WHERE cid = '2:07243fc0252056071eaa62af8c18d662' in /usr/local/www/muff.kiev.ua/includes/cache.inc on line 27.
  • user warning: Table './muffsql1/cache_filter' is marked as crashed and should be repaired query: UPDATE cache_filter SET data = '<p class=\"rtecenter\"><a class=\"thickbox\" href=\"/files/imagepicker/1/wake_up_ua.png\"><img alt=\"Вставай, Україно!\" class=\"imgp_img\" src=\"/files/imagepicker/1/thumbs/wake_up_ua.png\" style=\"height:200px; width:150px\" /></a></p>\n', created = 1767333213, expire = 1767419613, headers = '', serialized = 0 WHERE cid = '2:07243fc0252056071eaa62af8c18d662' in /usr/local/www/muff.kiev.ua/includes/cache.inc on line 112.
  • user warning: Table './muffsql1/cache_filter' is marked as crashed and should be repaired query: SELECT data, created, headers, expire, serialized FROM cache_filter WHERE cid = '3:cc913d232116f0426090404133377d88' in /usr/local/www/muff.kiev.ua/includes/cache.inc on line 27.
  • user warning: Table './muffsql1/cache_filter' is marked as crashed and should be repaired query: SELECT data, created, headers, expire, serialized FROM cache_filter WHERE cid = '2:d9a86123bfcbc57878743027b584400b' in /usr/local/www/muff.kiev.ua/includes/cache.inc on line 27.
  • user warning: Table './muffsql1/cache_filter' is marked as crashed and should be repaired query: UPDATE cache_filter SET data = '<p class=\"rtecenter\"><a href=\"http://muff.kiev.ua/rss.xml\"><img alt=\"RSS\" width=\"160\" height=\"60\" src=\"http://muff.kiev.ua/files/muf-rss.png\" /></a></p>\n', created = 1767333213, expire = 1767419613, headers = '', serialized = 0 WHERE cid = '2:d9a86123bfcbc57878743027b584400b' in /usr/local/www/muff.kiev.ua/includes/cache.inc on line 112.
  • user warning: Table './muffsql1/cache_filter' is marked as crashed and should be repaired query: SELECT data, created, headers, expire, serialized FROM cache_filter WHERE cid = '3:39649256b636e3d5ded656bc52bd8c01' in /usr/local/www/muff.kiev.ua/includes/cache.inc on line 27.
Опубликовано muff в Втр, 2012-01-10 03:21
Версия для печатиОтправить другуPDF version

MySQL

Не так давно, рыская по просторам Интернета, набрел на мудреный SQL-запрос, который позволяет подсчитать размер баз данных и вывести результат по каждой БД. Соответственно делюсь на страницах ресурса.

Листинг запроса:

SELECT table_schema "database_name", sum( data_length + index_length )/1024/1024 "Database size in MB" FROM information_schema. TABLES GROUP BY table_schema;

Пример выполнения запроса:

mysql> SELECT table_schema "database_name",
    -> sum( data_length + index_length )/1024/1024
    -> "Database size in MB" FROM information_schema.
    -> TABLES GROUP BY table_schema;
+--------------------+---------------------+
| database_name      | Database size in MB |
+--------------------+---------------------+
| cacti              |          0.30696487 |
| exim               |          0.01589584 |
| information_schema |          0.00878906 |
| mysql              |          0.63503933 |
| performance_schema |          0.00000000 |
| roundcube          |          0.48437500 |
+--------------------+---------------------+
6 rows in set (0.01 sec)

 

Ваша оценка: Нет Средняя: 4.3 (6 голосов)

Вставай, Україно!

Статьи

Всякое разное

Литература

Не говорите программисту: "Заходи, гостем будешь!", порадуйте его возможностью зайти администратором или, в крайнем случае - пользователем