From: | "philip johnson" <philip(dot)johnson(at)atempo(dot)com> |
---|---|
To: | "Josh Berkus" <josh(at)agliodbs(dot)com>, <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: how to configure my new server |
Date: | 2003-02-06 18:13:17 |
Message-ID: | NDBBJLHHAKJFNNCGFBHLAENEEKAA.philip.johnson@atempo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
pgsql-performance-owner(at)postgresql(dot)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 ?
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Pflug | 2003-02-06 21:43:23 | Re: how to configure my new server |
Previous Message | Josh Berkus | 2003-02-06 17:04:11 | Re: how to configure my new server |