Different type of threads used by MySQL

MySQL uses different type of threads for running specific utilities like mysql_install_db, flushes MyISAM tables, Replication, TCP/IP sockets etc. All threads can run with four different priorities like Interrupt, connect, wait and query priorities.

Different types of threads are:

  • The main thread : This one is created by MySQL client normally, this will be one per client but if thread_concurrency is set more than one then it can create multiple threads. (Normally, it depends on no. of physical CPU on server)
  • The bootstrap thread: The mysql_install_db script starts this thread for reading commands from the file for initializing GRANT tables. It will run once while setting up/configuration of mysql server and than will exist.
  • The maintenance thread: This thread occasionally flushes MyISAM tables to Disk. InnoDB has also its own maintenance thread. This one will begin from the startup of mysql server and end when mysql server shutdown.
  • The handle TCP/IP sockets thread: This is be used for handling the incoming connections from the clients.
  • The handle named pipes/handle shared memory connections thread: These will be created when while using named pipes / shared memory protocols. These are only for Windows.
  • Signal handler (interrupt) thread: This one is setting up for receiving signals and then handles them as they come in. This one will begin from the startup of mysql server and end when mysql server shutdown.
  • The shutdown thread : This one is created by the signal handling thread. When mysql server will be shutdown, this will close all connections with close_connections() function and then will end.
  • The delayed thread: This one is used for MyISAM’s delayed inserts statements.
  • The two slave threads:¬† These are used by slave in MySQL Replication. One thread will connect to the master and handle network I/O and another will read queries from the relay log and executes them on slave.

InnoDB has also some separate threads like,

  • The master thread: For purging and another utility operations
  • Two watchman threads: for locking timeout, server monitoring and error monitoring.
  • The I/O handler thread:For I/O operations.

Ref: MySQL Internal

Leave a Reply

Your email address will not be published. Required fields are marked *