MG 1.1 Media Gallery performance

[Sadik B]

Hi

I have just imported a 4 million images bespoke solution into Xen Gallery through a custom CLI importer I wrote.

The import was the easy part, but now I am having a hard time.

For starters, the rebuild thumbnails has been running for two days now and keeps hanging in between. I am going to write a CLI function for that now. Talking about rebuild, how does one cancel a rebuild process which is queued in deferred? I cancelled the thumbnail creation rebuild process and deleted the row from xf_deferred table but the "There are manual rebuild process pending..." link still shows up. How can I clear that?

However the main trouble is that the media index and album index is now impossible to open. I have set the past number of days to 1 now, but that does not seem to be helping.

I have just only begun to look into the queries and the permission queries seem to be the main culprit. I will dig deeper though... The first thing which showed up was the count(*) queries to count album and media in album and media models. A simple count(album_id) and count(media_id) would utilize the benefit of the primary key index and would be be much faster.

I am looking to see how I can optimize things further and make it usable. Will be happy to report back what I do.

 

[Mike]

I cancelled the thumbnail creation rebuild process and deleted the row from xf_deferred table but the "There are manual rebuild process pending..." link still shows up. How can I clear that?

There must still be something in the table that's triggering that. That's where it comes from.

A simple count(album_id) and count(media_id) would utilize the benefit of the primary key index and would be be much faster.

Unfortunately, that's not true. It will either be no different or slower, as that's not how counts work. Counts will potentially be tricky if trying to cover 4 million entries; it wouldn't really be much different in the forum, though the privacy element of the gallery makes it much more difficult. Limiting by date should help immensely, unless MySQL isn't using the index it should be (which happens).

If you can show some queries that are problematic along with the corresponding EXPLAIN output, that would help.

 

[teletubbi]

I had not so much pictures only about 300k.
But run also into this. Always stops while building the thumbnails.

Chris did this for me on the CLI but took also some time.

 

[Sadik B]

Yeah I am writing a CLI for the thumbnails as well as setting avatars from the images.

Mike, I see your point. The date filter does not seem to be working. I will see what I can find from the explain plan.