Wrong warning about table_open_cache

[makhomed]

Wrong warning about table_open_cache:

...
General recommendations:
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
...

You point to article http://bit.ly/1mi7c4C - this is MyISAM issue only
and it was fixed in MySQL 5.7.9 and all newer MySQL/PerconaServer/MariaDB versions,
See more details here: https://bugs.mysql.com/bug.php?id=49177

I am use Percona Server 5.7.20-18, so this issue is fixed already,
also I am not use MyISAM tables at all - I use only InnoDB tables.

So you should not display warning
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
if this bug already fixed in used version of MySQL Server, or if MyISAM tables are not used.

[jmrenouard]

Hi @makhomed

Can you tell me what kind of message can be displayed in this case ?

No warning if version > 5.7.9
Warning in all others cases.

Thanks for your feedback.

[makhomed]

Now MySQLTuner display such warning:

General recommendations:
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Beware that open_files_limit (131242) variable 
    should be greater than table_open_cache (65536)

If MySQL version > 5.7.9 line
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
should not be displayed, because this bug already fixed in MySQL. Also may be MySQLTuner should print warning, what this article http://bit.ly/1mi7c4C is only about MyISAM specific performance bug, and InnoDB is not affected, and what this bug already fixed in MySQL version 5.7.9.

Something like this for affected MySQL versions:

General recommendations:
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
        This is MyISAM only table_cache scalability problem, InnoDB not affected.
        See more details here: https://bugs.mysql.com/bug.php?id=49177
        This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
    Beware that open_files_limit (131242) variable 
    should be greater than table_open_cache (65536)

And something like this for not affected MySQL versions:

General recommendations:
    Increase table_open_cache gradually to avoid file descriptor limits
    Beware that open_files_limit (131242) variable 
    should be greater than table_open_cache (65536)

[jmrenouard]

Thanks for your feedback !

[davidfavor]

Be great if this diagnostic also provided the target setting for table_open_cache also.

Thanks for considering adding this.

[davidfavor]

Rereading this ticket.

If I understand, if I'm running MariaDB-10.3.7, then when presented with the table_open_cache (> 64) message, I really should increase this value till message disappears.

So disregard then http://bit.ly/1mi7c4C message completely.

Let me know if I'm understanding this correctly.

Thanks.

[davidfavor]

Be great if someone can answer this, so stated another way, If I receive this message...

table_open_cache (> 64)

Using only InnoDB storage engines, increasing table_open_cache to clear this message, will increase performance.

Yes/No?

[makhomed]

@davidfavor, MySQLTuner GitHub is not support forum for MariaDB.

You can google for "mariadb table_open_cache" and first result is article
from KB https://mariadb.com/kb/en/library/optimizing-table_open_cache/

From this article you can find answers for all your questions about MariaDB and table_open_cache.

[davidfavor]

Got it.

Thanks!

[davidfavor]

So for MariaDB, increasing table_open_cache does improve performance, at the expense of memory.

[jmrenouard]

Hi @davidfavor @makhomed

I push a new recommandations line with mariaDB KB link.
push( @GeneralRec,
"Read this before increasing for MariaDB"
. " https://mariadb.com/kb/en/library/optimizing-table_open_cache/");