Extreme high open file limit used - MariaDB 10.3

PeterChen

Member
Hi guys,

we have upgraded the the Database server to 10.3 and noticed high open file limit used while Mysqltuner.pl

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 78.0M (Tables: 5)
[--] Data in InnoDB tables: 21.1G (Tables: 236)
[--] Data in MEMORY tables: 10.8M (Tables: 3)
[!!] Total fragmented tables: 1

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 14h 11m 59s (52M q [1K qps], 3M conn, TX: 1503G, RX: 15G)
[--] Reads / Writes: 75% / 25%
[--] Binary logging is disabled
[--] Physical Memory : 94.2G
[--] Max MySQL memory : 37.4G
[--] Other process memory: 611.8M
[--] Total buffers: 35.1G global + 2.9M per thread (620 max threads)
[--] P_S Max memory usage: 531M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 35.7G (37.94% of installed RAM)
[OK] Maximum possible memory usage: 37.4G (39.68% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/52M)
[OK] Highest usage of available connections: 7% (45/620)
[OK] Aborted connections: 0.00% (0/3451781)
[--] Skipped name resolution test due to skip_networking=ON in system variables.
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (78 temp sorts / 3M sorts)
[!!] Joins performed without indexes: 837224
[!!] Temporary tables created on disk: 50% (1M on disk / 3M total)
[OK] Thread cache hit rate: 99% (45 created / 3M connections)
[OK] Table cache hit rate: 90% (667 open / 735 opened)
[!!] Open file limit used: 28147927174348900% (18446744073B/65K)
[!!] Table locks acquired immediately: 86%

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 531.1M
[--] Sys schema isn't installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 36 thread(s).
[--] Using default value is good enough for your version (10.3.10-MariaDB)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 19.5% (516M used / 2B cache)
[OK] Key buffer size / total MyISAM indexes: 2.5G/16.8M
[OK] Read Key buffer hit rate: 100.0% (98M cached / 10K reads)
[!!] Write Key buffer hit rate: 88.3% (7M cached / 6M writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 32.0G/21.1G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 4.0G * 2/32.0G should be equal 25%
[OK] InnoDB buffer pool instances: 32
[--] Number of InnoDB Buffer Pool Chunk : 256 for 32 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (36999275046 hits/ 37000117624 total)
[!!] InnoDB Write Log efficiency: 86.04% (3658360 hits/ 4251694 total)
[OK] InnoDB log waits: 0.00% (0 waits / 593334 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 98.5% (106M cached / 1M reads)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
OPTIMIZE TABLE admin_xxxxx.`xf_search`; -- can free 37 MB
Total freed space after theses OPTIMIZE TABLE : 37 Mb
MySQL was started within the last 24 hours - recommendations may be inaccurate
Adjust your join queries to always utilize indexes
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Optimize queries and/or use InnoDB to reduce lock wait
Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
join_buffer_size (> 256.0K, or always use indexes with JOINs)
open_files_limit (> 65535)

is it normal? I dont think it is.
what should i do?
 

MySiteGuy

Well-known member
Weird, because I'm using 10.3 and don't receive this error.

Run mysql from the command line and put in this query, to see what its set to:

show variables where variable_name='open_files_limit';
 

PeterChen

Member
Weird, because I'm using 10.3 and don't receive this error.

Run mysql from the command line and put in this query, to see what its set to:

show variables where variable_name='open_files_limit';
Sorry for late reply, i was on a trip. And forgot about this.

MariaDB [(none)]> show variables where variable_name='open_files_limit';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 65535 |
+------------------+-------+
1 row in set (0.001 sec)
 

MySiteGuy

Well-known member
Unless you have a very large number of databases and tables, anything over about 4000 is overkill. If it's not set in your configuration file, Mariadb 10.3 may be autosizing it.

Generally, you'll find it's a configuration in /etc/my.cnf, /etc/my.cnf.d/server.cnf or similar file. Location of the file may vary, depending on your Linux setup, but those are the two common default locations.

Find the file, open it and add (or edit) in the [mysqld] section:

open-files-limit = 4000;

Then restart the database.
 
Top