Creation of a remote table overhead
Hi, I am trying to develop a Python client which sends queries to different Monetdb servers and transfers the results between them using the remote tables functionalities. I’ve seen than my main overhead is execution of CREATE REMOTE TABLE statement. It requires 70-90msec so in cases with many transfers and fast and small queries this makes a difference. After looking at some older versions I’ve seen that the creation of a remote table was very fast before August 2018 (this is when authentication added). Is there any hint on this? Could the. authentication strategy change in someway so that it runs faster or is it possible to select among different authentication methods for the remote tables?
Best, Yannis
On 10 Jul 2020, at 15:26, Ioannis Foufoulas johnfouf@di.uoa.gr wrote:
Hi, I am trying to develop a Python client which sends queries to different Monetdb servers and transfers the results between them using the remote tables functionalities. I’ve seen than my main overhead is execution of CREATE REMOTE TABLE statement. It requires 70-90msec so in cases with many transfers and fast and small queries this makes a difference. After looking at some older versions I’ve seen that the creation of a remote table was very fast before August 2018 (this is when authentication added). Is there any hint on this? Could the. authentication strategy change in someway so that it runs faster or is it possible to select among different authentication methods for the remote tables?
Not sure if that’s the cause of the overhead, but better authentication was indeed added since Aug2018 (https://www.monetdb.org/blog/remote-tables-auth https://www.monetdb.org/blog/remote-tables-auth)
Jennie
Best, Yannis
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Probably not, but I ‘m trying to figure out why in versions prior to Aug.2018 I ‘m getting this:
sql>CREATE REMOTE TABLE rmt_tbl (c1 int) ON 'mapi:monetdb://127.0.0.1:50000/voc3/sys/data'; operation successful sql:0.000 opt:0.359 run:0.473 clk:4.634 ms
While in versions after Aug. 2018 I have: sql>CREATE REMOTE TABLE rmt_tbl (c1 int) ON 'mapi:monetdb://127.0.0.1:50000/voc3/sys/data'; operation successful sql:0.000 opt:0.068 run:0.057 clk:87.766 ms
On 10 Jul 2020, at 4:51 PM, Ying Zhang Y.Zhang@cwi.nl wrote:
On 10 Jul 2020, at 15:26, Ioannis Foufoulas <johnfouf@di.uoa.gr mailto:johnfouf@di.uoa.gr> wrote:
Hi, I am trying to develop a Python client which sends queries to different Monetdb servers and transfers the results between them using the remote tables functionalities. I’ve seen than my main overhead is execution of CREATE REMOTE TABLE statement. It requires 70-90msec so in cases with many transfers and fast and small queries this makes a difference. After looking at some older versions I’ve seen that the creation of a remote table was very fast before August 2018 (this is when authentication added). Is there any hint on this? Could the. authentication strategy change in someway so that it runs faster or is it possible to select among different authentication methods for the remote tables?
Not sure if that’s the cause of the overhead, but better authentication was indeed added since Aug2018 (https://www.monetdb.org/blog/remote-tables-auth https://www.monetdb.org/blog/remote-tables-auth)
Jennie
Best, Yannis
users-list mailing list users-list@monetdb.org mailto: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
maybe you can prefix the queries with TRACE to look deeper into the execution of these queries to see where the time was spent?
On 10 Jul 2020, at 16:22, Ioannis Foufoulas johnfouf@di.uoa.gr wrote:
Probably not, but I ‘m trying to figure out why in versions prior to Aug.2018 I ‘m getting this:
sql>CREATE REMOTE TABLE rmt_tbl (c1 int) ON 'mapi:monetdb://127.0.0.1:50000/voc3/sys/data' monetdb://127.0.0.1:50000/voc3/sys/data'; operation successful sql:0.000 opt:0.359 run:0.473 clk:4.634 ms
While in versions after Aug. 2018 I have: sql>CREATE REMOTE TABLE rmt_tbl (c1 int) ON 'mapi:monetdb://127.0.0.1:50000/voc3/sys/data' monetdb://127.0.0.1:50000/voc3/sys/data'; operation successful sql:0.000 opt:0.068 run:0.057 clk:87.766 ms
On 10 Jul 2020, at 4:51 PM, Ying Zhang <Y.Zhang@cwi.nl mailto:Y.Zhang@cwi.nl> wrote:
On 10 Jul 2020, at 15:26, Ioannis Foufoulas <johnfouf@di.uoa.gr mailto:johnfouf@di.uoa.gr> wrote:
Hi, I am trying to develop a Python client which sends queries to different Monetdb servers and transfers the results between them using the remote tables functionalities. I’ve seen than my main overhead is execution of CREATE REMOTE TABLE statement. It requires 70-90msec so in cases with many transfers and fast and small queries this makes a difference. After looking at some older versions I’ve seen that the creation of a remote table was very fast before August 2018 (this is when authentication added). Is there any hint on this? Could the. authentication strategy change in someway so that it runs faster or is it possible to select among different authentication methods for the remote tables?
Not sure if that’s the cause of the overhead, but better authentication was indeed added since Aug2018 (https://www.monetdb.org/blog/remote-tables-auth https://www.monetdb.org/blog/remote-tables-auth)
Jennie
Best, Yannis
users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org mailto: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
Sure,
The following is the trace result of the last release which takes 87msec +------+-------------------------------------------------------------------------------------------------------------------------------------------------+ | usec | statement | +======+=================================================================================================================================================+ | 4 | X_1=0@0:void := querylog.define("trace create remote table rmt_tbl8 (c1 int) on \'mapi:monetdb://127.0.0.1:50000/voc3/sys/data\';":str, "defa | : : ult_pipe":str, 10:int); #[1] (0) QLOGdefineNaive 1 <- 2 3 15 : | 78 | sqlcatalog.create_table("sys":str, "rmt_tbl8":str, 0x7f22dc193190:ptr, 0:int); #[2] (0) SQLcreate_table 13 <- 8 10 12 6 | | 1 | sql.exportOperation(); #[3] (0) mvc_export_operation_wrap 14 | +------+————————————————————————————————————————————————————————————————————————+
And this is the trace result of the older version which takes 4msec
+------+-------------------------------------------------------------------------------------------------------------------------------------------------+ | usec | statement | +======+=================================================================================================================================================+ | 212 | X_1=0@0:void := querylog.define("trace create remote table rmt_tbl9 (c1 int) on \'mapi:monetdb://127.0.0.1:50000/voc3/sys/data\';":str, "defa | : : ult_pipe":str, 10:int); : | 666 | sqlcatalog.create_table("sys":str, "rmt_tbl9":str, nil:ptr, 0:int); | | 103 | sql.exportOperation(); | +------+-------------------------------------------------------------------------------------------------------------------------------------------------+
On 10 Jul 2020, at 5:31 PM, Ying Zhang Y.Zhang@cwi.nl wrote:
maybe you can prefix the queries with TRACE to look deeper into the execution of these queries to see where the time was spent?
On 10 Jul 2020, at 16:22, Ioannis Foufoulas <johnfouf@di.uoa.gr mailto:johnfouf@di.uoa.gr> wrote:
Probably not, but I ‘m trying to figure out why in versions prior to Aug.2018 I ‘m getting this:
sql>CREATE REMOTE TABLE rmt_tbl (c1 int) ON 'mapi:monetdb://127.0.0.1:50000/voc3/sys/data' monetdb://127.0.0.1:50000/voc3/sys/data'; operation successful sql:0.000 opt:0.359 run:0.473 clk:4.634 ms
While in versions after Aug. 2018 I have: sql>CREATE REMOTE TABLE rmt_tbl (c1 int) ON 'mapi:monetdb://127.0.0.1:50000/voc3/sys/data' monetdb://127.0.0.1:50000/voc3/sys/data'; operation successful sql:0.000 opt:0.068 run:0.057 clk:87.766 ms
On 10 Jul 2020, at 4:51 PM, Ying Zhang <Y.Zhang@cwi.nl mailto:Y.Zhang@cwi.nl> wrote:
On 10 Jul 2020, at 15:26, Ioannis Foufoulas <johnfouf@di.uoa.gr mailto:johnfouf@di.uoa.gr> wrote:
Hi, I am trying to develop a Python client which sends queries to different Monetdb servers and transfers the results between them using the remote tables functionalities. I’ve seen than my main overhead is execution of CREATE REMOTE TABLE statement. It requires 70-90msec so in cases with many transfers and fast and small queries this makes a difference. After looking at some older versions I’ve seen that the creation of a remote table was very fast before August 2018 (this is when authentication added). Is there any hint on this? Could the. authentication strategy change in someway so that it runs faster or is it possible to select among different authentication methods for the remote tables?
Not sure if that’s the cause of the overhead, but better authentication was indeed added since Aug2018 (https://www.monetdb.org/blog/remote-tables-auth https://www.monetdb.org/blog/remote-tables-auth)
Jennie
Best, Yannis
users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org mailto: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 Yannis,
The reasoning behind the authentication for remote tables is explained here: https://www.monetdb.org/blog/remote-tables-auth
The authentication scheme is using the same machinery that the server is using to authenticate any client. I don't think that using a different authentication scheme for remote tables is feasible right now. The idea is that the credentials you specify for a remote table need to be stored safely in the local authentication vault so that they can be retrieved and used when a connection needs to be established. This is more or less the same process as creating a new user.
What happens authentication-wise when you create a remote table is the following: - Check if the specified table already has an entry in the vault. If you find one delete it since this can only happen if an earlier transaction that created a table with the same name in the same schema did not commit. - Check if the user specified an already encrypted (hashed) password. If not, hash it. - Perform some simple checks that the password we are storing is indeed encrypted. - Create an entry in the credentials vault. - Commit the changes to disk.
You can try to avoid the hashing step which might be expensive, by providing a pre-computed hash of the remote password as detailed in the 3rd or 5th example in the above page. If you compiled MonetDB with a reasonably recent openssl library, or you are using the pre-compiled binaries we supply, the hash function used should be SHA512. Unfortunately the rest of the steps cannot be avoided.
Hope this helps.
Best regards, Panos.
On 7/10/20 5:22 PM, Ioannis Foufoulas wrote:
Probably not, but I ‘m trying to figure out why in versions prior to Aug.2018 I ‘m getting this:
sql>CREATE REMOTE TABLE rmt_tbl (c1 int) ON 'mapi:monetdb://127.0.0.1:50000/voc3/sys/data'; operation successful sql:0.000 opt:0.359 run:0.473 clk:4.634 ms
While in versions after Aug. 2018 I have: sql>CREATE REMOTE TABLE rmt_tbl (c1 int) ON 'mapi:monetdb://127.0.0.1:50000/voc3/sys/data'; operation successful sql:0.000 opt:0.068 run:0.057 clk:87.766 ms
On 10 Jul 2020, at 4:51 PM, Ying Zhang Y.Zhang@cwi.nl wrote:
On 10 Jul 2020, at 15:26, Ioannis Foufoulas <johnfouf@di.uoa.gr mailto:johnfouf@di.uoa.gr> wrote:
Hi, I am trying to develop a Python client which sends queries to different Monetdb servers and transfers the results between them using the remote tables functionalities. I’ve seen than my main overhead is execution of CREATE REMOTE TABLE statement. It requires 70-90msec so in cases with many transfers and fast and small queries this makes a difference. After looking at some older versions I’ve seen that the creation of a remote table was very fast before August 2018 (this is when authentication added). Is there any hint on this? Could the. authentication strategy change in someway so that it runs faster or is it possible to select among different authentication methods for the remote tables?
Not sure if that’s the cause of the overhead, but better authentication was indeed added since Aug2018 (https://www.monetdb.org/blog/remote-tables-auth https://www.monetdb.org/blog/remote-tables-auth)
Jennie
Best, Yannis
users-list mailing list users-list@monetdb.org mailto: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
Hi Yannis,
one more remark. Looking at your original message I get the impression that you're using the remote tables on a one-off basis. So, you're executing a particular distributed query, your Python client decides to move some data, it creates remote tables, accesses them and tears them down again.
However, the remote tables were originally conceived more as a permanent thing, something you set up as part of your schema.
Would it be possible to rework the Python client to create the remote tables at database initialization time or at startup time? That would sidestep the issue entirely.
Joeri
On 7/10/20 4:39 PM, Panagiotis Koutsourakis wrote:
Hi Yannis,
The reasoning behind the authentication for remote tables is explained here: https://www.monetdb.org/blog/remote-tables-auth
The authentication scheme is using the same machinery that the server is using to authenticate any client. I don't think that using a different authentication scheme for remote tables is feasible right now. The idea is that the credentials you specify for a remote table need to be stored safely in the local authentication vault so that they can be retrieved and used when a connection needs to be established. This is more or less the same process as creating a new user.
What happens authentication-wise when you create a remote table is the following:
- Check if the specified table already has an entry in the vault. If you find one delete it since this can only happen if an earlier transaction that created a table with the same name in the same schema did not commit.
- Check if the user specified an already encrypted (hashed) password. If not, hash it.
- Perform some simple checks that the password we are storing is indeed encrypted.
- Create an entry in the credentials vault.
- Commit the changes to disk.
You can try to avoid the hashing step which might be expensive, by providing a pre-computed hash of the remote password as detailed in the 3rd or 5th example in the above page. If you compiled MonetDB with a reasonably recent openssl library, or you are using the pre-compiled binaries we supply, the hash function used should be SHA512. Unfortunately the rest of the steps cannot be avoided.
Hope this helps.
Best regards, Panos.
On 7/10/20 5:22 PM, Ioannis Foufoulas wrote:
Probably not, but I ‘m trying to figure out why in versions prior to Aug.2018 I ‘m getting this:
sql>CREATE REMOTE TABLE rmt_tbl (c1 int) ON 'mapi:monetdb://127.0.0.1:50000/voc3/sys/data'; operation successful sql:0.000 opt:0.359 run:0.473 clk:4.634 ms
While in versions after Aug. 2018 I have: sql>CREATE REMOTE TABLE rmt_tbl (c1 int) ON 'mapi:monetdb://127.0.0.1:50000/voc3/sys/data'; operation successful sql:0.000 opt:0.068 run:0.057 clk:87.766 ms
On 10 Jul 2020, at 4:51 PM, Ying Zhang Y.Zhang@cwi.nl wrote:
On 10 Jul 2020, at 15:26, Ioannis Foufoulas <johnfouf@di.uoa.gr mailto:johnfouf@di.uoa.gr> wrote:
Hi, I am trying to develop a Python client which sends queries to different Monetdb servers and transfers the results between them using the remote tables functionalities. I’ve seen than my main overhead is execution of CREATE REMOTE TABLE statement. It requires 70-90msec so in cases with many transfers and fast and small queries this makes a difference. After looking at some older versions I’ve seen that the creation of a remote table was very fast before August 2018 (this is when authentication added). Is there any hint on this? Could the. authentication strategy change in someway so that it runs faster or is it possible to select among different authentication methods for the remote tables?
Not sure if that’s the cause of the overhead, but better authentication was indeed added since Aug2018 (https://www.monetdb.org/blog/remote-tables-auth https://www.monetdb.org/blog/remote-tables-auth)
Jennie
Best, Yannis
users-list mailing list users-list@monetdb.org mailto: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
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Finally, it seems that in some way this depends on the system. Trying the same in another server, I got a creation time of a remote table in 4msec using the latest version. Using various different servers, it seems that the speed of remote table definition varies from 4 to 90msec.
Not sure what’s the reason since using the old monetdb release (before the authentication) the creation of a remote table is fast in all the machines. I ‘ve checked the output of ./configure to see if there are different libraries installed. The following output is the same in all the machines:
* Enabled/disabled build options: strict is disabled (by default) assert is disabled (by default) debug is disabled (by default) optimize is enabled developer is disabled (by default) sanitizer is disabled (by default) profiler is disabled (by default)
* Enabled/disabled components: geom is enabled py3integration is enabled rintegration is enabled odbc is enabled testing is enabled embedded is disabled (by default)
* Available features/extensions: bz2 = yes curl = yes fits = no (cfitsio library not found) getaddrinfo = yes gdal = yes liblas = no (liblas-config not found) liblzma = yes libxml2 = yes lidar = no (liblas-config not found) lz4 = no (liblz4 not found) netcdf = yes openssl = yes proj = yes pthread = yes python3 = yes readline = yes regex = PCRE shp = yes snappy = no (snappy library not found) unixodbc = yes valgrind = no (by default) zlib = yes
On 10 Jul 2020, at 5:39 PM, Panagiotis Koutsourakis panagiotis.koutsourakis@monetdbsolutions.com wrote:
Hi Yannis,
The reasoning behind the authentication for remote tables is explained here: https://www.monetdb.org/blog/remote-tables-auth https://www.monetdb.org/blog/remote-tables-auth
The authentication scheme is using the same machinery that the server is using to authenticate any client. I don't think that using a different authentication scheme for remote tables is feasible right now. The idea is that the credentials you specify for a remote table need to be stored safely in the local authentication vault so that they can be retrieved and used when a connection needs to be established. This is more or less the same process as creating a new user.
What happens authentication-wise when you create a remote table is the following:
- Check if the specified table already has an entry in the vault. If you find one delete it since this can only happen if an earlier transaction that created a table with the same name in the same schema did not commit.
- Check if the user specified an already encrypted (hashed) password. If not, hash it.
- Perform some simple checks that the password we are storing is indeed encrypted.
- Create an entry in the credentials vault.
- Commit the changes to disk.
You can try to avoid the hashing step which might be expensive, by providing a pre-computed hash of the remote password as detailed in the 3rd or 5th example in the above page. If you compiled MonetDB with a reasonably recent openssl library, or you are using the pre-compiled binaries we supply, the hash function used should be SHA512. Unfortunately the rest of the steps cannot be avoided.
Hope this helps.
Best regards, Panos.
On 7/10/20 5:22 PM, Ioannis Foufoulas wrote:
Probably not, but I ‘m trying to figure out why in versions prior to Aug.2018 I ‘m getting this:
sql>CREATE REMOTE TABLE rmt_tbl (c1 int) ON 'mapi:monetdb://127.0.0.1:50000/voc3/sys/data'; operation successful sql:0.000 opt:0.359 run:0.473 clk:4.634 ms
While in versions after Aug. 2018 I have: sql>CREATE REMOTE TABLE rmt_tbl (c1 int) ON 'mapi:monetdb://127.0.0.1:50000/voc3/sys/data'; operation successful sql:0.000 opt:0.068 run:0.057 clk:87.766 ms
On 10 Jul 2020, at 4:51 PM, Ying Zhang Y.Zhang@cwi.nl wrote:
On 10 Jul 2020, at 15:26, Ioannis Foufoulas <johnfouf@di.uoa.gr mailto:johnfouf@di.uoa.gr <mailto:johnfouf@di.uoa.gr mailto:johnfouf@di.uoa.gr>> wrote:
Hi, I am trying to develop a Python client which sends queries to different Monetdb servers and transfers the results between them using the remote tables functionalities. I’ve seen than my main overhead is execution of CREATE REMOTE TABLE statement. It requires 70-90msec so in cases with many transfers and fast and small queries this makes a difference. After looking at some older versions I’ve seen that the creation of a remote table was very fast before August 2018 (this is when authentication added). Is there any hint on this? Could the. authentication strategy change in someway so that it runs faster or is it possible to select among different authentication methods for the remote tables?
Not sure if that’s the cause of the overhead, but better authentication was indeed added since Aug2018 (https://www.monetdb.org/blog/remote-tables-auth https://www.monetdb.org/blog/remote-tables-auth <https://www.monetdb.org/blog/remote-tables-auth https://www.monetdb.org/blog/remote-tables-auth>)
Jennie
Best, Yannis
users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org <mailto:users-list@monetdb.org mailto:users-list@monetdb.org> https://www.monetdb.org/mailman/listinfo/users-list https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list https://www.monetdb.org/mailman/listinfo/users-list
participants (4)
-
Ioannis Foufoulas -
Joeri van Ruth -
Panagiotis Koutsourakis -
Ying Zhang