ERROR : Unable to connect to foreign data source: Can't create TCP/IP socket (24)

骑猪看日落 2022-03-20 04:57 405阅读 0赞

author：skate  
time：2012/12/4

环境说明：  
192.168.213.137：A  
192.168.213.120：B

在B上  
mysql> show create table b\\G;  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
       Table: b  
Create Table: CREATE TABLE \`b\` (  
  \`id\` int(10) default NULL,  
  \`name\` varchar(100) character set utf8 default NULL,  
  \`hostid\` int(6) default NULL  
) ENGINE=InnoDB DEFAULT CHARSET=latin1  
1 row in set (0.00 sec)

在A上  
mysql> show create table bbb\\G;  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
       Table: bbb  
Create Table: CREATE TABLE \`bbb\` (  
  \`id\` int(10) DEFAULT NULL,  
  \`name\` varchar(100) DEFAULT NULL,  
  \`hostid\` int(6) DEFAULT NULL  
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://root:root@192.168.213.120:3306/test/b'  
1 row in set (0.00 sec)

在A上执行压力测试：  
\[root@racdb2 ~\]\# /mysql/bin/mysqlslap -h192.168.213.137 -P3306 -uroot -proot --concurrency=500 --iterations=1 --create-schema='test' --query='insert into bbb(id) values(12)'  --number-of-queries=1000000 --debug-info  
/mysql/bin/mysqlslap: Error when connecting to server: 1040 Too many connections  
/mysql/bin/mysqlslap: Error when connecting to server: 1040 Too many connections  
....  
....  
/mysql/bin/mysqlslap: Error when connecting to server: 1040 Too many connections  
/mysql/bin/mysqlslap: Cannot run query insert into bbb(id) values(12) ERROR : Got error 10000 'Error on remote system: 1040: Too many connections' from FEDERATED

从上面的报错可以看出是A上的连接数超了，那就根据错误提示修改A的最大连接数

mysql> set global max\_connections=100000;  
Query OK, 0 rows affected (0.00 sec)

再测试  
\[root@racdb2 ~\]\# /mysql/bin/mysqlslap -h192.168.213.137 -P3306 -uroot -proot --concurrency=500 --iterations=1 --create-schema='test' --query='insert into bbb(id) values(12)'  --number-of-queries=1000000 --debug-info  
/mysql/bin/mysqlslap: Cannot run query insert into bbb(id) values(12) ERROR : Unable to connect to foreign data source: Too many connections

从错误信息看是远程B的连接数超了，再调整远程B的连接数

mysql> set global max\_connections=10000;  
Query OK, 0 rows affected (0.00 sec)

接续测试  
\[root@racdb2 ~\]\# /mysql/bin/mysqlslap -h192.168.213.137 -P3306 -uroot -proot --concurrency=500 --iterations=1 --create-schema='test' --query='insert into bbb(id) values(12)'  --number-of-queries=1000000 --debug-info  
/mysql/bin/mysqlslap: Cannot run query insert into bbb(id) values(12) ERROR : Unable to connect to foreign data source: Can't create TCP/IP socket (24)  
\[root@racdb2 ~\]\# perror 24  
OS error code  24:  Too many open files

这回从错误提示可以了解到远程B的文件描述符限制

B的os限制很大  
\[root@node3 ~\]\# ulimit -n  
65536

想到B的b表是innodb的，于是查看参数

mysql> set global innodb\_open\_files=10000;  
ERROR 1193 (HY000): Unknown system variable 'innodb\_open\_files'

需要修改配置文件my.cnf

再测试  
\[root@racdb2 ~\]\# /mysql/bin/mysqlslap -h192.168.213.137 -P3306 -uroot -proot --concurrency=500 --iterations=1 --create-schema='test' --query='insert into bbb(id) values(12)'  --number-of-queries=1000000 --debug-info  
Benchmark  
        Average number of seconds to run all queries: 690.724 seconds  
        Minimum number of seconds to run all queries: 690.724 seconds  
        Maximum number of seconds to run all queries: 690.724 seconds  
        Number of clients running queries: 500  
        Average number of queries per client: 2000

User time 7.47, System time 25.61  
Maximum resident set size 0, Integral resident set size 0  
Non-physical pagefaults 3688, Physical pagefaults 0, Swaps 0  
Blocks in 0 out 0, Messages in 0 out 0, Signals 0  
Voluntary context switches 1971423, Involuntary context switches 7669

这次通过

**总结：**  
和连接数有关的参数：  
os：  
修改文件：/etc/security/limits.conf  
或  
在文件/etc/profile最后添加“ulimit -n 100000”，然后运行sysctl -p ,这种方式可以在线修改

mysql：  
max\_user\_connections：控制mysql每个用户的最大的连接数  
max\_connections：控制mysql最大连接数  
open\_files\_limit：控制mysqld可以同时使用的文件描述符数量，如果状态变量Open\_files接近open\_files\_limit，那open\_files\_limit就需要增加  
innodb\_open\_files:可以同时打开innodb表使用的文件描述符数量，如果innodb特别多，且并发也大，要增大这个参数；和open\_files\_limit没有关系

相关变量  
table\_open\_cache：缓存所有线程打开表的数量，结合opened\_tables和open\_tables来调整table\_open\_cache  
table\_definition\_cache：这个变量比较好设置，把它设置得足够大，大到可以缓存所有表定义(\*.frm)

\---end---