CODEWITHSUNDEEP
mysqlperformanceloadmariadb
nisamudeen97
nisamudeen97

Reputation: 563

kernel limit "open file limit " and mysql "Opened_files" related?

We are hosting our courier api app which is having large data insertion and data read. We are trying to achieve maximum through put for our ubuntu 16 server with 55cpu and 128gb ram and 1tb SSD disk. Below is "innobdb status result" 

29 queries inside InnoDB, 517 queries in queue
32 read views open inside InnoDB
Process ID=2197, Main thread ID=140312433551104, state: sleeping
Number of rows inserted 169904, updated 1662462, deleted 3, read 44626802863
26.33 inserts/s, 202.20 updates/s, 0.00 deletes/s, 6061352.15 reads/s

While I have checked I could see our open file limit was 65000 for mysql and Opened_files usage in mysql is 84981. My question is "Is kernel limit "open file limit " and mysql Opened_files related?"

show global variables like 'open%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 65536 

MariaDB [(none)]> show status like '%Opened_files%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_files  | 84981  |
+---------------+-------+
1 row in set (0.00 sec)

We are experiencing abnormal sudden huge spikes and our api starts dropping connections.

Upvotes: 0

Views: 317

Answers (2)

Rick James
Rick James

Reputation: 142483

The interesting value would be the quotient of Opened_files / Uptime . Under, say, 2/second is 'reasonable'.

Also relevant: table_open_cache, innodb_open_files, and others.

For more thorough analysis, please provide

  • RAM size
  • SHOW VARIABLES;
  • SHOW GLOBAL STATUS;

The snippet you provided seems to imply some heavy table scans? Perhaps missing some indexes? Let's discuss some of the common or slower queries.

Upvotes: 1

tukan
tukan

Reputation: 17345

Those are two different things. One Opened_files is a counter which tells you how many times you have opened the table since the last mysqld restart. If you want to tune your database you may get better performence by tunning table_open_cache, but that is out of the scope of this question

On the other hand open file limit is a hard operating system limit, which tells you how many files you can have opened at the same time. The limit is usually somewhat lower than the 2^16 (alias your file limit). Greater detail is again longer to explain and out of scope.

To answer your question directly: They are not related.

Upvotes: 2

Related Questions