Mysql – ulimit ‘open files’ and MySQL max_connection

linuxmax-connectionsMySQL

When running mysql on SSD, does max_connections, if set more than the kernel's ulimit open files, be limited to the kernel's limit?

My understanding is that if the kernel's ulimit open files is 1024 for example, any value of max_connection for mysql greater (e.g, 3000) than the ulimit open files will be limited to just 1024.

My question is, does this also hold for SSD?

Best Answer

max_connections and ulimit and SSDs have virtually no relationship to each other.

ulimit is an upper bound (unnecessarily low at 1024) on the number of files that can be open in any 'process'. (mysqld is a 'process'.) It does relate to table_open_cache, which is now set dynamically based on ulimit. (In older versions, it was not automatically set.) However, a single table may need to open more than one file, so that 1024 is not really a limit on the number of "tables" in your databases.

See also Open_tables (bounded by table_open_cache) and Opened_tables/Uptime (frequency of opening an uncached table).

The current default for max_connections is 151, which is 'reasonable' for the 'typical' installation. Almost no one "needs" 3000.

SSDs, as already mentioned, is new technology that is like "disks", but faster. A few obscure things should be tuned for SSDs, but only if you have a very busy system.

Related Question