Alvaro Herrera wrote:
Performance analysis of strange queries is useful, but the input queries
have to be meaningful as well. Otherwise you end up optimizing bizarre
and useless cases.
I had a similar one a few weeks ago. I did some batch-processing over a
bunch of documents and discovered
I have 700 lines of non-performant pgSQL code that I'd like to
profile to see what's going on.
What's the best way to profile stored procedures?
Thanks,
Drew
---(end of broadcast)---
TIP 4: Have you searched our list archives?
am Fri, dem 03.11.2006, um 3:12:14 -0800 mailte Drew Wilson folgendes:
I have 700 lines of non-performant pgSQL code that I'd like to
profile to see what's going on.
What's the best way to profile stored procedures?
RAISE NOTICE, you can raise the aktual time within a transaction with
A. Kretschmer wrote:
am Fri, dem 03.11.2006, um 3:12:14 -0800 mailte Drew Wilson folgendes:
I have 700 lines of non-performant pgSQL code that I'd like to
profile to see what's going on.
What's the best way to profile stored procedures?
RAISE NOTICE, you can raise the aktual time within
Hi,
We've migrated one of our servers from pg 7.4 to 8.1 and from times to times (4 hours) the server start doing a lot of context switching and all transactions become very slow.
The average context switching for this server as vmstat shows is 1 but when the problem occurs it goes to 25.
Based on what other people have posted, hyperthreading seems not to be
beneficial for postgres -- try searching through the archives of this list.
(And then turn it off and see if it helps.)
You might also post a few details:
config settings (shared_buffers, work_mem, maintenance_work_mem, wal
[EMAIL PROTECTED] wrote:
Hi,
We've migrated one of our servers from pg 7.4 to 8.1 and from times
to times (4 hours) the server start doing a lot of context switching
and all transactions become very slow.
The average context switching for this server as vmstat shows is 1
but when the problem
Cosimo Streppone wrote:
Richard Huxton wrote:
[EMAIL PROTECTED] wrote:
The average context switching for this server as vmstat shows is 1
but when the problem occurs it goes to 25.
You'll tend to see it when you have multiple clients and most queries
can use RAM rather than disk I/O.
Am Donnerstag, den 02.11.2006, 09:41 -0600 schrieb Scott Marlowe:
Sometimes it's the simple solutions that work best. :) Welcome to the
world of pgsql, btw...
OTOH, there are also non-simple solutions to this, which might make
sense anyway: Install slony, and run your queries against a
The solution for us has been twofold:
upgrade to the newest PG version available at the time while we waited
for our new Opteron-based DB hardware to arrive.
Andreas
Am Freitag, den 03.11.2006, 13:29 + schrieb Richard Huxton:
Cosimo Streppone wrote:
Richard Huxton wrote:
[EMAIL
On Fri, 3 Nov 2006, Richard Huxton wrote:
It's memory bandwidth issues on the older Xeons. If you search the
archives you'll see a lot of discussion of this. I'd have thought 8.1
would be better than 7.4 though.
Hmmm... I just checked; one of our production systems is a multi-cpu Xeon
based
Richard Troy wrote:
On Fri, 3 Nov 2006, Richard Huxton wrote:
It's memory bandwidth issues on the older Xeons. If you search the
archives you'll see a lot of discussion of this. I'd have thought 8.1
would be better than 7.4 though.
Hmmm... I just checked; one of our production systems is a
Richard Huxton wrote:
[EMAIL PROTECTED] wrote:
Hi,
We've migrated one of our servers from pg 7.4 to 8.1 and from times
to times (4 hours) the server start doing a lot of context switching
and all transactions become very slow.
The average context switching for this server as vmstat shows is
If you can keep your numbers of clients down below the critical level, you should find the overall workload is fine.
We have at about 600 connections. Is this a case to use a connection pool (pg_pool) system?
And why this happens only with 8.0 and 8.1 and not with the 7.4?
[EMAIL PROTECTED] wrote:
If you can keep your numbers of clients down below the critical
level, you should find the overall workload is fine.
We have at about 600 connections. Is this a case to use a connection
pool (pg_pool) system?
Possibly - that should help. I'm assuming that most of
Arjen van der Meijden [EMAIL PROTECTED] writes:
... Rewriting it to something like this made the last iteration about as
fast as the first:
SELECT docid, (SELECT work to be done for each document)
FROM documents
WHERE docid IN (SELECT docid FROM documents
ORDER BY docid
LIMIT
On 11/3/06, Richard Huxton dev@archonet.com wrote:
There's a GUI debugger from EnterpriseDB I believe, but I've no idea how
good it is. Any users/company bods care to let us know?
If you visit:
http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/edb-debugger/#dirlist
We have both a PL/pgSQL profiler
[EMAIL PROTECTED] writes:
And why this happens only with 8.0 and 8.1 and not with the 7.4?
8.0 and 8.1 are vulnerable to this behavior because of conflicts for
access to pg_subtrans (which didn't exist in 7.4). The problem occurs
when you have old open transactions, causing the window over
Am Freitag, den 03.11.2006, 14:38 + schrieb Richard Huxton:
[EMAIL PROTECTED] wrote:
If you can keep your numbers of clients down below the critical
level, you should find the overall workload is fine.
We have at about 600 connections. Is this a case to use a connection
pool
To support migration of existing queries, it would be nice not to have
to rewrite EXISTS clauses as IN clauses. Here is one example of a query
which optimizes poorly:
DELETE FROM CaseDispo
WHERE EXISTS
(
SELECT * FROM Consolidation C
WHERE
20 matches
Mail list logo
