Re: Patch to improve reliability of postgresql on linux nfs

George,

I'm quoting you here because in the version of your email which got
posted to the list your whole explanation got put below the patch text,
making it hard to find the justification for the patch. Follows:

> I run a number of postgresql installations on NFS and on the whole I find this to be very reliable. I have however run into a few issues when there is concurrent writes from multiple processes.
>
> I see errors such as the following:
>
> 2011-07-31 22:13:35 EST postgres postgres [local] LOG: connection authorized: user=postgres database=postgres
> 2011-07-31 22:13:35 EST ERROR: could not write block 1 of relation global/2671: wrote only 4096 of 8192 bytes
> 2011-07-31 22:13:35 EST HINT: Check free disk space.
> 2011-07-31 22:13:35 EST CONTEXT: writing block 1 of relation global/2671
> 2011-07-31 22:13:35 EST [unknown] [unknown] LOG: connection received: host=[local]
>
> I have also seen similar errors coming out of the WAL writer, however they occur at the level PANIC, which is a little more drastic.
>
> After spending some time with debug logging turned on and even more time staring at strace, I believe this occurs when one process was writing to a data file and it received a SIGINT from another process, eg:
> (These logs are from another similar run)
>
> [pid 1804] <... fsync resumed> ) = 0
> [pid 10198] kill(1804, SIGINT <unfinished ...>
> [pid 1804] lseek(3, 4915200, SEEK_SET) = 4915200
> [pid 1804] write(3, "c\320\1\0\1\0\0\0\0\0\0\0\0\0K\2\6\1\0\0\0\0\373B\0\0\0\0\2\0m\0"..., 32768 <unfinished ...>
> [pid 10198] <... kill resumed> ) = 0
> [pid 1804] <... write resumed> ) = 4096
> [pid 1804] --- SIGINT (Interrupt) @ 0 (0) ---
> [pid 1804] rt_sigreturn(0x2) = 4096
> [pid 1804] write(2, "\0\0\373\0\f\7\0\0t2011-08-30 20:29:52.999"..., 260) = 260
> [pid 1804] rt_sigprocmask(SIG_UNBLOCK, [ABRT], <unfinished ...>
> [pid 1802] <... select resumed> ) = 1 (in [5], left {0, 999000})
> [pid 1804] <... rt_sigprocmask resumed> NULL, 8) = 0
> [pid 1804] tgkill(1804, 1804, SIGABRT) = 0
> [pid 1802] read(5, <unfinished ...>
> [pid 1804] --- SIGABRT (Aborted) @ 0 (0) ---
> Process 1804 detached
>
> After finding this, I came up with the following test case which easily replicated our issue:
>
> #!/bin/bash
>
> name=$1
> number=1
> while true; do
> /usr/bin/psql -c "CREATE USER \"$name$number\" WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN PASSWORD 'pass';"
> /usr/bin/createdb -E UNICODE -O $name$number $name$number
> if `grep -q PANIC /data/postgresql/data/pg_log/*`; then
> exit
> fi
> let number=$number+1
> done
>
> When I run a single copy of this script, I have no issues, however when I start up a few more copies to simultaneously hit the DB, it crashes quiet quickly - usually within 20 or 30 seconds.
>
> After looking through the code I found that when postgres calls write() it doesn't retry. In order to address the issue with the PANIC in the WAL writer I set the sync method to o_sync which solved the issue in that part of the code, however I was still seeing failures in other areas of the code (such as the FileWrite function). Following this, I spoke to an NFS guru who pointed out that writes under linux are not guaranteed to complete unless you open up O_SYNC or similar on the file handle. I had a look in the libc docs and found this:
>
> http://www.gnu.org/s/libc/manual/html_node/I_002fO-Primitives.html
>
> ----
> The write function writes up to size bytes from buffer to the file with descriptor filedes. The data in buffer is not necessarily a character string and a null character is output like any other character.
>
> The return value is the number of bytes actually written. This may be size, but can always be smaller. Your program should always call write in a loop, iterating until all the data is written.
> ----
>
> After finding this, I checked a number of other pieces of software that we see no issues with on NFS (such as the JVM) for their usage of write(). I confirmed they write in a while loop and set about patching the postgres source.
>
> I have made this patch against 8.4.8 and confirmed that it fixes the issue we see on our systems. I have also checked that make check still passes.
>
> As my C is terrible, I would welcome any comments on the implementation of this patch.
>
> Best regards,
>
> George

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com