MySQL Scalability Advice

Posted on 2007-10-02
Last Modified: 2008-01-31
I'm involved in a small VoIP service provider businees. Currently I have 2 MySQL DB's running on FreeBSD.

1) The Master DB server is hosting the Radius application on the same server which would be accepting requests from the Telephony switches and authenticating users. The Master DB has more reads than writes. 70/30 would be the typical ratio. We've left this DB running on MyISAM

2) the Salve DB is replicated from the Master and is hosting the Web application on the same server. This is where the real bottlenecks happen. Users query for call detail records on a very heavy manner. This might sound like a read intensive environment, however, there is a catch.. Whenever a user queries for records, the web app creates a temporary ondisk table and dumps all record into it from many tables (using joins etc). Once the temporary report table is created, the data is read from the report table and displayed to the user.. So there is a lotta writes too happening, for every read. This DB is running on InnoDB.

Technically, all these 4 applications can run on the same server (Radius, Apache, MySQL), however, we've split this into 2 servers. But the time has come now, that we need to scale more.

Please check below Image for current network diagram

My ideas for scaling:



I would like to know what you recommend best for this above the the 2 options, if not any other options available to scale more over time?

(Btw, isn't there any way to upload images / attach files to a question on EE? that would be nice)

Question by:shaf81
    LVL 17

    Accepted Solution


    Looks like you are having database tuning issue.  I would suggest that when you have some time, you read following book:

    However, for now, I think you should always keep database on its own server.  First thing you should do (if possible) keep web server, radius and database(s) on different servers.  Having a dedicated server for database and web server will allow you to tweak each server's settings to optimize memory usage for a specific purpose.  Therefore, I would go with option 2.  However, just getting new hardware does not necessarily mean you will get the best result.  Consider removing the bottleneck from within the application.  

    Looks like you are doing a lot of operations to create a specific view for the user.  

    Is it possible to generate this view in the database once and then simply diplay it to the user when she requests it?  Sometimes, users only need to see a specific reports multiple times but it only changes once every day.  In that case, you can perform heavy calculations at off peak times (2 a.m. every night) and have fast read-only queries during peak time.

    Also, seems to me, you are performing heavy operations on the database and small operations on the web server.  Can you change the app so that web server queries the data and performs heavy operation it self?  Of course, this part is only useful if you have two separate servers.  

    There is a lot to consider while thinking about getting the best performance out of your mysql server.  There are some guidelines you can follow (dedicated server for database) but a lot depends on your unique situation.
    LVL 17

    Expert Comment

    I suppose you've considered changin the temporary ondisk tables to memory tables?

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
    Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now