[Maria-discuss] How many open files by daemon is still sane?
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
Hi, Are you using MyISAM or InnoDB? What are the values of table_open_cache, table_definition_cache and innodb_open_files? --justin On Tue, Apr 21, 2015 at 6:26 AM, Honza Horak <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 Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
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@redhat.com <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
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). 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. 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. These value changes can be made online. On Tue, Apr 21, 2015 at 9:14 AM, Honza Horak <hhorak@redhat.com> 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@redhat.com <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
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@redhat.com> 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@redhat.com <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
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@mariadb.org> 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 <hhorak@redhat.com> 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@redhat.com <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
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@mariadb.org> 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 <hhorak@redhat.com> 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@redhat.com <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
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@mariadb.org> 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
Hi,
Is that a MariaDB table_cache improvement? MySQL 5.6 has partitioned
cache to overcome the negative scalability aspect of increasing table cache.
--Justin
On Tue, Apr 21, 2015 at 10:34 AM, Sergey Vojtovich <svoj@mariadb.org> 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 of
time it takes to search the table cache, as the search is linear
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
On Tue, Apr 21, 2015 at 10:43:05AM -0700, Justin Swanhart wrote: table through 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@redhat.com>
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@redhat.com <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
Hi, Justin! On Apr 21, Justin Swanhart wrote:
It is in fact, negatively scaleable without partitioning it: http://www.percona.com/blog/2009/11/16/table_cache-negative-scalability/
This doesn't directly apply to MariaDB. We didn't partition it because our table definition cache is lock-free. There were quite a few related changes in 10.0 (e.g. see MDEV-7292 and linked issues). In short, we didn't partition it, because it doesn't need to be partitioned. Not for this benchmark workload, at least. Regards, Sergei
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.
There was also a MDEV created about this very problem on MariaDB by Guillaume : https://mariadb.atlassian.net/browse/MDEV-7292 But in my opinion, this specific test is only usefull to see scalability issues or the cache warming impact but it doesnt really reflect a typical real-world usage (the original topic here) : if you use this cache its because you wont always query different tables but eventually have queries on tables already cached. I wonder what is the performance difference when the cache is warm and all tables are already in cache vs. a way too limited table cache that will have to purge LRU tables frequently. Le 21/04/2015 23:37, Sergei Golubchik a écrit :
Hi, Justin!
It is in fact, negatively scaleable without partitioning it: http://www.percona.com/blog/2009/11/16/table_cache-negative-scalability/ This doesn't directly apply to MariaDB. We didn't partition it because our table definition cache is lock-free. There were quite a few related changes in 10.0 (e.g. see MDEV-7292 and linked issues). In short, we didn't partition it, because it doesn't need to be
On Apr 21, Justin Swanhart wrote: partitioned. Not for this benchmark workload, at least.
Regards, Sergei
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.
ps: forgot to mention this MDEV which is also relevant to the thread (i think) that i made about backporting the default automatic value of "table_definition_cache" from MySQL 5.6.8+ (400 + (table_open_cache / 2) and limited to 2000) instead of a fixed value of 400 : https://mariadb.atlassian.net/browse/MDEV-7261 Le 22/04/2015 00:31, Jean Weisbuch a écrit :
There was also a MDEV created about this very problem on MariaDB by Guillaume : https://mariadb.atlassian.net/browse/MDEV-7292
But in my opinion, this specific test is only usefull to see scalability issues or the cache warming impact but it doesnt really reflect a typical real-world usage (the original topic here) : if you use this cache its because you wont always query different tables but eventually have queries on tables already cached.
I wonder what is the performance difference when the cache is warm and all tables are already in cache vs. a way too limited table cache that will have to purge LRU tables frequently.
Le 21/04/2015 23:37, Sergei Golubchik a écrit :
Hi, Justin!
On Apr 21, Justin Swanhart wrote:
It is in fact, negatively scaleable without partitioning it: http://www.percona.com/blog/2009/11/16/table_cache-negative-scalability/
This doesn't directly apply to MariaDB. We didn't partition it because our table definition cache is lock-free. There were quite a few related changes in 10.0 (e.g. see MDEV-7292 and linked issues). In short, we didn't partition it, because it doesn't need to be partitioned. Not for this benchmark workload, at least.
Regards, Sergei
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.
Hi Jean, On Wed, Apr 22, 2015 at 12:31:56AM +0200, Jean Weisbuch wrote:
There was also a MDEV created about this very problem on MariaDB by Guillaume : https://mariadb.atlassian.net/browse/MDEV-7292
But in my opinion, this specific test is only usefull to see scalability issues or the cache warming impact but it doesnt really reflect a typical real-world usage (the original topic here) : if you use this cache its because you wont always query different tables but eventually have queries on tables already cached.
I wonder what is the performance difference when the cache is warm and all tables are already in cache vs. a way too limited table cache that will have to purge LRU tables frequently. Among other expensive things table definition cache miss (as well as eviction) does I/O. So it is a lot more expensive than table cache hit. How much? That depends on workload.
Regards, Sergey
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@mariadb.org> 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
Hi,
Is that a MariaDB table_cache improvement? MySQL 5.6 has partitioned
cache to overcome the negative scalability aspect of increasing table cache.
--Justin
On Tue, Apr 21, 2015 at 10:34 AM, Sergey Vojtovich <svoj@mariadb.org> 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 of
time it takes to search the table cache, as the search is linear
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
On Tue, Apr 21, 2015 at 10:43:05AM -0700, Justin Swanhart wrote: table through 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@redhat.com>
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@redhat.com > <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
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@mariadb.org> 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@mariadb.org> 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
Hi,
Is that a MariaDB table_cache improvement? MySQL 5.6 has partitioned
On Tue, Apr 21, 2015 at 10:43:05AM -0700, Justin Swanhart wrote: 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@mariadb.org
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
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
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
opened by through 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@redhat.com>
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@redhat.com >> <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
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@gmail.com> 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@mariadb.org> 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
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
amount 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 <hhorak@redhat.com> 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@redhat.com <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
_______________________________________________ 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
participants (6)
-
Honza Horak -
Jean Weisbuch -
Justin Swanhart -
Rohan M C -
Sergei Golubchik -
Sergey Vojtovich