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

author：skate
time：2012/12/4

 

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

 

环境说明：
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的最大连接数

在A上
mysql> show variables like '%connections%';
+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| character_set_connection | latin1            |
| collation_connection     | latin1_swedish_ci |
| max_connections          | 151               |
| max_user_connections     | 0                 |
+--------------------------+-------------------+
4 rows in set (0.00 sec)

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的连接数

 

在B上
mysql> show variables like '%max_connection%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+
1 row in set (0.00 sec)

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的mysql库open_files_limit也很大
mysql> show variables like '%open_files_limit%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 65536 |
+------------------+-------+
1 row in set (0.00 sec)

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

mysql> show variables like '%innodb_open_files%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| innodb_open_files | 300   |
+-------------------+-------+
1 row in set (0.00 sec)

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---