Thx you for your hints.
I found lot of information in this thread
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@xxxxxxxxx>:
On Tue, Sep 29, 2015 at 8:45 AM, Bertrand Paquet <bertrand.paquet@xxxxxxxxxxx> 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
