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