From: Fernando Hevia <fhevia@ip-tel.com.ar>
To: '- -' <themanatuf@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: Re: Performance Question
Date: Wed, 12 Nov 2008 15:16:11 -0200
Message-ID: <14FCA72EC23448AB8207FC08D52094F4@iptel.com.ar> (raw)
In-Reply-To: <838490.42593.qm@web34202.mail.mud.yahoo.com>
References: <838490.42593.qm@web34202.mail.mud.yahoo.com>
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.
_____
De: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] En nombre de - -
Enviado el: Miércoles, 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 = 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.
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: fhevia@ip-tel.com.ar, themanatuf@yahoo.com
Subject: Re: Performance Question
In-Reply-To: <14FCA72EC23448AB8207FC08D52094F4@iptel.com.ar>
* 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