maria-discuss team mailing list archive

[Justin Swanhart <greenlion@xxxxxxxxx>]

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@xxxxxxxxxxx> 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@xxxxxxxxxxx>
> 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@xxxxxxxxxxx
> >
> > > 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@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
> > > > > > >>
> > > > > > >>
> > > > > > >>
> > > > >
> > > > > > _______________________________________________
> > > > > > Mailing list: https://launchpad.net/~maria-discuss
> > > > > > Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> > > > > > Unsubscribe : https://launchpad.net/~maria-discuss
> > > > > > More help   : https://help.launchpad.net/ListHelp
> > > > >
> > > > >
> > >
>