Re: [Maria-discuss] How many open files by daemon is still sane?

21 Apr 2015

      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  wrote:
...
Hi!
...
Hi,
If a MyISAM table is not in the table_open_cache, it will be opened by
On Tue, Apr 21, 2015 at 10:11:12AM -0700, Justin Swanhart wrote:
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
...
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
of
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  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 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