Обсуждение: gin performance issue.

Поиск
Список
Период
Сортировка

gin performance issue.

От
Marc Mamin
Дата:
Postgres Version 9.3.10 (Linux)
 
Hello,
this is a large daily table that only get bulk inserts (200-400 /days) with no update.
After rebuilding the whole table, the Bitmap Index Scan on r_20160204_ix_toprid falls under 1 second (from 800)
 
Fastupdate is using the default, but autovacuum is disabled on that table which contains 30 Mio rows.
Another specificity is that the cardinality of the indexed column is very high. The average count per distinct values is only 2.7
 
I'm not sure what the problem is. Does the missing vacuum affect the gin index sanity further than not cleaning the pending list?
As I understand it, this list will be merged into the index automatically when it get full, independently from the vaccum setting.
 
Can it be an index bloating issue ?
 
and last but not least, can I reduce the problem by configuration ?
 
regards,
 
Marc Mamin
 

Re: gin performance issue.

От
Tom Lane
Дата:
Marc Mamin <M.Mamin@intershop.de> writes:
> http://explain.depesz.com/s/wKv7
> Postgres Version 9.3.10 (Linux)

> Hello,
> this is a large daily table that only get bulk inserts (200-400 /days) with no update.
> After rebuilding the whole table, the Bitmap Index Scan on r_20160204_ix_toprid falls under 1 second (from 800)

> Fastupdate is using the default, but autovacuum is disabled on that table which contains 30 Mio rows.

Pre-9.5, it's a pretty bad idea to disable autovacuum on a GIN index,
because then the "pending list" only gets flushed when it exceeds
work_mem.  (Obviously, using a large work_mem setting makes this worse.)

            regards, tom lane


Re: gin performance issue.

От
Marc Mamin
Дата:

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Freitag, 5. Februar 2016 16:07


> > http://explain.depesz.com/s/wKv7
> > Postgres Version 9.3.10 (Linux)
> >
> > Hello,
> > this is a large daily table that only get bulk inserts (200-400 /days) with no update.
> > After rebuilding the whole table, the Bitmap Index Scan on
> > r_20160204_ix_toprid falls under 1 second (from 800)
> >
> > Fastupdate is using the default, but autovacuum is disabled on that
> > table which contains 30 Mio rows.


> Pre-9.5, it's a pretty bad idea to disable autovacuum on a GIN index,
> because then the "pending list" only gets flushed when it exceeds
> work_mem.  (Obviously, using a large work_mem setting makes this
> worse.)
>
>             regards, tom lane


Hello,
knowing what the problem is don't really help here:

- auto vacuum will not run as these are insert only tables
- according to this post, auto analyze would also do the job:
  http://postgresql.nabble.com/Performance-problem-with-gin-index-td5867870.html
  It seems that this information is missing in the doc

  but it sadly neither triggers in our case as we have manual analyzes called during the dataprocesssing just following
theimports. 
  Manual vacuum is just too expensive here.

  Hence disabling fast update seems to be our only option.

  I hope this problem will help push up the 9.5 upgrade on our todo list :)

  Ideally, we would then like to flush the pending list inconditionally after the imports.
  I guess we could achieve something approaching while modifying the analyze scale factor  and gin_pending_list_limit
  before/after the (bulk) imports, but having the possibility to flush it per SQL would be better.
  Is this a reasonable feature wish?

  And a last question: how does the index update work with bulk (COPY) inserts:
  without pending list: is it like a per row trigger or will the index be cared of afterwards ?
  with small pending lists : is there a concurrency problem, or can both tasks cleanly work in parallel ?

  best regards,

  Marc mamin




Re: gin performance issue.

От
Jeff Janes
Дата:
On Mon, Feb 8, 2016 at 2:21 AM, Marc Mamin <M.Mamin@intershop.de> wrote:
>
> - auto vacuum will not run as these are insert only tables
> - according to this post, auto analyze would also do the job:
>   http://postgresql.nabble.com/Performance-problem-with-gin-index-td5867870.html
>   It seems that this information is missing in the doc
>
>   but it sadly neither triggers in our case as we have manual analyzes called during the dataprocesssing just
followingthe imports. 
>   Manual vacuum is just too expensive here.
>
>   Hence disabling fast update seems to be our only option.

Does disabling fast update cause problems?  I always start with
fastupdate disabled, and only turn on if it I have a demonstrable
problem with it being off.

I would think "off" is likely to be better for you.  You say each
distinct key only appears in 2.7 rows.  So you won't get much benefit
from aggregating together all the new rows for each key before
updating the index for that key, as there is very little to aggregate.

Also, you say the inserts come in bulk.  It is generally a good thing
to slow down bulk operations by making them clean up their own messes,
for the sake of everyone else.


>   I hope this problem will help push up the 9.5 upgrade on our todo list :)
>
>   Ideally, we would then like to flush the pending list inconditionally after the imports.
>   I guess we could achieve something approaching while modifying the analyze scale factor  and gin_pending_list_limit
>   before/after the (bulk) imports, but having the possibility to flush it per SQL would be better.
>   Is this a reasonable feature wish?

That feature has already been committed for the 9.6 branch.

>   And a last question: how does the index update work with bulk (COPY) inserts:
>   without pending list: is it like a per row trigger or will the index be cared of afterwards ?

Done for each row.

>   with small pending lists : is there a concurrency problem, or can both tasks cleanly work in parallel ?

I don't understand the question.  What are the two tasks you are
referring to? Do you have multiple COPY running at the same time in
different processes?

Cheers,

Jeff