Re: how to configure my new server - Mailing list pgsql-performance

pgsql-performance-owner@postgresql.org wrote:
> Objet : Re: [PERFORM] how to configure my new server
>
>
> pgsql-performance-owner@postgresql.org wrote:
>> Phillip,
>>
>> First, a disclaimer:  my advice is without warranty whatsoever.  You
>> want a warranty, you gotta pay me.
>>
>>> I've a new configuration for our web server
>>>
>>> Processor    Processeur Intel Xeon 2.0 Ghz / 512 Ko de cache L2
>>> Memoiry    1 Go DDR SDRAM Disk1    18Go Ultra 3 (Ultra 160) SCSI 15 Ktpm
>>> Disk2    18Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm
>>> Disk3    18Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm
>>> Disk4    18Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm
>>> Disk5    36Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm
>>
>> No RAID, though?
>
> Yes no Raid, but will could change soon
>
>>
>> Think carefully about which disks you put things on.   Ideally, the
>> OS, the web files, the database files, the database log, and the swap
>> partition will all be on seperate disks.   With a large database you
>> may even think about shifting individual tables or indexes to
>> seperate disks.
>
> how can I put indexes on a seperate disk ?
>
>>
>>> linux values:
>>> kernel.shmmni = 4096
>>> kernel.shmall = 32000000
>>> kernel.shmmax = 256000000
>>
>> These are probably too high, but I'm ready to speak authoritatively
>> on that.
> I took a look a the performance archive, and it's not possible to find
> real info on how to set these 3 values.
>
>>
>>> postgresql values:
>>> shared_buffers
>>> max_fsm_relations
>>> max_fsm_pages
>>> wal_buffers
>>> wal_files
>>> sort_mem
>>> vacuum_mem
>>
>> Please visit the archives for this list.   Setting those values is a
>> topic of discussion for 50% of the threads, and there is yet no firm
>> agreement on good vs. bad values.
>>
>
> I'm surprised that there's no spreadsheet to calculate those values.
> There are many threads, but it seems that no one is able to find a
> rule to define values.
>
>
>> Also, you need to ask youself more questions before you start
>> setting values:
>>
>> 1. How many queries does my database handle per second or minute?
>> can't say now
>
>> 2. How big/complex are those queries?
> Not really complex and big as you can see
>
> SELECT qu_request.request_id, qu_request.type,
> qu_request_doc.ki_status, qu_request_doc.ki_subject,
> qu_request_doc.ki_description, qu_request_doc.ki_category,
> qu_request_doc.rn_description_us, qu_request_doc.rn_status_us,
> quad_config_nati.nati_version_extended FROM qu_request left join
> quad_config_nati on qu_request.quad_server_nati =
> quad_config_nati.nati_version left join qu_request_doc on
> qu_request.request_id = qu_request_doc.request_id
> WHERE qu_request.request_id = '130239'
>
>
> select  sv_inquiry.inquiry_id,  sv_inquiry.quad_account_inquiry_id
> ,to_char(sv_inquiry.change_dt, 'YYYY-MM-DD HH24:MI') as change_dt ,
> to_char(sv_inquiry.closed_dt, 'YYYY-MM-DD HH24:MI') as closed_dt
> ,sv_inquiry.state, sv_inquiry.priority, sv_inquiry.type,
> account_contact.dear as contact , account_contact2.dear as contact2,
> sv_inquiry.action, sv_inquiry.activity ,
> substr(sv_inq_txt.inquiry_txt, 1, 120) as inquiry_txt from sv_inquiry
> left join sv_inq_txt on sv_inquiry.inquiry_id = sv_inq_txt.inquiry_id
> left join account_contact on sv_inquiry.account_contact_id =
> account_contact.account_contact_id left join account_contact
> account_contact2
> on sv_inquiry.account_contact_id2 =
> account_contact2.account_contact_id where
> sv_inquiry.account_id=3441833 and sv_inquiry.state not in ('Closed',
> 'Classified') ORDER BY sv_inquiry.inquiry_id DESC
>
>
>> 3. What is the ratio of database read activity vs. database writing
>> activity?
> There are more insert/update than read, because I'm doing table
> synchronization
> from an SQL Server database. Every 5 minutes I'm looking for change
> in SQL Server
> Database.
> I've made some stats, and I found that without user acces, and only
> with the replications
> I get 2 millions query per day
>
>> 4. What large tables in my database get queried
>> simultaneously/together? why this questions ?
>
>> 5. Are my database writes bundled into transactions, or seperate?
>> bundle in transactions
>
>> etc.
>>
>> Simply knowing the size of the database files isn't enough.
>
> is it better like this ?
>
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 5: Have you checked our
> extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

someone could come back to first request ?