Piwigo.org

rp1428 · 2020-06-08 15:15:24

I have created a new piwigo database and reloaded some of my pictures. Currently at around 250K. The performance of creating thumbnails decreased quite a bit. I added an index on the path column for the table piwigo_images.

Is this safe long term? I was wondering whether to remove it once my rebuild is complete. Performance is now much faster.

samwilson · 2020-06-09 09:59:58

Sounds like a sensible column to index. Maybe you could submit a PR to add the index? I guess the only drama with you having a local difference like that might be that one day Piwigo will add an index there but call it something else and so an upgrade process wouldn't necessarily notice yours and create another.

rp1428 · 2020-06-10 13:31:25

Observed about a 10x increase in creation of thumbnails with this index set. Also, mysql cpu usage dropped to about a 10th.

deheme · 2020-06-11 19:37:33

Hello,

Seams very interesting, could you elaborate a bit and share this subject with us.
I'm newbie and I would be keen to get a detailed explanation.

Thnaks a lot
DéHème
Sorry for my frenglish.

rp1428 · 2020-06-11 19:54:54

The mysql query using "path" to the <db>_images table was taking around half a second (ish). I have a 255K images in my library. Adding the index to the "path" field significantly improved the query. Roughly it went from 1-2 per second to 10-12 per second. And top on the server indicated a big drop in mysql cpu usage.

deheme · 2020-06-11 21:05:51

Thanks,

I'm a bit dumb, I still dont understand what is the modification and where to apply it.
If it is such an improvement the "patch" should be largely shared.

I've only 50k images by ftp but the derivative generation is always too long!

DéHème

rp1428 · 2020-06-11 21:15:40

Sorry,

You'll need access to the database, for instance using mysql CLI or a GUI tool. The command is:

ALTER TABLE piwigo_images ADD KEY images_path_rp (path);

where images_path_rp can be any name.

executive · 2020-06-12 00:53:53

any downsides to this, or reasons why it shouldn't be included in Piwigo core?

Piwigo.org

Announcement

#1 2020-06-08 15:15:24

[resolved] Slow performance when generating thumbnails

#2 2020-06-09 09:59:58

Re: [resolved] Slow performance when generating thumbnails

#3 2020-06-10 13:31:25

Re: [resolved] Slow performance when generating thumbnails

#4 2020-06-11 19:37:33

Re: [resolved] Slow performance when generating thumbnails

#5 2020-06-11 19:54:54

Re: [resolved] Slow performance when generating thumbnails

#6 2020-06-11 21:05:51

Re: [resolved] Slow performance when generating thumbnails

#7 2020-06-11 21:15:40

Re: [resolved] Slow performance when generating thumbnails

#8 2020-06-12 00:53:53

Re: [resolved] Slow performance when generating thumbnails

Board footer