RE: very low insertion speed

Hi Poornima, If it is purely about speed, and you have a large set of data, then use COPY INTO instead of INSERTS. Write the data in csv format to a file, and then fire of a COPY INTO statement. See https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/LoadingBulkData If you are not able to do that because you cannot create files, or your database is on a different filesystem, then have a look at http://dev.monetdb.org/hg/MonetDB/file/tip/java/example/SQLcopyinto.java In this example you use COPY INTO from STDIN, and stream the data to it. Both forms work like a charm is much, much faster. We use it to insert millions of records in a couple of minutes. Hope this helps, Marcel Blok -----Oorspronkelijk bericht----- Van: users-list [mailto:users-list-bounces+m.blok=chs.nl@monetdb.org] Namens poornima@apakau.com Verzonden: maandag 14 december 2015 20:18 Aan: users-list@monetdb.org Onderwerp: Re: very low insertion speed I have tried with 30K of INSERT INTOs in one transaction. But still it got improved from 37 to 46reqs/sec. The code snipped is as follows: The database is local. Can you please help me how to improve the insertion speed ? StringBuilder s = new StringBuilder(" insert into rec ( "); {// for formating s.append(" dom_id, api_d "); s.append(" ,req_minute "); s.append(" ,cntry, end_ip, calls_r)"); s.append("values (?,?,?,?,?,?)"); } Connection conn = _dataSource.getConnection(); conn.setAutoCommit(false); try { PreparedStatement stmt = conn.prepareStatement(s.toString()); Iterator<List> iterator = args.iterator(); while(iterator.hasNext()) { List<Object> n = iterator.next(); stmt.setString(1, n.get(0).toString()); stmt.setString(2, n.get(1).toString()); stmt.setTimestamp(3, Timestamp.valueOf(n.get(2).toString())); stmt.setString(4, n.get(3).toString()); stmt.setString(5, n.get(4).toString()); stmt.setInt(6, Integer.valueOf(n.get(5).toString())); stmt.executeUpdate(); } //stmt.executeBatch(); } catch(Exception e) { _log.error(e); throw e; } finally { conn.commit(); conn.close(); } Thanks & Regards, Poornima. On 12/08/2015 09:01 AM, Ying Zhang wrote:

Hello Poornima,

30 - 35 INSERT INTOs/second with autocommit on may actually be expected. I’ve seen worse, in one of my experiments with Jul2015 some time ago, I only got 8~9 INSERTs done per second. However, in that same experiment, wrapping the ~20K of INSERT INTOs into _one_ transaction gave me a speed up of >2900 times.

Could you please tell us what exactly did you do when using balk insert? E.g., how did you turn off auto commit? What are the exactly query and commands used? Do you access the monetdb server remotely (it seems not, but just for sure)? Any other information you can think of? Which OS?

Regards,

Jennie

...

On Dec 03, 2015, at 00:15, Poornima Iyer wrote:

Hi Brian,

Thank you for your reply. I have put all the details down. I realise that there is something I am doing wrong. Can you please guide me to that.

apakau@apakau:~$ monetdbd --version MonetDB Database Server v1.7 (Jul2015-SP1) apakau@apakau:~$ monetdb --version MonetDB Database Server Toolkit v1.1 (Jul2015-SP1) apakau@apakau:~$ mclient --version mclient, the MonetDB interactive terminal (Jul2015-SP1) support for command-line editing compiled-in character encoding: UTF-8

The table on which we are doing the insertion is as follows : CREATE TABLE "apakau"."rec" ( "id" BIGINT NOT NULL DEFAULT next value for "apakau"."seq_7069", "dom_id" VARCHAR(128) NOT NULL, "api_d" VARCHAR(128) NOT NULL, "end_ip" VARCHAR(128), "time_s" TIMESTAMP NOT NULL, "d_time_m" TIMESTAMP NOT NULL, "cntry" VARCHAR(2) NOT NULL, "cntry_state" VARCHAR(16), "city" VARCHAR(32), "is_vpn_proxy" BOOLEAN, "kbtoep" INTEGER, "calls_r" INTEGER, "calls_w" INTEGER, "avg_latency" INTEGER, "cache_hit_count" INTEGER, "retries" INTEGER, "error_msg" VARCHAR(256), "blocked_by_rate" INTEGER, "blocked_by_geo" INTEGER, "sec_grade" INTEGER, "sec_grade_count" INTEGER, CONSTRAINT "rec_id_pkey" PRIMARY KEY ("id") ); CREATE INDEX "rec_cload1" ON "apakau"."rec" ("dom_id", "end_ip", "time_s", "calls_r", "calls_w", "id"); CREATE INDEX "rec_id" ON "apakau"."rec" ("id"); CREATE INDEX "rec_tgeo1" ON "apakau"."rec" ("dom_id", "cntry", "cntry_state", "api_d", "d_time_m", "id");

