From: Gavin Flower <GavinFlower@archidevsys.co.nz>
To: Anne Rosset <arosset@collab.net>
Cc: Thomas Kellerer <spam_eater@gmx.net>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Deterioration in performance when query executed in multi threads
Date: Tue, 07 May 2013 10:11:30 +1200
Message-ID: <51882A92.1060906@archidevsys.co.nz> (raw)
In-Reply-To: <9A6B86D66BD3C2438AFACFA09354890F20A30CE3@EXCH01.sp.corp.collab.net>
References: <9A6B86D66BD3C2438AFACFA09354890F20A2DF6D@EXCH01.sp.corp.collab.net>
<20130501141236.GG27807@aart.rice.edu>
<9A6B86D66BD3C2438AFACFA09354890F20A2E06C@EXCH01.sp.corp.collab.net>
<20130501162650.GI27807@aart.rice.edu>
<9A6B86D66BD3C2438AFACFA09354890F20A2E0E2@EXCH01.sp.corp.collab.net>
<A76B25F2823E954C9E45E32FA49D70EC1B7C92E0@mail.corp.perceptron.com>
<9A6B86D66BD3C2438AFACFA09354890F20A2F24A@EXCH01.sp.corp.collab.net>
<A76B25F2823E954C9E45E32FA49D70EC1B7CAE91@mail.corp.perceptron.com>
<9A6B86D66BD3C2438AFACFA09354890F20A30B1B@EXCH01.sp.corp.collab.net>
<km8o5u$l2t$1@ger.gmane.org>
<9A6B86D66BD3C2438AFACFA09354890F20A30CE3@EXCH01.sp.corp.collab.net>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Anne, please read the comment at the bottom of this post!
On 07/05/13 09:46, Anne Rosset wrote:
> Hi Thomas,
> It is not a dedicated box (we have Jboss running too).
>
> cpu_tuple_cost | 0.01
> seq_page_cost | 1
> random_page_cost | 4
> effective_cache_size | 512MB
>
> We have the data directory on nfs (rw,intr,hard,tcp,rsize=32768,wsize=32768,nfsvers=3,tcp). Note that we have also tested putting the data directory on local disk and didn't find a big improvement.
>
> Thanks,
> Anne
>
>
>
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Thomas Kellerer
> Sent: Monday, May 06, 2013 10:12 AM
> To: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Deterioration in performance when query executed in multi threads
>
> Anne Rosset, 06.05.2013 19:00:
>> Postgres version: 9.0.13
>>
>>> Work_mem is set to 64MB
>>> Shared_buffer to 240MB
>>> Segment_size is 1GB
>>> Wal_buffer is 10MB
>> Artifact table: 251831 rows
>> Field_value table: 77378 rows
>> Mntr_subscription: 929071 rows
>> Relationship: 270478 row
>> Folder: 280356 rows
>> Item: 716465 rows
>> Sfuser: 5733 rows
>> Project: 1817 rows
>>
>> 8CPUs
>> RAM: 8GB
>>
> With 8GB RAM you should be able to increase shared_buffer to 1GB or maybe even higher especially if this is a dedicated server.
> 240MB is pretty conservative for a server with that amount of RAM (unless you have many other applications running on that box)
>
> Also what are the values for
>
> cpu_tuple_cost
> seq_page_cost
> random_page_cost
> effective_cache_size
>
> What kind of harddisk is in the server? SSD? Regular ones (spinning disks)?
>
>
The policy on this list is to add comments at the bottom, so people can
first read what you are replying to.
Though you can intersperse comments where that is apprporiate.
Cheers,
Gavin
--
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: GavinFlower@archidevsys.co.nz, arosset@collab.net, spam_eater@gmx.net
Subject: Re: Deterioration in performance when query executed in multi threads
In-Reply-To: <51882A92.1060906@archidevsys.co.nz>
* 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