0

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

ramdaz
  • 675
  • 1
  • 8
  • 25

1 Answers1

2

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...

Mark Regensberg
  • 1,421
  • 12
  • 14
  • The application is that of a transport company, and is fairly basic. They have close to 2000 offices/agents across the world, who use a web interface to enter shipment data, and sometimes read data. There's a seperate application which reads from a copy of this database, and does all business stuff. But during some of the hours, you will actually have a lot of data entry happening.Does this help in a better suggestion for a spec – ramdaz Apr 21 '11 at 04:23
  • Its worth bearing in mind that the federated storage engine allows you to have specific tables on different servers - not just whole databases - indeed there's a case here for running mysql instances as front end proxies (since the connections are probably idle for a significant amount of time) – symcbean Apr 21 '11 at 14:02