Kevin Grittner wrote:
> Dave Owens wrote:
>
>> I now have 8 hours worth of snapshots from pg_stat_activity and
>> pg_locks (16 snapshots from each table/view). I have turned off
>> collection at this point, but I am still able to query pg_locks
>
> Could you take the earliest one after activity
Dave Owens wrote:
> I now have 8 hours worth of snapshots from pg_stat_activity and
> pg_locks (16 snapshots from each table/view). I have turned off
> collection at this point, but I am still able to query pg_locks
Could you take the earliest one after activity started, and the
latest one befo
I now have 8 hours worth of snapshots from pg_stat_activity and
pg_locks (16 snapshots from each table/view). I have turned off
collection at this point, but I am still able to query pg_locks:
# SELECT mode, count(mode) AS count FROM pg_locks GROUP BY mode ORDER BY mode;
mode | coun
Dave Owens wrote:
> On Tue, Aug 19, 2014 at 11:01 AM, Kevin Grittner wrote:
>> CREATE TABLE activity_snap_1 AS SELECT * FROM pg_stat_activity;
> Would the you or the list be interested in snapshots of pg_locks as well?
Most definitely! I'm sorry that copied/pasted the pg_stat_activity
example
On Tue, Aug 19, 2014 at 11:01 AM, Kevin Grittner wrote:
> If restart is an option, that sounds like a great idea. If you
> could capture the data into tables where we can summarize to
> analyze it in a meaningful way, that would be ideal. Something
> like:
>
> CREATE TABLE activity_snap_1 AS SEL
Dave Owens wrote:
> I wonder if it would be helpful to restart the database, then begin
> gathering information pg_locks while it can still respond to queries.
> I speculate that this is possible because the amount of memory needed
> to query pg_locks continues to grow (around 1900MB now).
If re
Dave Owens wrote:
> I do see a handful of backends that like to stay IDLE in
> transaction for minutes at a time. We are refactoring the
> application responsible for these long IDLE times, which will
> hopefully reduce the duration of their connections.
That may help some. Other things to con
I wonder if it would be helpful to restart the database, then begin
gathering information pg_locks while it can still respond to queries.
I speculate that this is possible because the amount of memory needed
to query pg_locks continues to grow (around 1900MB now).
Dave Owens
--
Sent via pgsql-p
Hi Andres,
On Tue, Aug 19, 2014 at 10:17 AM, Andres Freund wrote:
>> max_connections = 450 ...we have found that we run out of shared
>> memory when max_pred_locks_per_transaction is less than 30k.
>
> What was the precise error message when that happened?
2014-07-31 15:00:25 PDT 53dabbea.29c7ER
On 2014-08-18 14:36:52 -0700, Dave Owens wrote:
> On Mon, Aug 18, 2014 at 2:21 PM, Matheus de Oliveira
> wrote:
> > Do you really need such large values? What is your max_connections value?
>
> max_connections = 450 ...we have found that we run out of shared
> memory when max_pred_locks_per_trans
On Tue, Aug 19, 2014 at 9:40 AM, Kevin Grittner wrote:
> Hmm, that's not outrageous. How about long-running transactions?
> Please check pg_stat_activity and pg_prepared_xacts for xact_start
> or prepared (respectively) values older than a few minutes. Since
> predicate locks may need to be kept
Dave Owens wrote:
> 1358 tables
> 1808 indexes
Hmm, that's not outrageous. How about long-running transactions?
Please check pg_stat_activity and pg_prepared_xacts for xact_start
or prepared (respectively) values older than a few minutes. Since
predicate locks may need to be kept until an over
Hi Kevin,
Looking at pg_stat_all_tables and pg_stat_all_indexes on our four
databases we have:
1358 tables
1808 indexes
The above totals do not include template1, template0, or postgres
databases. We do not use partitioned tables. Only one database has a
meaningful level of concurrency (New Rel
Tom Lane wrote:
> Kevin Grittner writes:
>> Dave Owens wrote:
>>> max_connections = 450 ...we have found that we run out of shared
>>> memory when max_pred_locks_per_transaction is less than 30k.
>
>> It gathers the information in memory to return for all those locks
>> (I think both the normal
Kevin Grittner writes:
> Dave Owens wrote:
>> max_connections = 450 ...we have found that we run out of shared
>> memory when max_pred_locks_per_transaction is less than 30k.
> It gathers the information in memory to return for all those locks
> (I think both the normal heavyweight locks and the
Dave Owens wrote:
> max_connections = 450 ...we have found that we run out of shared
> memory when max_pred_locks_per_transaction is less than 30k.
>> SELECT COUNT(*) from pg_locks;
>
> ERROR: invalid memory alloc request size 1562436816
It gathers the information in memory to return for all
On Mon, Aug 18, 2014 at 2:21 PM, Matheus de Oliveira
wrote:
> Do you really need such large values? What is your max_connections value?
max_connections = 450 ...we have found that we run out of shared
memory when max_pred_locks_per_transaction is less than 30k.
On Mon, Aug 18, 2014 at 2:29 PM, M
On Mon, Aug 18, 2014 at 4:21 PM, Matheus de Oliveira
wrote:
>
> On Mon, Aug 18, 2014 at 6:01 PM, Dave Owens wrote:
>>
>> max_locks_per_transaction = 9000
>> max_pred_locks_per_transaction = 4
performance of any query to pg_locks is proportional to the setting of
max_locks_per_transaction. s
On Mon, Aug 18, 2014 at 6:01 PM, Dave Owens wrote:
> max_locks_per_transaction = 9000
> max_pred_locks_per_transaction = 4
>
Do you really need such large values? What is your max_connections value?
Regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível
19 matches
Mail list logo
