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—-
还没有评论,来说两句吧...