MERGE Tables works ?
Hi Everybody! Im facing a problem. We register telecomunications calls. As the volume is so big, we decided to store the data in different tables. The criteria we used for splitting the info was to create a table per each Switch and Day. So we have Sw1_20160601, Sw2_20160602...Sw3_20160620..etc etc Each table has aprox 15MM rows. In order to create some reports we have created a *MERGE TABLE *and added to this table the 90 tables (3 switches x 30 days). BUT, now Im trying to run a very simple query, like SELECT cdr_date FROM xxxx LIMIT 3; To get at least 3 rows (just to see that this schema works). It took 21 mins to give me those 3 rows.... Am I doing something wrong? Rgds Ariel
Ariel, I’ve seen something similar when I’ve added count() to an aggregate query using merge tables. You might try adding an explain statement to see what the server is doing. I am wondering if the server constructs the entire cdr_date column (and starts swapping to disk - hence the 21 min), and then returns the first three entries. I also ran into problems with columns not being found in the DB catalog of slave instances when using merge tables. It clearly looked like a bug, but I didn’t have the time to put together a reproducible test case. Dave From: users-list [mailto:users-list-bounces+david.b.anderson=citi.com@monetdb.org] On Behalf Of Ariel Abadi Sent: Monday, June 27, 2016 2:39 PM To: Communication channel for MonetDB users Subject: MERGE Tables works ? Hi Everybody! Im facing a problem. We register telecomunications calls. As the volume is so big, we decided to store the data in different tables. The criteria we used for splitting the info was to create a table per each Switch and Day. So we have Sw1_20160601, Sw2_20160602...Sw3_20160620..etc etc Each table has aprox 15MM rows. In order to create some reports we have created a MERGE TABLE and added to this table the 90 tables (3 switches x 30 days). BUT, now Im trying to run a very simple query, like SELECT cdr_date FROM xxxx LIMIT 3; To get at least 3 rows (just to see that this schema works). It took 21 mins to give me those 3 rows.... Am I doing something wrong? Rgds Ariel
Hi Dave! Thanks a lot for your answer. I tried to do the EXPLAIN, but (telling you the truth) I dont understand anything on the explain. Does this make sense to you ? I tried to attach the EXPLAIN but the message exceeds the limit of the forum, so I uploaded in here http://paste.ofcode.org/F4WAGkHquUGHDbmbRC6hCp, can you please check it? Thank you very much in advance! ARiel *Ariel Abadi* Direct: +54 11 5279.2054 Mobile:+54 9 11 6050.0101 Email: aabadi@starconnecting.com Web: www.starconnecting.com On Tue, Jun 28, 2016 at 12:16 PM, Anderson, David B < david.b.anderson@citi.com> wrote:
Ariel,
I’ve seen something similar when I’ve added count() to an aggregate query using merge tables. You might try adding an explain statement to see what the server is doing. I am wondering if the server constructs the entire cdr_date column (and starts swapping to disk - hence the 21 min), and then returns the first three entries.
I also ran into problems with columns not being found in the DB catalog of slave instances when using merge tables. It clearly looked like a bug, but I didn’t have the time to put together a reproducible test case.
Dave
*From:* users-list [mailto:users-list-bounces+david.b.anderson= citi.com@monetdb.org] *On Behalf Of *Ariel Abadi *Sent:* Monday, June 27, 2016 2:39 PM *To:* Communication channel for MonetDB users *Subject:* MERGE Tables works ?
Hi Everybody!
Im facing a problem. We register telecomunications calls. As the volume is so big, we decided to store the data in different tables. The criteria we used for splitting the info was to create a table per each Switch and Day.
So we have Sw1_20160601, Sw2_20160602...Sw3_20160620..etc etc Each table has aprox 15MM rows.
In order to create some reports we have created a *MERGE TABLE *and added to this table the 90 tables (3 switches x 30 days). BUT, now Im trying to run a very simple query, like SELECT cdr_date FROM xxxx LIMIT 3; To get at least 3 rows (just to see that this schema works).
It took 21 mins to give me those 3 rows....
Am I doing something wrong?
Rgds
Ariel
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Hey Ariel, Indeed, it seems that LIMIT is not very cleverly implemented in MonetDB. As you can see in the MAL plan, the LIMIT is only computed at the end of the plan (X_361 := algebra.subslice(X_359,0,2:wrd); => algebra.subslice is the MAL function used to compute the LIMIT). That means it will gather all the data together before calling LIMIT, which is why it takes such a long time. Regards, Mark ----- Original Message ----- From: "Ariel Abadi" <aabadi@starconnecting.com> To: "users-list" <users-list@monetdb.org> Sent: Tuesday, June 28, 2016 9:14:12 PM Subject: Re: MERGE Tables works ? Hi Dave! Thanks a lot for your answer. I tried to do the EXPLAIN, but (telling you the truth) I dont understand anything on the explain. Does this make sense to you ? I tried to attach the EXPLAIN but the message exceeds the limit of the forum, so I uploaded in here http://paste.ofcode.org/F4WAGkHquUGHDbmbRC6hCp, can you please check it? Thank you very much in advance! ARiel *Ariel Abadi* Direct: +54 11 5279.2054 Mobile:+54 9 11 6050.0101 Email: aabadi@starconnecting.com Web: www.starconnecting.com On Tue, Jun 28, 2016 at 12:16 PM, Anderson, David B < david.b.anderson@citi.com> wrote:
Ariel,
I’ve seen something similar when I’ve added count() to an aggregate query using merge tables. You might try adding an explain statement to see what the server is doing. I am wondering if the server constructs the entire cdr_date column (and starts swapping to disk - hence the 21 min), and then returns the first three entries.
I also ran into problems with columns not being found in the DB catalog of slave instances when using merge tables. It clearly looked like a bug, but I didn’t have the time to put together a reproducible test case.
Dave
*From:* users-list [mailto:users-list-bounces+david.b.anderson= citi.com@monetdb.org] *On Behalf Of *Ariel Abadi *Sent:* Monday, June 27, 2016 2:39 PM *To:* Communication channel for MonetDB users *Subject:* MERGE Tables works ?
Hi Everybody!
Im facing a problem. We register telecomunications calls. As the volume is so big, we decided to store the data in different tables. The criteria we used for splitting the info was to create a table per each Switch and Day.
So we have Sw1_20160601, Sw2_20160602...Sw3_20160620..etc etc Each table has aprox 15MM rows.
In order to create some reports we have created a *MERGE TABLE *and added to this table the 90 tables (3 switches x 30 days). BUT, now Im trying to run a very simple query, like SELECT cdr_date FROM xxxx LIMIT 3; To get at least 3 rows (just to see that this schema works).
It took 21 mins to give me those 3 rows....
Am I doing something wrong?
Rgds
Ariel
_______________________________________________ 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
Hi Mark! Thanks a lot for your answer. I will try to find a different solution to solve this issue. Even though my actual problem, I think is a good a idea to review for future releases. Thks again!!! Rgds Ariel *Ariel Abadi* Direct: +54 11 5279.2054 Mobile:+54 9 11 6050.0101 Email: aabadi@starconnecting.com Web: www.starconnecting.com On Tue, Jun 28, 2016 at 6:28 PM, Mark Raasveldt <m.raasveldt@cwi.nl> wrote:
Hey Ariel,
Indeed, it seems that LIMIT is not very cleverly implemented in MonetDB. As you can see in the MAL plan, the LIMIT is only computed at the end of the plan (X_361 := algebra.subslice(X_359,0,2:wrd); => algebra.subslice is the MAL function used to compute the LIMIT). That means it will gather all the data together before calling LIMIT, which is why it takes such a long time.
Regards,
Mark
----- Original Message ----- From: "Ariel Abadi" <aabadi@starconnecting.com> To: "users-list" <users-list@monetdb.org> Sent: Tuesday, June 28, 2016 9:14:12 PM Subject: Re: MERGE Tables works ?
Hi Dave!
Thanks a lot for your answer. I tried to do the EXPLAIN, but (telling you the truth) I dont understand anything on the explain. Does this make sense to you ? I tried to attach the EXPLAIN but the message exceeds the limit of the forum, so I uploaded in here http://paste.ofcode.org/F4WAGkHquUGHDbmbRC6hCp, can you please check it?
Thank you very much in advance! ARiel
*Ariel Abadi* Direct: +54 11 5279.2054 Mobile:+54 9 11 6050.0101 Email: aabadi@starconnecting.com Web: www.starconnecting.com
On Tue, Jun 28, 2016 at 12:16 PM, Anderson, David B < david.b.anderson@citi.com> wrote:
Ariel,
I’ve seen something similar when I’ve added count() to an aggregate query using merge tables. You might try adding an explain statement to see what the server is doing. I am wondering if the server constructs the entire cdr_date column (and starts swapping to disk - hence the 21 min), and then returns the first three entries.
I also ran into problems with columns not being found in the DB catalog of slave instances when using merge tables. It clearly looked like a bug, but I didn’t have the time to put together a reproducible test case.
Dave
*From:* users-list [mailto:users-list-bounces+david.b.anderson= citi.com@monetdb.org] *On Behalf Of *Ariel Abadi *Sent:* Monday, June 27, 2016 2:39 PM *To:* Communication channel for MonetDB users *Subject:* MERGE Tables works ?
Hi Everybody!
Im facing a problem. We register telecomunications calls. As the volume is so big, we decided to store the data in different tables. The criteria we used for splitting the info was to create a table per each Switch and Day.
So we have Sw1_20160601, Sw2_20160602...Sw3_20160620..etc etc Each table has aprox 15MM rows.
In order to create some reports we have created a *MERGE TABLE *and added to this table the 90 tables (3 switches x 30 days). BUT, now Im trying to run a very simple query, like SELECT cdr_date FROM xxxx LIMIT 3; To get at least 3 rows (just to see that this schema works).
It took 21 mins to give me those 3 rows....
Am I doing something wrong?
Rgds
Ariel
_______________________________________________ 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 _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (3)
-
Anderson, David B -
Ariel Abadi -
Mark Raasveldt