Imagine that you have a postgresql server that is capable of handling 10 SQL queries over psql (Network TCP ) at once, without service degrading. ie the performance of postgresql does not slow down, and each query is fulfilled within an expected and acceptable time.
Imagine that as the number of concurrent SQL queries increases over and above 10, that the performance degrades and each query takes longer to fulfil. Eventually the queries take an excessive amount of time that is not acceptable for good service delivery. Often if we could simply queue the SQL queries (psql sessions) and allow only say 10 concurrent queries active at any given time, performance overal will be better.
Note that an assumption is made that each new TCP _sessions_ equates to one discrete SQL query. In my situation, it does, and so queuing TCP session will effectively queue SQL queries.
One way to manage queuing is with HAproxy. I use HAProxy for its more common purpose - Load balancing HTTP service traffic across many web servers. But I also use it for plain old TCP load management too, simply becuse its there and available.
HAproxy can run in HTTP mode or in plain TCP mode. In TCP mode, HAPRoxy can manage the psql sessions without any protocol 'intelligence' which is exactly what I need. I do not use HAProxy to load balance psql sessions to multiple postgresql back ends by the way. It is simply used as a queuing mechanism for service load management.
An example configuration:
frontend pg_frontend
bind 192.168.0.140:5432
mode tcp
option tcplog
default_backend pg_backend
maxconn 20
backend pg_backend
# The balance algo won't matter
balance roundrobin
mode tcp
option tcplog
fullconn 15
timeout queue 120000
server pg1 192.168.0.142:5432 minconn 10 maxconn 20 check
The front end section 'pg_frontend' binds on 5432 the usual postgresql socket, on IP 192.168.0.140. We set the front end to run in tcp mode, turn on tcp logging, and tell it to use pg_backend as the default back end. I have maxconn for the front end set to 20, at which the tcp backlog of the operating system's tcp stack will queue up OR drop.
The pg_backend is where the queuing is performed by HAproxy. I define a balance algorithm even though it is not used for a single server like this, it is needed by HAproxy configuration syntax rules. The mode is again TCP, and I have tcp logging turned on.
The key settings for queuing are fullconn, minconn and maxconn. Under normal circumstances, the back end server will be given up to minconn (10) sessions concurrently. Sessions will queue up if they exceed 10. Once the concurrent sessions active and queued reaches 15 (fullconn), the HAProxy will begin increasing the number of concurrent sessions to the server, ramping up as the number of concurrent and queued sessions approach a total of 20. Once 20 concurrent sessions are active, any sessions in excess are left to the operating system's TCP backlog to manage or drop.
The last configuration directive is timeout queue 120000 - It is a safety net, allowing sessions to remain in the HAProxy backend queue for up to 2 minutes before expiring and being dropped.
The effect of fullconn, minconn and maxconn has is to allow concurrent sessions to burst, handling brief periods of higher load, while also queuing up to 5 sessions at any given time when the total number of sessions is 15 or less.
Note that fullconn, maxconn, and minconn at such low values is a little contrived, so as to simplify my explanation and example. I use larger values than this in reality
Imagine that as the number of concurrent SQL queries increases over and above 10, that the performance degrades and each query takes longer to fulfil. Eventually the queries take an excessive amount of time that is not acceptable for good service delivery. Often if we could simply queue the SQL queries (psql sessions) and allow only say 10 concurrent queries active at any given time, performance overal will be better.
Note that an assumption is made that each new TCP _sessions_ equates to one discrete SQL query. In my situation, it does, and so queuing TCP session will effectively queue SQL queries.
One way to manage queuing is with HAproxy. I use HAProxy for its more common purpose - Load balancing HTTP service traffic across many web servers. But I also use it for plain old TCP load management too, simply becuse its there and available.
HAproxy can run in HTTP mode or in plain TCP mode. In TCP mode, HAPRoxy can manage the psql sessions without any protocol 'intelligence' which is exactly what I need. I do not use HAProxy to load balance psql sessions to multiple postgresql back ends by the way. It is simply used as a queuing mechanism for service load management.
An example configuration:
frontend pg_frontend
bind 192.168.0.140:5432
mode tcp
option tcplog
default_backend pg_backend
maxconn 20
backend pg_backend
# The balance algo won't matter
balance roundrobin
mode tcp
option tcplog
fullconn 15
timeout queue 120000
server pg1 192.168.0.142:5432 minconn 10 maxconn 20 check
The front end section 'pg_frontend' binds on 5432 the usual postgresql socket, on IP 192.168.0.140. We set the front end to run in tcp mode, turn on tcp logging, and tell it to use pg_backend as the default back end. I have maxconn for the front end set to 20, at which the tcp backlog of the operating system's tcp stack will queue up OR drop.
The pg_backend is where the queuing is performed by HAproxy. I define a balance algorithm even though it is not used for a single server like this, it is needed by HAproxy configuration syntax rules. The mode is again TCP, and I have tcp logging turned on.
The key settings for queuing are fullconn, minconn and maxconn. Under normal circumstances, the back end server will be given up to minconn (10) sessions concurrently. Sessions will queue up if they exceed 10. Once the concurrent sessions active and queued reaches 15 (fullconn), the HAProxy will begin increasing the number of concurrent sessions to the server, ramping up as the number of concurrent and queued sessions approach a total of 20. Once 20 concurrent sessions are active, any sessions in excess are left to the operating system's TCP backlog to manage or drop.
The last configuration directive is timeout queue 120000 - It is a safety net, allowing sessions to remain in the HAProxy backend queue for up to 2 minutes before expiring and being dropped.
The effect of fullconn, minconn and maxconn has is to allow concurrent sessions to burst, handling brief periods of higher load, while also queuing up to 5 sessions at any given time when the total number of sessions is 15 or less.
Note that fullconn, maxconn, and minconn at such low values is a little contrived, so as to simplify my explanation and example. I use larger values than this in reality
