In this blog we would be navigating through the little details particularly about threads, connections, and sessions, these each element plays a distinct role, contributing towards the orchestration of database operations. As we embark on this knowledge journey, we will discuss about the differences between threads, connections, and sessions, shedding light on their individual significance and demystifying the terminology that often intertwines.

NOTE : We would be referring to the tables from sys , performance_schema and information_schema schemas for this blog.

What is a Connection ?

The easiest of all which we can easily understand and correlate here I guess is connection. A connection is nothing but a successful link established between a client application/User and the MySQL database server. This connection allows the client to send queries, receive results, and exchange data with the database. If you are interested in learning how the connections are handled please review this blog by Oracle team which has very detailed information on connection handling and scaling.

Each connection represents a separate session, enabling clients to execute queries and transactions independently of each other. A single MySQL server can handle multiple concurrent connections from different client applications, allowing for efficient multi-user access to the database.

Global variables / Status Variables

Variable_nameVariable TypeDescription
max_connectionsSystem VariableThe maximum permitted number of simultaneous client connections.
ConnectionsStatus VariableThe number of connection attempts (successful or not) to the MySQL server.
Max_used_connectionsStatus Variable The maximum number of connections that have been in use simultaneously since the server started.

Troubleshooting Tips

If your connections runs out of max_connections limit then your would see ” Too many connections” error. Therefore, database administrators have to often reconfigure connection limits and use connection pooling techniques to efficiently manage and reuse connections. It’s very important to manage and monitor connections effectively to ensure the optimal performance and resource utilisation of the MySQL server.

 SELECT Threads_connected, max_connections,
    (Threads_connected / max_connections) AS Connection_Usage_rate
 FROM
    (SELECT gs1.VARIABLE_VALUE AS Threads_connected
    FROM performance_schema.global_status gs1
    WHERE gs1.VARIABLE_NAME LIKE 'Threads_connected') tc
JOIN
    (SELECT gs2.VARIABLE_VALUE AS max_connections
    FROM performance_schema.global_variables gs2
    WHERE gs2.VARIABLE_NAME LIKE 'max_connections') c;

+-------------------+-----------------+-----------------------+
| Threads_connected | max_connections | Connection_Usage_rate |
+-------------------+-----------------+-----------------------+
| 141               | 1000            |                 0.141 |
+-------------------+-----------------+-----------------------+

NOTES:

  • If Connection_Usage_rate gets closer to 1 which means the current max_connections might run out and application won’t be able to connect to the database
  • If Connection_Usage_rate is closer 0 then the current max_connections is set way too large than needed which would be increasing the total estimated memory needed to serve that many connections
  • So we need to monitor Threads_connected status variable or processlist connections count and increase max_connections as needed.

What is Threads ?

MySQL uses threads to manage multiple client connections and process queries concurrently. Each client connection is assigned to a separate thread, allowing multiple clients to interact with the database server simultaneously.

The threads are classified as FOREGROUND or BACKGROUND threads. User connection threads are always known as foreground threads while threads associated with internal server activity are background threads. Examples are

  • Internal InnoDB threads,
  • binlog dump threads sending information to replicas,
  • Replication I/O and SQL threads.

Global variables / Status Variables

Threads in MySQL handle tasks such as query processing, communication with the client, and managing resources like memory and locks. MySQL’s threading mechanism allows it to be a multi-user, multi-tasking database server, enabling efficient and concurrent processing of queries from multiple clients. Below are the important variables that helps defining the behaviour and limits of thread handling.

System Variable NameDescription
thread_cache_sizeLimits the number of threads the server should cache for reuse ideally shouldn’t be more than max_connections ideally MySQL won’t store more connections in memory than it is allowed to serve.
thread_handlingDefines the thread-handling model used by the server for connection threads. 
– no-threads (the server uses a single thread to handle one connection)
– one-thread-per-connection
(the server uses one thread to handle each client connection)
– loaded-dynamically
(set by the thread pool plugin when it initializes)

