Re: [MonetDB-users] 64bit MonetDB, JDBC Insert via RJDBC, >300 million rows
On Thu, Mar 26, 2009 at 09:13:57AM -0700, Yue Sheng wrote:
(A) For thread 4: are you sure that it is waiting for anything, here is the iostat output, each observation one second apart:
msync is called. Which is supposed to write your dirty pages to disk. This needs some investigation but would surely require a MacOS system.
disk0 cpu load average
KB/t tps MB/s us sy id 1m 5m 15m
0.00 0 0.00 0 0 100 0.04 0.05 0.00
So there is no cpu nor disk load. As for threads 5 and 6, one would think that the argument '--set gdk_nr_threads=1' constrains the number of clients that can connect. Even if that assumption is incorrect, right now there are no clients connected. During the lifetime of the server process though, there have been three possible client candidates:
The number of threads is for processing, ie queries will use up to the specified number of threads to compute the query. Still each connected client will use its own thread.
- The server itself. We run the server with "bin/mserver5
--dbfarmUsers/howardshek/Onur-tmp/dbfarm --dbname=evatick_new --dbinit="include sql;" --set gdk_nr_threads=1 --daemon=yes ". So I am not sure if it qualifies as a client as well.
Yes its a client but not on the sql level, so doesn't take up the lock we were worried about.
- Command line client ran by: "bin/mclient -d evatick_new -l
sql". This runs a single sql command: "drop table Japan2008;" at the very beginning before the data load, and then I exit the client via Ctrl-D.
Should have finised ie would be strange if it still hangs on the lock.
- The R code using JDBC. At the moment, that process is killed
too, so mserver5 might be waiting for some lock/semaphore/mutex to finally switch to the thread relevant to that particular client and do the cleanup.
Both the killed clients may be the cause but it would need a mac box etc
(B) My reply is getting long, and keeps getting rejected by the user list....
Sorry about that, but I guess we cannot fix sf.
Niels
(B) My reply is getting long, and keeps getting rejected by the user list....
Sorry about that, but I guess we cannot fix sf.
Don't send your messages as HTML, but send them as plain text only. That saves a *lot*.
Hi, I've got 2 tables with 30 bytes varchar keys and matching those tables takes forever, eg. find 10 records from one table in another. Can you help with that? Thanks. Dariusz.
On Thu, Mar 26, 2009 at 01:42:59PM -0400, dariuszs wrote:
Hi, I've got 2 tables with 30 bytes varchar keys and matching those tables takes forever, eg. find 10 records from one table in another. Can you help with that? Thanks. Dariusz.
we would need more info for this, ie could you post your table definition and query?
Niels
MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
Hi, Sure. t1 100 records t2 120,000,000 records
create table t1 (c1 int, key30 varchar(30)); create table t2 (c1 int, key30 varchar(30));
update t1 set c1=(select c1 from t2 where t2.key30=t1.key30);
To find 100 records with 30 bytes key takes about 3 minutes on 64bit Windows with 48GB memory and 2 Xenon CPUs, compared to other counts that I can do with this database that very, very slow. Thanks. Dariusz.
Niels Nes wrote:
On Thu, Mar 26, 2009 at 01:42:59PM -0400, dariuszs wrote:
Hi, I've got 2 tables with 30 bytes varchar keys and matching those tables takes forever, eg. find 10 records from one table in another. Can you help with that? Thanks. Dariusz.
we would need more info for this, ie could you post your table definition and query?
Niels
MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
dariuszs wrote:
Hi, Sure. t1 100 records t2 120,000,000 records
Assuming you have more than a few thousand different strings this amounts to a ca 3.6G string table that might have to be loaded into memory that's cost factor 1. Then the system probably decides on building a string hash (depending on query type), cost factor 2 and if you are unlucky there it may chase through the collision list Such decisions can be obtained with the --algorithms flag
So the 3 minutes should first be understood as a result of running to a 'cold' database or 'hot' database.
In case you want to identify the precise cost, it is worthwhile to run the SQL query with the trace option or with the debugger and trace flags.
regards, Martin
create table t1 (c1 int, key30 varchar(30)); create table t2 (c1 int, key30 varchar(30));
update t1 set c1=(select c1 from t2 where t2.key30=t1.key30);
To find 100 records with 30 bytes key takes about 3 minutes on 64bit Windows with 48GB memory and 2 Xenon CPUs, compared to other counts that I can do with this database that very, very slow. Thanks. Dariusz.
Niels Nes wrote:
On Thu, Mar 26, 2009 at 01:42:59PM -0400, dariuszs wrote:
Hi, I've got 2 tables with 30 bytes varchar keys and matching those tables takes forever, eg. find 10 records from one table in another. Can you help with that? Thanks. Dariusz.
we would need more info for this, ie could you post your table definition and query?
Niels
MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
Hi, Are you saying that these columns are not indexed? I guess I'm missing something... I matched these tables in MySQL and it took less than a second to find those 100 records (although using B-tree index).... So how do you run fast queries when you have to run them against multiple tables? Could you please help me on this one? Thanks. Dariusz.
Martin Kersten wrote:
dariuszs wrote:
Hi, Sure. t1 100 records t2 120,000,000 records
Assuming you have more than a few thousand different strings this amounts to a ca 3.6G string table that might have to be loaded into memory that's cost factor 1. Then the system probably decides on building a string hash (depending on query type), cost factor 2 and if you are unlucky there it may chase through the collision list Such decisions can be obtained with the --algorithms flag
So the 3 minutes should first be understood as a result of running to a 'cold' database or 'hot' database.
In case you want to identify the precise cost, it is worthwhile to run the SQL query with the trace option or with the debugger and trace flags.
regards, Martin
create table t1 (c1 int, key30 varchar(30)); create table t2 (c1 int, key30 varchar(30));
update t1 set c1=(select c1 from t2 where t2.key30=t1.key30);
To find 100 records with 30 bytes key takes about 3 minutes on 64bit Windows with 48GB memory and 2 Xenon CPUs, compared to other counts that I can do with this database that very, very slow. Thanks. Dariusz.
Niels Nes wrote:
On Thu, Mar 26, 2009 at 01:42:59PM -0400, dariuszs wrote:
Hi, I've got 2 tables with 30 bytes varchar keys and matching those tables takes forever, eg. find 10 records from one table in another. Can you help with that? Thanks. Dariusz.
we would need more info for this, ie could you post your table definition and query?
Niels
MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
participants (4)
-
dariuszs -
Martin Kersten -
Niels Nes -
Sjoerd Mullender