Re: Problems with pg_locks explosion

Thanks Jeff, yup, I'm actually busy setting up pg_pool now. Preliminary
results looks promising after switching some client nodes to connect
through the pool.

Here's the output of pg_stat_dabatase but also doesn't seem to be spending
more time there either. I'll have a look through the archives for the posts
you refer to. Certainly a simple 'upgrade' that's gone south badly here. On
paper and with earlier tests with partial load this instance is much better
suited for our workload, bizarre.

Thanks for all the help.

user=# select * from pg_stat_database;
datid | datname | numbackends | xact_commit | xact_rollback |
blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted |
tup_updated | tup_deleted | conflicts | temp_files | temp_bytes |
deadlocks | blk_read_time | blk_write_time | stats_
reset
-------+-------------------+-------------+-------------+---------------+-----------+--------------+--------------+--------------+--------------+-------------+-------------+-----------+------------+-------------+-----------+---------------+----------------+
17671 | production | 232 | 524583730 | 213187 |
623507632 | 403590060861 | 256853219556 | 185283046675 | 95086454 |
66720609 | 4449894 | 0 | 18 | 12076277760 | 0
| 4689747.806 | 4526.539 | 2013-03-29 16:1
8:09.334432+00

On Tue, Apr 2, 2013 at 8:08 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Monday, April 1, 2013, Armand du Plessis wrote:
>
>> I've run an EXPLAIN ANALYZE on one of the queries that appeared in the
>> pg_locks (although like you say that might be a red herring) both during
>> normal response times (2) and also after the locks backlog materialized (1)
>>
>> The output below, I've just blanked out some columns. The IO timings do
>> seem an order of magnitude slower but not excessive unless I'm reading it
>> wrong.
>>
>> "Limit (cost=2364.19..2365.44 rows=500 width=177) (actual
>> time=6501.103..6507.196 rows=500 loops=1)"
>> " Output:
>> " Buffers: shared hit=7163 read=137"
>> " I/O Timings: read=107.771"
>>
>
> ...
>
>
>>
>> "Limit (cost=2366.57..2367.82 rows=500 width=177) (actual time=
>> 73.284..76.296 rows=500 loops=1)"
>> " Output: various columns"
>> " Buffers: shared hit=6738 read=562"
>> " I/O Timings: read=19.212"
>>
>
> You are correct that the different in IO timing for reads is not nearly
> enough to explain the difference, but the ratio is still large enough to
> perhaps be suggestive. It could be be that all the extra time is spent in
> IO writes (not reported here). If you turn on track_io_timing on
> system-wide you could check the write times in pg_stat_database.
>
> (Write time has an attribution problem. I need to make room for my data,
> so I write out someone else's. Is the time spent attributed to the one
> doing the writing, or the one who owns the data written?)
>
> But it is perhaps looking like it might not be IO at all, but rather some
> kind of internal kernel problem, such as the "zone reclaim" and "huge
> pages" and memory interleaving, which have been discussed elsewhere in this
> list for high CPU high RAM machines. I would summarize it for you, but I
> don't understand it, and don't have ready access to machines with 64 CPUs
> and 128 GB of RAM in order to explore it for myself.
>
> But if that is the case, then using a connection pooler to restrict the
> number of simultaneously active connections might actually be a big win
> (despite what I said previously).
>
> Cheers,
>
> Jeff
>