Обсуждение: "Time of latest checkpoint" stays too old on both master and slave
Current time is 10:44. pg_controldata shows on both on master & slave server which uses streaming replication: Time of latest checkpoint: Sun Jun 30 07:49:18 2019 So it was almost 3 hours ago. There are always some heavy writes and a new WAL file in the pg_wal/ directory is created every few minutes. checkpoint_timeout is 20min so it should have triggered long ago. checkpoint_timeout = 20min #5min # range 30s-1d max_wal_size = 8GB min_wal_size = 80MB checkpoint_completion_target = 0.9 hot_standby is enabled on the slave, hot_standby_feedback is off not to bloat the master, hot_standby_streaming_delay is 30min. Experiencing this long delay after the upgrade (via dump/restore) from PG 9.6 to 11.4. Thanks for any tips.
Damn. Sorry, and please disregard my post. The master server had the wrong time. Not wrong TZ, simply wrong time. $ date Sun Jun 30 08:34:52 +04 2019 while it's currently 10:58
On 06/30/2019 10:59 AM, rihad wrote: > Damn. Sorry, and please disregard my post. The master server had the > wrong time. Not wrong TZ, simply wrong time. > > $ date > Sun Jun 30 08:34:52 +04 2019 > > while it's currently 10:58 There's a weird problem, even when the time is initially set by openntpd it keeps lagging by one second every few seconds: $ sudo /usr/local/etc/rc.d/openntpd restart Performing sanity check on openntpd configuration: configuration OK Stopping openntpd. Waiting for PIDS: 85893. Performing sanity check on openntpd configuration: configuration OK Starting openntpd. $ ssh good-server date; date Sun Jun 30 11:04:17 +04 2019 Sun Jun 30 11:04:17 +04 2019 $ ssh good-server date; date Sun Jun 30 11:04:25 +04 2019 Sun Jun 30 11:04:24 +04 2019 $ ssh good-server date; date Sun Jun 30 11:04:32 +04 2019 Sun Jun 30 11:04:31 +04 2019 $ ssh good-server date; date Sun Jun 30 11:04:39 +04 2019 Sun Jun 30 11:04:37 +04 2019 $ ssh good-server date; date Sun Jun 30 11:04:48 +04 2019 Sun Jun 30 11:04:45 +04 2019 Really weird. But this isn't a PG problem at all, just a server glitch maybe. sorry again.
>>>>> "rihad" == rihad <rihad@mail.ru> writes: rihad> There's a weird problem, even when the time is initially set by rihad> openntpd it keeps lagging by one second every few seconds: rihad> $ sudo /usr/local/etc/rc.d/openntpd restart What OS is this? I've seen this kind of thing with FreeBSD where the kernel timecounter source has been chosen badly (i.e. choosing TSC when the TSC isn't actually invariant enough). Forcing TSC not to be used fixes it. The configuration I've especially noticed it on is when running in a VM with a single virtual CPU. -- Andrew (irc:RhodiumToad)
On 06/30/2019 09:45 PM, Andrew Gierth wrote: >>>>>> "rihad" == rihad <rihad@mail.ru> writes: > rihad> There's a weird problem, even when the time is initially set by > rihad> openntpd it keeps lagging by one second every few seconds: > > rihad> $ sudo /usr/local/etc/rc.d/openntpd restart > > What OS is this? > > I've seen this kind of thing with FreeBSD where the kernel timecounter > source has been chosen badly (i.e. choosing TSC when the TSC isn't > actually invariant enough). Forcing TSC not to be used fixes it. The > configuration I've especially noticed it on is when running in a VM with > a single virtual CPU. > Exactly. You're right. It's on FreeBSD 11.2. After some googling earlier I changed kern.timecounter.hardware=HPET and solved the problem. The default chosen value TSC-low seems to misbehave for this box, although it works on others (running the same FreeBSD version).