Multiple PHP processes with long processing time

[mitko]

Hi guys! I need some support from Dev team, as i don't clearly understand what's going on. From time to time i'm getting the error with mySQL simultaneous connection (limited at hoster for 30). Today i got the same, gallery worked quite instable. I request for hoster support and get following:

The problem is that your PHP processes never end and constantly emit database queries. The PHP processes finish after some time but this behaviour is not 'normal' - PHP processes should finish much, much faster and they should never be sending so much queries to the database. It appears that:
a. your PHP scripts (processes) run for much longer than they should (more than 4 minutes)
b. it seems the processes constantly run queries against the database
The fact that one process runs for more than 4 minutes means it is using a database connection for more than 4 minutes, these processes pile up and eventually cause the error message 'User 'mitko_photos' has exceeded the 'max_user_connections' resource (current value: 30)' you have observed.

In the logs they could see something like this
PID RSS %CPU ELAPSED COMMAND
690169 1860 0.0 14:28 sshd: mitko@notty
690177 2288 0.0 14:27 /usr/libexec/openssh/sftp-server
710217 20732 0.0 03:41 php54.cgi
710251 20752 0.0 03:39 php54.cgi
710261 20756 0.0 03:38 php54.cgi
710383 20740 0.0 03:34 php54.cgi
710387 20756 0.0 03:34 php54.cgi
710391 20740 0.0 03:33 php54.cgi
710418 20796 0.0 03:32 php54.cgi
710436 20808 0.0 03:30 php54.cgi
710463 20736 0.0 03:29 php54.cgi
710488 20752 0.0 03:27 php54.cgi
710512 20732 0.0 03:26 php54.cgi
710526 20740 0.0 03:26 php54.cgi
710589 20732 0.0 03:24 php54.cgi
710602 20788 0.0 03:23 php54.cgi
710959 20736 0.0 03:10 php54.cgi
711090 20832 0.0 03:05 php54.cgi
711110 20736 0.0 03:04 php54.cgi
711131 20728 0.0 03:02 php54.cgi
711162 20732 0.0 03:01 php54.cgi
711165 20728 0.0 03:01 php54.cgi
711167 20732 0.0 03:01 php54.cgi
715518 19688 0.2 00:28 php54.cgi

These processes seem to constantly execute the following queries in MySQL:

853395 Database Server Database 0 Query SELECT pid FROM cpg15x_pictures AS r INNER JOIN cpg15x_albums AS a ON a.aid = r.aid WHERE (1) AN
854491 Database Server Database 0 Query SELECT count(pid) AS link_pic_count, max(pid) AS link_last_pid FROM cpg15x_pictures WHERE ((aid
854935 Database Server Database 0 Query SELECT count(pid) AS link_pic_count, max(pid) AS link_last_pid FROM cpg15x_pictures WHERE ((aid
855369 Database Server Database 0 Query SELECT count(pid) AS link_pic_count, max(pid) AS link_last_pid FROM cpg15x_pictures WHERE ((aid
855400 Database Server Database 0 Query SELECT r.pid, r.aid, filepath, filename, url_prefix, pwidth, pheight, filesize, ctime, r.title,
853195 Database Server Database 1 Query SELECT p.* FROM cpg15x_pictures AS p INNER JOIN cpg15x_albums AS r ON r.aid = p.aid WHERE (1) AN
853270 Database Server Database 1 Query SELECT p.* FROM cpg15x_pictures AS p INNER JOIN cpg15x_albums AS r ON r.aid = p.aid WHERE (1) AN
853316 Database Server Database 1 Query SELECT p.* FROM cpg15x_pictures AS p INNER JOIN cpg15x_albums AS r ON r.aid = p.aid WHERE (1) AN
853401 Database Server Database 1 Query SELECT p.* FROM cpg15x_pictures AS p INNER JOIN cpg15x_albums AS r ON r.aid = p.aid WHERE (1) AN
853748 Database Server Database 1 Query SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg15x_pictures WHERE ((aid = '159'
853765 Database Server Database 1 Query SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg15x_pictures WHERE ((aid = '159'
853793 Database Server Database 1 Query SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg15x_pictures WHERE ((aid = '159'
853938 Database Server Database 1 Query SELECT count(pid) AS link_pic_count, max(pid) AS link_last_pid FROM cpg15x_pictures WHERE ((aid
854062 Database Server Database 1 Query SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg15x_pictures WHERE ((aid = '159'
854136 Database Server Database 1 Query SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg15x_pictures WHERE ((aid = '159'
854206 Database Server Database 1 Query SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg15x_pictures WHERE ((aid = '159'
854260 Database Server Database 1 Query SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg15x_pictures WHERE ((aid = '159'
854323 Database Server Database 1 Query SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg15x_pictures WHERE ((aid = '84' )
854550 Database Server Database 1 Query SELECT count(pid) AS link_pic_count, max(pid) AS link_last_pid FROM cpg15x_pictures WHERE ((aid
854694 Database Server Database 1 Query SELECT count(pid) AS link_pic_count, max(pid) AS link_last_pid FROM cpg15x_pictures WHERE ((aid
854729 Database Server Database 1 Query SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg15x_pictures WHERE ((aid = '259'
854752 Database Server Database 1 Query SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg15x_pictures WHERE ((aid = '45' )
854770 Database Server Database 1 Query SELECT count(pid) AS link_pic_count, max(pid) AS link_last_pid FROM cpg15x_pictures WHERE ((aid
855043 Database Server Database 1 Query SELECT count(pid) AS link_pic_count, max(pid) AS link_last_pid FROM cpg15x_pictures WHERE ((aid
855388 Database Server Database 1 Query SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg15x_pictures WHERE ((aid = '6' )
855397 Database Server Database 1 Query SELECT * FROM cpg15x_pictures AS p WHERE ((keywords LIKE '%Siracusa%')) AND approved = 'YES' ORD
854285 Database Server Database 2 Query SELECT filepath, filename, url_prefix, pwidth, pheight FROM cpg15x_pictures WHERE ((aid = '159'
854515 Database Server Database 2 Query SELECT count(pid) AS link_pic_count, max(pid) AS link_last_pid FROM cpg15x_pictures WHERE ((aid
855344 Database Server Database 2 Query SELECT count(pid) AS link_pic_count, max(pid) AS link_last_pid FROM cpg15x_pictures WHERE ((aid

[Joe Carver]

First, you need to update your Coppermine.

<!--Coppermine Photo Gallery 1.5.16 (stable)-->

Security fixes and DB query reductions are in the new version.
Note the top of this page.

News: cpg1.5.28 Security release - upgrade mandatory!

Then, look at the plugins/scripts you have installed on the home / main album List pages. They load very much slower than an image page.

[mitko]

Thanks. Will do and inform about results.

[mitko]

Upgraded to latest version of coppermine, follow some instructions on different topics in order to improve performance. Overall the gallery became faster, but still have the database connection problems from time to time. As i'm not a very skilled in programming would be appreciate for step by step instructions for further troubleshooting of this issue.

[gmc]

One recommendation would be to turn on debug in the config (for admin at least) and then load the cpg main page. The debug data can be viewed at the bottom of the page (expand) - and will (among other things) list all queries... With each query - it will give the file, line number, and execution time (information your ISPs data doesn't have).
See where the most time is being spent - and perhaps we can help.
Copy/paste the long query(ies), file and line number, and time into this thread.

You didn't say how large your gallery is... There are some functions that will impact larger galleries if enabled - but again the long running querires should help us pinpoint where.

[mitko]

Hi Greg!
The gallery is around 10k photos, plus i use SMF bridging instead of the gallery users.
Debug from main page attached. The problem with connection appears not all the time, but few times per day.
If you need any info - just let me know. Thanks in advance.

[Αndré]

It seems that you've added keywords to a lot of albums. Please read the docs what the album keyword feature is and how it works, as I'm quite sure you haven't done yet and can simply remove the album keyword(s) from a lot/all albums to improve your gallery performance.

[mitko]

Hi Andre!
Thanks very much, i was really not aware about this functionality, now it is cleared for me. All keywords removed, will inform about results.
Also could somebody please have a look at compatibility issue with Geo plugin in the another topik ? It was perfectly well with old version, but now seems it is not work at all, but all geo-tags in database are okay.

[gmc]

I expect the difference to be significant based on your debug...
An alternative would be to turn off config option 'Show number of linked coins' under album list view...  Could still have keywords - but it won't attempt to count them.

Many long running queries trying to count linked files (line 1000 of index.php).

Code Select Expand

"SELECT count(pid) AS link_pic_count, max(pid) ... [index.php:1000] (1416.9 ms)"
Each one of these scans the pictures table (per EXPLAIN) and with the LIKE criteria for keywords - isn't an indexable column.

I'd expect to see virtually all these numbers come down significantly:

Code Select Expand

==========================
Page (performance)
------------------
Parameter        Current     Peak       
Memory usage     5.54 ÌÁ     5.90 ÌÁ   
Page generation  22781.73 ms 22781.73 ms
Page query time  22681.01 ms 22681.01 ms
Page query count 275         275       
==========================

[mitko]

Well, the result of all steps: site become much and much faster. Main page timing is better. But still from time to time get SQL access problem. I would say it is not so critical anymore, but I asked the hoster to provide SQL query analysis and made some typical operations on site. As the result you can see attached Slow.SQL file - slow SQL queries. Maybe it would help to developers in order to improve performance in future?