maria-discuss team mailing list archive

[Justin Swanhart <greenlion@xxxxxxxxx>]

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
> > >
> > >
>