MySQL hosting: What are the recommended specs and w

Question

I have two specific cases of MySQL hosting.

1) I have a case where I have 2000 plus concurrent users to a single MySQL Database. They are not simple web users, but actual business users who need to connect write and read constantly through about 8-9 business hours. What should be the specs of the MySQL server, ie processor, RAM, number of CPUs etc? Would a dual Quad Core Xeon 5500 series suffice? Or Can I use Xeon 3400 series?. Will extra memory help? Someone suggested clustering! Is it necessary?

2) I have to set up hosting service for approximately 300 web sites for a client. They are expecting around 3000 to 6000 concurrent users between 300 MySQL databases. What should be the ideal configuration of the server or should I split the load on to multiple servers?

3) Does adding more cores help MySQL or is raw CPU speed more important?

Advice welcome

Answer 1

Question 1 - it depends what they are writing and reading throughout the day, the volume of data, the nature of the application(s) served by the database etc.

If by 'business user' you mean this would be a database server supporting an internal business app used by 2000 users (e.g. a call centre CRM application supporting 2000 seats where response time is critical), then a fairly beefy machine with as much RAM as you can cram in and an intelligent disk arrangement will probably do the job, but no-one would be able to offer concrete advice with the information you've given above.

A couple of badly written queries in your business app or lack of maintenance (indexing, storage management etc.) and no amount of server infrastructure is going to make the system perform well.

As with all database backed systems, everything depends on the required use case and the slowest response time you can get away with without impacting the business requirement - requirements for reporting/read centric operations are vastly different to OLTP environments. You need to figure out where the stress is going to occur first before you start buying tin. Clustering is all well and good, but requires an exponentially greater understanding of both MySQL and the application environment you are servicing.

Question 2 - Separate databases can be on separate servers. We had this in consumer web hosting environments, and we were running between 150 and 300 databases per box (standard dual processor DL380's, if I recall. this was a few years back.) Databases doing large volumes were moved to different boxes depending on load requirements. As with any DB server, loads of RAM and intelligent disk management are your friends.

Question 3 - Multiple cores deliver additional speed for parallel queries, but I recall it having diminishing returns quite quickly. Again, it depends on the type of queries and structure of the DB. Simply put, the odds of a normal use OLTP database being CPU bound are pretty slim - it's usually IO bound and it depends on how effective your caching is, how you have structured the disks (separate channels for read & write) and how you data is stored and retrieved (effective indexing, queries etc.). If I were given a choice between a faster CPU and more cores, I would buy buckets of RAM and spend most of my time agonizing over the disk layout :)

edit: apart from the huge volume of material and case studies on mysql performance available online, the High Performance MySQL book is a a pretty good starting point...

Answer 2

I would like to suggest using MySQL 5.5.11 and InnoDB for your multiple core needs

For clustering, I would recommend Circular Replication M1, M2 plus multiple read slaves S1 - S4

M1 as Master and Slave to M2
M2 as Master and Slave to M1
S1 as Slave to M1
S2 as Slave to M1
S3 as Slave to M2
S4 as Slave to M2

Convert all tables on S1 - S4 to MyISAM for faster reads
After that conversion, change all MyISAM row formats to FIXED (speeds up reads 20% without any additional structural changes)

I would create a DBVIP on a Zeus LoadBalancer to Distribute Reads across the Slaves

Use M1 strictly for Writes

Use M2 for nightly backups using either mysqldumps or CDP R1Soft's MySQL Module

This is not a panacea, but it's a start.