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