Crashing the web host's DB server

[phil1011]

I'm running Coppermine 1.6.03 with PHP Version 7.4.30.

After routinely adding a few captions to my photos, the site got sluggish. Then the DB was disabled and my web host sent me this message:

"The database in question, nnnnn_copp, has been responsible for driving
load or crashing the database daemon multiple times over the last several
days. As it a shared database server, it is negatively affecting other
customers.

Any traffic to the site looks to induce numerous queries such as this one:

| 144754 | nnnnn_3        | 000.00.0.0000:10957  | nnnnn_copp               
| Query   | 5    | Sending data        | SELECT count(pid) AS
link_pic_count, max(pid) AS link_last_pid, max(ctime) AS link_last_upload 
FROM |

Either the site code will need to be adjusted or the query/database
structure will need to be adjusted."

I've been using Coppermine for many years without major problems until now. Any advice would be appreciated.

[ron4mac]

Your host may have changed how they deploy their databases and are trying to reduce use where they can. Doesn't sound like a very robust host.
If appropriate for your site, you can use this template caching plugin to greatly reduce database access.

[phil1011]

Thank you very much for the advice and plug-in.

For the cache folder, do I set it to chmod 755 to make it writable?

On which line do I set the cache time? Line 17?
How long should the cache time be? Seconds, hours, or days?
(I'm not a coder.)

[ron4mac]

Yes, 755 should be okay.
Cache time is set using the plugin manager.

[phil1011]

Thank you very much for your assistance. Hopefully, I will hear fewer complaints from my host.

[ron4mac]

Thank you very much for your assistance. Hopefully, I will hear fewer complaints from my host.

Hmm! I thought you were inquiring regarding photoguide.jp. But that site isn't being cached. Can you share what site it was that your host was complaining about?

[phil1011]

Hmm! I thought you were inquiring regarding photoguide.jp. But that site isn't being cached. Can you share what site it was that your host was complaining about?

Sorry, this is my site https://photoguide.jp/pix/index.php which is being cached I hope.

I'm afraid I have to find a new host. My Coppermine site has over 60,000 images in 1000+ albums. It's slowing down the shared server and they want me to get a dedicated server.

Can anyone recommend a reliable host which can handle a big Coppermine site on a shared server? Or am I asking for too much?

[ron4mac]

That site is not being cached. If you need help I would be glad to setup the caching plugin for you. If you PM me with your hosting (Pair?) login, I will set it up for you.

[phil1011]

I wonder why it's not caching. I followed your instructions. Even with the plugin, would it solve the problem?

My web host sent me this message. Can we resolve this or should I find another host?

======
The things that determine how databases are utilized are how the queries
are written. A single badly written query could hypothetically examine
infinite numbers of rows in a fairly small table. A handful of poorly
optimized queries could clog up the works and cause issues for an entire
shared database server (which is worth mentioning is separate hardware that
only exists to run MySQL).

Unfortunately I'm not a MySQL admin so the best I can offer with this one
database that's been locked is a copy of slow_log entries and hope the
queries mean something to you.

# Time: 2022-08-16T07:37:10.660550Z
# User@Host: MyName_3[MyName_3] @  [xxx.xx.x.xxx]  Id: 2779088
# Query_time: 15.251633  Lock_time: 0.000188 Rows_sent: 1132 
Rows_examined: 73482
use MyName_copp;
SET timestamp=1660635430;
SELECT c.cid, r.aid, COUNT(pid) AS pic_count, MAX(pid) AS last_pid,
MAX(ctime) AS last_upload, depth AS level, lft
       FROM cpg130_categories AS c
       INNER JOIN cpg130_albums AS r ON r.category = c.cid
       INNER JOIN cpg130_pictures AS p ON p.aid = r.aid
       WHERE c.depth >= 0 + 1
       AND approved = 'YES'

--
# Time: 2022-08-16T11:54:25.899764Z
# User@Host: MyName_3[MyName_3] @  [xxx.xx.x.xxx]  Id: 2837084
# Query_time: 21.519374  Lock_time: 0.000195 Rows_sent: 1132 
Rows_examined: 73482
use MyName_copp;
SET timestamp=1660650865;
SELECT c.cid, r.aid, COUNT(pid) AS pic_count, MAX(pid) AS last_pid,
MAX(ctime) AS last_upload, depth AS level, lft
       FROM cpg130_categories AS c
       INNER JOIN cpg130_albums AS r ON r.category = c.cid
       INNER JOIN cpg130_pictures AS p ON p.aid = r.aid
       WHERE c.depth >= 0 + 1
       AND approved = 'YES'

--
# Time: 2022-08-16T13:06:34.260492Z
# User@Host: MyName_3[MyName_3] @  [xxx.xx.x.xxx]  Id: 2853877
# Query_time: 10.179615  Lock_time: 0.000284 Rows_sent: 1  Rows_examined:
70053
use MyName_copp;
SET timestamp=1660655194;
SELECT COUNT(*) FROM cpg130_pictures WHERE ((aid='399'  AND aid NOT IN
(778, 814, 934, 935, 936, 937, 938, 939) ) OR (keywords like '%japanlake%' 
AND aid NOT IN (778, 814, 934, 935, 936, 937, 938, 939) )) AND
approved='YES';

--
# Time: 2022-08-16T15:00:36.193039Z
# User@Host: MyName_3[MyName_3] @  [xxx.xx.x.xxx]  Id: 2876820
# Query_time: 14.899287  Lock_time: 0.000193 Rows_sent: 1132 
Rows_examined: 73482
use MyName_copp;
SET timestamp=1660662036;
SELECT c.cid, r.aid, COUNT(pid) AS pic_count, MAX(pid) AS last_pid,
MAX(ctime) AS last_upload, depth AS level, lft
       FROM cpg130_categories AS c
       INNER JOIN cpg130_albums AS r ON r.category = c.cid
       INNER JOIN cpg130_pictures AS p ON p.aid = r.aid
       WHERE c.depth >= 0 + 1
       AND approved = 'YES'

I know nothing about this particular site or how it's designed and as such
I can't tell why these queries are mostly sending around 1000 rows and
examining around 73k rows in response.

Granted these are only going to be queries that exceeded 10 seconds
run-time as well so it's entirely possible you're getting frequent queries
running 9.9 seconds and ending without being logged.
=========

[ron4mac]

The theme cache plugin would definitely help.

You have a large number of categories and sub-categories. Certain settings in your gallery are causing the home page to grind through a bunch of long database queries. If you don't mind making some config changes that would alter your front page display, these changes would significantly reduce the load on your database:

Number of levels of categories to display = 1
Show first level album thumbnails in categories = Off
Display statistics on index page = Off

And, as I said, I would help get the theme cache to work.

I would say that your host's database setup is not the most robust and there may be better hosts out there that more easily handle the load, but it can be a crap shoot (don't know what you'll get) to find an inexpensive one.

[phill104]

I agree it is a pain finding a decent host these days. I tried Amazon Lightsail for a bit and while it was fast, it was restrictive and support was very minimal. Here in the UK Mythic Beasts has been excellent but Thais is here, you really need a host in your part of the world to make support easier. Best of luck and I look forward to seeing your site running smoothly.

[phil1011]

The theme cache plugin would definitely help.

You have a large number of categories and sub-categories. Certain settings in your gallery are causing the home page to grind through a bunch of long database queries. If you don't mind making some config changes that would alter your front page display, these changes would significantly reduce the load on your database:
And, as I said, I would help get the theme cache to work.

Thank you for your advice. I've changed the settings as advised. Doesn't look as pretty as before, but I'll get used to it.
Will PM you soon about the cache plugin.