GA

2020/06/16

information_schema.tables を定期的に貯めてASCIIグラフにしている

最近(でもないけど) 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 だけで案外楽しんでいる毎日でした。

0 件のコメント :

コメントを投稿