最近（でもないけど） information_schema.tables の中身を1日1回程度取得してMySQLに突っ込んでいる。
/*!80013 SET SESSION information_schema_stats_expiry = 0; */
SELECT
table_schema AS table_schema,
table_name AS table_name,
table_rows AS table_rows,
data_length AS data_length,
index_length AS index_length,
data_free AS data_free,
engine AS engine,
NOW() AS last_update
FROM
information_schema.tables
WHERE
table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys') AND
table_type = 'BASE TABLE'
ORDER BY
data_length + index_length DESC;
CREATE TABLE `table_status_info` (
`seq` bigint unsigned NOT NULL AUTO_INCREMENT,
`ipaddr` varchar(15) NOT NULL,
`port` smallint unsigned NOT NULL,
`table_schema` varchar(255) NOT NULL,
`table_name` varchar(255) NOT NULL,
`table_rows` bigint unsigned NOT NULL,
`data_length` bigint unsigned NOT NULL,
`index_length` bigint unsigned NOT NULL,
`data_free` bigint unsigned NOT NULL,
`engine` varchar(32) NOT NULL,
`last_update` datetime NOT NULL,
PRIMARY KEY (`seq`),
KEY `idx_lastupdate` (`last_update`),
KEY `table_status_info_ibfk_1` (`ipaddr`,`port`),
CONSTRAINT `table_status_info_ibfk_1` FOREIGN KEY (`ipaddr`, `port`) REFERENCES `instance_info` (`ipaddr`, `port`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=211 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO table_status_info
(ipaddr, port, data_free, data_length, engine, index_length, last_update, table_name, table_rows, table_schema)
VALUES
('localhost', '3306', '0', '143310848', 'InnoDB', '9977856', '2020-06-16 10:47:08', 'stock', '391992', 'tpcc'),
..
ON DUPLICATE KEY UPDATE
data_free = VALUES(data_free), /* この書き方は8.0.19でdeprecated.. */
data_length = VALUES(data_length),
engine = VALUES(engine),
index_length = VALUES(index_length),
last_update = VALUES(last_update),
table_name = VALUES(table_name),
table_rows = VALUES(table_rows),
table_schema = VALUES(table_schema);
採取自体は yt-collect を使ってやってる（というよりは、これをやるためにyt-collectを作ったのだから）
$ yt-collect -h${ipaddr} -P${port} -u${watch_user} -p${watch_password} --output=sql --sql-update | mysql -u${local_user} -S${local_socket} -p${local_password} admintool
これに いくつかのView を噛ませて、こんなSQLで対象を引っ張り出す。
WITH maybe_maintained AS (
SELECT DISTINCT hostname, datadir, table_schema, table_name
FROM adminview.table_status_list_analyze_90
WHERE _diff < 0) -- 差分がマイナスになるタイミングがあるってことはたぶんお掃除バッチが仕事してる
SELECT DISTINCT
hostname, datadir, table_schema, table_name, CAST(_first AS UNSIGNED) AS _fist, CAST(_last AS UNSIGNED) AS _last
FROM
adminview.table_status_list_analyze_90
WHERE
_first > 100000 AND -- 90日くらい前の時点で10万行を超えていて
_last > _first * 1.05 AND -- 90日くらい経って5%以上増えてる
(hostname, datadir, table_schema, table_name) NOT IN (SELECT hostname, datadir, table_schema, table_name FROM maybe_maintained)
AND
(ipaddr, port) NOT IN (SELECT ipaddr, port FROM admintool.slave_info) -- スレーブを除外してマスターだけチェック
set terminal dumb をおぼえて gnuplot のグラフをASCIIで出せるようになってからめっきり gnuplot 派になった(was. redashでグラフだけ作る、グラフだけExcel)
sql=$(cat << EOF
SELECT _date, table_rows
FROM adminview.table_status_list_analyze_90
WHERE (hostname, datadir, table_schema, table_name) = ('$hostname', '$datadir', '$table_schema', '$table_name')
ORDER BY _date
EOF
)
mysql -u${local_user} -S${local_socket} -p${local_password} -e "${sql}" | gnuplot -e "set terminal dumb; set xdata time; set timefmt '%Y-%m-%d'; set format y '%10.0f'; plot '< cat -' using 1:2 with lines title ''"
多少なりともお掃除がされているもの ( WITH maybe_maintained で指定してる _diff < 0 になるタイミングがあるやつ ) はこんな感じになる。
12300000 ++---+---+----+---+----+---+----+---+----+---+----+---+----+--++
+ ***** + + + + + +
12200000 ++ *** * ++
| ** |
12100000 ++ ** ++
| * * |
12000000 ++ ** ++
| * * * |
11900000 ++ * * * * ++
| * * * ** |
| * * * * * |
11800000 ++ * ** * * * ++
| *** ** * * * |
11700000 ++ ** ** * * * ++
| ** * * ***** |
11600000 ++ ** * * ++
| * * |
11500000 ++ * * ++
+ + + + + + * + +
11400000 ++---+---+----+---+----+---+----+---+----+---+-*--+---+----+--++
03/14 03/28 04/11 04/25 05/09 05/23 06/06 06/20
増えているにしても「消し込まれている上で増えてる」ので、必要な容量なのであろう。ということで対象からは除外。
クエリー全体で引っ掛かるのはこんな線形に増えているやつか（要らないレコードは消してね！）
12800000 ++---+---+----+---+----+---+----+---+----+---+----+---+----**-++
+ + + + + + + *** +
12700000 ++ **** ++
| *** |
12600000 ++ ** ++
| *** |
12500000 ++ ***** ++
| *** |
12400000 ++ *** ++
| *** |
| **** |
12300000 ++ **** ++
| ** |
12200000 ++ *** ++
| *** |
12100000 ++ ** ++
| * |
12000000 ++ *** ++
+ ***+ + + + + + +
11900000 ++---+---+----+---+----+---+----+---+----+---+----+---+----+--++
03/14 03/28 04/11 04/25 05/09 05/23 06/06 06/20
何か良いことでもあったのかな、みたいなグラフ。
45500000 ++---+---+----+---+----+---+----+---+----+---+----+---+----+--++
+ + + + + + + +
45000000 ++ ************** ++
| * |
44500000 ++ * ++
| * |
44000000 ++ * ++
| * |
43500000 ++ * ++
43000000 ++ * ++
| * |
42500000 ++ * ++
| * |
42000000 ++ * ++
| * |
41500000 ++ ********* ++
| ************ |
41000000 ++ **************** ++
+ + + + + + + +
40500000 ++---+---+----+---+----+---+----+---+----+---+----+---+----+--++
03/14 03/28 04/11 04/25 05/09 05/23 06/06 06/20
Window関数とWITH句と gnuplot だけで案外楽しんでいる毎日でした。
