If I have a website hosted on linux server. Assuming the website uses PHP and MySQL database. If I needed one day to add another server in order to support the performance of the site, what should I do in that time? Do I need to purshase non-free edition of MySQL? How can I make MySQL database stores data between both servers? And how can I connect the two servers?
1
-
Try searching for load balancing.– OrblingMar 30, 2011 at 8:05
Add a comment
|
5 Answers
Assuming that we are talking about a production system that should eventually scale into infinity, these are the rules:
- Performance must be measured.
- Do not expect scalability without design.
Make sure you understand what is going on. I assume this is a LAMP system on a Linux box. Some tools to help you measure performance: atop, collectd, iostat, vmstat, iptraf, netacct. If you are using an Apache frontend, turn on logging of request roundtrip (see LogFormat %D). In mysql turn on logging of slow queries and set the threshold low (e.g. 5 seconds) so you can catch the problematic queries before your users notice them. Use mysqlreport to see if yuo should change your mysql configuration. Insert log statements in your PHP code that triggers if relevant parts take longer than acceptable to complete. Use e.g. logwatch to monitor logs for such lines and notify you when they occur.
Once you understand which parts of your system produces load, start looking at your design to make it more modular. Then you create tools to measure the performance of each module in isolation to make sure that it stands up to your performance requirements.
Do not worry about performance of your system until you have measured and found a problem.
If you should measure and find the database your bottleneck your first step should be to move the database to a separate server from your PHP installation. That will give you a lot of extra headroom. Unless you are dealing with a very large database, or very expensive queries or a very high amount of users a single server is in most cases enough. Just make sure you have enough ram to store the whole database in memory. Single server SQL installations are much easier to deal with, and will typically give you better performance for your money.
-
Will it be easy to move the database to another server? Will I lose some data? How much time should I stop my site in this case in order to complete this process?– GomaMar 30, 2011 at 8:23
-
@Goma Moving your database is done by doing a backup and and then restore the database to the new server (or using replication, but I would recommend against it). The downtime depends on how much time it takes to move the database. As long as you have written your PHP application in a sane way you only have to change the location of the database, which should be 1 line of code.– pehrsMar 30, 2011 at 8:30
-
-
-
symcbean> it would have been great if you had elaborated a bit more your comment so we could all learn...– RafaMay 8, 2012 at 7:26
Investigate and install some sort of monitoring system now. Learn how your system responds to various loads and what begins to perform poorly as the load increases. This will then allow you to make an informed decision about what needs to be done when the time comes.
-
+1. collectd requires a bit of infrastructure to present its data, but as a collector of stats on your boxes performance, it is unrivalled. Mar 30, 2011 at 8:17
A simple way to scale MySQL is to setup master-slave replication and direct writes to the master and reads to the slave. Enterprise edition is not needed as this is supported in the community edition.
Here is MySQL's howto: http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html
There's a few ways of doing it. You could look into Master-Master replication for the Databasing and Round Robin for the DNS. Should be free, no licences.
