(Database: MYSQL 5.5, OS: RHEL)

A case study about how to configure MYSQL resource limitation

Set up MYSQL resource limitation at file: /etc/security/limits.conf

Following is a case of properly setting of max_open_files

During MYSQL startup, the system shows a warning as following marked in RED.    

110905 20:12:24  InnoDB: Starting shutdown...
110905 20:12:26  InnoDB: Shutdown completed; log sequence number 1595675
110905 20:12:26 [Note] /usr/sbin/mysqld: Shutdown complete

110905 20:12:26 mysqld_safe mysqld from pid file /home/mysql/data/cpprdjm1.ctm.plc.cwintra.com.pid ended
110905 20:13:54 mysqld_safe Starting mysqld daemon with databases from /home/mysql/data
110905 20:13:54 [Warning] Could not increase number of max_open_files to more than 1024 (request: 4356)
110905 20:13:54 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
110905 20:13:54  InnoDB: Using Linux native AIO
110905 20:13:54  InnoDB: Initializing buffer pool, size = 5.0G
110905 20:13:54  InnoDB: Completed initialization of buffer pool
110905 20:13:54  InnoDB: highest supported file format is Barracuda.
110905 20:13:55  InnoDB: 1.1.4 started; log sequence number 1595675
110905 20:13:55 [Note] Event Scheduler: Loaded 0 events
110905 20:13:55 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.8-enterprise-commercial-advanced-log'  socket: '/home/mysql/mysql.sock'  port: 3306  MySQL Enterprise Server - Advanced Edition (Commercial)

I tried to update the /etc/security/limits.conf.  Relogin the server and try again.

 [mysql@cpprdjm1 ~]$ ulimit -n

5120

[mysql@cpprdjm1 ~]$ ulimit -a

core file size          (blocks, -c) 0

data seg size           (kbytes, -d) unlimited

scheduling priority             (-e) 0

file size               (blocks, -f) unlimited

pending signals                 (-i) 106496

max locked memory       (kbytes, -l) 32

max memory size         (kbytes, -m) unlimited

open files                      (-n) 5120

pipe size            (512 bytes, -p) 8

POSIX message queues     (bytes, -q) 819200

real-time priority              (-r) 0

stack size              (kbytes, -s) 10240

cpu time               (seconds, -t) unlimited

max user processes              (-u) 106496

virtual memory          (kbytes, -v) unlimited

file locks                      (-x) unlimited

The warning message does not appear anymore when MySQL startup.

 110908 12:26:03 mysqld_safe mysqld from pid file /home/mysql/data/cpprdjm1.ctm.plc.cwintra.com.pid ended
110908 12:26:48 mysqld_safe Starting mysqld daemon with databases from /home/mysql/data
110908 12:26:48 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
110908 12:26:48  InnoDB: Using Linux native AIO
110908 12:26:48  InnoDB: Initializing buffer pool, size = 5.0G
110908 12:26:49  InnoDB: Completed initialization of buffer pool
110908 12:26:49  InnoDB: highest supported file format is Barracuda.
110908 12:26:49  InnoDB: 1.1.4 started; log sequence number 1613515
110908 12:26:50 [Note] Event Scheduler: Loaded 0 events
110908 12:26:50 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.8-enterprise-commercial-advanced-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Enterprise Server - Advanced Edition (Commercial)

Reference:

如何使用 ulimit

ulimit 通过一些参数选项来管理不同种类的系统资源。在本节，我们将讲解这些参数的使用。

ulimit 命令的格式为：ulimit [options] [limit]

具体的 options 含义以及简单示例可以参考以下表格。

表 1. ulimit 参数说明