论坛徽章:: 0

发表于 2010-08-20 22:27 |显示全部楼层

本帖最后由 justin033 于 2010-08-20 23:04 编辑

背景：我们这边大量的分库分表，总共354个库，表总共7W多个，3分1是MyISAM表。

系统版本：CentOS release 5.4 (Final) MySQL 版本5.1.39

[root@~]# find /data02/mysql/ -name "*.frm" | wc -l
70468

复制代码

sysctl.conf和limit.conf的配置，如下：

# Add By Justin
vm.swappiness = 10
fs.file-max = 204800

复制代码

# Add By Justin
* soft nofile 204800
* hard nofile 204800

复制代码

[root@~]# 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) 81920
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 204800
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) 81920
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
[root@~]# cat /proc/sys/fs/file-nr
4590 0 204800

复制代码

用户MySQL的环境设置：

[root@~]# su - mysql
[mysql@~]$ whoami
mysql
[mysql@~]$ 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) 81920
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 204800
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) 81920
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited

复制代码

但是：mysql里open_files_limit只到65535，没达到我设置的值，如下：

"Variable_name","Value"
"open_files_limit","65535"

复制代码

主机也重启了，mysql的open_files_limit的值还没超过65535。。。有人遇到过没。。。。。。。。。。。。。。。。。。。。。。。。

文库|博客

justin033

小富即安

论坛徽章:: 0

发表于 2010-08-20 23:02 |显示全部楼层

回复 1# justin033

peisai，补充下mysql error log的记录：

100820 18:31:30 mysqld_safe mysqld from pid file /data02/mysql/db102.greenemls.org.pid ended
100820 18:32:50 mysqld_safe Starting mysqld daemon with databases from /data02/mysql
100820 18:32:50 [Warning] Could not increase number of max_open_files to more than 65535 (request: 102561)
100820 18:32:50 [Note] Plugin 'FEDERATED' is disabled.
100820 18:32:50 [Note] Plugin 'ndbcluster' is disabled.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins.
100820 18:32:50 InnoDB: highest supported file format is Barracuda.
100820 18:32:51 InnoDB Plugin 1.0.4-7 started; log sequence number 7203394656
100820 18:32:51 [Note] Event Scheduler: Loaded 0 events
100820 18:32:51 [Note] /usr/local/mysql/libexec/mysqld: ready for connections.
Version: '5.1.39-Community-log' socket: '/data02/mysql/mysql.sock' port: 3306 5.1.39 Source
100820 18:32:51 [Note] Event Scheduler: scheduler thread started with id 1
100820 18:33:30 [Note] /usr/local/mysql/libexec/mysqld: Normal shutdown
100820 18:33:30 [Note] Event Scheduler: Killing the scheduler thread, thread id 1
100820 18:33:30 [Note] Event Scheduler: Waiting for the scheduler thread to reply
100820 18:33:30 [Note] Event Scheduler: Stopped
100820 18:33:30 [Note] Event Scheduler: Purging the queue. 0 events
100820 18:33:30 InnoDB: Starting shutdown...
100820 18:33:32 InnoDB: Shutdown completed; log sequence number 7203394666
100820 18:33:32 [Warning] Forcing shutdown of 2 plugins
100820 18:33:32 [Note] /usr/local/mysql/libexec/mysqld: Shutdown complete
100820 18:33:32 mysqld_safe mysqld from pid file /data02/mysql/db102.greenemls.org.pid ended
100820 18:37:15 mysqld_safe Starting mysqld daemon with databases from /data02/mysql
100820 18:37:16 [Warning] Could not increase number of max_open_files to more than 65535 (request: 102561)
100820 18:37:16 [Note] Plugin 'FEDERATED' is disabled.
100820 18:37:16 [Note] Plugin 'ndbcluster' is disabled.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins.
100820 18:37:17 InnoDB: highest supported file format is Barracuda.
100820 18:37:20 InnoDB Plugin 1.0.4-7 started; log sequence number 7203394666
100820 18:37:21 [Note] Event Scheduler: Loaded 0 events
100820 18:37:21 [Note] /usr/local/mysql/libexec/mysqld: ready for connections.
Version: '5.1.39-Community-log' socket: '/data02/mysql/mysql.sock' port: 3306 5.1.39 Source
100820 18:37:21 [Note] Event Scheduler: scheduler thread started with id 1
100820 18:37:52 mysqld_safe A mysqld process already exists
[root@db102 ~]#

复制代码

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

limohao

白手起家

论坛徽章:: 0

发表于 2010-08-20 23:10 |显示全部楼层

设置成0好了
设置成0即不更改系统中的配置数
open_files_limit

Command-Line Format --open-files-limit=#
Config-File Format open-files-limit
Option Sets Variable Yes, open_files_limit
Variable Name open_files_limit
Variable Scope Global
Dynamic Variable No
Permitted Values
Type numeric
Default 0
Range 0-65535

The number of files that the operating system permits mysqld to open. This is the real value permitted by the system and might be different from the value you gave using the --open-files-limit option to mysqld or mysqld_safe. The value is 0 on systems where MySQL can't change the number of open files.