Re: Deterioration in performance when query executed in multi threads

От: Gavin Flower
Тема: Re: Deterioration in performance when query executed in multi threads
Дата: ,
Msg-id: 51882A92.1060906@archidevsys.co.nz
(см: обсуждение, исходный текст)
Ответ на: Re: Deterioration in performance when query executed in multi threads  (Anne Rosset)
Список: pgsql-performance

Скрыть дерево обсуждения

Deterioration in performance when query executed in multi threads  (Anne Rosset, )
 Re: Deterioration in performance when query executed in multi threads  ("", )
  Re: Deterioration in performance when query executed in multi threads  (Anne Rosset, )
   Re: Deterioration in performance when query executed in multi threads  ("", )
    Re: Deterioration in performance when query executed in multi threads  (Scott Marlowe, )
    Re: Deterioration in performance when query executed in multi threads  (Anne Rosset, )
     Re: Deterioration in performance when query executed in multi threads  (Igor Neyman, )
      Re: Deterioration in performance when query executed in multi threads  (Anne Rosset, )
       Re: Deterioration in performance when query executed in multi threads  (Igor Neyman, )
        Re: Deterioration in performance when query executed in multi threads  (Anne Rosset, )
         Re: Deterioration in performance when query executed in multi threads  (Thomas Kellerer, )
          Re: Deterioration in performance when query executed in multi threads  (Igor Neyman, )
          Re: Deterioration in performance when query executed in multi threads  (Anne Rosset, )
           Re: Deterioration in performance when query executed in multi threads  (Gavin Flower, )
         Re: Deterioration in performance when query executed in multi threads  (Igor Neyman, )
          Re: Deterioration in performance when query executed in multi threads  (Anne Rosset, )
           Re: Deterioration in performance when query executed in multi threads  (Igor Neyman, )
            Re: Deterioration in performance when query executed in multi threads  (Anne Rosset, )
       Re: Deterioration in performance when query executed in multi threads  (Merlin Moncure, )

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
testedputting the data directory on local disk and didn't find a big improvement. 
>
> Thanks,
> Anne
>
>
>
> -----Original Message-----
> From:  [mailto:] On Behalf Of Thomas
Kellerer
> Sent: Monday, May 06, 2013 10:12 AM
> To: 
> 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
dedicatedserver. 
> 240MB is pretty conservative for a server with that amount of RAM (unless you have many other applications running on
thatbox) 
>
> 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



В списке pgsql-performance по дате сообщения:

От: Igor Neyman
Дата:
Сообщение: Re: Deterioration in performance when query executed in multi threads
От: Simon Riggs
Дата:
Сообщение: Re: In progress INSERT wrecks plans on table