UPDATE query thrashing mySQL

Sad Eeyore

Joined: 2014-01-25
Posts: 3

Posted: Sun, 2014-02-16 16:24

For some reason when uploading an image Gallery3 decides run this update query (or one similar):

UPDATE `items`
SET `right_ptr` = `right_ptr` + 2
WHERE `right_ptr` >= '22962'

Which means it goes through updating over 100k rows in the database causing the entire thing to ground to a halt. All the reads are stuck waiting for locks to open, and mySQL's load goes through the roof. Adding one image should not cause updates to the entire table, that is a terrible design. The more images I convert to gallery 3 the slower and slower adding new images becomes causing the entire site to stop working.

Anyone know of a way to rewrite this, change this, fix this? Because I can't continue using it if it can't scale beyond a few hundred thousand images.

mblythe

Joined: 2011-01-23
Posts: 3

Posted: Sun, 2014-07-20 23:06

I was having a similar problem, so I investigated a bit. I found that it's due to the way Gallery3 stores the hierarchical album/photo data. It uses a scheme called "Modified Preorder Tree Traversal", or MPTT. This is a way of representing hierarchical data that makes lookups & reading the data very fast, but the consequence is that modfying the hierarchical data is computationally expensive. MPTT is described very well here: http://www.sitepoint.com/hierarchical-data-database/

I have to admit that the use of MPTT is a reasonable design decision. One tenet of programming is "optimize the common case"...in this case, reading the tree structure (e.g. displaying the web page to site visitors) is much more common than modifying it (e.g. uploading new photos), so the use of MPTT seems approriate.

I do agree, however, that the implementation of MPTT might be improved. For instance, instead of updating every existing item whever a single image is added, the MPTT data could be updated after the "current batch" of photos is added. This would introduce a small period of time where the MPTT data is inconsistent, so the impact of that would need to be evaluated. Also, "batch" updating like that may be more expensive than the current implementation when uploading a small number of photos.

Unfortunately, I don't think we'll ever see an official release of Gallery3 with any fix for this, since the developers have stepped down.

If I find some time, I may try to make some improvments myself. I've forked the Gallery3 code to my github here: https://github.com/mblythe86/gallery3/tree/3.0.x and that's where I'll post any changes I make.

Sad Eeyore Joined: 2014-01-25 Posts: 3	Posted: Sun, 2014-02-16 16:24
	For some reason when uploading an image Gallery3 decides run this update query (or one similar): UPDATE `items` SET `right_ptr` = `right_ptr` + 2 WHERE `right_ptr` >= '22962' Which means it goes through updating over 100k rows in the database causing the entire thing to ground to a halt. All the reads are stuck waiting for locks to open, and mySQL's load goes through the roof. Adding one image should not cause updates to the entire table, that is a terrible design. The more images I convert to gallery 3 the slower and slower adding new images becomes causing the entire site to stop working. Anyone know of a way to rewrite this, change this, fix this? Because I can't continue using it if it can't scale beyond a few hundred thousand images.
	XUser login Username: * Password: * Create new account Request new password
mblythe Joined: 2011-01-23 Posts: 3	Posted: Sun, 2014-07-20 23:06
	I was having a similar problem, so I investigated a bit. I found that it's due to the way Gallery3 stores the hierarchical album/photo data. It uses a scheme called "Modified Preorder Tree Traversal", or MPTT. This is a way of representing hierarchical data that makes lookups & reading the data very fast, but the consequence is that modfying the hierarchical data is computationally expensive. MPTT is described very well here: http://www.sitepoint.com/hierarchical-data-database/ I have to admit that the use of MPTT is a reasonable design decision. One tenet of programming is "optimize the common case"...in this case, reading the tree structure (e.g. displaying the web page to site visitors) is much more common than modifying it (e.g. uploading new photos), so the use of MPTT seems approriate. I do agree, however, that the implementation of MPTT might be improved. For instance, instead of updating every existing item whever a single image is added, the MPTT data could be updated after the "current batch" of photos is added. This would introduce a small period of time where the MPTT data is inconsistent, so the impact of that would need to be evaluated. Also, "batch" updating like that may be more expensive than the current implementation when uploading a small number of photos. Unfortunately, I don't think we'll ever see an official release of Gallery3 with any fix for this, since the developers have stepped down. If I find some time, I may try to make some improvments myself. I've forked the Gallery3 code to my github here: https://github.com/mblythe86/gallery3/tree/3.0.x and that's where I'll post any changes I make.

UPDATE query thrashing mySQL

XUser login