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
>
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list