Смотрим размер баз данных mysql.#
Нашёл интересный запрос, который выводит результат в удобочитаемом виде.
1
2
3
4
| SELECT table_schema "database_name",
sum( data_length + index_length )/1024/1024 "DB size in MB"
FROM information_schema.
TABLES GROUP BY table_schema;
|
В результате получаем:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| +-----------------------+---------------------+
| database_name | Database size in MB |
+-----------------------+---------------------+
| 3456models_wp | 6.75263977 |
| 4563oems_wp | 1.42401886 |
| 346_kinkdb | 5.32735062 |
| 7657blog_wp | 546.99484158 |
| information_schema | 0.00781250 |
| 78_atm | 0.19333363 |
| 87_345videos | 23.48179626 |
| 87_bio35 | 0.64177418 |
| 87_germanperverts | 0.63171864 |
| 87_go456 | 2.92414284 |
+-----------------------+---------------------+
|
Для того чтоб узнать размер таблиц конкретной базы данных:
1
2
3
4
| SELECT table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.tables
WHERE table_schema = "my_table";
|
Где my_table – имя интересующей базы данных.
В результате получаем:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
| +---------------------------+---------+
| table_name | Size MB |
+---------------------------+---------+
| phpbb_privmsgs_rules | 0.00 |
| phpbb_modules | 0.04 |
| phpbb_boardrules | 0.06 |
| phpbb_push_subscriptions | 0.20 |
| phpbb_profile_fields_data | 0.67 |
| phpbb_bookmarks | 0.00 |
| phpbb_ads | 0.05 |
| phpbb_ranks | 0.00 |
| phpbb_acl_groups | 0.07 |
| phpbb_user_notifications | 19.09 |
| phpbb_topics_track | 3.86 |
| phpbb_drafts | 0.03 |
| phpbb_forums_access | 0.00 |
| phpbb_banlist | 0.06 |
| phpbb_confirm | 24.43 |
| phpbb_privmsgs | 4.80 |
| phpbb_moderator_cache | 0.00 |
| phpbb_bbcodes | 0.01 |
| phpbb_notifications | 15.55 |
| phpbb_poll_options | 0.01 |
| phpbb_lang | 0.00 |
| phpbb_privmsgs_folder | 0.00 |
| phpbb_icons | 0.00 |
| phpbb_ad_locations | 0.03 |
| phpbb_search_wordlist | 0.00 |
| phpbb_register_log | 0.05 |
| phpbb_bots | 0.01 |
| phpbb_sessions | 5197.88 |
| phpbb_acl_users | 0.00 |
| phpbb_acl_roles | 0.01 |
| phpbb_zebra | 0.01 |
| phpbb_captcha_answers | 0.00 |
| phpbb_topics_watch | 0.16 |
| phpbb_ad_group | 0.02 |
| phpbb_sitelist | 0.00 |
| phpbb_ext | 0.02 |
| phpbb_config_text | 0.02 |
| phpbb_privmsgs_to | 0.23 |
| phpbb_teampage | 0.02 |
| phpbb_profile_lang | 0.00 |
| phpbb_reports | 0.07 |
| phpbb_sessions_keys | 0.80 |
| phpbb_attachments | 2.20 |
| phpbb_log | 6.47 |
| phpbb_oauth_tokens | 0.05 |
| phpbb_styles | 0.01 |
| phpbb_extensions | 0.00 |
| phpbb_config | 0.04 |
| phpbb_users | 10.78 |
| phpbb_search_wordmatch | 0.00 |
| phpbb_sphinx | 0.02 |
| phpbb_notification_types | 0.03 |
| phpbb_forums_track | 0.23 |
| phpbb_forums | 0.06 |
| phpbb_login_attempts | 10.12 |
| phpbb_thanks | 10.09 |
| phpbb_words | 0.00 |
| phpbb_oauth_accounts | 0.02 |
| phpbb_oauth_states | 0.05 |
| phpbb_captcha_questions | 0.00 |
| phpbb_search_results | 0.00 |
| phpbb_profile_fields_lang | 0.00 |
| phpbb_forums_watch | 0.00 |
| phpbb_posts | 155.20 |
| phpbb_reports_reasons | 0.00 |
| phpbb_acl_roles_data | 0.02 |
| phpbb_topics | 0.45 |
| phpbb_profile_fields | 0.01 |
| phpbb_acl_options | 0.01 |
| phpbb_groups | 0.01 |
| phpbb_warnings | 0.00 |
| phpbb_extension_groups | 0.00 |
| phpbb_user_group | 0.93 |
| phpbb_qa_confirm | 0.01 |
| phpbb_poll_votes | 0.02 |
| phpbb_topics_posted | 0.08 |
| phpbb_migrations | 0.09 |
| phpbb_smilies | 0.01 |
| phpbb_disallow | 0.00 |
| stk_reparse_bbcode_backup | 36.02 |
+---------------------------+---------+
80 rows in set (0.008 sec)
|
Смотрим размер всех таблиц всех баз данных и сортируем их по размеру:
1
2
3
4
| SELECT table_schema as `Database`, table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC;
|
В результате получаем:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| +--------------------+----------------------------------------------------+------------+
| Database | Table | Size in MB |
+--------------------+----------------------------------------------------+------------+
| xxx_forum2 | phpbb_sessions | 5197.88 |
| xxx_stat7v | piwik674_archive_blob_2020_01 | 1124.55 |
| xxx_wp7v936 | wp7srv_5_posts | 632.20 |
| xxx_stat7v | piwik674_log_visit | 566.77 |
| xxx_wp7v936 | wp7srv_5_postmeta | 440.06 |
| xxx_stat7v | piwik674_log_link_visit_action | 417.11 |
| yyy_sat943 | phpbb_posts | 310.22 |
| xxx_stat7srv | piwik674_session | 235.98 |
| yyy_sat943 | phpbb_search_wordmatch | 190.75 |
| xxx_forum2 | phpbb_posts | 155.20 |
| xxx_wp7v936 | wp7srv_4_posts | 85.66 |
| xxx_grab_842 | wp2345_posts | 60.64 |
| xxx_joomla | nz6ol_content | 57.70 |
| xxx_wp7v936 | wp7srv_4_postmeta | 55.61 |
| xxx_stat7v | piwik674_archive_blob_2020_04 | 43.09 |
| xxx_joomla | nz6ol_acymailing_geolocation | 38.58 |
| nnn_db | core_search_index | 36.60 |
...
+--------------------+----------------------------------------------------+------------+
1722 rows in set (0.767 sec)
|
