Re: Performance problem with gin index

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(dot)janes(at)gmail(dot)com>:

> On Tue, Sep 29, 2015 at 8:45 AM, Bertrand Paquet <
> bertrand(dot)paquet(at)doctolib(dot)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
>