shaf81
asked on
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
http://img231.imageshack.us/img231/2192/mysql1hc8.jpg
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)
Regards
Shaf.
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
http://img231.imageshack.us/img231/2192/mysql1hc8.jpg
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)
Regards
Shaf.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Aleksandar Bradarić
I suppose you've considered changin the temporary ondisk tables to memory tables?