Some time ago I have shown that the proxy can do connection pooling and can keep server-side connections open to be reused by another client later. The keepalive tutorial shows how this can be implemented.
When it comes to reusing a server-connection for multiple client connections we have to face a small problem:
SQL connections aren't stateless (temporary tables, session variables, ...)
We have to handle this somehow.
When you use this feature the proxy will clean up for you by default, by issuing a COM_CHANGE_USER as the first command that is sent to the server. It basicly resets the connection and re-authenticates the user. You always get a clean environment and can't really tell that the server-side connection was open all the time.
But you don't really win performance either. A small benchmark shall give you an idea.
# connecting the client to the server ...
11:58:41.241956 accept(7, {sa_family=AF_INET, sin_port=htons(26093), ...) = 8
11:58:41.243603 connect(9, {sa_family=AF_INET, sin_port=htons(3306), ...)
# forwarding the hand-shake ...
11:58:41.248285 recvfrom(9, "=\0\0\0\n5.2.5-alpha-log...
11:58:41.248752 writev(8, [{"=\0\0\0\n5.2.5-alpha-log...
# sending the client-auth back the server ...
11:58:41.258674 recvfrom(8, ":\0\0\1...
11:58:41.259215 writev(9, [{":\0\0\1...
# server is happy
11:58:41.260984 recvfrom(9, "\7\0\0\2...
11:58:41.261392 writev(8, [{"\7\0\0\2...
This. takes about 20ms over all. As we use the mysql shell as test-driver we have to remove the small gap of 10ms between to first writev and the recvfrom(8, .... In that time the mysql shell is reading some XML files. We will see them same gap in the next tests too.
# proxy accept()s client and sends the known server hand-shake
12:03:42.334554 accept(7, {sa_family=AF_INET, sin_port=htons(4061), ...) = 8
12:03:42.336101 writev(8, [{"=\0\0\0\n5.2.5-alpha-log...
# get client-auth and forward it as COM_CHANGE_USER
12:03:42.348278 recvfrom(8, ":\0\0\1...
12:03:42.348830 writev(9, [{"\34\0\0\0...
# patch the response and forward it
12:03:42.350526 recvfrom(9, "\7\0\0\1...
12:03:42.351035 writev(8, [{"\7\0\0\2...
With the keepalive script we reduce to the overall time to 17ms, minus 12ms we get 5ms. Just by removing the initial connect and connection setup we already safe 5ms.
# accept client and do a internal auth
12:07:31.968788 accept(7, {sa_family=AF_INET, sin_port=htons(18500), ...) = 8
# send old hand-shake and get the client-auth
12:07:31.970350 writev(8, [{"=\0\0\0\n5.2.5-alpha-log
12:07:31.982274 recvfrom(8, ":\0\0\1\205...
# compare it internally and ACK it
12:07:31.982837 writev(8, [{"\7\0\0\2...
With removing the COM_CHANGE_USER too, we can get the whole client-connect down to 14ms overall, minus 12ms for the XML reading it is 2ms.
Now you have the option between performance and correctness.
All the tests were done on localhost (mysql, proxy and mysqld are on the same box). If you add a network in between the latency will go up and the effect of the reduced number of packets will improve too. If you care about performance, think about "skip-name-resolve".
Comments