I don\u2019t know how common is this problem, but it is good to know from time to time about which tables how many disk space needed at a certain time. For example, you can catch an amok running software part which writes your database full. Or, \u2013 as you will see so \u2013 you can catch up some code what doesn\u2019t work as excepted.<\/p>\n
So, let’s start at the beginning. You wanna to know how big are your tables, and you need to know how many data gets there day-by-day (or minute-by-minute. or whatever).<\/p>\n
<\/p>\n
You can query information_schema.tables for table sizes, this is good, but you won\u2019t be happy just with these results, because you won\u2019t find any time-based changes, so you have to store the data.<\/p>\n
So first, we have to create a table to store this historical data:<\/p>\n
CREATE TABLE `test`.`table_sizes` (\n `id` int(10) NOT NULL AUTO_INCREMENT,\n `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n `tablename` varchar(50) DEFAULT NULL,\n `datasize` decimal(10,2) DEFAULT NULL,\n `indexsize` decimal(10,2) DEFAULT NULL,\n `totalsize` decimal(10,2) DEFAULT NULL,\nPRIMARY KEY (`id`);\n<\/code><\/pre>\nThe next step is to provide some data here. For this, I made a script called \u2018monitor_space_collector.pl<\/a>\u2018. This script can be run either manually, or as a cronjob. Needless to say, your data resolution depends on how often you will run this, but the size of your table depends on this too (and of course the number of your tables what you wanna to keep track.) On Kinja, I run this script as a cronjob in every 15 minutes.<\/p>\nIf you made this, and your data is monitored, you can query this table for example like this:<\/p>\n
SELECT tablename, max(totalsize) - min(totalsize) AS r\nFROM\n table_sizes\nWHERE\n `date` > '2013-07-10 05:55:00'\nGROUP BY\n tablename HAVING r > 0;\n<\/code><\/pre>\nIt will show you the results you wanted, (in this case the table size changes from 2013-07-10 05:55:00 till now, and show only that tables which size are changed.<\/p>\n
Well, I don\u2019t like write SQL queries unnecessarily all the time when I needed the same data, so I wrote instead a query tool named monitor_space_qt.pl<\/a>. This query tool has two major modes: when you give the -w option you must provide a table name, (even with % wildcard) and the tool will list all the entries from your table_sizes table about the queried table(s). You can fine-tune the data you got with -f (from the time) and -t (\u2019till time). When you don\u2019t use -w, then it will print the size changes of the tables in megabytes from the start of monitoring until now (or, of course, you can fine-tune the date again with -f and -t)<\/p>\n[root@sql-master2 vividcortex]# \/usr\/local\/bin\/monitor_space_qt.pl -h\nUsage: .\/msqt.pl [ -h ] [ -u user ] [ -p password ]\n -h this help screen\n -u username username with connect to mysql. Defaults 'root'\n -p password password with connect to mysql. If not provided,asks for it.\n -f date from date (mysql date format)\n -t date to date (mysql date format\n -w tablename table to check\n -e don't write headers (useful for sorting, and using output in pipeline)\n<\/code><\/pre>\nLet’s see an example! As I mentioned before in these times we have some fight of database sizes \u2013 the FusionIO cards we are using are pretty expensive \u2013 so I made this to figure out, how can I spare some data. So I made this utility, left the monitor_space_collector to collect data for a few days, and I started to look the results I\u2019ve got this:<\/p>\n
[root@sql-master2]# \/usr\/local\/bin\/monitor_space_qt.pl -e | sort -n -k 2\n[...]\nkinja.stats_day 630.00\nkinja.post 1022.00\nkinja.oid 1856.00\nkinja.sessiontoken 2072.02\nsso.sessions2 4697.99\n<\/code><\/pre>\n(notice the fine -e option, I didn\u2019t print out any header so I won’t be a mess when I sorted it with sort! cool, right?)<\/em><\/p>\nSo, I started to figure out which table is for that and is it okay, to have so many size changes. I found the kinja.oid table, what is used for oid generation \u2013 and it should hold only the current maximum of oid number. In fact, there is no delete job on that table, so it\u2019s size hits the moon! It is now ~9 Gb of data and counting. So here is the benefit of this tool: I know that I have to delete from this table and run an optimize table command on this, and I have freed up 9 Gb\u2019s of data.<\/p>\n
You can find the all the files on GitHub under my Utility<\/a> repository (where all of my MySQL utilities are) under the monitor_space<\/a> directory. Use it if you think it is useful for you.<\/p>\n","protected":false},"excerpt":{"rendered":"I don\u2019t know how common is this problem, but it is good to know from time to time about which tables how many disk space needed at a certain time. For example, you can catch an amok running software part which writes your database full. Or, \u2013 as you will see so \u2013 you can […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[134],"tags":[144,126,234],"class_list":["post-17","post","type-post","status-publish","format-standard","hentry","category-tech-en","tag-mysql-en","tag-oldblog-en","tag-perl-en"],"_links":{"self":[{"href":"https:\/\/banyek.net\/index.php?rest_route=\/wp\/v2\/posts\/17","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/banyek.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/banyek.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/banyek.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/banyek.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=17"}],"version-history":[{"count":1,"href":"https:\/\/banyek.net\/index.php?rest_route=\/wp\/v2\/posts\/17\/revisions"}],"predecessor-version":[{"id":446,"href":"https:\/\/banyek.net\/index.php?rest_route=\/wp\/v2\/posts\/17\/revisions\/446"}],"wp:attachment":[{"href":"https:\/\/banyek.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=17"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/banyek.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=17"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/banyek.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=17"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}