查询数据库连接数

[6 Mar 2006 8:45] Olaf van der Spek
Description:
| Threads_connected                 | 8         |
| Threads_created                   | 6         |

How can more threads be connected than created?

How to repeat:
-[6 Mar 2006 8:51] Andrey Hristov
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.[6 Mar 2006 8:56] Olaf van der Spek
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?[6 Mar 2006 9:16] Andrey Hristov
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).[6 Mar 2006 9:34] Valeriy Kravchuk
Have you performed FLUSH STATUS?[6 Mar 2006 9:37] Olaf van der Spek
No.[6 Mar 2006 10:59] Valeriy Kravchuk
Is there any known way to repeat this result?[8 Mar 2006 19:36] Olaf van der Spek
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.[9 Mar 2006 10:18] Valeriy Kravchuk
Please, try to repeat it. Any ideas on situations when this happens are also appreciated.[14 Mar 2006 8:59] Olaf van der Spek
I think it counts insert delayed threads as connected but not as created.[6 Apr 2006 11:03] Tomash Brechko
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--[6 Apr 2006 11:33] Valeriy Kravchuk
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.[14 Jan 2008 11:28] Bugs System
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, [e-mail] +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.[22 Jan 2008 17:40] Dmitry Lenev
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?[18 Mar 2008 0:56] Bugs System
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, [e-mail] +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.[18 Mar 2008 18:08] Bugs System
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, [e-mail] +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.[19 Mar 2008 12:03] Bugs System
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, [e-mail] +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.[19 Mar 2008 12:06] Davi Arnaut
Queued in 6.0-runtime[20 Apr 2008 13:01] Bugs System
Pushed into 6.0.6-alpha[21 Apr 2008 17:17] Paul DuBois
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.[20 Nov 2009 20:28] Konstantin Osipov
Queued into next-mr-runtime (5.6.0)[20 Nov 2009 20:30] Bugs System
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: [e-mail]/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.[25 Nov 2009 13:32] Bugs System
Pushed into 6.0.14-alpha (revid:[e-mail]-20091124194633-yc0achgq1ioyqzng) (version
source revid:[e-mail]-20091124194633-yc0achgq1ioyqzng) (merge vers: 6.0.14-alpha)
(pib:13)[25 Nov 2009 13:33] Bugs System
Pushed into 5.6.0-beta (revid:[e-mail]-20091124193905-3iyzegd75k4givuz) (version
source revid:[e-mail]-20091120203000-lky3jqo7r35s24ps) (merge vers: 5.6.0-beta)
(pib:13)[25 Nov 2009 14:44] Paul DuBois
Noted in 5.6.0 changelog.

Already fixed in 6.0.x.[6 Mar 2010 11:03] Bugs System
Pushed into 5.5.3-m3 (revid:[e-mail]-20100306103849-hha31z2enhh7jwt3) (version source
revid:[e-mail]-20091125142014-7asc9sj33gzki0ym) (merge vers: 5.6.0-beta)
(pib:16)