Re: [PERFORM] Pointers needed on optimizing slow SQL statements

On Wed, Jun 3, 2009 at 8:32 PM, Janine Sisk wrote: > I'm sorry if this is a stupid question, but...  I changed > default_statistics_target from the default of 10 to 100, restarted PG, and > then ran "vacuumdb -z" on the database.  The plan is exactly the same as > before.  Was I supposed to do som

Re: [PERFORM] degenerate performance on one server of 3

Erik Aronesty wrote: I think, perhaps, autovac wasn't running on that machine. Is there any way to check to see if it's running? since it looks like stats are on too http://www.network-theory.co.uk/docs/postgresql/vol3/ViewingCollectedStatistics.html read the entry on pg_stat_all_tables

Re: [PERFORM] Pointers needed on optimizing slow SQL statements

I'm sorry if this is a stupid question, but... I changed default_statistics_target from the default of 10 to 100, restarted PG, and then ran "vacuumdb -z" on the database. The plan is exactly the same as before. Was I supposed to do something else? Do I need to increase it even further?

[PERFORM] Query plan issues - volatile tables

Hi, We have a problem with some of our query plans. One of our tables is quite volatile, but postgres always uses the last statistics snapshot from the last time it was analyzed for query planning. Is there a way to tell postgres that it should not trust the statistics for this table? Basic

Re: [PERFORM] Pointers needed on optimizing slow SQL statements

On Wed, Jun 3, 2009 at 6:04 PM, Janine Sisk wrote: > Ok, I will look into gathering better statistics.  This is the first time > I've had a significant problem with a PG database, so this is uncharted > territory for me. > > If there is more info I could give that would help, please be more specif

Re: [PERFORM] Best way to load test a postgresql server

On Tue, 2 Jun 2009, Shaul Dar wrote: If you want to test the H/W and configuration of your DBMS then you can use the pgbench tool (which uses a specific built-in DB+schema, following the TPC benchmark). There are a lot of TPC benchmarks. pgbench simulates TPC-B (badly), which is a benchmark

Re: [PERFORM] Pointers needed on optimizing slow SQL statements

Ok, I will look into gathering better statistics. This is the first time I've had a significant problem with a PG database, so this is uncharted territory for me. If there is more info I could give that would help, please be more specific about what you need and I will attempt to do so.

Re: [PERFORM] Pointers needed on optimizing slow SQL statements

Janine Sisk writes: > I've been Googling for SQL tuning help for Postgres but the pickings > have been rather slim. Maybe I'm using the wrong search terms. I'm > trying to improve the performance of the following query and would be > grateful for any hints, either directly on the problem a

[PERFORM] Pointers needed on optimizing slow SQL statements

I've been Googling for SQL tuning help for Postgres but the pickings have been rather slim. Maybe I'm using the wrong search terms. I'm trying to improve the performance of the following query and would be grateful for any hints, either directly on the problem at hand, or to resources I c

Re: [PERFORM] degenerate performance on one server of 3

Erik Aronesty writes: > I think, perhaps, autovac wasn't running on that machine. > Is there any way to check to see if it's running? > I have enabled all the options , and I know it's running on my other > servers because I see > LOG: autovacuum entries (a profusion of them) > I suspect,

Re: [PERFORM] Scalability in postgres

On 6/3/09 11:39 AM, "Robert Haas" wrote: > On Wed, Jun 3, 2009 at 2:12 PM, Scott Carey wrote: >> Postgres could fix its connection scalability issues -- that is entirely >> independent of connection pooling. > > Really? I'm surprised. I thought the two were very closely related. > Could you

Re: [PERFORM] degenerate performance on one server of 3

I think, perhaps, autovac wasn't running on that machine. Is there any way to check to see if it's running? I have enabled all the options , and I know it's running on my other servers because I see LOG: autovacuum entries (a profusion of them) I suspect, perhaps, that it's just not showin

Re: [PERFORM] Scalability in postgres

On Wed, Jun 3, 2009 at 2:12 PM, Scott Carey wrote: > Postgres could fix its connection scalability issues -- that is entirely > independent of connection pooling. Really? I'm surprised. I thought the two were very closely related. Could you expand on your thinking here? ...Robert -- Sent via

Re: [PERFORM] Scalability in postgres

