Question regarding mysql_thread___threshold_resultset_size.

[pranavpandey1998official]

Hi, we are experimenting with mysql_thread___threshold_resultset_size and found ourself in a unique situation

How i understand mysql_thread___threshold_resultset_size is that proxysql will first get the whole resultset from the backend in the resultset and then send it to the client, (mentioned here #1031)

So we want to minimize the latency , for this we set mysql_thread___threshold_resultset_size to be minimum so it is in streaming mode. so I am not waiting for the resultset to be filled hence its always in streaming mode

Now there is also a unique behaviour that if the resultset increases beyond 8* resultset_size , then it will throtle the backend connections, which will again increase the latency,

What is the solution here, I don't want the resultset I just want it to be in passthough mode

[renecannao]

Hi @pranavpandey1998official .

Thank you for using ProxySQL.

Now there is also a unique behaviour that if the resultset increases beyond 8* resultset_size , then it will throtle the backend connections, which will again increase the latency,

I think a detail slipped through here.
If the buffered resultset goes beyond that threshold it means that the client is not fetching the data fast enough from proxysql, thus proxysql pauses fetching data from the backend until the client consumes enough data.

In light of this, I am not sure if the question is still relevant

[pranavpandey1998official]

Hi @renecannao, thank you for the quick response!
As I mentioned earlier, we’ve set mysql_threads__threshold_resultset_size to a very low value to ensure we’re operating in streaming mode.

I suspect that your 8x threshold_resultset_size limit is effectively capping the TCP window size, which may be causing a network bottleneck and increasing the latenency

When streaming mode is enabled, data starts flowing through. But since the result set is large, the TCP window eventually grows up to 8 * threshold_resultset_size or more. now as soon as it ties to grow beyond a certiain size I think proxysql is getting into pause mode , so its not actually the client that is getting throttled but tcp windowing showing that its throttled.

the network throughput seems to be constrained by that cap.

[renecannao]

Hi @pranavpandey1998official .

Thank you for the follow up.
proxysql enters in pause/throttling mode if unable to write in the TCP socket to the client.
It could be a consequence of TCP window size, network buffer size, slow client , etc, or...

I think I now understood what you are saying. Because mysql-threshold_resultset_size is set very low, proxysql switches to pause/throttling move very quickly (as soon as 8KB of data is not read).

What is the size of the resultset? Are you actually sure that tuning mysql-threshold_resultset_size is beneficial at all?

[pranavpandey1998official]

hi @renecannao ,

Through our benchmarking, we observed a significant improvement in latency metrics when mysql-threshold_resultset_size was set to a low value. Based on this, we configured mysql-threshold_resultset_size to its minimum value (1KB) to optimize for latency

But when we experimented with some of our clients whose result set sizes are in the MB range (we don't know the exact size, but it can grow up to 100MB), we observed that ProxySQL was experiencing a bottleneck in bytes sent and received from the backend.

As soon as we increased the mysql-threshold_resultset_size, we saw a corresponding increase in the number of bytes recived. from proxysql, you can see that its hovering around 10M before and after the shootup

Here's the graph:

We are thinking of adding a variable to configure the 8x limit, so that, for example, we could set it to 8000x—after which ProxySQL would pause. Do you think that would be a good approach, considering we want to keep mysql-threshold_resultset_size small to retain the latency improvements we've observed?

[renecannao]

Through our benchmarking, we observed a significant improvement in latency metrics when mysql-threshold_resultset_size was set to a low value. Based on this, we configured mysql-threshold_resultset_size to its minimum value (1KB) to optimize for latency

Can you show this please?
And with what resultset size?

... considering we want to keep mysql-threshold_resultset_size small to retain the latency improvements we've observed?

I am not sure this is true.
Using small buffers will lead to a lot of context switch, potentially small packets, that could actually increase latency instead of reducing it.