Hi Graham,
Thanks for all your help. We have adjusted our postgres config to autovacuum,
as well as log slow queries, and after running a full reindex we haven't had
the same issue happen again. We've also activated the cron jobs to keep things
current. Looks like it just needed a bit of maintenance.
We'll look into upgrading postgres as well. Is there a recommended 9.x version?
regards,
Steve
On 13/11/2010, at 8:42 AM, Graham Triggs wrote:
> Steve,
>
> I've been trying again on my machine. Postgres 8.4 seemed to be quite slow /
> expensive (can't recall the numbers precisely - it was the other day). I have
> dumped the data and loaded it into Postgres 9.0, and the results are
> interesting.
>
> Just after restoring the database, the same query (for the first 10 records)
> took about 6 seconds to execute (with a lot of CPU usage). After vacuuming
> bi_4_dmap and bi_item (pgadmin recommended both be vacuumed), the query took
> 3 seconds.
>
> This is with 379,900 records in the item / bi_item tables, and 4,775,227
> records in bi_4_dmap.
>
> I would recommend you vacuum and analyze the tables. And possibly consider
> upgrading the version of Postgres.
>
> G
>
> On 11 November 2010 01:30, Steve Swinsburg <[email protected]> wrote:
> Hi Graham,
>
> We have ~48000 records in the item table, and ~263,000 in the bi_4_dmap
> table. We've recreated the indexes (only via index-update though, should we
> run index-init?) and things went ok for a while, but now back up to 99% and
> the same query appears in pg_stat_activity.
>
> We are running Postgres 8.1.18.
>
> cheers,
> Steve
>
>
>
>
>
> On 11/11/2010, at 11:10 AM, Graham Triggs wrote:
>
>> It's a second level browse - ie. if your 4th browse index is 'subject', then
>> it's someone looking at all the items that have a particular subject entry.
>>
>> This shouldn't be that expensive a query (given there is no offset
>> involved), but you may have an issue with an index missing (although the
>> browse code should create all the ones that it needs when it creates the
>> table), or more likely you have too low a value for your shared_buffers
>> (required to load the indexes), or work_mem (used for the join between the
>> tables). Additionally, you may need to analyze and/or reindex the tables.
>>
>> How many items are in the repository, and how many values do you have in the
>> 4th browse option (subject?). And what version of Postgres are you running?
>>
>> G
>>
>> On 10 November 2010 22:42, Steve Swinsburg <[email protected]> wrote:
>> A followup:
>>
>> I restarted postgres and within minutes the same query has appeared in the
>> stats and CPU is back up to 99%. Could we be missing some indexes or
>> something? We only recently ran the filter-media script and generated
>> thousands of thumbnails and branded previews but have since performed a
>> vacuum.
>>
>> Any information would be much appreciated.
>>
>> cheers,
>> Steve
>>
>> On 11/11/2010, at 9:12 AM, Steve Swinsburg wrote:
>>
>>> Hi all,
>>>
>>> We are experiencing an issue on both of our dspace instances where
>>> postmaster spins up 99% of the CPU. Sometimes it's just one process at 99%,
>>> othertimes its a dozen or more processes around 7-9% each. I ran some stats
>>> on postgres via:
>>>
>>> select * from pg_stat_activity
>>>
>>> I found this query in the output about 15 times:
>>>
>>> SELECT bi_item.* FROM bi_item, (SELECT bi_4_dmap.item_id FROM bi_4_dmap,
>>> bi_4_dis WHERE bi_4_dmap.distinct_id=bi_4_dis.id AND bi_4_dis.sort_value=$1
>>> ) mappings WHERE bi_item.item_id=mappings.item_id ORDER BY sort_3 ASC
>>> LIMIT $2
>>>
>>> The earliest has a start time of about 5 hours ago.
>>>
>>> Anyone know whats up?
>>>
>>> cheers,
>>> Steve
>>>
>>
>>
>> ------------------------------------------------------------------------------
>> Centralized Desktop Delivery: Dell and VMware Reference Architecture
>> Simplifying enterprise desktop deployment and management using
>> Dell EqualLogic storage and VMware View: A highly scalable, end-to-end
>> client virtualization framework. Read more!
>> http://p.sf.net/sfu/dell-eql-dev2dev
>> _______________________________________________
>> DSpace-tech mailing list
>> [email protected]
>> https://lists.sourceforge.net/lists/listinfo/dspace-tech
>>
>>
>
>
------------------------------------------------------------------------------
Centralized Desktop Delivery: Dell and VMware Reference Architecture
Simplifying enterprise desktop deployment and management using
Dell EqualLogic storage and VMware View: A highly scalable, end-to-end
client virtualization framework. Read more!
http://p.sf.net/sfu/dell-eql-dev2dev
_______________________________________________
DSpace-tech mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/dspace-tech
