Обсуждение: Killed background writer process
Hi Team,
I've killed background writer process for a testing purpose. I found that after sometime it automatically came up with a new PID without me doing anything from my end.
How postgres automatically brings it up? Is there any impact on the Database and connections?
Thanks,
Raj Kumar Narendiran
There is a separate server process called the background writer, whose function is to issue writes of “dirty” (new or modified) shared buffers. When the number of clean shared buffers appears to be insufficient, the background writer writes some dirty buffers to the file system and marks them as clean. This reduces the likelihood that server processes handling user queries will be unable to find clean buffers and have to write dirty buffers themselves. However, the background writer does cause a net overall increase in I/O load, because while a repeatedly-dirtied page might otherwise be written only once per checkpoint interval, the background writer might write it several times as it is dirtied in the same interval. The parameters discussed in this subsection can be used to tune the behavior for local needs.
I believe the postmaster checks if it is running, if not it starts again i think.
in source.
The same goes for wal writer as well.
On Fri, 4 Jun 2021 at 14:55, Raj kumar <rajkumar820999@gmail.com> wrote:
Hi Team,I've killed background writer process for a testing purpose. I found that after sometime it automatically came up with a new PID without me doing anything from my end.How postgres automatically brings it up? Is there any impact on the Database and connections?Thanks,Raj Kumar Narendiran
Thanks,
Vijay
Mumbai, India
On Fri, 2021-06-04 at 14:54 +0530, Raj kumar wrote: > I've killed background writer process for a testing purpose. I found that after sometime > it automatically came up with a new PID without me doing anything from my end. > > How postgres automatically brings it up? Is there any impact on the Database and connections? The postmaster process keeps track of its children and restarts any background process that died. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
not sure if this explains well,
window1 :
postgres=# begin transaction isolation level serializable;
BEGIN
postgres=*# delete from t;
DELETE 200
----- wait while you kill bg writer, should not be impacted.
postgres=*# commit;
COMMIT
in a parallel window, strace the pid of background writer.
window2:
strace -p 13665
strace: Process 13665 attached
select(8, [5 6 7], NULL, NULL, {tv_sec=26, tv_usec=338078}) = ? ERESTARTNOHAND (To be restarted if no handler)
--- SIGCHLD {si_signo=SIGCHLD, si_code=CLD_EXITED, si_pid=13668, si_uid=1001, si_status=0, si_utime=0, si_stime=1} ---
wait4(-1, [{WIFEXITED(s) && WEXITSTATUS(s) == 0}], WNOHANG, NULL) = 13668
wait4(-1, 0x7fffa51c2474, WNOHANG, NULL) = 0
rt_sigreturn({mask=[]}) = -1 EINTR (Interrupted system call)
rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT SYS RTMIN RT_1], NULL, 8) = 0
clone(child_stack=NULL, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|SIGCHLD, child_tidptr=0x7fabf885c610) = 13832
openat(AT_FDCWD, "postmaster.pid", O_RDWR) = 9
read(9, "13665\n/opt/postgresql-13/pgsql/d"..., 8191) = 88
close(9) = 0
getpid() = 13665
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
select(8, [5 6 7], NULL, NULL, {tv_sec=60, tv_usec=0}
window3:
postgres@go:~/postgresql-13.2/src$ ps -aef | grep postgres
root 13607 13594 0 15:04 pts/1 00:00:00 su - postgres
postgres 13608 13607 0 15:04 pts/1 00:00:00 -bash
root 13657 13537 0 15:17 pts/0 00:00:00 su - postgres
postgres 13658 13657 0 15:17 pts/0 00:00:00 -bash
postgres 13665 1 0 15:17 ? 00:00:00 /opt/postgresql-13/local/bin/postgres -D /opt/postgresql-13/pgsql/data
postgres 13667 13665 0 15:17 ? 00:00:00 postgres: checkpointer
postgres 13668 13665 0 15:17 ? 00:00:00 postgres: background writer
postgres 13669 13665 0 15:17 ? 00:00:00 postgres: walwriter
postgres 13670 13665 0 15:17 ? 00:00:00 postgres: stats collector
postgres 13671 13665 0 15:17 ? 00:00:00 postgres: logical replication launcher
postgres 13672 13658 0 15:17 pts/0 00:00:00 psql
postgres 13673 13665 0 15:17 ? 00:00:00 postgres: postgres postgres [local] idle in transaction
window4:
postgres@go:~$ kill 13668
On Fri, 4 Jun 2021 at 15:16, Vijaykumar Jain <vijaykumarjain.github@gmail.com> wrote:
There is a separate server process called the background writer, whose function is to issue writes of “dirty” (new or modified) shared buffers. When the number of clean shared buffers appears to be insufficient, the background writer writes some dirty buffers to the file system and marks them as clean. This reduces the likelihood that server processes handling user queries will be unable to find clean buffers and have to write dirty buffers themselves. However, the background writer does cause a net overall increase in I/O load, because while a repeatedly-dirtied page might otherwise be written only once per checkpoint interval, the background writer might write it several times as it is dirtied in the same interval. The parameters discussed in this subsection can be used to tune the behavior for local needs.I believe the postmaster checks if it is running, if not it starts again i think.in source.The same goes for wal writer as well.On Fri, 4 Jun 2021 at 14:55, Raj kumar <rajkumar820999@gmail.com> wrote:Hi Team,I've killed background writer process for a testing purpose. I found that after sometime it automatically came up with a new PID without me doing anything from my end.How postgres automatically brings it up? Is there any impact on the Database and connections?Thanks,Raj Kumar Narendiran--Thanks,VijayMumbai, India
Thanks,
Vijay
Mumbai, India
Hi,
Thanks Laurenz and Vijay.. it really helps!
Thanks & Regards,
Raj Kumar Narendiran
On Fri, 4 Jun 2021, 15:22 Vijaykumar Jain, <vijaykumarjain.github@gmail.com> wrote:
not sure if this explains well,window1 :postgres=# begin transaction isolation level serializable;BEGINpostgres=*# delete from t;DELETE 200----- wait while you kill bg writer, should not be impacted.postgres=*# commit;COMMITin a parallel window, strace the pid of background writer.window2:strace -p 13665strace: Process 13665 attachedselect(8, [5 6 7], NULL, NULL, {tv_sec=26, tv_usec=338078}) = ? ERESTARTNOHAND (To be restarted if no handler)--- SIGCHLD {si_signo=SIGCHLD, si_code=CLD_EXITED, si_pid=13668, si_uid=1001, si_status=0, si_utime=0, si_stime=1} ---wait4(-1, [{WIFEXITED(s) && WEXITSTATUS(s) == 0}], WNOHANG, NULL) = 13668wait4(-1, 0x7fffa51c2474, WNOHANG, NULL) = 0rt_sigreturn({mask=[]}) = -1 EINTR (Interrupted system call)rt_sigprocmask(SIG_SETMASK, ~[ILL TRAP ABRT BUS FPE SEGV CONT SYS RTMIN RT_1], NULL, 8) = 0clone(child_stack=NULL, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|SIGCHLD, child_tidptr=0x7fabf885c610) = 13832openat(AT_FDCWD, "postmaster.pid", O_RDWR) = 9read(9, "13665\n/opt/postgresql-13/pgsql/d"..., 8191) = 88close(9) = 0getpid() = 13665rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0select(8, [5 6 7], NULL, NULL, {tv_sec=60, tv_usec=0}window3:postgres@go:~/postgresql-13.2/src$ ps -aef | grep postgresroot 13607 13594 0 15:04 pts/1 00:00:00 su - postgrespostgres 13608 13607 0 15:04 pts/1 00:00:00 -bashroot 13657 13537 0 15:17 pts/0 00:00:00 su - postgrespostgres 13658 13657 0 15:17 pts/0 00:00:00 -bashpostgres 13665 1 0 15:17 ? 00:00:00 /opt/postgresql-13/local/bin/postgres -D /opt/postgresql-13/pgsql/datapostgres 13667 13665 0 15:17 ? 00:00:00 postgres: checkpointerpostgres 13668 13665 0 15:17 ? 00:00:00 postgres: background writerpostgres 13669 13665 0 15:17 ? 00:00:00 postgres: walwriterpostgres 13670 13665 0 15:17 ? 00:00:00 postgres: stats collectorpostgres 13671 13665 0 15:17 ? 00:00:00 postgres: logical replication launcherpostgres 13672 13658 0 15:17 pts/0 00:00:00 psqlpostgres 13673 13665 0 15:17 ? 00:00:00 postgres: postgres postgres [local] idle in transactionwindow4:postgres@go:~$ kill 13668On Fri, 4 Jun 2021 at 15:16, Vijaykumar Jain <vijaykumarjain.github@gmail.com> wrote:There is a separate server process called the background writer, whose function is to issue writes of “dirty” (new or modified) shared buffers. When the number of clean shared buffers appears to be insufficient, the background writer writes some dirty buffers to the file system and marks them as clean. This reduces the likelihood that server processes handling user queries will be unable to find clean buffers and have to write dirty buffers themselves. However, the background writer does cause a net overall increase in I/O load, because while a repeatedly-dirtied page might otherwise be written only once per checkpoint interval, the background writer might write it several times as it is dirtied in the same interval. The parameters discussed in this subsection can be used to tune the behavior for local needs.I believe the postmaster checks if it is running, if not it starts again i think.in source.The same goes for wal writer as well.On Fri, 4 Jun 2021 at 14:55, Raj kumar <rajkumar820999@gmail.com> wrote:Hi Team,I've killed background writer process for a testing purpose. I found that after sometime it automatically came up with a new PID without me doing anything from my end.How postgres automatically brings it up? Is there any impact on the Database and connections?Thanks,Raj Kumar Narendiran--Thanks,VijayMumbai, India--Thanks,VijayMumbai, India