mysql connection fails after 3000+ attempts

Question

Hi,

I was just testing a small script in Prolog to sanity check the MySQL connection. The connection fails randomly, after making around 3000+ connections. Is there any limitation in MySQL Server for number of connections

:-dynamic db_connection/1.

sanity_check_open_db:-
            odbc_connect('myDSN', _,
            [ user(bob),
              password(pop),
              alias(myDSN),
              open(once)
            ]),

            (   db_connection(_),
                retractall(db_connection(_))
            ;   assert(db_connection(myDSN))).

sanity_chec_close:-
            (   db_connection(C),
                odbc_disconnect(C),
                retractall(db_connection(C))
            ;   write('Error: No connection opened to close')).

sanity_check_open_close(10000).

sanity_check_open_close(N):-
    format(atom(C),'~wth Iteration~n',[N]),
    write(C),
            sanity_check_open_db,
            sanity_chec_close,
            N1 is N + 1,!,
            sanity_check_open_close(N1).

Answer 1

TCP connections take kernel memory, even after they are closed. If you say:

netstat -na |grep WAIT

I think you'll find that most of those 3000 connections are still in the TIME_WAIT state, which typically lasts 120 seconds. It might be optimized to a lower value in your case, but still quite long, like 30 seconds. If your program can make enough connections in that time, you can run the kernel out of memory reserved for keeping track of TCP connections.