How are concurrent queries handled in a MySQL database?

Question

I am using a MySQL database, and I would like to know if I can make several (500 or more) queries at the same time to get information from several tables, how are these queries processed? In series or in parallel?

Answer 1

Requests are always processed in parallel between multiple sessions (i.e. client connections). All requests in one connection are started one after another. The level of parallelism between multiple connections can be adjusted depending on the server resources available.

Typically, some operations are secured between separate request sessions (called transactions). They are supported by InnoDB backends, but not MyISAM tables (but it does support a concept called atomic operations). There are various isolation levels, which differ in that operations are protected from each other (and, therefore, how operations in one parallel transaction affect another) and in their impact on performance.

Learn more about transactions in general and implementation in MySQL .

Answer 2

Each connection can run a maximum of one request at a time and does so in a single thread. The server opens one thread for each request.

Usually, I hope the requests do not block each other. However, depending on the engine and requests, they may. MySQL has a lot of locks, as detailed in the manual.

However, if they do not block each other, they can still slow down each other, consuming resources. I / O operations are a special source of these slowdowns. If your data does not fit into memory, you really should limit the number of concurrent requests for what your I / O subsystem can handle, or everything will be very bad. Dimension is the key.

I would say that if 500 requests are started immediately (and DO NOT wait for locks), you may not get the best value from your hardware (do you have 500 cores? How many threads are waiting for IO?)

Answer 3

Usually all requests will be executed in parallel.

But ... there are some exceptions. Depending on the transaction isolation level, the row may be locked during the update. Read more about this here: http://dev.mysql.com/doc/refman/5.1/en/dynindex-isolevel.html