Announcement

  •  » Engine
  •  » Performance issue - excessive mysqld usage.

#1 2021-05-03 11:28:21

deemon
Member
2021-05-03
45

Performance issue - excessive mysqld usage.

EDIT: This has nothing directly to do with images upload. But with viewing images while removing the image category number from url.

Hello/Hi/Greetings,

(Here your message)
Sometimes on pictures upload the server is crawling under absurd (IMHO) mysqld load. Any explanation why that is?
It's almost to the point where CPU is consumed 100% for hour or so making the website even unresponsive or timeout for users to view pictures. Watched the situation with htop in server... and it's up to 580% cpu (6 cores) usage only by mysqld, everything else is trivial.

You don't keep images in SQL, right? So ... why uploading new images kills SQL?

(Copy here your environment details, found on your Piwigo page [Administration > Tools > Maintenance])
220k photos
5.9k albums
974 tags

    Piwigo 11.4.0 Check for upgrade
    Operating system: Linux
    PHP: 7.1.33-3+0~20191218.29+debian9~1.gbp18b07c (Show info) [2021-05-03 12:19:59]
    MySQL: 5.5.5-10.1.48-MariaDB-1~stretch [2021-05-03 12:19:59]
    Graphics Library: External ImageMagick 6.9.7-4

Last edited by deemon (2021-05-07 22:40:35)

Offline

 

#2 2021-05-03 12:25:21

plg
Piwigo Team
Nantes, France, Europe
2002-04-05
13786

Re: Performance issue - excessive mysqld usage.

Hi deemon,

Aren't you in the case described on [Forum, topic 28338] Performance issue with high volume ? (looks like it)

Offline

 

#3 2021-05-03 15:59:50

deemon
Member
2021-05-03
45

Re: Performance issue - excessive mysqld usage.

Might be, but I am quite new to the Piwigo myself.

What is this cache? Where do I check if the cache (re?)generation is the culprit? Is it stored to mysql or in files (can I check some folder for new files generation date or something, to see if they were created during this excessive mysql usage?) or just some info is pulled from mysql for this cache generation?
Cache of what exactly?

On the topic of any cache "re-generation", shouldn't it add only the new stuff to the cache, not brutally regenerate it for the entire DB/site again?

Offline

 

#4 2021-05-04 15:02:50

plg
Piwigo Team
Nantes, France, Europe
2002-04-05
13786

Re: Performance issue - excessive mysqld usage.

deemon wrote:

What is this cache?

It's a cache with informations related to albums granted to the user. Depending on the user, each album won't have the same infos. For example user U1 might have 75 photos within album A1 and user U2 might see only 46 photos inside the very same album. This information is stored in this cache.

Each time a photo is added (and other administration action), this cache is "invalidated". When a user opens the gallery, if the cache is invalid, Piwigo rebuilds this cache for this specific user.

When you are uploading many photos very quickly and visitors are visiting your gallery at the same time, Piwigo constantly invalidate the cache and rebuild the cache. This costs a lot.

deemon wrote:

Is it stored to mysql or in files

In the database.

deemon wrote:

On the topic of any cache "re-generation", shouldn't it add only the new stuff to the cache, not brutally regenerate it for the entire DB/site again?

Among the most difficult things in programming, you find "maintaining a cache synchronized" (this is something we try to do in the Android App and it's very complicated). We don't want to do that in Piwigo. The cache is either "fully built" or "fully destroyed".

Offline

 

#5 2021-05-05 01:07:50

deemon
Member
2021-05-03
45

Re: Performance issue - excessive mysqld usage.

plg wrote:

deemon wrote:

What is this cache?

It's a cache with informations related to albums granted to the user. Depending on the user, each album won't have the same infos. For example user U1 might have 75 photos within album A1 and user U2 might see only 46 photos inside the very same album. This information is stored in this cache.

Alright. Sounds like absurd idea. Can I turn this cache off entirely as we don't need any of it at all?
All photos in non-locked albums should be visible to every page visitor, not even registered users.

Especially when this unneeded cache rebuilding knocks the server unresponsive for an hour or so...

Last edited by deemon (2021-05-05 01:19:30)

Offline

 

#6 2021-05-05 02:56:58

erAck
Only trying to help
2015-09-06
1998

Re: Performance issue - excessive mysqld usage.

AFAIK a cache is also built for the guest user.


Running Piwigo at https://erack.net/gallery/

Offline

 

#7 2021-05-07 00:38:24

deemon
Member
2021-05-03
45

Re: Performance issue - excessive mysqld usage.

plg wrote:

deemon wrote:

On the topic of any cache "re-generation", shouldn't it add only the new stuff to the cache, not brutally regenerate it for the entire DB/site again?

Among the most difficult things in programming, you find "maintaining a cache synchronized" (this is something we try to do in the Android App and it's very complicated). We don't want to do that in Piwigo. The cache is either "fully built" or "fully destroyed".

How can I verify that my problem is certainly related to this cache rebuilding?
Is it logged somewhere?

Is there at least some mitigation options to make this less painful? add certain mysql cache? something else?
I mean like seriously. There must be something...

I mean like the piwigo mysql databse folder is like 145MiB ONLY (edit: after "Repair and optimize database" only 134MiB). Any clue how to make it all fit into server memory/cached instead of being loaded over and over again from disk for like 40minutes straight just today... at some point. I don't know but maybe you could optimize this cache regeneration somehow? pull it with 1 query maybe and then sort it in memory if you really need?

How exactly this cache rebuilding does work? Can you walk me through the logic and each sql query it does, like line by line?

Like how many queries does it exactly take to purge and rebuild this entire cache? like 1? 5? 10? 6k for 6k albums? 200k for 200k pictures (which would suggest really terrible programming, but I have seen worse solutions in my lifetime)? even more awful coding and several queries per picture (like 200k * X for each user?) :D
I mean like when the entire database "repair and optimize" mainteneance task takes like less than minute, then ... what exactly is happening with the 100% CPU load by mysql queries for 40minutes??? It's surreal.

Mysql should not die in this manner. Whatever piwigo is doing there with the mysql, it's doing it really really wrong. And I would really like to figure the exact action out and find a sane solution. Any clues how to approach this and pinpoint the issue here?

other issues... maybe off topic, but checked, there was from this 220k photos, 134k without checksum. Why aren't they generated at photos upload automatically? What are they used for? Just duplicate search or anything else also?
Anyway when I ran to add those, it takes only like 13-14% server cpu to do that... and it did it in like 10k checksums in 4 minutes and then died with error 200 (probably cause I went to other browser tabs to check stuff and came back too late?). clicked "refresh photo set", got 124k missing checksums. Ran it again, now doing the rest... stopped again with error 200; stopped at 97k missing checksums, so second batch was like ... 27k done in like 13 minutes...

Last edited by deemon (2021-05-07 01:40:00)

Offline

 

#8 2021-05-07 05:45:32

Zentalquabula
Member
2014-05-10
217

Re: Performance issue - excessive mysqld usage.

Seems like a resource issue. How much traffic do you have? Do you have bots crawling your site?

How much memory do you have installed, and how much is dedicated to MySQL? How many cores? What parameters do you use for MySQL?

Offline

 

#9 2021-05-07 09:30:20

plg
Piwigo Team
Nantes, France, Europe
2002-04-05
13786

Re: Performance issue - excessive mysqld usage.

in plugin LocalFiles Editor, local configuration, you can add :

Code:

<?php
$conf['show_queries'] = true;
?>

and get an idea of MySQL queries performed and their duration.

As brainstormed before (but not implemented, I will work on it for Piwigo 12) I think the solution is in rebuilding the user cache less often, buy putting the single photos in a stash mode.

The mecanics of the cache system was designed when photos were added by synchronization. Meaning photos were added in a batch, not one by one. We need to recreate this workflow, somehow.

Offline

 

#10 2021-05-07 15:47:25

deemon
Member
2021-05-03
45

Re: Performance issue - excessive mysqld usage.

Zentalquabula wrote:

Seems like a resource issue. How much traffic do you have? Do you have bots crawling your site?

Not really I hope, I have set robots.txt:

User-agent: *
Disallow: /

Zentalquabula wrote:

How much memory do you have installed, and how much is dedicated to MySQL? How many cores? What parameters do you use for MySQL?

8G memory; 6 cores atm (was 4, asked 2 extra because of the current issue);
MySQL parameters are not my strong side, so any suggestions what I should set them given the 8G on RAM in the machine? I am not even sure what exact parameters I should tweak.
For sure it could and should use more than it currently is. I have never seen this server use more than 1.2G out of 8G RAM. But I can't exactly find one ram parameter there, there's like 10+ or something... :(
Piwigo seems to be using MyISAM... so the InnoDB numbers should be irrelevant here, but just in case.

max_connections=100
max_allowed_packet=16M
thread_cache_size=128         (128 what? no idea)
sort_buffer_size=4M
bulk_insert_buffer_size=16M
tmp_table_size=32M
max_heap_table_size=32M

query_cache_limit=128K
query_cache_size=128M

myISAM
key_buffer_size=128M
table_open_cache=400
myisam_sort_buffer_size=512M
concurrent_insert=2
read_buffer_size=2M
read_rnd_buffer_size=1M

innodb_
buffer_pool_size=256M
log buffer=8M
open_files=400
io_capacity=400

Btw. is there a reason why Piwigo uses MyISAM engine and not InnoDB? (I read that MyISAM only caches keys (and relies on OS file caching for data), whereas InnoDB can also cache data.)

Last edited by deemon (2021-05-07 16:52:54)

Offline

 

#11 2021-05-07 17:49:56

Zentalquabula
Member
2014-05-10
217

Re: Performance issue - excessive mysqld usage.

You could simply convert the Piwigo tables to InnoDB. More memory, but more efficient.

Site suckers ignore your robots.txt, and just crawl as much as they can. You would need to firewall out all that crap, as it can cause the problems you are describing.

You could also install mysqltuner to get an idea of what may be wrong.

Offline

 

#12 2021-05-07 18:16:11

deemon
Member
2021-05-03
45

Re: Performance issue - excessive mysqld usage.

I doubt about the site suckers, because the max-CPU-windows correlate with our admins uploading new galleries. and/or briefly after that the slowdown begins and goes on quite a while. ( I mean sometimes the mysql cpu hogging begins during the upload and might even kill the upload ... other times it happens like just after the upload.)

Zentalquabula wrote:

You could simply convert the Piwigo tables to InnoDB. More memory, but more efficient.

Can you? I mean is it supported? Will it be OK for Piwigo updates to work correctly? Anyone done so and has had no complications for a longer period of time??

Last edited by deemon (2021-05-07 18:27:30)

Offline

 

#13 2021-05-07 18:40:56

deemon
Member
2021-05-03
45

Re: Performance issue - excessive mysqld usage.

plg wrote:

in plugin LocalFiles Editor, local configuration, you can add :

Code:

<?php
$conf['show_queries'] = true;
?>

and get an idea of MySQL queries performed and their duration.

Thanks. Tested it, but ... this doesn't really help. I can't add this to the live server for obvious reasons and it does show only the queries for the user who causes the slowdown. When this happens, I am not the culprit, ever.

Last edited by deemon (2021-05-07 18:41:35)

Offline

 

#14 2021-05-07 21:56:39

deemon
Member
2021-05-03
45

Re: Performance issue - excessive mysqld usage.

OMG!!! I found the problem. When someone is trying to view picture without gallery (like, tampering with the URL and removing the gallery number), with link like:
https://<filtered>/picture.php?/478517
or
https://<filtered>/picture.php?/478517/categories

all hell breaks loose. the SQL query alone is like 1.5MB. The query takes like 23 seconds to get an answer... and then when someone (like a crawler or bot, clicking on every left and right arrow there, to move to the next picture in the same manner) is like scrolling through those pictures, it's like the end. Can't even paste the query here, as piwigo.org forum dies with error:

413 Request Entity Too Large
nginx/1.14.2

submitted bug-report:
[Github] Piwigo issue #1406


I mean like seriously... :D ... one can't even mitigate this really as noone really caches 1.5MB queries, right? :D

Last edited by deemon (2021-05-07 22:29:11)

Offline

 

#15 2021-05-07 22:34:11

erAck
Only trying to help
2015-09-06
1998

Re: Performance issue - excessive mysqld usage.

Sounds being related to the "related albums" feature problem [Forum, topic 31187] 11.x, related albums and performance issues.


Running Piwigo at https://erack.net/gallery/

Offline

 
  •  » Engine
  •  » Performance issue - excessive mysqld usage.

Board footer

Powered by FluxBB

github twitter newsletter Donate Piwigo.org © 2002-2024 · Contact