maria-discuss team mailing list archive

[Justin Swanhart <greenlion@xxxxxxxxx>]

Hi,

If a MyISAM table is not in the table_open_cache, it will be opened by the
query that needs the table.  To increase the chances of the table being in
the table_open_cache, increase the size to 4000 for a start (1/10 of your
current open files).

Be careful though.  Increasing the table_open_cache increases the amount of
time it takes to search the table cache, as the search is linear through
the structure that holds the table_cache.  It also increases the time for a
flush tables operation.

Also increase table_definition_cache.  This value is hash backed, so the
performance impact of increasing it is minimal.  I would increase it to
40000 since you have 40000 open files.

These value changes can be made online.

On Tue, Apr 21, 2015 at 9:14 AM, Honza Horak <hhorak@xxxxxxxxxx> wrote:

> On 04/21/2015 05:07 PM, Justin Swanhart wrote:
>
>> Hi,
>>
>> Are you using MyISAM or InnoDB?  What are the values of
>> table_open_cache, table_definition_cache and innodb_open_files?
>>
>
> It is a mixture of MyISAM and InnoDB, but we got complaints especially
> about the MyISAM tables.
>
> Options table_open_cache, table_definition_cache have default values, so
> 400, innodb_open_files is changed to 128.
>
> Honza
>
>  --justin
>>
>> On Tue, Apr 21, 2015 at 6:26 AM, Honza Horak <hhorak@xxxxxxxxxx
>> <mailto:hhorak@xxxxxxxxxx>> wrote:
>>
>>     Hey, with limited experiences with mariadb deployments, I'm
>>     wondering how many open files by the mysqld daemon is still sane
>>     (expected).
>>
>>     We have a customer that reports hundreds of thousands of open files
>>     with 600 connections (thread_pool_max_threads is used). Those
>>     processes seem rather stalled, but files in the datadir are often
>>     opened 40.000 times.
>>
>>     Is it even possible that one file is opened several times during
>>     processing one query? Or does it mean there are ~40.000 queries
>> running?
>>
>>     Version of MariaDB: 5.5.40
>>
>>     Honza
>>
>>     _______________________________________________
>>     Mailing list: https://launchpad.net/~maria-discuss
>>     Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>>     <mailto:maria-discuss@xxxxxxxxxxxxxxxxxxx>
>>     Unsubscribe : https://launchpad.net/~maria-discuss
>>     More help   : https://help.launchpad.net/ListHelp
>>
>>
>>