Hi,

table_definition_cache has existed since 5.1 and scales fine.  table_open_cache has negative scalability in all versions of mysql (mariadb?), until 5.6 when table_open_cache can be partitioned.  I got conflicting reports if mariadb implemented some table_open_cache method that is different from 5.5 because if it is implemented as in 5.5 it is negatively scalable with that workload.

--Justin

On Wed, Apr 22, 2015 at 12:10 AM, Sergey Vojtovich <svoj@mariadb.org> wrote:
Hi Justin,

this Percona article mentions table cache that was split into table definition
cache and table open cache. IIRC this was implemented in 5.1 and that's something
MariaDB has.

Partitioned table cache is different story, it appeared in MySQL 5.6 and is
supposed to solve another problem. As Sergei mentioned MariaDB solved this
problem differently.

Still there is no linear search in table cache. But there is linear search
inside MyISAM open tables list, which may cause performance degradation you're
referring to. Btw this MyISAM issue can be fixed more or less easily now.

But all this is releveant only if table cache can't fit all requests. Table
cache eviction and table reopen are expensive. Thus my suggestion was to try
to avoid eviction as much as possible.

Regards,
Sergey

On Tue, Apr 21, 2015 at 01:42:17PM -0700, Justin Swanhart wrote:
> Hi,
>
> It is in fact, negatively scaleable without partitioning it:
> http://www.percona.com/blog/2009/11/16/table_cache-negative-scalability/
>
> It is easy to reproduce.
>
> On Tue, Apr 21, 2015 at 10:54 AM, Sergey Vojtovich <svoj@mariadb.org> wrote:
>
> > Hi!
> >
> > I think original question was about 5.5.
> >
> > MySQL 5.6 has partitioned table cache, but rather to overcome the negative
> > scalability aspect of increasing number of concurrent connections.
> >
> > No version of MariaDB has partitioned table cache. At least yet.
> >
> > Regards,
> > Sergey
> >
> > On Tue, Apr 21, 2015 at 10:43:05AM -0700, Justin Swanhart wrote:
> > > 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
> > > >
> > > >
> >