 So good afternoon everyone. My name is Nick Visas from Proxieskill and today I'm going to present the new features and changes in Proxieskill version 2. So a bit about us, Proxieskill LLC. We're a small company, a bit smaller than Google and we primarily do Proxieskill development, but of course we also provide support services to our customers and also some DBA consulting stuff. We're hiring, so if anyone's interested in doing either C development or you know Proxieskill support, please reach out to us. We work 100% remote and we're very flexible with the time and the type of work. There's a lot that you can do at our company, so reach out to us if you're interested. This is an old slide of some companies using Proxieskill and let's get started with a specific talk. So in the beginning I'm going to start off with some performance enhancements that have been added and then go into the features, specifically GTID, causal reads, the extended support we've introduced for Galera and Amazon Aurora. We're going to touch on the LDAP integration that has been implemented and then move over to security kind of stuff, audit logging features that we've introduced to SSL. We've built a lot of things in terms of logging to get information about what all of your connections and what all of your processes in your database are doing, so I mean we've implemented all of that with JSON, so we're going to try and go through that quickly. This is a longer presentation than we have time for today, so I'm going to try and whiz past some sections. And then finally discuss the firewall that's been introduced and SQL injection detection. So performance enhancements, starting off we've upgraded a lot of our internal dependencies. This led to a lot of code changes. We moved to the MariaDB connector version 3. We've upgraded Gemalock, which in itself is faster to version 5.2 and we've also upgraded the SQL light and the lib config. We improved the performance of the MySQL refresh variables process, let me call it. It's very core because I mean this takes care of updating all of the stats variables for every single thread that's running in every single process, so the changes here really made a big improvement. We also made, well it was actually a complete overhaul rather than several optimizations for the stat tables and that goes together with a monitor module changes. A lot of code was just completely rewritten and some bugs were found. Well there weren't actually bugs, there were performance issues in FAST when running proxy SQL in FAST forward, so those were addressed and we've also added a variable where you can pass through a list of variables that you want to keep multiplexing enabled for. That will make a major performance improvement because you won't have to create individual query rules. You can just group them all together and say, okay do not disable multiplexing for this list of variables, so that's quite useful. We also introduced some memory optimization specifically on the vacuuming of the internal proxy SQL database and the stat schemas and we also introduced this variable to normalize the digest text so you can actually just store a portion of your digest rather than the full query especially when it's a bed sheet, full double bed SQL statement and you don't want to store that in your database. And German lock which we already discussed. So in the query cache we've also finally implemented, this was widely requested, just the command so that you can be able to flush your cache and we also improved the cache TTL, now it actually purges the cache entries rather than handle it as a background process. Previously it would just mark that the cache should get emptied at some point, now it actually gets emptied in real time and we also have added an option to avoid caching empty result sets which can create a lot of garbage essentially in your cache. So GTID causal reads, I'm just going to touch on this feature, I hope everybody has, has anybody here worked with the GTID causal reads in proxy SQL or is familiar with it? Okay, this is a presentation in itself but I'll just try and give you an overview of the feature. The way it works is we'll set up proxy SQL as we normally do, we'll also have to deploy an additional component called the bin log reader. What this does is it checks the currently, the bin logs that are currently being processed on each of the instances and just sends the transaction ID, so that's the identifier for the server and the sequence number for the GTID. So in this way proxy SQL can track the replication status of every single instance and you can define query rules to ensure that all of your statements that are being executed on the slaves will be accessing a node that has data that is needed for your transaction. So I won't say up to date data but it will make sure that the data that you're trying to access is up to date on the instance. So the bin log reader itself is a really lightweight process. It just sends two numbers for every event where actually it sends the source ID just once and then it just sends the sequence ID. So it's lightweight both in terms of what it's processing and what it's sending. You don't have to worry about network throughput. It's also built with an angel process so it will stay up and ensure that the data is flowing back to the proxy SQL instances. So where can we get this? This is in a separate repository to proxy SQL itself. If you want to download it from the repo, from GitHub repo, you can build it yourself, it's just make and then your platform name. We have actually created packages and we will be releasing these soon. So keep an eye out for that. So Galera application. I hope everybody is familiar with Galera. Show of hands. So I will skip the overview of Galera application and just say it's a very nice technology. It's virtually synchronous. Proxy SQL has great support for it. So we've introduced a similar table to the one that we used for group application where we can define the concepts we need for the clustered configuration. So we can define how many writer instances we have. We can define whether our writer instance is also going to be used as a reader. We can define a threshold for how many transactions behind we can let a node be before considering it offline. And then we also have the option for the backup writer host group, which is essentially similar to the backup option in HA proxy. You can have instances just on like a warm standby in case you lose your master. So that's what the table looks like inside proxy SQL. You'll need to define four different host groups, one for your writer, one for your backup writer. This can obviously be an empty host group if you don't want to have a backup writer. And you also have to define your reader host group. The offline host group is a special host group that proxy SQL will use itself when it detects a node as offline. It will move it into the offline host group, keeping only the active nodes in the writer and reader host groups. So you can also define which instances you have active in case you haven't have an instance that you haven't launched yet or you've taken down for whatever reasons. Again, these are the settings we discussed before about configuring your max writers and whether the writer should also be used as a reader. Separate health checks have been introduced. These check the WSREP variables to determine the status of a node. You obviously can set the interval, the timeout, and the max timeout count for each of the checks. By default, we've left the amount number of checks to three. You might want to tune this according to the needs of your environment. There's also a new variable, this is specifically for Pocona X2DB cluster, PXC main mode. So if you set this variable on the PXC node, proxy SQL will pick up on the change and set this node to offline soft so that no traffic will be routed to it. Amazon Aurora. So we've added a lot of support for Amazon Aurora in version two. The way it works is it will track the replica host status table, which contains the replication information about your Aurora cluster. It supports auto discovery to a certain level. You still need to define your reader and writer host groups, but it's enough to just specify one node and proxy SQL will connect to that instance and based on the information it collects, it will build your topology. So it has native, so it has auto discovery, it has easy awareness, and you can even set replication leg granularity to milliseconds. We have a similar table to the one for Galera cluster as well, but we have, I don't know if you can see these clearly, there's some new Aurora specific variables. For instance, your Aurora port, your domain name, the new reader weight variable. These are all documented on the GitHub wiki, so if you want to have a look at what each one does, the main variables to keep in mind are max lag millisecond, which is how much milliseconds we can tolerate to keep a node active, and the check timeout and interval. The rest are similar to a regular group replication or Galera cluster configuration. We also have a logging table where we keep all of the statistics of how many checks failed, the reasons for their failures, and this is where you can check the monitoring. It basically takes all of the information it finds in Aurora and also persists it in proxy SQL. And then the last thing that I want to touch on here is we also give an option where you can directly in your select statement specify how many milliseconds of delay are okay for a query, in case you have some query that needs to be more up-to-date, and it will go and it will find an instance that has the most up-to-date data for you. Just to note, it's not the exact measured lag, but it's kind of an estimate because you can't know the exact amount of lag from the time the connection was initialized. So this is the lag that proxy SQL at least knows about, but it's highly granular and very accurate. So again, we have the option to use the writer as a reader, and we can also set this variable to only read from replicas. So LDAP, I don't want to talk about this too much because the implementation is still rudimentary, but there's going to be a lot of active development on LDAP in the next few months. The way it works is a client will connect to proxy SQL and try and authenticate. If proxy SQL doesn't find those credentials within its own list of users, it will connect to LDAP, and it will try to authenticate. It will check multiple groups. It will try to find that user. If it finds map credentials, it will allow the user to authenticate. It's available as a separate plug-in. It's not on the website. It will be released at some point, but it still needs to be brushed up a bit. You can see the variables related to LDAP. It's pretty simple to use. You just have to specify your prefixes, your suffixes, your URI, and make sure you have access, and you can start using this. So SSL supports. We've implemented not only backend, but also front-end support for SSL and proxy SQL version two. To configure it, you just have to set have SSL true and then proxy SQL will automatically generate your certificates. If you want to use your own ones, you just have to create them with these specific names in the proxy SQL data directory. You can verify your front-end connection just by checking the status, and then you can also see that in proxy SQL admin. So there's two points where you would want to verify your connections. The impact, as you can see here, there's a higher impact when there's a lower number of threads, but as the threads increase, because proxy SQL reuses backend connections, there's not a huge impact. I mean, it's almost comparable with just the backend SSL. Also, you'll see reduced latency as the connections get prepared in the connection pool, and once they're ready, you won't even see the difference. AuditLog is a new feature that was sponsored in proxy SQL 2.0. This allows you to track events both for the MySQL module and for the admin module, so you can track successful failed authentications, graceful connects, close connections, change of schemas within MySQL, and also you can see what's being changed within proxy SQL admin. Just an example of what these logs look like. It's all in JSON, so you can see the type of error or the type of message, connector K, you can see if you've got SSL, so this is a good way to be able to log all activities that are happening in proxy SQL. Additionally, we improved the general log, which was previously a strange custom format. Now we've migrated that to use JSON, so in order to enable the events log, you just have to specify the file and that will start producing events, which look kind of like this. I don't know if it's a bit small, but here you can see a select statement was executed, and it shows you the type of query, and it shows the host group ID. Here we can see host group ID minus one, so this was actually served from cache, whereas this went to host group one. I think no, host group zero. So it's a very nice way to see what's going on in proxy SQL. This is, think of it as the equivalent to the slower general log in MySQL. Very useful. It should always be on in development environment so that you can see what's going on. I did a lot of implementation here on exporting stats in JSON, specifically the process list and the free connections. That's a lot of information because you have to consider that for every connection in proxy SQL, you have information about the front-end side of the connection, the back-end side of the connection. These connections could be changing. There's a whole stack. There's a whole lot of reusing, so it's very difficult to track all this, and it also results in a lot of information. So in order to sift through this, you can use JSON extract in proxy SQL admin. This is what the information looks like for a third ID zero. Don't worry. Keep calm and love JSON. If you beautify this and you break it down and dissect it, what we can see here, it's a bit small here, we have back-ends, the client, and the connection. So you can get information about what's happening on your back-end, very useful information. For instance, if multiplexing is disabled, if auto-commit was true, if what the last auto-commit sent was, and within my SQL here, you can even see the statements that you're executing, whether it's a prepared statement. You can see exactly what's going on. But of course, it's a lot of information. It's difficult to sort through unless you know exactly what you want to get, but we wanted to keep it explicit so that we could have an easier path to diagnosing issues. So here we can see the connection element. You can see what char-sets being used. You can see binary logins enabled. It's a complete log of everything going inside, going on in your processes. And here we can see an example of what three connections looks like. This is a much more contained view of data. Most of the information you would get from your stats MySQL connection pool table, as well as some other system stuff. Here you can see affected rows, the character set, some systemic information, packet size, and some MySQL info. If you want to get this information in a clear form, it's best to use JSON extract and just get exactly what you're interested in getting. We still have some time. I'm quickly going to go through this. We've introduced a firewall. We used the whitelist approach. Previously, you would use a query rule to block users. Instead, we've allowed a whitelist where you can specify either users or schemas and you can set it up in one of three modes, either off completely, detecting so that you can track what's going through and finally protecting. So think of this as AC Linux for proxy SQL. We also keep a track of all of the traffic that goes through. So it's useful to have statistics on that as well. Finally, we've also added a library for SQL injection. It does work. It does give a lot of false positives because it's very picky. If you're interested, you can have a look at how it works. By enabling it, you'll see messages in your error log about whether there are dangerous statements being executed. And finally, just to add on this, because it was an issue with the initial releases, we have got full support for my SQL 8 authentication mechanism. So thank you very much. We have 30 seconds for questions. We can handle them afterwards.