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

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 ?