Magento and WordPress site performance can improve with proxysql
proxysql is a small infrastructure change that can bring about a huge performance difference in large traffic Magento and WordPress websites.
What is proxysql?
Proxysql is a high performance mysql proxy. As a proxy it sits between the application and mysql. mysql requests from the application are routed to the proxy, which in turn routes the request to mysql.
Since it sits between the application and mysql, it can intercept the query and do many things – like routing select queries to a read-only replica, or load balance between a set of write replicas (such as a galeria cluster). It also collects a lot of interesting statistics that can help optimize the application.
Unexpected performance gain from an issue with mysql threading model
But, the a huge gain is achieved by increasing performance of mysql in a high traffic environment. This is achieved due to a drawback in the mysql threading model.
Mysql follows a “one thread per connection” threading model. Moreover, a single mysql thread corresponds to a operating system thread. A high traffic php application does not share mysql connections and as a result can have a large number of threads.
Some of the drawbacks of this model :
- Mysql consumes memory per connection used and once consumed, this memory is never given back. As a result, mysql performance can deteriorate over time.
- A CPUs performance is very heavily dependent on hardware level caches. These are dependent on the “principle of locality“. With too many threads executing simultaneously, the cache hit ratios go down, reducing CPU performance.
- With too many threads executing in parallel, context switching overhead is high. Context switch is when the operating system decides to switch execution from one thread to another. A single CPU core can only execute on thread at a time.
- Too many transactions executing in parallel increases resource contention in mysql. In InnoDB, this increases the time spent holding central mutexes. This results in queries running slow for no obvious reason.
Proxysql solves this problem by not opening a new connection for each client request. It reuses connection it may already have open with mysql.
Reference : MySQL Enterprise Documentation
Effect of context switching due to high number of mysql threads
The figure alongside shows the effect of context switching.
Upto 3rd May 2024, there is high CPU usage. The green bars are for CPU used by the operating system in context switching between threads. The DB server consuming almost 100% CPU and at the same time not responding to queries.
3rd May saw the introduction of proxysql. The CPU usage is now stable, with not much used by system in context switching.
Towards the leftThe green bars are CPU spent by the system in context switching between threads. Adding proxysql immediately dropped CPU usage.
The image alongside is the number of mysql threads for the same period. The correlation between the system CPU and the number of threads makes it obvious cause and effect.
Upto 3rd May, mysql has a surge in threads – caused by a spiral of slow insert or select queries adding more threads and slowing down the db. 3rd May saw the introduction of proxysql – and mysql now uses a small number of threads.
We will explore Mariadb Thread Pool shortly to see how it compares with the proxysql approach.
