Scaling ProxySQL with --idle-threads option and epoll

Update 21 Mar 2019

There’s a new post Fixing ProxySQL Idle Threads Epoll Hang Heisenbug about fixing a bug of ProxySQL when --idle-threads is enabled.

Problem

ProxySQL does not scale with high numbers of connections by default. When there are tens of thousands of client connection to ProxySQL, the CPU usage of ProxySQL may go up to 300%+ because by default it uses 4 threads. Even with 3000 mostly idle connections, it hits ~70% CPU usage.

Investigation

top shows that the system CPU usage is quite high (e.g. 20% for a 8-core machine, i.e. 160%). strace shows that poll and nanosleep are the most frequent system calls. It does not seem usual to get that high CPU just because the number of connection gets to tens of thousands. HAProxy, nginx, and even some simple Python web servers could do much better. It comes to my mind that it has to be about poll and epoll.

Digging deeper

This issue on GitHub shows that v1.3.0 has epoll already. This presentation and many other presentations show that the scalability issue has been resolved through epoll and “Auxiliary Threads”.

Solution

It turns out that the epoll feature is not turned on by default. ProxySQL is not known for its documentation. The feature is only mentioned in 1 line in this wiki, which is also the only result in a Google search. Another problem is that it is a command line option, and cannot be controlled through the configuration file.

Here’s how to enable it in Ubuntu:

sudo sed -i -e 's/OPTS="-c \/etc\/proxysql.cnf -D $DATADIR"/OPTS="-c \/etc\/proxysql.cnf -D $DATADIR --idle-threads"/' /etc/init.d/proxysql
sudo systemctl daemon-reload
sudo service proxysql restart

This requires a reload of service file and a restart of proxysql obviously.

Conclusion

ProxySQL is a solid product for performance, availability and monitoring. This “Auxiliary Threads” feature is extremely underrated because it is the key to scale ProxySQL server. There should be more documentation on this. Easier configuration would also help.