1

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)
5
  • 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, 2018 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 :( Apr 12, 2018 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, 2018 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 Apr 13, 2018 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. Jun 13, 2018 at 19:47

1 Answer 1

1

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.

4
  • Thanks. Here is output of commands pastebin.com/raw/0nCTCHrt Apr 16, 2018 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 Apr 16, 2018 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, 2018 at 7:11
  • 1
    #4: the OS's ulimit is inherited into MySQL and leads to capping table_open_cache.
    – Rick James
    Apr 17, 2018 at 19:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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