Speeding Up Gallery 2
I’ve written a couple posts in the past raving about Gallery 2, an open source, web based photo album organizer. An I’ve imported most of my digital photos and media, bringing the total ‘items’ up to over 16,000. But I have been finding Gallery 2 to be incredibly slow. And I don’t remember if it was always like this or if it’s a result of me adding more and more photos. But this afternoon I decided to get to the root of the problem(s).
I did some quick searching around on Google and discovered some other users have found the app slow, but no solid answers as to why. I didn’t bother posting to the forums myself because I run Gallery on top of PostgreSQL and far too often I’ve been given the flame answer of ‘Oh, well it runs fine for me on MySQL so that’s likely your problem’. And perhaps, in this case, it is partially to do with PostgreSQL. I knew it wasn’t an issue with the server because the load is low and there was no real hit on apache until just before the page rendered. Unfortunately, that left the database. And truth be told, this might also be an issue under MySQL in some cases, but since I use PGSQL, I can only talk about it.
If you’re finding Gallery 2 slow, start by disabling the ‘Image Block’ module. It has some SQL in it that can be the problem. It’s not that the SQL is bad, it’s just that the Image Block module runs a couple of queries that if not using an index will take far longer than necessary. That’s both a PostgreSQL and a Gallery problem. If that works, and you can live without the nice random image you get with the Image Block, then you’re done. If not, then you need to have access to your database so you can do some tweaking.
There are two slow queries, both which should be using an index. Load up PostgreSQL’s psql program, or some other DB admin tool that lets you run queries, and try this:
EXPLAIN SELECT COUNT(*) FROM g2_ImageBlockCacheMap WHERE g2_ImageBlockCacheMap.g_userId = 10;
You should see:
QUERY PLAN
———————————————————————–
Aggregate (cost=11042.91..11042.91 rows=1 width=0)
-> Index Scan using g2_imageblockcachemap_1627 on g2_imageblockcachemap (cost=0.00..11007.43 rows=7095 width=0)
Index Cond: (g_userid = 10)
(3 rows)
The important part is that you see Index Scan and NOT Seq Scan. If you see Seq Scan, then the database is going through the entire table. Twice, in fact, since the second query I mentioned is likely going to give you a similar result. At this point you have two options, and you may want to consider doing both.
First, you can tweak the PostgreSQL configuration options that deal with query plans. I suggest reading up on the options first and doing trials on a non-production database. Especially since a PG restart is required for any changes to take effect. Every database config will be different based on processor power and memory and such, so I leave it up to you to figure out how best to change things for your setup. The key thing is though, that you want to tell PostgreSQL’s planner that whenever possible it should use indexes. And sequential scans should be a last resort, except for small tables.
That done, it still may not be enough. And it wasn’t enough to work for me. I’m not entirely sure why the index on the g2_ImageBlockCacheMap table still wasn’t being used but I suspect that it might have to do with the fact that it’s a tuple. Regardless, all that was required was to add an index and vacuum the DB, as such:
CREATE INDEX g2_imageblockcachemap_idx_extra ON g2_imageblockcachemap(g_userid);
VACUUM ANALYZE;
Then re-run that EXPLAIN to see if the index is used instead. It worked for me. And once again I can use the Image Block module.
Tue, 13 Jun 2006 03:03 Posted in Technology
