Announcement

Announcement Module
Collapse
No announcement yet.

Threads_cached is zero

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Threads_cached is zero

    Even though thread_cache_size is 256 Threads_cached is 0.

    Code:
    mysql> show global variables like '%thread%';
    +-----------------------------------------+---------------------------+
    | Variable_name                           | Value                     |
    +-----------------------------------------+---------------------------+
    | innodb_purge_threads                    | 1                         |
    | innodb_read_io_threads                  | 64                        |
    | innodb_thread_concurrency               | 24                        |
    | innodb_thread_sleep_delay               | 0                         |
    | innodb_write_io_threads                 | 64                        |
    | max_delayed_threads                     | 20                        |
    | max_insert_delayed_threads              | 20                        |
    | myisam_repair_threads                   | 1                         |
    | performance_schema_max_thread_classes   | 50                        |
    | performance_schema_max_thread_instances | 612                       |
    | thread_cache_size                       | 256                       |
    | thread_concurrency                      | 10                        |
    | thread_handling                         | one-thread-per-connection |
    | thread_pool_high_prio_mode              | transactions              |
    | thread_pool_high_prio_tickets           | 4294967295                |
    | thread_pool_idle_timeout                | 60                        |
    | thread_pool_max_threads                 | 100000                    |
    | thread_pool_oversubscribe               | 3                         |
    | thread_pool_size                        | 24                        |
    | thread_pool_stall_limit                 | 500                       |
    | thread_stack                            | 262144                    |
    | thread_statistics                       | OFF                       |
    | wsrep_slave_threads                     | 1                         |
    +-----------------------------------------+---------------------------+
    Code:
    mysql> show global status like '%threads_%';
    +-------------------+--------+
    | Variable_name     | Value  |
    +-------------------+--------+
    | Threads_cached    | 0      |
    | Threads_connected | 3      |
    | Threads_created   | 223544 |
    | Threads_running   | 1      |
    +-------------------+--------+
    Any reason you could think why server is not caching threads?

  • #2
    Hi,

    As per the doc, "When a client disconnects, the client's threads are put in the cache if there are fewer than thread_cache_size threads there."
    https://dev.mysql.com/doc/refman/5.5...ead_cache_size

    So, thread will be only cached when client/thread disconnected. As per above result, you have 3 threads_connected. Just close any of the client and check, it will be cached.

    Comment


    • #3
      We have recently upgraded two clusters to 5.6 and after upgrade we are observing Threads_cached as zero.

      MySQL Tuner report.
      Code:
      [!!] Thread cache hit rate: 0% (231K created / 231K connections)
      Node1
      Code:
      mysql> show global status like '%threads_%';
      +-------------------+--------+
      | Variable_name     | Value  |
      +-------------------+--------+
      | Threads_cached    | 0      |
      | Threads_connected | 4      |
      | Threads_created   | 305604 |
      | Threads_running   | 1      |
      +-------------------+--------+
      Node2
      Code:
      +-------------------+-------+
      | Variable_name     | Value |
      +-------------------+-------+
      | Threads_cached    | 0     |
      | Threads_connected | 12    |
      | Threads_created   | 18525 |
      | Threads_running   | 1     |
      +-------------------+-------+
      Node3
      Code:
      +-------------------+-------+
      | Variable_name     | Value |
      +-------------------+-------+
      | Threads_cached    | 0     |
      | Threads_connected | 9     |
      | Threads_created   | 32066 |
      | Threads_running   | 2     |
      +-------------------+-------+
      This is with all nodes in both clusters.

      Comment


      • #4
        i have same issue with u, i set thread_cache_size = 10 or thread_cache_size=100 or thread_cache_size=1000. they are the same, the status of Threads_cached is always zero.
        did u find solution of this issue? please help me if u can solve it. i use same version , 5.6. before this i used 5.5, and no issue on 5.5.
        thank you

        Comment


        • #5
          Hi,

          Tested locally with 5.6.16, it seems, you have to connect more than thread_cache_size once, then you'll see cache used. As soon as we have more concurrently running threads than thread_cache_size, we'll see it >0 Try to set it 10 or 20 and then test it with mysqlslap like this.

          mysqlslap -uroot -p --create-schema=test --concurrency=20 --no-drop --number-of-queries=1000 --query="select 1"

          Comment


          • #6
            Hi,
            i use version 5.6.15-56. i set thread_cache_size=5 and then i try test with mysqlslap. after that i see status:
            +-------------------+-------+
            | Variable_name | Value |
            +-------------------+-------+
            | Threads_cached | 0 |
            | Threads_connected | 25 |
            | Threads_created | 43243 |
            | Threads_running | 3 |
            +-------------------+-------+
            4 rows in set (0.00 sec)

            Threads_cached is still zero.
            so what i have to do now?

            Comment


            • #7
              Hello?
              there is no solution for this issue?

              Comment


              • #8
                Hi,

                I have checked on both Percona Server 5.6.15 and 5.6.16 versions. I'm really confused that why you can't able to do it. check below log

                On 5.6.16 :

                nilnandan@Nil-Dell-XPS:~$ mysql -uroot -p
                Enter password:
                Welcome to the MySQL monitor. Commands end with ; or \g.
                Your MySQL connection id is 38
                Server version: 5.6.16-64.0-553.saucy (Ubuntu)

                Copyright (c) 2009-2014 Percona LLC and/or its affiliates
                Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

                Oracle is a registered trademark of Oracle Corporation and/or its
                affiliates. Other names may be trademarks of their respective
                owners.

                Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

                mysql> show global variables like 'thread_cache%';
                +-------------------+-------+
                | Variable_name | Value |
                +-------------------+-------+
                | thread_cache_size | 8 |
                +-------------------+-------+
                1 row in set (0.00 sec)

                mysql> show global status like '%threads_%';
                +-------------------+-------+
                | Variable_name | Value |
                +-------------------+-------+
                | Threads_cached | 0 |
                | Threads_connected | 1 |
                | Threads_created | 1 |
                | Threads_running | 1 |
                +-------------------+-------+
                4 rows in set (0.00 sec)

                I run this on second session,

                nilnandan@Nil-Dell-XPS:~$ mysqlslap -uroot -p --create-schema=test --concurrency=20 --no-drop --number-of-queries=1000 --query="select 1"
                Enter password:
                Benchmark
                Average number of seconds to run all queries: 0.014 seconds
                Minimum number of seconds to run all queries: 0.014 seconds
                Maximum number of seconds to run all queries: 0.014 seconds
                Number of clients running queries: 20
                Average number of queries per client: 50

                nilnandan@Nil-Dell-XPS:~$

                Again check status.

                mysql> show global status like '%threads_%';
                +-------------------+-------+
                | Variable_name | Value |
                +-------------------+-------+
                | Threads_cached | 8 |
                | Threads_connected | 1 |
                | Threads_created | 22 |
                | Threads_running | 1 |
                +-------------------+-------+
                4 rows in set (0.00 sec)

                mysql>


                On 5.6.15,

                nilnandan@Nil-Dell-XPS:~/sandboxes/msb_5_6_15$ mysql -umsandbox -p --socket=/tmp/mysql_sandbox5615.sock
                Enter password:
                Welcome to the MySQL monitor. Commands end with ; or \g.
                Your MySQL connection id is 1
                Server version: 5.6.15-rel63.0 Percona Server with XtraDB (GPL), Release rel63.0, Revision 519

                Copyright (c) 2009-2014 Percona LLC and/or its affiliates
                Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

                Oracle is a registered trademark of Oracle Corporation and/or its
                affiliates. Other names may be trademarks of their respective
                owners.

                Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

                mysql>
                mysql> show global variables like 'thread_cache%';
                +-------------------+-------+
                | Variable_name | Value |
                +-------------------+-------+
                | thread_cache_size | 9 |
                +-------------------+-------+
                1 row in set (0.01 sec)

                mysql> show global status like '%threads_%';
                +-------------------+-------+
                | Variable_name | Value |
                +-------------------+-------+
                | Threads_cached | 0 |
                | Threads_connected | 1 |
                | Threads_created | 1 |
                | Threads_running | 1 |
                +-------------------+-------+
                4 rows in set (0.00 sec)

                Run this on second session,

                nilnandan@Nil-Dell-XPS:~/sandboxes/msb_5_6_15$ mysqlslap -umsandbox -p --socket=/tmp/mysql_sandbox5615.sock --create-schema=test --concurrency=20 --no-drop --number-of-queries=1000 --query="select 1"
                Enter password:
                Benchmark
                Average number of seconds to run all queries: 0.012 seconds
                Minimum number of seconds to run all queries: 0.012 seconds
                Maximum number of seconds to run all queries: 0.012 seconds
                Number of clients running queries: 20
                Average number of queries per client: 50

                nilnandan@Nil-Dell-XPS:~/sandboxes/msb_5_6_15$

                mysql> show global status like '%threads_%';
                +-------------------+-------+
                | Variable_name | Value |
                +-------------------+-------+
                | Threads_cached | 9 |
                | Threads_connected | 1 |
                | Threads_created | 21 |
                | Threads_running | 1 |
                +-------------------+-------+
                4 rows in set (0.00 sec)

                mysql>

                Comment


                • #9
                  here is mine:

                  mysql> show global variables like 'thread_cache%';
                  +-------------------+-------+
                  | Variable_name | Value |
                  +-------------------+-------+
                  | thread_cache_size | 8 |
                  +-------------------+-------+
                  1 row in set (0.01 sec)

                  mysql> show global status like '%threads_%';
                  +-------------------+--------+
                  | Variable_name | Value |
                  +-------------------+--------+
                  | Threads_cached | 0 |
                  | Threads_connected | 26 |
                  | Threads_created | 110280 |
                  | Threads_running | 2 |
                  +-------------------+--------+
                  4 rows in set (0.00 sec)


                  and on second session:

                  [root@db-1-ssd ~]# mysqlslap -uroot -p --create-schema=test --concurrency=20 --no-drop --number-of-queries=1000 --query="select 1"
                  Enter password:
                  Benchmark
                  Average number of seconds to run all queries: 0.021 seconds
                  Minimum number of seconds to run all queries: 0.021 seconds
                  Maximum number of seconds to run all queries: 0.021 seconds
                  Number of clients running queries: 20
                  Average number of queries per client: 50

                  [root@db-1-ssd ~]# mysql -p
                  Enter password:
                  Welcome to the MySQL monitor. Commands end with ; or \g.
                  Your MySQL connection id is 115198
                  Server version: 5.6.15-56 Percona XtraDB Cluster (GPL), Release 25.4, Revision 731, wsrep_25.4.r4043

                  Copyright (c) 2009-2013 Percona LLC and/or its affiliates
                  Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

                  Oracle is a registered trademark of Oracle Corporation and/or its
                  affiliates. Other names may be trademarks of their respective
                  owners.

                  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

                  mysql> show global status like '%threads_%';
                  +-------------------+--------+
                  | Variable_name | Value |
                  +-------------------+--------+
                  | Threads_cached | 0 |
                  | Threads_connected | 32 |
                  | Threads_created | 115343 |
                  | Threads_running | 5 |
                  +-------------------+--------+
                  4 rows in set (0.00 sec)


                  it is why i am confused. i follow your steps, but i get different result. threads_cached is still zero. i am frustrated now.

                  Comment


                  • #10
                    Hi,

                    Now, I came to know that you are using PXC (XtraDB Cluster) which you should mention earlier. Still, I have checked with the same version you are using but I can't able reproduce what you are saying...Definitely, you should check from your side that where you are running test and where you are checking. Are there multiple mysql instances on same server?

                    [root@percona-pxc56-1 mysql]# mysql -uroot -p
                    Enter password:
                    Welcome to the MySQL monitor. Commands end with ; or \g.
                    Your MySQL connection id is 4
                    Server version: 5.6.15-56 Percona XtraDB Cluster (GPL), Release 25.4, Revision 731, wsrep_25.4.r4043

                    Copyright (c) 2009-2013 Percona LLC and/or its affiliates
                    Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

                    Oracle is a registered trademark of Oracle Corporation and/or its
                    affiliates. Other names may be trademarks of their respective
                    owners.

                    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

                    mysql> show global variables like 'thread_cache%';
                    +-------------------+-------+
                    | Variable_name | Value |
                    +-------------------+-------+
                    | thread_cache_size | 9 |
                    +-------------------+-------+
                    1 row in set (0.00 sec)

                    mysql>
                    mysql> show global status like '%threads_%';
                    +-------------------+-------+
                    | Variable_name | Value |
                    +-------------------+-------+
                    | Threads_cached | 0 |
                    | Threads_connected | 3 |
                    | Threads_created | 3 |
                    | Threads_running | 1 |
                    +-------------------+-------+
                    4 rows in set (0.00 sec)

                    mysql> quit
                    Bye
                    [root@percona-pxc56-1 mysql]# mysqlslap -uroot -p --create-schema=test --concurrency=20 --no-drop --number-of-queries=1000 --query="select 1"
                    Enter password:
                    Benchmark
                    Average number of seconds to run all queries: 0.053 seconds
                    Minimum number of seconds to run all queries: 0.053 seconds
                    Maximum number of seconds to run all queries: 0.053 seconds
                    Number of clients running queries: 20
                    Average number of queries per client: 50

                    [root@percona-pxc56-1 mysql]# mysql -uroot -p
                    Enter password:
                    Welcome to the MySQL monitor. Commands end with ; or \g.
                    Your MySQL connection id is 206
                    Server version: 5.6.15-56 Percona XtraDB Cluster (GPL), Release 25.4, Revision 731, wsrep_25.4.r4043

                    Copyright (c) 2009-2013 Percona LLC and/or its affiliates
                    Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

                    Oracle is a registered trademark of Oracle Corporation and/or its
                    affiliates. Other names may be trademarks of their respective
                    owners.

                    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

                    mysql> show global status like '%threads_%';
                    +-------------------+-------+
                    | Variable_name | Value |
                    +-------------------+-------+
                    | Threads_cached | 8 |
                    | Threads_connected | 3 |
                    | Threads_created | 36 |
                    | Threads_running | 1 |
                    +-------------------+-------+
                    4 rows in set (0.01 sec)

                    Comment


                    • #11
                      Still facing this problem. I am also using Cluster.

                      Percona-XtraDB-Cluster-client-56-5.6.15-25.4.731.rhel6.x86_64
                      Percona-XtraDB-Cluster-server-56-5.6.15-25.4.731.rhel6.x86_64
                      Percona-XtraDB-Cluster-galera-3-3.3-1.207.rhel6.x86_64
                      Percona-Server-shared-compat-5.5.35-rel33.0.611.rhel6.x86_64
                      Percona-XtraDB-Cluster-shared-56-5.6.15-25.4.731.rhel6.x86_64
                      Server 1
                      Code:
                      mysql> show global status like '%threads_%';
                      +-------------------+---------+
                      | Variable_name     | Value   |
                      +-------------------+---------+
                      | Threads_cached    | 0       |
                      | Threads_connected | 14      |
                      | Threads_created   | 3284767 |
                      | Threads_running   | 2       |
                      +-------------------+---------+
                      4 rows in set (0.00 sec)
                      Server 2
                      Code:
                      mysql> show global status like '%threads_%';
                      +-------------------+---------+
                      | Variable_name     | Value   |
                      +-------------------+---------+
                      | Threads_cached    | 0       |
                      | Threads_connected | 57      |
                      | Threads_created   | 3237214 |
                      | Threads_running   | 1       |
                      +-------------------+---------+
                      4 rows in set (0.00 sec)
                      Surprisingly, it is working for third node.
                      Code:
                      mysql> show global status like '%threads_%';
                      +-------------------+-------+
                      | Variable_name     | Value |
                      +-------------------+-------+
                      | Threads_cached    | 57    |
                      | Threads_connected | 9     |
                      | Threads_created   | 66    |
                      | Threads_running   | 1     |
                      +-------------------+-------+
                      4 rows in set (0.00 sec)
                      All configurations on all three nodes are exactly same including thread_cache_size which is 256. We have haproxy solution in front, so that have same traffic (i.e. number of connections).

                      Comment


                      • #12
                        Hi,

                        As you are using HAproxy, it might be possible that each time connection goes to 3rd node.
                        Can you try to change "round robin" in HAproxy settings and then check?

                        Comment


                        • #13
                          If we see number of Threads_created, it shows a very high number which certifies that MySQL server is active and receiving many connections.

                          I am also attaching haproxy stats that shows that it is distributing connections correctly.

                          Code:
                           
                          Frontend 1 233 - 0 154 3000 4815359 26794882489 2910594723339 0 0 0 OPEN
                          db01 0 0 - 1 223 0 51 200 1592074 1592074 9698974961 994083732645 0 0 0 0 0 4d23h UP L7OK/200 in 12ms 1 Y - 0 0 0s -
                          db02 0 0 - 1 220 0 52 200 1539503 1539503 8723529847 944306623426 0 0 0 0 0 4d23h UP L7OK/200 in 13ms 1 Y - 0 0 0s -
                          db03 0 0 - 0 220 0 51 200 1683782 1683782 8372377681 972204367268 0 0 0 0 0 4d23h UP L7OK/200 in 13ms 1 Y - 0 0 0s -
                          Backend 0 0 1 233 0 154 3000 4815359 4815359 26794882489 2910594723339 0 0 0 0 0 0 4d23h UP 3 3 0 0 0s

                          Comment


                          • #14
                            Originally posted by jaigupta View Post
                            We have recently upgraded two clusters to 5.6 and after upgrade we are observing Threads_cached as zero.
                            no surprisingly, you upgraded only two clusters to 5.6. i think 3rd node, it is still 5.5, it is why your 3rd node has Threads_cached = 57, and the others = zero,
                            the difference is the version. so we get problem with 5.6

                            Comment


                            • #15
                              Originally posted by niljoshi View Post
                              Hi,

                              Now, I came to know that you are using PXC (XtraDB Cluster) which you should mention earlier. Still, I have checked with the same version you are using but I can't able reproduce what you are saying...Definitely, you should check from your side that where you are running test and where you are checking. Are there multiple mysql instances on same server?
                              there is no multiple mysql instance on each node. i have 3 nodes. each of them only has single mysql instance.

                              Comment

                              Working...
                              X