2

our problem is that MySQL is delaying our app after we reduced the CPU cores, although CPU usage always is and has been below 40%. My question is: Was it really the CPU reduction which triggered MySQL to be slow now? Or should I be looking somewhere else?

More details: My team is running a mobile app with up to a couple of thousand users being online at the same time. They fire up to 100 requests/second to the backend. We are using PHP/MySQL and had 12 CPU cores and 8 GB RAM. The tool phpMyAdmin showed that CPU usage was 15-25% and RAM usage at 1 GB.

Then we reduced CPU cores to 6. CPU usage went up to about 40% max. However, faced with higher load (but not higher load than we had while running 12 cores) MySQL is not able to process all queries immediately and queries are lining up delaying our whole app. This did not happen when we had 12 cores.

I would be grateful for any hints or tips. We are already conducting a full-review of the server(variable) configuration. We are using only InnoDB-tables.

Thanks a lot, Fman

Improve this question
14
  • 2
    MySQL mostly depends on the hard disk and ram it has at its disposal. You've mentioned nothing about that. Also, there's this magic variable called innodb_buffer_pool_size. It's default value is 8MB. You want that variable very high, up to 80-90% of your RAM. Commented Nov 11, 2014 at 16:28
  • @Martin Barker I know that queries are lining up using "SHOW PROCESSLIST". Yesterday, during peak hours, many queries were waiting 2-20 seconds to be processed. I have no limit on concurrent connections (set to 0). The whole thing runs on Apache 2.2.22, MySQL Version 5.5.37 and PHP 5.4.30. Commented Nov 11, 2014 at 16:29
  • 1
    When in doubt - best check what each thing does. innodb_buffer_pool_size is a number that says how much RAM MySQL is allowed to allocate for whatever purpose. Usually, MySQL keeps the working data set there. It means it's going to pull the data from RAM rather than HDD. With 128MB you can't do much. You want your entire data set to fit into RAM. It's not a problem to crunch the data, the problem is getting data fast enough to the CPU. Transferring it from HDD to CPU is, well, slow :) Commented Nov 11, 2014 at 16:45
  • 1
    As for log files, Percona guys explains it really well so I don't think I've much to say about it. Commented Nov 11, 2014 at 16:47
  • 1
    @FMan - you're welcome, and yes - they have excellent articles about tuning and various other things. Also, since we're already here, here's another thing you might consider and that just might solve your problems just like that - a drop-in ACID compliant engine for MySQL with excellent performance and compression rate. As for the comment about using MyISAM - it really isn't faster than InnoDB if you are able to utilize InnoDB to its full potential. You should more or less never need MyISAM. Commented Nov 11, 2014 at 21:58

1 Answer 1

Reset to default
3

It sounds to me like your MySQL is probably reaching it's I/O limits. We did a stress test on our DB server and something that's critical to understanding DB choke points is that when you add or change records, you have to do a disk write (SELECT can be cached). So when we were just reading our website it handled quite a load, but when we simulated a checkout (multiple records created) it bogged down very quickly.

Back then, we didn't have any options. SSDs were too expensive to provision vs magnetic (and our choke number was way beyond anything we've ever really hit). Best we could do was RAID 10. But, thanks to clouds, you can now easily (and cheaply) have a fairly powerful DB server that will handle the I/O loads much better (especially with SSD prices dropping rapidly). You'll note that Amazon sells even their middle instances on the Provisioned IOPS feature

For any production application that requires fast and consistent I/O performance, we recommend Provisioned IOPS (input/output operations per second) storage. Provisioned IOPS storage is a storage type that delivers fast, predictable, and consistent throughput performance. When you create a DB instance, you specify an IOPS rate and storage space allocation. Amazon RDS provisions that IOPS rate and storage for the lifetime of the DB instance or until you change it. Provisioned IOPS storage is optimized for I/O intensive, online transaction processing (OLTP) workloads that have consistent performance requirements.

It sounds very much like you probably have a simple setup (and maybe even host your DB on the same server). AWS isn't the only game in town but you might want to consider moving to some sort of cloud, or at least step up to something that can handle the higher I/O demands you're putting on your server. Forget the number of cores. If your HDD is choking you could be running 72 cores and not have any difference whatsoever.

One other tip: Check out MySQL Tuner. When you run it, it checks your database stats (the longer it's been running, the better) and can recommend lots of helpful tweaks that can also improve performance.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.