[MonetDB-users] Loading and querying on the same database.
Loading and querying simultaneously seems to slowdown the database significantly. I can understand if the queries became slower during loading, but it seems that only the first query before loading is finished is slow and all queries coming after.
I can only fix the slowdown by using "killall mserver5" in a terminal, which is not an acceptable solution.
I start 1 "copy into" action with one connection and at the same time a series of simple queries on another connection. The Simple query looks something like this: SELECT x,y FROM table WHERE x = '519';
Used OS: Ubuntu 9.10 and 10.04 MonetDB: Feb2010 release (SP! and SP2 are no option due to them giving loading problems)
Results Query: 1 --> 0.000191 SECONDS : : : Query: 10501 --> 0.000170 SECONDS Query: 10502 --> 0.000169 SECONDS Query: 10503 --> 0.000169 SECONDS Query: 10504 --> 0.000172 SECONDS Query: 10505 --> 0.000172 SECONDS Query: 10506 --> 0.000174 SECONDS Query: 10507 --> 0.000178 SECONDS Query: 10508 --> 1.199149 SECONDS
Load : 1 --> 3.238532 SECONDS
Query: 1-10509 --> 1.238804 SECONDS Query: 1-10510 --> 1.175408 SECONDS Query: 1-10511 --> 1.141172 SECONDS Query: 1-10512 --> 1.132525 SECONDS Query: 1-10513 --> 1.159554 SECONDS Query: 1-10514 --> 1.158303 SECONDS Query: 1-10515 --> 1.160997 SECONDS
How do I solve this or is this an error in MonetDB?
Thanks in advance.
Hi,
do you load into the same table that you query?
If so, how large (#records) is that table before and after (extra) loading, and do the x values in your table happen to be sorted before the (extra) loading, while they are no no longer after loading, i.e., the newly added (appended) value "destroy" sorted order?
Do you load and query in the same transaction, or in different transactions, or do you use autocommit mode for the load?
How many queries after loading did you check?
Did you wait for some time (say, couple of minutes) after loaading and check how fast the queries are then?
And about the loading problems with Feb2010-SP1 & -SP2: Did you file a bug report about that?
Thanks!
Stefan
On Wed, May 19, 2010 at 02:05:59AM -0700, Nozhup wrote:
Loading and querying simultaneously seems to slowdown the database significantly. I can understand if the queries became slower during loading, but it seems that only the first query before loading is finished is slow and all queries coming after.
I can only fix the slowdown by using "killall mserver5" in a terminal, which is not an acceptable solution.
I start 1 "copy into" action with one connection and at the same time a series of simple queries on another connection. The Simple query looks something like this: SELECT x,y FROM table WHERE x = '519';
Used OS: Ubuntu 9.10 and 10.04 MonetDB: Feb2010 release (SP! and SP2 are no option due to them giving loading problems)
Results Query: 1 --> 0.000191 SECONDS : : : Query: 10501 --> 0.000170 SECONDS Query: 10502 --> 0.000169 SECONDS Query: 10503 --> 0.000169 SECONDS Query: 10504 --> 0.000172 SECONDS Query: 10505 --> 0.000172 SECONDS Query: 10506 --> 0.000174 SECONDS Query: 10507 --> 0.000178 SECONDS Query: 10508 --> 1.199149 SECONDS
Load : 1 --> 3.238532 SECONDS
Query: 1-10509 --> 1.238804 SECONDS Query: 1-10510 --> 1.175408 SECONDS Query: 1-10511 --> 1.141172 SECONDS Query: 1-10512 --> 1.132525 SECONDS Query: 1-10513 --> 1.159554 SECONDS Query: 1-10514 --> 1.158303 SECONDS Query: 1-10515 --> 1.160997 SECONDS
How do I solve this or is this an error in MonetDB?
Thanks in advance.
View this message in context: http://old.nabble.com/Loading-and-querying-on-the-same-database.-tp28605728p... Sent from the monetdb-users mailing list archive at Nabble.com.
MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
On Wed, May 19, 2010 at 11:22:19AM +0200, Stefan Manegold wrote:
Hi,
do you load into the same table that you query?
If so, how large (#records) is that table before and after (extra) loading, and do the x values in your table happen to be sorted before the (extra) loading, while they are no no longer after loading, i.e., the newly added (appended) value "destroy" sorted order?
Did/could you also check whether queries without where clause, e.g., select sum(x) from table do slow down during/after loading?
Do you load and query in the same transaction, or in different transactions, or do you use autocommit mode for the load?
How many queries after loading did you check?
Did you wait for some time (say, couple of minutes) after loaading and check how fast the queries are then?
And about the loading problems with Feb2010-SP1 & -SP2: Did you file a bug report about that?
Thanks!
Stefan
On Wed, May 19, 2010 at 02:05:59AM -0700, Nozhup wrote:
Loading and querying simultaneously seems to slowdown the database significantly. I can understand if the queries became slower during loading, but it seems that only the first query before loading is finished is slow and all queries coming after.
I can only fix the slowdown by using "killall mserver5" in a terminal, which is not an acceptable solution.
I start 1 "copy into" action with one connection and at the same time a series of simple queries on another connection. The Simple query looks something like this: SELECT x,y FROM table WHERE x = '519';
Used OS: Ubuntu 9.10 and 10.04 MonetDB: Feb2010 release (SP! and SP2 are no option due to them giving loading problems)
Results Query: 1 --> 0.000191 SECONDS : : : Query: 10501 --> 0.000170 SECONDS Query: 10502 --> 0.000169 SECONDS Query: 10503 --> 0.000169 SECONDS Query: 10504 --> 0.000172 SECONDS Query: 10505 --> 0.000172 SECONDS Query: 10506 --> 0.000174 SECONDS Query: 10507 --> 0.000178 SECONDS Query: 10508 --> 1.199149 SECONDS
Load : 1 --> 3.238532 SECONDS
Query: 1-10509 --> 1.238804 SECONDS Query: 1-10510 --> 1.175408 SECONDS Query: 1-10511 --> 1.141172 SECONDS Query: 1-10512 --> 1.132525 SECONDS Query: 1-10513 --> 1.159554 SECONDS Query: 1-10514 --> 1.158303 SECONDS Query: 1-10515 --> 1.160997 SECONDS
How do I solve this or is this an error in MonetDB?
Thanks in advance.
View this message in context: http://old.nabble.com/Loading-and-querying-on-the-same-database.-tp28605728p... Sent from the monetdb-users mailing list archive at Nabble.com.
MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4199 |
Stefan Manegold wrote:
Did/could you also check whether queries without where clause, e.g., select sum(x) from table do slow down during/after loading?
I just did. I used the select sum(x) from table
Results: Query: 31 --> 0.027063 SECONDS Query: 32 --> 0.024661 SECONDS Query: 33 --> 0.033313 SECONDS Query: 34 --> 0.038308 SECONDS Query: 35 --> 1.360738 SECONDS
Load: 1 --> 2.806712 SECONDS
Query: 36 --> 0.758454 SECONDS Query: 37 --> 0.531243 SECONDS Query: 38 --> 0.560807 SECONDS Query: 39 --> 0.526350 SECONDS Query: 40 --> 0.527241 SECONDS Query: 41 --> 0.530362 SECONDS Query: 42 --> 0.534914 SECONDS Query: 43 --> 0.527346 SECONDS
After killall mserver5
Query: 1-1 --> 0.206654 SECONDS Query: 1-2 --> 0.020420 SECONDS Query: 1-3 --> 0.028521 SECONDS Query: 1-4 --> 0.027112 SECONDS Query: 1-5 --> 0.032308 SECONDS Query: 1-6 --> 0.033865 SECONDS Query: 1-7 --> 0.033647 SECONDS Query: 1-8 --> 0.026499 SECONDS Query: 1-9 --> 0.031616 SECONDS
The slowdown is a bit less severe, but it is still present.
Stefan Manegold wrote:
May I also assume that you do use the default optimzer pipeline, i.e., did not explicitly change any optimzer pipeline when starting the server?
I forgot to mention that we are using the recycler pipeline. We are using it instead of the default because it results in way faster queries. I have tried the same test with the default pipe and it results in the same problem.
Stefan Manegold wrote:
I see --- but are the x values in the initial 10M tuples "by chance" in order?
No, we have an unsorted csv-file with 1 million records "real-world" data. We load this file 10 times to create the initial 10M tuples.
Stefan Manegold wrote:
The evaluation of the where clause should be faster on ordered than on unordered data --- if the newly added data makes previously ordered data unordered, that would be one explanation for the slowdown.
However, if your queries are fast, again, after loading and restarting the server --- are they? --- than this is not the problem.
Yes, queries are faster again after restarting.
Stefan Manegold wrote:
I see --- could you please report (there) also the information that Martin asked for, in particular whether you have any constraints defined on your table(s) prior to loading data?
This is the table we are using (a,y,z,a,b,c and table are placeholders for confidential names): CREATE TABLE table ( x BIGINT DEFAULT NULL, y BIGINT DEFAULT NULL, z TINYINT DEFAULT NULL, a TIMESTAMP DEFAULT NULL, b INT DEFAULT NULL, c INT DEFAULT NULL);
I'll also post this table in the other topic.
Stefan Manegold wrote:
Hi,
do you load into the same table that you query?
Yes.
Stefan Manegold wrote:
If so, how large (#records) is that table before and after (extra) loading,
I started with a database of 10 million records and add an additional 1 million records with "COPY INTO" in each test.
Stefan Manegold wrote:
and do the x values in your table happen to be sorted before the (extra) loading, while they are no no longer after loading, i.e., the newly added (appended) value "destroy" sorted order?
We don't sort the data.
Stefan Manegold wrote:
Do you load and query in the same transaction, or in different transactions, or do you use autocommit mode for the load?
I have autocommit on.
Stefan Manegold wrote:
How many queries after loading did you check?
Around 1 hundred.
Stefan Manegold wrote:
Did you wait for some time (say, couple of minutes) after loaading and check how fast the queries are then?
It is still slow after waiting.
Stefan Manegold wrote:
And about the loading problems with Feb2010-SP1 & -SP2: Did you file a bug report about that?
I made a topic in the monetdb-users branch: http://old.nabble.com/Loading-problems-since-FEB2010%2BSP1-release-td2828765...
I have done some additional testing and the problem only seems to happen about 75% of the time.
Summary: - I don't have a problem when I Load-->Query-->Load-->Query-->Etc. - It's purely after I have done Loading and Querying simultaneously. - The problem occurs about 75% of the time. - Autocommit is on. - Only the database that had Loading and Querying done simultaneously has become slow.
On Wed, May 19, 2010 at 03:35:17AM -0700, Nozhup wrote:
Stefan Manegold wrote:
Hi,
do you load into the same table that you query?
Yes.
Stefan Manegold wrote:
If so, how large (#records) is that table before and after (extra) loading,
I started with a database of 10 million records and add an additional 1 million records with "COPY INTO" in each test.
Hm, then the extra amout of data (just 10% more) should not a reason for the quite significant slowdown you reported.
May I also assume that you do use the default optimzer pipeline, i.e., did not explicitly change any optimzer pipeline when starting the server?
Stefan Manegold wrote:
and do the x values in your table happen to be sorted before the (extra) loading, while they are no no longer after loading, i.e., the newly added (appended) value "destroy" sorted order?
We don't sort the data.
I see --- but are the x values in the initial 10M tuples "by chance" in order? The evaluation of the where clause should be faster on ordered than on unordered data --- if the newly added data makes previously ordered data unordered, that would be one explanation for the slowdown.
However, if your queries are fast, again, after loading and restarting the server --- are they? --- than this is not the problem.
We'll need to be able to reproduce the problem to analyse it in more detail.
Stefan Manegold wrote:
Do you load and query in the same transaction, or in different transactions, or do you use autocommit mode for the load?
I have autocommit on.
Stefan Manegold wrote:
How many queries after loading did you check?
Around 1 hundred.
Stefan Manegold wrote:
Did you wait for some time (say, couple of minutes) after loaading and check how fast the queries are then?
It is still slow after waiting.
Stefan Manegold wrote:
And about the loading problems with Feb2010-SP1 & -SP2: Did you file a bug report about that?
I made a topic in the monetdb-users branch: http://old.nabble.com/Loading-problems-since-FEB2010%2BSP1-release-td2828765...
I see --- could you please report (there) also the information that Martin asked for, in particular whether you have any constraints defined on your table(s) prior to loading data?
I have done some additional testing and the problem only seems to happen about 75% of the time.
Summary:
- I don't have a problem when I Load-->Query-->Load-->Query-->Etc.
- It's purely after I have done Loading and Querying simultaneously.
- The problem occurs about 75% of the time.
- Autocommit is on.
- Only the database that had Loading and Querying done simultaneously has
become slow.
Thanks.
Do the queries become fast(er), again, after restarting the server?
Stefan
-- View this message in context: http://old.nabble.com/Loading-and-querying-on-the-same-database.-tp28605728p... Sent from the monetdb-users mailing list archive at Nabble.com.
MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
participants (2)
-
Nozhup -
Stefan Manegold