![]() Memory rooted in the THD will grow significantly during query execution, but exactly how much it grows will depend upon the query. The THD is a large data structure which is used to keep track of various aspects of execution state. The size of the THD is ~10K and its definition is found in sql_class.h. There is always a one-to-one correspondence between a user connection and a THD, i.e. The connection is represented by a data structure called the THD which is created when the connection is established and deleted when the connection is dropped. If everything goes well in the connection phase, the user thread will enter the command phase. In this process the user credentials are stored in the THD’s security context. ![]() This user thread will allocate and initialize the corresponding THD, and then continue with capability negotiation and authentication. It is the user thread that handles the client-server protocol, e.g. It might make sense to try increasing the thread cache in cases where number of connections fluctuates between having very few connections and having many connections. The thread_cache_size default value is calculated as 8 + (max_connections / 100) and is rarely changed. ![]() Nowadays creating an OS thread is relatively cheap and the thread cache can perhaps be said to be legacy. The thread cache used to be important for connect speed when OS threads were costly to create. The receiver thread will either create a new OS thread or reuse an existing “free” OS thread if found in the thread cache. The only job of the receiver thread is to create a user thread, further processing is done by the user thread. Incoming connection requests are queued and then processed by the receiver thread one by one. A connection request is simply a TCP-IP connect message sent to port 3306 on the server host machine. The MySQL Clients send connection requests to the MySQL Server. A single multi-threaded client can open many connections to the server, but for simplicity we here say one client opens one connection to the server.Ĭonnection Requests. A MySQL Client is a command line tool or an application that talks to the MySQL Server over the MySQL Client-Server protocol using the libmysqlclient library or some of the many MySQL connectors. Figure 1 illustrates what happens when a MySQL Client connects to a MySQL Server. A client connects to the MySQL Server and stays connected until it does a disconnect. Connect and DisconnectĬonnections correspond to Sessions in SQL standard terminology. But at some point we reach a limit and adding more user threads will not be useful or efficient. As long as all user threads execute as if they are alone we can say that the system (MySQL) scales well. ![]() When more and more users connect to the database, more and more user threads execute in parallel. When a user connects to the database a user thread is created inside mysqld and this user thread executes user queries, sending results back to the user, until the user disconnects. MySQL does not have its own thread implementation, but relies on the thread implementation of the underlying OS. The MySQL Server (mysqld) executes as a single OS process, with multiple threads executing concurrent activities. We describe how connections work in a plain community server and we do not cover related topics such as thread pooling, resource groups, or connection multiplexing in this post. We hope that an increased understanding of how MySQL works will help application developers and system administrators to make good choices and trade-offs. In this post we describe MySQL connections, user threads, and scaling.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |