 Great, so my name is Mark Leith. I'm a senior software development manager in Oracle I actually work in what's called the enterprise tools team in Oracle So my my responsibility is actually a tool called my SQL Enterprise Monitor, which comes with the subscription in my SQL I'm also the original developer of the Sys Schema, which is now included in 5.7 by default and because I have so much Responsibility around the monitoring tool. I also have a whole bunch of input into all of the instrumentation in the server as well So I've had a lot of input into Shaping performance schema and a lot of the the other instrumentation in the server as well So a quick Overview of what I'll talk today first why you should instrument your plugins in performance schema I think Valerie did a pretty good job of describing why a minute ago A quick look at the the interfaces that are available to actually use from performance schema itself Show a few examples of how you to take your current code in in a plug-in and push it into the instrumented interfaces And then I answer any questions before I would do that I want to get an idea of does anybody actually written a plug-in with perform a plug-in for my SQL Wow, that's more people than I expected. Some of you are actually developers on Oracle though. That's that's wrong Of any of you actually considered writing plugins for for my SQL couple So the why I People don't really realize that as my SQL has Moved on over the years. We've actually added a whole bunch of really good interfaces into the server So you hear a lot that you know, you there's not enough instrumentation I want to do firewalling and all of this kind of stuff Over the years. We've added a bunch of interfaces not just storage engines, but being able to do things like See what it's doing change it along the way block it with firewalling those kind of things We have an audit interface. We have our own audit plug-in that we have with Oracle There are other ones that are out there like the the one that Bacona has and Maria But the other thing is with that data you can do your own thing, right? I think there's there's a lot of data that you can get in the audit stream About who is doing what what kind of problems are coming up with errors those kind of things So it's a great interface That's that's probably the one that I think most people should look at or most DBAs that want to trace What's going on in their systems and I'll give an example based on the audit one today We also have full-text search Not many people use that information schema tables can be pluggable as well so you can again look at Or get a whole bunch of data from the server and expose that if you want to I've written for instance information schema tables In the past that go off and do system calls and find CPU usage and IO usage and all of that kind of stuff. It's it's not hard to write those kind of things And then replication interfaces, you know that as we've worked on things like group Replication and the like we have given interfaces into the binary log streams as well So those are things that people can I think will start to pick up and use more You know as we do integration with other big data systems You can interact interact with the binary log stream and for instance throw it over to Kafka or whatever you want to do On that side, so there's a lot of great interfaces there But as they're doing a whole bunch of work, you need to instrument them as well And if you don't do that You you saw the example from Valerie earlier Where for example rocks DB was waiting for a whole bunch of time Waiting for a mutex, right? He had to go off and use perf for that Performance schema does track mutexes and it could have shown that as well So it is essentially creating your own new black holes, right? I mean it's this is the way that I look at the the survey You know the little points of light are essentially instruments in in performance schema The the black hole is essentially, you know, you don't know what what is happening in the server That used to be a supermassive black hole before performance schema came along essentially But yeah, you should try and shrink those as much as possible So the the interfaces themselves The main interface is all in this include my SQL PSI directory It has the PSI H file, which is essentially the ABI Within that one, it's a versioned interface, but exposed as a standard interface So you use the standard interface and it calls out to the version version interfaces appropriately Shit And The first thing I'd say is go off and read this document. We've put a bunch of a bunch of work into actually Doing proper documentation in doctor doxygen format The performance schema interface is a great example of that as well. We it's probably the better Documented interface in doxygen right now So go and have a read of the that file. I mean all that That section of the manual essentially It tells you all about the the ethos of how performance schema should work and is instrumented and how it's architected in the background and Then gives you a whole bunch of info in how to use these interfaces So that that's where it start essentially The API itself is broken down into these smaller header files, so it the The main these are pretty much the main ones that you'll use as a performance or a plug-in developer So the first one is this mysql thread header file That was almost the original one that came along with 5.5. So in 5.5. We instrumented mutexes read write locks conditions File IO as well and threads themselves So if you're doing anything like creating a thread or Locking a mutex creating a mutex Anything you know with the the standard posix interface on those things in the in this in the thread Had a what we essentially do is we wrap all of those Interfaces that the standard posix interfaces with our own instrumentation, so we Call into performance schema say that we're going to start some weight whatever it may be We do the standard posix call and then we call in afterwards and say hey, I finished essentially that that's all all that these do So they create a bunch of macros if performance schema is not enabled they Basically turn into no ops and if it is enabled it turns into we'll do some instrumentation of this section of the code So if you create a thread or you know Well, a lot of plugins don't create threads, but if you do that then you'll do it through there With file IO it's all in this mysql file header Again same simple thing. So if you're doing a An open of a file or a write of a file or whatever it is you'd instead go and look in this this interface The nice thing about the file interface is actually it will end up Shrinking your code a little bit as well, and I'll talk about that in a minute's well Memory is new in 5.7. So if you're allocating memory Then use this interface so it instead of a malloc you do a mysql malloc All of those kind of things and what we'll do there is not so much track the time spent but track the overall memory that you're Allocating over over time in your plugins as well Network IO is standard stuff. So you can both socket and TCP Not a lot of plugins tend to do that, but you can do that in plugins as well Each of these they all call out to the underlying PSI and you use the PSI Interfaces through the version they be coming. So let's dream a little bit. We'll Imagine you're gonna create an audit log plug-in It's gonna log some stuff to a file Track some stats What you basically need to be able to do with that is open the file right to it in some way Maybe sync it from time to time You'll also have to have a mutex to protect the file access because you'll have multiple threads trying to do this stuff at one point in time It's a fairly simple plug-in, but just imagine that kind of plug-in and what you need to do for it. So When you come in to start doing this stuff Originally you'll start off with if you haven't or if you've written a plug-in not many of you have if you had written one You'd have something like a p-thread mutex T You'll go into an in it. You'll in it the mutex in some way But what you do with Performance schema to actually push this stuff in into performance schema is you instead use this MySQL mutex So essentially most of the posix interface you can Replace the p-thread with a MySQL and you'll be done from that perspective So you pretty much like for like change that the next line you'll see there and I hope you guys can Read that bit over there But then the next line you see there is actually given it a key So when you look at performance schema each of the instrument names They all start with something they have a category And they have their end name. That's essentially the key that you create here You create an array of those things as well You can actually do that you can in it them once with without the array But the interface allows both an array or a single one to be passed So if you have multiple mutexes you'd add them all to this array You give the the category right you see where I'm saying this Category client error that's the Generally you'd name that that your plug-in name Yeah, generally you'd that would be your plug-in name, so we'll call it client error And then we count how many Values are in that array again. You don't have to do this. You can go straight to the register But if you use an array count the number of things in the array pass it to this registration Function and the thing that I should have done here actually I missed it out is you include the minus girl thread header file as well, of course And then you go and register the the mutexes themselves That's actually calling into performance schema and saying hey I've got these mutexes you need to track them in some way you need to expose them in the instruments table to let people Turn them on or off those kind of things So once you've registered you then go off and actually in it your Your mutex like you would with p thread, but instead using my Sql So it's a fairly simple change right for the most part you're changing the the prefix of whatever you're doing and Creating some key to register it within performance schema and then registering it essentially So similar with files, but before you'd have some file pointer you'd go off and do an f open standard interface basically Error at the end of it in the audit class we log and we're going into the end of the the actual query run But if you're doing other kinds of plugins you may consider where you sit in that and how do you actually instrument the rest of the instrumentation after that basically and That's pretty much it from me any questions So the question is What if there's a lot of files if you have a lot of files that are created by your plug-in should you instrument them singly or all as well You shouldn't instrument them all singly, right? I mean the point with a lot of this stuff is to To drill down at that that little point of light right and find out in the file I o a case Who's creating the most IO if you do it all of as one file? You can't do that right you need to track it down to one file The one thing that you have to consider is we have these max counters in performance schema right max mutex Is and max files and those kind of things we set those based on our Defaults of what we need generally So one of the things that you might consider after you've done that is Increasing those max things as the defaults in the server itself You can see that they get lost right you have these performance schema xx lost counters that are in the status counters If you have the file one actually losing instrumentation, then you have to increase it So I mean there you you consider the increased buffers and everything that comes with it But never mush everything into one I wouldn't anyway right because it it hides where time is being spent again No, because bin logs are actually tracked individually as well Right if you look in the file instances table in performance schema every single bin log is there So we don't mush it all into one. It's it's a bin log event type But the instances of those are each individual bin log Okay, thanks