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

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

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

I have been actively clicking around in your site, but the amount of time it takes to create a page doesn't tell me why it is taking so long. In fact now, I'm getting an error connecting to database.
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:34 pm

paulnsorensen wrote:David,
Code: Select all
SELECT DISTINCT
    TABLE_NAME,
    INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'archon_db';


To speed things up, is there a way to put this into PHP? I know this is not likely right, but something like:

Code: Select all
<?php
    $result = $_ARCHON->mdb2->query("SELECT DISTINCT TABLE_NAME, INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'archon_db';"); ?>


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 » Thu Jun 23, 2011 2:55 pm

Code: Select all
<?php
$result = $_ARCHON->mdb2->query("SELECT DISTINCT TABLE_NAME, INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'archon_merge'");
$indexes = array();
while($row = $result->fetchRow()){
  $indexes[$row['TABLE_NAME']][] = $row['INDEX_NAME'];
}

var_dump($indexes);
?>
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 3:54 pm

paulnsorensen wrote: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';


That is what we get from the MYSQL:
+_____+
SELECT DISTINCT
TABLE_NAME,
INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'archon_db';
+--------------------------------+

mysql> SELECT DISTINCT TABLE_NAME, INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'archon1';
+--------------------------------------------------+---------------------+
| TABLE_NAME | INDEX_NAME |
+--------------------------------------------------+---------------------+
| tblAVSAP_AVSAPAssessments | PRIMARY |
| tblAVSAP_AVSAPAudioCassetteAssessments | PRIMARY |
| tblAVSAP_AVSAPAudioCassetteAssessments | AssessmentID |
| tblAVSAP_AVSAPFilmAssessments | PRIMARY |
| tblAVSAP_AVSAPFilmAssessments | AssessmentID |
| tblAVSAP_AVSAPGroovedCylinderAssessments | PRIMARY |
| tblAVSAP_AVSAPGroovedCylinderAssessments | AssessmentID |
| tblAVSAP_AVSAPGroovedDiscAssessments | PRIMARY |
| tblAVSAP_AVSAPGroovedDiscAssessments | AssessmentID |
| tblAVSAP_AVSAPInstitutions | PRIMARY |
| tblAVSAP_AVSAPOpenReelAudioAssessments | PRIMARY |
| tblAVSAP_AVSAPOpenReelAudioAssessments | AssessmentID |
| tblAVSAP_AVSAPOpenReelVideoAssessments | PRIMARY |
| tblAVSAP_AVSAPOpenReelVideoAssessments | AssessmentID |
| tblAVSAP_AVSAPOpticalMediaAssessments | PRIMARY |
| tblAVSAP_AVSAPOpticalMediaAssessments | AssessmentID |
| tblAVSAP_AVSAPStorageFacilities | PRIMARY |
| tblAVSAP_AVSAPVideoCassetteAssessments | PRIMARY |
| tblAVSAP_AVSAPVideoCassetteAssessments | AssessmentID |
| tblAVSAP_AVSAPWireAudioAssessments | PRIMARY |
| tblAVSAP_AVSAPWireAudioAssessments | AssessmentID |
| tblAccessions_AccessionCollectionIndex | PRIMARY |
| tblAccessions_AccessionCreatorIndex | PRIMARY |
| tblAccessions_AccessionLocationIndex | PRIMARY |
| tblAccessions_AccessionSubjectIndex | PRIMARY |
| tblAccessions_Accessions | PRIMARY |
| tblAccessions_ProcessingPriorities | PRIMARY |
| tblCollections_BookCreatorIndex | PRIMARY |
| tblCollections_BookLanguageIndex | PRIMARY |
| tblCollections_BookSubjectIndex | PRIMARY |
| tblCollections_Books | PRIMARY |
| tblCollections_Classifications | PRIMARY |
| tblCollections_CollectionBookIndex | PRIMARY |
| tblCollections_CollectionContentCreatorIndex | PRIMARY |
| tblCollections_CollectionContentCreatorIndex | CollectionContentID |
| tblCollections_CollectionContentCreatorIndex | CreatorID |
| tblCollections_CollectionContentSubjectIndex | PRIMARY |
| tblCollections_CollectionContentSubjectIndex | CollectionContentID |
| tblCollections_CollectionContentSubjectIndex | SubjectID |
| tblCollections_CollectionCreatorIndex | PRIMARY |
| tblCollections_CollectionLanguageIndex | PRIMARY |
| tblCollections_CollectionLocationIndex | PRIMARY |
| tblCollections_CollectionSubjectIndex | PRIMARY |
| tblCollections_CollectionSubjectIndex | CollectionID |
| tblCollections_CollectionSubjectIndex | SubjectID |
| tblCollections_Collections | PRIMARY |
| tblCollections_Content | PRIMARY |
| tblCollections_Content | CollectionID |
| tblCollections_Content | ParentID |
| tblCollections_Content | LevelContainerID |
| tblCollections_ExtentUnits | PRIMARY |
| tblCollections_LevelContainers | PRIMARY |
| tblCollections_LocationRepositoryIndex | PRIMARY |
| tblCollections_Locations | PRIMARY |
| tblCollections_MaterialTypes | PRIMARY |
| tblCollections_ResearchAppointmentMaterialsIndex | PRIMARY |
| tblCollections_ResearchAppointmentPurposes | PRIMARY |
| tblCollections_ResearchAppointments | PRIMARY |
| tblCollections_ResearchCarts | PRIMARY |
| tblCollections_ResearcherTypes | PRIMARY |
| tblCollections_UserFields | PRIMARY |
| tblCore_Configuration | PRIMARY |
| tblCore_ModificationLog | PRIMARY |
| tblCore_Modules | PRIMARY |
| tblCore_Packages | PRIMARY |
| tblCore_PatternUnitTestIndex | PRIMARY |
| tblCore_PatternUnitTestIndex | PatternID |
| tblCore_Patterns | PRIMARY |
| tblCore_Phrases | PRIMARY |
| tblCore_Phrases | PhraseName |
| tblCore_Phrases | ModuleID |
| tblCore_Phrases | PackageID |
| tblCore_Phrases | PhraseTypeID |
| tblCore_Repositories | PRIMARY |
| tblCore_Sessions | PRIMARY |
| tblCore_StateProvinces | PRIMARY |
| tblCore_UserPermissions | PRIMARY |
| tblCore_UserProfileFieldCategories | PRIMARY |
| tblCore_UserProfileFieldCountryIndex | PRIMARY |
| tblCore_UserProfileFields | PRIMARY |
| tblCore_UserRepositoryIndex | PRIMARY |
| tblCore_UserUserProfileFieldIndex | PRIMARY |
| tblCore_UserUsergroupIndex | PRIMARY |
| tblCore_UsergroupPermissions | PRIMARY |
| tblCore_Usergroups | PRIMARY |
| tblCore_Users | PRIMARY |
| tblCreators_CreatorCreatorIndex | PRIMARY |
| tblCreators_CreatorCreatorIndex | CreatorID |
| tblCreators_CreatorCreatorIndex | RelatedCreatorID |
| tblCreators_CreatorSources | PRIMARY |
| tblCreators_Creators | PRIMARY |
| tblDigitalLibrary_DigitalContent | PRIMARY |
| tblDigitalLibrary_DigitalContentCreatorIndex | PRIMARY |
| tblDigitalLibrary_DigitalContentLanguageIndex | PRIMARY |
| tblDigitalLibrary_DigitalContentSubjectIndex | PRIMARY |
| tblDigitalLibrary_FileTypes | PRIMARY |
| tblDigitalLibrary_Files | PRIMARY |
| tblDigitalLibrary_Files | DigitalContentID |
| tblDigitalLibrary_MediaTypes | PRIMARY |
| tblSubjects_SubjectSources | PRIMARY |
| tblSubjects_Subjects | PRIMARY |
| tblSubjects_Subjects | ParentID |
+--------------------------------------------------+---------------------+
102 rows in set (0.36 sec) mysql>
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 4:17 pm

Well it doesn't seem like you're missing anything major, but I have some indexes you're missing (and I will also double check that these are in the install scripts):

tblCollections_Classifications ParentID
tblCollections_Collections ClassificationID
tblCollections_Collections SortTitle
tblCore_Modules PackageID
tblCore_UserPermissions UserID

These probably won't speed things up significantly. Have you recently imported a lot of collections? How many records do you have in your collections and content tables? Do we know what problems you encountered while upgrading, yet?
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 4:23 pm

paulnsorensen wrote: Have you recently imported a lot of collections? How many records do you have in your collections and content tables? Do we know what problems you encountered while upgrading, yet?


We have not added descriptive data, but have been adding content for the digital library (several hundred photos, each about 200-300K). I have passed on your questions to our systems staff to obtain specifics. They have added 2GB of memory and another CPU to the server and this has helped the most severe delays (e.g. 874 seconds), but this has brought us back to similar lags experienced after the upgrade. We still have pages that stop loading at the same spot on all finding aid pages no matter the size as it appears that the scripts are doing a specific type of query.

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 » Thu Jun 23, 2011 4:32 pm

paulnsorensen wrote:
Code: Select all
<?php
$result = $_ARCHON->mdb2->query("SELECT DISTINCT TABLE_NAME, INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'archon_merge'");
$indexes = array();
while($row = $result->fetchRow()){
  $indexes[$row['TABLE_NAME']][] = $row['INDEX_NAME'];
}

var_dump($indexes);
?>


Here's what I get by running this within a page. I cleaned it up a bit due to the arrays, etc.

tblAVSAP_AVSAPAssessments | PRIMARY
tblAVSAP_AVSAPAudioCassetteAssessments | PRIMARY | AssessmentID
tblAVSAP_AVSAPFilmAssessments | PRIMARY | AssessmentID
tblAVSAP_AVSAPGroovedCylinderAssessments | PRIMARY | AssessmentID
tblAVSAP_AVSAPGroovedDiscAssessments | PRIMARY | AssessmentID
tblAVSAP_AVSAPInstitutions | PRIMARY
tblAVSAP_AVSAPOpenReelAudioAssessments | PRIMARY | AssessmentID
tblAVSAP_AVSAPOpenReelVideoAssessments | PRIMARY | AssessmentID
tblAVSAP_AVSAPOpticalMediaAssessments | PRIMARY | AssessmentID
tblAVSAP_AVSAPStorageFacilities | PRIMARY
tblAVSAP_AVSAPVideoCassetteAssessments | PRIMARY | AssessmentID
tblAVSAP_AVSAPWireAudioAssessments | PRIMARY | AssessmentID
tblAccessions_AccessionCollectionIndex | PRIMARY
tblAccessions_AccessionCreatorIndex | PRIMARY
tblAccessions_AccessionLocationIndex | PRIMARY
tblAccessions_AccessionSubjectIndex | PRIMARY
tblAccessions_Accessions | PRIMARY
tblAccessions_ProcessingPriorities | PRIMARY
tblCollections_BookCreatorIndex | PRIMARY
tblCollections_BookLanguageIndex | PRIMARY
tblCollections_BookSubjectIndex | PRIMARY
tblCollections_Books | PRIMARY
tblCollections_Classifications | PRIMARY
tblCollections_CollectionBookIndex | PRIMARY
tblCollections_CollectionContentCreatorIndex | PRIMARY | CollectionContentID | CreatorID
tblCollections_CollectionContentSubjectIndex | PRIMARY | CollectionContentID | SubjectID
tblCollections_CollectionCreatorIndex | PRIMARY
tblCollections_CollectionLanguageIndex | PRIMARY
tblCollections_CollectionLocationIndex | PRIMARY
tblCollections_CollectionSubjectIndex | PRIMARY | CollectionID | SubjectID
tblCollections_Collections | PRIMARY
tblCollections_Content | PRIMARY | CollectionID | ParentID | LevelContainerID
tblCollections_ExtentUnits | PRIMARY
tblCollections_LevelContainers | PRIMARY
tblCollections_LocationRepositoryIndex | PRIMARY
tblCollections_Locations | PRIMARY
tblCollections_MaterialTypes | PRIMARY
tblCollections_ResearchAppointmentMaterialsIndex | PRIMARY
tblCollections_ResearchAppointmentPurposes | PRIMARY
tblCollections_ResearchAppointments | PRIMARY
tblCollections_ResearchCarts | PRIMARY
tblCollections_ResearcherTypes | PRIMARY
tblCollections_UserFields | PRIMARY
tblCore_Configuration | PRIMARY
tblCore_ModificationLog | PRIMARY
tblCore_Modules | PRIMARY
tblCore_Packages | PRIMARY
tblCore_PatternUnitTestIndex | PRIMARY | PatternID
tblCore_Patterns | PRIMARY
tblCore_Phrases | PRIMARY | PhraseName | ModuleID | PackageID | PhraseTypeID
tblCore_Repositories | PRIMARY
tblCore_Sessions | PRIMARY
tblCore_StateProvinces | PRIMARY
tblCore_UserPermissions | PRIMARY
tblCore_UserProfileFieldCategories | PRIMARY
tblCore_UserProfileFieldCountryIndex | PRIMARY
tblCore_UserProfileFields | PRIMARY
tblCore_UserRepositoryIndex | PRIMARY
tblCore_UserUserProfileFieldIndex | PRIMARY
tblCore_UserUsergroupIndex | PRIMARY
tblCore_UsergroupPermissions | PRIMARY
tblCore_Usergroups | PRIMARY
tblCore_Users | PRIMARY
tblCreators_CreatorCreatorIndex | PRIMARY | CreatorID | RelatedCreatorID
tblCreators_CreatorSources | PRIMARY
tblCreators_Creators | PRIMARY
tblDigitalLibrary_DigitalContent | PRIMARY
tblDigitalLibrary_DigitalContentCreatorIndex | PRIMARY
tblDigitalLibrary_DigitalContentLanguageIndex | PRIMARY
tblDigitalLibrary_DigitalContentSubjectIndex | PRIMARY
tblDigitalLibrary_FileTypes | PRIMARY
tblDigitalLibrary_Files | PRIMARY | DigitalContentID
tblDigitalLibrary_MediaTypes | PRIMARY
tblSubjects_SubjectSources | PRIMARY
tblSubjects_Subjects | PRIMARY | ParentID


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 » Thu Jun 23, 2011 4:54 pm

