Home / server / Questions / 266462
Accepted
Andrew Fashion
Asked: 2011-05-05 15:32:46 +0800 CST

Trying to scale my dedicated server for high traffic

  • 772

I have a quad core xeon, 8GB ram, and only 2 x 1TB SATA drives right now. I am trying to figure out how to scale my application to handle as much load and traffic per day as possible. I have an industry network site and it's currently bogging around 30,000 signup's in a day, 60k UV, and 600-1,000,000 pageviews per day. It was going so slow I had to shut the server off, and now I am losing money and traffic.

I'm pretty certain part of it is the PHP code and MySQL queries (hired them from India). What can I do to optimize and make sure the server is handling as much traffic as possible without bottlenecking. As I am expecting even more signups every day. It's a very database intensive site as it's a "networking" site for a certain niche, and you must be logged in, so everything revolves around MySQL.

PS. CentOS 5 64bit, PHP5, MySQL5. I know the database is MyISAM, and I am pretty sure they didn't index the tables too well either.

Thanks for the advice!

performance-tuning database-performance dedicated-server centos5

4 Answers

  1. I am sorry, but my honest answer is this: If you don't even know how to check if your database is indexed you are so far out of your league that your only option is to hire a professional, given the scope of your project. Not a group of cheapo code slaves, but someone with a lot of experience with sites like yours.

    We can give you a plethora of tips, but properly tuning such a site is a job that needs experience and can't be done with a few questions on ServerFault.

    • 11
  2. Best Answer

    There are a few things you can do and check to give you more information on the issue and, in turn, handle it better:

    • You have to find out exactly what is making your server "slow". Is it maxing out the CPU, is it running out of RAM and using swap space, is it IO limited, etc...? Each of these have a different solution so you need to know what the problem is before you attempt to solve it. Spending time to fix the database when the real issue is simply a bad Apache configuration would be a huge waste of time and resources.
    • Some form of monitoring would be useful. This could be either something simple like sar or more a complete package like Nagios/Zabbix. This will help you now in narrowing down the cause as well as later to see how effective (or not) your solutions are. Ideally you want to scale proactively before you actually need to.
    • Make sure you are running a PHP op-code cache (APC, eAccelerator, etc...).
    • Try turning the MySQL query cache on or off. A read heavy application will likely benefit with it on while a write heavy may benefit with it off. Monitoring the query cache stats will let you know if it is helping or hurting things.
    • Try reducing the MaxClients in Apache to limit the number of users and, hopefully, reduce the server load. This may make your site at least partially available and usable.
    • Have you done any modification to the MySQL configuration? The default configuration it typically comes in is not meant for a "db-heavy" site and you may be able to see significant gains by tweaking a few values. Search here and on the web for a variety of resources in editing the configuration.
    • Check how much memory MySQL is using in top. If your application is indeed write heavy I would expect/want to see it using at least 1GB if not a few more depending on your database size. If it is only using a few 100MB, or using 6-7GB, there is likely a configuration issue.
    • Creating indexes on MySQL tables is easy...the hard part is figuring out what indexes you need and what benefit, if any, they will have. This is where an experienced professional can potentially pay off quickly (assuming the issue is indeed at the database layer).
    • Scaling out to multiple machines may be a good option if you need to quickly get your site back to operating. Put the database on one machine and Apache on another. This will also quickly tell you what your bottleneck is if one or the machine achieves a high load. The Apache server can probably be scaled to multiple machines behind a load balancer and the database can be clustered or scaled to a bigger machine if needed. Properly analyzing and optimizing your application and hardware may be cheaper in the long run but sometimes you just need to get it working yesterday. This is also where a professional can save you a lot of time and headaches in getting up and running quickly.
    • When (or if) you do get back online some form of monitoring and benchmarking would be good to have. Knowing your limits and your current/average parameters will permit you to scale before your server crashes.

    Hope this helps some and good luck!

    • 4

  3. You need to use "MySQL workbench" to login to the MySQL server. Once you are logged in as "root" (from localhost unless you edited the permissions to allow remote login) then you should be able to see the indexes. Then, if you don't have them, it is just a matter of creating them, and it should make a pretty good speed difference with your app I think. To help with creating the indexes, you need to review the instructions on the EXPLAIN command.

    • 1

  4. Depending on your cashflow, a very quick (and dirty) fix might be asking your hosting company to upgrade you to a SAS/SCSI server which will also get you into the enterprise server category. Big SATA drives are slow and your server may be great for CPU but lousy at IO.

    Congrats on the signup numbers btw. Your problem is a good problem to have.

    • 1