От: Alexander Priem
Тема: Re: Tuning PostgreSQL
Дата: ,
Msg-id: 006801c35050$518c49a0$b696a8c0@APR
(см: обсуждение, исходный текст)
Ответ на: Re: Tuning PostgreSQL  ("Shridhar Daithankar")
Ответы: Re: Tuning PostgreSQL  (Ron Johnson)
Re: Tuning PostgreSQL  (Andrew McMillan)
Список: pgsql-performance

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

Tuning PostgreSQL  ("Alexander Priem", )
 Re: Tuning PostgreSQL  ("Shridhar Daithankar", )
 Re: Tuning PostgreSQL  ("Shridhar Daithankar", )
  Re: Tuning PostgreSQL  ("Alexander Priem", )
  Re: Tuning PostgreSQL  (Ang Chin Han, )
   Re: Tuning PostgreSQL  ("Shridhar Daithankar", )
    Re: Tuning PostgreSQL  (Ang Chin Han, )
     Re: Tuning PostgreSQL  ("Shridhar Daithankar", )
  Re: Tuning PostgreSQL  ("Alexander Priem", )
   Re: Tuning PostgreSQL  ("Shridhar Daithankar", )
    Re: Tuning PostgreSQL  ("Alexander Priem", )
  Re: Tuning PostgreSQL  (Ron Johnson, )
  Re: Tuning PostgreSQL  ("Alexander Priem", )
   Re: Tuning PostgreSQL  (Ron Johnson, )
   Re: Tuning PostgreSQL  (Andrew McMillan, )
    Re: Tuning PostgreSQL  ("Arjen van der Meijden", )
     Re: Tuning PostgreSQL  (Tom Lane, )
     Re: Tuning PostgreSQL  ("Balazs Wellisch", )
      Re: Tuning PostgreSQL  (Josh Berkus, )
 Re: Tuning PostgreSQL  ("Roman Fail", )
  Re: Tuning PostgreSQL  ("Alexander Priem", )
   Re: Tuning PostgreSQL  (Josh Berkus, )
    Re: Tuning PostgreSQL  (Vincent van Leeuwen, )
  Re: Tuning PostgreSQL  ("Alexander Priem", )
   Re: Tuning PostgreSQL  (Vincent van Leeuwen, )
    Re: Tuning PostgreSQL  (Bruno Wolff III, )
  Re: Tuning PostgreSQL  ("Alexander Priem", )
   Re: Tuning PostgreSQL  (Andrew Sullivan, )
   Re: Tuning PostgreSQL  ("Jim C. Nasby", )
    Re: Tuning PostgreSQL  ("scott.marlowe", )
     Re: Tuning PostgreSQL  (Greg Stark, )
      Re: Tuning PostgreSQL  (Ron Johnson, )
     Re: Tuning PostgreSQL  (Vivek Khera, )
      Re: Tuning PostgreSQL  (Ron Johnson, )
       Re: Tuning PostgreSQL  ("scott.marlowe", )
        Re: Tuning PostgreSQL  (Ron Johnson, )
         Re: Tuning PostgreSQL  ("scott.marlowe", )
          Re: Tuning PostgreSQL  (Ron Johnson, )
           Re: Tuning PostgreSQL  ("scott.marlowe", )
            Re: Tuning PostgreSQL  (Ron Johnson, )
             Re: Tuning PostgreSQL, pt 2  (Ron Johnson, )
          Re: Tuning PostgreSQL  (Vivek Khera, )
      Re: Tuning PostgreSQL  (Will LaShell, )
  Re: Tuning PostgreSQL  ("Alexander Priem", )
   Re: Tuning PostgreSQL  (Ron Johnson, )
  Re: Tuning PostgreSQL  (Vivek Khera, )
 'View'-performance  ("Alexander Priem", )
  Re: 'View'-performance  (Tom Lane, )

Wow, I never figured how many different RAID configurations one could think
of   :)

After reading lots of material, forums and of course, this mailing-list, I
think I am going for a RAID5 configuration of 6 disks (18Gb, 15.000 rpm
each), one of those six disks will be a 'hot spare'. I will just put the OS,
the WAL and the data one one volume. RAID10 is way to expensive   :)

If I understand correctly, this will give great read-performance, but less
write-performance. But since this server will be equipped with an embedded
RAID controller featuring 128Mb of battery-backed cache, I figure that this
controller will negate that (at least somewhat). I will need to find out
whether this cache can be configured so that it will ONLY cache WRITES, not
READS....

Also because of this battery backed cache controller, I will go for the ext2
file system, mounted with 'noatime'. I will use a UPS, so I don't think I
need the journaling of ext3. XFS is not natively supported by RedHat and I
will go for the easy way here   :)

1 Gb of RAM should be enough, I think. That is about the only point that
almost everyone agrees on   :)   Do you think ECC is very important? The
server I have in mind does not support it. Another one does, but is is about
1.000 euros more expensive   :(

One CPU should also be enough.

As for postgresql.conf settings, I think I will start with the following :

max_connections = 128
superuser_reserved_connections = 1
shared_buffers = 8192
max_fsm_relations = 1000
max_fsm_pages = 100000
wal_buffers = 32
sort_mem = 2048
vacuum_mem = 32768
effective_cache_size = 28672 (this one I'm not sure about, maybe this one
needs to be higher)
random_page_cost = 2
geq0_threshold = 20

This pretty much sums it up. What do you think about this config? It may not
be the fastest, but a server like this will cost about 4750 euros, and that
is including an Intel Xeon 2.4GHz cpu, redundant power supply, WITHOUT the
UPS. Seems very reasonable to me...

Kind regards,
Alexander Priem.



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

От: Jord Tanner
Дата:
Сообщение: Re: Dual Xeon + HW RAID question
От: "Castle, Lindsay"
Дата:
Сообщение: One table or many tables for data set