From: M. D. <lists@turnkey.bz>
To: Craig James <cjames@emolecules.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: hardware advice
Date: Thu, 27 Sep 2012 14:46:22 -0600
Message-ID: <5064BB1E.4090904@turnkey.bz> (raw)
In-Reply-To: <CAFwQ8rf4Cnt9Ksd+3GbkkWtZ3NM+hYukd1gKRLmsihXnKzVteA@mail.gmail.com>
References: <5064A4DC.5090304@turnkey.bz>
<CAFwQ8rf4Cnt9Ksd+3GbkkWtZ3NM+hYukd1gKRLmsihXnKzVteA@mail.gmail.com>
On 09/27/2012 01:37 PM, Craig James wrote:
> I don't think you've supplied enough information for anyone to give
> you a meaningful answer. What's your current configuration? Are you
> I/O bound, CPU bound, memory limited, or some other problem? You need
> to do a specific analysis of the queries that are causing you problems
> (i.e. why do you need to upgrade at all?)
My current configuration is a Dell PE 1900, E5335, 16GB Ram, 2 250GB Raid 0.
I'm buying a new server mostly because the current one is a bit slow and
I need a new gateway server, so to get faster database responses, I want
to upgrade this and use the old one for gateway.
The current system is limited to 16GB Ram, so it is basically maxed out.
A query that takes 89 seconds right now is run on a regular basis
(82,000 rows):
select item.item_id,item_plu.number,item.description,
(select number from account where asset_acct = account_id),
(select number from account where expense_acct = account_id),
(select number from account where income_acct = account_id),
(select dept.name from dept where dept.dept_id = item.dept_id) as dept,
(select subdept.name from subdept where subdept.subdept_id =
item.subdept_id) as subdept,
(select sum(on_hand) from item_change where item_change.item_id =
item.item_id) as on_hand,
(select sum(on_order) from item_change where item_change.item_id =
item.item_id) as on_order,
(select sum(total_cost) from item_change where item_change.item_id =
item.item_id) as total_cost
from item join item_plu on item.item_id = item_plu.item_id and
item_plu.seq_num = 0
where item.inactive_on is null and exists (select item_num.number from
item_num
where item_num.item_id = item.item_id)
and exists (select stocked from item_store where stocked = 'Y'
and inactive_on is null
and item_store.item_id = item.item_id)
Explain analyse: http://explain.depesz.com/s/sGq
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@turnkey.bz, cjames@emolecules.com
Subject: Re: hardware advice
In-Reply-To: <5064BB1E.4090904@turnkey.bz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox