maria-discuss team mailing list archive

[Rohan M C <rohanmc@xxxxxxxxx>]

Hi,

We had similar issues and we went through a couple of exercises to
determine how file descriptors work with both mysql and mariadb. Please
correct me if I'm wrong...this is what I've seen based on a number of
repeated tests.

For innodb, regardless of how many queries you have or any other
parameters, the number of fds the daemon will open is bound strictly by
innodb_open_files. It won't exceed this and it won't reduce either once you
hit the max until you restart the daemon

For myisam, it;s much trickier...table_open_cache controls not the number
of file descriptors but the number of "tables". So from a system level, the
number of file descriptors open for a myisam table would be  2 X number of
partitions (because each partition consists of a myi file and an myd file,
unlike innodb which is a single ibd file). Furthermore, the number of
copies of each table that mariadb/mysql open depend on the
concurrency/joins. You can test this by writing a query that joins the same
table 4 times..in this case the number of file descriptors open is 4 X 2 X
number of partitions. The same holds for concurrency as well. If you have 3
very slow queries hitting the same myisam table, the daemon opens up 3
copies of each file belonging to that table..so a total of 3 X 2 X number
of partitions. (for innodb, the number of copies of each file descriptor
open is always 1...so the number of fds is always = number of innodb
partitons).

Another caveat for myisam is that the number represents not the number of
tables, but the total number of "copies" that are permissible to be open.
So if table_open_cache is 10, you can have 10 different tables or 10 copies
of the same table or a mix. table_open_cache doesn't care how many
partitons there are, so if each table has a thousand partitions, then you
can find that with a table_open_cache of 10, you have a maximum of 10,000
fds open. Also keep in mind that table_open_cache affects innodb tables as
well as myisam tables (not sure if this is intended, but you can try this
by setting table_open_cache to 1 and doing a show create table on some
innodb table...it will close myisam fds)

You can test the number of copies open per table with a simple shell script
or command

/usr/sbin/lsof -p `cat /var/run/mysqld/mysqld.pid` | sed 1d | awk '{ print
$9 }' | cut -d "/" -f 5 | grep -v '.MYI' | sort -n | uniq -c | cut -d '.'
-f 1 | cut -d '#' -f 1 | uniq -c | awk '{ print $1*$2,$0 }' | sort -n -r

first column is total number of fds, second column is number of partitions,
3rd column is the number of copies of each file.

Also, for myisam you can close open file descriptors by doing a "flush
tables" but this is a little risky as we've seen unpredictable behavior
when flush runs on a system under load...esp if tables are being repaired,
it has a small chance of messing things up.

A safer but hackier option to reduce file descriptor count is to lower the
value of table_open_cache..this is a dynamic variable and can be lowered,
at which point the daemon eventually closes file descriptors until the max
is reached (i tested this by continuously hitting the db with very quick
and light queries..the fds kept getting closed until the num tables reached
table_open_cache. I usually do a show create table on some innodb table)

Thanks,
Rohan

On Tue, Apr 21, 2015 at 10:43 AM, Justin Swanhart <greenlion@xxxxxxxxx>
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
>>
>>
>
> _______________________________________________
> 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
>
>