Just to say you don't need a mega server to keep thousands connections with Oracle, it's just trivial, nor CPU affinity and other stuff you may or may not need with Sybase :-) Regarding PostgreSQL, I think it'll only benefit to have an integrated connection pooler as it'll make happy all populatio

Re: [PERFORM] Scalability in postgres

On 6/3/09 10:45 AM, "Kevin Grittner" wrote: > Dimitri wrote: >> Few weeks ago tested a customer application on 16 cores with Oracle: >> - 20,000 sessions in total >> - 70,000 queries/sec >> >> without any problem on a mid-range Sun box + Solaris 10.. > > I'm not sure what point you are t

Re: [PERFORM] Scalability in postgres

Dimitri wrote: > Few weeks ago tested a customer application on 16 cores with Oracle: > - 20,000 sessions in total > - 70,000 queries/sec > > without any problem on a mid-range Sun box + Solaris 10.. I'm not sure what point you are trying to make. Could you elaborate? (If it's that Orac

Re: [PERFORM] Scalability in postgres

Few weeks ago tested a customer application on 16 cores with Oracle: - 20,000 sessions in total - 70,000 queries/sec without any problem on a mid-range Sun box + Solaris 10.. Rgds, -Dimitri On 6/3/09, Kevin Grittner wrote: > James Mansion wrote: > >> I'm sure most of us evaluating Postgres

Re: [PERFORM] poor performing plan from analyze vs. fast default plan pre-analyze on new database

On 6/3/09 8:42 AM, "Davin Potts" wrote: > Hi all -- > > > A little more background:  The table of interest, content, has around > 1.5M rows on the production system and around 1.1M rows on the > development system at the time this query was run.  On both systems, > the smirkfp databases are ce

Re: [PERFORM] poor performing plan from analyze vs. fast default plan pre-analyze on new database

Davin Potts writes: > How to approach manipulating the execution plan back to something more > efficient?  What characteristics of the table could have induced > analyze to suggest the much slower query plan? What's evidently happening is that the planner is backing off from using a hashed subpla

Re: [PERFORM] poor performing plan from analyze vs. fast default plan pre-analyze on new database

Postgresql isn't very efficient with subselects like that, try: explain select c.id from content c LEFT JOIN (select min(id) AS id from content group by hash) cg ON cg.id=c.id WHERE cg.id is null; -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to you

[PERFORM] poor performing plan from analyze vs. fast default plan pre-analyze on new database

Hi all -- In attempting to perform a particular query in postgres, I ran into what appeared to be a pretty severe performance bottleneck.  I didn't know whether I'd constructed my query stupidly, or I'd misconfigured postgres in some suboptimal way, or what else might be going on. Though I very mu

Re: [PERFORM] Scalability in postgres

James Mansion wrote: > I'm sure most of us evaluating Postgres from a background in Sybase > or SQLServer would regard 5000 connections as no big deal. Sure, but the architecture of those products is based around all the work being done by "engines" which try to establish affinity to differen

Re: [PERFORM] Best way to load test a postgresql server

On Wed, 03 Jun 2009 05:29:02 -0400, Dimitri Fontaine wrote: Last time I used it it was in the context of a web application and to compare PostgreSQL against Informix after a migration. So I used the HTTP protocol support of the injector. Tsung seems well suited for that. Tsung is based on e

Re: [PERFORM] Best way to load test a postgresql server

I considered Tsung myself but haven't tried it. If you intend to, I suggest you read this excellent tutorial on using Tsung for test-loading Postgresql. While impressed I decided the procedure was too daunting and went with JMeter :-)

Re: [PERFORM] Scalability in postgres

Greg Smith wrote: 3500 active connections across them. That doesn't work, and what happens is exactly the sort of context switch storm you're showing data for. Think about it for a minute: how many of those can really be doing work at any time? 32, that's how many. Now, you need some multip

Re: [PERFORM] Best way to load test a postgresql server

"Kenneth Cox" writes: > On Tue, 02 Jun 2009 05:26:41 -0400, Dimitri Fontaine > wrote: >> I'd recommand having a look at tsung which will be able to replay a >> typical application scenario with as many concurrent users as you want >> to: http://archives.postgresql.org/pgsql-admin/2008-12/msg00032