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
