Avoiding Connection Lost (during query) exceptions
Posted: Mon 01 Dec 2014 10:58
Hi,
We have a scientific program that calculates data in a very large matrix. 50 instances of the program run on 50 processors on 10 PCs, so 50 matrices are in use at any moment by all program instances. The average size of a matrix is about 500 MB but can go up to 2 GB. The server runs on Win 7 64. The client PCs run on Win XP 32.
The calculation of a single matrix by a program instance can take days or even weeks, then when it’s finished, the program starts to write all matrix data (about 1 GB) to the database.
Then I get this error:
"Lost connection to MySQL server during queryError on data writing to the connection:An established connection was aborted by the software in your host machine.Socket Error Code: 10053($2745)"
This happens on some of the write operations. Other do not have problems. I presume the largest matrices show this problems.
There seem to be two issues:
(1). The connection will be aborted by the server after a certain timeout, certainly the time to calculate a matrix is much larger than the server timeout;
(2). When writing tens of millions of records to a database, the connection might be reset or lost during query to store the data;
What’s the correct procedure to write very large amounts after a long time of inactivity to a server? In other words: How do I avoid any errors?
The structure (in pseudocode) I use now is:
(… do some very time consuming data)
MyConnection.connect ;
MyQuery.Connection := MyConnection ;
for i := 1 to 10000 do
begin
Query.SQL.Text := ALongSQLInsertStatement ;
Query.Execute ;
end ;
I tried the START TRANSACTION / COMMIT method before, but that caused even more connection lost errors as it probably tries to write the whole set of data to the database in one chunk. In the above example the data is split in 10,000 pieces which solved many errors, but not all. I still get the above error in about 10% of the (largest?) matrix write to database operations.
We have a scientific program that calculates data in a very large matrix. 50 instances of the program run on 50 processors on 10 PCs, so 50 matrices are in use at any moment by all program instances. The average size of a matrix is about 500 MB but can go up to 2 GB. The server runs on Win 7 64. The client PCs run on Win XP 32.
The calculation of a single matrix by a program instance can take days or even weeks, then when it’s finished, the program starts to write all matrix data (about 1 GB) to the database.
Then I get this error:
"Lost connection to MySQL server during queryError on data writing to the connection:An established connection was aborted by the software in your host machine.Socket Error Code: 10053($2745)"
This happens on some of the write operations. Other do not have problems. I presume the largest matrices show this problems.
There seem to be two issues:
(1). The connection will be aborted by the server after a certain timeout, certainly the time to calculate a matrix is much larger than the server timeout;
(2). When writing tens of millions of records to a database, the connection might be reset or lost during query to store the data;
What’s the correct procedure to write very large amounts after a long time of inactivity to a server? In other words: How do I avoid any errors?
The structure (in pseudocode) I use now is:
(… do some very time consuming data)
MyConnection.connect ;
MyQuery.Connection := MyConnection ;
for i := 1 to 10000 do
begin
Query.SQL.Text := ALongSQLInsertStatement ;
Query.Execute ;
end ;
I tried the START TRANSACTION / COMMIT method before, but that caused even more connection lost errors as it probably tries to write the whole set of data to the database in one chunk. In the above example the data is split in 10,000 pieces which solved many errors, but not all. I still get the above error in about 10% of the (largest?) matrix write to database operations.