Bug #91826 Explicitly setting thread_cache_size=-1 (autosizing) doesn't work [ Bug #91806 ]

• Submitted: 30 Jul 2018 7:40
• Modified: 30 Jul 2018 11:44
• Reporter: lalit Choudhary
• Status: Not a Bug
• Category: MySQL Server: Options
• Severity: S3 (Non-critical)
• Version: 5.6,5.7,8.0
• OS: Any
• CPU Architecture: Any
• Tags: thread_cache_size

[30 Jul 2018 7:40] lalit Choudhary

Description:
Creating this new bugs, since not able to reopen original Bug#91806.

Issue:

setting "thread-cache-size=-1" in my.cnf which should cause an automatic value for thread_cache_size according to https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_thread_cache_s...

However when set to "-1" "thread_cache_size" showing value as 0 

mysql> SHOW VARIABLES LIKE "thread_cache_size";
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 0 |
+-------------------+-------+
1 row in set (0.00 sec)

As per MySQL Official Manual, we don't assign -1 value for thread_cache_size

Default Value (>= 5.6.8)    -1 (signifies autosizing; do not assign this literal value)

In this case, user should not allow to set -1 value explicitly and even if it is allowing it for some reasons at least it should log this change as warning/note in MySQL server log.
Above ask may be a feature request?

How to repeat:
Steps: 
1. Add "thread-cache-size=-1" in my.cnf
2. Restart mysqld server
3. execute SHOW VARIABLES LIKE "thread_cache_size";

Expected value should be -1 as per my.cnf setting, but it showing value as 0.

mysql> SHOW VARIABLES LIKE "thread_cache_size";
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 0 |
+-------------------+-------+
1 row in set (0.00 sec)

Suggested fix:
In this case, user should not allow to set -1 value explicitly and even if it is allowing it for some reasons at least it should log this change as warning/note in MySQL server log.
Above ask may be a feature request?

[30 Jul 2018 11:44] MySQL Verification Team

Hello Lalit,

Thank you for the report.
Imho, as our Miguel rightly pointed in your earlier report i.e autosizing here means that the user does not have to do it and same has been explicitly mentioned i.e "-1 (signifies autosizing; do not assign this literal value)".
Moreover, the default value is based on the following formula, capped to a limit of 100.

Also, if you see in server's error log, message is already logged about the adjusted value.

rm -rf 91825
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/91825 --log-error-verbosity=3
bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/91825 --core-file --socket=/tmp/mysql_ushastry.sock --port=3333 --log-error=$PWD/91825/log.err --thread-cache-size=-1 2>&1 &

-- 8.0.12

cat 91825/log.err
2018-07-30T11:25:09.593463Z 0 [Warning] [MY-011071] [Server] option 'thread_cache_size': value -1 adjusted to 0
2018-07-30T11:25:09.593679Z 0 [System] [MY-010116] [Server] /export/umesh/server/binaries/GABuilds/mysql-8.0.12/bin/mysqld (mysqld 8.0.12) starting as process 5052
2018-07-30T11:25:10.031937Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2018-07-30T11:25:10.054719Z 0 [System] [MY-010931] [Server] /export/umesh/server/binaries/GABuilds/mysql-8.0.12/bin/mysqld: ready for connections. Version: '8.0.12'  socket: '/tmp/mysql_ushastry.sock'  port: 3333  MySQL Community Server - GPL.

-- 5.7.23

cat 91825/log.err
2018-07-30T11:28:31.777932Z 0 [Warning] option 'thread_cache_size': value -1 adjusted to 0
[...]
2018-07-30T11:28:31.903819Z 0 [Note] bin/mysqld: ready for connections.
Version: '5.7.23'  socket: '/tmp/mysql_ushastry.sock'  port: 3333  MySQL Community Server (GPL)

-- 5.6.41

rm -rf 91825/
scripts/mysql_install_db --basedir=$PWD --datadir=$PWD/91825 -v
bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/91825 --core-file --socket=/tmp/mysql_ushastry.sock   --port=3333 --log-error=$PWD/91825/log.err --thread-cache-size=-1 2>&1 &

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.6.41: bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/91825 --core-file --socket=/tmp/mysql_ushastry.sock   --port=3333 --log-error=$PWD/91825/log.err --thread-cache-size=-1 2>&1 &
[1] 5756
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.6.41: 2018-07-30 13:31:06 0 [Warning] option 'thread_cache_size': value -1 adjusted to 0

Also, see Bug #85682

thanks,
Umesh