Crashing the web host's DB server Crashing the web host's DB server
 

News:

cpg1.5.48 Security release - upgrade mandatory!
The Coppermine development team is releasing a security update for Coppermine in order to counter a recently discovered vulnerability. It is important that all users who run version cpg1.5.46 or older update to this latest version as soon as possible.
[more]

Main Menu

Crashing the web host's DB server

Started by phil1011, July 22, 2022, 05:45:03 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

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

Quote from: phil1011 on July 23, 2022, 09:52:47 AM
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

Quote from: ron4mac on July 23, 2022, 01:37:24 PM
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:
QuoteNumber 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.
It is a mistake to think you can solve any major problems just with potatoes.

phil1011

Quote from: ron4mac on August 17, 2022, 02:09:46 PM
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.