Re: [PERFORM] Linux machine aggressively clearing cache

2012-04-12 Thread Steve Crawford
On 03/30/2012 05:51 PM, Josh Berkus wrote: So this turned out to be a Linux kernel issue. Will document it on www.databasesoup.com. Anytime soon? About to build two PostgreSQL servers and wondering if you have uncovered a kernel version or similar issue to avoid. Cheers, Steve -- Sent via

[PERFORM] Random performance hit, unknown cause.

2012-04-12 Thread Brian Fehrle
Hi all, OS: Linux 64 bit 2.6.32 PostgreSQL 9.0.5 installed from Ubuntu packages. 8 CPU cores 64 GB system memory Database cluster is on raid 10 direct attached drive, using a HP p800 controller card. I have a system that has been having occasional performance hits, where the load on the syst

Re: [PERFORM] Random performance hit, unknown cause.

2012-04-12 Thread Claudio Freire
On Thu, Apr 12, 2012 at 3:41 PM, Brian Fehrle wrote: > This morning, during our nightly backup process (where we grab a copy of the > data directory), we started having this same issue. The main thing that I > see in all of these is a high disk wait on the system. When we are > performing 'well',

Re: [PERFORM] Random performance hit, unknown cause.

2012-04-12 Thread Kevin Grittner
Claudio Freire wrote: > On Thu, Apr 12, 2012 at 3:41 PM, Brian Fehrle > wrote: >> This morning, during our nightly backup process (where we grab a >> copy of the data directory), we started having this same issue. >> The main thing that I see in all of these is a high disk wait on >> the system.

Re: [PERFORM] Random performance hit, unknown cause.

2012-04-12 Thread Claudio Freire
On Thu, Apr 12, 2012 at 3:41 PM, Brian Fehrle wrote: > Is there anything that can help us maximise the performance to disk in this > case, as it seems to be one of our major bottlenecks? If it's indeed autovacuum, like I think it is, you can try limiting it with pg's autovacuum_cost_delay params.

Re: [PERFORM] Random performance hit, unknown cause.

2012-04-12 Thread Brian Fehrle
Interesting, that is very likely. In this system I have a table that is extremely active. On a 'normal' day, the autovacuum process takes about 7 hours to complete on this table, and once it's complete, the system performs an autoanalyze on the table, finding that we have millions of new dead

Re: [PERFORM] Random performance hit, unknown cause.

2012-04-12 Thread Kevin Grittner
Brian Fehrle wrote: > In this system I have a table that is extremely active. On a > 'normal' day, the autovacuum process takes about 7 hours to > complete on this table, and once it's complete, the system > performs an autoanalyze on the table, finding that we have > millions of new dead rows.

[PERFORM] Slow fulltext query plan

2012-04-12 Thread Benoit Delbosc
Hi, I would like to understand why the following query execution don't use any fulltext indexes and takes more than 300s (using lot of temporary files): EXPLAIN ANALYZE SELECT hierarchy.id FROM hierarchy JOIN fulltext ON fulltext.id = hierarchy.id, TO_TSQUERY('whatever') query1, TO_T

Re: [PERFORM] Slow fulltext query plan

2012-04-12 Thread Tom Lane
Benoit Delbosc writes: >EXPLAIN ANALYZE SELECT hierarchy.id >FROM hierarchy >JOIN fulltext ON fulltext.id = hierarchy.id, >TO_TSQUERY('whatever') query1, >TO_TSQUERY('whatever') query2 >WHERE (query1 @@ nx_to_tsvector(fulltext.fulltext)) OR (query2 @@ > nx_to_tsvector(full

Re: [PERFORM] Slow fulltext query plan

2012-04-12 Thread Benoit Delbosc
On 13/04/2012 00:25, Tom Lane wrote: Benoit Delbosc writes: EXPLAIN ANALYZE SELECT hierarchy.id FROM hierarchy JOIN fulltext ON fulltext.id = hierarchy.id, TO_TSQUERY('whatever') query1, TO_TSQUERY('whatever') query2 WHERE (query1 @@ nx_to_tsvector(fulltext.fulltext)) OR