I'm trying to increase to 9000 but MariaDB sets it at 4895 automatically.

Here is my simple config https://pastebin.com/raw/4eFiuFSv

MySQLTuner output:

Control warning line(s) into /var/lib/mysql/cp.goal.ge.err file
Control error line(s) into /var/lib/mysql/cp.goal.ge.err file
Restrict Host for user@% to user@SpecificDNSorIp
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64:

Beware that open_files_limit (10000) variable

should be greater than table_open_cache (4895)

Optimize queries and/or use InnoDB to reduce lock wait
Consider installing Sys schema from https://github.com/mysql/mysql-sys

Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_size (> 125M)
    table_open_cache (> 4895)
  • Look in your log file /var/lib/mysql/cp.goal.ge.err to see if there are any references to this variable. – eroomydna Apr 12 '18 at 10:05
  • @eroomydna Looked for it. tail -f /var/lib/mysql/cp.goal.ge.err and restarting mariadb server several times to see if there is any warnings or similar output but nothing goes here :( – user3446664 Apr 12 '18 at 10:06
  • Have you tried setting this variable dynamically? E.g. log in with the mysql client and execute: SET GLOBAL table_open_cache=9000; Does that work? – dbdemon Apr 12 '18 at 18:21
  • @dbdemon Yes. It works. But as you know it resets this value after restarting. This is main reason I need to set it in my.cnf – user3446664 Apr 13 '18 at 0:02
  • @user3446664 Use ulimit -a to find out what the limit is for Open Files. If you know you want table_open_cache=9000, then other needs for open files such as open_table_definitions. Suggest you raise the limit to liniux with ulimit -n 18000 to leave some for other purposes. Posting the ENTIRE MySQL Tuner report would be helpful, even though some of their recommendations should be ignored. – Wilson Hauck Jun 13 '18 at 19:47

I can see 3 different reasons why MariaDB might reset this variable. In order of most to least likely explanation:

  1. You have another .cnf file where the table_open_cache variable is being set to 4895.
  2. The MariaDB Systemd unit file (or SysV init script on older Linux versions) is overwriting the variable value you've set.
  3. You're running a non-standard MariaDB server that's been compiled with a hard-coded value for table_open_cache. (Unlikely!)

Additionally, it's possible that another software (such as cPanel) might change the variable dynamically after MariaDB has started.

To see which options the MariaDB server will get from the option files, run:

mysqld --print-defaults 

To find all the places where the MariaDB server is looking for .cnf files, run:

mysqld --verbose --help | grep -A 1 'Default options are read'

You can then grep each of these files to see if you find any instances of table_open_cache or table-open-cache.

For reference, you should be able to see the current value of the variable with:

show global variables like 'table_open_cache';

And you may also want to consult this page in the MariaDB Knowledge Base: Optimizing table_open_cache.

  • Thanks. Here is output of commands pastebin.com/raw/0nCTCHrt – user3446664 Apr 16 '18 at 1:04
  • 1
    Sorry not to mentioning it, but there is cPanel installed on this server. So, I have found warning message in log file. Something is changing open_cache and open_files_limit options. Reporting it to cPanel – user3446664 Apr 16 '18 at 1:30
  • @user3446664 Ah, so maybe cPanel is changing the variables after MariaDB has started. Very cunning! I'll add that to my answer to make it more helpful to others. – dbdemon Apr 16 '18 at 7:11
  • 1
    #4: the OS's ulimit is inherited into MySQL and leads to capping table_open_cache. – Rick James Apr 17 '18 at 19:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.