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 <s...@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 <s...@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 > > > > > > 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 <s...@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 > 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 <hho...@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 < > hho...@redhat.com > > > > > > >> <mailto:hho...@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
