During the troubleshooting that happened at T252324: Tendril mysql is stalling I found that the table global_status_log_5m had data from 2015:
mysql:root@localhost [tendril]> select min(stamp),max(stamp) from global_status_log_5m; +---------------------+---------------------+ | min(stamp) | max(stamp) | +---------------------+---------------------+ | 2015-06-19 11:40:00 | 2020-05-10 10:40:00 | +---------------------+---------------------+ 1 row in set (0.11 sec) mysql:root@localhost [tendril]> select @stamp := now() - interval 7 day; +----------------------------------+ | @stamp := now() - interval 7 day | +----------------------------------+ | 2020-05-03 11:00:35 | +----------------------------------+ 1 row in set (0.00 sec) mysql:root@localhost [tendril]> select * from global_status_log_5m where stamp < @stamp limit 2; +-----------+---------------------+---------+-------------+ | server_id | stamp | name_id | value | +-----------+---------------------+---------+-------------+ | 1082 | 2015-06-19 11:40:00 | 352 | 13561583583 | | 1209 | 2015-06-19 11:40:00 | 348 | 0 | +-----------+---------------------+---------+-------------+ 2 rows in set (0.10 sec)
That table was causing contention on tendril as the table had grown to an insane amount of rows:
mysql:root@localhost [(none)]> show explain for 291612; +------+-------------+----------------------+-------+---------------+------+---------+------+-------------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------------------+-------+---------------+------+---------+------+-------------+-------------+ | 1 | SIMPLE | global_status_log_5m | index | NULL | i2 | 8 | NULL | 87877089565 | Using index | +------+-------------+----------------------+-------+---------------+------+---------+------+-------------+-------------+ 1 row in set, 1 warning (0.00 sec)
There is an event that is supposed to purge it every 7 days, but it wasn't working (my guess is just because it is unable to get a lock there):
mysql:root@localhost [tendril]> show create event tendril_purge_global_status_log_5m;
+------------------------------------+----------+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Event | sql_mode | time_zone | Create Event |
+------------------------------------+----------+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tendril_purge_global_status_log_5m | | SYSTEM | CREATE DEFINER=`root`@`208.80.154.151` EVENT `tendril_purge_global_status_log_5m` ON SCHEDULE EVERY 5 MINUTE STARTS '2014-05-23 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO n
if (get_lock('tendril_purge_global_status_log_5m', 1) = 0) then
signal sqlstate value '45000' set message_text = 'get_lock';
end if;
select @stamp := now() - interval 7 day;
delete from global_status_log_5m where stamp < @stamp limit 10000;
do release_lock('tendril_purge_global_status_log_5m');
end | utf8 | utf8_general_ci | latin1_swedish_ci |
+------------------------------------+----------+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)Even if it worked, it would have probably never finished as the table was insanely big.
We should probably make sure it works or create a more radical approach, something along the lines of:
- Stops the event scheduler
- Waits for the output to be clean
- Perform the delete + optimize
- Starts the event scheduler
The table global_status_log was also massive for just holding a month's of data, so we might want to purge it more frequently. It doesn't make any sense to keep that amount of days:
mysql:root@localhost [tendril]> show explain for 283543; +------+-------------+-------------------+-------+---------------+---------+---------+------+-------------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------------------+-------+---------------+---------+---------+------+-------------+-------------+ | 1 | SIMPLE | global_status_log | index | NULL | PRIMARY | 24 | NULL | 10164518550 | Using index | +------+-------------+-------------------+-------+---------------+---------+---------+------+-------------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql:root@localhost [tendril]> select min(stamp),max(stamp) from global_status_log; +---------------------+---------------------+ | min(stamp) | max(stamp) | +---------------------+---------------------+ | 2020-04-11 00:00:00 | 2020-05-10 10:44:52 | +---------------------+---------------------+ 1 row in set (0.01 sec)
So I think we should also set up maybe a weekly cronjob purging that table too. We don't use it for any historic things anyways so it doesn't make any sense to let it grow that much.