Bug #85682 [Warning] option 'thread_cache_size': value -1 adjusted to 0

• Submitted: 29 Mar 2017 11:54
• Modified: 30 Mar 2017 7:24
• Reporter: kfpanda kf
• Status: Not a Bug
• Category: MySQL Server: Options
• Severity: S3 (Non-critical)
• Version: 5.7.17
• OS: Any
• CPU Architecture: Any

[29 Mar 2017 11:54] kfpanda kf

Description:
Hello,
   It'is said that the default value of variable thread_cache_size is -1(autosized).
  https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_thread_cache_s...
   
mysql> set global thread_cache_size = -1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------+
| Level   | Code | Message                                           |
+---------+------+---------------------------------------------------+
| Warning | 1292 | Truncated incorrect thread_cache_size value: '-1' |
+---------+------+---------------------------------------------------+
1 row in set (0.00 sec)

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

How to repeat:
mysql> set global thread_cache_size = -1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------+
| Level   | Code | Message                                           |
+---------+------+---------------------------------------------------+
| Warning | 1292 | Truncated incorrect thread_cache_size value: '-1' |
+---------+------+---------------------------------------------------+
1 row in set (0.00 sec)

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

Suggested fix:
I hope that it is autosized the value of thread_cache_size when i set thread_cache_size -1.

[30 Mar 2017 7:24] MySQL Verification Team

Hello kfpanda kf,

Thank you for the report.
Imho autosizing here means that the user does not have to do it. Per manual "The default value is based on the following formula, capped to a limit of 100:

8 + (max_connections / 100)

Also, allowed default value is between 0 and 16384

In my environment:

root@localhost [(none)]>  set global thread_cache_size = DEFAULT;
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]>  show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 9     |
+-------------------+-------+
1 row in set (0.01 sec)

root@localhost [(none)]> show global variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

root@localhost [(none)]> select 8+(151/100);
+-------------+
| 8+(151/100) |
+-------------+
|      9.5100 |
+-------------+
1 row in set (0.00 sec)

Thanks,
Umesh