We are using ubuntu15.0. Our machine has apakau@apakau:~$ lscpu Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian CPU(s): 4 On-line CPU(s) list: 0-3 Thread(s) per core: 1 Core(s) per socket: 4 Socket(s): 1 NUMA node(s): 1 Vendor ID: GenuineIntel CPU family: 6 Model: 60 Model name: Intel(R) Core(TM) i5-4460 CPU @ 3.20GHz Stepping: 3 CPU MHz: 800.000 CPU max MHz: 3400.0000 CPU min MHz: 800.0000 BogoMIPS: 6385.52 Virtualization: VT-x L1d cache: 32K L1i cache: 32K L2 cache: 256K L3 cache: 6144K NUMA node0 CPU(s): 0-3

apakau@apakau:~$ cat /proc/meminfo MemTotal: 3922352 kB MemFree: 481064 kB MemAvailable: 1955012 kB Buffers: 198488 kB Cached: 1343160 kB SwapCached: 8392 kB Active: 2619384 kB Inactive: 639576 kB Active(anon): 1663028 kB Inactive(anon): 187248 kB Active(file): 956356 kB Inactive(file): 452328 kB Unevictable: 32 kB Mlocked: 32 kB SwapTotal: 4069372 kB SwapFree: 3988984 kB Dirty: 40 kB Writeback: 0 kB AnonPages: 1710392 kB Mapped: 245924 kB Shmem: 132968 kB Slab: 121080 kB SReclaimable: 94520 kB SUnreclaim: 26560 kB KernelStack: 5984 kB PageTables: 20288 kB NFS_Unstable: 0 kB Bounce: 0 kB WritebackTmp: 0 kB CommitLimit: 6030548 kB Committed_AS: 3821748 kB VmallocTotal: 34359738367 kB VmallocUsed: 356028 kB VmallocChunk: 34358947836 kB HardwareCorrupted: 0 kB AnonHugePages: 1255424 kB CmaTotal: 0 kB CmaFree: 0 kB HugePages_Total: 0 HugePages_Free: 0 HugePages_Rsvd: 0 HugePages_Surp: 0 Hugepagesize: 2048 kB DirectMap4k: 115088 kB DirectMap2M: 2906112 kB DirectMap1G: 1048576 kB

Regards, Poornima. ----- Original Message ----- From: "Brian Hood" To: "Communication channel for MonetDB users" Sent: Wednesday, December 2, 2015 5:33:07 PM Subject: Re: very low insertion speed

Hi Poornima,

30 - 35 inserts per second is slow with autocommit on.

What is your setup you said you are inserting into a local database / mserver5 instance.

Also what version / environment are you using monetdb ?

Regards,

Brian Hood On Dec 2, 2015 7:18 PM, " poornima@apakau.com " < poornima@apakau.com > wrote:

Hi,

I understand. I shall try bulk insert. But even with auto-commit false, I don't get any performance improvement.

Regards, Poornima.

On 12/02/2015 09:46 AM, Mark Raasveldt wrote:

Hey Poornima,

Are the insertions happening in a transaction? If you perform single INSERT INTO statements in auto commit mode it will be very slow, as each of the individual insertions will be written to disk. Bulk insert statements are much faster. If you have to load a very large dataset, you might want to use COPY INTO rather than individual INSERT INTO statements as well. Check out this page for more information and performance tips: https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/LoadingBul kData

Regards, Â Â Mark

On Wed, Dec 2, 2015 at 6:29 PM, poornima@apakau.com < poornima@apakau.com > wrote:

Hi,

Is there something that I need to configure. I am inserting to a local database to a table with only one covered index. I am using jdbc to write. I am getting only 30-35 insertions/sec even on a new table. This seems very low. Am I missing something ?

Regards, Poornima.

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

---

users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list