From: Tomas Vondra <tv@fuzzy.cz>
To: pgsql-performance@postgresql.org
Subject: Re: postgres performance
Date: Sat, 07 Dec 2013 01:13:29 +0100
Message-ID: <52A26829.5040408@fuzzy.cz> (raw)
In-Reply-To: <CABUk_4jr9z3CU-5b8tMvgEhAsKJ8+JKRn+TivPGAis9FOZo1jQ@mail.gmail.com>
References: <CABUk_4jr9z3CU-5b8tMvgEhAsKJ8+JKRn+TivPGAis9FOZo1jQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 6.12.2013 18:36, chidamparam muthusamy wrote:
> hi,
> Registered with PostgreSQL Help Forum to identify and resolve the
> Postgres DB performance issue, received suggestions but could not
> improve the speed/response time. Please help.
>
> Details:
> Postgres Version 9.3.1
> Server configuration:
> Processor: 2 x Intel Quad core E5620 @ 2.40GHz
> RAM: 16 GB
>
> Postgres configuration:
> Effective cache size = 10 GB
> shared Buffer = 1250 MB
> random page cost = 4
>
> Table size = 60 GB
> Number of records = 44 million
> Carried out Vacuum Analyze after inserting new records and also after
> creating Index,
> 6 months data, every month around 10 GB will get added. Expecting good
> performance with 3 years data.
So, what is good performance? What times do you need to achieve for the
queries you've posted?
It's difficult to read the explain plans wrapped in the message, so I've
pasted some of them into explain.depesz.com:
http://explain.depesz.com/s/9SHhttp://explain.depesz.com/s/hFp
The estimates are very accurate, and as Richard Huxton pointed out, the
dominating part is the bitmap heap scan. I assume this is because or
reading the data from disk. Can you check iostat/vmstat while the
queries are running? Are you CPU or I/O bound? I'd guess the latter.
In that case, adding more RAM / more powerful I/O is probably the
easiest way to improve the performance. And a partitioning (but that
depends on the queries, as it may improve some and hurt others).
regards
Tomas
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: tv@fuzzy.cz
Subject: Re: postgres performance
In-Reply-To: <52A26829.5040408@fuzzy.cz>
* 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