{"id":958,"date":"2016-03-25T08:45:18","date_gmt":"2016-03-25T03:45:18","guid":{"rendered":"http:\/\/alexeyka.zantsev.com\/?p=958"},"modified":"2016-03-25T08:52:11","modified_gmt":"2016-03-25T03:52:11","slug":"sql-get-the-database-size","status":"publish","type":"post","link":"https:\/\/alexeyka.zantsev.com\/?p=958","title":{"rendered":"SQL: get the database size"},"content":{"rendered":"<p>I&#8217;m going on writing primitive posts about the fascinating world of relational database management systems.  :)<\/p>\n<p>MySQL, size of all databases:<\/p>\n<pre>\r\nmysql> SELECT table_schema \\\r\n       \"Database name\", \\\r\n       sum( data_length + index_length ) \/ 1024 \/ 1024 \\\r\n       \"Data Base Size in MB\" FROM \\\r\n       information_schema.TABLES GROUP BY table_schema;\r\n+--------------------+----------------------+\r\n| Database name      | Data Base Size in MB |\r\n+--------------------+----------------------+\r\n| fluxbb             |           0.04585648 |\r\n| information_schema |           0.00781250 |\r\n| mysql              |           0.60614872 |\r\n| wordpress          |           2.22493362 |\r\n+--------------------+----------------------+\r\n4 rows in set (0.13 sec)\r\n<\/pre>\n<p>PostgreSQL, size of all databases:<\/p>\n<pre>\r\ncdr=> SELECT pg_database.datname,pg_size_pretty(pg_database_size(pg_database.datname)) \\\r\n      AS size FROM pg_database;\r\n  datname  |  size   \r\n-----------+---------\r\n template1 | 6705 kB\r\n template0 | 6697 kB\r\n postgres  | 6820 kB\r\n cdr       | 28 GB\r\n<\/pre>\n<p>Size of &#8216;acme_cdr&#8217; table:<\/p>\n<pre>\r\ncdr=> SELECT pg_size_pretty(pg_total_relation_size('acme_cdr'));\r\n pg_size_pretty \r\n----------------\r\n 28 GB\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>I&#8217;m going on writing primitive posts about the fascinating world of relational database management systems. :) MySQL, size of all databases: mysql> SELECT table_schema \\ &#8220;Database name&#8221;, \\ sum( data_length + index_length ) \/ 1024 \/ 1024 \\ &#8220;Data Base Size in MB&#8221; FROM \\ information_schema.TABLES GROUP BY table_schema; +&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;+&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;-+ | Database name | Data [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[80,94,93],"class_list":["post-958","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-mysql","tag-postgresql","tag-sql"],"_links":{"self":[{"href":"https:\/\/alexeyka.zantsev.com\/index.php?rest_route=\/wp\/v2\/posts\/958","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/alexeyka.zantsev.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/alexeyka.zantsev.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/alexeyka.zantsev.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/alexeyka.zantsev.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=958"}],"version-history":[{"count":6,"href":"https:\/\/alexeyka.zantsev.com\/index.php?rest_route=\/wp\/v2\/posts\/958\/revisions"}],"predecessor-version":[{"id":964,"href":"https:\/\/alexeyka.zantsev.com\/index.php?rest_route=\/wp\/v2\/posts\/958\/revisions\/964"}],"wp:attachment":[{"href":"https:\/\/alexeyka.zantsev.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=958"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/alexeyka.zantsev.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=958"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/alexeyka.zantsev.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=958"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}