MySQL Bugs: #17954: Threads_connected > Threads

Bug #17954	Threads_connected > Threads_created
Submitted:	6 Mar 2006 8:45	Modified:	6 Mar 2010 19:17
Reporter:	Olaf van der Spek (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	6.0-BK	OS:	Linux (Linux)
Assigned to:	Davi Arnaut	CPU Architecture:	Any
Tags:	qc

Description:
| Threads_connected                 | 8         |
| Threads_created                   | 6         |

How can more threads be connected than created?

How to repeat:
-

Hi,
do you have thread_cache set? MySQL can reuse old threads and not kill them. In this case thread_created will be lower than threads_running.

Yes, I do:
| thread_cache_size               | 5                           |

> In this case thread_created will be lower than threads_running.

I didn't mention threads_running.
But if there are currently 8 threads connected, each of those 8 threads has been created in the past. Or are multiple connections sharing a single thread?

Ok, seems like a bug. My fault regarding Thread_running. Here is meaning of all these:

Thread_created -> total number of created threads (calls to pthread_create() or equivalent)
Thread_connected -> number of connections (threads with connected user)
Thread_running -> a command is executed on a connection (thread with a connected user).

Have you performed FLUSH STATUS?

No.

Is there any known way to repeat this result?

I haven't seen it on Windows yet, but on my Linux systems I've seen it a few times.
I'll see if I can reproduce it myself.

Please, try to repeat it. Any ideas on situations when this happens are also appreciated.

I think it counts insert delayed threads as connected but not as created.

I can confirm the last statement of Olaf van der Spek and would also add that because of counting insert delayed threads as connected, Threads_connected may be greater than Max_used_connections.

In a test case below I expect both

  SHOW STATUS LIKE 'threads_connected';

to return 1:

--cut--
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

# Kill delayed_insert threads, if any.
FLUSH TABLES;

# Prerequisites.
SHOW STATUS LIKE 'delayed_insert_threads';
SHOW STATUS LIKE 'threads_connected';

# Ensure that delayed_insert_limit is not zero (FIXME: could it
# possibly be?).
SET @save_delayed_insert_limit=@@delayed_insert_limit;
SET GLOBAL delayed_insert_limit=10;

CREATE TABLE t1 (i1 INT);

# Create one delayed_insert thread.
INSERT DELAYED INTO t1 (i1) VALUES (0);

SHOW STATUS LIKE 'delayed_insert_threads';
SHOW STATUS LIKE 'threads_connected';

# Restore original setting.
SET GLOBAL delayed_insert_limit=@save_delayed_insert_limit;

DROP TABLE t1;
--cut--

Verified just as described in the last comment with 5.0.21-BK (ChangeSet@1.1616.2144.145, 2006-04-06) on Linux:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.21

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW STATUS LIKE 'delayed_insert_threads';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Delayed_insert_threads | 0     |
+------------------------+-------+
1 row in set (0.00 sec)

Smysql> SHOW STATUS LIKE 'threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 2     |
+-------------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL delayed_insert_limit=10;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1(i1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT DELAYED INTO t1 (i1) VALUES (0);
Query OK, 1 row affected (0.00 sec)

mysql> SHOW STATUS LIKE 'delayed_insert_threads';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Delayed_insert_threads | 1     |
+------------------------+-------+
1 row in set (0.01 sec)

mysql> SHOW STATUS LIKE 'threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 3     |
+-------------------+-------+
1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE 'threads_created';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Threads_created | 2     |
+-----------------+-------+
1 row in set (0.00 sec)

If it is intended behaviour, it should be explicitely documented.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40983

ChangeSet@1.2583, 2008-01-14 09:28:42-02:00, davi@mysql.com +5 -0
  Bug#17954 Threads_connected > Threads_created
  
  The problem is that insert delayed threads are counted as connected
  but not as created, leading to a Threads_connected value greater then
  the Threads_created value.
  
  The solution is to increment the Threads_created variable when insert
  delayed threads are created. Nevertheless, the Threads_connected may
  still be greater then Threads_created because in reality it reflects
  the number of open connections (or open thread states) that also might
  not be associated with created threads.

During the discussion of above patch the following facts were revealed:

- Currently internal 'thread_count' variable (to which 'Threads_connected' corresponds) doesn't contains the number of currently open connections but rather a number of threads which are handling open connections + number of some system threads (like event scheduler and delayed insert threads, but not including replication threads).
- OTOH 'thread_created' variable reflects the number of threads which were created to handle users connections (which is exactly what our manual says).
- Finally in 6.0 with libevent support there is no one-to-one correspondence between connections and threads (so 'Threads_connected' reflects number of open connections rather than number of any kind of threads).

So questions arise:
- How we should resolve discrepancy between manual and real meaning of 'Threads_connected'? Should we update the manual or change code to adhere to the manual? Also in the former case should we include other threads like replication  threads into this value?
- Taking into account libevent changes may be it makes sense to retire this variable and introduce something like 'Open_connections' instead?

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44161

ChangeSet@1.2604, 2008-03-17 21:55:52-03:00, davi@mysql.com +5 -0
  Bug#17954 Threads_connected > Threads_created
  
  The problem is that insert delayed threads are counted as connected
  but not as created, leading to a Threads_connected value greater then
  the Threads_created value.
  
  The solution is to increment the Threads_created variable when insert
  delayed threads are created and to change the Threads_connected variable
  to reflect (as documented in the manual) the number of client connections.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44197

ChangeSet@1.2604, 2008-03-18 15:08:06-03:00, davi@mysql.com +4 -0
  Bug#17954 Threads_connected > Threads_created
  
  The problem is that insert delayed threads are counted as connected
  but not as created, leading to a Threads_connected value greater then
  the Threads_created value.
  
  The solution is to enforce documented behavior that the 
  Threads_connected value shall be the number of currently
  open connections and that Threads_created shall be the
  number of threads created to handle connections.
  
  Also there is a incompatible change that Thread_connected
  and Thread_created are not reset to 0 anymore by the FLUSH
  STATUS statement.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44218

ChangeSet@1.2604, 2008-03-19 09:03:08-03:00, davi@mysql.com +3 -0
  Bug#17954 Threads_connected > Threads_created
  
  The problem is that insert delayed threads are counted as connected
  but not as created, leading to a Threads_connected value greater then
  the Threads_created value.
  
  The solution is to enforce the documented behavior that the 
  Threads_connected value shall be the number of currently
  open connections and that Threads_created shall be the
  number of threads created to handle connections.

Queued in 6.0-runtime

Pushed into 6.0.6-alpha

Noted in 6.0.6 changelog.

Delayed-insert threads were counted as connected but not as created,
incorrectly leading to a Threads_connected value greater than the
Threads_created value.

Queued into next-mr-runtime (5.6.0)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/91167

2928 Konstantin Osipov	2009-11-20
      Backport of:
      ------------------------------------------------------------
      revno: 2572.23.1
      committer: davi@mysql.com/endora.local
      timestamp: Wed 2008-03-19 09:03:08 -0300
      message:
      Bug#17954 Threads_connected > Threads_created
      
      The problem is that insert delayed threads are counted as connected
      but not as created, leading to a Threads_connected value greater then
      the Threads_created value.
      
      The solution is to enforce the documented behavior that the
      Threads_connected value shall be the number of currently
      open connections and that Threads_created shall be the
      number of threads created to handle connections.
     @ mysql-test/r/status.result
        Add test case result for Bug#17954
     @ mysql-test/t/status.test
        Add test case for Bug#17954
     @ sql/mysqld.cc
        Change Threads_connected to reflect the number of
        open connections. SHOW_INT type variables are not
        reset.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091124194633-yc0achgq1ioyqzng) (version source revid:alik@sun.com-20091124194633-yc0achgq1ioyqzng) (merge vers: 6.0.14-alpha) (pib:13)

Pushed into 5.6.0-beta (revid:alik@sun.com-20091124193905-3iyzegd75k4givuz) (version source revid:kostja@sun.com-20091120203000-lky3jqo7r35s24ps) (merge vers: 5.6.0-beta) (pib:13)

Noted in 5.6.0 changelog.

Already fixed in 6.0.x.

Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20091125142014-7asc9sj33gzki0ym) (merge vers: 5.6.0-beta) (pib:16)

Moved 5.6.0 changelog entry to 5.5.3.