Hi,

Is that a MariaDB table_cache improvement?  MySQL 5.6 has partitioned table cache to overcome the negative scalability aspect of increasing table cache.

--Justin

On Tue, Apr 21, 2015 at 10:34 AM, Sergey Vojtovich <svoj@mariadb.org> wrote:
Hi!

On Tue, Apr 21, 2015 at 10:11:12AM -0700, Justin Swanhart wrote:
> 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).
Same applies to InnoDB tables, especially with low innodb_file_per_table and
innodb_open_files. But MyISAM needs to open a 2x more files indeed.

>
> 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.
Table cache is doubly-linked list, but there is no table cache search as such:
we either take front element or push front element. So increased table_open_cache
doesn't have any negative effect on table cache performance. FLUSH TABLES will be
slower indeed.

To get best performance table_open_cache should be something like:
number of hot tables * average number of concurrent connections.

>
> 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.
A good value for table_definition_cache is number of hot tables + some reserve
(e.g. 100).

Regards,
Sergey

>
> These value changes can be made online.
>
> On Tue, Apr 21, 2015 at 9:14 AM, Honza Horak <hhorak@redhat.com> 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@redhat.com
> >> <mailto:hhorak@redhat.com>> 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@lists.launchpad.net
> >>     <mailto:maria-discuss@lists.launchpad.net>
> >>     Unsubscribe : https://launchpad.net/~maria-discuss
> >>     More help   : https://help.launchpad.net/ListHelp
> >>
> >>
> >>

> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp