Creating a manual network delay on MySQL (or almost any) connections.

While working in TransLoc Inc. I had to stress test a component that writes to a MySQL database in a very frequent manner. Each process of the component will update about 10-20 rows in one table every second, and there will be about fifty of those processes running.

In the production environment, the component does not run on the same server as the MySQL server, so there is a network latency in each query. Thus, the result of stress testing the component on my own machine will not be realistic because it does not take the network latency into account.

To create a manual network delay in the testing, I used the SSH Tunnel to forward my MySQL request to a remote host, and forward it back onto the MySQL server on my own machine using the following command:

ssh  -N -L 23306:127.0.0.1:60000 -R 60000:127.0.0.1:3306

Command break down:

• ssh -N creates a SSH tunnel without executing any command on the remote host. This way, you won't see the usual remote shell command line after you execute the command.
• -L 23306:127.0.0.1:60000 forwards the local connection to port 23306 to the 127.0.0.1 of the remote host port 60000. Which means, when a client tries to connect to your local machine port 23306, the request will have same effect of doing it on the remote host to 127.0.0.1:60000.
• -R 60000:127.0.0.1:3306 forwards a connection to the remote host port 60000 to the port 3306 of 127.0.0.1 of your local machine. it opens the port 60000 on the remote host. When there is a connection to the port, it is forwarded to the 127.0.0.1:3306 of the local machine.

To explain SSH tunnel a little bit more, here's a better example:

Imagine the source machine wants to connect to a port on the target machine. But both machines are in a sub-network and cannot connect to the each other directly. To solve this problem, An SSH tunnel can be created on the local host to the remote host using this command:

ssh 61.21.61.8 -N -L 12345:10.10.10.200:54321

Note that 61.21.61.8 is the public IP address the remote host have. It must be an address that can be seen by the local host. Now if the source machine make a connection request to local host port 12345, the request will be forwarded to the remote host, then to the target machine port 54321. This way, a program listening on port 54321 on target machine can receive the request and accept the connection request. The source machine can just connect to 192.168.1.1:12345 and it will be equivalent to directly connect to 10.10.10.200:54321 on remote host.

For more information, you can always refer to the manual page of ssh.

man ssh