Re: FW: [PERFORM] Queries becoming slow under heavy load

2011-01-26 Thread Scott Marlowe
On Wed, Jan 26, 2011 at 10:16 AM, Shaun Thomas wrote: > Worse however, is your checkpoints. Lord. Increase checkpoint_segments to > *at least* 20, and increase your checkpoint_completion_target to 0.7 or 0.8. > Check your logs for checkpoint warnings, and I'll bet it's constantly > complaining abo

Re: FW: [PERFORM] Queries becoming slow under heavy load

2011-01-26 Thread Scott Marlowe
On Wed, Jan 26, 2011 at 9:04 AM, Anne Rosset wrote: PLEASE post just the settings you changed. I'm not searching through a list that big for the interesting bits. > Today we did more analysis and observed  postgress processes that > continually reported status 'D' in top. Full stop. The mos

Re: [PERFORM] Queries becoming slow under heavy load

2011-01-26 Thread mark
> -Original Message- > From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance- > ow...@postgresql.org] On Behalf Of Ivan Voras > Sent: Wednesday, January 26, 2011 6:25 AM > To: pgsql-performance@postgresql.org > Subject: Re: [PERFORM] Queries becoming slow under heavy load

Re: [PERFORM] anti-join chosen even when slower than old plan

2011-01-26 Thread Bruce Momjian
Robert Haas wrote: > On Thu, Jan 20, 2011 at 4:17 AM, C?dric Villemain > wrote: > >>> I think his point is that we already have a proven formula > >>> (Mackert-Lohmann) and shouldn't be inventing a new one out of thin air. > >>> The problem is to figure out what numbers to apply the M-L formula to

Re: [PERFORM] Real vs Int performance

2011-01-26 Thread Tom Lane
David Greco writes: > Came across a problem I find perplexing. I recreated the dimensional tables > in Oracle and the fields that are integers in Oracle became integers > in Postgres. Was experiencing terrible performance during the load and > narrowed down to a particular dimensional lookup pro

Re: [PERFORM] Real vs Int performance

2011-01-26 Thread Kevin Grittner
David Greco wrote: > If I change this field from an integer to a real, I get about a > 70x increase in performance of the query. > I wished to simplify things a bit here (and don't yet know how to > EXPLAIN ANALYZE a parameterized query). > carrier_source_id | integer |

[PERFORM] Real vs Int performance

2011-01-26 Thread David Greco
New to Postgres and am prototyping a migration from Oracle to Postgres 9.0.1 on Linux. Starting with the data warehouse. Current process is to load the data from our OLTP (Oracle), dump it into another instance of Oracle for staging and manipulation, then extract it and load it into Infobright.

Re: FW: [PERFORM] Queries becoming slow under heavy load

2011-01-26 Thread Shaun Thomas
On 01/26/2011 10:04 AM, Anne Rosset wrote: We've been able to match long running database queries to such processes. This occurs under relatively low load average (say 4 out of 8) and can involve as little as 1 single sql query. The b state means the process is blocking, waiting for... someth

FW: [PERFORM] Queries becoming slow under heavy load

2011-01-26 Thread Anne Rosset
Sorry it seems like the postgres configuration didn't come thru the first time. name| setting - + -- add_missing_from| off allow_system_table_mods | off archive_command | (disabled) archive_mode|

Re: [PERFORM] Queries becoming slow under heavy load

2011-01-26 Thread Ivan Voras
On 25/01/2011 22:37, Anne Rosset wrote: Hi, We are running some performances tests. With a lot of concurrent access, queries get very slow. When there is no load, those queries run fast. As others said, you need to stat how many concurrent clients are working on the database and also the nu