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 <steve.swinsb...@gmail.com>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 <steve.swinsb...@gmail.com>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
>> DSpace-tech@lists.sourceforge.net
>> 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
DSpace-tech@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/dspace-tech
