I have over 70,000 images and hundreds of albums. Chronic problem with my host has been that the overloaded db has been impacting the host's server. So they disabled my coppermine db. Cannot access anything anymore.
They want me to switch to VPS hosting. Do you think their cheapest VPS plan would resolve my problem? Or if you have any suggestions for a solution, let me know.
https://www.pair.com/vps-hosting/
A few years ago, I suggested to you that you try a theme caching plugin (https://coppermine-gallery.com/forum/index.php/topic,80231.0.html). If your site is mainly public facing (users don't log in), the plugin will greatly reduce total database access. Many gallery pages are cached and the database is only accessed for them at whatever interval you set.
If caching doesn't work for you ... yes, their cheapest VPS plan should provide all you would need.
Host informed me that most of the requests are from bots. They recommended Cloudflare so I've signed up and changed the nameservers. It should reduce unwanted traffic. Host re-enabled the coppermine db. Hopefully this will work in the long term.
In addition to what ron4mac has suggested and what you have done so far, turninig off logging detailed Hit Statistics will take a large load off of your DB. Bot/crawler traffic has been hugely inreasing as of late.
I can confirm this major issue; it's probably due to AI bots, that in the last year are incredible increasing.
Although I've installed the Theme Cache (theme_cache) plugin: v1.1 and restricted the display of full-size photos to registered users only, I'm still getting the following errors:
Fri, 06 Nov 26 22:23:45 +0100 – Unable to connect to database: 1226: User 'XYZ' has exceeded the 'max_user_connections' resource (current value: 9)
This is becoming a huge problem because it brings the whole site to a standstill. I've actually had to buy a second database just for the three Coppermine galleries I have on the site, but I remain convinced that there's some sort of vulnerability through which attackers or bots are managing to run queries even without being logged in.
Otherwise, there's no explanation for the problem – the album views are all cached?!?
I've also tried making countless changes to the code using ChatGPT (searches restricted to registered users, other changes to the .htaccess file) but without resolving anything.