Results 1 to 25 of 26
-
05-14-2011, 03:09 AM #1Web Hosting Guru
- Join Date
- Mar 2005
- Posts
- 304
MySQL in a multi server enviroment?
Im going to get a Dedicated Server and its going to be running a PHP/MySQL website.
When traffic gets too high, im going to mirror the site with cloud hosting (LiquidWeb) but i have a problem with MySQL that i dont know how to fix.
I want the clouds to be able to write to my MySQL database just like the dedicated server - how do i go about this?
MySQL Clustering is out of the question because it costs too much. Master/Slave wont work either since its only in one direction. Federated also wont work because i dont need that functionality.
That leaves me with Master/Master. But i heard there could be some problems with replication if one of the masters goes down.
What are my options? Am i totally screwed?
-
05-14-2011, 03:31 AM #2The Guru!
- Join Date
- Nov 2007
- Location
- India, USA and Amsterdam
- Posts
- 2,581
Are you running DB server and web server separately now?
-
05-14-2011, 01:13 PM #3Web Hosting Guru
- Join Date
- Mar 2005
- Posts
- 304
I havent gotten the server yet (im getting it mid-june), but im planning the network now to reduce stress when it comes to launch.
I'm going to start out with the web server running PHP and MySQL. Then when traffic gets too high, im getting a few cloud servers and they too will run PHP and MySQL.
If the site doesnt perform well with this setup, then ill do a dedicated web server for PHP and a dedicated web server for MySQL.
-
05-14-2011, 01:41 PM #4The Guru!
- Join Date
- Nov 2007
- Location
- India, USA and Amsterdam
- Posts
- 2,581
Offloading Mysql to a powerful server and running multiple http servers/cloud would be generally sufficient in most cases. How much traffic you are expecting?
-
05-14-2011, 01:56 PM #5Lord of live chats
- Join Date
- Jul 2009
- Location
- UK
- Posts
- 1,312
As long as you keep the Mysql server close ( so you get low latency ) then just use one beefy mysql box.
Then in your php, Rather than connect(localhost,myuser,password) etc..
Use
connect (10.0.0.1,myuser,password)
You would need to setup a new user and allow connect/select/update/etc in your mysql servers permissions table.
This way you can have 3 servers...
2 - frontend webservers.
1 - Mysql server that the webservers read from.
Clustering mysql requires more servers
2 for mysql + 1 for cluster management (3 server just for mysql )
So one big very beefy mysql box should be good.
Heck. if you can get a box with lots of ram, Run the DB in ram ( or at least busy / quick tables )Live Chat Support Software for your Business website - IMsupporting.com
-
05-14-2011, 02:38 PM #6Web Hosting Guru
- Join Date
- Mar 2005
- Posts
- 304
Im going to post the site on a few very popular websites/blogs, so im expecting anywhere from 1000 to a few million hits in traffic and most of that traffic will be converted into regular visits.
What sort of box do you suggest so that i dont have to get additional database servers so soon? How much ram should i start out with?
-
05-14-2011, 02:41 PM #7Lord of live chats
- Join Date
- Jul 2009
- Location
- UK
- Posts
- 1,312
I honestly couldn't say.
I suppose it all depends on the kind of queries going to the mysql server, how many of them ( per second ) and how big the database actually is / how big the tables are.. ( and how optimized your queries / indexes are )
Id personally go with a supplier that allows easy upgrades..
Ie: go for a server with x amount of ram and cpu and then upgrade as needed.Live Chat Support Software for your Business website - IMsupporting.com
-
05-14-2011, 02:45 PM #8Web Hosting Guru
- Join Date
- Mar 2005
- Posts
- 304
My site is going to be made up of two systems:
The same forum software that powers WebHostingTalk (theres 10 to 30 queries per page on my site)
And a Stats tracking system (i have no idea how many queries this will be yet as its still being developed).
The database is currently tiny as the site isint live yet (30MB).
-
05-14-2011, 03:01 PM #9The Guru!
- Join Date
- Nov 2007
- Location
- India, USA and Amsterdam
- Posts
- 2,581
Get yourself a server with SSD and good amount of RAM(12,24gb). This would be very much sufficient to start with and as you grow, you can increase your RAM. SSD have shown very good improvement in mysql performance due to very fast I/O.
-
05-14-2011, 03:56 PM #10Web Hosting Guru
- Join Date
- Mar 2005
- Posts
- 304
Generally speaking - what uses more resources: the web server, PHP or MySQL ?
-
05-14-2011, 04:28 PM #11Web Hosting Master
- Join Date
- Apr 2000
- Location
- Brisbane, Australia
- Posts
- 2,602
For vB 4 for high traffic site or wordpress, php will use more as there's alot more potential for poorly coded addons etc to make their way into the scripts but that's not how you should look at it. You're over complicating your setup to be honest.
I've worked with vB, drupal and wordpress based sites handling 8+ million unique ip visitors per month and over 80+ million page views per month just on a 2 or 3 dedicated server setup, dedicated web + dedicated mysql server or 2x load balanced web servers + dedicated mysql server - where setups push 4,000+ queries/second. Entire setup doesn't even break a sweat with cpu loads less than 1.5 to 3.0 on dual quad core + HT = 16 cpu thread based servers.
Probably best to offload stats script to a separate server/mysql instance or 3rd party service would take a lot of load off management and server resources.: CentminMod.com Nginx Installer Nginx 1.25, PHP-FPM, MariaDB 10 CentOS (AlmaLinux/Rocky testing)
: Centmin Mod Latest Beta Nginx HTTP/2 HTTPS & HTTP/3 QUIC HTTPS supports TLS 1.3 via OpenSSL 1.1.1/3.0/3.1 or BoringSSL or QuicTLS OpenSSL
: Nginx & PHP-FPM Benchmarks: Centmin Mod vs EasyEngine vs Webinoly vs VestaCP vs OneInStack
-
05-14-2011, 04:32 PM #12The Guru!
- Join Date
- Nov 2007
- Location
- India, USA and Amsterdam
- Posts
- 2,581
I would agree with eva2000. Infact if you use web servers like Litespeed, you can even still handle php load better.
-
05-14-2011, 04:50 PM #13Web Hosting Guru
- Join Date
- Mar 2005
- Posts
- 304
Eva! You helped me with stuff on the vb forums too ^_^
Yeah, im going with the following setup:
2 Web Servers for VB (Load Balanced)
1 MySQL Server
2 Web Servers for Stats System (Load Balanced)
1 MySQL Server
If i turn the MySQL servers into Masters and put a Slave SQL server on every Web Server - will that work better? Or will MySQL Slaves on the web servers put too much strain?
Also Eva, what were the hardware stats of those servers you mentioned that had 8 million visitors?
-
05-14-2011, 04:52 PM #14Junior Guru
- Join Date
- Aug 2008
- Posts
- 176
-
05-14-2011, 05:01 PM #15Web Hosting Master
- Join Date
- Apr 2000
- Location
- Brisbane, Australia
- Posts
- 2,602
Honestly any reason for own stats system, you can save a bundle or headache and time and effort spent if you go 3rd party unless you are planning to start your own commercial stats service offering ?
each web server
- single Xeon W3550 3.06Ghz
- 6GB memory
- 146GB SAS 15K
- Litespeed web server
mysql server
- dual xeon e5520 2.26ghz
- 12GB memory
- 6x 146GB SAS 15K Raid 10
- Adaptec 5805 512MB cache BBU enabled
All server's backed up with R1Soft CDP/MySQL
The site could run entire traffic on just a single web server + db server as we had to do that for 1 week while the 2nd web server was being setup. This was way before I got onto Varnish cache, Litespeed cache and better performing MySQL forks like MariaDB 5.2.x. So I'm sure performance could be improved even more.: CentminMod.com Nginx Installer Nginx 1.25, PHP-FPM, MariaDB 10 CentOS (AlmaLinux/Rocky testing)
: Centmin Mod Latest Beta Nginx HTTP/2 HTTPS & HTTP/3 QUIC HTTPS supports TLS 1.3 via OpenSSL 1.1.1/3.0/3.1 or BoringSSL or QuicTLS OpenSSL
: Nginx & PHP-FPM Benchmarks: Centmin Mod vs EasyEngine vs Webinoly vs VestaCP vs OneInStack
-
05-14-2011, 05:28 PM #16Web Hosting Guru
- Join Date
- Mar 2005
- Posts
- 304
Its not those kind of stats. Its Player stats from game servers and it needs to be custom coded for what im doing.
Litespeed is currently out of my budget range. What webserver do you recommend it its place? Nginx ?
Also what cache do you recommend?Last edited by Think Tank Networks; 05-14-2011 at 05:39 PM.
-
05-14-2011, 05:30 PM #17Junior Guru Wannabe
- Join Date
- Dec 2007
- Location
- Proxy
- Posts
- 44
As others suggested, 1 quad core server with LSWS and 1 will mysql should do for most of the requirement. If you get this server from a managed provider, they will take care of scalability in future by setting up multiple db servers with db replication.
I am Mr.WebHostingTalk.
-
05-14-2011, 09:07 PM #18Web Hosting Guru
- Join Date
- Mar 2005
- Posts
- 304
I cant afford LSWS right now though. I read some articles that say Nginx is even faster than LSWS (obviously the article on LSWS blog about Nginx will favor LSWS).
-
05-15-2011, 08:31 AM #19WHT Addict
- Join Date
- May 2008
- Posts
- 117
-
05-15-2011, 02:18 PM #20Web Hosting Guru
- Join Date
- Mar 2005
- Posts
- 304
Yeah i know. The rewrite rules for Nginx are a pain to write, but once they're set - you never have to worry about them.
-
05-16-2011, 10:23 AM #21Web Hosting Guru
- Join Date
- Mar 2005
- Posts
- 304
Also, after some more research i finally decided on Master/Slave setup. Though there are some limitation associated with it, its all i can currently do within my budget.
-
05-17-2011, 09:35 PM #22Web Hosting Guru
- Join Date
- Mar 2005
- Posts
- 304
Thanks everyone for the tips. Especially eva2000, i based my network on the specs of those servers.
-
05-17-2011, 10:07 PM #23Doh!!
- Join Date
- Jan 2001
- Location
- NJ
- Posts
- 2,343
Have you considered multi-master? It works but needs baby sitting. Or even postgres sql which has much more mature clustering and failover
Jay
-
05-17-2011, 11:21 PM #24Web Hosting Guru
- Join Date
- Mar 2005
- Posts
- 304
Multi master looks like it has too many problems. I cant use PostGres because vbulletin isint compatible with it.
-
05-17-2011, 11:23 PM #25Doh!!
- Join Date
- Jan 2001
- Location
- NJ
- Posts
- 2,343
Similar Threads
-
Additional 10% off for WHT Members !, Multi Domain, Multi Server, HSphere Hosting
By CRego3D in forum Shared Hosting OffersReplies: 0Last Post: 04-29-2005, 06:06 AM -
Multi Server MySQL
By VolkNet in forum Programming DiscussionReplies: 1Last Post: 08-12-2004, 04:34 PM -
Additional 10% off for WHT Members !, Multi Domain, Multi Server, Hsphere Hosting
By CRego3D in forum Shared Hosting OffersReplies: 0Last Post: 06-17-2004, 06:08 PM -
Additional 10% off for WHT Members !, Multi Domain, Multi Server, Hsphere Hosting.
By CRego3D in forum Shared Hosting OffersReplies: 0Last Post: 05-09-2004, 12:54 PM -
I need help setting up a JSP/MySQL development enviroment.
By helpme in forum Hosting Security and TechnologyReplies: 0Last Post: 04-18-2002, 04:21 PM