0

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?

1

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.

0

If the SSD you are talking is "Solid State Drive", it will only act as an ordinary drive with faster read and write. That is the reason why servers are being migrated to SSD drives for critical parts of it.

Increasing the max_connection will also increase CPU and Memory Usage. I won't recommend a limit of 3000.

About the issue with kernel limit of open files, I don't know if it applies to MySQL. It only says "open files"

3
  • You would not recommend a limit of 3000, then what would you recommend? And about open files, yes, it applies to MySQl as well. I read it somewhere and i have also done tests in the past regarding this Feb 5 '16 at 4:15
  • The first question is how many users are you expecting to be online at a time. Next, how long are your SQL Queries takes time to complete, each page loads closes the SQL connection as soon as it completes rendering. Third is, how much CPU power and memory you have. There is no magic number on the max connections you have since CPU and Memory will be your barrier. I usually use 500 max connections since all of my queries was run under 1 connection and these connection completes in less than 4 seconds. You can also check persistent connections, that will give you a new view
    – Vhortex
    Feb 5 '16 at 4:19
  • 500 connections can potentially use 1.5GB of RAM, if the system allows 3000 then you can expect a memory issue when the system tries to use approximately 9GB RAM.
    – Vhortex
    Feb 5 '16 at 4:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.