v3.20 / changes to query methods??

General discussion about installation and use of the Archon software. (Link to your site here.)

Moderators: paulnsorensen, kylefox2, prom, rishel, sgriffin

v3.20 / changes to query methods??

Postby david.b.malone » Sat Jun 11, 2011 9:27 am

We just upgraded to v3.20 and I am seeing what I would describe as an issue/concern in how results are returned in the finding aid when Detailed Description is selected.

When this option is selected the page builds quickly up until the series are listed under Browse by Series. It then "hangs" a bit as, I assume, the database is queried for the collection inventory. It does this no matter the size of the collection so it would appear to be the query method rather than the size of the collection.

Here are some time/query stats from the bottom of the page for one our smallest collection and our largest.

Smallest:
http://archon.wheaton.edu/index.php?p=c ... aid&id=134
Page Generated in: 42.578 seconds (using 317 queries).
Using 7.37MB of memory. (Peak of 7.53MB.)

Largest:
http://archon.wheaton.edu/index.php?p=c ... ngaid&id=5
Page Generated in: 215.819 seconds (using 447 queries).
Using 7.27MB of memory. (Peak of 12.23MB.)

Nearly a minute for a small collection and over three minutes for a larger collection seems quite a bit of time to search, especially compared to v3.12.

Any thoughts??

David Malone
Wheaton College (IL)
Archon v3.20
SUSE Linux Enterprise Server 10 (last I knew)
David B. Malone
Wheaton College Archives & Special Collections
david.b.malone
 
Posts: 186
Joined: Mon Apr 21, 2008 1:44 pm
Location: Wheaton, IL

Re: v3.20 / changes to query methods??

Postby david.b.malone » Mon Jun 20, 2011 2:44 pm

david.b.malone wrote:We just upgraded to v3.20 and I am seeing what I would describe as an issue/concern in how results are returned in the finding aid when Detailed Description is selected.

When this option is selected the page builds quickly up until the series are listed under Browse by Series. It then "hangs" a bit as, I assume, the database is queried for the collection inventory. It does this no matter the size of the collection so it would appear to be the query method rather than the size of the collection.

Here are some time/query stats from the bottom of the page for one our smallest collection and our largest.

Smallest:
http://archon.wheaton.edu/index.php?p=c ... aid&id=134
Page Generated in: 42.578 seconds (using 317 queries).
Using 7.37MB of memory. (Peak of 7.53MB.)

Largest:
http://archon.wheaton.edu/index.php?p=c ... ngaid&id=5
Page Generated in: 215.819 seconds (using 447 queries).
Using 7.27MB of memory. (Peak of 12.23MB.)

Nearly a minute for a small collection and over three minutes for a larger collection seems quite a bit of time to search, especially compared to v3.12.

Any thoughts??

David Malone
Wheaton College (IL)
Archon v3.20
SUSE Linux Enterprise Server 10 (last I knew)


Anyone?? I have found a change in performance after our installation. It would seem that we can only do one "call" to the database at a time. If someone is uploading a picture for the digital library no one can do anything else until that event/call/activity is over, including using the public search interface. Any thoughts??
David B. Malone
Wheaton College Archives & Special Collections
david.b.malone
 
Posts: 186
Joined: Mon Apr 21, 2008 1:44 pm
Location: Wheaton, IL

Re: v3.20 / changes to query methods??

Postby paulnsorensen » Mon Jun 20, 2011 2:47 pm

Did you experience this change in performance after enabling content level creators and subjects? Also do you have a query count comparison between the two versions by any chance?
paulnsorensen
Site Admin
 
Posts: 631
Joined: Mon Mar 31, 2008 8:55 am

Re: v3.20 / changes to query methods??

Postby david.b.malone » Mon Jun 20, 2011 2:54 pm

paulnsorensen wrote:Did you experience this change in performance after enabling content level creators and subjects? Also do you have a query count comparison between the two versions by any chance?


One of the first things I did after the upgrade was enable content level creators and subjects. I did not anticipate any issues so I don't have a before and after in v3.20 on this change. I don't know about a query count comparison. Is this something tracked internal to Archon or something we would have had to track ad hoc? I have a sense that the number of queries (as illustrated in the information provided above) has gone up in v3.20.

David
David B. Malone
Wheaton College Archives & Special Collections
david.b.malone
 
Posts: 186
Joined: Mon Apr 21, 2008 1:44 pm
Location: Wheaton, IL

Re: v3.20 / changes to query methods??

Postby paulnsorensen » Mon Jun 20, 2011 3:16 pm

We didn't really implement anything that could have caused it. Enabling the content level creators and subjects would add 2 extra queries for each of the root content subtrees. Either way, I have implemented a way to cache your finding aid pages in the database. Pending some further testing, this should make it out in the next release. That way it won't take as long to build them.
paulnsorensen
Site Admin
 
Posts: 631
Joined: Mon Mar 31, 2008 8:55 am

Re: v3.20 / changes to query methods??

Postby paulnsorensen » Mon Jun 20, 2011 3:17 pm

You could also try enabling and disabling the content level creators and subjects and see what difference it makes in your finding aid generation times.
paulnsorensen
Site Admin
 
Posts: 631
Joined: Mon Mar 31, 2008 8:55 am

Re: v3.20 / changes to query methods??

Postby david.b.malone » Mon Jun 20, 2011 3:18 pm

paulnsorensen wrote:You could also try enabling and disabling the content level creators and subjects and see what difference it makes in your finding aid generation times.


Will this have a negative impact upon subjects and creators that have been attached within the content levels??

David
David B. Malone
Wheaton College Archives & Special Collections
david.b.malone
 
Posts: 186
Joined: Mon Apr 21, 2008 1:44 pm
Location: Wheaton, IL

Re: v3.20 / changes to query methods??

Postby paulnsorensen » Mon Jun 20, 2011 3:31 pm

No, it won't affect the database.
paulnsorensen
Site Admin
 
Posts: 631
Joined: Mon Mar 31, 2008 8:55 am

Re: v3.20 / changes to query methods??

Postby paulnsorensen » Mon Jun 20, 2011 3:47 pm

Another thing you may want to do, if this is what's making everything slow, is double check that there are indexes on the tblCollections_ColllectionContentSubjectIndex and tblCollections_CollectionContentCreatorIndex tables on each of the SubjectID/CreatorID fields and the CollectionContentID fields.
paulnsorensen
Site Admin
 
Posts: 631
Joined: Mon Mar 31, 2008 8:55 am

Re: v3.20 / changes to query methods??

Postby david.b.malone » Mon Jun 20, 2011 4:26 pm

paulnsorensen wrote:You could also try enabling and disabling the content level creators and subjects and see what difference it makes in your finding aid generation times.


I made the change and things seemed to have picked up in the general pages, but here is the info related to retrieving the finding aid listed as the "longest" in my original post above.

Page Generated in: 874.966 seconds (using 458 queries).
Using 8.94MB of memory. (Peak of 13.59MB.)

It would appear that the number of queries has not changed significantly, but the amount of memory is greater and the length of time to generate the page nearly doubled.

David
David B. Malone
Wheaton College Archives & Special Collections
david.b.malone
 
Posts: 186
Joined: Mon Apr 21, 2008 1:44 pm
Location: Wheaton, IL

Re: v3.20 / changes to query methods??

Postby david.b.malone » Mon Jun 20, 2011 4:27 pm

paulnsorensen wrote:Another thing you may want to do, if this is what's making everything slow, is double check that there are indexes on the tblCollections_ColllectionContentSubjectIndex and tblCollections_CollectionContentCreatorIndex tables on each of the SubjectID/CreatorID fields and the CollectionContentID fields.


I'm inclined to say: "you're speakin' gibberish...."

But I'll just go with:

"How would I check this?" as this would be something I would pass along to our library systems staff.

David
David B. Malone
Wheaton College Archives & Special Collections
david.b.malone
 
Posts: 186
Joined: Mon Apr 21, 2008 1:44 pm
Location: Wheaton, IL

Re: v3.20 / changes to query methods??

Postby paulnsorensen » Mon Jun 20, 2011 5:09 pm

You should try to coordinate with your systems staff and see if they can return to you the tail of their /var/log/mysql/mysql-slow.log immediately after you load one of these finding aid pages. Something just doesn't seem right about this situation. One thing I might also suggest to them is to install a fresh copy of archon to a new database, and go table-by-table to see if your current implementation is missing indexes that are automatically installed by archon.

