Page 1 of 2 12 LastLast
Results 1 to 25 of 26
  1. #1

    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?

  2. #2
    Join Date
    Nov 2007
    Location
    India, USA and Amsterdam
    Posts
    2,581

  3. #3
    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.

  4. #4
    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?

  5. #5
    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

  6. #6
    Quote Originally Posted by chennaihomie View Post
    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?
    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.

    Quote Originally Posted by lynxus View Post
    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 )
    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?

  7. #7
    Join Date
    Jul 2009
    Location
    UK
    Posts
    1,312
    Quote Originally Posted by |Jordan| View Post
    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?

    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

  8. #8
    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).

  9. #9
    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.

  10. #10
    Generally speaking - what uses more resources: the web server, PHP or MySQL ?

  11. #11
    Join Date
    Apr 2000
    Location
    Brisbane, Australia
    Posts
    2,602
    Quote Originally Posted by |Jordan| View Post
    Generally speaking - what uses more resources: the web server, PHP or MySQL ?
    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

  12. #12
    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.

  13. #13
    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?

  14. #14
    Join Date
    Aug 2008
    Posts
    176
    Quote Originally Posted by eva2000 View Post
    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.
    What kind of RAM and disk drive setup did those servers have?

  15. #15
    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

    1. single Xeon W3550 3.06Ghz
    2. 6GB memory
    3. 146GB SAS 15K
    4. Litespeed web server


    mysql server

    1. dual xeon e5520 2.26ghz
    2. 12GB memory
    3. 6x 146GB SAS 15K Raid 10
    4. 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

  16. #16
    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.

  17. #17
    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.

  18. #18
    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).

  19. #19
    Join Date
    May 2008
    Posts
    117
    Quote Originally Posted by |Jordan| View Post
    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).
    Nginx will replace LSWS but .htaccess files wont work with Nginx.

  20. #20
    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.

  21. #21
    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.

  22. #22
    Thanks everyone for the tips. Especially eva2000, i based my network on the specs of those servers.

  23. #23
    Have you considered multi-master? It works but needs baby sitting. Or even postgres sql which has much more mature clustering and failover
    Jay

  24. #24
    Multi master looks like it has too many problems. I cant use PostGres because vbulletin isint compatible with it.

  25. #25
    Quote Originally Posted by |Jordan| View Post
    Multi master looks like it has too many problems. I cant use PostGres because vbulletin isint compatible with it.
    If you know how to run it, it works, as i said it requires TLC.
    Jay

Page 1 of 2 12 LastLast

Similar Threads

  1. Replies: 0
    Last Post: 04-29-2005, 06:06 AM
  2. Multi Server MySQL
    By VolkNet in forum Programming Discussion
    Replies: 1
    Last Post: 08-12-2004, 04:34 PM
  3. Replies: 0
    Last Post: 06-17-2004, 06:08 PM
  4. Replies: 0
    Last Post: 05-09-2004, 12:54 PM
  5. I need help setting up a JSP/MySQL development enviroment.
    By helpme in forum Hosting Security and Technology
    Replies: 0
    Last Post: 04-18-2002, 04:21 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •