Re: does wal archiving block the current client connection?

On Wed, 17 May 2006, Jeff Frost wrote:

> And in the window where I started postgres via pg_ctl, I had this:
>
> cat: pg_xlog/00000001000000000000000E: No such file or directory
> cat: pg_xlog/00000001000000000000000E: No such file or directory

Hrmmm...my pgbench died with an integer out of range error:

Client 56 aborted in state 8: ERROR:  integer out of range
client 81 receiving
client 81 sending UPDATE branches SET bbalance = bbalance + 4512 WHERE bid =
1;
client 86 receiving
Client 86 aborted in state 8: ERROR:  integer out of range
client 85 receiving
Client 85 aborted in state 8: ERROR:  integer out of range
client 81 receiving
Client 81 aborted in state 8: ERROR:  integer out of range
client 78 receiving
client 78 sending UPDATE branches SET bbalance = bbalance + 2868 WHERE bid =
1;
client 78 receiving
Client 78 aborted in state 8: ERROR:  integer out of range
Client 56 aborted in state 8: ERROR:  integer out of range
client 81 receiving
client 81 sending UPDATE branches SET bbalance = bbalance + 4512 WHERE bid =
1;
client 86 receiving
Client 86 aborted in state 8: ERROR:  integer out of range
client 85 receiving
Client 85 aborted in state 8: ERROR:  integer out of range
client 81 receiving
Client 81 aborted in state 8: ERROR:  integer out of range
client 78 receiving
client 78 sending UPDATE branches SET bbalance = bbalance + 2868 WHERE bid =
1;
client 78 receiving
Client 78 aborted in state 8: ERROR:  integer out of range

I'm guessing those bbalance + x updates ran bbalance up too high.

Anyway, I keep getting these on occassion:

May 17 23:01:22 discord postgres[23817]: [8-1] LOG:  archive command
"/usr/local/pgsql-8.1.3/bin/archive_test.sh "pg_xlog/000000010000000000000011"
"000000010000000000000011""
May 17 23:01:22 discord postgres[23817]: [8-2]  failed: return code 256
May 17 23:01:22 discord postgres[23817]: [9-1] WARNING:  transaction log file
"000000010000000000000011" could not be archived: too many failures
May 17 23:04:06 discord postgres[20573]: [40-1] LOG:  archived transaction log
file "000000010000000000000013"
May 17 23:04:06 discord postgres[20573]: [41-1] WARNING:  could not rename
file "pg_xlog/archive_status/000000010000000000000013.ready" to
May 17 23:04:06 discord postgres[20573]: [41-2]
"pg_xlog/archive_status/000000010000000000000013.done": No such file or
directory
May 17 23:04:27 discord postgres[20228]: [22-1] LOG:  archived transaction log
file "000000010000000000000014"

and in the other window:

cat: pg_xlog/000000010000000000000011: No such file or directory
cat: pg_xlog/000000010000000000000011: No such file or directory
cat: pg_xlog/000000010000000000000011: No such file or directory

How on earth can this happen?

>
>
>>
>>> Currently I'm using pgbench to generate WAL rollover, do you guys have any
>>> other handy testing tools for this sort of job or is this the best tool?
>>
>> pgbench seems like an OK load for this, although it doesn't start/end
>> any new connections while running.  I *think* that that's not critical
>> --- my guess is that your observation of new connections hanging is just
>> because each new connection has to run one startup transaction, and
>> transactions in general are at risk of hanging --- but that could be
>> wrong.
>>
>> Also, you could increase the rate of WAL generation by decreasing the
>> checkpoint segments/timeout parameters, if you need to.
>>
>>             regards, tom lane
>>
>>
>
>

--
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954