Tracing Tableau to Postgres connectivity issue using Wireshark!

I spent last couple of weeks trying to resolve connection issues from Tableau Server 10.3 to Postgres 9.6.6.  If you are not familiar with Tableau , it  is popular enterprise grade  visualization tool  allow advanced analytic capabilities to understand and visualize  data .  As it is very popular in the industry, it is obvious that tableau has to talk with  a popular database in the industry, which is Postgres!

Issue:    My developers could able to connect to Postgres DB using Tableau Desktop on their laptop without any issue and make LIVE connection to Postgres 9.6 database.  However,  when they publish same dashboard with LIVE connection to database, it was hanging on the tableau server.

To reproduce the issue, we installed and tried to use Tableau Desktop application on Tableau server itself. The Desktop was hanging while connecting to Postgres database. This made it clear that there is some problem on connecting from Tableau server only.  Over the last couple of weeks I have looked various things to understand and resolve the issue:

1.  Database encoding ; UTF8 Vs ANSI
2. Tested Postgres ODBC drivers on Tableau Server (Windows 2016)
3. A lot of troubleshooting because the connection to empty database was working !!
4. Working with tableau support to explain and track down issue
5. Windows settings

None of the above helped!

Finally,  Wireshark  came to rescue ! We traced the network traffic on port 5432 on Windows 2016 while it was making the connection to Postgres database running on Linux server.

In the Wireshark logs, when the TCP communicates with the Postgres database, filtering down to the server  indicates that the TCP/IP sequencing is inconsistent. When initiating a connection, sequence numbers are maintained on each side of the connection. In this case, the packets are sent and received from the Postgres Server (PDB), and Tableau Desktop (TD). The sequence number is comprised of both values added together to output a single value to ensure information is accurate.

Typically, TCP data transmission is sequentially ordered. Each packed has an acknowledgement number, known as ACK. The value is equal to the next chronological sequence number from the packet that server has just received.

Like pages in a book, we expect a sequential order. The Wireshark logs indicate the PDB sequence number is not consistent with the TD sequence number as shown below:

1. PDB > TD Sequence=1;Length=14;Next Sequence=15
2. TD > PDB Sequence=1;Length=34;Next Sequence=35;Acknowledge=15
3. PDB > TD Sequence=15;Length=304;Next Sequence=319;Acknowledge=35
4. TD > PDB Sequence=35;Length=174;Next Sequence=209;Acknowledge=319

In the good packets sequence  above, similar to a book of pages, the first line indicates we sequentially expect that if there are 14 pages in the database, the next packet on the next page should from Tableau Desktop should be 15. In the second line, Tableau Desktop returns the response on page 1 and has 34 pages total. The next packet sent will start on page 35 from Postgres. Third, on the database side, PDB indicates to Tableau Desktop, page 15 is the starting page with a total of 304 pages. The next page from Tableau Desktop should start at 319.

Let’s look at problematic lines in below  screenshot for the packets captured  between numbers 7701-7710:

PDB > TD Sequence=1283;Length=32;Next Sequence=1315;Acknowledge=1963
TD > PDB Sequence=1963;Length=40;Next Sequence=2003;Acknowledge=1315
PDB > TD Sequence=1315;Length=22;Next Sequence=1337;Acknowledge=2003
TD > PDB Sequence=2003;Length=343;Next Sequence=2346;Acknowledge=1337
PDB > TD Sequence=9529;Length=1449;Next Sequence=10978;Acknowledge=2346
TD > PDB Sequence=2346;Length=0;;Acknowledge=1337

In the second to last line above, the data has been skipped. PDB expects the next packet should start at byte #1337, but instead the starting point is at byte #9529. Normally, in situations where the bytes don’t match, this would indicate that some of the packets didn’t get captured.

For example, Wireshark missed a few messages. But the ACK value for that packet points to the last packet we captured from TD > PDB, which clearly states it expects PDB to begin its next packet from byte #1337. Not only did our sequence number increment unexpectedly, it increased suspiciously at the value of: 8,192. That’s the exact number of bytes in 8KB (8*2^10=8192).

As a result of the bad packet mismatch, Tableau Desktop machine recognizes the information has been skipped according to the sequence number and re-requests a packet starting at byte #1337, however, PDB does not respond.

To track down packet loss.. you can ping the other server with different packet size…

 PS C:\Windows\system32> ping 192.168.10.100 -l 2000 -f
 
 Pinging 192.168.10.100 with 2000 bytes of data:
 Reply from 192.168.10.100: bytes=2000 time<1ms TTL=64
 Reply from 192.168.10.100: bytes=2000 time<1ms TTL=64
 Reply from 192.168.10.100: bytes=2000 time<1ms TTL=64
 Reply from 192.168.10.100: bytes=2000 time<1ms TTL=64
 
 
 
 PS C:\Windows\system32> ping 192.168.10.100 -l 2100 -f
 
 Pinging 192.168.10.100 with 2100 bytes of data:
 Request timed out.
 Request timed out.
 Request timed out.
 Request timed out.

As you can see in output above, when the package size is larger than 200 bytes, the packets loss is 100%.

In terms on root cause, it turned out to be MTU size mismatch issue. Once the MTU size is adjusted on switch and both  Tableau Windows and Postgres Unix database server, the packet loss issue has been resolved and Tableau managed to connect to Postgres without any issue.

It was a roller coaster experience dealing with mysterious packet loss issue. In case, if you come across similar connectivity issue between application to database, you might want to make sure you are not having packet loss issue. Wireshark can be your friend 🙂

Thanks for reading and happy holidays 🙂