Adjusting MySQL settings to handle big load of users

Question

I have developed a php game that is obviously depending on mysql queries. Im running it on a monster server: Intel Xeon 7550 with 32GB RAM (Redhat x86_64 ES 5.0) with cPanel installed. I released the game on Facebook and only 250 users could use it before the CPU went mental.

I had "top" on and monitored the CPU usage. MySQL was at one point using up to 1600%! All other stuff was around 3%. Obviously I need to adjust the settings for mysql in order to release this on a bigger scale.

Apologies if this question is vague. My knowledge with adjusting mysql server settings is very limited. What settings do I need to adjust? And what should I set them to? I am guessing that memory is one of them? I am predicting a user base of 10,000 sessions running simultaneously. With my current settings, I can only have 250. Any guidance from experienced server admins would be very appreciated.

Answer 1

The problems i forsee..

1.) Mysql uses alot of memory for hundreds/thousands of connections by design..

Basically you need to set up the confiuguration to help minimise memory usage
I dont think this is a memory problem
With a database of 200Mb, and 32Gb installed you should not be hitting your maximum memory
You only have 250 users and are maxing out CPU

2.) Mysql by design, gives one connection/thread to a core ( this can be shared )

This obviously can be shared with many threads but 1 thread cant breach a core.
The problem you are seeing is too many "SLOW/HEAVY" queries, whereby maximizing each core..
Therefore you cant take more users, as the CPU is at its limit..
- Start profiling your queries
- Index Correctly for Reads
- If needed use a write master and a read slave

Some questions

What is your ratio READS / WRITES???  
Have you tried Slow_query logging ( set it to 1 second )?  
Have you used "explain" on queries to see if indexing is working?  
Have you considered a job queue for writes?  
Are you using the mysql cache effectively for reads?

Realistically your problem is bad queries/inserts..
if you can solve this problem We can help you with your config to minimise memory usage..

The config basically doesnt offer much to help with CPU usage, that is up to you with your code!

Hope this is clear :D

Answer 2

I only have a couple of points you should think about.

32GB of RAM on a heavily used database server is not a "monster", but it depends on how your database is used. If you have a bajillion of requests - AND the data (that's being requested) can't all fit into RAM, you'll have a serious issue with the disk system hammering I/O on the data storage. It doesnt sound like you need more RAM, as you're maxing out CPU.

1600% CPU usage equals 16 logical (8 physical) cores on a X7550 maxing out. You should note that hyper-threading on database servers is highly debated, as it usually gives you a tiny bit of more write performance and a tiny bit less read performance. On the other hand, disabling it will not solve your problem.

It sounds to me like your database and/or your application isn't designed very well in terms of optimization and processing time. 250 users literally killing 8x2,4GHz cores is not something I would call ordinary, and it's not something mysql server settings can solve for you.

I've voted to move this question to Stack Overflow.

Answer 3

It's fairly basic, but the "mysql performance tuning primer script" (http://www.day32.com/MySQL/) provides some good suggestions to get started.

Currently it handles recomendations for the following:

• Slow Query Log
• Max Connections
• Worker Threads
• Key Buffer
• Query Cache
• Sort Buffer
• Joins
• Temp Tables
• Table (Open & Definition) Cache
• Table Locking
• Table Scans (read_buffer)
• Innodb Status

Answer 4

I can give some general suggestions as more specific optimizations are difficult/impossible for a "blind" diagnosis:

• The default my.cnf uses very low-end settings (at least all the default ones I've seen). There are a variety of example my.cnf out there to give you a rough idea of what to start with (search for "mysql my.cnf optimization"). Be aware that the exact settings you need to adjust depend on your application so the settings that work well for someone else might not work as well for you.
• Saying "250 users" isn't enough information. Is the application read/write heavy? How many queries per second per user?
• Is the database properly designed for performance in mind? Does each table have indices setup for frequently used queries? Are the queries simple, complex, or super-complex? Be aware of "gotchas" in queries that can introduce orders of magnitudes of performance issues. Are you filtering records on the database side using "WHERE" or are you filtering on the server side by requesting and ignoring multiple records per request?
• For a 200MB database with the correct my.cnf settings the entire database working set should easily fit into RAM, especially with a server with 32GB of memory.
• If your application is read heavy with complex queries then look at a caching strategy to reduce the load on the MySQL server.
• As a comparison: I have a MySQL server 1/5th the capacity (CPU/RAM) of your server that averages 100 queries/second for a MediaWiki site and is always 90-95% idle. Although I don't know the details of your application/setup I think by a few tweaks of my.cnf and some application and database profiling/optimization you can increase your performance by an order of magnitude or two.
• If you are serious about MySQL development look into getting the book High Performance MySQL. Its a great overview of setting up and using MySQL for novice users. The MySQL High Performance Blog is also a great site to frequent.