Here’s a daily count of servers that were off by ~1 second (+/- 100ms) during that day.
mysql> select date(ts) d, s.ip_version ip, count(distinct(server_id)) servers from log_scores inner join servers s on (s.id=log_scores.server_id) where ts > '2015-06-29 19:00' and abs(offset) > 0.9 and abs(offset) < 1.1 group by d, s.ip_version order by d; +------------+----+---------+ | d | ip | servers | +------------+----+---------+ | 2015-06-29 | v4 | 3 | | 2015-06-29 | v6 | 2 | | 2015-06-30 | v4 | 4 | | 2015-06-30 | v6 | 2 | | 2015-07-01 | v4 | 304 | | 2015-07-01 | v6 | 97 | | 2015-07-02 | v4 | 10 | | 2015-07-02 | v6 | 2 | | 2015-07-03 | v4 | 9 | | 2015-07-03 | v6 | 3 | +------------+----+---------+ 10 rows in set (13.43 sec) Curiously the number of servers that were “off” in the unexpected direction jumped, too. There’s just so much brokenness around the leap second and NTP servers are basically just about the only software that gets it right at all. Just about everything else manages to ignore it at best, basically. mysql> select date(ts) d, s.ip_version ip, count(distinct(server_id)) servers from log_scores inner join servers s on (s.id=log_scores.server_id) where ts > '2015-06-29 19:00' and offset < -0.9 and offset > -1.1 group by d, s.ip_version order by d; +------------+----+---------+ | d | ip | servers | +------------+----+---------+ | 2015-06-29 | v4 | 2 | | 2015-06-30 | v4 | 2 | | 2015-06-30 | v6 | 1 | | 2015-07-01 | v4 | 15 | | 2015-07-01 | v6 | 8 | | 2015-07-02 | v4 | 2 | | 2015-07-03 | v4 | 4 | | 2015-07-03 | v6 | 1 | +------------+----+---------+ 8 rows in set (13.35 sec) Most of the off-by-a-second stuff recovered within a few hours. mysql> select date(ts) d, hour(ts) h, count(distinct(server_id)) from log_scores where ts > '2015-06-29 19:00' and ts < '2015-07-02' and offset > 0.9 and offset < 1.1 group by d,h order by d,h; +------------+------+----------------------------+ | d | h | count(distinct(server_id)) | +------------+------+----------------------------+ | 2015-06-29 | 19 | 2 | | 2015-06-29 | 20 | 1 | | 2015-06-30 | 4 | 1 | | 2015-06-30 | 21 | 1 | | 2015-06-30 | 22 | 1 | | 2015-06-30 | 23 | 1 | | 2015-07-01 | 0 | 365 | | 2015-07-01 | 1 | 182 | | 2015-07-01 | 2 | 104 | | 2015-07-01 | 3 | 61 | | 2015-07-01 | 4 | 33 | | 2015-07-01 | 5 | 24 | | 2015-07-01 | 6 | 19 | | 2015-07-01 | 7 | 20 | | 2015-07-01 | 8 | 17 | | 2015-07-01 | 9 | 13 | | 2015-07-01 | 10 | 9 | | 2015-07-01 | 11 | 8 | | 2015-07-01 | 12 | 8 | | 2015-07-01 | 13 | 8 | | 2015-07-01 | 14 | 11 | | 2015-07-01 | 15 | 9 | | 2015-07-01 | 16 | 9 | | 2015-07-01 | 17 | 10 | | 2015-07-01 | 18 | 10 | | 2015-07-01 | 19 | 10 | | 2015-07-01 | 20 | 10 | | 2015-07-01 | 21 | 10 | | 2015-07-01 | 22 | 10 | | 2015-07-01 | 23 | 9 | +------------+------+----------------------------+ 30 rows in set (3.21 sec) Finally if I only count servers included in the NTP Pool DNS then all but a few recovered very quickly (as in the rest of the broken stuff got kicked out): mysql> select date(ts) d, hour(ts) h, count(distinct(server_id)) from log_scores where ts > '2015-06-29 19:00' and ts < '2015-07-02' and offset > 0.9 and offset < 1.1 and score > 10 group by d,h order by d,h; +------------+------+----------------------------+ | d | h | count(distinct(server_id)) | +------------+------+----------------------------+ | 2015-06-30 | 23 | 1 | | 2015-07-01 | 0 | 344 | | 2015-07-01 | 1 | 6 | | 2015-07-01 | 3 | 1 | | 2015-07-01 | 4 | 1 | | 2015-07-01 | 7 | 1 | | 2015-07-01 | 8 | 1 | | 2015-07-01 | 14 | 2 | +------------+------+----------------------------+ 8 rows in set (3.32 sec) Ask _______________________________________________ pool mailing list [email protected] http://lists.ntp.org/listinfo/pool
