От: Alexander Priem
Тема: Tuning PostgreSQL
Дата: ,
Msg-id: 002a01c34f62$75c01de0$b696a8c0@APR
(см: обсуждение, исходный текст)
Ответы: Re: Tuning PostgreSQL  ("Shridhar Daithankar")
Список: 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, )

Hi guys,
 
I am new to PostgreSQL and have done some "extensive" research already. If you could give me some advice/confirmation, I would be really grateful.
 
I am going to build a PostgreSQL database server for a client. This database will contain many tables (over 100, maybe more), with some of them containing over 1 million records pretty soon. This database will be accessed via a desktop application (Windows) and a web application (PHP/Apache). There may be over 100 people accessing the database at any given time (selecting, inserting and updating), but I don't think it will be a lot more than 100 at any given time. Most of the time, it will be less.
 
What I am thinking about is buying a server with the following specifications:
 
* 1 or 2 Intel Xeon processors (2.4 GHz).
* 2 Gigabytes of RAM (DDR/ECC).
* Three 36Gb SCSI160 disks (10.000rpm) in a RAID-5 config, giving 72Gb storage space (right?). The RAID-5 controller has a (hardware) cache of 128Mb.
* 100Mbit ethernet.
 
I will run RedHat Linux 9 (kernel 2.40) with PostgreSQL 7.3.3 on this server.
 
What would you think of this hardware config? Would it do? Of would 4Gb RAM be a lot better? What do you think about the need for two Xeon procs?
 
Finally, I have some questions about postgresql.conf (who doesnt?). After some research, I think I will go for the following settings initially. Maybe benchmarking will lead to other values, but I think these settings will be a fine starting point :
 
shared_buffers = 6000 (kernel.shmmax = 60000000)
sort_mem = 4096
max_connections = 150
vacuum_mem = 65536
 
What do you think of these settings? Do you have any other hints for optimizing PostgreSQL
 
Many many thanks in advance    :)
 

Kind regards,

Alexander Priem
CICT Solutions
Email:
Internet: www.cict.nl

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

От: "Alexander Priem"
Дата:
Сообщение: Re: Tuning PostgreSQL
От: Josh Berkus
Дата:
Сообщение: Re: Tuning PostgreSQL