pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rob Nagler <nagler@bivio.biz>
To: pgsql-performance@postgresql.org
Subject: Re: vacuum locking
Date: Thu, 30 Oct 2003 09:59:04 -0700
Message-ID: <16289.17240.68000.402231@gargle.gargle.HOWL> (raw)
In-Reply-To: <24018.1067472198@sss.pgh.pa.us>
References: <16272.318.870000.93584@gargle.gargle.HOWL>
	<x7d6cpcdsr.fsf@yertle.int.kciLink.com>
	<16279.4988.496000.539525@gargle.gargle.HOWL>
	<200310230814.56738.mweilguni@sime.com>
	<29123.1066915615@sss.pgh.pa.us>
	<16279.61590.158000.290397@gargle.gargle.HOWL>
	<87vfqeveab.fsf@stark.dyndns.tv>
	<16281.45354.209000.280418@gargle.gargle.HOWL>
	<878ynaupgu.fsf@stark.dyndns.tv>
	<16285.17811.994000.543635@gargle.gargle.HOWL>
	<87d6ciy3bf.fsf@stark.dyndns.tv>
	<16288.19970.172547.319577@jump.bivio.com>
	<24018.1067472198@sss.pgh.pa.us>

Tom Lane writes:
> Rob Nagler <nagler@bivio.biz> writes:
> > q5 and q6 are too complex to discuss here,
> 
> How do you expect us to get better if you don't show us the problems?

With all due respect and thanks for the massive amount of help, I have
presented the problems.  q5 and q6 are a subset of the following
general problems:

    * Multiple ORDER BY results in no index used.
      Solution: drop multiple ORDER BY, only use first

    * Vacuum locks out interactive users
      Solution: don't run vacuum full and only run vacuum at night

    * Low cardinality index on large table confuses planner
      Solution: Drop (foreign key) index, which hurts other performance

    * Grouped aggregates result in disk sort
      Solution: Wait to 7.4 (may work), or write in Perl (works today)

    * Extreme non-linear performance (crossing magic number in
      optimizer drops performance three orders of magnitude)
      Solution: Don't cross magic number, or code in Perl

The general problem is that our system generates 90% of the SQL we
need.  There are many advantages to this, such as being able to add
OFFSET/LIMIT support with a few lines of code in a matter of hours.
Every time we have to custom code a query, or worse, code it in Perl,
we lose many benefits.  I understand the need to optimize queries, but
my general experience with Oracle is that I don't have to do this very
often.  When the 80/20 rule inverts, there's something fundamentally
wrong with the model.  That's where we feel we're at.  It's cost us a
tremendous amount of money to deal with these query optimizations.

The solution is not to fix the queries, but to address the root
causes.  That's what my other note in this thread is about.  I hope
you understand the spirit of my suggestion, and work with us to
finding an acceptable approach to the general problems.

> BTW, have you tried any of this with a 7.4beta release?

I will, but for my other projects, not this one.  I'll run this data,
because it's a great test case.

We have a business decision to make: devote more time to Postgres or
go with Oracle.  I spent less than a day getting the data into Oracle
and to create the benchmark.  The payoff is clear, now.  The risk of
7.4 is still very high, because the vacuum problem still looms and a
simple "past performance is a good indicator of future performance".
Going forward, there's no choice.  We've had to limit end-user
functionality to get Postgres working as well as it does, and that's
way below where Oracle is without those same limits and without any
effort put into tuning.

Thanks again for all your support.

Rob





view thread (45+ messages)  latest in thread

Message-ID: <16289.17240.68000.402231@gargle.gargle.HOWL>
Permalink:  ../16289.17240.68000.402231@gargle.gargle.HOWL/
Also on:    postgresql.org/message-id/16289.17240.68000.402231@gargle.gargle.HOWL

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: nagler@bivio.biz
  Subject: Re: vacuum locking
  In-Reply-To: <16289.17240.68000.402231@gargle.gargle.HOWL>
  References: <16272.318.870000.93584@gargle.gargle.HOWL> <x7d6cpcdsr.fsf@yertle.int.kciLink.com> <16279.4988.496000.539525@gargle.gargle.HOWL> <200310230814.56738.mweilguni@sime.com> <29123.1066915615@sss.pgh.pa.us> <16279.61590.158000.290397@gargle.gargle.HOWL> <87vfqeveab.fsf@stark.dyndns.tv> <16281.45354.209000.280418@gargle.gargle.HOWL> <878ynaupgu.fsf@stark.dyndns.tv> <16285.17811.994000.543635@gargle.gargle.HOWL> <87d6ciy3bf.fsf@stark.dyndns.tv> <16288.19970.172547.319577@jump.bivio.com> <24018.1067472198@sss.pgh.pa.us> <16289.17240.68000.402231@gargle.gargle.HOWL>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox