Hello all,
So we've been watching our Gallery2 application response time degrade over time. Now to a point where users are no longer willing to deal with the application.
I've gone through the trouble shooting and performance tips doing what I can where I can. I've just recently turned the database logging up to a level where I can watch what is being sent to the DB as far as DML statements.
I have one statement repeatedly being sent, the query is simple enough but there's an 'IN' condition which contains something like 26000 values. here's a sample:
SELECT g2_ChildEntity.g_id , g2_ItemAttributesMap.g_orderWeight
FROM
g2_ChildEntity, g2_DataItem, g2_AccessSubscriberMap, g2_ItemAttributesMap
WHERE g2_ChildEntity.g_parentId = 248307
AND
g2_ChildEntity.g_id = g2_DataItem.g_id
AND
g2_AccessSubscriberMap.g_itemId = g2_ChildEntity.g_id
AND
g2_AccessSubscriberMap.g_accessListId IN (548846,552680,526531,548770,525938,53939
3,546627,561641,553097,557975,544820,539662,525283,561603,516005,334905,545193,545688,456692,554785,558
700,537090,556889,540180,521396,559065,548427,554194,515319,547194,556675,513711,545757,539045,536658,5
15145,517996,519053,523353,526667,542265,535507,558345,540648,540897,543430,537913,512179,551424,550023
,538649,560438,533682,544913,529944,533938,539986,541719,533585,527120,454341,535242,516701,535472,5529
03,556805,515005,557045,547661,512336,529991,533818,551486,554485,525543,554988,556779,541519,473709,51
4816,541262,533081,546001,552508,553788,557821,454491,512901,520033,512414,542141,516012,454382,528899,
535065,528884,550080,545667,546134,542632,521230,452252,534660,557046,535813,544241,555412,539028,51504
4,559173,526490,524334,535903,558749,525479,555342,526383,540373,557868,543649,553227,517317,533982,535
445,533249,529772,542408,523399,535547,513441,552026,533791,541809,548816,512577,539307,522943,519950,5
25211,533536,536181,556158,512327,517373,517629,533285,512144,522579,528388,550556,514395,530540,518917
,533817,539359,555891,527875,521578,545748,530799,516726,520953,529447,556633,557956,549165,526396,5338
21,538481,540026,549013,513886,514200,534476,540960,515063,548883,537165,546115,551501,533365,557541,53
8573,540731,542889,536749,565465,544926,530521,545811,519047,523101) LIMIT 1;
Can anyone tell me what g2_AccessSubscriberMap.g_accessLIstId is, and more importantly why gallery2 would be sending a list of 26k+ values as part of that final IN clause?
Please help!?
thank you,
--Dom
Oh, btw. Here's my system info:
Get system details; useful for copy/paste into G2 support forum.
Last Run Details:
Gallery version = 2.1 core 1.1.0
PHP version = 5.2.5 apache2handler
Webserver = Apache/2.2.8 (Unix) DAV/2 mod_ssl/2.2.8 OpenSSL/0.9.8g PHP/5.2.5
Database = postgres 8.2.5 3 9.0.4, lock.system=flock
Toolkits = ImageMagick, SquareThumb, Exif, Getid3, Thumbnail, Gd
Acceleration = full/1800, full/1800
Operating system = SunOS lantern 5.10 Generic_120012-14 i86pc
Default theme = *****
Locale = en_US
Browser = Mozilla/5.0 (Macintosh; Intel Mac OS X 10.6; rv:11.0) Gecko/20100101 Firefox/11.0
Posted: Fri, 2012-08-10 22:46
Posts: 8339
Are you using the dynamic albums module?
-s
All New jQuery Minislideshow for G2/G3
Posts: 1642
How many items and users do you have?
The permissions requirements for G2 can be a strain on a system when there are large numbers involved.
However, I also note you are using an extremely old version of G2 and who knows whether the issues you are facing have since been addressed/mitigated in one of the 12 or so updates of G2 released since your installation.
--
dakanji.com
Posts: 12
No I don't believe we are using this module. Or at least it isn't listed within the 'Modules' section under administration.
Posts: 12
Just running a simple query...
select count(g_id) from gallery2.g2_item where g_cancontainchildren = 1;
count
-------
31501
(1 row)
So we do have many user created "albums".
Doing a count on the gallery2.g2_user table there are 141 users.
So obviously there's probably a big ACL problem with this many users having permissions on that many albums.
If I modify the first query to list images only there's a count of:
109187
We are going to upgrade to gallery 2.3.2 here real soon. But i'm still a bit concerned that this many items coumpounded with this many users is our biggest problem. And I don't really see a way around this. Is the upgrade going to make that big of a difference on the ACL?
Posts: 1642
Going back to your original question
You have answered this. You have 31.5k+ albums on your site and 26k+ of these appear to have some permission restrictions on them that the application needs to track.
Could it do the tracking more efficiently? Maybe.
I am sure though that you will agree that that 31.5k+ albums is pushing things.
On the upgrade and the ACL, I don't know. I just know that trying to troubleshoot any application sitting a dozen updates behind is generally a waste of time.
Your choices appear to be:
1. Upgrade your DB resources. Maybe you need a dedicated Db server
2. Restrict the number of Access Controlled items your users can create ... will need a custom module
3. Find another application to do the task G2 does.
--
dakanji.com
Posts: 12
Thanks for the help. We are looking at 1st: upgrading our server for both the application and the database. 2nd: upgrading gallery2 to the newest version (2.3.2). 3rd: perhaps modifying our album structures and permission scheme.
again... thank you.
--Dom
Posts: 8339
@pbo_dom the preferred method for upgrading such an old gallery is:
2.1.x -> 2.1.2 -> 2.2.6 -> 2.3.2
Several users have had issues where not all tables update properly when making the full jump from 2.1.x to 2.3.x
-s
All New jQuery Minislideshow for G2/G3
Posts: 12
@suprsidr
Well, I can't find 2.1.2 as a download. So i'm skipping to 2.2.6. I'll let you know how it goes.
thanks,
Posts: 1642
In theory, you should be able to upgrade between any two versions of G2. That is how the part of the code that handles the upgrades is written to work.
In reality however, it appears something is broken there and no one is ever likely to try to get to the bottom of it.
If your v2.1 to v2.2.6 upgrade works, that will help narrow down the suspect code a bit (we know v2.2.6 to v2.3.x works and that v2.2.4/v2.2.5 to v2.3.x is also likely to work) in the unlikely event that someone does take a stab at fixing it.
--
dakanji.com
Posts: 12
OK. So attached is the output from the upgrader page.
I'm attempting to upgrade from 2.1 to 2.2.6
A few notes here:
1) I'm migrating to a new server. Faster and more disc space.
2) Rather then upgrading my production running 2.1 i've rsynced the g2data directory and scp'd the gallery2 directory.
3)I've created a new database using backups captured from production.
4) I've changed all directory references in config.php to the correct location(s) on the new server. (I think)
I can start up the upgrade application in my browser and walk through. myserver.org/gallery2/upgrade/index.php?step=1
I get the errors listed in gallery2.2.6_authenticate_error.txt after I click authenticate. These are displayed at the top of the screen above the upgrader app. which by the way says 'authentication successful!'
I click the 'continue to step 2' button and I bounce back the the welcome screen listed above and get the errors listed in gallery2.2.6_upgrader_error.txt
Seem the app is stuck in the loop.
any ideas?
or perhaps does anyone have working links to get the following versions: 2.1.2, 2.2, etc. So I don't have to skip from 2.1 to 2.2.6 immediately?