The art of multiplexing MySQL connections with ProxySQL
This is a modal window.
The media could not be loaded, either because the server or network failed or because the format is not supported.
Formal Metadata
| Title |
| |
| Title of Series | ||
| Number of Parts | 561 | |
| Author | ||
| License | CC Attribution 2.0 Belgium: You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor. | |
| Identifiers | 10.5446/44580 (DOI) | |
| Publisher | ||
| Release Date | ||
| Language |
Content Metadata
| Subject Area | ||
| Genre | ||
| Abstract |
|
00:00
TDMAService (economics)Group actionThread (computing)GradientEndliche ModelltheorieScalabilitySoftwareComputer hardwareContext awarenessBefehlsprozessorCache (computing)Read-only memoryImplementationComputer networkAerodynamicsEnterprise architectureClient (computing)Computer configurationSystem callNormal (geometry)Proxy serverLevel (video gaming)Front and back endsServer (computing)Database transactionTable (information)Variable (mathematics)Presentation of a groupGraph (mathematics)Default (computer science)BefehlsprozessorConnected spaceServer (computing)Context awarenessRevision controlSemiconductor memoryProxy serverLatent heat10 (number)Multiplication signAuthorizationCache (computing)Thread (computing)NumberWechselseitiger AusschlussContent (media)Group actionConsistencyInjektivitätEndliche ModelltheorieCartesian coordinate systemClient (computing)Windows RegistrySoftwarePhysicalismProcess (computing)Different (Kate Ryan album)CASE <Informatik>InformationImplementationInternational Date LineReplication (computing)Set (mathematics)Database transactionTDMASequelDatabaseWater vaporQuery languageQuicksortSequenceWavePhysical systemPerformance appraisalEnterprise architectureBoiling pointComputer animation
07:10
Client (computing)Computer configurationFront and back endsDatabase transactionTable (information)Reading (process)Communications protocolSet (mathematics)Variable (mathematics)Rule of inferenceMoving averageException handlingTDMASample (statistics)NumberDifferent (Kate Ryan album)Variable (mathematics)Run time (program lifecycle phase)Proxy serverMobile appServer (computing)Connected spaceGraph (mathematics)Thread (computing)Query languageFront and back endsDefault (computer science)Queue (abstract data type)Maxima and minimaKey (cryptography)Revision controlSoftware testingStructural loadTDMARule of inferenceSet (mathematics)Table (information)Reading (process)Latent heatMultiplicationCommunications protocolClient (computing)RoutingGoodness of fitDatabase transactionStatement (computer science)Cartesian coordinate systemResponse time (technology)Device driverMessage passingFlash memoryProduct (business)BenchmarkSequelGraph (mathematics)Multiplication signPhysical systemOnline helpLogic gateWordRight angleComputer data loggingComputer animation
14:15
Client (computing)FreewareFront and back endsMoving averageComputer configurationException handlingClient (computing)Proxy serverServer (computing)Thread (computing)Connected spaceStatement (computer science)Configuration spaceQuery languageMultiplication signAverageNumberRoutingGraph (mathematics)Total S.A.BitType theoryDifferent (Kate Ryan album)Front and back endsVariable (mathematics)DatabaseWorkloadDrop (liquid)Run time (program lifecycle phase)Rule of inferenceSlide ruleBenchmarkOrder (biology)EmailSequelElement (mathematics)Selectivity (electronic)Internet forumCodeSocial classCommunications protocolTheory
21:19
Euler anglesDiagramComputer animation
Transcript: English(auto-generated)
00:06
Welcome, everybody. This presentation is about proxy SQL and mirroring. So what we do, we do basically the support, but we'll just skip here. Who am I? I'm Reneko Ngo. I'm the author of proxy SQL, founder of the same company.
00:20
My background is on my SQL DBA for quite a long time. So quick overview of what is proxy SQL. How many of you are familiar with it or know it? Great. How many of you are using it? OK, great. OK, so basically, in a nutshell, what it is is a proxy that is protocol aware
00:41
that sits between the application and the back end, the SQL server. It gets requests from the application. It processes them, decides what to do with them, and then performs action based on some internal logic. It is all programmable. So there is a bunch of features that I'm not going to go on this presentation,
01:01
but the most important features that we are going to discuss is about multiplexing. And the reason behind this is to try to solve one specific problem that is quite common in MySQL. In MySQL server, when you have a lot of connection, a lot of client connection, performance tends to degrade very badly.
01:22
The higher the number of connection, like 1,000 connection or 10,000 for connection, performance drops drastically. Here, we have graphs about one specific use case in which replication was lagging. And the only reason why replication was lagging was because there were too many connections
01:42
to that server. Those connections were absolutely idle. They were not processing any requests. The only thread that was running and doing some work on this server was replication, and replication was just lagging. As soon as they killed all the idle connection, replication was able to catch up.
02:00
And they identified this one because they had two different server with same specs, one with connection, connected to the server, and the one without connection was not lagging. So the only reason why one of the two slave was lagging was because it has tens of thousands of connection. So quick overview of what are the various threading models
02:23
that this can apply on any sort of software. So you can have either one thread per connection, or in some other system, you can have even one process per connection, like in Postgres. Or you have some thread pool. Pros and cons of both of them. Well, the pros of one thread per connection
02:41
is that it's easy to develop because every thread is just handling one connection. On the other hand, one of the problems is blocking IO. So that specific thread has to handle all the specific connection and nothing else. On the other hand, if you are using thread pool, it is way more difficult to implement
03:02
because you have one single thread that has to manage multiple connection, but at the same time, it's way more scalable because you don't have so many threads. So MySQL, by default, it has one thread per connection. And of course, this has some cost.
03:21
The cost, again, is not specific to MySQL, but any application that uses this model of having one thread per connection. Basically, in short, you have a lot of software thread, while the number of physical threads that are in the CPU are way less. So you have a lot of context switch because the CPU, every time, has a switch from one
03:41
software thread to another one. The number of registry has to do all the context switching. And you have mutex and contention between all those threads. And at the same time, the CPU cache, most of the time, is absolutely useless because all the context has been moved in and out from the CPU cache. And for the same reason, you have to access memory.
04:02
So access to memory is way more expensive than accessing the cache in the CPU. So what about implementation of pool, of thread pool? So normally, what you have is one thread that is accepting of the connection. And then this thread's normally passing the connection to a set of threads pool that is the one processing
04:23
the request. In MySQL, this is not the default. But if you're using MySQL Enterprise, you have a thread pool. MyODB and then Percona implemented their own version of thread pool. The way it works is that all the new connections are being
04:43
accepted by one listener. And then this listener is passing the connection to a thread pool. And that specific thread pool is the one that is going to handle the connection until the connection disconnects. And of course, because of the use of the thread pool, performance gets a lot better if you're using this.
05:01
Great. What about in proxy SQL? Implementation of thread pool in proxy SQL is slightly different. Instead of having one listener, what we have is that all the threads in proxy SQL, they are all listening on the same port. So one of the threads is the one that's going to get the connection. And it will be handling the connection until the client disconnects.
05:24
So it has some pros that basically there is no context switching between the threads because all the information always stays located to the specific thread. Of course, there are also some cons because it can be happened that the threads are unbalanced. But this is a quite rare case.
05:42
OK. So we have proxy SQL that has its own thread pool. Does this solve the problem of my SQL having a lot of connection? In reality, this by itself does not solve the problem. The reason why it doesn't solve the problem is that, of course, having proxy SQL, his own thread pool, it improves the performance of the proxy.
06:00
But it does not solve the problem that you might have still a lot of connection to the big server. The reason being is that a lot of proxies, if they are layer 4 proxy but also layer 7 proxy, what they normally do is that for every client connection, they create one backend connection. So there is a one-to-one mapping.
06:21
And of course, this, of course, does not scale. Because if you have tens of thousands of connections on the proxy SQL, you will have tens of thousands of connections to the database server. So proxy SQL has his own different implementation. What it does is that it has its own connection pool. And when a connection is being used after it executes a query,
06:43
normally this connection goes back immediately to the connection pool, assuming that the connection is safe to be shared. When a connection is safe to be shared, it means that there is no transaction running in the connection, there are no temporary table, there are no user-defined variable, et cetera, et cetera. So there are a certain number of criteria that define whatever the connection is safe
07:02
to be shared or not. And if it is safe, just go back to the connection pool so the next client request can be executed in that specific client connection. And well, so basically what we can have is that we can have thousands of front-end connection
07:21
that go finally into very few back-end connection. So basically this is how proxy SQL performs multiplexing. So again, you can define the number of back-end connection and the number of front-end connection can be extremely, extremely higher.
07:41
Of course, there are certain criteria that automatically disable multiplexing. They are listed here. For example, if there is an active transaction, the connection cannot be shared until the transaction is completed. Or if you, for example, if there is some table lock. So if you have executed a lock table or flash table with read lock and that specific connection,
08:01
that connection cannot be shared again until unlock table is executed. Same thing happen with get lock. If you execute get lock, that connection cannot be shared anymore. Surprised there are still a lot of application that use it. Other criteria for disabling being lock
08:21
is that if you execute set SQL log, SQL log being equal to zero. After you execute this, of course, that connection cannot be shared anymore because otherwise this means that other connection which have binary log disabled. And of course, this is not something that you would desire. And multiplexing is automatically enabled when you issue a set SQL log being equal one.
08:43
So as soon as you enable it, the multiplexing is automatically enabled it. Another reason why multiplexing is disabled is when you execute create temporary table or if you use SQL, calc, found rows or prepare. Prepare is the text protocol version of the prepare statement.
09:03
Other reason, there are a lot of session variables that when they are enabled, proxy SQL automatically disable multiplexing. Here I listed some of them like set unique check or set out increment increment, foreign key check and so on. If the client execute one of those,
09:22
multiplexing is automatically disabled. Now by default is also disabled multiplexing for every query that has the app that is normally used for user variable or session variable and no matter if you are setting it
09:41
or if you are reading it. And this of course can be problematic if you are using somewhere RAM or some JDBC driver that is actually, it want to read all the session variables so it performs some seg statement and which specify this set. So for those specific query, you are also able to create rules that automatically re-enable multiplexing
10:04
if there are such queries. Okay, questions so far? Good. Okay. One important thing to remember is that multiplexing does not automatically disable routing. Those are two different concepts. So you can have multiplexing to be disabled
10:23
on a specific connection but still you can have declined the same traffic into multiple connections. So one connection to the writer and one connection to the reader or if you have sharding, you can have one connection to one shard and another connection to another shard and traffic is being load balanced
10:40
based on certain criteria. Now, let's show some example of how it actually proxy SQL helped with improving performance using multiplexing. What would follow is some example of performance improvement that we got at Shopify
11:01
that they are using proxy SQL on all their infrastructure. So, those are initial testing they were doing. So basically, those are benchmark. They were running the same traffic directly on MySQL and through proxy SQL.
11:21
So you can easily see the difference. I don't know if you can see the numbers but here we are close to 15,000 while here we are barely around 700. So the number of connection was 5% of the original. So they drastically reduced the number of connection. And during the same benchmark,
11:43
they noticed that basically the number of thread running, this with MySQL and this with proxy SQL. So thread running with MySQL was spiking up to 100 while when they were doing, while they were using proxy SQL, the number of thread running was spiking up to 25.
12:03
This was the maximum. Thread inside InnoDB before they had 80 while with proxy SQL, they have some few spike to 15. And more interesting is that the queue, the thread in the InnoDB queue with the straight connecting to MySQL
12:21
was again spiking to 100 while with proxy SQL was fret zero. So those are other graphs of some benchmark. Let's start from down actually. So here MySQL traffic, here traffic passing through proxy SQL,
12:41
the number of QPS is the same. Actually with proxy SQL is slightly higher. So in proxy SQL they were having more throughput. And the number of the execution time of the query before was around 25,000 while when the pass to proxy SQL was 6,000.
13:02
So the execution time of the query was less when passing through proxy SQL. Proxy SQL was not filtering any traffic, was not caching anything. It was just ordering the execution of the query before sending them to the server. So it was basically reducing the number of connection to the server.
13:22
It also, the response time was drastically reduced. Here before the response time was averaging at around 1,000. I think this would be millisecond. And when they switched to proxy SQL, it was around 0.3 or 0.4. So huge performance improvement.
13:42
Again, those are the graphs. This is actually production traffic. So before when they're connected to MySQL and when they switch to proxy SQL. So it passed from around 15,000 connection to, I don't know, probably they had 300 or something like this.
14:01
Thread running in MySQL, again, before they were having around 20, spike up to 35. When they switched to proxy SQL, the number of thread running was around eight, around eight, less than 10 for sure. And again, they got huge performance improvement in the total query execution time
14:21
that passed from an average of 10,000 up to 20,000 down to, I think, less than four, something like this. So huge performance improvement. Those are just another graphs about some spike traffic. So when they had those spike traffic with MySQL,
14:43
the number of thread running spiked to 439, huge number. And the number of QPS on MySQL was around 35,000, this spike. When they switched to proxy SQL and they had a similar spike, the number of QPS was around 70,000.
15:02
So nearly doubled than this throughput, but proxy SQL limited the number of connection to database server, so it spiked, thread running spiked to a bit higher than 100.
15:22
Okay, one of the misconcept that people normally do when configuring proxy SQL is that they configure the number of backend connection to be as high as the number of client connection. So if they expect the client to open 1,000 connection,
15:41
they configure that proxy SQL to open up to 1,000 connection to database server. In reality, this is something that you should not do if you always try to configure proxy SQL to open way less connection to database server. And what happen if all the client executes some query at the same time, and if you have 100 client
16:00
and you don't have enough backend connection? What happen is that proxy SQL will internally sort those query based on the start time, and will basically queue them internally and execute them serialized, so based on the number of connection that are open.
16:20
And as I was showing in the previous slide, these actually improve performance. And this is even more easy to understand on the next slide. So basically, this was a benchmark done in 2016, so almost three years ago, done by Percona. So this is some read-write workload using MySQL 5.7
16:44
and Percona server 5.7 with thread pool and proxy SQL limiting the number of backend connection to 200. At low number of connection, the performance of both MySQL server and Percona server were higher than proxy SQL, and that's expected
17:02
because proxy SQL has to analyze all the traffic before executing it. But then as the number of connection increased at around 300 or something like this, no, around 200, the performance with MySQL server dropped drastically.
17:21
The performance with Percona server dropped as well, but because they had thread pool enabled, didn't drop so much. When I said proxy SQL was tapping the number of connection to database server to 200, and performance keeps stable no matter how many connection were on proxy SQL.
17:42
So of course, if here we were adding more connection, again, the performance in proxy SQL was going to be quite linear, while MySQL and Percona were somehow dropping. Question? Yes, yes.
18:05
Are you to the right master, or to the second master, do you have to duplicate the state, or does it do that by now? Okay, it doesn't duplicate, so it will only execute it to the rider, to the master,
18:24
depending according to the query rules you have configured. And of course, if you're executing a select statement, the select statement has to be executed on the same connection. Proxy SQL does not guarantee this. It really depends how you configure your query rules. This is why I'm normally saying, do not send all the select to the reader,
18:42
because this is wrong, because your example is a very clear example of when you should not send all the select to the reader. And actually, what you ask is, basically, I'm highlighting this here, that multiplexing does not disable routing.
19:00
So you can still have different type of routing. So like the write statement goes to the master, and the select statement goes to the slave. And if you are sending an act to the master, you should not send the select to the slave, because it is wrong. You should send the select to the master. Yes, but this is why what I normally recommend is
19:22
that you identify which are the select that are safe to be sent to the slave. You don't send all the select to the slave. Yes, I mean, you need to understand, sorry? Repeat the question. Oh, yes. So the question is about what happen
19:41
if you have a query that has session variables, so specify an act, and it is a DML statement that goes on the writer, and then the select statement, you have configured proxy to send the select statement to the reader. If you're sending the select statement that use the same session variables to the reader, the reader won't have that session variables,
20:01
because you haven't execute DML there. So what I'm suggesting is that you don't send all the select to the reader, but you only send to the reader the select statement that are okay to be sent to the reader. So you order in proxy SQL, you check from start my SQL query adjust, which are the query that are safe to be sent to the reader
20:21
and you only send those instead of everything. Yes, yes. Oh, yes. So the question was, if here, the time, not this one, actually this one.
20:44
So the query time in those graphs, they were about establish connection, or they were counting also the time to establish the connection. Those are about establish connection. So in those graphs, you don't even see the benefit of having proxy SQL and its own connection pool,
21:02
because those were already persistent connection. Any other question? Thanks.