MySQL Forums
Forum List  »  MyISAM

Re: MySQL threads level sequence diagram at OS level
Posted by: Rick James
Date: November 06, 2014 08:05PM

Most of the threads are per-connection threads. When a connection is created (due to a client request on port 3306 or through a socket), a thread is created (or taken from a pool). This thread chatters with the client. This chatter is mostly of the form of: Client sends SQL to Server; Server sends back a resultset, plus various meta info.

Eventually, the client closes the connection, and the process (thread) goes away (unless it is cached).

I believe (without concrete evidence) that one of the threads is the central controller. It communicates with the other threads, mostly at their initiation. The chatter has to do with various mutexes and other locks.

In InnoDB, the I/O with disk is centralized in a few threads, and probably not done by the per-connection threads.

Since the code is optimized with WAN traffic in mind, one SQL statement implies only one round trip between the client and server.

Even with the "sequence" diagram, things can be very complex. For example, there are multiple caches that effectively avoid doing I/O most of the time.
* The first mention of a table leads to opening the file(s) that embody that table. Subsequent SQL statements do not need that "open".
* Referencing a block of an index or data will load it into some cache -- the first time. Or after that block has been bumped from cache.
* Writes (at least in InnoDB) are delayed. That is, an INSERT statement may not show any writes for hours.
* Log files are written.
* If the Query cache is involved, even less activity will show up.

I think your task is mind boggling. What is the goal?

Options: ReplyQuote

Written By
Re: MySQL threads level sequence diagram at OS level
November 06, 2014 08:05PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.