0

This is a general question, not to solve a problem: Since MySQL uses one thread per connection. That means if you do IO, and it has to wait for it, the CPU that the thread runs on will be in IOWAIT. If you have as many MySQL threads as CPU threads, all CPUs will be in IOWAIT. But, that's not to say they can't do anything else. So as long as you still have IO scaling capacity, isn't the CPU use in panels like Amazon RDS misleading when you do many concurrent writes?

Part of this seems confirmed in Amazon RDS, where nowadays you see non-cpu load:

RDS graph showing non-cpu load

However, if you then go into Enhanced Monitoring, you things like this:

enhanced monitoring

Or in the overview:

RDS overview

In other words, would htop with 'detailed cpu stats' on show a bunch of gray CPU bars indicating that's not actual CPU power that is maxed out? Like this (example, NOT the actual MySQL server; it's not about the numbers shown):

htop iowait

Improve this question
2
  • "NOT the actual MySQL server" -- Please do not confuse the Question by including data directly related to the problem.
    – Rick James
    May 23 at 15:46
  • With the comment, I think it's clear enough that I'm talking about the gray bars. To me, an htop with gray bars speaks volumes and I think it does to many other sysops too. Perhaps I'm too biased towards that and others will look more at the numbers, but isn't it clear enough this way?
    – Halfgaar
    May 23 at 22:44

1 Answer 1

Reset to default
0
  • InnoDB has some background threads doing I/O. So, only some I/O is being waited for. I suggest that what is going on is too involved to make simplistic conclusions.
  • Oracle 8.0 and Aurora have a few cases where multiple CPUs are deployed for a single connection. It's too early to get excited about these rare cases of parallelism.
  • Much of the possible I/O operations is avoided when innodb_buffer_pool_size is bigger than the dataset.
  • 1K connections is not necessarily alarming. 100 Threads_running is.
  • You have a small amount of Swap in use and nearly all of RAM in use. This is worrisome. Can you decrease innodb_buffer_pool_size slightly? Or maybe lower some other memory settings?
  • Assuming that htop is related,... 8 Cores, mostly busy -- It Sounds like some queries could use a better index and/or be reformulated. Is the slowlog turned on? Is long_query_time set no higher than 1?
  • If previous queries have made many index changes, I/O (reads and/or writes) could be involved in that. Cf: "change buffering". [Caveat: I'm speaking for MySQL; RDS may work differently.]
Improve this answer
4
  • No matter how big the buffer pool is, the write-ahead is always written. That's also what keeps thread_running busy, right? It's the number of threads not sleeping, so IOWAIT counts. As for the htop screenshot, that was just an example of any random server. Better remove your responses about that; it confuses. I edited my question to make it clear.
    – Halfgaar
    May 23 at 6:03
  • I know of some read-ahead, but it did not seam significant here. What is involved in write-ahead?
    – Rick James
    May 23 at 15:49
  • I meant this: dev.mysql.com/blog-archive/…. In my interpretation, that article indirectly suggests it's OK to have hundreds of threads running. Especially the picture at the bottom, which shows this write ahead log can now handle up to 512 connections (and more) writing.
    – Halfgaar
    May 23 at 22:48
  • Another thing to consider is that certain wait-for-mutex pauses are done via a CPU loop instead of interrupting to the system. The former hangs onto the CPU (hopefully for only microseconds); the latter releases the CPU (but takes longer).
    – Rick James
    May 24 at 2:21

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.