On 19/08/16 11:07, wang.tong@shomop.com wrote:
Hi,
I try to synchronize data from mysql to monetdb. Steps: 1. Read the modified rows from mysql table periodically. 2. Delete the rows in monetdb by primary key. (To avoid 'PRIMARY KEY constraint violated' error.) The sql is like 'delete from a where id in (1, 2, 3)'. 3. Insert the rows into monetdb by 'copy into ... from stdin'.
I find step 2 may be too slow. When there are 2 million rows, delete 10,000 rows takes about 1 minute. When there are 30 million rows, delete 10,000 rows takes about 20 minute. I have to do join on the 30 million rows table, so I cannot split it. Deleted tuples are not removed but marked deleted.
It might be better/easier to sent the mysql table completely.
I try to upgrade the server from 2 core 8 G memory to 8 core 32 G memory, but it has no effect. It seems the limitation is CPU, and the deleted operation can only use 1 core. How can I optimize the performance? Is there any better approach to synchronize data? Further opens are described in : https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/LoadingBulkData Thanks in advance!
OS version: centos-release-7-0.1406.el7.centos.2.5.x86_64 mserver5 --version MonetDB 5 server v11.23.7 "Jun2016-SP1" (64-bit, 64-bit oids, 128-bit integers) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2016 MonetDB B.V., all rights reserved Visit http://www.monetdb.org/ for further information Found 31.0GiB available memory, 8 available cpu cores Libraries: libpcre: 8.32 2012-11-30 (compiled with 8.32) openssl: OpenSSL 1.0.1e 11 Feb 2013 (compiled with OpenSSL 1.0.1e-fips 11 Feb 2013) libxml2: 2.9.1 (compiled with 2.9.1) Compiled by: root@jstu87d9w6zj4u (x86_64-unknown-linux-gnu) Compilation: gcc -g -O2 Linking : /usr/bin/ld -m elf_x86_64
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- wang.tong@shomop.com
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list