[MonetDB-users] JDBC insert performance
Hi all,
I am new to monetDB and ran into the following problem (I'll try to be as verbose as I can, perhaps it helps helping me):
I've installed MonetDB server 5.0.0, clients and commons 1.18.2 and sql 2.18.2 on Ubuntu Feisty. Then I've taken the JDBC example which is available online and modified it to be able to insert data into a test database. More specifically I'm using prepared statements and executeBatch() together with the monetdb-1.6 JDBC driver.
Which is quite successful until about 5 million records are inserted - afterwards the speed of the inserts drops dramatically. I've experimented with different batch sizes (10000 records seems to work ok), and I've tried to understand the chapter about memory tuning for MonetDB (not sure yet about how to use mem_cursize() though). In any case at first inserting 2 million records only takes about 5 minutes (measured with Javas System.timemillis), later on this time increases and the server gets rather unresponsive.
I found only two posts concerning the speed of JDBC inserts (from January 2007 I think) and I would be interested if a solution or explanation has been found off-list.
Thank you
Hi,
I am wondering whether you are able to solve the issue of slow insert sppeds once it reaches more than 5 million records..
Do you have still this problem..
We will be inserting close to that amount every day (may be) and curious how MonetDB is going to handle the huge no.of records..
Sateesh
chinarinde wrote:
Hi all,
I am new to monetDB and ran into the following problem (I'll try to be as verbose as I can, perhaps it helps helping me):
I've installed MonetDB server 5.0.0, clients and commons 1.18.2 and sql 2.18.2 on Ubuntu Feisty. Then I've taken the JDBC example which is available online and modified it to be able to insert data into a test database. More specifically I'm using prepared statements and executeBatch() together with the monetdb-1.6 JDBC driver.
Which is quite successful until about 5 million records are inserted - afterwards the speed of the inserts drops dramatically. I've experimented with different batch sizes (10000 records seems to work ok), and I've tried to understand the chapter about memory tuning for MonetDB (not sure yet about how to use mem_cursize() though). In any case at first inserting 2 million records only takes about 5 minutes (measured with Javas System.timemillis), later on this time increases and the server gets rather unresponsive.
I found only two posts concerning the speed of JDBC inserts (from January 2007 I think) and I would be interested if a solution or explanation has been found off-list.
Thank you
sateesh wrote:
Hi,
I am wondering whether you are able to solve the issue of slow insert sppeds once it reaches more than 5 million records..
Do you have still this problem..
it seems you are running on an old version of MonetDB, more then a year old. We are already at 5.4. I recall improvements in that area have been taking place.
BEWARE, if you roll forward you have to dump/restore the database due to major differences in the storage structure.
Aside that, it is hard to judge what causes this issue. The most probable ones are swapping due to large database table sizes, i.e. running out of memory. See http://monetdb.cwi.nl/projects/monetdb//SQL/Benchmark/TPCH/ which indicate the loading times of tables far larger and the effect of going from in memory processing to out of memory processing.
We will be inserting close to that amount every day (may be) and curious how MonetDB is going to handle the huge no.of records..
Sateesh
chinarinde wrote:
Hi all,
I am new to monetDB and ran into the following problem (I'll try to be as verbose as I can, perhaps it helps helping me):
I've installed MonetDB server 5.0.0, clients and commons 1.18.2 and sql 2.18.2 on Ubuntu Feisty. Then I've taken the JDBC example which is available online and modified it to be able to insert data into a test database. More specifically I'm using prepared statements and executeBatch() together with the monetdb-1.6 JDBC driver.
Which is quite successful until about 5 million records are inserted - afterwards the speed of the inserts drops dramatically. I've experimented with different batch sizes (10000 records seems to work ok), and I've tried to understand the chapter about memory tuning for MonetDB (not sure yet about how to use mem_cursize() though). In any case at first inserting 2 million records only takes about 5 minutes (measured with Javas System.timemillis), later on this time increases and the server gets rather unresponsive.
I found only two posts concerning the speed of JDBC inserts (from January 2007 I think) and I would be interested if a solution or explanation has been found off-list.
Thank you
participants (3)
-
chinarinde -
Martin Kersten -
sateesh