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