Postgres Pro
Downloads
Home   >   mailing lists

Thread: Performance problem with gin index

Performance problem with gin index

From
Bertrand Paquet
Date:
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

Re: Performance problem with gin index

From
"ktm@rice.edu"
Date:
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

Re: Performance problem with gin index

From
Jeff Janes
Date:
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

Re: Performance problem with gin index

From
Bertrand Paquet
Date:
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@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