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.