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
