MySQL Bugs: #45475: TABLE_OPEN_CACHE_MIN should be same value as TABLE_OPEN_CACHE

Bug #45475	TABLE_OPEN_CACHE_MIN should be same value as TABLE_OPEN_CACHE_DEFAULT
Submitted:	12 Jun 2009 17:03	Modified:	21 Jul 2009 18:41
Reporter:	Vincent Carbone	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S5 (Performance)
Version:	5.4.0-Beta	OS:	Solaris (snv_106 X86)
Assigned to:	Mikael Ronström	CPU Architecture:	Any
Tags:	5.4-Beta, table_open_cache, TABLE_OPEN_CACHE_DEFAULT, TABLE_OPEN_CACHE_MIN

Description:
In MySQL 5.4-Beta TABLE_OPEN_CACHE_DEFAULT was increased from 64 to 400 to reduce contention on the table cache. TABLE_OPEN_CACHE_MIN was not increased also. The impact of this occurs in init_common_variables in sql/mysqld.cc. If less file handles than were explicity requested cannot be satisfied max_connections and table_open_cache will be recalculated. table_open_cache will be decreased but not below TABLE_OPEN_CACHE_MIN. Currently that means table_open_cache can be resized down to the old default of 64.

How to repeat:
This condition was found on Solars (snv_106 X86) but probably applies to Unix and Linux in general.

1. login as mysql (or a non-root user that does not have privilege to increase hard resource limits)

2. Set table_open_cache=4096 and max_connections=2049

3. From the command shell set the soft and hard file descriptor limit to 1024
# ulimit -n 1024

4. mysqld_safe &

5. Check error log:
090611 10:52:09 [Warning] Changed limits: max_open_files: 1024  max_connections: 886  table_cache: 64

Suggested fix:
In client/mysql_priv.h:
-- #define TABLE_OPEN_CACHE_MIN    64
++ :#define TABLE_OPEN_CACHE_MIN  400

In sql/mysql_priv.h:
-- #define TABLE_OPEN_CACHE_MIN    64
++ :#define TABLE_OPEN_CACHE_MIN  400

Thank you for the problem report.

Set TABLE_OPEN_CACHE_MIN (64 before) and TABLE_DEFAULT_CACHE_MIN (256 before) to 400

Pushed to 5.4.2 on July 2/2009

Noted in 5.4.2 changelog.

The minimum value for table_open_cache was increased from 64 to 400.

table_cache broken in databases 5.1 and up to 5.6.20. settings won't work in my.ini when starting databases. There is no table_open_cache setting or table_cache variable in database to set. It will give you unknown variable when
trying to use these names in the my.ini. if you try to set these with these names it will break your database and set the variable permanently so you can't start your database. There is these settings but I have not tested them. Had to locate the proer variables you can set but I don't know if it will break your database. please test these variables. Had to use workbench to find them and see performance values in database when running. A good my.ini file looks like this:

my.ini: You can have customer use this after testing variables in database when starting services.

install goes like this:
sc delete "mysql" //deletes my sql service on windows
mysqld install mysql // install service for mysql
services.msc // use run on windows to start up services on windows

this above will start your database up. You will have to grant permissions to localhost and set your password once started.

here is the my.ini I used to start database up given below I tested:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[mysqld]
character-set-server=latin1
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 175M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
log-error=C:\Program Files (x86)\MySQL\logs\mysql.log
max_connections=100
thread_cache_size=10
# These are commonly set, remove the # and set as required.
basedir = C:\Program Files (x86)\MySQL
datadir = C:\Program Files (x86)\MySQL\data
port = 3306
server_id = 0
query_cache_size=170M

myisam_max_sort_file_size=20G
myisam_sort_buffer_size=10M
key_buffer_size=10M
read_buffer_size=164K
#read_rnd_buffer_size=656K
sort_buffer_size = 2M
#mysql.allow_persistent=on
join_buffer_size = 128M
#max_allowed_packet max_connections Wait_timeout=1M
innodb_additional_mem_pool_size=170M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=170M
innodb_log_file_size=10M
explicit_defaults_for_timestamp = TRUE
interactive_timeout=300
wait_timeout=300
session.wait_timeout=28800
session.interactive_timeout=28800
key_buffer=256M
max_allowed_packet=1M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

________________________________________________________

Below this line is the extra variables for settings table_cache that breaks the database because table_cache does not exist in database:

Only these:

Table_locks_immediate=249
Table_locks_waited=0
Table_open_cache_hits=308
Table_open_cache_misses=73
Table_open_cacheOverflows=0

you can set them with this:

either set variable or set global variable:

Solution below:
example:
set Table_locks_immediate=300; or set global Table_locks_immediate=300;
otherwise you can fix table_cache so it doesn't break database if you choose this variable table_cache. But you will need to file a bug for broken variable.

I used a table cache value over 980.  This is the value that broke the database.  I would try higher values to test table_cache out. Otherwise this is not logical mccoy. Will try your tests out to see where I end up....the log reported unknown table_cache=980 can not change set value in log file.... wanted to just  justify settings...