It was clear that the client was going through a significant growth phase and this was affecting performance of their systems. There are many ways to manage growth and it is not always possible to immediately implement the utopian solution, the dx solution identified a cost effect way of getting to an end solution that met the present and future needs of the client. As with everything in the Internet, there is no right way of getting to an end point. This solution was a starting point which gave a roadmap whilst incorporating flexibility to rollout or amend the solution as the clients requirements developed.
The initial requirement had been defined in a series of e-mails: The bulk of the CPU power is used for the dB. For a lot of users the software isn’t even hosted on the server. Flash portals prefer to host the software on their servers so the customer primarily handles the database traffic. There is essentially no processing of data on the server it is just shuttled back and forth between the game and the server via http calls. The client currently does not host the streamed music because of bandwidth considerations. The music is a low priority though. What the customer really needs is to keep the database up and serving requests quickly. The client needed a scalable solution that delivers fast http access to the database that could support up-to 3,000,000 hits a day. The client was unclear as to what 3,000,000 hits meant in terms of simultaneous users. They were unable to find that statistic on their current platform. The only statistic to work on was the previous Saturday sustained 3 million hits most of that representing database traffic. The priority was exclusively about the database being quick
Original configuration ran on a Dual Core Xeon with guarantee of 2 Cores, employing a shared network store of 160 Gbytes, using 4 GBytes RAM. The Database was MySQL, some of the biggest performance affecters on MySQL are available memory and disk speed access. Immediate performance improvement should be from increasing memory to 8 GBytes. As the disk store was over a network this could be degrading performance. On a Sunday there were 3,000,000 hits (we assume this is DB requests) and 200,000 games played. Further investigation was required into the traffic profile of the client systems, detailed below are our assumptions and calculations used to work out key load parameters. Key parameters are peak number of concurrent sessions and the peak number of requests to the database.The assumptions made need to be verified. If we assume that peak traffic in the day is twice the average and the average duration of a session is 15 minutes the following stats could be gleamed:
The traffic calculations do not seem to indicate an initial requirement beyond a beefy Database Server, say 2 x Quad Core Xeons, 8 GByte RAM and 2 x 1 Terabyte SCSI drives configured as RAID 1. Should the usage continue to grow significantly an ultimate solution would be database clustering across several servers. There are several steps from a single server to a clustered solution, these will be determined by rate of growth, the application environment and of course funding. The clustering solution requires a minimum of 4 servers and should be viewed as an architecture that can give performance, reliability and scalability with incremental capacity growth beyond the minimum configuration. MySQL Clustering is a standard solution which employs “commodity” servers thus reducing the total cost of ownership. A MySQL server can employ a cluster through enabling ndb-cluster, the cluster facility therefore is independent of the database application and should not require changes to the existing operation. The basic key components of a MySQL cluster are shown in the diagram below:
The database is replicated across multiple Data Nodes which in turn are controlled by a Management server. If one Data Node fails the other continues to server requests. The number of data nodes must be a minimum of two but can increase with demand. In the same way the number of SQL Nodes can independently increase. A major benefit of this approach is that the increased capacity in SQL Nodes or Data Nodes can be in a definable independent incremental manner, since “commodity” servers can be employed the start up and incremental costs are more manageable.
The dx proposal was to initially implement a single server solution to deliver services in the same manner that the client was delivering service at the time. If traffic continued to grow at its current rate the client had the option to upgrade to a clustered solution. The benefits of this approach include:
dx proposed that it implement a dedicated database server within its Atlanta data centre with significantly increased capacity on the current configuration. Should the client wish to upgrade to a cluster based solution the initial Cluster solution that dx proposed is shown below:
The proposal was to frontend the service with SQL Nodes sitting behind a traffic load balancer, this enhanced performance as well as giving a graceful failover should one of the Frontend servers fail. Throughout we proposed to employ Quad Core Processors and 8 Gbytes of RAM, using more RAM assists in the caching nature of MySQL and again improving performance. The Frontend servers were configured with single disks as resilience is in the Load Balancer and of course the cluster. We maintained the use of dual discs in RAID1 configuration for the cluster, although this may be considered overkill particularly if 3 or more Data Nodes are employed.
The client saw performance issues on their current systems resulting from successful rollout of their game. Based on scant information and assumptions a traffic profile was quantified forming a basis for designing the solution and a methodology for defining future Service Indicators. Should the traffic continue to exponentially grow a resilient high performance and cost effective solution was required. dx proposed that the ultimate solution could be based upon a clustered MySQL database, it proposed a migration path that can be determined by the client, based upon growth and economic criteria.