From themanatuf@yahoo.com Wed Nov  12 16:27:54 2008
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 77D6B64FD93
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 12 Nov 2008 12:27:54 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 51598-05
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 12 Nov 2008 12:27:49 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from web34202.mail.mud.yahoo.com (web34202.mail.mud.yahoo.com
	[66.163.178.117])
	by mail.postgresql.org (Postfix) with SMTP id E604264FD42
	for <pgsql-performance@postgresql.org>;
	Wed, 12 Nov 2008 12:27:48 -0400 (AST)
Received: (qmail 42618 invoked by uid 60001); 12 Nov 2008 16:27:46 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Message-ID;
	b=PQaEzGqhOntZGaiAJCOxyQiQ/uwaje/rZxO070YVKvEksvKuC0kPQPNr/QJk2J1hNKk+t9vfq8TbkzGD/+7GENkhtkv9+adidsWHU+Gj8mDbxZ1LWevNhFAt/mUslBY7eWAcVPItuFyKRu15mDeNqSyrls5UjEzqZxXMt5F8EG4=;
X-YMail-OSG: 
 qvNY4lIVM1mXc1tSiZv.enTuFNLJwh07_fC_Soa3Ep8zGkt6MZre_nLujQaNcevDQJdUt3BLRwf8O9flZ3QMofVe.bN.08xt__xG1XUIhrYF3yybLATWMZNoIPDkY6kJbshD9ifoaS4I54Q7lZ8MmCNWWkS2mHoPH4xamdQo
Received: from [71.41.44.139] by web34202.mail.mud.yahoo.com via HTTP;
	Wed, 12 Nov 2008 08:27:46 PST
X-Mailer: YahooMailRC/1155.29 YahooMailWebService/0.7.260.1
Date: Wed, 12 Nov 2008 08:27:46 -0800 (PST)
From: - - <themanatuf@yahoo.com>
Subject: Performance Question
To: pgsql-performance@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-1445783529-1226507266=:42593"
Message-ID: <838490.42593.qm@web34202.mail.mud.yahoo.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200811/91
X-Sequence-Number: 31822

--0-1445783529-1226507266=:42593
Content-Type: text/plain; charset=us-ascii

I've been searching for performance metrics and tweaks for a few weeks now. I'm trying to determine if the length of time to process my queries is accurate or not and I'm having a difficult time determining that. I know postgres performance is very dependent on hardware and settings and I understand how difficult it is to tackle. However, I was wondering if I could get some feedback based on my results please.

The database is running on a dual-core 2GHz Opteron processor with 8GB of RAM. The drives are 10K RPM 146GB drives in RAID 5 (I've read RAID 5 is bad for Postgres, but moving the database to another server didn't change performance at all). Some of the key parameters from postgresql.conf are:

max_connections = 100
shared_buffers = 16MB
work_mem = 64MB
everything else is set to the default

One of my tables has 660,000 records and doing a SELECT * from that table (without any joins or sorts) takes 72 seconds. Ordering the table based on 3 columns almost doubles that time to an average of 123 seconds. To me, those numbers are crazy slow and I don't understand why the queries are taking so long. The tables are UTF-8 encode and contain a mix of languages (English, Spanish, etc). I'm running the query from pgadmin3 on a remote host. The server has nothing else running on it except the database.

As a test I tried splitting up the data across a number of other tables. I ran 10 queries (to correspond with the 10 tables) with a UNION ALL to join the results together. This was even slower, taking an average of 103 seconds to complete the generic select all query.

I'm convinced something is wrong, I just can't pinpoint where it is. I can provide any other information necessary. If anyone has any suggestions it would be greatly appreciated. 



      
--0-1445783529-1226507266=:42593
Content-Type: text/html; charset=us-ascii

<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:arial,helvetica,sans-serif;font-size:10pt"><div>I've been searching for performance metrics and tweaks for a few weeks now. I'm trying to determine if the length of time to process my queries is accurate or not and I'm having a difficult time determining that. I know postgres performance is very dependent on hardware and settings and I understand how difficult it is to tackle. However, I was wondering if I could get some feedback based on my results please.<br><br>The database is running on a dual-core 2GHz Opteron processor with 8GB of RAM. The drives are 10K RPM 146GB drives in RAID 5 (I've read RAID 5 is bad for Postgres, but moving the database to another server didn't change performance at all). Some of the key parameters from postgresql.conf are:<br><br>max_connections = 100<br>shared_buffers = 16MB<br>work_mem = 64MB<br>everything else is set
 to the default<br><br>One of my tables has 660,000 records and doing a SELECT * from that table (without any joins or sorts) takes 72 seconds. Ordering the table based on 3 columns almost doubles that time to an average of 123 seconds. To me, those numbers are crazy slow and I don't understand why the queries are taking so long. The tables are UTF-8 encode and contain a mix of languages (English, Spanish, etc). I'm running the query from pgadmin3 on a remote host. The server has nothing else running on it except the database.<br><br>As a test I tried splitting up the data across a number of other tables. I ran 10 queries (to correspond with the 10 tables) with a UNION ALL to join the results together. This was even slower, taking an average of 103 seconds to complete the generic select all query.<br><br>I'm convinced something is wrong, I just can't pinpoint where it is. I can provide any other information necessary. If anyone has any suggestions it
 would be greatly appreciated. <br></div></div><br>

      </body></html>
--0-1445783529-1226507266=:42593--



From sisson.j@gmail.com Wed Nov  12 16:54:27 2008
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id E0E7A64FEA9
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 12 Nov 2008 12:54:27 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 55278-03
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 12 Nov 2008 12:54:21 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from yw-out-2324.google.com (yw-out-2324.google.com [74.125.46.31])
	by mail.postgresql.org (Postfix) with ESMTP id 392AA64FD42
	for <pgsql-performance@postgresql.org>;
	Wed, 12 Nov 2008 12:54:20 -0400 (AST)
