 All right. Welcome, everybody, to your last talk of the day. My name is Vipul Subaya. I work for HP. This is George Lorch. He's a developer at Precona. And we're going to talk about some of the features that we've asked Precona to build, as well as some of the features that they've built themselves, in order to support Precona's server with Introv. So a couple of years ago, I'll just start with the HP angle of this. A couple of years ago, HP decided that we wanted to run databases of service in a public cloud sort of environment. At the time, Trove supported MySQL. And HP made the decision that we would support the Precona implementation of MySQL as the primary offering. Quickly, we realized that running MySQL as a service and giving customers access to MySQL in a cloud environment was pretty difficult. There was many ways that customers could break the system that would break the ability for Trove to manage that system. So we partnered with Precona. We started talking about some of the features that we could build that are not necessarily in MySQL, but they could be in Precona that allows us to sort of harden the database as a service offering that we would provide. So we're going to talk about those features. We're going to talk about how they intersect with Trove and how you guys can also use these same features because they're actually available in upstream Precona in your own Trove installations. OK, so we'll start off a quick overview of some of the different features that we've got that we've implemented and negotiated with the HP over the time. And as we discussed earlier, they're not actually necessarily using every aspect of every feature that's in there, but it's there. And in some cases, you should be able to make use of them yourself if you choose within Trove. So some of the first issues they ran into were securing. Well, I should back up a bit. Trove uses this concept of a controlling client that needs to get access to the database to be able to manipulate things, say like setting up replication and other administrative tasks like that, maybe resetting the group user password, things of that nature. And it does this through the Trove client, or I'm sorry, the Trove agent in the instance. Well, that agent needs access to MySQL via a proper user. So there was an issue with the end users potentially deleting this user and other security things. So we have a feature that handles some of that. We've got some different ongoing research regarding trying to keep the MySQL instance from really falling apart once it hits no space situations. And there's numerous, numerous ways that you can hit no space, either from just running out of space by adding too much data or having a poorly designed app that uses very large and long transactions. So there's a couple of different ways that space management comes into play within these environments as well. Log files, binary logs, things like that. We've got several integration points and some new things right now on the horizon that occur between Percona Server and Percona Extra Backup that will allow your backups to execute either more efficiently or with less locking on the server. So we'll talk a little bit about that. And we've got features now in Extra Backup that will allow to use Extra Backup itself to help you secure your backup stream and compress it as you're backing it up. There's encryption, compression, parallelization, and we've just released an alpha version of our streaming to Swift that Extra Backup can do now natively rather than needing secondary scripting to handle that for you. And we're just going to kind of discuss how all of these things intersect with Trove and how you can either alter your Trove deployment to make use of these and how HP has actually come to use it within their own stack. So the way this is going to work a little bit is I'm going to talk about some of the Trove integration aspects, and then we're going to defer to the MySQL expert and talk about how they actually implemented the feature. So the first one here is the utility user. So again, as George mentioned, Trove runs a guest agent alongside the database process. And Trove also supports certain APIs that require the guest agent to talk to the local database. What Trove typically does on first boot is it creates a OS Admin user. This user is basically a user that's reserved for the guest agent. And the OS Admin user is able to talk to the database going forward using that user. The problem with this is that somebody outside of the Trove API, if you log into the MySQL instance, they could actually drop the OS Admin user. What that means is our ability to sort of manage that MySQL instance is forever broken. So the one thing that we wanted Prokona to support was the concept of a utility user, a user that doesn't actually exist in the user's table. It's a hidden user. And we also wanted them to support us specifying sort of the privileges and the permissions that this user has so that we don't necessarily have a backdoor into the customer data. So we could limit things like, hey, I allowed the user to create schemas, create users, other users, but don't allow them access to read from customer database schemas, things like that. So George is going to talk about how they actually implemented that feature. This was actually really interesting. This was one of the first tasks that I had when I came to Prokona. And it was quite an interesting thing to try to do because internally, the MySQL user system is actually fairly simple and also fairly complex at the same time. So wedging this guy in there was a bit of a challenge. And as Vipul says, the utility user, one of the requirements that came out was we didn't want the people that were actually using the hosted database to get the feeling that they were being spied on or have any kind of a hint of impropriety. So there were several different mandates really that came along with this user. Mainly is if somebody had root access, they could not see this user anywhere. It wouldn't show up in the user's table. It wouldn't show up in any kind of grants. It just wouldn't show up anywhere. It's got to be completely invisible to the end user. Other issues with it were how to give it the right rights so that it could tiptoe around the system to do its job without giving it too many rights that would compromise the function, the purpose of the user, and allow perhaps a rogue employee or somebody access to data where they shouldn't have it. So it's pretty easy to specify. It's documented well. I don't really think we need to get into details here, but there's basically allowed one user per system, utility user per system. You can give it a password, and you can specify the typical MySQL user syntax of if you want it only from a certain host, or if you want local host, or if you want wild carded, and so on. And this user, as Vipul mentioned as well, can have privileges to certain databases on the system. So if you want this user to be basically nothing but a password reset user type thing, then you would only need access to the MySQL user's table. If you wanted to be able to do more, then you can give it more scope of access to different databases on the system. Yeah, the user, as I mentioned, he can't be modified by anybody. Nobody can see him. Nobody can delete him. It's there. It's there for good forever. It won't appear in any of the user, client, or thread stats. The only thing that might show up would be if this user starts executing interesting queries in the system, it will show up in the slow log, the general query log, and things like that. That was actually kind of an accident and intentional, because it was an accident because we didn't even think about that when we were developing it. It's showing up in the logs. And somebody wrote up a bug that, hey, if I log in as this user and go and start executing big inserts, it's showing up and it starts appearing in the logs. And then we're thinking about it, well, you know what? That actually makes sense, because if this guy's out there deleting your data, reading your data, doing something to your data, you want to know about it. So it will show up in the logs then, and it will raise a red flag for the customer. Excuse me. This user may modify system variables based on other normal constraints on things that you'll see later on. So if you needed to do some maintenance to the server or change something, if you needed to turn into DBA mode, you can log in with this user and actually modify some system state. And it can see, create, modify other users within the system. And it can control basically all of the higher level functions of the server. It can bootstrap replication, configure replica slaves, things like that. They're obvious constraints. It must not be the same, or it must not qualify the same as any other user on the system. And if a client try, this last bullet point is really one of the only places where an end user might get a hint that this user exists is if they try to create a user that matches this user's spec, it'll kick back an error form. But they still won't see the user name obviate, but they'll know what it is. So if you do use the feature, use a bizarre user name, use a UUID or something like that. There's some interesting behaviors with it. So with this user, you can grant it either a specific set of MySQL grant rights, or you can give them access to specific schemas or a combination of both. So if you only, say, give him replication control, but yet you still give him access to another complete database, he'll have full access rights to that database, regardless of what the replication control states. So there's some tiptoeing and decision making that you have to think about before you use it. And that's pretty much it for the utility user. So there's actually a lot of weird detail that we could get into with it, but in the sense of time, it's probably unnecessary here unless anything. So we'll pause for questions if you guys have. And if not, then we'll just move on to the next thing. Yeah, any questions on the user? OK. Come on, Doug. I know you've got something. He's reserving. Wake up. If he had access to it, and you had your slow query log or general query log on, yeah. If they do, write it up. It's a bug. All right. So the next category here is options modifier. So Trove currently supports an API basically that allows the user to set configuration options on their data store. We call that config groups. And within Trove, we have the ability to sort of restrict and control values that certain configuration parameters can have. But MySQL obviously supports dynamic session level variables, so a user could theoretically skip the Trove API and try to set a variable that bypasses the ability for Trove to validate. So if the user were to do that directly, we wanted to also be able to limit sort of what options and what values a user could specify. And whether they could even, there are certain variables that we could completely hide from the user. So that's kind of what this feature ended up being, the ability to sort of control what options, whether it's dynamic or whether it's a my.conf variable that MySQL would honor. Yeah, upstream MySQL actually has the concept of options modifiers. And when I got to this task, it was actually kind of puzzled because it just seemed like half baked. Like they had some things you could do. But well, for example, like what I mentioned today, you could set a maximum on a value, but you couldn't set a minimum. I mean, why would you even go through the effort of setting, writing the code for one when you could write almost the same, use the same identical code for the second? So yeah, the idea is that we want to keep the end users from shooting themselves in the foot. So that way, as a hosting provider, you're not getting DBA calls. Oh, gee, I changed this value. And now my database is not working. So this allows you to set up some reasonable limits for whatever options you think you may need to just for that sized instance that the user has purchased. Yeah, this slide kind of explains how upstream options modifiers work. It's documented as well, but I had it here for reference. Currently, the one that MySQL supports these five options, enable, disable, loose, which are usually not used on a actually running, actively running instance they're used for various bootstrapping functions. They support the maximum option. That was one they have. So you can cap a value. So if you set the maximum of something to say 100, and the user tries to set it to 105, they'll get a warning, and it just gets set to 100. They also have the skip option, which allows you to tell MySQL to basically ignore the processing or function of this value if it sees it anywhere in a comp file. So this is what upstream has. And it's not really what is really needed to fully box in MySQL instance. So we added in the minimum, which behaves just like maximum. You can set some minimum threshold of a rational value. And if a user tries to set it lower than that, the warning spits out, and it gets bounced to the minimum value. But the ones that were really particularly interesting were the hidden and read-only options. Since in MySQL, basically, everything's system variables. You can see everything about or learn a lot about the machine that it's running on just by looking through the existing system variables. There's paths in there like datadurs and all that kind of stuff. Figured maybe somebody wants to hide them or wants to make them so the end user can't change them. So with these two options, you can basically either make something hidden or fully read-only to the end user. They will not show up in any command line means of taking a peek at the values. And they're pretty simple to use. So with this, now we have like a nice round package of options modifiers that allow you to just really kind of harden down an instance, just let the user change the few things that make sense to their workload, and then the rest of the stuff is hands-off. So they can't go and blow some value that maybe goes out and allocates more memory than the box has, and just shoots themselves in the foot. And it also allows you to protect yourself a little bit. You can obscure or hide various bits about the install so they can't see what's and get a hint as to what's underneath. Any questions on the modifiers? All right, so enforcing a storage engine. This is another feature that we had pre-configured. We wanted a way to sort of restrict the storage engine that a user could create a schema under. Given that NODB is a much more reliable and it's got the ability to recover from crashes a lot better than my ISM, we wanted to sort of make our service that have been more hands-off and force the user to actually have NODB as the only storage engine that they get. So one of the issues with running a public cloud database service is that we have many, many instances, right? Lots of customers using the service. And we have limited resources, so we can't really, we can't be a DBA for all of these customer instances. So our choice of restricting the engine allows us to sort of get out of the business of supporting individual databases if they crash, and we don't have to go and manually recover things. NODB is going to do a much better job. It's also important when we talk about backups, because we can do backups without doing a flush table. And we'll talk about that a little bit more later. But the main idea is that there is an option in Percona Server that allows you to sort of restrict what storage engines are available and visible to the user. Yeah. Not yet. Yeah. Yeah, this one was, I mean, it's kind of self-explanatory. There are one specific area where it gets a little interesting, and yeah, you can go to the next slide, is the way this would interact with the SQL mode with the engine substitution flag. So all it really comes down to is whether or not the user is going to get an error or if we're just going to switch the engine on them during the create statement. So because SQL mode is obviously something that they can set as a system variable or as a SQL's a session variable too, right? Peter's asleep, yeah. So you can, so that way it is really not any way to kind of force them to accept the behavior one way or another. They're just going to have to know that this is what's going on. So if they've got an engine forced to be INODB and they try to do my ISAM and they get an error, they'll be like, oh, what's going on? So again, that's a pretty straightforward one, but it's actually pretty valuable specifically because of the recovery. Somebody goes and shuts down their instance with my ISAM, with a bunch of tables open, middle of a huge insert, and then fires it back up and wonders where their data went. They don't want to get the phone call. So any questions on that one? OK, the next feature that we added to a Percona was around preventing certain operations, preventing the user from running certain SQL commands. So Trove treats every instance essentially like a black box. The end user doesn't necessarily have the ability to SSH onto the instance. The only interface to the instance is the MySQL protocol. And again, a Trove instance is not a file system. We don't want the user to write arbitrary data within a Trove instance. There's also other configuration files that are on that instance. Because we have the guest agent running on the instance, there's configuration for the guest agent. There could be credentials to RabbitMQ and other things that we just don't want people to read. So explicitly disabling load data in file and select into out file basically allows us to properly secure the database. And it prevents the user from reading and writing to that file system. So these are options that we actually have in our public cloud offering. Yeah, this was another pretty self-explanatory. It's simple, but its impact is fairly huge. Because without the ability to restrict this in place, an end user could theoretically probe the entire file system by trying load data in file and doing whatever they want. They could also, again, shoot themselves in the foot by constantly dumping into out file stuff all over the place that the Trove agent's got no idea what's there. It doesn't know to clean it up. So they could be completely out of disk space and not know where, can't where it went. Yeah, Amrith? Potentially. But the thing is, is the app would still have access to the data directory, so they could still dump stuff into the data directory and just consume space. But yeah, as far as getting outside of the existing or the allowed access data area, yeah, you're right. You could use anything else. But then that's also one more tool or thing you need to make sure you have in your image. I wouldn't, I don't know. Yeah, I'm not an image maker. If you have Ubuntu as the host, he automatically gets an app armor, so yeah. Yeah, they'll get an error back. I forget the exact wording and which error number it was. I think we were able to repurpose one of the existing error numbers. I forget exactly which one it was. Huh, what was that? Yeah. Yeah. Oops. Now the need to actually do a load data in file still exists, right? I mean somebody might, you know, if they got their data somewhere, they want to shove it up there or if they've got to dump, they want to try to restore. So load data local in file actually still works. But that is, if you're not familiar with it, you can connect from MySQL Client from any other machine and then upload a file from your local machine and it will populate. So you do have to go through the, you know, across the network through the protocol and all that for that. But it's, so you still have that ability, you just can't do it remotely. OK, I think we already had some questions about this. All right, so again, just iterating on the getting out of the business of supporting individual customer instances. Disk space usage is definitely one of the bigger issues that we hit with our customers. Typically, users create a small instance. They forget to, you know, resize their volume or they forget to resize their instance and we quickly run out of disk space. So this is especially important now that Trove supports replication that we have the ability to sort of limit the size of the bin log as well as the number of bin log files that can exist on a VM. It just becomes one less thing that could fill up your system. So Trove is also able to take, you know, the information when provisioning an instance such as the flavor or the volume size and properly set these values when the MySQL starts up. Yeah, upstream already has the maximum bin log size and it's your typical, you know, I want maximum n number of files and I want each one to be maximum this size. So with that, you pretty much have now a known constant of how much disk space your bin logs are going to take. And when it gets to replication, you know, you have to think about this as to how much replication lag you might expect because if you get too deep then now you're rolling past your ability to stay caught up. But what MySQL didn't have was anything for slow log. If somebody went in to their CLI and started enabling slow logging or anything like that or if Trove once gets to the point of being able to create, capture and collect logs. Oh, let me turn this on. I've got some trouble on this machine. You forget about it. Four days later, your disk is full because it's just got now gigabytes of log files. So we basically took the same concept and implemented it for slow logs so now slow logs will rotate in pretty much the same way. You can specify number of files, number of size, yes, Doug. Yeah, the same issue does exist with general log but for, in most cases, people don't use general logging. I mean, you're gonna use, if you're looking for errant queries and not you use in slow log. But that's actually an interesting question and I remember it being asked at the before. Yeah, yeah. I think that's another feature actually that's not even on this slide. Yeah, that's not on this list. We talked about that earlier. The logging has been with Trove and the work that we've done with HP has been an issue for a while but as a forward-looking issue because the reality is right now, I mean, they've got no way of even seeing them or getting them off the box really. So, but now that we're getting that point, it's probably something worth discussing out in the summit. Yeah, I think tomorrow we're gonna talk about how to expose some of these logs to Swift or something and I think this is gonna help sort of control the size of some of those logs. Yeah, and as mentioned, the main idea was just to keep somebody from turning something on, forgetting about it and then wondering why they ran out of disk space three days later, because it happens. So, questions on that? Yeah, seriously? Don't worry about it, we're all friends here. It's a pattern. All right, so this is another item. This one isn't quite solved yet. It's another feature sort of to limit how much disk MySQL uses. So, each time whenever there's a transaction that occurs, MySQL is actually storing an undue log for that transaction. There's currently no way to sort of limit how big that undue log is gonna get. So, we're gonna still actually figuring out how to implement this correctly, but essentially it would be a way that this doesn't fill up as well as allows NODB to sort of recover automatically. Yeah, this is a tough one because it crosses horizontally and vertically through NODB in a pretty tough way. It's, the issue really is that, as the pool mentioned, that if you could say do a start transaction and then just start inserting a ton of data, or if load data infile was allowed and you had a huge table, you end up with this monstrous transaction and if you don't commit it, you're gonna have a couple of things. First, you're gonna start creating purge lag because the purging thread is never gonna be able to push its way through the undue space and get finished, which is a totally different topic, but the main thing is it's taking up all this space in MySQL 5.5 in your primary system table space and in 5.6 you're allowed to put it into a secondary table space, but it's still space on disk. This stuff is just taking up space and there's no way to prevent the user or the app writer from doing something that would cause a runaway undue space growth. And this is actually an age old kind of a common problem with MySQL. Yes, Amrith? Yeah, that's- Not for an object, not for an instance. No, MySQL's undue log is for any change. Yeah, but there's nothing there's not before. Well, yeah, you're right, you're right, you're right. Actually, that is the type. I know that I was only on- No, well there is a, yeah, that's what- You're not feeling a lot of space? Yeah, it's the, well there's insert, delete, and it's been a while since I've been in this code. So, you know, we can actually talk about it offline, but yeah, there is growth, there is growth. So anyway, the thing is, yeah, it can run away, run away from you and there's just no way to set a high watermark or something to say, hey, you know what, this transaction has just gotten out of hand. So let's chop it at the knees. And so that leads, there's actually a bug, surprise, in deep down in INODB, where you can get into a situation where if you hit no space on a transaction, the transaction fails and needs to roll back. During the process of rollback, assuming that you had other operations going on, it may need to do a deep page split. And in order to do a page split to reapply the undo log or undo, it needs to go get more space. So you end up in this basically this deathly embrace where your server is now irrecoverable until you give it more disk space. It's a hard bug to hit, but we have people, have had people hit it. It particularly actually shows up if you're using any kind of disk space quotaing on XFS where it was reported against. So we're doing research, we've got some ongoing tasks where we're just trying to figure out what's a rational way to kind of keep this tamp down. So again, users don't shoot themselves in the foot. They don't go and chew up a tremendous amount of this undo space that is now not usable for anything else, because I know DB, once a page is marked for undo, that's it, it's undo forever. So it can create a pretty nasty situation. Some of the things that you can do to kind of help get around this bug or prevent it from happening is you'll drop a dummy file out on the file system, just enough for a couple of hundred pages or whatever, just some small fraction of a percent of your disk space. So if they do hit this bug, they do run out of space, server goes down, is unrecoverable, you delete this file, bring it back up, let it apply everything, roll through the recovery process, and at least get your server back into a running state so that way you now have an opportunity to move your data around or do something with it. So that's a one possible workaround, so. But beyond that, yeah, these are links, oh, they don't really show up that well, do they? There's links to different bugs and blog posts on this particular issue with the bug, and if you're really interested in it, in the morbid details. Any questions on this? All right, so another area that we've worked with Precona is around backups. So Trove supports a backup API that allows you to take full backups as well as incremental backups, and for the MySQL guest implementations we leverage Precona extra backup. So as we're taking the backup, we're also encrypting the backup and directly streaming that backup to Swift. The encryption, the streaming, it's all handled currently by the guest agent, and we're using XP Stream to sort of get a hold of the stream, pipe it to open SSL, and pipe it back to a Swift handler. So the code is pretty complex. We probably want to get rid of it at some point from the guest agent and make this a general feature within extra backup. So one of the feature requests we have is to implement the streaming and encryption capabilities natively within extra backup as well as to sort of store all the backups that have occurred in the actual customer database instance. So I think that's... Yeah, okay, I can... Yeah, there's a couple of... As mentioned, there's integration points between Precona server and extra backup that don't exist with upstream because, well, they haven't absorbed the code. So there's ways to make backups. One particular feature is the incremental change page in the bitmap setting. What it does is the server starts maintaining just a bitmap of what pages have changed since some previous marked point in time. When you go to do a backup, an incremental backup, it says, okay, hey, I know that this particular LSN is where I'm going to start at, and it will then just pick up the pages that have changed or been marked as changed rather than having to do a complete scan of the pages on disk to say, hey, have you changed yet? Oh, no, all right, have you changed yet? No, so this was released, I think, about a year, year and a half ago. It was last, not this past year's Precona Live, but the one before it. And Lawrence, who did most of the work on it, did actually submit interesting benchmarks and discovered that the server appears to suffer absolutely no lag or delay or decrease in performance because of the tracking. But the backup, if you actually have a reasonably, from either a very light to a very heavy workload, it performs almost perfectly linear based on the number of pages changed. So no longer do you have this long, long page scan that needs to take place. So if you only have a couple of pages changed, the backup executes really like that. If you've got a complete set of change pages, it performs almost exactly the same as if the feature didn't exist. So it was really interesting because we were expecting to take a ding somewhere on it, but that feature's in Precona server and it's pretty easy to turn on. An extra backup will detect it automatically when it fires up. The other, a more recent feature that is actually really cool, this is the killer one here is the lightweight locks. When extra backup executes a backup, if it's, for I know DB, it's basically just copying pages off disk and tracking the log positions, copying it out so it can reapply the changes after the backup, well, when the backup's ready to be prepared. But when it gets to the point where it needs to finalize what LSN, what positions it's actually backed up to, as well as backing up my ISAM data, it needs to execute a flush table with read lock, which basically just puts a halt to all changes for the duration that it's being executed. So this lightweight lock does is, first off, it eliminates the need to do the flush tables. So tables are no longer flushed from the I know DB in memory cache. And it only needs to take a metadata lock then on I know DB as well as, though it still needs the full lock on all non-transactional stores, which my ISAM is. And for the purposes of Trove, it's, well, it depends on, like in HP's case, they don't have my ISAM except for the system table, so that's not a big killer there. But if you are allowing my ISAM and you've got big tables, your backups are going to be painful. So that's a really neat feature and worth using the combination for just for that. Now, coming soon... Yeah, it lost my place. Yeah, there you go. Encryption and... Yeah, coming soon. Extra Backup currently has a couple of things built into it to help your backups execute faster. First off is it's kind of an old feature, but it's the ability to execute backup in parallel. It can copy INODB table spaces in parallel with a dash-dash-parallel option. So if you've got file per table or many multiple databases, it can actually copy from multiples and stream them at the same time. After reading and writing, reading from the INODB and pumping it through the output chain, it can also inline execute lightweight compression and symmetric encryption, each of which can be put into multiple threads. So if you've got the CPU power to do it, you can be doing a compression and encryption in parallel rather than just in a single serial stream. And that is for each parallel backup pump, as we kind of call it. Coming soon is asymmetric encryption. We've actually had the feature kind of ready for a while, but we had some issues with a bug in the library that we use for it on certain distros, so it's been kind of on hold for that. And just as mentioned earlier, just released into Alpha is now we have our own baked version written in C of... I think they're calling it XB Cloud is the binary. And what it will do is it will take an incoming XB stream off of extra backup, break it apart, and in parallel push it out to Swift Object Store. It maintains basically a manifest of where it put all the different blocks, so it knows how to reconstruct them, and then you can use it as well then to go pull and recreate or back the dataset. Okay, cool. Any questions on any of that stuff? We're kind of out of time. Great on time. So tomorrow, during the design summit, either tomorrow or Friday or whatever, we'll have some time with the group. Some of this stuff, particularly with the backup, I'd like to bring up to see if we can improve the user experience with the backup space with some of these new features. A lot of these features are not fully baked into Trove, so HP runs a lot of these features, but they're not natively in Trove, so tomorrow we should probably talk about getting some of these features added to Trove, so that's available for everybody. We have a lot of really good guidelines on how to set baseline values with backup if you're going to get into various threading options. There's the balance and trade-off of, do I want my backup executed fast and I don't care what it does to the server, or I want my backup, I don't care how long it takes, but don't impact the server's ability to do its job. So there's a balancing act that goes on there and we have a lot of options that we can use to tweak that. Cool, thank you. Thanks guys, thanks for coming. Two hours to the party. Two hours. Bus is out front at seven o'clock.