AdministratorMay 9, 2020 at 5:06 pm
Yes, the TCP packet size could change depending on various situations. The main idea behind this scenario is to properly troubleshoot the Redshift issue. In the scenario, it says that the development team completed their coding and deployed their new application in AWS. It never said that they have done load testing for their application.
One key phrase here is that “all queries stop responding in Redshift”, which alludes to an issue in the database/data warehouse -tier and not on the web tier. Therefore, the issue here is not about “simple pings or traces” to the web server, but simple SELECT statements to Redshift, each with a small packet size. On those few days that the application was running, the Redshift cluster only received simple SELECT statements with small packet sizes. As the load spikes up, the number of complex, and packet-heavy, SELECT statements or INSERT commands, are received by Redshift. If the MTU is not properly configured, then the query could hang or dropped.
Just as mentioned above, you can test this out by launch a single-node Redshift (dc2.large) instance then configure your SQL client tool to send and receive small packets by using a simple SELECT statement or so. And conversely, use a complex INSERT or COPY command that loads a large amount of data into a table from a data file. That will shoot up the packet size and your request may fail. This is the same thing that is happening in the scenario.