 thank you and thank you all for attending so yeah it's a basic introduction to performance tuning we could talk all day about performance tuning and so it can only really scratch the surface in in the next 45 to 55 minutes and as some of you might have seen before today the SAGE statement I'll mainly focus on existing GA features but there might be an occasional reference to things that hasn't been released yet sorry yeah we have to do it we have to do it so today's agenda I'll start out with some best practices and go through my two two best practices tank and monitoring then look at some important configuration options when setting up an initial configuration file look some more specific offers and caches and the issue of choosing between data consistency versus performance and finally looking at the whole stack so best practices you all love best best practices right it's so easy just do this but my actually my first advice is to be very honest so there are several reasons for this the main things are two systems are not the same so what might work really well on one system might be really bad on another system so you need to take these kind of things into consideration when you're applying best practices and also things change so what was really a good rule of thumb back in maybe 5.0 10 years ago might be a really bad advice today so do think about when somebody offers you best practices that's that some guidelines can be given so my first best practice is to think when you are working with your database and it's not really that different from all other things you work with haven't keep in mind what you're doing and why you're doing it I'll give some more details on this later the second one monitor your system really important and it's really important for performance tuning as well and make sure your chest your chances because it might seem that some change should obviously improve for instance performance then you deploy it and it turns out it didn't I'll try that myself and the only way to minimize that risk is to test in a production like environment and yeah make sure testing actually reflects your production system doesn't help you're testing on the world sample database with the biggest table having 4000 rows and then you deploy to a system with a billion rows in one table and suddenly didn't be nearly the same and make relatively small changes don't change too many options at the same time particularly not if they are related because then you might have one option improved performance and other options make it worse overall it looks like it had no effect and you discard it if you changed one at a time you might realize oh this one is good this one is bad and then you all will end up with a better result same when you're actually changing one option don't jump from the minimum value to the maximum value and because belt might be horrible in between there might be a sweet spot that you only can find by gradually changing your options and then monitor the effect of it another thing is to be mindful of your requirements some of the options that I'll also discuss some today will give you a choice between data throughput or latency or data consistency and in some cases sacrificing some data consistency might be acceptable because the data can be regenerated or you're missing values might be acceptable because it's some statistical sample in other cases like transfers it's absolutely unexcusable to lose data so have that in mind as well sometimes you just have to accept a little slower performance to get the consistency you need another advice and it's particularly true in the newer version of my scholarly views is that the default option is often either the best choice for you or at least a very good starting point for a performance tune and that is also something I'll get back into ensure all tables have a primary key there are several reasons for this some storage engines like NODB simply almost always must have a primary key and if you don't give it and define it NODB will add itself as a hidden key it will still take storage space but you don't have any benefit from it so it's better to add it yourself it will also have that hidden primary key and NODB also is here basically among all NODB tables so there can easily be some congestion there and in replication if you have that set up particularly with row based replication where it's the actual changes that are replicating if you don't have a primary key you might end up doing table scans where you only need it to access one row and when you choose your primary key for NODB it's important to be aware that NODB organizes its data according to the primary key so it has a couple of consequences it's necessary to include the primary key in all secondary indexes because that's how you find the actual row so if you have a huge let's say utfa mb4 varchar 256 column you'll use a kilobyte and a kilobyte later so every row in the secondary index you really don't want to do that so try to choose something relatively small and for instance an integer is a very good choice in this case and secondly and mostly sequential primary key is optimal to avoid that inserts end up being a have to go into the middle on a possible course page splits and other issues so in principle an auto increment primary key is a really good choice for NODB if you choose uuid make sure that the time components as was discussed in the optionizer talk earlier if you intended that are reordered to have it become sequential with time and as also mentioned in 8.0 there are functions to help do that and convert it to a binary value that's much smaller to store as well so what do i mean when i say it's very important to think it sounds very obvious and in some ways it is but there's a little more to it so first of all thinking of what you're doing and why you're doing it is your best defense to catch potential issues before you make a mistake on your production system and the first thing is to analyze what your performance problem is and to make sure you understand what the issue is and what the goal is so don't say performance is too slow because what does that actually mean and also if you don't set a target you could do this the rest of your life because you'll never reach the end so you need to know when it attains good enough so instead formulate your problem like the curie takes 10 seconds but it's for interactive use it should finish at most point one of a second i actually had a curie like this myself and it was possible to make such a huge performance improvement or how what is the target throughput for the system once you know that then you can start analyze why you are not get your target performance and in this case take a step back and consider things instead of just jumping to conclusions and make sure you consider the whole step a curie might be reported slow it turns out it's actually slow at the application level when you look at my scale itself it might be fast so the actual root cause is somewhere in between it could be the network it could be how my skills sense the data is i mean it can be all through the stacks you need to ensure you you evaluate the whole stack as well and once you think you have fine found the cause justified be able to describe performance of somebody else but at least for yourself why this is actually the cause of your problems and similar for the solution start think of possible solutions and feel free to be creative sometimes it takes weird solutions one optimization issue i had while working for support was a joint that couldn't use indexes and it was terrible slow and rewriting one of those joins into a subcury because my spell five six and later support adding ad hoc indexes to subcuries suddenly this curie was the change to actually use an index for the join and became an order of magnitude faster so we had solutions in mind as well sometimes to really work it's also an example of how newer versions can give you tools to solve issues that you couldn't do in old versions and similar to the course explain why you obtained these solutions will work at pros and cons and so on and then when you have found the solution you want to implement write down an action plan so you know exactly what your intent to implement test it and then update the action plan with the results of your tests now we can implement the production system and having a test plan helps you to ensure sorry an action plan helps you to ensure that what you implement that production is the same as your test that might have been a few days or even a longer between you created the solution until you actually applying it so it's important to have these steps down and it also helps you if there's some regressions that you didn't foresee to know exactly what was done so you can might be able to reproduce it in a test system the second best practice advice I had was monitoring and that's your first second and third line of defense so what do I mean by that well it gives you a baseline so when you do make changes as it would be to configuration options as some of those will be discussed later you can see actually what was your change so you can might have curious per second monitored and you make a change and you can see them it goes over down or you might have latency is monitored for your curious and you can see whether improvements are it actually causes worse performance it's also useful for investigating when some user comes and tell you oh the system is basically down everything is it's too slow you go in and log in to test and in the meantime it has been resolved so now what happened again monitoring is your friend you can go back and look at historical data see what happened one hour ago when the issue was occurring and finally it can help you to altogether prevent issues like you might be able to see from your monitoring data that your IO subsystem is getting saturated it's not still an issue but you can see that maybe a couple of months down the track you will run into an issue because of database growth more users etc and you can use the monitoring data to take action before it becomes an actual issue so there are several monitoring options available and for this respect it's not so important which one you'd use it's important to have a good monitoring solution and that you're familiar with it no understands how it works and that you make sure to actually go through and consider and alerts in the monitoring system one issue I sometimes see is that the user was actually alerted about an important issue but it was ignored make sure that you only get alerts with whatever severity level that is appropriate for it if you get an sms text message at two a.m. in the morning if you don't get out of bed to fix it you shouldn't have got the message in the first place because you have to make sure that day where the system is really down and it's really urgent that something is done along the fact that you get the message get you out of bed just but yeah just not a plug-in or go enterprise monitoring is there the enterprise manager control so this one yes so yes that is a plug-in for Oracle Enterprise Manager it is an enterprise addition so basically the Oracle wants everything to be monitored through their so we have put our little screen that makes me and in addition to those more permanent monitoring solutions it's also important to keep in mind that they are more or less real-time solutions and I can really recommend Perf on Linux I've used it a few times myself to find issues that seemed hopeless to debug otherwise MySQL Workbench has performance reports based on the SYS schema I'll get back to the SYS schema in a couple of slides and some monitoring solutions of those listed here might have other snapshots reports such as displaying the process list or other things that that will vary from solution to solution and you can of course also run things like show process list manually to get a snapshot so here's an example of the MySQL Workbench performance reports these are available for MySQL five six and later and they're relying on the SYS schema so there's a list of reports over here and then it actually cures the data to get a snapshot of what values are available right now so here you can see for instance the data files are the ones that uses the most IO the form files that is the legacy data dictionary a second and then you can go in and analyze is that actually an issue based on the timings here it's reasonable to believe the server was just started so it's probably reasonable that it just arrived a lot of form files if it's still up here after a week's up time you probably should look at some table caches to reduce keep looking up the data dictionary so what is this schema so it's a collection of views function and store procedures that is aiming at making the information schema and the performance schema easier to use so the information schema is supposed to mainly have relatively static data such as table column statistics statistics information whereas the performance schema is more for more dynamic data such as which queries are being run IO information etc and actually the next talk will be dedicated to the performance schema so if you're interested stay around for that it's included by default in my skill five seven and later but it can be used with five six as well but you need to download and install it manually from github it was formally known as psilver and was originally created by mark leads and so you might have heard of it in in that context as well and today i can tell i'm also one of those working on it so if you have any issues or questions feel free to come to me afterwards and let me know the manual pages that are available in the five seven reference manual that should be quite good for five six as well so now i've been talking and talking about how good monitoring is but there's actually a such thing as too much monitoring so all monitoring will have some overhead and particularly some will have more than others things like show process list and show engineer dv status if you run that really frequently i've seen it done run up to every second it actually can cause outages on its own in some cases like for the show process list in five six and later there is a table called threads in the performance schema that has less overhead so you can switch to use that instead in other cases you simply have to reduce the frequency a little so do have the overhead of monitoring in mind and finally for monitoring i'll give a couple of examples of how you can find slow cures that need attention of the mind not necessarily be slow there could be other things that causes you to want to to look into it and you can use various methods you can monitoring the in the application you can manually run a curie and see how fast it performs that's mainly useful during the investigation there's the slow curie lock that has been around for ages and is still good because it actually persists the lock so you can look at it even after a server restart and lastly there are two examples here of using the performance schema and system they're both relying on this event statement summary by digest which you have an example of here so it contains a digest text which is a normalized version of the curie so if you're looking up id equals one and id equals two they'll show up in the same normalized statement this is similar to my still dumb slow for the slow curie lock if you ever use that and then you have the aggregator statistics and there are many more columns that are shown here there are whether they'd use the full table scan average time executed minimum whether it used internal temporary tables whether indexes were used etc one thing you might also notice here are these timing values they are not terrible easy to read for human and those are the timing values you get out of the performance schema and those are in pico seconds so 10 to the minus 12 seconds and those can be yes really hard for humans to digest so if you look at the system output and in this case there are several views starting with statements that has information about statements sorted by various things so here it's showing those in the 95th percentile here it comes out as as nice nice human readable values and that's because the system has some formatting functions for instance to convert bytes and timings into to scale it into something that is easier to read for human that's what i wanted to tell about monitoring and i'll move on to looking at the initial configuration file and doing that discussion i will say gain several options that might also need to be modified later in the lifecycle of of the installation so my recommendation start with an empty configuration file in the old days of my scale there was huge templates that you could use you about rate of those and instead just use an empty configuration file there are some things you will probably need to set such as parts port and so on particularly if you have more and more instance at the same host you will then need to make some modifications i will recommend a little extra monitoring compared to the default and you will need to look at some capacity options and that's about it i'll go more into details with these in the coming slides so why an empty configuration file as as the outset so in five six and five seven a lot of work has got to improve the defaults and this work continues i'm sure there'll be other changes for eight zero so it's no longer necessary to have various templates very often the default value is very good at least as a starting point so instead of using the template just leave it with that and change as monitoring shows it's appropriate so for the parts there's a long list here of parts that should be considered i included the error log which is not really related to performance but it's so important that you know where it is that it can be good to always specify this specifically i will discuss some of these in more details as well so why would you want to set parts and not just rely on the defaults which will place everything in the same directory well the disk subsystem often becomes a bottleneck in in database work so you might want to split out some some of the files for instance some hot tablespaces into separate disks or you might want to have something like the undo log that has a lot of random IO on an ssd to to improve performance or it might simply be that you have multiple instances that cannot you cannot have those right to the same directory and also note that it's possible for uh with inno dv for file for table table spaces and general table spaces when you create it create them to move it to a different uh directory than the original data directory so for monitoring what i will recommend enable the inno dv underscore monitor underscore enable it set it enable everything uh experience have turned out that it's not really anything that has a measurable effect and so it's it's better to have everything available for the performance schema make sure it is enabled that's the default in five six and later and often the default configuration is a really good starting point it can be configured at one time as long as it's enabled um so uh you might not need to do anything more though in five seven you might want to consider enable transaction monitoring if you're using a lot of multi-statement transactions uh in your application so capacity settings these are some things that are very difficult to provide a really good default value of four because the range of uh systems deployed using my skill is very so greatly from a small test instance where uh you only need maybe 50 megabyte inno dv buffer pools for caching data and indexes up to a sense of terabytes of data where just need as much memory as as you can possible get your hand on so these are some things that is useful to to consider when you're uh deploying so if you look for the inno dv buffer pool into the uh reference manual it says on a dedicated database server you might set the buffer pool to 80 percent of the machine's physical memory size so this is one of the examples where the thinking part can be good to to take into consideration because what if your friends just have one terabyte of memory you really want to reserve 200 gigabytes of memory for the operating system uh probably not so instead you might want to go through and it's not as simple as it sounds here because it's actually hard to predict how much memory my skill would use but try to to at least guesstimate how much memory you can make available and also it doesn't help if the inno dv buffer pool is much larger than you actually working set in five seven or later you can resize the inno dv buffer pool without restarting my skill so it does make it much easier to to adjust if you realize that you have used the wrong size related to the buffer pool is the redo lock where inno dv writes out the committed transactions the size is set by two options so you have a number of files and each file have a given size and in later versions if they the combined size can be up to just below 512 gigabytes if you have a large redo lock you can avoid excessive check pointing so check pointing is required to avoid the redo lock to become full at some point it's a circular lock so there's once you have the head and tail reach you simply cannot do any more uh writes until it has been cleaned up so you should ensure that it's large enough to avoid that i'll give an example and but at the same time have in mind that the more basically the pages that are written to the redo lock but not still being check pointed to the data files are dirty so that needs to be done either doing shutdown or doing restart so if it's too large you'll have to wait too too long to make restarts so how to determine whether it's big enough and i'm running a bit short on time so go very briefly through this the slides should be made available so you can look at it in more details this number is how far you are in commits this number is where your last checkpoint was and if you have all the matrix i've made recommended to enable earlier enabled you can get it as a plain select theory which can make things easier the used log is simply the difference between those two and you can then combine compare to the total size and in this case 47 percent of the log is used so is that good or bad well it kind of depends because what the important thing is is you will never want to reach 75 percent full because at that point what is called an asynchronous flush is triggered and that will basically store everything on your system until it's done so that will cause stores in the voting select and write curious and it's not as innocent as it sounds with the asynchronous flush and so you should try to keep a bit of head room so you have room for for some peak that comes in and then can continue flushing afterwards and that said also be aware that newer versions have better algorithms for flushing so it's less likely to hit this issue at all in the newer version you're using another example of if you're hitting this issue the solution might be to upgrade rather than trying to work around it so here's a monitoring example of of the examples shown before so the bottom graph shows the usage and we can see here the 47 percent in this case corresponded to a peak so it's not a bad case in this situation had it been down here it was 47 percent it would use you probably look at increasing it and you can also see how the IO Act activity goes up as the users increases so the undo lock it's not really a capacity setting as such but i will include it because it can only be changed when you first initialize in a dv so it's quite important to to consider what you want so in five six and later you can choose to move the tape undo tablespaces out of the system tablespace and any of you have issues with the ip data one file being really huge and not containing any data because if that's the case you probably have that it's the undo lock taking up all the space and by moving it out you can avoid that the ip data one file gets large and in 5.7 there's support for online truncation of the undo table spaces so if you had a huge transaction that caused a lot of undo lock or long running transaction that had to keep reviews open then you can truncate the undo lock tablespaces afterwards and reclaim your disk space so my recommendation is to move the undo tablespaces out of the system tablespace and then you can also later consider moving then to our ssd to get better performance but that can always be done at a later stage some other capacity settings be mindful of max connections it sounds nice to increase it a lot so avoid having connection attempts rejected but each connection does use memory and file descriptors so it might prevent these resources being used for other purposes the table caches can be a really a really cheap way to get a fairly good performance gain and it's because high or moraisons are simply so expensive particularly the definition cache you should definitely ensure that you can have all your table definitions called at all times the table open cache can be split into instances this helps on contention it makes it cheaper to find tables to edit if it's too small in 5.7 the default value has been set to 16 and that's a really good starting point also in 5.6 our general recommendation recommendation in support when somebody asked for for this setting we just say put it to 16 bothersome caches surely caching is a good thing and surely for something that is good the bigger the better right no and here's a few examples why you should be careful it's true that buffers and caches can be very good but pick them carefully first example the curicash this was enabled by default earlier it has been disabled for some versions now and for a good reason it's actually often a performance bottleneck so the curicast works by taking the raw query submitted and storing the result set with it but it's all guarded by a single mutech so if you have 100 concurrent curies they'll have to line up waiting to check the curicast one by one so that's terrible for concurrent workloads in practice usually disabling the curicast and look at potentially other caching solutions is a better way to move forward another buffer is the join buffer this is also an example of an option that has changed the bit of meaning over the years in my skill 5.5 and earlier it had a relatively limited uses and there was no reason to have it larger than what could one row could fit into it so if you didn't have any rows larger than 32 kilobytes having it at one megabyte would just be waste however in five six and later an optimization called bat key access was introduced that also uses the join buffer and in some cases for some curies it can actually be beneficial to have a relatively large join buffer to be able to fully take advantage of this optimization i'm not going to so this optimization it's a bit beyond introduction level so and then saying to be aware of join buffer size is the minimum size allocated so if your join just needs 100 bytes of buffer but your set join buffer size to one gigabyte one gigabyte of memory is allocated and memory allocations are expensive so it will really hurt the performance so instead keep a small global the default value of a new connections for instance between 32 kilobytes and 256 kilobytes is usually a good range and then increase if your session your furious actually needed and a similar story for the sort buffer size same range is usually a good value our performance architect always uses 32 kilobytes that's what his test show is the best value for his workload it is of course workload dependent as well this one can be relatively easy monitor through search merge passes which is a status variable you can get it like this if this one increments with a few like five to ten per seconds it's usually you're in the right ballpark and again it can be increased at a pair connection or pass session basis so if you know you have a query that needs to do a huge sort you can increase it and get the benefit of the last problem so why do I keep advocating small sizes beyond trying to reduce the overall memory uses well in some cases as for the join buffer it's a minimum size so you don't really want to allocate more than you actually need but also for instance with the clipc matterman library on Linux which is the default used crossing certain thresholds can cause it to be an order of magnitude or more slower to allocate the memory so even if you're in principle could benefit from the larger buffer and sometimes that overhead of allocating it makes it better to use a smaller buffer and and live with the overhead of having that in principle too small buffer eventually testing is the only one that can really show what what is the best for your workload so data consistency versus performance so two examples in a db flush logger transaction commit and sink in later so in order of data safety you can have three different values depending on how often you sink the safest and the default is one where every commit will cause a sink of the the redo log that's required for the default durability and acid and it's also why it's recommended but you would think that that's the slowest as well and it is however if you make certain changes such as using separate disks consider an SSD particularly if you have a high commit rate because the redo log is a sequential write and read so spinning this is in principle good but you might run out of how many flushes you can do for a second whereas SSD can handle more flushes so that can be a useful reason for using an SSD and also battery back disk cast can help you sink bin lock is a bit similar but it's for the binary lock that is used for replication and for point-and-time recovery three classes of settings zero my scale will only flush the log to this when it's the log is rotated one happens in every commit so this is the safest and a positive integer will flush every end commits the default value used to be zero now it's one and one of the reasons we've been able to change the default value is that inodb now supports group commit which reduces the overhead of of having the value set to one again an example of how upgrading can give you some benefits in terms of performance so a warning if you don't set the value to one if you're having replication set up and the master presses you're most likely have to rebuild the slave or it'll be our sink but you could argue sink bin lock equals zero surely must be the best performing again as with the caches and buffers it's not always quite that simple so by default and the maximum allowed value is that the bin lock is rotated when they're one gigabyte large back when this feature was implemented one gigabyte of memory was a lot today yeah it's not so often if you have this the operating system will end up caching the whole binary lock and then when you're rotating it suddenly you have one gigabyte that has to be written to this and while that is done no commits can happen so if you see in your show process list the several cures waiting and commit and it happens to be while the log is rotating consider looking at sink bin lock so in short sink bin lock equals zero will probably give you the best throughput but sending it equals to one might actually give more predictable performance and often that's as important as as the throughput itself so final topic is to consider the whole stack so when you have the performance problem that can occur anywhere application through to my skill anyway in between so you need to take that into consideration both when investigating and when monitoring make sure you're monitoring solution and monitors disc users network memory etc and you should also with respect to configuration consider the operating system and hardware settings couple of examples the IO schedule on linux you have several choices on many distributions the cfq schedule is the default that works pretty well for reads it's terrible for writes because it serializes so really poor for observable database workload so change the scheduler to either to know up or deadline it's really easy to check what your current scheduler is it's in the square brackets and you can change dynamically to write into the same file and then you can confirm that it's been changed can you can also set it at good time to ensure that you have the same value each time the host is restarted second example the memory allocation library remember so clip c is the default it's actually often a bottleneck on linux it's better to choose tcmalloc or jemalloc they're both under active development so i prefer that it's being tested for your workload what is currently the most optimal how you change the malloc library juice depend on exactly how you start my skill if you use mysql dsafe you can add something like this to your mysql configuration file where you set the malloc lip option to the path to the to the malloc library if you use system d you set the ld underscore preload option in etc sysconfig mysql so reading the end of the talk and wrapping it all up in short performance tuning in mysql is actually not that different from other types of performance tuning or problem solving in general many of these things you'll if you're a developer software developer you'll probably have heard of them while optimizing your software as well make sure that you understand what your requirements are consider the whole stack and consider whether upgrading is actually the easiest way just to get where you need to to be thank you very much and i think that might be one or two minutes left for questions you mentioned the transition monitoring how is monitor the transition actually means for example there's some certain of inserves updates are doing on the for example order table and some goes to the customer table so how you can monitor it means is there any flagging you can mention on the transition so you're thinking of that for the performance schema in a transaction monitoring is that the means you mentioned for transition monitoring so i think this is available by report or is there a part of the performance schema so it's in the performance schema so there's a hierarchy of the transaction statements statements might have some statements like a score procedure it's invoking a different statement statements have stages and stages various weights and it's that highest level of transaction you can enable so so when you go in and look at events in the performance schema you can get that this transaction included these five fewer statements so this is the thing which we can evaluate the performance schema yes so would you recommend something for the lower testing for example if i enable a general law on the main production maybe so what i have the single file and everything is written in a sequential order in that particular file if i read that file on some testing instance it is a sequential manner we can learn in a sequential order what i actually wanted is a parallel as compared to the database load which comes from different users so is there anything which we can test the same thing or similar this load on the production for some testing instance yeah i believe there is some script somewhere that can take a general log and use the timestamps to to replay with the right intervals and can i know i'm searching for a question if i record correctly i don't i wonder whether it's part of the background the tool kit i'm not completely sure but you might want to try to look there as a first place oh but thank you