Best way to serve multiple continents(hosting)

[Mynight]

Hello please excuse my ignorance,

Long story short the majority of my users are either France or US based and my server is located in London. I've noticed increasing response/load times for both French and US users which is a problem. I've also hit the point that shared hosting isn't quite cutting it for me due to database work. I appear to be hitting the limit on concurrent users trying to update the database.

Based off my googling I have the options of;

1)Rent a server in France and in US and then a load balancer to direct users to the correct geo. Considerable cost increase from shared hosting and I presume response will still be limited by where the load balancer is located?

2)Using a CDN. However as I understand it these are for static pages not databases nor advertising JS. This would presumably still result in slow response/load times.

So I'm completely stuck and opting for number 3 and hoping someone here can point me in the right direction.

 

[On Holiday]

Assuming you're using cPanel..

What limits are you actually reaching (i.e. CPU/RAM/IO/MySQL IO), you should see this in cPanel unless your hosting provider has hidden it. Your MySQL usage could be seriously gimped by the provider too which might be playing into it.

No harm in trying a CDN, it'll be free unless you've got seriously heavy usage, CloudFlare is the obvious choice but not the best, QUIC.Cloud is also free for basic usage and fresh out of beta by the LiteSpeed team, almost every shared hosting provider *should* run LiteSpeed now given the performance benefits it offers to their customers and the load reduction on their servers.

Users on my servers get 10x the default QUIC.Cloud credits as a result of my servers running LiteSpeed so you could well find a decent solution just by moving to a decent shared host using LiteSpeed if your existing one isn't already.

Additionally, New York is a good middle point for latency for general US and UK usage, i.e. 70ms from West Coast to New York and 70ms from New York to UK.

 

[Mynight]

I am using cpanel. I'm not confident enough to run security on my own servers yet. I'm happy to code what I need but would prefer the lowend systems to be secured by someone in the know. SPEED/PMEM/IO/EP/NPROC/IOPS all well below the level I pay for even at peak usage.

It's the concurrent users to the mysql, doesn't actually show up in the cpanel hosting sadly but it starts returning errors from PHP saying max_concurrent_user exceeded. Supposedly this is set to 100 by Krystal and only way I can go any higher is to go VPS.

Unfortunately Krystal VPS isn't that competitive with Stablepoint so considering moving hosts now.


I have now set up Cloudflare which is helping the EU users somewhat. However the US is still pretty slow. Unfortunately due to the way I've set my app up the initial connection always has to go to source to check against database which Cloudflare cannot intercept.

Any suggestions on decent shared hosting as I was under the impression they were one of the best(could well just be due to my traffic locations).

 

[On Holiday]

I am using cpanel. I'm not confident enough to run security on my own servers yet. I'm happy to code what I need but would prefer the lowend systems to be secured by someone in the know. SPEED/PMEM/IO/EP/NPROC/IOPS all well below the level I pay for even at peak usage.

It's the concurrent users to the mysql, doesn't actually show up in the cpanel hosting sadly but it starts returning errors from PHP saying max_concurrent_user exceeded. Supposedly this is set to 100 by Krystal and only way I can go any higher is to go VPS.

Unfortunately Krystal VPS isn't that competitive with Stablepoint so considering moving hosts now.


I have now set up Cloudflare which is helping the EU users somewhat. However the US is still pretty slow. Unfortunately due to the way I've set my app up the initial connection always has to go to source to check against database which Cloudflare cannot intercept.

Any suggestions on decent shared hosting as I was under the impression they were one of the best(could well just be due to my traffic locations).

I see, 100 is actually in the upper end of what you'll find with shared hosts - most being around the 25 mark. I know of a couple that go up to 300 for example but their service is generally quite poor.

The limits are generally set globally which is why you'll be pushed to a VPS of your own but there are ways to allow a user to have more resources than the global limit

You could try to find a host that will be more lenient with the connection limit providing your queries aren't actually exceeding your allocated resources / causing issues for others on the same server.

Without knowing what your database work is specifically, 100 concurrent queries is quite high in itself, have you already looked at optimizing it in the way of joining any queries at all etc?

 

[Mynight]

I see, 100 is actually in the upper end of what you'll find with shared hosts - most being around the 25 mark.

The limits are generally set globally which is why you'll be pushed to a VPS of your own but there are ways to allow a user to have more resources than the global limit

You could try to find a host that will be more lenient with the connection limit providing your queries aren't actually exceeding your allocated resources / causing issues for others on the same server.

Without knowing what your database work is specifically, 100 concurrent queries is quite high in itself, have you already looked at optimizing it in the way of joining any queries at all etc?

Yeah I figured 100 would have been enough to be honest. Max at any time should be around the 30 a second mark. I do appreciate I’m at the high end of what a shared host is for annoyingly I’m not really in the need of a full VPS. I need to find something in the middle ground.

Sadly I don’t think Krystal can accommodate me on their system. I’ve offered to pay more but the advice is understandably get a dev to optimise or upgrade to VPS.


Specifically the database is search for a row with matching date,country,cpm and update count otherwise insert. Couple of days of data hit 3.8mb 92,000 rows. Indexed and innodb so not sure I can eek much more performance out of that. I could look into if there’s a way of running the insert/update in the mysql select rather than bouncing back to php to run the second statement (Guess on that basis it’s around 60 connections per second). I could look into splitting the connections across databases rather than all on one table but I feel as though that’s just bypassing the issue and taking the **** with using their shared host.

Somewhat leaning towards just biting the bullet on a VPS from stablepoint.

Edit: Realised I can knock out a step and instead of Selecting first just run the update statement and check for success else insert. Apparently there is more performance I can get from it...

 

[Itchytrigg]

Look into on duplicate key update. If you set your unique keys right you’ll only need that one insert statement and let the db do the work to prevent duplicate entries.

 

[Mynight]

Look into on duplicate key update. If you set your unique keys right you’ll only need that one insert statement and let the db do the work to prevent duplicate entries.

Thanks for the idea. I have been struggling to think of a decent unique key. The data I'm collecting doesn't really lend well to a unique key.

I believe the main issue could have been caused by myisam table locking. Each user would have to wait for the previous to finish before executing leading to a long backlog. I've since swapped to innodb to hopefully reduce that somewhat.

On Holiday has kindly set me up with hosting which should be more than capable of what I need.