Skip site navigation (1) Skip section navigation (2)

Re: Parallel Select query performance and shared buffers

From: Claudio Freire <klaussfreire(at)gmail(dot)com>
To: Metin Doslu <metin(at)citusdata(dot)com>
Cc: postgres performance list <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Parallel Select query performance and shared buffers
Date: 2013-12-03 15:56:11
Message-ID: CAGTBQpZtU0yo=eV7Xxw6gCvMoMkMZ-qFi+2BxgSisdzrn44c3Q@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
On Tue, Dec 3, 2013 at 10:49 AM, Metin Doslu <metin(at)citusdata(dot)com> wrote:
> We have several independent tables on a multi-core machine serving Select
> queries. These tables fit into memory; and each Select queries goes over one
> table's pages sequentially. In this experiment, there are no indexes or
> table joins.
>
> When we send concurrent Select queries to these tables, query performance
> doesn't scale out with the number of CPU cores. We find that complex Select
> queries scale out better than simpler ones. We also find that increasing the
> block size from 8 KB to 32 KB, or increasing shared_buffers to include the
> working set mitigates the problem to some extent.
>
> For our experiments, we chose an 8-core machine with 68 GB of memory from
> Amazon's EC2 service. We installed PostgreSQL 9.3.1 on the instance, and set
> shared_buffers to 4 GB.


If you are certain your tables fit in RAM, you may want to disable
synchronized sequential scans, as they will create contention between
the threads.


In response to

Responses

pgsql-performance by date

Next:From: Metin DosluDate: 2013-12-03 16:24:55
Subject: Re: Parallel Select query performance and shared buffers
Previous:From: Alvaro HerreraDate: 2013-12-03 13:53:23
Subject: Re: Parallel Select query performance and shared buffers

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2013-12-03 16:12:13
Subject: Re: pg_upgrade segfaults when given an invalid PGSERVICE value
Previous:From: Antonin HouskaDate: 2013-12-03 15:48:31
Subject: Review: ECPG infrastructure changes part 1, was: Re: ECPG fixes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group