MySQL Socket refuses to connect after several thousand consecutive connections

Question

I am currently trying to populate a MySQL5.1 database on the current Ubuntu machine> 5,000,000 records. Due to the architecture of the program, a new database connection is opened and closed for each INSERT statement. I know this is an expensive operation, but changing this will require changing the loooot of the code, so I would rather avoid this.

Problem . I came across the fact that after a while (usually about 12 seconds, but this number increases after some attempts) this process can no longer connect to the database. Other processes can connect to the database without problems.

I don’t think this is a MySQL problem because none of the MySQL logs reports any errors. In addition, max 1 connection opens at a time (which I checked by looking at the MySQL state variables).

Question : Is there a limit to how many serial connections to a socket can be opened in a given time period?

Here is a simplified perl-script (the actual program is in Java) that leads to the same problem (database variables are not included):

# ...
my $i = 0;
my $DBH = 0;
for ($i = 0;$i < $MAX; ++$i) {
  $DBH = DBI->connect("DBI:mysql:$DBNAME:$DBHOST:$DBPORT", $DBUSER, $DBPW)
    or die ("Error - Connection to database failed: \n   $i times ok\n".DBI->errstr);

  $DBH -> disconnect;
  if ($i % 10000 == 0) {
    print $i. " ";
  }
}

Question : any suggestions / ideas?

Answer 1

try to increase the number of allowed connections in /etc/mysql/my.cnf?

max_connections        = 300

if php, change all instances of mysql_connect () to mysql_pconnect ()?

Answer 2

, , TCP, TIME_WAIT (. ) Linux , ( Windows , , , , ).

netstat , , db.

TIME_WAIT, , , , ( ).