0

This is a really ignorant question, but I'm wondering what impact queries have on a server's resources in general and can't seem to find a straight answer through searching.

For example, suppose a query takes 0.1s to execute. Does that mean a CPU/core is tied up at 100% for that amount of time?

As an extreme example, what would happen if a query took a full minute to execute? Would the server be completely bogged down during that time, or do database management systems internally limit how much CPU, memory, etc., that they allow themselves to use so as to prevent locking up a system?

1 Answer 1

2

A query takes some combination of CPU and I/O. It might be 100% and 0% if everything it needs is cached in RAM, or it might be 1% and 99% if nothing is cached. Or anywhere in between.

Only one "core" of the CPU will be used for one connection in MySQL. Multiple connections could use all the cores; but I rarely see that.

Another issue is "locks" or "mutexes". This is where one connection is busy doing something, but has a lock on something; and another connection wants that something. Now the second connection has to wait, not using CPU or I/O.

So, 0.1 seconds (or a full minute) does not really say anything other than that the user had to wait that long for the answer. It will be some combination of CPU (one core), I/O, and being blocked.

The Operating system is responsible for parceling out resources such that the system never appears to be "locked up", and everything gets done eventually.

I have seen a query take 57 hours; it was mostly I/O. I have seen queries that I could estimate would take months. And I have seen queries that take far less than 0.1 second.

And I have seen systems that are running over a 1000 queries per second even though every query took more than 1/1000th second. This is the magic of Operating systems.

Your Answer

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

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