US/UK Problem

to6y · 19th July 2008

hi guys

i run a webshop and my customers are roughly split 50% in us and 50% in uk

currently i have a server in softlayer's dallas dc

however i am considering also getting a uk based server as i would like to speed up load times for my uk customers

the problem i seem to have is that even though i have two websites...shop.com and shop.co.uk, they both read/write to the same mysql database

i hear that if i moved shop.co.uk php files to uk, the website loading time would actually increase as having the php files here and the sql files in the states is a very bad idea...is this true?

i do not want to have two separate databases as i have thousands of products and the time it would take copying back and forwards just wont work for me.

does anybody have a solution for me?...i did consider sql clusters a few months ago (when i last thought about this) but i think back then i decided they werent going to help me...cant remember what the problem with them was!!

any help greatly appreciated

many thanks

M8INTERNET · 20th July 2008

You can read/write to one MySQL database
Firstly you need the IP address of the server using the MySQL database
On the remote server, in config file replace "localhost" with the IP address
Works fine for me

to6y · 20th July 2008

recognise that is it possible to read/write to database on different server

i am told though that having php here (uk) and sql there (us) is slower than having both php and sql there

othellotech · 20th July 2008

Quote:

Originally Posted by to6y

i am told though that having php here (uk) and sql there (us) is slower than having both php and sql there

It depends on how well written the code is and how many queries needed to generate the page, but no, in general it will be quicker.

pipeten · 20th July 2008

operating your database and web nodes without replication, security or optimisation in geographically diverse location?!?

you could consider (at this early stage):
sort out a secure tunnel between your sites.
then create a copy of the database to the secondary site.
modify the administration and front-end sql updates and inserts to both sites.
setup rsync between the two web nodes for web data.
modify all sql selects to use the local database (with optional failover off-site).

if you're not keeping your UK customers exclusively on the .co.uk then you'll need to look at geolocation on either the code or dns level.

cdn for images and other media could also be an option.

alot of it depends on how far you want to take it. obviously you need the web node local to take proper advantage of localised search results.

irrespective, to have an e-commerce shop with PHP in one location and SQL in another and no security wrapper is crazy talk

goscombtech · 20th July 2008

Quote:

Originally Posted by pipeten

modify the administration and front-end sql updates and inserts to both sites.
setup rsync between the two web nodes for web data.
modify all sql selects to use the local database (with optional failover off-site).

why? Just use NDBCluster... its what its there for. If you are not doing a lot of writes you could even set up a master/master replication system.

BurtyB · 20th July 2008

I have a mysql master-master setup for a clients sites across UK/US servers and it works well for them. I also rsync the html/php/etc files between the servers to allow us to move the sites between the servers if needed.

In my tests running mysql in the US and querying from the UK does harm the sites performance even if it only does a couple of queries you might be looking at a an extra ~150ms which quickly adds up if you need to do a fair few queries to create a page.

ChrisB.

pipeten · 20th July 2008

Quote:

Originally Posted by goscombtech

why? Just use NDBCluster... its what its there for

Because the OP gave the impression that he'd tried/considered clustering already and requested alternate solutions?

Quote:

i did consider sql clusters a few months ago (when i last thought about this) but i think back then i decided they werent going to help me

Dhosting · 20th July 2008

I'd go with Master to Master replication works very well even on windows

burble · 20th July 2008

A simple first step would be to shift your media (css, js, images etc) off onto a subdomain, then get a server in the uk to serve the media.shop.co.uk, leaving the www.shop.co.uk pointing at the PHP and DB in the US.

As I imagine most of the download time will be on the media content rather than the page itself, it should help boost performance for UK visitors. This way you'll also have a foot in the UK hosting market, which will let you do real-world performance tests to find your best option for serving your PHP from the uk server too.

JamesSykes · 21st July 2008

As above, if you split your media files that will speed up the site for your UK visitors.

Also, assuming you have a good connection between your UK DC and your US DC you could run with a single sql as long as you dont have a huge number of queries going on.

to6y · 21st July 2008

thanks guys for all your comments....

my plan is:
1. more media files to uk
2. run tests to see effect of moving php to uk and leaving sql in us
3. if 2 is no good, will research more into setting up sql cluster

i have done no specific research but i seem to have 2 problems to do with page loading:
a. currently "background" page loads in 1 second, then images load for next 10 seconds
b. for normal browsing of site, everything is fine but after customers adds to cart then checkout each page after that has a noticible slow down...perhaps due to https, perhaps due to move sql queries (work out what shipping should be, write to database, check stock levels etc)

anyway, i guess...
...problem a will be solved by doing 1
...problem b will be solved by doing 2 and/or 3

THANKS AGAIN FOR ALL REPLIES VERY HELPFUL

Dhosting · 21st July 2008

Quote:

Originally Posted by to6y

b. for normal browsing of site, everything is fine but after customers adds to cart then checkout each page after that has a noticible slow down...perhaps due to https, perhaps due to move sql queries (work out what shipping should be, write to database, check stock levels etc)

Sounds like poor code either php or sql.

Check the mysql server load when your using those pages.

to6y · 21st July 2008

again, i think you have hit the nail on the head...

during day when server does nothing other than run website all is fine (server load 0.5-1.0), at night when server is backing up and running website the load on server increases...obviously mainly due to the backup BUT server guys say that we notice server load more at night due to our shop database being too large! we are no amazon so this database is not large...currently about 150 MiB (if that means anything to anybody)

when i speak to php guys about server load/checkout speed, they say:
1. that we already have a cron that automatically optimises database weekly
2. they are continuously changing/improving code so i guess number of read/write requests will be changed/improved...HOWEVER...they say that we have very old/messy code hacked together over years of work and to see any noticible changes we would have to rewrite everything from scratch...which would take them at least a year!!

as i am way to tight to pay them to re-write everythig again, i guess i will just have to get some more ram!!

Dhosting · 21st July 2008

Enable the mysql slow query log.

It will output the queries that take a long time.

Also what PHP version are you using, i would hope 5.2?