Was the database you upgraded to 3.20 also running your 3.14 version? Or was there some sort of database migration involved?
paulnsorensen
Site Admin
 
Posts: 631
Joined: Mon Mar 31, 2008 8:55 am

Re: v3.20 / changes to query methods??

Postby david.b.malone » Thu Jun 23, 2011 8:32 am

paulnsorensen wrote:You should try to coordinate with your systems staff and see if they can return to you the tail of their /var/log/mysql/mysql-slow.log immediately after you load one of these finding aid pages. Something just doesn't seem right about this situation. One thing I might also suggest to them is to install a fresh copy of archon to a new database, and go table-by-table to see if your current implementation is missing indexes that are automatically installed by archon.

Was the database you upgraded to 3.20 also running your 3.14 version? Or was there some sort of database migration involved?


Hello all,

Let me start by saying any help that can be offered to help us resolve the problem we are experiencing (certainly a local issue) would be most appreciated. Our system response is spiraling downward. It is taking .5 to 1 hour to load an entire finding aid page (?p=collections/findingaid). Everything else is "generally" running at a decent speed (though 19-22 seconds to build the front page is a bit long I think). The biggest speed hit comes when someone is loading anything into the system (200K file). When not uploading one of our largest finding aids takes 30 minutes, when uploading 74 minutes. Here is a link to one of our smallest finding aids. If you select the link you'll see the load stall once the series list loads. This stall happens on all findings aids, big or small. Can someone describe what query occurs here and to what table?

http://archon.wheaton.edu/?p=collections/findingaid&id=157&q=

When I talked with our systems staffer working on this he said that he does not have a "mysql-slow.log." Is this a common log with a fairly common path? He said he would have to create the log.

Because I am not a technical expert (though I understand conceptually) I can't seem to figure out the issues. My understanding from our staffer is that he went from our 3.12 install and upgraded to 3.14 and then to 3.20. He said he experienced difficulties with the 3.20 install. I wonder if something is out of whack.

Thanks in advance,

David Malone
Wheaton College (IL)
David B. Malone
Wheaton College Archives & Special Collections
david.b.malone
 
Posts: 186
Joined: Mon Apr 21, 2008 1:44 pm
Location: Wheaton, IL

Re: v3.20 / changes to query methods??

Postby paulnsorensen » Thu Jun 23, 2011 2:06 pm

David,

Here's a link to pass to your IT folks to show them how to enable logging of slow queries.

Unfortunately, I'm going to need a lot more information to be able to troubleshoot your problem. What sort of problems did you have when upgrading to 3.20? The best path might be to just re-upgrade. It's imperative that your systems folks make sure you have the proper database indexes. If you can pass me back the results of the following query, I should be able to see what indexes you are missing.
Code: Select all
SELECT DISTINCT
    TABLE_NAME,
    INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'archon_db';
paulnsorensen
Site Admin
 
Posts: 631
Joined: Mon Mar 31, 2008 8:55 am

Re: v3.20 / changes to query methods??

Postby david.b.malone » Thu Jun 23, 2011 2:16 pm

paulnsorensen wrote:David,
Unfortunately, I'm going to need a lot more information to be able to troubleshoot your problem. What sort of problems did you have when upgrading to 3.20? The best path might be to just re-upgrade. It's imperative that your systems folks make sure you have the proper database indexes. If you can pass me back the results of the following query, I should be able to see what indexes you are missing.


Thanks for engaging in the conversation. I will pass these along and see what I can come up with. Unfortunately, all that I can speak to is the admin module of the software and the public interface/experience.

I don't know if you have tried to retrieve a finding aid page to see what the user is experiencing. I don't know if this gives any insight into the point at which the PHP encounters a hiccup.

http://archon.wheaton.edu/index.php?p=collections/findingaid&id=5

David
David B. Malone
Wheaton College Archives & Special Collections
david.b.malone
 
Posts: 186
Joined: Mon Apr 21, 2008 1:44 pm
Location: Wheaton, IL

Next

Return to Archon Discussion

Who is online

Users browsing this forum: No registered users and 1 guest