Status Variable NameDescription
Threads_connectedThis shows the number of open connections currently.
Threads_cachedAnd this is the number of threads in the thread cache based on the limits configured using thread_cache_size.
Threads_createdThe number of threads created to handle new connections because there were not enough threads available in the thread_cache_size. So if Threads_created is big then, you may want to increase the thread_cache_size value. The cache miss rate can be calculated as Threads_created/Connections.
Threads_runningThese are the number of threads that are active for running queries or doing some work but not sleeping.

Troubleshooting Tips

Sometimes you might see a huge number of threads created to handle new connections that is because there were not enough threads available in the thread_cache_size then that’s an indication to increase the thread_cache_size value. Moreover you can the cache miss rate can be calculated as Threads_created/Connections.

Lets see with an example here.

I reduced the thread_cache_size on a test environment with workload to simulate these readings. The performance degradations due to these won’t be too visible however over the period of time you would see mysql is not able to

show global variables like '%thread_cache_size%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 128   |
+-------------------+-------+
1 row in set (0.29 sec)

mysql> show global status like 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 55    |
| Threads_connected | 138   |
| Threads_created   | 3788  |
| Threads_running   | 6     |
+-------------------+-------+
4 rows in set (0.29 sec)

Query to find Thread cache miss ratio

 SELECT threads_created, connections,
    (threads_created / connections) AS thread_cache_miss_rate
 FROM
    (SELECT gs1.VARIABLE_VALUE AS threads_created
    FROM performance_schema.global_status gs1
    WHERE gs1.VARIABLE_NAME LIKE 'Threads_created') tc
JOIN
    (SELECT gs2.VARIABLE_VALUE AS connections
    FROM performance_schema.global_status gs2
    WHERE gs2.VARIABLE_NAME LIKE 'Connections') c;

+-----------------+-------------+------------------------+
| threads_created | connections | thread_cache_miss_rate |
+-----------------+-------------+------------------------+
| 3788            | 11020391    |  0.0003437264612480628 |
+-----------------+-------------+------------------------+

NOTES:

  • If thread_cache_miss_rate gets closer to 1 which means the current thread cache size is not large enough to keep up with new connection requests.
  • If thread_cache_miss_rate is closer 0 then the current thread cache size is either equal to or larger than needed to keep up with new connection requests.
  • So we need to monitor Threads_cached status variable to determine the right value for thread cache size

What is a Session ?

Sessions in general gives us the insights on how a client and the database server are interact ing. A session represents the period during which a client application connects to the server, executes one or more queries or transactions, and then disconnects. During a session, the client maintains its connection to the server, allowing it to execute one or more queries.

What Information a session can have ?

  1. Connection Establishment: When the client established a connection to the MySQL server and using which user
  2. Query Execution: Tracks and stores queries executed within that session
  3. State Persistence: It also maintains the client’s current state, such as variables, temporary tables, and locks etc. This information remains available for the duration of the session.
  4. Transaction Management: Then we can get all the commands or transactions a user performed from performance_schema.events_statements_history table
  5. Resource Utilization: While a session is active, the server allocates memory and resources to handle the client’s queries and manage any locks that are needed. You can see the sys.session output for a admin session to the database and its metadata for analysis

Once the client completes its tasks or decides to disconnect, the session ends. The resources associated with the session are released, and the server then can handle other client requests.

It’s important to manage sessions effectively to ensure that resources are used efficiently and to avoid issues such as resource contention, deadlocks, or excessive memory consumption.

How to configure Statement Event Collection

  • Query to CHECK which instruments are enabled in performance_schema to collect event statements.
SELECT NAME, ENABLED, TIMED
       FROM performance_schema.setup_instruments
       WHERE NAME LIKE 'statement/%';

SELECT *
       FROM performance_schema.setup_consumers
       WHERE NAME LIKE '%statements%';

  • Query to ENABLE which instruments are enabled in performance_schema to collect event statements.
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'statement/%';

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE '%statements%';

OR

[mysqld]
performance-schema-instrument='statement/%=ON'
performance-schema-consumer-events-statements-current=ON
performance-schema-consumer-events-statements-history=ON
performance-schema-consumer-events-statements-history-long=ON
performance-schema-consumer-statements-digest=ON
  • Query to DISABLE which instruments are enabled in performance_schema to collect event statements.
UPDATE performance_schema.setup_instruments
SET ENABLED = 'NO', TIMED = 'NO'
WHERE NAME LIKE 'statement/%';

UPDATE performance_schema.setup_consumers
SET ENABLED = 'NO'
WHERE NAME LIKE '%statements%';

[mysqld]
performance-schema-instrument='statement/%=OFF'
performance-schema-consumer-events-statements-current=OFF
performance-schema-consumer-events-statements-history=OFF
performance-schema-consumer-events-statements-history-long=OFF
performance-schema-consumer-statements-digest=OFF

NOTE: Be careful while enabling performance schema instruments and metrics on production servers as it use significant resources based on the workload. Test it in non-prod or stage environment before enabling in prod.

How do you get Session details ?

Let’s see this with an example here.

We can get the thread ID details for sys.processlist view OR performance_schema.threads OR

performance_schema.threads

The threads table contains a row for each server thread. Each row contains information about a thread and indicates whether monitoring and historical event logging are enabled for it:

select THREAD_ID,PROCESSLIST_ID,NAME,PROCESSLIST_COMMAND from performance_schema.threads where PROCESSLIST_USER like 'user_name%' limit 10; 
+-----------+----------------+---------------------------+---------------------+
| THREAD_ID | PROCESSLIST_ID | NAME                      | PROCESSLIST_COMMAND |
+-----------+----------------+---------------------------+---------------------+
|  14169897 |       10817277 | thread/sql/one_connection | Sleep               |
|  14352829 |       10970033 | thread/sql/one_connection | Sleep               |
|  14401517 |       11010861 | thread/sql/one_connection | Sleep               |
|  14329294 |       10949802 | thread/sql/one_connection | Sleep               |
|  14441519 |       11045095 | thread/sql/one_connection | Sleep               |
|  14444806 |       11047946 | thread/sql/one_connection | Sleep               |
|  14446134 |       11049078 | thread/sql/one_connection | Sleep               |
|  14446135 |       11049079 | thread/sql/one_connection | Sleep               |
|  14418506 |       11025318 | thread/sql/one_connection | Sleep               |
|  14418507 |       11025319 | thread/sql/one_connection | Sleep               |
+-----------+----------------+---------------------------+---------------------+

processlist and x$processlist Views

The processlist and x$processlist views summarize process information which are currently running. They provide more complete information than the SHOW PROCESSLIST statement and the INFORMATION_SCHEMA PROCESSLIST table, and are also nonblocking. These view provides additional details like latency , progress , rows , scan types etc. which is more useful for analysing and troubleshooting purposes.

select thd_id,conn_id,command from sys.processlist where user like 'user_name%' limit 10;

+----------+----------+---------+
| thd_id   | conn_id  | command |
+----------+----------+---------+
| 14443687 | 11046967 | Sleep   |
| 14361149 | 10977093 | Sleep   |
| 14445014 | 11048138 | Sleep   |
| 14446139 | 11049083 | Sleep   |
| 14331352 | 10951508 | Sleep   |
| 14441519 | 11045095 | Sleep   |
| 14444806 | 11047946 | Sleep   |
| 14446134 | 11049078 | Sleep   |
| 14401569 | 11010901 | Sleep   |
| 14331600 | 10951720 | Sleep   |
+----------+----------+---------+

