Bug #11269 'table_cache' Entry In my.ini Not Being Used
Submitted: 12 Jun 2005 12:09 Modified: 24 Aug 2005 16:19
Reporter: Martin Hill Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.12a-nt OS:Microsoft Windows (Windows Server 2003 Standard)
Assigned to: Jim Winstead CPU Architecture:Any

[12 Jun 2005 12:09] Martin Hill
Description:
The 'table_cache' variable is ignored in my.ini. However, if the query 'SET GLOBAL table_cache=X' is executed from the mySQL client, the table_cache variable is updated. I have tested this on two Windows Server 2003 Standard Edition servers and both have the same problem. The my.ini file is definately being used by mySQL as I can change other entries in my.ini and the changes appear in the system variables.

How to repeat:
Set table_cache=X in my.ini on a Windows Server 2003 Standard Edition server with the latest Windows updates running mySQL 4.1.12a-nt (bug may be present in prior versions) and then check the value of the system variable table_cache.

Suggested fix:
Make mySQL use the table_cache entry in my.ini.
[13 Jun 2005 8:28] Vasily Kishkin
Tested on Win 2000 Sp4, MySQL server 4.1.13

mysql:

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

mysql> show variables like "table_cache";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| table_cache   | 969   |
+---------------+-------+
1 row in set (0.08 sec)

mysql> set global table_cache = 2000;
Query OK, 0 rows affected (0.02 sec)

mysql> show variables like "table_cache";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| table_cache   | 2000  |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

my.ini:

[mysqld]
table_cache=2000
[15 Jun 2005 8:52] Vasily Kishkin
Additional information - if I start mysqld with "--print-default" option I get follow:

D:\mysql-4.1.13\client_debug>mysqld-debug.exe --print-defaults
mysqld-debug.exe would have been started with the following arguments:
--table_cache=2000 --ft_min_word_len=3 --shared-memory --shared-memory-base-name=MySQL
[22 Jul 2005 2:23] Jim Winstead
And if you check the log, you'll see a warning like this in the log:

050721 23:41:12 [Warning] Changed limits: max_open_files: 2048  max_connections: 100  table_cache: 969
[24 Aug 2005 16:19] Jim Winstead
As noted, the adjustment of the value on startup is intended, and results in a warning in the error log.
[24 Aug 2005 17:01] Martin Hill
No explanation has been given as to why the table_cache change is intentional and also why this is not a bug.
[10 Oct 2005 17:17] DZ Jay
Can someone provide (or point to a resource) an explanation as to why this change occurs and is expected?
[11 Oct 2005 7:09] Sergei Golubchik
MySQL tries to increase the [OS imposed] limit of open file descriptors to match user-requested 
max_open_files, max_connections, and table_cache. Then it queries the actual value of the limit, and if it's lower than what was requested, MySQL adjusts max_open_files, max_connections, and table_cache accordingly.