search

wvanbergen / node-vertica

Pure javascript Vertica database client. Except it is written in CoffeeScript.
MIT License
44 stars 30 forks source link

Support for load balance connection options in Vertica 7.0? #27

Open awinder opened 10 years ago

awinder commented 10 years ago

In Vertica 7.0 theres support for in-database load balancing of queries (https://my.vertica.com/docs/7.0.x/HTML/Content/Authoring/AdministratorsGuide/LoadBalancing/AboutNativeConnectionLoadBalancing.htm) in a clustered setup. There seems to be a bunch of additional logic built in to the odbc/jdbc/ado drivers related to actually making the connection to the right machine, but at the core it seems to be a new connection option that results in the server communicating back with what server in the cluster that should be utilized for the next query. Is there any way that this could be supported in this library? I'd be willing to help out in any way I can, but I'm not really sure how I would get started on building that support in. Any pointers on how to map stuff that the ODBC/JDBC drivers are doing with Vertica, to actions I could take over the socket connection?

wvanbergen commented 10 years ago

I think it could be supported because the logic is straightforward. The problem is that the protocol is not documented anywhere; so I wouldn't know what actually goes over the wire. If you can somehow dump the TCP traffic between the JDBC client and the server when going through this, that would be a great start

awinder commented 10 years ago

I took some verbose dumps with tcpdump, hopefully this is helpful. I'd definitely be willing to help in any way I can, so let me know if theres anything more I can do.

Node-Vertica.connect();

20:18:39.667386 IP 10.0.0.1.55841 > 10.0.0.101.pyrrho: Flags [S], seq 54504026, win 65535, options [mss 1460,nop,wscale 4,nop,nop,TS val 1022639786 ecr 0,sackOK,eol], length 0
20:18:39.667414 IP 10.0.0.101.pyrrho > 10.0.0.1.55841: Flags [S.], seq 3397161805, ack 54504027, win 14480, options [mss 1460,sackOK,TS val 437459265 ecr 1022639786,nop,wscale 7], length 0
20:18:39.667564 IP 10.0.0.1.55841 > 10.0.0.101.pyrrho: Flags [.], ack 1, win 8235, options [nop,nop,TS val 1022639786 ecr 437459265], length 0
20:18:39.667648 IP 10.0.0.1.55841 > 10.0.0.101.pyrrho: Flags [P.], seq 1:9, ack 1, win 8235, options [nop,nop,TS val 1022639786 ecr 437459265], length 8
20:18:39.667655 IP 10.0.0.101.pyrrho > 10.0.0.1.55841: Flags [.], ack 9, win 114, options [nop,nop,TS val 437459265 ecr 1022639786], length 0
20:18:39.669321 IP 10.0.0.101.pyrrho > 10.0.0.1.55841: Flags [P.], seq 1:2, ack 9, win 114, options [nop,nop,TS val 437459266 ecr 1022639786], length 1
20:18:39.669341 IP 10.0.0.1.55841 > 10.0.0.101.pyrrho: Flags [.], ack 2, win 8235, options [nop,nop,TS val 1022639787 ecr 437459266], length 0
20:18:40.034738 IP 10.0.0.1.55841 > 10.0.0.101.pyrrho: Flags [P.], seq 9:35, ack 2, win 8235, options [nop,nop,TS val 1022639787 ecr 437459266], length 26
20:18:40.034762 IP 10.0.0.1.55841 > 10.0.0.101.pyrrho: Flags [P.], seq 9:35, ack 2, win 8235, options [nop,nop,TS val 1022640151 ecr 437459266], length 26
20:18:40.034945 IP 10.0.0.101.pyrrho > 10.0.0.1.55841: Flags [.], ack 35, win 114, options [nop,nop,TS val 437459632 ecr 1022640151,nop,nop,sack 1 {9:35}], length 0
20:18:40.035690 IP 10.0.0.101.pyrrho > 10.0.0.1.55841: Flags [P.], seq 2:163, ack 35, win 114, options [nop,nop,TS val 437459633 ecr 1022640151], length 161
20:18:40.035965 IP 10.0.0.1.55841 > 10.0.0.101.pyrrho: Flags [.], ack 163, win 8225, options [nop,nop,TS val 1022640152 ecr 437459633], length 0

JDBC Connect w/o the load-balanced option

21:21:43.650686 IP 10.0.0.1.57021 > 10.0.0.101.pyrrho: Flags [S], seq 3570590657, win 65535, options [mss 1460,nop,wscale 4,nop,nop,TS val 1026386004 ecr 0,sackOK,eol], length 0
21:21:43.650723 IP 10.0.0.101.pyrrho > 10.0.0.1.57021: Flags [S.], seq 543792005, ack 3570590658, win 14480, options [mss 1460,sackOK,TS val 441243248 ecr 1026386004,nop,wscale 7], length 0
21:21:43.650848 IP 10.0.0.1.57021 > 10.0.0.101.pyrrho: Flags [.], ack 1, win 8235, options [nop,nop,TS val 1026386004 ecr 441243248], length 0
21:21:44.009196 IP 10.0.0.1.57021 > 10.0.0.101.pyrrho: Flags [P.], seq 1:245, ack 1, win 8235, options [nop,nop,TS val 1026386362 ecr 441243248], length 244
21:21:44.009380 IP 10.0.0.101.pyrrho > 10.0.0.1.57021: Flags [.], ack 245, win 122, options [nop,nop,TS val 441243607 ecr 1026386362], length 0
21:21:44.009812 IP 10.0.0.101.pyrrho > 10.0.0.1.57021: Flags [P.], seq 1:162, ack 245, win 122, options [nop,nop,TS val 441243607 ecr 1026386362], length 161
21:21:44.009946 IP 10.0.0.1.57021 > 10.0.0.101.pyrrho: Flags [.], ack 162, win 8225, options [nop,nop,TS val 1026386362 ecr 441243607], length 0
21:21:44.010373 IP 10.0.0.1.57021 > 10.0.0.101.pyrrho: Flags [P.], seq 245:279, ack 162, win 8225, options [nop,nop,TS val 1026386363 ecr 441243607], length 34
21:21:44.010569 IP 10.0.0.101.pyrrho > 10.0.0.1.57021: Flags [P.], seq 162:197, ack 279, win 122, options [nop,nop,TS val 441243608 ecr 1026386363], length 35
21:21:44.010679 IP 10.0.0.1.57021 > 10.0.0.101.pyrrho: Flags [.], ack 197, win 8223, options [nop,nop,TS val 1026386363 ecr 441243608], length 0
21:21:44.011662 IP 10.0.0.1.57021 > 10.0.0.101.pyrrho: Flags [P.], seq 279:313, ack 197, win 8223, options [nop,nop,TS val 1026386364 ecr 441243608], length 34
21:21:44.012341 IP 10.0.0.101.pyrrho > 10.0.0.1.57021: Flags [P.], seq 197:232, ack 313, win 122, options [nop,nop,TS val 441243610 ecr 1026386364], length 35
21:21:44.012467 IP 10.0.0.1.57021 > 10.0.0.101.pyrrho: Flags [.], ack 232, win 8221, options [nop,nop,TS val 1026386364 ecr 441243610], length 0
21:21:44.114149 IP 10.0.0.1.57021 > 10.0.0.101.pyrrho: Flags [P.], seq 313:372, ack 232, win 8221, options [nop,nop,TS val 1026386461 ecr 441243610], length 59
21:21:44.121543 IP 10.0.0.101.pyrrho > 10.0.0.1.57021: Flags [P.], seq 232:316, ack 372, win 122, options [nop,nop,TS val 441243719 ecr 1026386461], length 84
21:21:44.121729 IP 10.0.0.1.57021 > 10.0.0.101.pyrrho: Flags [.], ack 316, win 8215, options [nop,nop,TS val 1026386468 ecr 441243719], length 0
21:21:44.124165 IP 10.0.0.1.57021 > 10.0.0.101.pyrrho: Flags [P.], seq 372:542, ack 316, win 8215, options [nop,nop,TS val 1026386470 ecr 441243719], length 170
21:21:44.135760 IP 10.0.0.101.pyrrho > 10.0.0.1.57021: Flags [P.], seq 316:551, ack 542, win 130, options [nop,nop,TS val 441243733 ecr 1026386470], length 235
21:21:44.135974 IP 10.0.0.1.57021 > 10.0.0.101.pyrrho: Flags [.], ack 551, win 8201, options [nop,nop,TS val 1026386481 ecr 441243733], length 0
21:21:44.137973 IP 10.0.0.1.57021 > 10.0.0.101.pyrrho: Flags [P.], seq 542:1594, ack 551, win 8201, options [nop,nop,TS val 1026386482 ecr 441243733], length 1052
21:21:44.151249 IP 10.0.0.101.pyrrho > 10.0.0.1.57021: Flags [.], seq 551:4895, ack 1594, win 147, options [nop,nop,TS val 441243748 ecr 1026386482], length 4344
21:21:44.151292 IP 10.0.0.101.pyrrho > 10.0.0.1.57021: Flags [P.], seq 4895:5432, ack 1594, win 147, options [nop,nop,TS val 441243749 ecr 1026386482], length 537
21:21:44.151292 IP 10.0.0.1.57021 > 10.0.0.101.pyrrho: Flags [.], ack 3447, win 8020, options [nop,nop,TS val 1026386495 ecr 441243748], length 0
21:21:44.151292 IP 10.0.0.1.57021 > 10.0.0.101.pyrrho: Flags [.], ack 5432, win 8067, options [nop,nop,TS val 1026386495 ecr 441243748], length 0
21:21:44.275731 IP 10.0.0.1.57021 > 10.0.0.101.pyrrho: Flags [P.], seq 1594:1653, ack 5432, win 8192, options [nop,nop,TS val 1026386618 ecr 441243748], length 59
21:21:44.283781 IP 10.0.0.101.pyrrho > 10.0.0.1.57021: Flags [P.], seq 5432:5516, ack 1653, win 147, options [nop,nop,TS val 441243881 ecr 1026386618], length 84
21:21:44.284038 IP 10.0.0.1.57021 > 10.0.0.101.pyrrho: Flags [.], ack 5516, win 8186, options [nop,nop,TS val 1026386625 ecr 441243881], length 0

JDBC Connect w/ the load-balanced option set to true

21:23:50.282582 IP 10.0.0.1.57039 > 10.0.0.101.pyrrho: Flags [S], seq 2668480624, win 65535, options [mss 1460,nop,wscale 4,nop,nop,TS val 1026511617 ecr 0,sackOK,eol], length 0
21:23:50.282609 IP 10.0.0.101.pyrrho > 10.0.0.1.57039: Flags [S.], seq 2332927645, ack 2668480625, win 14480, options [mss 1460,sackOK,TS val 441369880 ecr 1026511617,nop,wscale 7], length 0
21:23:50.282727 IP 10.0.0.1.57039 > 10.0.0.101.pyrrho: Flags [.], ack 1, win 8235, options [nop,nop,TS val 1026511617 ecr 441369880], length 0
21:23:50.284003 IP 10.0.0.1.57039 > 10.0.0.101.pyrrho: Flags [P.], seq 1:9, ack 1, win 8235, options [nop,nop,TS val 1026511618 ecr 441369880], length 8
21:23:50.284003 IP 10.0.0.101.pyrrho > 10.0.0.1.57039: Flags [.], ack 9, win 114, options [nop,nop,TS val 441369882 ecr 1026511618], length 0
21:23:50.284459 IP 10.0.0.101.pyrrho > 10.0.0.1.57039: Flags [P.], seq 1:2, ack 9, win 114, options [nop,nop,TS val 441369882 ecr 1026511618], length 1
21:23:50.284560 IP 10.0.0.1.57039 > 10.0.0.101.pyrrho: Flags [.], ack 2, win 8235, options [nop,nop,TS val 1026511618 ecr 441369882], length 0
21:23:50.583314 IP 10.0.0.1.57039 > 10.0.0.101.pyrrho: Flags [P.], seq 9:253, ack 2, win 8235, options [nop,nop,TS val 1026511916 ecr 441369882], length 244
21:23:50.584188 IP 10.0.0.101.pyrrho > 10.0.0.1.57039: Flags [P.], seq 2:163, ack 253, win 122, options [nop,nop,TS val 441370182 ecr 1026511916], length 161
21:23:50.584318 IP 10.0.0.1.57039 > 10.0.0.101.pyrrho: Flags [.], ack 163, win 8225, options [nop,nop,TS val 1026511917 ecr 441370182], length 0
21:23:50.584710 IP 10.0.0.1.57039 > 10.0.0.101.pyrrho: Flags [P.], seq 253:287, ack 163, win 8225, options [nop,nop,TS val 1026511917 ecr 441370182], length 34
21:23:50.585000 IP 10.0.0.101.pyrrho > 10.0.0.1.57039: Flags [P.], seq 163:198, ack 287, win 122, options [nop,nop,TS val 441370182 ecr 1026511917], length 35
21:23:50.585018 IP 10.0.0.1.57039 > 10.0.0.101.pyrrho: Flags [.], ack 198, win 8223, options [nop,nop,TS val 1026511917 ecr 441370182], length 0
21:23:50.585561 IP 10.0.0.1.57039 > 10.0.0.101.pyrrho: Flags [P.], seq 287:321, ack 198, win 8223, options [nop,nop,TS val 1026511918 ecr 441370182], length 34
21:23:50.585796 IP 10.0.0.101.pyrrho > 10.0.0.1.57039: Flags [P.], seq 198:233, ack 321, win 122, options [nop,nop,TS val 441370183 ecr 1026511918], length 35
21:23:50.585874 IP 10.0.0.1.57039 > 10.0.0.101.pyrrho: Flags [.], ack 233, win 8221, options [nop,nop,TS val 1026511918 ecr 441370183], length 0
21:23:50.650528 IP 10.0.0.1.57039 > 10.0.0.101.pyrrho: Flags [P.], seq 321:380, ack 233, win 8221, options [nop,nop,TS val 1026511980 ecr 441370183], length 59
21:23:50.657247 IP 10.0.0.101.pyrrho > 10.0.0.1.57039: Flags [P.], seq 233:317, ack 380, win 122, options [nop,nop,TS val 441370254 ecr 1026511980], length 84
21:23:50.657717 IP 10.0.0.1.57039 > 10.0.0.101.pyrrho: Flags [.], ack 317, win 8215, options [nop,nop,TS val 1026511986 ecr 441370254], length 0
21:23:50.660228 IP 10.0.0.1.57039 > 10.0.0.101.pyrrho: Flags [P.], seq 380:550, ack 317, win 8215, options [nop,nop,TS val 1026511988 ecr 441370254], length 170
21:23:50.668052 IP 10.0.0.101.pyrrho > 10.0.0.1.57039: Flags [P.], seq 317:552, ack 550, win 130, options [nop,nop,TS val 441370266 ecr 1026511988], length 235
21:23:50.668241 IP 10.0.0.1.57039 > 10.0.0.101.pyrrho: Flags [.], ack 552, win 8201, options [nop,nop,TS val 1026511996 ecr 441370266], length 0
21:23:50.670343 IP 10.0.0.1.57039 > 10.0.0.101.pyrrho: Flags [P.], seq 550:1602, ack 552, win 8201, options [nop,nop,TS val 1026511998 ecr 441370266], length 1052
21:23:50.683431 IP 10.0.0.101.pyrrho > 10.0.0.1.57039: Flags [.], seq 552:4896, ack 1602, win 147, options [nop,nop,TS val 441370281 ecr 1026511998], length 4344
21:23:50.683513 IP 10.0.0.101.pyrrho > 10.0.0.1.57039: Flags [P.], seq 4896:5433, ack 1602, win 147, options [nop,nop,TS val 441370281 ecr 1026511998], length 537
21:23:50.683620 IP 10.0.0.1.57039 > 10.0.0.101.pyrrho: Flags [.], ack 3448, win 8020, options [nop,nop,TS val 1026512010 ecr 441370281], length 0
21:23:50.683631 IP 10.0.0.1.57039 > 10.0.0.101.pyrrho: Flags [.], ack 5433, win 8067, options [nop,nop,TS val 1026512010 ecr 441370281], length 0
21:23:50.772127 IP 10.0.0.1.57039 > 10.0.0.101.pyrrho: Flags [P.], seq 1602:1661, ack 5433, win 8192, options [nop,nop,TS val 1026512095 ecr 441370281], length 59
21:23:50.779497 IP 10.0.0.101.pyrrho > 10.0.0.1.57039: Flags [P.], seq 5433:5517, ack 1661, win 147, options [nop,nop,TS val 441370377 ecr 1026512095], length 84
21:23:50.779799 IP 10.0.0.1.57039 > 10.0.0.101.pyrrho: Flags [.], ack 5517, win 8186, options [nop,nop,TS val 1026512102 ecr 441370377], length 0
wvanbergen commented 10 years ago

Unfortunately this dump does not contain the contents of the packets :(

awinder commented 10 years ago

Sorry about that! I ran some new tcpdump tests and hopefully these contain the full contents, but I'm not sure if I did this right this time around either. I included the command I used so if theres something simple I can tweak to get the right info here, I'd be glad to run them again. Let me know, thanks:

https://gist.github.com/awinder/9627975

wvanbergen commented 10 years ago

I've been staring at the TCP dump for a while, but I don't find anything different between the node dump and the ODBC dump that could indicate how it works. Unfortunately, the ODBC example doesn't actually connect to a different host during the process.

The ODBC driver does a lot of extra initialization after the connection has been established, but that would be too late. The only thing that could make a difference is the connection options that the ODBC driver sends. Vertica could do something based on the version number that it receives from the client. The node driver doesn't specify any client name, version or OS.

diwu1989 commented 10 years ago

Have you tried to de-compile the JDBC driver?

The last time I had to figure out how something was done, I used this decompiler http://jd.benow.ca/ on the client jar file and just read what the java client did. The decompiled code is very readable, it should be easier than trying to reverse the protocol based on the tcp dumps.

wvanbergen commented 10 years ago

I don't have a Vertica 7 cluster available to me, so it's unlikely I will put in more effort to make this work any time soon. Feel free to post any additional information here.