Re: PostgreSQL performance issues

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(at)studentvillage(dot)co(dot)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(at)postgresql(dot)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.