From: PFC <lists@peufeu.com>
To: valiouk@yahoo.co.uk
To: pgsql-performance@postgresql.org
Subject: Re: postgres overall performance seems to degrade when large SELECT are requested
Date: Wed, 14 May 2008 13:10:34 +0200
Message-ID: <op.ua45bwqjcigqcu@apollo13.peufeu.com> (raw)
In-Reply-To: <942836.27599.qm@web25805.mail.ukl.yahoo.com>
References: <942836.27599.qm@web25805.mail.ukl.yahoo.com>
> The problem seem to arise when a SELECT that returns a lot of rows is
Does the SELECT return a lot of rows, or does it scan a lot of rows ?
(for instance, if you use aggregates, it might scan lots of data but only
return few rows).
> The problem is that when the SELECTs are run the main application starts
> running out of available connections which means that postgres is not
> returning the query results fast enough. What I find a little bit
> starnge is that the report engine's SELECTs operate on a different set
> of tables than the ones the main application is using. Also the db box
> is hardly breaking a sweat, CPU and memory utilization are ridiculously
> low and IOwaits are typically less than 10%.
Is it swapping ? (vmstat -> si/so)
Is it locking ? (probably not from what you say)
Is the network connection between the client and DB server saturated ?
(easy with 100 Mbps connections, SELECT with a large result set will
happily blast your LAN)
Is the reporting tool running on the same machine as the DB client and
killing it ? (swapping, etc)
If it's a saturated network, solutions are :
- install Gb ethernet
- run the report on the database server (no bandwidth problems...)
- rewrite the reporting tool to use SQL aggregates to transfer less data
over the network
- or use a cursor to fetch your results in chunks, and wait a little
between chunks
> Has anyone experienced this?
Yeah on benchmarks sometimes the LAN gave up before Postgres broke a
sweat... Gb ethernet solved that...
> Are there any settings I can change to improve throughput? Any help
> will be greatly appreciated.
iptraf will tell you all about your network traffic
vmstat will tell you if your server or client is io-cpu-swap bound
you'd need to post output from those...
>
>
> Thanks,
> val
>
>
> __________________________________________________________
> Sent from Yahoo! Mail.
> A Smarter Email http://uk.docs.yahoo.com/nowyoucan.html
>
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@peufeu.com, valiouk@yahoo.co.uk
Subject: Re: postgres overall performance seems to degrade when large SELECT are requested
In-Reply-To: <op.ua45bwqjcigqcu@apollo13.peufeu.com>
* 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