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
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