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.
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?
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