On Thu, Sep 17, 2015 at 03:14:43PM -0400, Dave Stibrany wrote: > Hi all. I am occasionally seeing really slow update/inserts into a fairly > large table. By really slow I mean around 10-40 seconds, > while the majority of queries take milliseconds. I cannot reproduce this > problem myself, but it is occurring multiple times a day > (maybe 30 times). > > System Info > --------------- > Model: Dell PowerEdge R420 > CPU: 12 core Intel(R) Xeon(R) @ 2.20GHz > Memory: 16GB > Disk: PERC H310 Mini Raid Controller using Raid 1 > OS: Ubuntu 14.04.3 LTS > > DB Settings > ---------------- > ... a lot of information deleted... Hi Dave, This search index is almost certainly the cause of your slowdowns: > Indexes: > "document_search_ix" gin (contents_search) We observed similar odd slowdowns with a GIN text search index. We had to disable the 'fastupdate' option for the index to stop the large pauses by the index entry clean-up processing. There have been some recent patches to address the penalty problem caused by the fastupdate processing. > What I haven't tried > -------------------- > - more aggressive auto-vacuum > - trying gist table for full text search index instead of gin > - removing full text search altogether (are users don't use it very much) Nope, keep using GIN. GIST is too slow for this usage. Just disable the 'fastupdate' on the index: ALTER INDEX document_search_ix SET (fastupdate = off); Regards, Ken -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