Received: by yw-out-2324.google.com with SMTP id 3so211807ywj.73
	for <pgsql-performance@postgresql.org>;
	Wed, 12 Nov 2008 08:54:19 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from:to
	:subject:cc:in-reply-to:mime-version:content-type:references;
	bh=cv+0LwqYXgSOSvYj+BbIp319j8FsM9eVZ5hRRxk6uXU=;
	b=UpNQ4NtARofHV/NaWHuUOxjcYjmBp9DV56c6OorlXV+j+iz1PxYcwK7KeTiG0mM/v/
	4JLOFm2zNMB4vGcZ6DUMEnEjrbbtrDgcjF2CRA+bCfqm03+7vl7sRlhr62l8Cy5QsZ0h
	D67sdJRRZHlECSoKbp6bBFVRIFBNfJ2FwagP0=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:references;
	b=x/84b12BsGTxFqfh2QKVW9sTh/EMTsIjqY0LOHK6pUWV7Ql9nxAGS1E/X4+8utfpP/
	xxpesIYfsOQj7wVCno89Ypoy2i+Bp3fNhMvZumvr9ZTQDXeEdbH3OW7VMfKEKjua5N73
	NoO3mr+A3uAkoSJtoSeajdPG+cFOpKh9D/Iw4=
Received: by 10.151.146.18 with SMTP id y18mr14384215ybn.122.1226508501719;
	Wed, 12 Nov 2008 08:48:21 -0800 (PST)
Received: by 10.150.204.20 with HTTP; Wed, 12 Nov 2008 08:48:21 -0800 (PST)
Message-ID: <4297a9020811120848t1e57b2f9g21247dc04d2564b1@mail.gmail.com>
Date: Wed, 12 Nov 2008 10:48:21 -0600
From: "J Sisson" <sisson.j@gmail.com>
To: "- -" <themanatuf@yahoo.com>
Subject: Re: Performance Question
Cc: pgsql-performance@postgresql.org
In-Reply-To: <838490.42593.qm@web34202.mail.mud.yahoo.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_4277_25756043.1226508501701"
References: <838490.42593.qm@web34202.mail.mud.yahoo.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200811/93
X-Sequence-Number: 31824

------=_Part_4277_25756043.1226508501701
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

There are a few things you didn't mention...

First off, what is the context this database is being used in?  Is it the
backend for a web server?  Data warehouse?  Etc?

Second, you didn't mention the use of indexes.  Do you have any indexes on
the table in question, and if so, does EXPLAIN ANALYZE show the planner
utilizing the index(es)?

