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 по дате отправления: