ProxySQL Threads

René Cannaò edited this page Dec 3, 2017 · 1 revision

ProxySQL is multi-threaded daemon, with one or more threads per module.
The following is a list of threads normally running:

Main thread

This thread is only responsible to bootstrap the core modules and to start the other core threads

Admin thread

This thread is responsible for the following:

  • it initializes and bootstraps of the Admin interface
  • it performs the initial load of configuration from either config file or database file
  • if enabled, it starts the HTTP server that handles the web UI
  • if enabled, it configures the Cluster module
  • it starts a listener responsible to accept new connections in Admin interface and creates a new thread for each admin/stats connection

MySQL workers

mysql-threads threads are responsible to handle all the MySQL traffic, for all the clients connections and for all the backends connections. That is: few threads handling any number of connections.
MySQL worker threads listen all on the same port(s). When a new client connects, one of MySQL workers will successfully accept the connection and creates a MySQL Session: that MySQL Session/client is bind to that specific worker until the client disconnects. In other words, a client connection is always processed by the same MySQL worker.

MySQL auxiliary threads

These threads are also known as “idle threads”.
If proxysql is started with —idle-threads option, for each MySQL worker thread an auxiliary thread is started. Each MySQL worker thread and it auxiliary thread work together: the first thread handles active connections and assign to the second thread all idle connections, while the second thread only waits for an event (or a timeout) to happen on the idle connections and pass them back to the first thread if this happens.
The use of “idle threads” is recommended when the number of active clients connections is very small compared to the number of idle clients connections.
This allows ProxySQL to handle hundreds of thousands of connections (tested up to 1 million connections).

HTTP server (new in 1.4.4)

ProxySQL has a built-in HTTP server that uses libmicrohttpd. Is configured with MHD_USE_INTERNAL_POLLING_THREAD, therefore can potentially use multiple threads.

Cluster threads (new in 1.4.2)

For each ProxySQL node in a ProxySQL Cluster, a thread is started and is responsible for that one peer only. The number of threads can dynamically increase or decreased if nodes are added or removed.

Query Cache purge thread

This thread acts as a garbage collector for query cache. A garbage collector is used to ensure that data purging is not performed in the serving path (while clients are waiting replies)

ClickHouse Server thread (new in 1.4.3)

If ClickHouse Server is enabled (if support for ClickHouse is compiled, and proxysql started with --clickhouse-server), this thread is responsible for the following:

  • it initializes the ClickHouse module
  • it starts a listener responsible to accept new connections and creates a new thread for each client connection that wants to access ClickHouse

SQLite3 Server thread (new in 1.4.3)

If SQLite3 Server is enabled (proxysql started with --sqlite3-server), this thread is responsible for the following:

  • it initializes the SQLite3 Server module
  • it starts a listener responsible to accept new connections and creates a new thread for each client connection that wants to access the built-in SQLite3 Server

Monitor threads

Monitor module starts several threads.
The implementation has changed in the past, some time reducing the number of threads, other time increasing it.
In ProxySQL 1.4, the threads started by Monitor module are:

  • a main thread responsible to start a thread for each monitor category (5 in total)
  • a thread scheduling connection checks
  • a thread scheduling ping checks
  • a thread scheduling read-only checks
  • a thread scheduling replication lag checks
  • a thread scheduling group replication monitoring
  • a threads pool (initially twice the size of mysql-threads)

The thread pool is responsible to perform all the checks and monitoring scheduled by the scheduling threads. The pool can automatically grow and shrink based on the number of checks in the monitor queue. The same threads are also responsible to immediately take action based on the result of checks, like shunning a node or reconfigure a hostgroup.

Note on CPU usage

The threads listed above have very difference CPU usage profiles.
In general, MySQL worker threads are the most busy threads and the ones responsible for most of the CPU usage.
Although their CPU usage is very low, it is possible that the Monitor module drastically increases the size of the Monitor thread pool if ProxySQL has to check hundreds or thousands of servers.

You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.
Press h to open a hovercard with more details.