Re: PostgreSQL performance issues

Поиск
Список
Период
Сортировка
От Willo van der Merwe
Тема Re: PostgreSQL performance issues
Дата
Msg-id 44F57E9F.2020604@studentvillage.co.za
обсуждение исходный текст
Ответ на PostgreSQL performance issues  (Willo van der Merwe <willo@studentvillage.co.za>)
Список pgsql-performance
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.

В списке pgsql-performance по дате отправления:

Предыдущее
От: Willo van der Merwe
Дата:
Сообщение: Re: PostgreSQL performance issues
Следующее
От: Dave Cramer
Дата:
Сообщение: Re: PostgreSQL performance issues