MySQL Troubleshooting

Operating System Limits

Other limits on MySQL are set by operating systems. For example, I saw a case when a server failed because the Linux host specified vm.overcommit_ratio = 50. This option specifies the percentage of the system’s total virtual memory that can be allocated via malloc(); attempts to allocate more memory by a process will fail. Fifty percent is the default value for many Linux installations. So when mysqld allocates about 50% of existing RAM and then tries to allocate more, it fails. In a multitasking setup, such an option may be useful because it protects other critical processes from a greedy MySQL server, but it’s absolutely ridiculous in a dedicated server.

Another important Unix option is ulimit, which restricts various resources for users.^[15] When you set resources using the ulimit command or another system utility, remember that the MySQL server runs as a simple user and is subject to the same limits as everyone else.

Server-related options mentioned how OS restrictions affect the open_files_limit variable, and I’ll show an example here. Suppose the server’s ulimit on open files (the -n option) is set to 1024, the default on many systems. If you try to start mysqld with --open-files-limit=4096, it would not override the operating system limit.

$ulimit -n
1024

$./bin/mysqld --defaults-file=support-files/my-small.cnf --basedir=.
--datadir=./data --socket=/tmp/mysql_ssmirnova.sock --port=33051 --log-error
--open-files-limit=4096 &
[1] 31833

$./bin/mysql -uroot ...