maria-discuss team mailing list archive

[Justin Swanhart <greenlion@xxxxxxxxx>]

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