Postgres Professional
en

Re: Feature Request --- was: PostgreSQL Performance Tuning

От: Sebastian Hennebrueder
Тема: Re: Feature Request --- was: PostgreSQL Performance Tuning
Дата: ,
Msg-id: 463CA8B9.4080808@laliluna.de
(см: обсуждение, исходный текст)
Ответ на: Re: Feature Request --- was: PostgreSQL Performance Tuning  (Mark Kirkwood)
Список: pgsql-performance

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

Re: [GENERAL] PostgreSQL Performance Tuning  (Steve Crawford, )
 Feature Request --- was: PostgreSQL Performance Tuning  (Carlos Moreno, )
  Re: Feature Request --- was: PostgreSQL Performance Tuning  (Tom Lane, )
   Re: Feature Request --- was: PostgreSQL Performance Tuning  (Carlos Moreno, )
    Re: Feature Request --- was: PostgreSQL Performance Tuning  (Michael Stone, )
     Re: Feature Request --- was: PostgreSQL Performance Tuning  (Mark Lewis, )
      Re: Feature Request --- was: PostgreSQL Performance Tuning  (Michael Stone, )
     Re: Feature Request --- was: PostgreSQL Performance Tuning  (Jim Nasby, )
     Re: Feature Request --- was: PostgreSQL Performance Tuning  (Dan Harris, )
      Re: Feature Request --- was: PostgreSQL Performance Tuning  (Josh Berkus, )
       Re: Feature Request --- was: PostgreSQL Performance Tuning  (Greg Smith, )
        Re: Feature Request --- was: PostgreSQL Performance Tuning  ("Craig A. James", )
         Re: Feature Request --- was: PostgreSQL Performance Tuning  (Kevin Hunter, )
          Re: Feature Request --- was: PostgreSQL Performance Tuning  (Greg Smith, )
        Re: Feature Request --- was: PostgreSQL Performance Tuning  (Josh Berkus, )
         Re: Feature Request --- was: PostgreSQL Performance Tuning  (Carlos Moreno, )
          Re: Feature Request --- was: PostgreSQL Performance Tuning  (, )
           Re: Feature Request --- was: PostgreSQL Performance Tuning  (Sebastian Hennebrueder, )
            Re: Feature Request --- was: PostgreSQL Performance Tuning  (Josh Berkus, )
             Re: Feature Request --- was: PostgreSQL Performance Tuning  (Sebastian Hennebrueder, )
             Re: Feature Request --- was: PostgreSQL Performance Tuning  (Mark Kirkwood, )
              Re: Feature Request --- was: PostgreSQL Performance Tuning  (Sebastian Hennebrueder, )
             Re: Feature Request --- was: PostgreSQL Performance Tuning  (Jim Nasby, )
              Re: Feature Request --- was: PostgreSQL Performance Tuning  (Andreas Kostyrka, )
         Re: Feature Request --- was: PostgreSQL Performance Tuning  (Greg Smith, )
          Re: Feature Request --- was: PostgreSQL Performance Tuning  (, )
          Re: Feature Request --- was: PostgreSQL Performance Tuning  (Josh Berkus, )
           Re: Feature Request --- was: PostgreSQL Performance Tuning  (, )
            Re: Feature Request --- was: PostgreSQL Performance Tuning  (Carlos Moreno, )
             Re: Feature Request --- was: PostgreSQL Performance Tuning  (, )
              Re: Feature Request --- was: PostgreSQL Performance Tuning  (Carlos Moreno, )
               Re: Feature Request --- was: PostgreSQL Performance Tuning  (, )
                Re: Feature Request --- was: PostgreSQL Performance Tuning  (Carlos Moreno, )
                 Re: Feature Request --- was: PostgreSQL Performance Tuning  (, )
                  Re: Feature Request --- was: PostgreSQL Performance Tuning  (Carlos Moreno, )
                   Re: Feature Request --- was: PostgreSQL Performance Tuning  (, )
           Re: Feature Request --- was: PostgreSQL Performance Tuning  (Greg Smith, )
            Re: Feature Request --- was: PostgreSQL Performance Tuning  (Michael Stone, )
             Re: Feature Request --- was: PostgreSQL Performance Tuning  (Greg Smith, )
              Re: Feature Request --- was: PostgreSQL Performance Tuning  ("Steinar H. Gunderson", )
       Re: Feature Request --- was: PostgreSQL Performance Tuning  (Ron, )
      Re: Feature Request --- was: PostgreSQL Performance Tuning  (Bill Moran, )
       Re: Feature Request --- was: PostgreSQL Performance Tuning  (Josh Berkus, )
       Re: Feature Request --- was: PostgreSQL Performance Tuning  (Dan Harris, )
        Re: Feature Request --- was: PostgreSQL Performance Tuning  (Josh Berkus, )
         Re: Feature Request --- was: PostgreSQL Performance Tuning  (, )
    Re: Feature Request --- was: PostgreSQL Performance Tuning  (Tom Lane, )
     Re: Feature Request --- was: PostgreSQL Performance Tuning  ("H.J. Sanders", )
     Re: Feature Request --- was: PostgreSQL Performance Tuning  (Kevin Hunter, )
      Re: Feature Request --- was: PostgreSQL Performance Tuning  (Ray Stell, )
    Re: Feature Request --- was: PostgreSQL Performance Tuning  ("Harald Armin Massa", )
     Re: Feature Request --- was: PostgreSQL Performance Tuning  ("Jonah H. Harris", )
      Re: Feature Request --- was: PostgreSQL Performance Tuning  ("Harald Armin Massa", )
     Re: Feature Request --- was: PostgreSQL Performance Tuning  (Carlos Moreno, ) 
Mark Kirkwood schrieb:
> Josh Berkus wrote:
>> Sebastian,
>>
>>> Before inventing a hyper tool, we might consider to provide 3-5 example
>>> szenarios for common hardware configurations. This consumes less time
>>> and be discussed and defined in a couple of days. This is of course not
>>> the correct option for a brandnew 20 spindle Sata 10.000 Raid 10 system
>>> but these are probably not the target for default configurations.
>>
>> That's been suggested a number of times, but some GUCs are really
>> tied to the *exact* amount of RAM you have available.  So I've never
>> seen how "example configurations" could help.
>>
>
> I'm not convinced about this objection - having samples gives a bit of
> a heads up on *what* knobs you should at least look at changing.
>
> Also it might be helpful on the -general or -perf lists to be able to
> say "try config 3 (or whatever we call 'em) and see what changes..."
>
> I've certainly found the sample config files supplied with that
> database whose name begins with 'M' a useful *start* when I want
> something better than default...
>
> Cheers
>
> Mark
>
Some ideas about szenarios and setting. This is meant as a discussion
proposal, I am by far not a database guru!
The settings do not provide a perfect setup but a more efficient as
compared to default setup.

criterias:
free memory
cpu ? what is the consequence?
separate spindels
total connections
Windows/linux/soloars ?

adapted settings:
max_connections
shared_buffers
effective_cache_size
/work_mem
//maintenance_work_mem

/checkpoint_segments ?
checkpoint_timeout ?
checkpoint_warning ?


Szenario a) 256 MB free memory, one disk or raid where all disks are in
the raid,
max_connections = 40
shared_buffers = 64MB
effective_cache_size = 180 MB
/work_mem = 1 MB
//maintenance_work_mem = 4 MB
/

Szenario b) 1024 MB free memory, one disk or raid where all disks are in
the raid
max_connections = 80
shared_buffers = 128 MB
effective_cache_size = 600 MB
/work_mem = 1,5 MB
//maintenance_work_mem = 16 MB
/
Szenario c) 2048 MB free memory, one disk or raid where all disks are in
the raid
max_connections = 160
shared_buffers = 256 MB
effective_cache_size = 1200 MB
/work_mem = 2 MB
//maintenance_work_mem = 32 MB
/
Szenario d) 2048 MB free memory, raid of multiple discs, second raid or
disk
max_connections = 160
shared_buffers = 256 MB
effective_cache_size = 1200 MB
/work_mem = 2 MB/
/maintenance_work_mem = 32 MB
/WAL on second spindle

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

Предыдущее:
От: Sebastian Hennebrueder
Дата:
Сообщение: Re: Feature Request --- was: PostgreSQL Performance Tuning
Следующее:
От: Yudhvir Singh Sidhu
Дата:
Сообщение: How to Find Cause of Long Vacuum Times - NOOB Question