Thanks!
Deleted tuples are not removed but marked deleted. When will the tuples be removed?
It might be better/easier to sent the mysql table completely. For the first time , I can send the mysql table completely. But when the mysql table is updated later, I cannot drop the old monetdb table and send it again.
Further opens are described in : https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/LoadingBulkData I have read this documentation. I think the problem is how to synchronize the modified data after the first time synchronization. If we use 'COPY INTO', we need to solve 'PRIMARY KEY constraint violated' error. And as I know, there is no 'REPLACE INTO ...' or 'INSERT INTO ... ON DUPLICATE KEY UPDATE ...' in monetdb.
wang.tong@shomop.com From: Martin Kersten Date: 2016-08-19 17:21 To: users-list Subject: Re: synchronize data from mysql performance 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