Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 77C5A64FD42 for ; 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 ; 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 ; 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" To: "'- -'" , 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
Incrementing shared_buffers to 1024MB and=20 set effective_cache_size to 6000MB and test = again.
To speed up sort operations, increase work_mem = till you=20 notice an improvement.
Play with those settings with different=20 values.
 

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

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.

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:

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

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.

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.

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.

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