david.b.malone wrote: We still have pages that stop loading at the same spot on all finding aid pages no matter the size as it appears that the scripts are doing a specific type of query.


The best way to pinpoint this query would be to enable the mysql.slow log.
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 5:05 pm

paulnsorensen wrote:
david.b.malone wrote: We still have pages that stop loading at the same spot on all finding aid pages no matter the size as it appears that the scripts are doing a specific type of query.


The best way to pinpoint this query would be to enable the mysql.slow log.


Thanks! Will try and push this through. This afternoon 2GB and another CPU were added to the server and this has helped address some of our lagging response.

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 gordieschmitt » Tue Jul 31, 2012 3:15 pm

Something to try. I believe we're having same issues and we seem to have the indexes in place. I tracked down one of the queries giving us problems and tweaked it a bit and it seems to resolve the problem.


In the file ./archon/packages/collections/lib/collection.inc.php

about line 636ish

I changed the following query line:

#$query = "SELECT ID,CollectionContentID FROM tblDigitalLibrary_DigitalContent WHERE CollectionContentID IN (SELECT CollectionContentID FROM tblDigitalLibrary_DigitalContent WHERE CollectionID = $this->ID AND CollectionContentID IN (" . implode(",", $contentKeys) . ") $browsable GROUP BY CollectionContentID HAVING COUNT(1) = 1)";

to


$query = "SELECT dd.ID,dd.CollectionContentID FROM tblDigitalLibrary_DigitalContent dd, (SELECT CollectionContentID FROM tblDigitalLibrary_DigitalContent WHERE CollectionID = $this->ID AND CollectionContentID IN (" . implode(",", $contentKeys) . ") $browsable GROUP BY CollectionContentID HAVING COUNT(1) = 1) a where dd.CollectionContentID = a.CollectionContentID";


I post this as something to test and if it works something to consider as a patch.

Gordie
gordieschmitt
 
Posts: 9
Joined: Mon Jul 06, 2009 8:23 am

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

Postby insane_shane101 » Fri Sep 07, 2012 2:05 pm

I know this is an old thread, but in case people are still running into this issue: Gordie's solution has been tested on the Archon system I have been tasked with enhancing. The page loads on the Finding Aid pages are significantly faster, even with the cache turned off (which is great for keeping the DB size low and not having to deal with flushing the cache for template alterations).

The query speed went from ~8.5-9 seconds to 0.00.

Thanks, Gordie.
insane_shane101
 
Posts: 1
Joined: Fri Sep 07, 2012 1:59 pm

Previous

Return to Archon Discussion

Who is online

Users browsing this forum: No registered users and 1 guest

cron