pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance problem with gin index
4+ messages / 3 participants
[nested] [flat]
* Performance problem with gin index
@ 2015-09-29 15:45 Bertrand Paquet <bertrand.paquet@doctolib.fr>
0 siblings, 2 replies; 4+ messages in thread
From: Bertrand Paquet @ 2015-09-29 15:45 UTC (permalink / raw)
To: pgsql-performance
Hi,
We have got big slow down on our production plateform (PG 9.4.4).
After analyzing wals with pg_xlogdump, we see lot of writing in Gin Indexes.
We suspect slow down are related to the write of pending update on the
index.
So, is there any method to see
- what is the current config of gin_pending_list_limit on a given index ?
- the current size of pending list on a given index ?
Regards,
Bertrand
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance problem with gin index
@ 2015-09-29 16:12 ktm@rice.edu <ktm@rice.edu>
parent: Bertrand Paquet <bertrand.paquet@doctolib.fr>
1 sibling, 0 replies; 4+ messages in thread
From: ktm@rice.edu @ 2015-09-29 16:12 UTC (permalink / raw)
To: Bertrand Paquet <bertrand.paquet@doctolib.fr>; +Cc: pgsql-performance
On Tue, Sep 29, 2015 at 05:45:41PM +0200, Bertrand Paquet wrote:
> Hi,
>
> We have got big slow down on our production plateform (PG 9.4.4).
> After analyzing wals with pg_xlogdump, we see lot of writing in Gin Indexes.
> We suspect slow down are related to the write of pending update on the
> index.
>
> So, is there any method to see
> - what is the current config of gin_pending_list_limit on a given index ?
> - the current size of pending list on a given index ?
>
> Regards,
> Bertrand
Hi Bertrand,
You might try disabling fastupdate for the index. 9.5 has some work in
this area, but prior to that disabling it is the best fix. It certainly
helped our system with the same issue.
Regards,
Ken
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance problem with gin index
@ 2015-09-29 17:17 Jeff Janes <jeff.janes@gmail.com>
parent: Bertrand Paquet <bertrand.paquet@doctolib.fr>
1 sibling, 1 reply; 4+ messages in thread
From: Jeff Janes @ 2015-09-29 17:17 UTC (permalink / raw)
To: Bertrand Paquet <bertrand.paquet@doctolib.fr>; +Cc: pgsql-performance
On Tue, Sep 29, 2015 at 8:45 AM, Bertrand Paquet <
bertrand.paquet@doctolib.fr> wrote:
> Hi,
>
> We have got big slow down on our production plateform (PG 9.4.4).
>
What is it slow compared to? Did your version change, or your
workload/usage change?
>
> After analyzing wals with pg_xlogdump, we see lot of writing in Gin
> Indexes.
>
> We suspect slow down are related to the write of pending update on the
> index.
>
> So, is there any method to see
> - what is the current config of gin_pending_list_limit on a given index ?
>
gin_pending_list_limit will be introduced in 9.5. In 9.4 and before, there
is no such parameter. Instead, the limit is tied to the setting of
work_mem in those versions.
> - the current size of pending list on a given index ?
>
You can use this from the pgstattuple contrib module:
SELECT * FROM pgstatginindex('test_gin_index');
Your best bet may be to turn off fastupdate. It will slow down most
inserts/updates, but you will not have the huge latency spikes you get with
fastupdate turned on.
Also, you might (or might not) have a higher overall throughput with
fastupdate turned off, depending on a lot of things like the size of the
index, the size of ram and shared_buffers, the number of spindles in your
RAID, the amount of parallelization in your insert/update activity, and the
distribution of "keys" among the data you are inserting/updating.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance problem with gin index
@ 2015-09-29 20:51 Bertrand Paquet <bertrand.paquet@doctolib.fr>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Bertrand Paquet @ 2015-09-29 20:51 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-performance
Thx you for your hints.
I found lot of information in this thread
http://postgresql.nabble.com/how-to-investigate-GIN-fast-updates-and-cleanup-cycles-td5863756.html
Currently, we are monitoring pending_pages (pgstatginindex works on 9.4.4),
and run a vacuum every night. We hope it will solve the problem, without
disabling fast update.
Regards,
Bertrand
2015-09-29 19:17 GMT+02:00 Jeff Janes <jeff.janes@gmail.com>:
> On Tue, Sep 29, 2015 at 8:45 AM, Bertrand Paquet <
> bertrand.paquet@doctolib.fr> wrote:
>
>> Hi,
>>
>> We have got big slow down on our production plateform (PG 9.4.4).
>>
>
> What is it slow compared to? Did your version change, or your
> workload/usage change?
>
>
>>
>> After analyzing wals with pg_xlogdump, we see lot of writing in Gin
>> Indexes.
>>
>> We suspect slow down are related to the write of pending update on the
>> index.
>>
>> So, is there any method to see
>> - what is the current config of gin_pending_list_limit on a given index ?
>>
>
> gin_pending_list_limit will be introduced in 9.5. In 9.4 and before,
> there is no such parameter. Instead, the limit is tied to the setting of
> work_mem in those versions.
>
>
>> - the current size of pending list on a given index ?
>>
>
> You can use this from the pgstattuple contrib module:
>
> SELECT * FROM pgstatginindex('test_gin_index');
>
> Your best bet may be to turn off fastupdate. It will slow down most
> inserts/updates, but you will not have the huge latency spikes you get with
> fastupdate turned on.
>
> Also, you might (or might not) have a higher overall throughput with
> fastupdate turned off, depending on a lot of things like the size of the
> index, the size of ram and shared_buffers, the number of spindles in your
> RAID, the amount of parallelization in your insert/update activity, and the
> distribution of "keys" among the data you are inserting/updating.
>
> Cheers,
>
> Jeff
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2015-09-29 20:51 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-09-29 15:45 Performance problem with gin index Bertrand Paquet <bertrand.paquet@doctolib.fr>
2015-09-29 16:12 ` ktm@rice.edu <ktm@rice.edu>
2015-09-29 17:17 ` Jeff Janes <jeff.janes@gmail.com>
2015-09-29 20:51 ` Bertrand Paquet <bertrand.paquet@doctolib.fr>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox