Networking databases?

[Guest]

My problem, especially this time of year, is I reach the "max_questions" limit on my database which shuts down the site for an hour before resetting. Bonus, the message to the world is "user- 'dummy_pays_us_money_for_this_lack_of_service' has exceeded the resources for "max_questions" blablahblah" (not my real user name... yet, hmmm may replace _us_ with host company name)

I have cloned a database and made new configuration files to quickly get the site back up but that only helps when I know there is a problem and it is a bit more work to update the spare db..

I can have unlimited databases with my hosting plan so I looked into networking them. My research shows it can be done with access to the environment, don't have that.

If anyone has a solution to that it would be appreciated.

Another solution I thought of would be to automatically switch the configuration file when approaching the limit. I know I can count the db page hits on a page load, so it must be possible to track all hits per hour.  Maybe some    if db_hitcounter='70000'( strftime() -* configuration_file=a) else configuration_file=b sort of thing. And it would need to be able to switch back. Suggestions?

 

[greasemonkey]

I’d be looking for a new host. 

[Guest]

When I went looking in the past, none disclosed the db hits alotted or if there was a limit at all. The hassle of switching at this point isn't worth it to me. I may end up with worse service

[greasemonkey]

@Big Bear I know what you mean. When I first started years ago I had a horrible stretch with hosts.

I found a great one about 4 years ago.... after years of horrible service, down time and stupid limits....

I've moved 3 or 4 times - and the transition has always gone smoothly (from VPS to VPS... not shared hosting).

[MrPhil]

Some hosts count DB queries, others have overall CPU load limits. I don't know if one would be better than the other, or how you can tell if you're close to the limit.

I have seen spam, from time to time, advertising commercial network-based DB services. Your site would still have CPU and bandwidth loads talking to such a service, and you still have to pay for their services, but it might keep your site from going down. I can't tell you whether it would be worth it.

[clustersolutions]

Wow, it sounds like you have perfected your standby DB switchover procedure. Me, I say either a $5 Digital Ocean droplet, an AWS EC2 instance, or anything similar to these will save you boat loads of headaches...I second go find a new hosting provider.

[Guest]

2 hours ago, MrPhil said:

Some hosts count DB queries, others have overall CPU load limits. I don't know if one would be better than the other, or how you can tell if you're close to the limit.

I have seen spam, from time to time, advertising commercial network-based DB services. Your site would still have CPU and bandwidth loads talking to such a service, and you still have to pay for their services, but it might keep your site from going down. I can't tell you whether it would be worth it.

Allegedly, I have unlimited bandwidth and I don't mind paying for service that actually works with no hidden caveats. The trouble is they don't tell you up front what it really includes. I have unlimited data bases but I can only query each one 75K times an hour. I would rather have a few that keep going.

1 hour ago, clustersolutions said:

Wow, it sounds like you have perfected your standby DB switchover procedure. Me, I say either a $5 Digital Ocean droplet, an AWS EC2 instance, or anything similar to these will save you boat loads of headaches...I second go find a new hosting provider.

I have looked into clouds but my head has been in them so long I just can't grasp it. Are you saying run my DB in the cloud? Would the site stay secure with an external db address?

[clustersolutions]

1 hour ago, Big Bear said:

I have looked into clouds but my head has been in them so long I just can't grasp it. Are you saying run my DB in the cloud? Would the site stay secure with an external db address?

I meant that by you move your whole stack out of your current hosting provider.  I can't see how you could survive an DoS attack with them. I personally prefer Centos, but Ubuntu works too. $5 LAMP stack:

https://www.digitalocean.com/products/one-click-apps/lamp/

It should be more than enough to power your OSC site, and with the $5 instance, you will need to setup an 1-2G swap...or just pay for the $10 or $20 instance.

https://www.digitalocean.com/community/tutorials/how-to-add-swap-space-on-ubuntu-16-04

Oh, I also had started using this free SSL cert...real nice and you can set it up for auto renewal.

https://www.digitalocean.com/community/tutorials/how-to-secure-apache-with-let-s-encrypt-on-ubuntu-14-04

Now, the hard way would be to run the DB over the network. You can secure the DB connection using SSL, but why drive a new car with a beater engine? Plus you will have more forms to fill out for PCI compliance.

 

 

 

[♥bruyndoncx]

I assume you have somekind of caching going for eg configuration and other static parts of your website so you don't hit the database that often ?

[Jack_mcs]

Big Bear - There is a code change that has been around for years that will sometimes work. The idea is to create multiple users for the database and then randomly choose a user for connections. Since some of the measurements are per user, this gets around some of the failures. See here.

But the reason a site reaches the limit, in most cases, is due to the data skimmers and hackers that are on the site, especially at this time of year. If you know how to read your access log, you will almost certainly see this. I suggest you install View Counter. It will allow you to see that extra traffic and stop it. Most shop owners would be surprised at how much traffic is on their site that they are not aware of.

As for your host, unlimited bandwidth and/or disk space is a marketing ploy and never true. The conditions are usually mentioned in their TOS but most shop owners don't think about such things when moving to host. It only becomes an issue when there is a problem, as you have found out.

[Guest]

6 hours ago, bruyndoncx said:

I assume you have somekind of caching going for eg configuration and other static parts of your website so you don't hit the database that often ?

I have been caching since Chemo's days

5 hours ago, Jack_mcs said:

Big Bear - There is a code change that has been around for years that will sometimes work. The idea is to create multiple users for the database and then randomly choose a user for connections. Since some of the measurements are per user, this gets around some of the failures. See here.

But the reason a site reaches the limit, in most cases, is due to the data skimmers and hackers that are on the site, especially at this time of year. If you know how to read your access log, you will almost certainly see this. I suggest you install View Counter. It will allow you to see that extra traffic and stop it. Most shop owners would be surprised at how much traffic is on their site that they are not aware of.

As for your host, unlimited bandwidth and/or disk space is a marketing ploy and never true. The conditions are usually mentioned in their TOS but most shop owners don't think about such things when moving to host. It only becomes an issue when there is a problem, as you have found out.

I will try the multi user code.

I used View Counter in my last remodel before switching to BS, I modified Linuxuk HTTP Error to allow me to track everything and ban as needed, after the fact. Most attempts are to get into my blog.

I am about done with this website stuff so I won't go looking for a new host. Over the past several years I have watched the traffic to my sites decrease and other venues increase. I believe the cause, at least in part, is that the mobile users don't surf as much as DT users do/did and mostly shop the profit eating sites.

thanks all!

[MrPhil]

On 12/4/2017 at 10:04 PM, Big Bear said:

Allegedly, I have unlimited bandwidth and I don't mind paying for service that actually works with no hidden caveats. The trouble is they don't tell you up front what it really includes. I have unlimited data bases but I can only query each one 75K times an hour. I would rather have a few that keep going.

Yeah, they'll get you one way or another. There's no such thing as "unlimited" anything. In the end, it often comes down to measuring CPU load on your account, and cutting you off at some point (if you're sharing a server with others).

On 12/5/2017 at 8:44 AM, Jack_mcs said:

Big Bear - There is a code change that has been around for years that will sometimes work. The idea is to create multiple users for the database and then randomly choose a user for connections. Since some of the measurements are per user, this gets around some of the failures. See here.

This might work, but beware: from the point of view of your host, it's cheating to get around their limits. If all your users are under the same account, they may still be able to limit you, so don't expect miracles.

I like the idea of blocking skimmers and other such vermin as early as possible, to reduce use of resources. Since you don't want to block legitimate search engines such as Google, you may have to frequently check your logs and manually update your IP block (deny) list. You might talk with your host about whether there's any way to get blocking done even further upstream, so they don't eat up any of your resources, but don't be surprised if a host refuses, as this will affect other users.

[Jack_mcs]

It is best to block unwanted IP's at the server level when possible, though that requires more of the shop owners time. The latest version of View Counter can block IP's automatically, if they enter into the trap directory - and most will. The auto-blocking code can tell if the IP belongs to a search bot so it is safe to use.

[Guest]

1 hour ago, MrPhil said:

This might work, but beware: from the point of view of your host, it's cheating to get around their limits. If all your users are under the same account, they may still be able to limit you, so don't expect miracles.

 Since you don't want to block legitimate search engines such as Google, you may have to frequently check your logs and manually update your IP block (deny) list. You might talk with your host about whether there's any way to get blocking done even further upstream, so they don't eat up any of your resources, but don't be surprised if a host refuses, as this will affect other users.

From the DA's point of view, advertising unlimited anything and putting hidden limits on it's use may be construed as illegal activity, IDK

55 minutes ago, Jack_mcs said:

The latest version of View Counter can block IP's automatically, if they enter into the trap directory - and most will. The auto-blocking code can tell if the IP belongs to a search bot so it is safe to use.

With Linuxuk HTTP Error, search engines show in red and no ban button is available. It has an auto ban feature but I prefer to see what they were trying to do.

I liked View counter, it just slowed down my admin to much.

zoominfobot was nosing around yesterday. Added it to my bad bots list and banned those IP's

[MrPhil]

21 hours ago, Big Bear said:

From the DA's point of view, advertising unlimited anything and putting hidden limits on it's use may be construed as illegal activity, IDK

I'm sure there's something in the Terms of Service fine print that they could point to in order to justify their actions. It may not be well defined, or in plain English, but evidently their lawyers feel it would hold up in court. They'd be fools not to have such language.