From: Willo van der Merwe <willo@studentvillage.co.za>
To: Alex Hayward <xelah-pgsql@xelah.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL performance issues
Date: Wed, 30 Aug 2006 14:03:43 +0200
Message-ID: <44F57E9F.2020604@studentvillage.co.za> (raw)
In-Reply-To: <000801c6cc2b$fcb9e8a0$0a00a8c0@svo.co.za>
References: <44F446B2.2090900@studentvillage.co.za>
<b42b73150608291639o226b645cq41175b416590f029@mail.gmail.com>
<44F56649.9050604@studentvillage.co.za>
<000801c6cc2b$fcb9e8a0$0a00a8c0@svo.co.za>
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.
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: willo@studentvillage.co.za, xelah-pgsql@xelah.com
Subject: Re: PostgreSQL performance issues
In-Reply-To: <44F57E9F.2020604@studentvillage.co.za>
* 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