mysql> select * from sys.processlist where user like 'user_name%' limit 1G
*************************** 1. row ***************************
                thd_id: 14405312 -----------> Lets take this Thread ID and dig more
               conn_id: 11013932 -----------> ProcessList ID 
                  user: XXXXXX@XXXXXXX
                    db: XXXXXXXXXXXXXX
               command: Sleep
                 state: NULL
                  time: 83
     current_statement: NULL
      execution_engine: PRIMARY
     statement_latency: NULL
              progress: NULL
          lock_latency: 5.00 us
           cpu_latency:   0 ps
         rows_examined: 0
             rows_sent: 0
         rows_affected: 0
            tmp_tables: 0
       tmp_disk_tables: 0
             full_scan: NO
        last_statement: XXXXXXXXXXXXXXXXX
last_statement_latency: 448.79 us
        current_memory: 36.23 KiB
             last_wait: NULL
     last_wait_latency: NULL
                source: NULL
           trx_latency: 92.21 us
             trx_state: COMMITTED
        trx_autocommit: YES
                   pid: 195751
          program_name: proxysql
1 row in set (0.78 sec)

session and x$session Views

These views are similar to processlist and x$processlist, but they filter out background processes to display only user sessions. For descriptions of the columns, see Section 28.4.3.22, “The processlist and x$processlist Views”.

mysql> select * from sys.session where thd_id=14405312G
*************************** 1. row ***************************
                thd_id: 14405312
               conn_id: 11013932
                  user: XXXXXX@XXXXXXX
                    db: XXXXXXXXXXXX
               command: Sleep
                 state: NULL
                  time: 53
     current_statement: NULL
      execution_engine: PRIMARY
     statement_latency: NULL
              progress: NULL
          lock_latency:   0 ps
           cpu_latency:   0 ps
         rows_examined: 0
             rows_sent: 0
         rows_affected: 0
            tmp_tables: 0
       tmp_disk_tables: 0
             full_scan: NO
        last_statement: NULL
last_statement_latency: 56.98 us
        current_memory: 26.23 KiB
             last_wait: NULL
     last_wait_latency: NULL
                source: NULL
           trx_latency: 90.10 us
             trx_state: COMMITTED
        trx_autocommit: YES
                   pid: 195751
          program_name: proxysql
1 row in set (0.71 sec)

Session activities can be retrieved from performance_schema.events_statements_history

mysql> select THREAD_ID,EVENT_NAME,SQL_TEXT AS Query from performance_schema.events_statements_history where THREAD_ID=14405312;
+-----------+----------------------+----------------------------------------------------+
| THREAD_ID | EVENT_NAME           | Query                                              |
+-----------+----------------------+----------------------------------------------------+
|  14405312 | statement/com/Ping   | NULL                                               |
|  14405312 | statement/com/Ping   | NULL                                               |
|  14405312 | statement/com/Ping   | NULL                                               |
|  14405312 | statement/sql/delete | DELETE FROM ************************************** |
|  14405312 | statement/com/Ping   | NULL                                               |
|  14405312 | statement/sql/delete | DELETE FROM ************************************** |
|  14405312 | statement/sql/delete | DELETE FROM ************************************** |
|  14405312 | statement/com/Ping   | NULL                                               |
|  14405312 | statement/sql/delete | DELETE FROM ************************************** |
|  14405312 | statement/sql/delete | DELETE FROM ************************************** |
+-----------+----------------------+----------------------------------------------------+

Conclusion

System tables and performance tables that can give us details about our complete workload as they collect lots of data in the backend when enabled Querying them could cause significant performance impact. Kindly review the complete performance_schema documentation while configuring it. Performance schema should be turned on and off as needed.

Here are some of the commands that we have used here to get these metadata around processlist.

  • Processlist Based
    • show processlist OR
    • show full processlist OR
    • select * from performance_schema.processlist OR
    • select * from `sys`.`x$processlist`
  • select * from performance_schema.threads
  • select * from sys.x$session

NOTE : Be mindful in performing any analytical operations on performance_schema related to InnoDB which can be performance impacting so please test them in lower environment and use them carefully. 

Hope you find this information useful. Keep reading and follow us for more exclusive content for open source databases. Do not forget to reach out to our support is your would like to know about our services

References:

We have also shared details about how concurrency would impact the performance in this blog

%d bloggers like this: