• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 896
  • Last Modified:

MySQL Scalability Advice

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:

Option1: http://img232.imageshack.us/img232/584/mysql2pa6.jpg

Option2: http://img220.imageshack.us/img220/5400/mysql3oy6.jpg

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)

1 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.
Aleksandar BradarićSoftware DeveloperCommented:
I suppose you've considered changin the temporary ondisk tables to memory tables?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now