Third, you have 8 GB of RAM on a dedicated machine.  Consider upping the
memory settings in postgresql.conf.  For instance, on my data warehouse
machines (8 GB RAM each) I have shared_buffers set to almost 2 GB and
effective_cache_size set to nearly 5.5 GB.  (This is dependent on how you're
utilizing this database, so don't blindly set these values!)

Last, you didn't mention what RAID level the other server you tested this on
was running.

On Wed, Nov 12, 2008 at 10:27 AM, - - <themanatuf@yahoo.com> wrote:

> I've been searching for performance metrics and tweaks for a few weeks now.
> I'm trying to determine if the length of time to process my queries is
> accurate or not and I'm having a difficult time determining that. I know
> postgres performance is very dependent on hardware and settings and I
> understand how difficult it is to tackle. However, I was wondering if I
> could get some feedback based on my results please.
>
> The database is running on a dual-core 2GHz Opteron processor with 8GB of
> RAM. The drives are 10K RPM 146GB drives in RAID 5 (I've read RAID 5 is bad
> for Postgres, but moving the database to another server didn't change
> performance at all). Some of the key parameters from postgresql.conf are:
>
> max_connections = 100
> shared_buffers = 16MB
> work_mem = 64MB
> everything else is set to the default
>
> One of my tables has 660,000 records and doing a SELECT * from that table
> (without any joins or sorts) takes 72 seconds. Ordering the table based on 3
> columns almost doubles that time to an average of 123 seconds. To me, those
> numbers are crazy slow and I don't understand why the queries are taking so
> long. The tables are UTF-8 encode and contain a mix of languages (English,
> Spanish, etc). I'm running the query from pgadmin3 on a remote host. The
> server has nothing else running on it except the database.
>
> As a test I tried splitting up the data across a number of other tables. I
> ran 10 queries (to correspond with the 10 tables) with a UNION ALL to join
> the results together. This was even slower, taking an average of 103 seconds
> to complete the generic select all query.
>
> I'm convinced something is wrong, I just can't pinpoint where it is. I can
> provide any other information necessary. If anyone has any suggestions it
> would be greatly appreciated.
>
>


-- 
Computers are like air conditioners...
They quit working when you open Windows.

------=_Part_4277_25756043.1226508501701
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

There are a few things you didn&#39;t mention...<br><br>First off, what is the context this database is being used in?&nbsp; Is it the backend for a web server?&nbsp; Data warehouse?&nbsp; Etc?<br><br>Second, you didn&#39;t mention the use of indexes.&nbsp; Do you have any indexes on the table in question, and if so, does EXPLAIN ANALYZE show the planner utilizing the index(es)?<br>
<br>Third, you have 8 GB of RAM on a dedicated machine.&nbsp; Consider upping the memory settings in postgresql.conf.&nbsp; For instance, on my data warehouse machines (8 GB RAM each) I have shared_buffers set to almost 2 GB and effective_cache_size set to nearly 5.5 GB.&nbsp; (This is dependent on how you&#39;re utilizing this database, so don&#39;t blindly set these values!)<br>
<br>Last, you didn&#39;t mention what RAID level the other server you tested this on was running.<br><br><div class="gmail_quote">On Wed, Nov 12, 2008 at 10:27 AM, - - <span dir="ltr">&lt;<a href="mailto:themanatuf@yahoo.com">themanatuf@yahoo.com</a>&gt;</span> wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div><div style="font-family: arial,helvetica,sans-serif; font-size: 10pt;"><div>I&#39;ve been searching for performance metrics and tweaks for a few weeks now. I&#39;m trying to determine if the length of time to process my queries is accurate or not and I&#39;m having a difficult time determining that. I know postgres performance is very dependent on hardware and settings and I understand how difficult it is to tackle. However, I was wondering if I could get some feedback based on my results please.<br>
<br>The database is running on a dual-core 2GHz Opteron processor with 8GB of RAM. The drives are 10K RPM 146GB drives in RAID 5 (I&#39;ve read RAID 5 is bad for Postgres, but moving the database to another server didn&#39;t change performance at all). Some of the key parameters from postgresql.conf are:<br>
<br>max_connections = 100<br>shared_buffers = 16MB<br>work_mem = 64MB<br>everything else is set
 to the default<br><br>One of my tables has 660,000 records and doing a SELECT * from that table (without any joins or sorts) takes 72 seconds. Ordering the table based on 3 columns almost doubles that time to an average of 123 seconds. To me, those numbers are crazy slow and I don&#39;t understand why the queries are taking so long. The tables are UTF-8 encode and contain a mix of languages (English, Spanish, etc). I&#39;m running the query from pgadmin3 on a remote host. The server has nothing else running on it except the database.<br>
<br>As a test I tried splitting up the data across a number of other tables. I ran 10 queries (to correspond with the 10 tables) with a UNION ALL to join the results together. This was even slower, taking an average of 103 seconds to complete the generic select all query.<br>
<br>I&#39;m convinced something is wrong, I just can&#39;t pinpoint where it is. I can provide any other information necessary. If anyone has any suggestions it
 would be greatly appreciated. <br></div></div><br>

      </div></blockquote></div><br><br clear="all"><br>-- <br>Computers are like air conditioners...<br>They quit working when you open Windows.<br>

------=_Part_4277_25756043.1226508501701--



From tv@fuzzy.cz Wed Nov  12 16:56:33 2008
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 428896501EA
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 12 Nov 2008 12:56:33 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 55368-07
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 12 Nov 2008 12:56:25 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail.gransy.com (unknown [82.208.29.208])
	by mail.postgresql.org (Postfix) with ESMTP id 2F1CC64FF5F
	for <pgsql-performance@postgresql.org>;
	Wed, 12 Nov 2008 12:56:24 -0400 (AST)
Received: from localhost (localhost [127.0.0.1])
	by mail.gransy.com (Postfix) with ESMTP id 173F67AC0B6;
	Wed, 12 Nov 2008 17:56:22 +0100 (CET)
Received: from mail.gransy.com ([127.0.0.1])
	by localhost (nathalia.gransy.com [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id 96SE38t1wUe4; Wed, 12 Nov 2008 17:56:21 +0100 (CET)
Received: from anastacia.gransy.com (anastacia.gransy.com [89.187.130.100])
	by mail.gransy.com (Postfix) with ESMTP id E692A7AC047;
	Wed, 12 Nov 2008 17:56:21 +0100 (CET)
Received: from sq.gransy.com (localhost [127.0.0.1])
	by anastacia.gransy.com (Postfix) with ESMTP id 0FD4831F86C;
	Wed, 12 Nov 2008 17:56:29 +0100 (CET)
Received: from 89.102.139.23 (SquirrelMail authenticated user tv@fuzzy.cz)
	by sq.gransy.com with HTTP; Wed, 12 Nov 2008 17:56:29 +0100 (CET)
Message-ID: <64051.89.102.139.23.1226508989.squirrel@sq.gransy.com>
In-Reply-To: <838490.42593.qm@web34202.mail.mud.yahoo.com>
References: <838490.42593.qm@web34202.mail.mud.yahoo.com>
Date: Wed, 12 Nov 2008 17:56:29 +0100 (CET)
Subject: Re: Performance Question
From: tv@fuzzy.cz
To: "- -" <themanatuf@yahoo.com>
Cc: pgsql-performance@postgresql.org
User-Agent: SquirrelMail/1.4.10a
MIME-Version: 1.0
Content-Type: text/plain;charset=iso-8859-2
Content-Transfer-Encoding: 8bit
X-Priority: 3 (Normal)
Importance: Normal
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.1 tagged_above=0 required=5 tests=RDNS_NONE=0.1
X-Spam-Level: 
X-Archive-Number: 200811/94
X-Sequence-Number: 31825

> max_connections = 100
> shared_buffers = 16MB
> work_mem = 64MB
> everything else is set to the default

OK, but what about effective_cache_size for example?

Anyway, we need more information about the table itself - the number of
rows is nice, but it does not say how large the table is. The rows might
be small (say 100B each) or large (say several kilobytes), affecting the
amount of data to be read.

We need to know the structure of the table, and the output of the
following commands:

ANALYZE table;
SELECT relpages, reltuples FROM pg_class WHERE relname = 'table';
EXPLAIN SELECT * FROM table;

>
> One of my tables has 660,000 records and doing a SELECT * from that table
> (without any joins or sorts) takes 72 seconds. Ordering the table based on
> 3 columns almost doubles that time to an average of 123 seconds. To me,
> those numbers are crazy slow and I don't understand why the queries are
> taking so long. The tables are UTF-8 encode and contain a mix of languages
> (English, Spanish, etc). I'm running the query from pgadmin3 on a remote
> host. The server has nothing else running on it except the database.
>
> As a test I tried splitting up the data across a number of other tables. I
> ran 10 queries (to correspond with the 10 tables) with a UNION ALL to join
> the results together. This was even slower, taking an average of 103
> seconds to complete the generic select all query.

Well, splitting the tables just to read all of them won't help. It will
make the problem even worse, due to the necessary processing (UNION ALL).

regards
Tomas




From fhevia@ip-tel.com.ar Wed Nov  12 17:15:36 2008
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 77C5A64FD42
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 12 Nov 2008 13:15:36 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 63651-03
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 12 Nov 2008 13:15:32 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from pyxis.powervt.com.ar (host6.powervt.com.ar [190.11.152.66])
	by mail.postgresql.org (Postfix) with ESMTP id 4C008650160
	for <pgsql-performance@postgresql.org>;
	Wed, 12 Nov 2008 13:15:31 -0400 (AST)
Received: from pcfhevia ([201.216.249.98])
	by pyxis.powervt.com.ar (IPGroup Mail Server) with ASMTP id 1OT48953;
	Wed, 12 Nov 2008 14:15:25 -0200
From: "Fernando Hevia" <fhevia@ip-tel.com.ar>
To: "'- -'" <themanatuf@yahoo.com>,
	<pgsql-performance@postgresql.org>
References: <838490.42593.qm@web34202.mail.mud.yahoo.com>
Subject: Re: Performance Question
Date: Wed, 12 Nov 2008 15:16:11 -0200
Organization: IP-TEL
Message-ID: <14FCA72EC23448AB8207FC08D52094F4@iptel.com.ar>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_030B_01C944D9.98C4B4F0"
X-Mailer: Microsoft Office Outlook 11
In-Reply-To: <838490.42593.qm@web34202.mail.mud.yahoo.com>
Thread-Index: AclE20Vr+y5kLWpiSPygzkW5rH9SjwACPSnw
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200811/96
X-Sequence-Number: 31827

This is a multi-part message in MIME format.

------=_NextPart_000_030B_01C944D9.98C4B4F0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Incrementing shared_buffers to 1024MB and set effective_cache_size to =
6000MB
and test again.
To speed up sort operations, increase work_mem till you notice an
improvement.
Play with those settings with different values.
=20


  _____ =20

De: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] En nombre de - -
Enviado el: Mi=E9rcoles, 12 de Noviembre de 2008 14:28
Para: pgsql-performance@postgresql.org
Asunto: [PERFORM] Performance Question


I've been searching for performance metrics and tweaks for a few weeks =
now.
I'm trying to determine if the length of time to process my queries is
accurate or not and I'm having a difficult time determining that. I know
postgres performance is very dependent on hardware and settings and I
understand how difficult it is to tackle. However, I was wondering if I
could get some feedback based on my results please.

The database is running on a dual-core 2GHz Opteron processor with 8GB =
of
RAM. The drives are 10K RPM 146GB drives in RAID 5 (I've read RAID 5 is =
bad
for Postgres, but moving the database to another server didn't change
performance at all). Some of the key parameters from postgresql.conf =
are:

max_connections =3D 100
shared_buffers =3D 16MB
work_mem =3D 64MB
everything else is set to the default

One of my tables has 660,000 records and doing a SELECT * from that =
table
(without any joins or sorts) takes 72 seconds. Ordering the table based =
on 3
columns almost doubles that time to an average of 123 seconds. To me, =
those
numbers are crazy slow and I don't understand why the queries are taking =
so
long. The tables are UTF-8 encode and contain a mix of languages =
(English,
Spanish, etc). I'm running the query from pgadmin3 on a remote host. The
server has nothing else running on it except the database.

As a test I tried splitting up the data across a number of other tables. =
I
ran 10 queries (to correspond with the 10 tables) with a UNION ALL to =
join
the results together. This was even slower, taking an average of 103 =
seconds
to complete the generic select all query.

I'm convinced something is wrong, I just can't pinpoint where it is. I =
can
provide any other information necessary. If anyone has any suggestions =
it
would be greatly appreciated.=20




------=_NextPart_000_030B_01C944D9.98C4B4F0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<STYLE type=3Dtext/css>DIV {
	MARGIN: 0px
}
</STYLE>

<META content=3D"MSHTML 6.00.6000.16735" name=3DGENERATOR></HEAD>
<BODY>
<DIV dir=3Dltr align=3Dleft><SPAN class=3D506173216-12112008><FONT =
face=3DArial=20
color=3D#0000ff size=3D2>Incrementing shared_buffers to 1024MB and=20
s</FONT></SPAN><SPAN class=3D506173216-12112008><FONT face=3DArial =
color=3D#0000ff=20
size=3D2>et effective_cache_size to 6000MB and test =
again.</FONT></SPAN></DIV>
<DIV dir=3Dltr align=3Dleft><SPAN class=3D506173216-12112008><FONT =
face=3DArial=20
color=3D#0000ff size=3D2>To speed up sort operations, increase work_mem =
till you=20
notice an improvement.</FONT></SPAN></DIV>
<DIV dir=3Dltr align=3Dleft><SPAN class=3D506173216-12112008><FONT =
face=3DArial=20
color=3D#0000ff size=3D2>Play with those settings with different=20
values.</FONT></SPAN></DIV>
<DIV dir=3Dltr align=3Dleft><SPAN class=3D506173216-12112008><FONT =
face=3DArial=20
color=3D#0000ff size=3D2></FONT></SPAN>&nbsp;</DIV><BR>
<BLOCKQUOTE=20
style=3D"PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #0000ff 2px =
solid; MARGIN-RIGHT: 0px">
  <DIV class=3DOutlookMessageHeader lang=3Des dir=3Dltr align=3Dleft>
  <HR tabIndex=3D-1>
  <FONT face=3DTahoma size=3D2><B>De:</B> =
pgsql-performance-owner@postgresql.org=20
  [mailto:pgsql-performance-owner@postgresql.org] <B>En nombre de </B>-=20
  -<BR><B>Enviado el:</B> Mi=E9rcoles, 12 de Noviembre de 2008=20
  14:28<BR><B>Para:</B> =
pgsql-performance@postgresql.org<BR><B>Asunto:</B>=20
  [PERFORM] Performance Question<BR></FONT><BR></DIV>
  <DIV></DIV>
  <DIV style=3D"FONT-SIZE: 10pt; FONT-FAMILY: =
arial,helvetica,sans-serif">
  <DIV>I've been searching for performance metrics and tweaks for a few =
weeks=20
  now. I'm trying to determine if the length of time to process my =
queries is=20
  accurate or not and I'm having a difficult time determining that. I =
know=20
  postgres performance is very dependent on hardware and settings and I=20
  understand how difficult it is to tackle. However, I was wondering if =
I could=20
  get some feedback based on my results please.<BR><BR>The database is =
running=20
  on a dual-core 2GHz Opteron processor with 8GB of RAM. The drives are =
10K RPM=20
  146GB drives in RAID 5 (I've read RAID 5 is bad for Postgres, but =
moving the=20
  database to another server didn't change performance at all). Some of =
the key=20
  parameters from postgresql.conf are:<BR><BR>max_connections =3D=20
  100<BR>shared_buffers =3D 16MB<BR>work_mem =3D 64MB<BR>everything else =
is set to=20
  the default<BR><BR>One of my tables has 660,000 records and doing a =
SELECT *=20
  from that table (without any joins or sorts) takes 72 seconds. =
Ordering the=20
  table based on 3 columns almost doubles that time to an average of 123 =

  seconds. To me, those numbers are crazy slow and I don't understand =
why the=20
  queries are taking so long. The tables are UTF-8 encode and contain a =
mix of=20
  languages (English, Spanish, etc). I'm running the query from pgadmin3 =
on a=20
  remote host. The server has nothing else running on it except the=20
  database.<BR><BR>As a test I tried splitting up the data across a =
number of=20
  other tables. I ran 10 queries (to correspond with the 10 tables) with =
a UNION=20
  ALL to join the results together. This was even slower, taking an =
average of=20
  103 seconds to complete the generic select all query.<BR><BR>I'm =
convinced=20
  something is wrong, I just can't pinpoint where it is. I can provide =
any other=20
  information necessary. If anyone has any suggestions it would be =
greatly=20
  appreciated. <BR></DIV></DIV><BR></BLOCKQUOTE></BODY></HTML>

------=_NextPart_000_030B_01C944D9.98C4B4F0--




From tgl@sss.pgh.pa.us Wed Nov  12 20:57:50 2008
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 1B72564FD9B
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 12 Nov 2008 16:57:50 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 11050-09
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 12 Nov 2008 16:57:41 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id 5E82C64FD2A
	for <pgsql-performance@postgresql.org>;
	Wed, 12 Nov 2008 16:57:39 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id mACKvcp7020745;
	Wed, 12 Nov 2008 15:57:38 -0500 (EST)
To: - - <themanatuf@yahoo.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Performance Question 
In-reply-to: <838490.42593.qm@web34202.mail.mud.yahoo.com> 
References: <838490.42593.qm@web34202.mail.mud.yahoo.com>
Comments: In-reply-to - - <themanatuf@yahoo.com>
	message dated "Wed, 12 Nov 2008 08:27:46 -0800"
Date: Wed, 12 Nov 2008 15:57:38 -0500
Message-ID: <20744.1226523458@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200811/110
X-Sequence-Number: 31841

- - <themanatuf@yahoo.com> writes:
> One of my tables has 660,000 records and doing a SELECT * from that table (without any joins or sorts) takes 72 seconds. Ordering the table based on 3 columns almost doubles that time to an average of 123 seconds. To me, those numbers are crazy slow and I don't understand why the queries are taking so long. The tables are UTF-8 encode and contain a mix of languages (English, Spanish, etc). I'm running the query from pgadmin3 on a remote host. The server has nothing else running on it except the database.

pgadmin has got its own performance issues with large select results.
Are you sure the bulk of the time isn't being spent on the client side?
Watching top or vmstat on both machines would probably tell much.

			regards, tom lane



From scott.marlowe@gmail.com Thu Nov  13 02:26:06 2008
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id C34EE64FEF7
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 12 Nov 2008 22:26:06 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 73148-07
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 12 Nov 2008 22:26:03 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from rv-out-0506.google.com (rv-out-0506.google.com
 [209.85.198.226])
	by mail.postgresql.org (Postfix) with ESMTP id 846E964FDA8
	for <pgsql-performance@postgresql.org>;
	Wed, 12 Nov 2008 22:26:03 -0400 (AST)
Received: by rv-out-0506.google.com with SMTP id b25so648975rvf.43
	for <pgsql-performance@postgresql.org>;
	Wed, 12 Nov 2008 18:26:02 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from:to
	:subject:cc:in-reply-to:mime-version:content-type
	:content-transfer-encoding:content-disposition:references;
	bh=iOPT0/4U8hyrxMJyKz07Mj27xnYMkBX0sRP+31xeIvM=;
	b=osJDEQKBx5buuVLihehVGAFc5A6mVNs303CSXcTfBDX2hqyeoAnTxOVTW0gd/V1LGc
	x2g6thkrZh/hxi5QllvPs2oAWaoRYDnL63pej2xmmk6oLt5jEBtw/yMIAkTJLFLIhMam
	5YlOdQRHQFgx4yhNUhlp+s0pkwUwDdaqIxG4I=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:content-transfer-encoding:content-disposition
	:references;
	b=Pb3qybBfOSSw9dBPd7yWcS7yK74tXMHTz1PhFfrdltVYeg/3Lp3ObshN6BHkPn1usV
	A6b5svFiCkcMavecfDX/ht+DW0oWEcZBkigFyx2NqSwol3zUslwc70OesUeJ0HB98qA4
	Hj8wboEDIpB3Igizr/iI7ofM60kmGXJ8/EXOU=
Received: by 10.115.94.1 with SMTP id w1mr6498566wal.30.1226543162115;
	Wed, 12 Nov 2008 18:26:02 -0800 (PST)
Received: by 10.114.39.11 with HTTP; Wed, 12 Nov 2008 18:26:02 -0800 (PST)
Message-ID: <dcc563d10811121826i114e9d47g894f5b729a8d08e@mail.gmail.com>
Date: Wed, 12 Nov 2008 19:26:02 -0700
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: "- -" <themanatuf@yahoo.com>
Subject: Re: Performance Question
Cc: pgsql-performance@postgresql.org
In-Reply-To: <838490.42593.qm@web34202.mail.mud.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <838490.42593.qm@web34202.mail.mud.yahoo.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200811/118
X-Sequence-Number: 31849

On Wed, Nov 12, 2008 at 9:27 AM, - - <themanatuf@yahoo.com> wrote:
> I've been searching for performance metrics and tweaks for a few weeks now.
> I'm trying to determine if the length of time to process my queries is
> accurate or not and I'm having a difficult time determining that. I know
> postgres performance is very dependent on hardware and settings and I
> understand how difficult it is to tackle. However, I was wondering if I
> could get some feedback based on my results please.
>
> The database is running on a dual-core 2GHz Opteron processor with 8GB of
> RAM. The drives are 10K RPM 146GB drives in RAID 5 (I've read RAID 5 is bad
> for Postgres, but moving the database to another server didn't change
> performance at all). Some of the key parameters from postgresql.conf are:

I'm not sure what you mean.  Did you move it to another server with a
single drive?  A 100 drive RAID-10 array with a battery backed caching
controller?  There's a lot of possibility in "another server".

>
> max_connections = 100
> shared_buffers = 16MB

WAY low.  try 512M to 2G on a machine that big.

> work_mem = 64MB

acceptable.  For 100 clients, if each did a sort you'd need 6.4Gig of
free ram, but since the chances of all 100 clients doing a sort that
big at the same time are small, you're probably safe.

>
> One of my tables has 660,000 records and doing a SELECT * from that table
> (without any joins or sorts) takes 72 seconds. Ordering the table based on 3
> columns almost doubles that time to an average of 123 seconds. To me, those

How wide is this table?  IF it's got 300 columns, then it's gonna be a
lot slower than if it has 10 columns.

Try running your query like this:

\timing
select count(*) from (my big query goes here) as a;

and see how long it takes.  This will remove the network effect of
transferring the data.  If that runs fast enough, then the real
problem is that your client is waiting til it gets all the data to
display it.



From dpage@pgadmin.org Thu Nov  13 08:55:09 2008
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 3D13164FCEF
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu, 13 Nov 2008 04:55:09 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 47750-07
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu, 13 Nov 2008 04:55:05 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from rv-out-0506.google.com (rv-out-0506.google.com
 [209.85.198.235])
	by mail.postgresql.org (Postfix) with ESMTP id 9C26E64FC39
	for <pgsql-performance@postgresql.org>;
	Thu, 13 Nov 2008 04:55:05 -0400 (AST)
Received: by rv-out-0506.google.com with SMTP id b25so759314rvf.43
	for <pgsql-performance@postgresql.org>;
	Thu, 13 Nov 2008 00:55:04 -0800 (PST)
Received: by 10.141.69.1 with SMTP id w1mr5370654rvk.215.1226566504407;
	Thu, 13 Nov 2008 00:55:04 -0800 (PST)
Received: by 10.140.128.14 with HTTP; Thu, 13 Nov 2008 00:55:04 -0800 (PST)
Message-ID: <937d27e10811130055n5dc00b63uaf1fce9096117a9e@mail.gmail.com>
Date: Thu, 13 Nov 2008 08:55:04 +0000
From: "Dave Page" <dpage@pgadmin.org>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Subject: Re: Performance Question
Cc: "- -" <themanatuf@yahoo.com>, pgsql-performance@postgresql.org
In-Reply-To: <20744.1226523458@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <838490.42593.qm@web34202.mail.mud.yahoo.com>
	<20744.1226523458@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200811/119
X-Sequence-Number: 31850

On Wed, Nov 12, 2008 at 8:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> - - <themanatuf@yahoo.com> writes:
>> One of my tables has 660,000 records and doing a SELECT * from that table (without any joins or sorts) takes 72 seconds. Ordering the table based on 3 columns almost doubles that time to an average of 123 seconds. To me, those numbers are crazy slow and I don't understand why the queries are taking so long. The tables are UTF-8 encode and contain a mix of languages (English, Spanish, etc). I'm running the query from pgadmin3 on a remote host. The server has nothing else running on it except the database.
>
> pgadmin has got its own performance issues with large select results.

They were fixed a couple of years ago. We're essentially at the mercy
of libpq now.


-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com



From mmoncure@gmail.com Thu Nov  13 13:07:36 2008
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id A810F650518
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu, 13 Nov 2008 09:07:36 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 99486-02-9
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu, 13 Nov 2008 09:07:33 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from ik-out-1112.google.com (ik-out-1112.google.com [66.249.90.177])
	by mail.postgresql.org (Postfix) with ESMTP id 1D54864FCF0
	for <pgsql-performance@postgresql.org>;
	Thu, 13 Nov 2008 09:05:57 -0400 (AST)
Received: by ik-out-1112.google.com with SMTP id c21so741637ika.3
	for <pgsql-performance@postgresql.org>;
	Thu, 13 Nov 2008 05:05:55 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from:to
	:subject:cc:in-reply-to:mime-version:content-type
	:content-transfer-encoding:content-disposition:references;
	bh=+TWFOk6TghiyyW4v+IXBpV2ME251ehh2qzt+8g4bzmc=;
	b=UA8DIRX0OXMMwSKtoF8mjMaXYmRrmwWpX90a/OpAxMmHhLDlkbQe0GRx8CwGtNiGH2
	ge3SusxUdRV6dN7Ybz5OlhXDt/dWtlp6+0vz40KKwPcmL+ZWjjWc+cNhIA++7BsKJK5u
	WbPh0cwP5WTNksh3FdKETVScV0APLoBULHbn0=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:content-transfer-encoding:content-disposition
	:references;
	b=DWa8qa6iU7b5EoPZhwO4Zw2F++CFpfewfH5er3lMXEJkVXYyDGPk0/3rEtSsVpf+Yt
	5nzjPWXYdoZiWHnyOJVVP86jQAQV1k4iRTFMuKoY300dE6+D4n0h9y+RQm6OhIpuwDgy
	JSTYY5Do/1JhCCQP13XSiLV5mpKEBbf4XWTa0=
Received: by 10.103.239.10 with SMTP id q10mr5813094mur.67.1226581555603;
	Thu, 13 Nov 2008 05:05:55 -0800 (PST)
Received: by 10.102.247.18 with HTTP; Thu, 13 Nov 2008 05:05:55 -0800 (PST)
Message-ID: <b42b73150811130505p5ae4e17elb3d8e275af5b3a68@mail.gmail.com>
Date: Thu, 13 Nov 2008 08:05:55 -0500
From: "Merlin Moncure" <mmoncure@gmail.com>
To: "- -" <themanatuf@yahoo.com>
Subject: Re: Performance Question
Cc: pgsql-performance@postgresql.org
In-Reply-To: <838490.42593.qm@web34202.mail.mud.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <838490.42593.qm@web34202.mail.mud.yahoo.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200811/121
X-Sequence-Number: 31852

On Wed, Nov 12, 2008 at 11:27 AM, - - <themanatuf@yahoo.com> wrote:
> I've been searching for performance metrics and tweaks for a few weeks now.
> I'm trying to determine if the length of time to process my queries is
> accurate or not and I'm having a difficult time determining that. I know
> postgres performance is very dependent on hardware and settings and I
> understand how difficult it is to tackle. However, I was wondering if I
> could get some feedback based on my results please.
>
> The database is running on a dual-core 2GHz Opteron processor with 8GB of
> RAM. The drives are 10K RPM 146GB drives in RAID 5 (I've read RAID 5 is bad
> for Postgres, but moving the database to another server didn't change
> performance at all). Some of the key parameters from postgresql.conf are:
>
> max_connections = 100
> shared_buffers = 16MB
> work_mem = 64MB
> everything else is set to the default
>
> One of my tables has 660,000 records and doing a SELECT * from that table
> (without any joins or sorts) takes 72 seconds. Ordering the table based on 3
> columns almost doubles that time to an average of 123 seconds. To me, those
> numbers are crazy slow and I don't understand why the queries are taking so
> long. The tables are UTF-8 encode and contain a mix of languages (English,
> Spanish, etc). I'm running the query from pgadmin3 on a remote host. The
> server has nothing else running on it except the database.
>
> As a test I tried splitting up the data across a number of other tables. I
> ran 10 queries (to correspond with the 10 tables) with a UNION ALL to join
> the results together. This was even slower, taking an average of 103 seconds
> to complete the generic select all query.
>
> I'm convinced something is wrong, I just can't pinpoint where it is. I can
> provide any other information necessary. If anyone has any suggestions it
> would be greatly appreciated.

Maybe there is a lot of dead rows?  Do a
VACUUM VERBOSE;

That performance is quite slow unless the rows are really big (you
have huge text or bytea columns).  What is the average row size in
bytes?  Try running the following command as a benchmark:

select generate_series(1,500000);

on my imac that takes about 600ms.

merlin



From lists@peufeu.com Sun Nov  16 15:19:52 2008
Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 5047D64FCE3
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sun, 16 Nov 2008 11:19:52 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 19875-05
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sun, 16 Nov 2008 11:19:49 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from 27.mail-out.ovh.net (27.mail-out.ovh.net [91.121.30.210])
	by mail.postgresql.org (Postfix) with SMTP id D254864FCAC
	for <pgsql-performance@postgresql.org>;
	Sun, 16 Nov 2008 11:19:48 -0400 (AST)
Received: (qmail 32567 invoked by uid 503); 16 Nov 2008 16:34:20 -0000
Received: from gw2.ovh.net (HELO mail54.ha.ovh.net) (213.251.189.202)
	by 27.mail-out.ovh.net with SMTP; 16 Nov 2008 16:34:20 -0000
Received: from b0.ovh.net (HELO queue-out) (213.186.33.50)
	by b0.ovh.net with SMTP; 16 Nov 2008 15:19:22 -0000
Received: from par69-8-88-161-102-87.fbx.proxad.net (HELO soyouz)
	(88.161.102.87) by ns0.ovh.net with SMTP; 16 Nov 2008 15:19:16 -0000
Date: Sun, 16 Nov 2008 16:20:04 +0100
To: "- -" <themanatuf@yahoo.com>, pgsql-performance@postgresql.org
Subject: Re: Performance Question
From: PFC <lists@peufeu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
References: <838490.42593.qm@web34202.mail.mud.yahoo.com>
Content-Transfer-Encoding: 7bit
Message-ID: <op.ukpwvq1fcigqcu@soyouz>
In-Reply-To: <838490.42593.qm@web34202.mail.mud.yahoo.com>
User-Agent: Opera Mail/9.62 (Linux)
X-Ovh-Tracer-Id: 4175962754579564059
X-Ovh-Remote: 88.161.102.87 (par69-8-88-161-102-87.fbx.proxad.net)
X-Ovh-Local: 213.186.33.20 (ns0.ovh.net)
X-Spam-Check: DONE|H 0.5/N
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200811/142
X-Sequence-Number: 31873


> I've been searching for performance metrics and tweaks for a few weeks  
> now. I'm trying to determine if the length of time to process my queries  
> is accurate or not and I'm having a difficult time determining that. I  
> know postgres performance is very dependent on hardware and settings and  
> I understand how difficult it is to tackle. However, I was wondering if  
> I could get some feedback based on my results please.

	Well, the simplest thing is to measure the time it takes to process a  
query, but :

	- EXPLAIN ANALYZE will always report a longer time than the reality,  
because instrumenting the query takes time. For instance, EXPLAIN ANALYZE  
on a count(*) on a query could take more time to count how many times the  
"count" aggregate is called and how much time is spent in it, than to  
actually compute the aggregate... This is because it takes much longer to  
measure the time it takes to call "count" on a row (syscalls...) than it  
takes to increment the count.
	This is not a problem as long as you are aware of it, and the information  
provided by EXPLAIN ANALYZE is very valuable.

	- Using \timing in psql is also a good way to examine queries, but if  
your query returns lots of results, the time it takes for the client to  
process those results will mess with your measurements. In this case a  
simple : SELECT sum(1) FROM (your query) can provide less polluted  
timings. Remember you are not that interested in client load : you can  
always add more webservers, but adding more database servers is a lot more  
difficult.

	- You can add some query logging in your application (always a good idea  
IMHO). For instance, the administrator (you) could see a list of queries  
at the bottom of the page with the time it takes to run them. In that  
case, keep in mind that any load will add randomness to this measurements.  
For instance, when you hit F5 in your browser, of the webserver and  
database run on the same machine as the browser, the browser's CPU usage  
can make one of your queries appear to take up to half a second... even if  
it takes, in reality, half a millisecond... So, average.
	You could push the idea further. Sometimes I log the parameterized query  
(without args), the args separately, and the query time, so I can get  
average timings for things like "SELECT stuff FROM table WHERE column=$1",  
not get a zillion separate queries depending on the parameters. Such  
logging can destroy your performance, though, use with care.

	OF COURSE YOU SHOULD MEASURE WHAT IS RELEVANT, that is, queries that your  
application uses.

> The database is running on a dual-core 2GHz Opteron processor with 8GB  
> of RAM.

	8GB. 64 bits I presume ?

> The drives are 10K RPM 146GB drives in RAID 5 (I've read RAID 5 is bad  
> for Postgres, but moving the database to another server didn't change  
> performance at all).

	RAID5 = good for reads, and large writes.
	RAID5 = hell for small random writes.
	Depends on your load...
	
> shared_buffers = 16MB

	That's a bit small IMHO. (try 2 GB).

> work_mem = 64MB
> everything else is set to the default
>
> One of my tables has 660,000 records and doing a SELECT * from that  
> table (without any joins or sorts) takes 72 seconds.

	Well, sure, but why would you do such a thing ? I mean, I don't know your  
row size, but say it is 2 KB, you just used 1.5 GB of RAM on the client  
and on the server. Plus of course transferring all this data over your  
network connection. If client and server are on the same machine, you just  
zapped 3 GB of RAM. I hope you don't do too many of those concurrently...
	This is never going to be fast and it is never going to be a good  
performance metric.

	If you need to pull 600.000 rows from a table, use a CURSOR, and pull  
them in batches of say, 1000.
	Then you will use 600 times less RAM. I hope you have gigabit ethernet  
though. Network and disk IO will be your main bottleneck.

	If you don't need to pull 600.000 rows from a table, well then, don't do  
it.

	If you're using a client app to display the results, well, how long does  
it take to display 600.000 rows in a GUI box ?...

> Ordering the table based on 3 columns almost doubles that time to an  
> average of 123 seconds.

	Same as above, if your rows are small, say 100 bytes, you're sorting 66  
megabytes, which would easily be done in RAM, but you specified work_mem  
too small, so it is done on disk, with several passes. If your rows are  
large, well you're facing a multi gigabyte disksort with only 64 MB of  
working memory, so it's really going to take lots of passes.

	If you often need to pull 600.000 rows from a table in a specific order,  
create an index on the column, use a CURSOR, and pull them in batches of  
say, 1000.
	If you seldom need to, don't create an index but do use a CURSOR, and  
pull them in batches of say, 1000.
	If you don't need to pull 600.000 rows from a table in a specific order,  
well then, don't do it.

> To me, those numbers are crazy slow and I don't understand why the  
> queries are taking so long. The tables are UTF-8 encode and contain a  
> mix of languages (English, Spanish, etc). I'm running the query from  
> pgadmin3 on a remote host. The server has nothing else running on it  
> except the database.

	OK, I presume you are sorting UNICODE strings (which is also slower than  
binary compare) so in this case you should really try to minimize the  
number of string comparisons which means using a much larger work_mem.

> I'm convinced something is wrong, I just can't pinpoint where it is. I  
> can provide any other information necessary. If anyone has any  
> suggestions it would be greatly appreciated.

	Well, the big questions are :

	- do you need to run this query often ?
	- what do you use it for ?
	- how many bytes does it weigh ?

	Until you answer that, it is difficult to help...