Issue when setting album as hidden with password...
|
SinnerG
Joined: 2007-04-19
Posts: 58 |
Posted: Mon, 2007-04-23 12:29
|
|
Running gallery 2.2.1. Had a large very large album, edited it and requested it to be hidden with a password. Something timed out probably due to the size of the gallery. When I logout to get guest view the album reports a massive count loss in the number of items in it. When logged in the count is fine. I would assume the DB table holding permission information or something that is used to determine descendent count has gone wrong. What table is it? I will set the gallery into debug and check the sql statements, but I'm new to gallery2 and the way it builds sql queries so probably get no luck finding it in the code. |
|
Posts: 32509
- FAQ: What information is required when I ask for help in the forums?
- what's "very large"? how many items / subalbums?
- the item count shown is the number of items a user can see. guests can't see the items by default, thus the different view count.
--------------
Documentation: Support / Troubleshooting | Installation, Upgrade, Configuration and Usage
Posts: 58
Hi!
60,000 total item count when user logged in. I'll have to look at the number of albums in there as I'm not sure who added what and how many. The album was set to hidden, script stopped, now guest still shows the gallery and says some 43,000 items available.
On the previous install (2.1) I simply set hidden and in guest mode it was not displayed. The albums not marked as hidden still showed as expected.
I was even looking at messing with the AccessMap table and annihilating anything with user ID 6 which is guest.
Posts: 32509
> I was even looking at messing with the AccessMap table and annihilating anything with user ID 6 which is guest.
Sounds like a very dangerous idea.
First, you should create a backup of your G2. Unless you have a backup from right before the operation already.
Then you should proceed trying to fix things.
I'd try resetting (removing) permissions by using the normal G2 edit permission user interface.
--------------
Documentation: Support / Troubleshooting | Installation, Upgrade, Configuration and Usage
Posts: 58
Yeah, made a backup, but ofcourse too late as I didn't expect it to die.
Set user 6 to access permission 1 where permission=2147483647... oops ... :D Logged in as admin, head to the permissions section of that album and it says 6 does not have permission for one of the items. I've got back to the permissions page now and will use that.
I will do a read up on how the permissions part is handled.
Posts: 58
Ok, gone through it again. No luck. Still shows the gallery when logged out, reports incorrect items.
Actually, on logout it moans about faked request. I'll create a new album, transfer all the sub-albums and see if I get more luck out of it.
Posts: 58
Moving galleries seems to have fixed the problem. I have a new hidden gallery, set the permissions, etc, before moving some of the sub-albums from the other album. It seems sub-albums carry around 10-200 images, I haven't moved the larger ones yet.
It is incredibly slow. I thought it was the comm time between web server and mysql server, but the ping is on average 0.4ms.
Unsurprisingly the AccessMap table is large (28MB) with over 310,000 records, with AccessSubscriberMap holding 68,000 records which seems inline with the gallery photo count.
Question is, does that AccessSubscriberMap contain every single item in the gallery, be it image or album? Is it possible to only work from album, with photos taking on the access of the parent album instead? That's if I'm thinking correctly ... which might not be the case.
It would cut the table size quite a bit, reduce SQL record retrieval as well, with a bit more work just being done in php instead.
Failing that, I'm gonna tell me users to clean up their crap. :D
Posts: 32509
- yes, there's 1 row per item in AccessSubscriberMap. (Items = DataItem (e.g. photos) or AlbumItem)
- yes, permissions are set on an item-level, not on the album level.
- no, you can't easily switch to album-level permissions.
but it wouldn't help that much in your case. yes, the problem is some bad design, but it has to do with your usage.
sounds like you're making extensize use of the useralbum module. right?
this leads to very heterogenous permissions. almost every item (or every 20th item) of your gallery has different permissions.
thus there are far too many AccessList IDs.
thus, normal SQL queries are getting huge and slow (dozens, hundreds or even thousands of ACL IDs per query).
solution:
for now, avoid the useralbum module if you have a lot of users.
yes, not really a solution, but it tames your SQL queries.
also see:
- docs -> admin & maintenance -> performance.
- development -> roadmap -> performance -> permissions / ACL
--------------
Documentation: Support / Troubleshooting | Installation, Upgrade, Configuration and Usage
Posts: 58
Fantastic. Will take a look at that and see about going to album-level permissions. I would assume a clean up the access tables is to remove those referring to image items once album-level permissions are used.
Looking at only about 20 users, but they have sub-albums. There's about 3000 albums total.
No, isn't a bad design.
Have worked on a similar thing on a C#.NET application also having large reference collections to a few tables. In that instance I walked into a situation where I had to "fix this web page which seems to be consuming 750MB of memory". :-D I've kinda read a bit of the gallery2 code and I'm well impressed. Last time I used PHP was back in 2000, need to get around the object orientated additions to it.
I spent some time last night while transferring galleries and looked around for alternatives (before I get taken out on the job), but nothing matches up.
Posts: 58
I'm too dumb for this ... how do you switch to album-level permissions only?
Posts: 32509
arg, sorry. false promises. i inended to write "no, you can't easily switch to album-level permissions", the "can" is a typo.
i've changed the above post now, the typo is gone.
you say you have 20 users only?
if you do a
select distinct g_accessListId from g2_AccessMap;
select count(*) from g2_AccessMap;
and
select count(*) from g2_User;
what do you get?
--------------
Documentation: Support / Troubleshooting | Installation, Upgrade, Configuration and Usage
Posts: 58
Sorry, my mistake. Only have 3 users right now. I hoofed the rest when gallery 2.1 messed up on their server for some reason.
81711 distinct accessListId and 261586 total records. If I'm thinking correctly that's an extra 20,000 odd which seems to account for the 20,000 photos showing as visible to guest.
It was 311,000+ records this morning, but I've since removed the comments and rating modules which I'm not concerned about.
Been playing around a bit.
I've noticed that functions such as fetchPermissionsForItems and fetchChildCounts can result in queries over 500KB in size or more. 6 characters for each ID, 80000+ and the IDs alone hit 500KB+. I couldn't test how heavy a 500KB sql statement would be from mysql command line interface nor from the mysqladmin interface, it wouldn't take it and I'm busy remotely at the moment. I would suspect with a sql server on a different machine from the web server it could be very noticable.
I've also changed a few sql statements here and there and used INNER JOINs instead of cross product with WHERE clauses and, maybe it is just me, but there's a better response coming back now.
Going to play with fetchPermissionsForItems and fetchChildCounts to replace the aclIds with a select statement originally used to get the acls anyway. The usual "select * from x where x.id in (select id from y)" kind of thing, or a further inner join might be better but will mean more to replace where the aclIds is used. I'm sure mysql is going to do the same amount of work there compared to processing a 80,000 item long integer list. Smaller size to send to the mysql server, same query and I got a result back instantly (0.3 seconds).
Posts: 32509
there are additional accessListIds if you use the hidden / password modules. but having 50 or 300k rows in AccessMap shouldn't be that bad. after all, the records are indexed, the values are integers and the queries shouldn't be that heavy.
the problem is if you have too many accessListIds that map to a specific user, then your queries to select images etc will get expensive.
--------------
Documentation: Support / Troubleshooting | Installation, Upgrade, Configuration and Usage
Posts: 58
Yeah, 300K rows isn't large at all come to thing of it. The problem is ofcourse that admin user has access to everything so that's the one that struggles. I am still going to try using the included sql statement instead of the much larger complete sql statement and see what impact it has. The inner joins have helped quite a bit.
Posts: 32509
@inner joins:
please post a diff / patch to illustrate your changes. maybe we can consider them for inclusion in g2.
--------------
Documentation: Support / Troubleshooting | Installation, Upgrade, Configuration and Usage
Posts: 58
Oooooh, dunno if ya wanna see that. :D It might be surprisingly horrible.
It's been 7 years since I've used PHP and that was v3.
I've finally finished the changes. I need a decent PHP IDE. I messed up with the "getMapEntry" function and managed to keep being redirected back to the main gallery. Fair enough, it was turning a string into a number and means it was looking for access list ID of 0. :P
Well, I now no longer have 500KB+ sql queries going across to the mysql machine. Unfortunately, there's a bit in the function that calculates album item counts where I think you had something nifty for only calculating the missing counts, I had to skip that.
I think it seems faster. Whether this helps me transferring the large galleries is yet to be seen. I will try in the morning.
Will make a diff for you when I can. It's 2AM.
Posts: 58
One more question you might have more insight on than I do...
How bad is it on the gallery if using php.cgi over mod_php in apache?
Posts: 32509
@inner joins:
that's about SQL, not php...
anyhow, if you just change from implicit inner joins to explicit inner joins, then there should be no performance impact at all since this is the same for pretty much all DBMS (some that had issues with that fixed that a few versions ago).
> Well, I now no longer have 500KB+ sql queries going across to the mysql machine.
curious what you changed there.
--------------
Documentation: Support / Troubleshooting | Installation, Upgrade, Configuration and Usage
Posts: 58
Yeah, I know. But I had to do a line or 2 of changes here and there and I've started calling my variables "ook". :D
I've also been explicit on joins, breaking the where across the joins to reduce the size of each table. It does make a difference. I hounded my juniors about there enormous sql statements and no-one got it. They only believed it helps when some document from MS came out. However, the document also explains that you get 2 different results when using cross joins with the same filtering as when using inner joins. I just think that's a SQLServer issue anyway.
All I changed was to remove the list of access IDs being placed in the SQL statement and replaced it with the actual SQL statement that gave the IDs. The DB is going to go and run the statement every time, but it isn't slow. It's just a WHERE ID IN (...) statement. Suppose there'd be an even better boost if one could join directly to the access list table.
Posts: 58
Me again.
I spent a good part of the morning putting in print statements because the gallery started dying unexpectedly after moving alot of albums. As for the album moving, the changes I made seemed to speed that up wonderfully.
Anyway, it came down to a problem where there is a call to get descendent counts. I backed tracked and found another permission list which I then changed to use an included SQL statement and suddenly I was back up and running.
Descendent counts fly now. 
I moved all the smaller albums, now left with 3 of around 1500-3000 photos each. Those simply gave up and resulted in a blank page, no album moved and so on. I then went piece by piece on the first album and then started getting server errors. Then even viewing the albums or main page would give a server error. Funny thing is a reload of the page would then give me back the gallery/album. I am not hunting this down now, too late, but maybe tomorrow I'll have a look.
I just decided to delete the remaining 3 albums and get them to upload again. Have about 8000 photos and I requested a delete. Been running for well over 20 minutes and then suddenly I get a "resource reclaimed" error, but the php engine is still running in the background so I'm leaving it to do its job.
I am hoping the initial faults I saw before I tackled this (with the 20,000 images showing when they should not) is related to a permissions issue within those 3 albums. So hopefully once all these albums are gone the whole thing works again.
I am a bit concerned about the amount of work done when moving items around. I see in the code that all the child items need to have their "paths" fixed as well. I assume this is done to keep a table updated in order to find the child much faster, but the downside is the amount of work required when doing a simple move.
Have you considered moving some of the work (deletes, etc) to stored procedures? Mind you, they can be quite different in implementation between the supported DBs.
I guess tomorrow is another day for sorting out the issues this specific deployment of gallery has.
Posts: 58
Here's an interesting one. I cleared out the cache thinking this problem I am now getting is related to some cached data. But still it happens. So I looked at main.php and around line 357 there is a statement starting "if ($shouldCache) {...". After a print statement or 2 I see it dies within that if statement, so I commented it out and I'm back in business.
There's nothing serious in that "if" block except a call to get getSession and something to make a cacheable URL.
Any ideas, valiant? Session error in apache or I got stuffed cookies?
Posts: 32509
> Have you considered moving some of the work (deletes, etc) to stored procedures? Mind you, they can be quite different in implementation between the supported DBs.
No, we don't consider triggers and stored procedures for G2.
It's a level of optimization that we didn't consider yet because of the associated development, framework, and maintenance costs.
--------------
Documentation: Support / Troubleshooting | Installation, Upgrade, Configuration and Usage
Posts: 58
Yup, understood, it'll open a whole new can of worms.
SQL procedures is like writing old javascript... write one for IE, then one for Netscape. :P :D
Posts: 58
I'm back.
Well, so far, so good this weekend. No errors. I installed eAccelerator on the machine and that sure saves some time.
I also installed xdebug yesterday evening to see where the workload is. Unsurprisingly it is in the template section, doing about 12 seconds work on the main page. The rest isn't holding up anything. Delving down into that it seems within that the work is being done in the section calculating descendent count. The sql call runs just over 3 seconds each. That is not bad at all given the amount of data.
I just attempted a permission setting on the larger gallery again. It's a no-go. Just stops, gives a blank page. The g2_AccessMap reports 861,283 records. Is that right for a total of 60,000 images?!?!
Gonna look tonight if descendent counts can be sped up, but I don't think that time is unreasonable at all. Then I need to figure out why it all packs up when doing permissions.
All in all, the experience is getting better and better.
Posts: 58
I just changed the section for copying access lists to use a sub-select (as indicated in the mysql 3 support comment right above it in the code). Much faster, but it still does not complete the task.
Now, I'm wondering if it fails out because there is just too much to do in the transaction. It is effectively trying to do 69,444 inserts within the transaction. That 69444 ofcourse indicating the image and album count relating to the g2_AccessMap entries needing to be copied. It stops after 19422. :P
valiant, do you think the reason this bombs is because of there being too many queries going off in the transaction?
Posts: 560
Hi SinnerG,
1. you have a huge g2 there, and far over the avarage sized processes..
edit: 60k images isn't that huge, but 800k+ accessmap rows is, definitely ;>
@descendent counts: probably you could live without them, and in that case you could eg. modify the the fetch(Uncached)DescendentCounts() to return an empty array or something like that thus ending up in no desc count display. (Certainly there is a better way to do this, but this is quick;))
Hopefully MPTT will make it into 2.3 and then this should be much faster.
@861,283 rows:
you should try to force compacting that, by changing the odds of a compacting task to 100% and changing a permission somewhere (eg add/remove a core.all perm on a single image).
I don't really see the exact reason for the timeout in the thread: is it db config/php config or g2 based timeout?
(I have 651,104 items with 27,922 accessmap entries, so yours is not normal for sure)
Posts: 58
Hi!
Yeah, the descendent counts can get hoof'd. It's not such a big deal as long as no-one complains.
I did notice that the accessListId is not unique, easily reaching a count of 4 or 5 per accessListId in the AccessMap table for some reason. I am not sure about this being an issue.
I will try and force compacting, but let's just hope that doesn't stop in the middle of nowhere either. Well, here goes ... if I'm not back in a couple of hours then I've stuffed things up and I'm busy restoring the DB.
Posts: 58
No luck. Coughed up an internal server error. I'll dig up the part of G2 where it does the compacting and attempt a direct php call instead.
Posts: 58
As for MPTT... don't you think this is going to be a major change to the way things are setup already? That is, probably much further down the line than 2.3.
Posts: 58
Ughh...
OK, I just tried this:
SELECT *
FROM `g2_AccessMap`
WHERE g_accessListId NOT
IN (
SELECT DISTINCT g_accessListId
FROM `g2_AccessSubscriberMap`
)
LIMIT 0 , 30
The result is 691,715 records in length.
Can I, and should I, remove all those unused entries from the AccessMap? Prefer asking first before death and destruction ensues due to my lack of fully understanding AccessMap and AccessSubscriberMap. 
Posts: 58
Just found tools.gallery2.org... looks like mptt is well under way.
Posts: 58
"User permission added successfully" :D
Set some timeout in the Gallery.class file from 30 seconds to a whopping 100 minutes.
Debug showed some timeout in the advanced permissions class where it looked at something like item count >= 200 and then called updateMap. Took a while to run, but the job is done.
And deleting the 691,715 from AccessMap affected nothing, reducing that file to 100,000 odd entries.
And after the permissions setting worked, I had 168K unmatched entries in AccessMap again so I removed them as well. This would explain how I got up to 800K rows in there.