Message-ID: <44F57E9F.2020604@studentvillage.co.za>
Date: Wed, 30 Aug 2006 14:03:43 +0200
From: Willo van der Merwe <willo@studentvillage.co.za>
User-Agent: Thunderbird 1.5.0.5 (Windows/20060719)
MIME-Version: 1.0
To: Alex Hayward <xelah-pgsql@xelah.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL performance issues
References: <44F446B2.2090900@studentvillage.co.za>
	<b42b73150608291639o226b645cq41175b416590f029@mail.gmail.com>
	<44F56649.9050604@studentvillage.co.za>
	<000801c6cc2b$fcb9e8a0$0a00a8c0@svo.co.za>
In-Reply-To: <000801c6cc2b$fcb9e8a0$0a00a8c0@svo.co.za>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Alex Hayward wrote:
> On Wed, 30 Aug 2006, Willo van der Merwe wrote:
>
>   
>> Merlin Moncure wrote:
>>     
>>> On 8/29/06, Willo van der Merwe <willo@studentvillage.co.za> wrote:
>>>
>>>       
>>>>  and it has 743321 rows and a explain analyze select count(*) from
>>>> property_values;
>>>>
>>>>         
>>> you have a number of options:
>>>       
>> All good ideas and I'll be sure to implement them later.
>>
>>     
>>> I am curious why you need to query the count of records in the log
>>> table to six digits of precision.
>>>       
>> I'm not with you you here.
>> I'm drawing statistic for the my users on a per user basis in real-time,
>> so there are a couple of where clauses attached.
>>     
>
> Most of the advice so far has been aimed at improving the performance of
> the query you gave. If this query isn't representative of your load then
> you'll get better advice if you post the queries you are actually making
> along with EXPLAIN ANALYZE output.
>
>   
>> Hi Merlin,
>>
>> This was just an example. All queries have slowed down. Could it be that
>> I've reached some cut-off and now my disk is thrashing?
>>
>> Currently the load looks like this:
>> Cpu0  : 96.8% us,  1.9% sy,  0.0% ni,  0.3% id,  0.0% wa,  0.0% hi,  1.0% si
>> Cpu1  : 97.8% us,  1.6% sy,  0.0% ni,  0.3% id,  0.0% wa,  0.0% hi,  0.3% si
>> Cpu2  : 96.8% us,  2.6% sy,  0.0% ni,  0.3% id,  0.0% wa,  0.0% hi,  0.3% si
>> Cpu3  : 96.2% us,  3.2% sy,  0.0% ni,  0.3% id,  0.0% wa,  0.0% hi,  0.3% si
>>     
>
> It seems to be a sort of standing assumption on this list that databases
> are much larger than memory and that database servers are almost always IO
> bound. This isn't always true, but as we don't know the size of your
> database or working set we can't tell. You'd have to look at your OS's IO
> statistics to be sure, but it doesn't look to me to be likely that you're
> IO bound.
>
> If there are significant writes going on then it may also be interesting
> to know your context switch rate and whether dropping your foreign key
> constraint makes any difference. IIRC your foreign key constraint will
> result in the row in log_sites being locked FOR UPDATE and cause updates
> and inserts into your log table for a particular site to be serialized (I
> may be out of date on this, it's a while since I heavily used foreign
> keys).
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>   

Hi Alex,

Yes, I haven't noticed any major I/O waits either. The crazy thing here 
is that all the queries were running an an acceptable time limit, but 
then suddenly it went haywire. I did not change any of the queries or 
fiddle with the server in any way. Previously we've experienced 1 or 2 
spikes a day (where load would suddenly spike to 67 or so, but then 
quickly drop down to below 4) but in this case it stayed up. So I 
restarted the service and started fiddling with options, with no 
apparent effect.