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)

 

Environment Description:
192.168.213.133:
192.168.213.120: B

 

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

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

 

Perform a stress test on:
[Root @ racdb2 ~] #/MySQL/bin/mysqlslap-h192.168.213.20.- 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 connector connections
/MySQL/bin/mysqlslap: error when connecting to server: 1040 too connector connections
....
....
/MySQL/bin/mysqlslap: error when connecting to server: 1040 too connector connections
/MySQL/bin/mysqlslap: cannot run query insert into BBB (ID) values (12) error: Got error 10000 'error on remote system: 1040: too has connections 'from federated

 

From the preceding error, we can see that the number of connections on a has exceeded, so we can modify the maximum number of connections on a according to the error message.

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

 

Retest
[Root @ racdb2 ~] #/MySQL/bin/mysqlslap-h192.168.213.20.- 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 tables connections

The error message indicates that the number of connections of remote B has exceeded and the number of connections of remote B has been adjusted.

 

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

Connection test
[Root @ racdb2 ~] #/MySQL/bin/mysqlslap-h192.168.213.20.- 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 enabled open files

This time, the error message shows the file descriptor restrictions of remote B.

The operating system of B is very limited.
[Root @ node3 ~] # Ulimit-n
65536

B's MySQL library open_files_limit is also very large
Mysql> show variables like '% open_files_limit % ';
+ ------------------ + ------- +
| Variable_name | value |
+ ------------------ + ------- +
| Open_files_limit | 65536 |
+ ------------------ + ------- +
1 row in SET (0.00 Sec)

If the B table is InnoDB, view the parameters

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'

You need to modify the configuration file my. CNF.

Retest
[Root @ racdb2 ~] #/MySQL/bin/mysqlslap-h192.168.213.20.- 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

This pass

Summary:
Parameters related to connections:
OS:
Modify the file:/etc/security/limits. conf
Or
Add "ulimit-N 100000" at the end of the/etc/profile file and run sysctl-P. This method can be modified online.

MySQL:
Max_user_connections: controls the maximum number of connections for each MySQL user.
Max_connections: controls the maximum number of connections of MySQL.
Open_files_limit: controls the number of file descriptors that mysqld can use at the same time. If the state variable open_files is close to open_files_limit, open_files_limit needs to be added.
Innodb_open_files: the number of file descriptors that can be used to open the InnoDB table at the same time. If InnoDB is too large and the concurrency is large, this parameter needs to be increased; it has nothing to do with open_files_limit.

Related Variables
Table_open_cache: cache the number of tables opened by all threads. Use opened_tables and open_tables to adjust table_open_cache.
Table_definition_cache: this variable is better configured. It is large enough to cache all table definitions (*. FRM)

 

 

 

--- End ---