open_files_limit加大不了，只到65535

justin033

背景：我們這邊大量的分庫(kù)分表，總共354個(gè)庫(kù)，表總共7W多個(gè)，3分1是MyISAM表。

系統(tǒng)版本：CentOS release 5.4 (Final) MySQL 版本5.1.39

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

復(fù)制代碼

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

1. # Add By Justin
   
2. vm.swappiness = 10
   
3. 
   
4. fs.file-max = 204800

復(fù)制代碼

1. # Add By Justin
   
2. *               soft     nofile         204800
   
3. *               hard     nofile         204800

復(fù)制代碼

1. [root@~]# ulimit -a
   
2. core file size          (blocks, -c) 0
   
3. data seg size           (kbytes, -d) unlimited
   
4. scheduling priority             (-e) 0
   
5. file size               (blocks, -f) unlimited
   
6. pending signals                 (-i) 81920
   
7. max locked memory       (kbytes, -l) 32
   
8. max memory size         (kbytes, -m) unlimited
   
9. open files                      (-n) 204800
   
10. pipe size            (512 bytes, -p) 8
    
11. POSIX message queues     (bytes, -q) 819200
    
12. real-time priority              (-r) 0
    
13. stack size              (kbytes, -s) 10240
    
14. cpu time               (seconds, -t) unlimited
    
15. max user processes              (-u) 81920
    
16. virtual memory          (kbytes, -v) unlimited
    
17. file locks                      (-x) unlimited
    
18. [root@~]# cat /proc/sys/fs/file-nr
    
19. 4590    0       204800

復(fù)制代碼

用戶MySQL的環(huán)境設(shè)置：

1. [root@~]# su - mysql
   
2. [mysql@~]$ whoami
   
3. mysql
   
4. [mysql@~]$ ulimit -a
   
5. core file size          (blocks, -c) 0
   
6. data seg size           (kbytes, -d) unlimited
   
7. scheduling priority             (-e) 0
   
8. file size               (blocks, -f) unlimited
   
9. pending signals                 (-i) 81920
   
10. max locked memory       (kbytes, -l) 32
    
11. max memory size         (kbytes, -m) unlimited
    
12. open files                      (-n) 204800
    
13. pipe size            (512 bytes, -p) 8
    
14. POSIX message queues     (bytes, -q) 819200
    
15. real-time priority              (-r) 0
    
16. stack size              (kbytes, -s) 10240
    
17. cpu time               (seconds, -t) unlimited
    
18. max user processes              (-u) 81920
    
19. virtual memory          (kbytes, -v) unlimited
    
20. file locks                      (-x) unlimited

復(fù)制代碼

但是：mysql里open_files_limit只到65535，沒達(dá)到我設(shè)置的值，如下：

1. "Variable_name","Value"
   
2. "open_files_limit","65535"
   

復(fù)制代碼

主機(jī)也重啟了，mysql的open_files_limit的值還沒超過65535。。。有人遇到過沒。。。。。。。。。。。。。。。。。。。。。。。。

justin033

回復(fù) 1# justin033


    peisai，補(bǔ)充下mysql error log的記錄：

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

復(fù)制代碼

limohao

設(shè)置成0好了
設(shè)置成0即不更改系統(tǒng)中的配置數(shù)
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.

renxiao2003

好像挺多現(xiàn)在都是65535這個(gè)數(shù)字。

justin033

看來(lái)是我錯(cuò)了，:wink:，認(rèn)真看了文檔的open_files_limit說明，最大只能65535。。。。。。杯具了，難道要改源代碼

ruochen

看來(lái)是我錯(cuò)了，，認(rèn)真看了文檔的open_files_limit說明，最大只能65535。。。。。。杯具了，難道要改源 ...
justin033 發(fā)表于 2010-08-20 23:19

    估計(jì)得改源代碼，還要注意是否影響其他的，避免crash

cenalulu

http://bugs.mysql.com/bug.php?id=47095
他的意思是，如果設(shè)置為0，那么就會(huì)使用ulimit -n 的值，也就不會(huì)有65535的限制。popgo可以試下~

justin033

設(shè)置open_files_limit=0，mysqld起不來(lái)，而且error log不報(bào)錯(cuò)