MySQL Performance Tuning: Part 1. Configuration (Covers MySQL 5.7)

is my mascot server configured properly should I run community Moscow mariadb Percona or web-scale Oscar how many inner DB buffer pool instances should I run how do i size the inner DB log file size and what is that in Ruby log anyway these and many more questions will be answered shortly hi my name is Armas Michalowski I am a former percona performance consultant and architect currently writing consulting and teaching at speed Emmy calm I have started using MySQL in 1999 with version 3 point when t3 and in just a few years around 2004 I got an opportunity to work as a consultant on scaling the 5 fastest-growing websites in lutherie Nia at that time in December 2006 I have joined percona as their first consultant where I spent over nine years working with hundreds of companies helping them fine-tune optimize and scale their database systems and designing large-scale online applications companies such as BBC Engine Yard various social networks and small shops like estan the virtual bank of TX Pico Isle and many others in this video you will learn all there is to learn about tuning Masco configuration file for best performance after you're done watching assuming you watch it till the very end you will know exactly which settings you need to tweak on freshly installed server or one you've been running for years and what to change when you upgrade your server with more CPUs more memory faster magnetic disks son or a brand new SSD disk and where to look next here's how this is going to be structured we will begin with the discussion on different MySQL distributions so you know which distribution you should use for which project next I will introduce you to the essentials of MySQL configuration tuning most common mistakes I've seen and done myself during this 12 years and best practices including how to apply the changes and see the effects then we will look at 17 key MySQL settings when it comes to performance and finally as a bonus I will show you how to make sense of various ways to look at MySQL status counters and gauges enjoy first let's talk about the number of different MySQL distributions that are available these days there's the Oracle community MySQL percona server MariaDB and web-scale SQL let's quickly discuss what they have in common and what are the key differences so you know when to use which it's very important to note that as long as you're using the same major version you can use most of them interchangeably you don't need to do migration or even upgrades for your tables you can just stop the server switch the binaries and run the different version using the same data directory your application won't notice the difference well at least from the functionality point of view let's talk each of the MySQL versions real quick community MySQL this is the Oracle developed MySQL that in my view receives the most development efforts it is also the version that is used as an upstream for all of the other Maskell versions we'll talk about how each of them is using the upstream in each section separately in terms of performance there are a few important things to know community mysql 5.1 does not have inaudible plugin enabled by default so if you run with default configuration you will have a very poor performance at scale and you also will be missing out on a lot of features that's not to say you should be using phi1 at all but if you are currently beware that the very first thing you could do to take its performance to the next level is enable the inner DB plugin community mysql 5.5 already has strengthened inner DB at its core however there's still lots of performance improvements as well as features missing that you could find in appropriate Percona server with extra DB and Maria DB with extra DB versions especially when it comes to stability at high write rate other than that you can get pretty far with Maskell 5.5 as your base server community mysql 5.6 got a lot of new features and performance improvement including optimizer improvements a number of new mutexes that were introduced to alleviate contention on the colonel mutex performance schema improvements multi-threaded punch threads separate flush thread and so on and so forth add this release there are even fewer reasons to use an alternative version such as Percona or mariadb and we'll talk about them in the appropriate sections community MySQL 5.7 the newest MySQL GA release yet is currently the server with most features including such new features like multi-source replication JSON support proper multi-threaded application online buffer pool resize spatial data types or in ODB c schema and so on and so forth also according to Oracle's benchmarks it is the best performing Maskell server that's currently available in the market beware that some vendors have attempted to manipulate benchmark results showing better performance on their versions by using a different default configuration so do not be confused by that I took a bit more time talking about community MySQL because it's used at the core of most of the other server variants to explain what I mean by that let me now switch to Percona server Percona server was originally launched around July 2008 when we started releasing our builds of Maskell 500 and 5.1 with few additional patches microsecond resolution slow query log execution plan details additional inner DB statistics and users that is expect from Google although it wasn't called per corner server back then yet it was called simply Percona patches for mysql unofficially however percona has been building past versions of moscow for their customers even earlier in addition to slow query log details that helped us as consultants understand better what is happening inside the server those were also various performance fixes we always follow the one simple rule use the latest upstream community Masco remove any redundant features that are now implemented properly Maskull and never break backwards-compatibility meaning that if you run percona server 5 173 you should be able to switch back to Moscow 5 173 by simply swapping the binaries there were a few exceptions to that like with additional rollback segments for extremely right heavy databases which if enabled didn't allow downgrade however in all of these cases percona made it very clear I mean danger in capital red clear that these changes are not backwards compatible and they were never enabled by default and none of these incompatible changes are available in either five five five six or five seven versions of proko no server so in a nutshell if you are running mysql 5.6 dot 28 and you want to try out per corner server just remove community mysql 5.6 down to 28 install percona server 5.6 dot 28 and if you don't like something you can switch back to community edition the same way it's just a matter of stopping one version and starting the other now in terms of performance here's a few interesting things if you are running mass called 5.1 do upgrade to pro con a server 5.1 you will be amazed how much better it is on perk on a server you don't need to enable the energy be plugin it will be enabled by default but it has an enormous amount of performance and especially scalability improvements plus you will have all the additional features that showed up in mysql only in versions 5.5 and 5.6 broke on a server 5.5 still has a great advantage over moscow 5.5 adaptive hash index partitions faster checksums buffer pool scalability improvements much better adaptive flushing algorithm plus a number of additional plugins that are otherwise only available in enterprise mysql such as pam authentication unity b table import audit log and thread pool so it's both more stable and more scalable perkiness 5.6 has the same benefits of version 5.5 and it has a few performance improvements but the difference over my skull 5.6 is now only visible with high-end hardware superfast flash storage hundreds of gigabytes of RAM and tens of CPU cores at this time per corner server 5.7 is not yet generally available however I would only expect per corner server to make a difference with edge cases and for most common even high performance workloads MySQL 5.7 may be good enough for you one other big advantage of per corner server I didn't talk much about is the enhanced slow quelaag for me it is the top reason why I run perc on a server everywhere I can just by using the slow query log I can get such an enormous amount of information about the queries that I pretty much don't need any other statistics from MySQL but since this is not a query optimization course let's now talk Mariya DB Maria DB is a fork of MySQL created by Monte Vida news the original author of MySQL it is now adopted by many Linux distributions and while it's a good muscular placement in many aspects in my view it's slightly over appreciated if you have ever been to Monty's talk you know he's focusing his marketing efforts on making Oracle look bad while many of the things he has been manipulating never became true anyways politics aside let's talk about two versions of MariaDB MariaDB 5.5 and maria db10 maria 5.5 is using mysql 5.5 at its core plus it adds the extra DB storage engine replacement for inner DB adopted from percona server and some code from maria DB 5.3 which has a number of query optimization improvements multi master replication group commit fix and a few other features so if you are using 5.5 branch with some specific query types you will see benefits by upgrading to Maria DB 5.5 mariadb 10 on the other hand is very different from all of the other variants of MySQL in that it has started diverging from the upstream MySQL it is what would be called a real fork rather than a spoon Mariya be used mysql 5.6 at its foundation but you should expect that MariaDB will not be backwards compatible with mysql 5.6 5.7 or any later maskol versions most noticeable new features of Moorea DB are parallel replication multi-source replication cassandra spider and Tocco DB storage engines the adaption of Moorea DB is very limited so far and there aren't any good quality benchmarks comparing mysql 5.6 or 5.7 to Maria DB 10 except for the ones produced by Maria DB which are potentially biased so it's too early to say if it's better to switch to Maria db10 or stay on Moscow however given the amount of development resources MySQL is getting and the vast improvements in Maskell 5.7 my recommendation is to stay on MySQL rather than marry a DB for now finally let's discuss the last player in the field web scale SQL web scale SQL is a collaboration amongst engineers from several companies that face similar challenges in running mascar at scale namely Alibaba Facebook Google LinkedIn and Twitter it is a minor scale fork using mysql 5.6 at its core and as far as i'm aware there are no plans to use MySQL 5.7 as they consider 5.6 a good enough version to use as a basis however it is also not planning to diverge from MySQL upstream even though some features might make your data files not Maskell 5.6 backwards compatible oh and also the coolest stuff is back ported from MySQL 5.7 when needed it's very important to mention that unlike other variants mentioned earlier web-scale SQL serves a very special purpose it's not meant to be used as a general-purpose Moscow server rather it addresses very specific needs of the named companies running Moscow at scale well scale with the capital S so unless you are running at least few tens of MySQL servers you may wait a little on optimizing to that degree here's just a few things that are different on web-scale SQL things that other variants don't and may not even have plans to have for quite obvious reasons ability to specify millisecond timeouts for various network operations super read-only mode ability to disable deadlock detection not something you want to run on a typical transactional database server prefix index query optimization absence of performance schema by default apparently performance schema may have a noticeable overhead even when not enabled you know to be flushing performance fixes most of which are also available in per corner server Marie DB and now MySQL 5.7 you can find a full list of extensive details in the following blog post by my former colleague Lorena's on Percona blog so you can see that unless you know exactly what you're doing some features are potentially dangerous to run with therefore if you're in doubt stick to proko no server Murray gdb or stock mask hole 5.7 until you are absolutely sure web-scale SQL is what you need now let's start with the essentials of MySQL performance optimization namely MySQL configuration for high performance here's a fact MySQL defaults are poor yes you can start Maskull with no configuration and you can start using it for development right away however you can't just put my SQL with default configuration to production and expect that it will handle the increasing workload with ease you have to prepare your server for that there's two good news though first maskull v dot semmen has better default than ever before and second my skull is very easy to configure it's just one configuration you have to deal with my dot CNF and it has one option per line so the format is very convenient the location of MySQL configuration file may be different across different operating systems and distributions however on linux it's typically either at semi CNF the Red Hat style or Etsy MySQL my dot CNF Debian style on windows it's going to be the data directory open that file as we'll need it real soon or create it if it's not there yet oh and note that on some systems you would typically already have the default configuration file so you may need to do some merging in the editing process and remove some options that are apparently not necessary I would like to start with the most common mistakes when configuring Masco as these are very very common during the last nine years I've spent that percona working as a MySQL performance and scalability consultant I found that customers often use the trial and error approach when tuning Maskell configuration they change a few things and check if it feels better and this is the biggest mistake that I've seen being done when approaching Masco configuration problem is that by the time you're measuring how your application feels situation may have changed already plus you have to consider the warmup time and disregard any overhead you see due to it and most importantly you can't actually base your optimization efforts on feelings it's either faster more scalable and you understand why or it's not after such a tuning session you may end up with a worse configuration that you have started with in fact more often than not that's exactly what I would find where the customer would first contact me so please don't do it understand exactly what it is that you're changing and only change it when you know that is exactly what you need most important variables to performance will be discussed here so you have a good opportunity to learn this and avoid this most critical mistake now here's a few other common errors using Google for performance advice never trust the first response you find on Google when searching for a performance advice or a value for your specific variable a lot of the advice on the Internet is very generic and often lacks context for example you may find a lot of configuration files on the internet that were used for benchmarks however benchmarks often intentionally do certain things that should not be done on production servers such as disabling W write buffer setting in a DB thread concurrency to zero and similar also a lot of the settings are Hardware dependent which is why it's even more so important to understand what it is that a specific variable is doing exactly obsessing about the fine tuning the my CNF don't get obsessed about fine tuning the configuration usually 1015 variables will give you the most impact and fine tuning the variables is highly unlikely to have any additional benefits it can do harm though if you still have a performance problem even after you have applied all the recommendations and gotten rid of everything that you shouldn't have touched in the first place the problem is probably somewhere else bad queries lack of resources etc changing many things at once when working with the configuration file change only one thing at a time especially if you already have a solid configuration otherwise when things go east it may be very hard or even impossible to figure out which setting could have caused the issue so you will have to roll back all of the changes and then start one by one anyway if it's a new setup or you are running with default configuration until now feel free to go wild and implement all of the changes recommended here at once otherwise change one thing and take some time to monitor the server after the change keep my CNF in think with the changes you make it's no secret that many things can now be changed online without even touching my CNF even the inner DB buffer pool size can be changed online in MySQL 5.7 that's very convenient but make sure you update my CNF after you are done with the changes or you will lose all of the changes when MySQL is restarted and you'll have to start over redundant entries in my CNF if you use the same variable twice Moscow will not complain about it in most cases it will just use the latest value found for the same variable so be sure you don't add the same variable twice otherwise you may end up not seeing the impact also note that the - and then underscore can be used interchangeably so you know DB log file sales with dashes between words and inner to be log file size with underscores between words are both referring to the same server setting multiplying buffer sizes when you add more memory to the server don't just multiply the size of all buffers in effect some buffers are local some global some are storage engine related some are Server wide in fact there are very few variables that you need to increase in size as you add more memory yes these are crucial to update or you won't have the desired effect but only these and no other I will talk about these variables as we progress using the wrong my CNF section while MySQL configuration file is simple it's important to mention that it does have sections and these are important for example such sections as MySQL client mysqld safe and there's also MySQL D section which is exactly the section you must use if you want server configuration to take effect so all of the variables for server configuration should be placed after mysqld and before the next section begins the only exception here is if you're using the mysqld multi script in which case you'll be working with several sections rather than just one changing configuration online like I mentioned earlier there is a way to change some parameters online and in fact it's safe to try even if you're not sure if you can change it online MySQL will just tell you that the variable is read-only meaning that you should be changing the my CNF file instead and restarting the server here's how I would change in the DB buffer pool size to 128 megabytes online on MySQL 5.7 except that this is mysql 5.6 which is why i do not succeed how about inner DB threat concurrency let's change that but first let's check the current value for it it's zero let's change it to eight this time we succeed and in addition to the syntax of changing the configuration you can see how you can access a value of a single global configuration variable which brings me to the next section global versus local variables we have just seen a way to alter global Masco configuration but here's an interesting thing quite often you don't need to update the global configuration just to get the single query work properly in fact very often it's better that you leave the global configuration untouched because an optimization for one query may affect all of the other queries in a negative way for example one of the things I recommend is to keep the sort buffer size at its default value because otherwise a full buffer is allocated for any session we're sorting is done and that may end up wasting a lot of memory and time allocating it but what do you do if you have a query that is consistently sorting large amounts of data and you can't add an efficient index but you also want to avoid disk based sort or what if you have a query that's constantly using index merge intersection optimization and you know it would be much better off not using it in that case just set a session variable prior to running that query by issuing a statement similar to this one this will only change the value and behavior for this session 17 key MySQL variables and now let's get down to business let's talk about the 17 mics and app settings that are key for optimal Masco performance I have also prepared a special ready to use in production my CNF file that includes all of these 17 variables with short explanations and links to appropriate sections on my blog with more verbose explanations you can download it by going to speedy me comm slash 17 basically you can use this file as a starting point for your production server by tweaking one or two settings where appropriate now here's the few Maskell settings we should talk about default storage engine if all of your masks all tables are using InnoDB and you don't need convincing that inner DB is the way to go you're all set with this one if you're unsure however bear with me we have some ground to cover Moscow has supported pluggable storage engines since its inception over 20 years ago but for a very long time my Isom was the default storage engine and many people running Maskell didn't even know anything about the underlying storage engines after all Moscow was originally designed to be a small practical database for small websites and many applications got into habit of using my Assam storage engine explicitly this seemed like a good idea first but here's the problem my eeeeyes 'm was not designed with highly concurrent workload number of cpu cores and raid Ares in mind and it was never meant to be resilient either so as websites started getting more traffic they could no longer scale because my SQL queries would spend seconds waiting on table level logs the only locking mechanism that my eye some support and they didn't want to be losing their business critical data on each Maskell crash either what many people don't know though is that virtually for as long as masc system my eyes on storage engine had cousin called InnoDB and highly concurrent workload performance and resilience also thomas ET consistency and isolation was exactly where InnoDB shined sure it had a few bumps as it was growing up most notably scalability issues before version 5 0 30 but over the last nine years you know the B has been improved in all areas you can and can't imagine whereas my Sam got virtually no attention at all therefore as of moscow v dot v dot v in ODB became the default storage engine and now this you will hardly find a sizable mysql installation that's still using my item and not in a DB now before we go any further let me show you how you can quickly get a count and list of my asam tables on your system so you can start planning your migration here's a really cool query that shows the storage engines you are using and a number of tables using each storage engine you can fetch this query from the e-book you will get add the link i mentioned earlier oh I didn't mention you will get an e-book well consider yourself warned so go to speedy me comm slash 17 to download both my CNF file and the e-book where you can pick up all the queries that I will mention on this video on the screen you can see that this particular customer had 13 my some tables holding over 7 gigabytes worth of data combined and if you find yourself in a similar situation don't worry we'll fix that soon to get a list of all my some tables sorted by size just run this query bear in mind that changing the default storage engine setting to in ODB or upgrading mysql doesn't automatically convert all your tables to energy be far from it you actually have to go and convert tables one by one or have a script to it now before you go on and convert just the big ones to InnoDB here's something really important sometimes as part of migration to inner DB DBAs start with the large mass on tables to see if things will get better sometimes it helps but in many cases it doesn't here's the problem if at least one table in a joint is my Assam the entire query is using table level locks so having even a small my Assam table in a large joint can be very bad for concurrency so when you're ready to convert make sure to convert all my some tables to unity be not just the big ones I recommend that you hold off with conversion for now until you understand in the DB configuration better but when you're ready you can run the following query to get a list of queries that will convert all tables in a given schema from my Assam to nodb in the beginning I mentioned that many applications are using my eyes on explicitly what I mean by that is that during the initialization of the database when all tables are created create table statements often have engine equals my eyes am set at the end so tables are created as my eyes am regardless of your default storage engine setting so it's a good idea to check for any mice on tables every now and then and if you're using pork on a server you may also want to set the following in my CNF in a DB buffer pool size this is the most important energy variable actually if you're using inner DB as your main storage engine for you it's the most important variable for the entire MySQL server computers use most of their memory to improve access the most commonly used data this is known as caching and it is a very important part of computing because accessing data on a disk can be a hundred 200 thousand times slower depending on the amount of data being accessed just think of it a report that takes one second to generate when all the days in memory could take over a day to generate if all data had to be read from disk every single time assuming also random i/o my sum is using OS file system cache to cache the data that queries are reading over and over again whereas inner DB uses a very different approach instead of relying on operating system to do the right thing you know DB handles caching itself within the inner DB buffer pool and throughout this video you will learn how it works and why it was good idea to implement it that way in a DB buffer pool actually serves multiple purposes it's used for data caching this is definitely the big part of it in this is caching yes these share the same buffer pool buffering modified data often called dirty data lives here before it's flush storing internal structures some structures such as adaptive hash index will get it later or row locks are also stored in the inner DB buffer pool here's a very typical in a DB buffer pool page distribution from a customer machine with inner DB buffer pool size set to 62 gigabytes as you can see buffer pool is mostly filled with regular inner DB pages but about 10 percent of it is used for other purposes oh and in case you're wondering what units are used in this graph that's inner DB pages a single page is typically 16 kilobytes in size so you can multi apply these numbers by 16384 to get a sense of usage in bytes so what should energy be buffer pool size be set - I'm glad you asked I was just about to gather on a dedicated Moscow server running all in ODB as a rule of thumb recommendation is to set the inner DB buffer pool size to 80% of the total available memory on the server why not 90% or 100% because other things need memory - every query needs at least few kilobytes of memory and sometimes few megabytes there's various other internal muscular structures and caches you know DB has a number of structures using memory beyond the buffer pool dictionary cache file system lock system and page hash tables etc there's also some - call files that must be in os cache binary logs relay logs energy transaction logs plus you want to leave some room for the operating system memory structures by the way this number is not pulled out of the Hat I've seen hundreds of systems large and small and even on the servers with 512 gigabytes of RAM we found 80% to be about the right size for the sustainable operation if you see a lot of free memory sure you can bump it up a bit especially as MySQL type that seven makes this much easier but do not let mask you'll consume all memory or you will face problems big problems namely swapping swapping is the worst thing that can happen to database server it's much worse than having buffer pool size that's not large enough one example how this may go wrong is you need to be using a lock that it typically uses when accessing a page in memory 109 and seconds access time to access the page that is swapped out 10 milliseconds access time this would cause all currently running queries to stall and as these things usually don't walk alone you can guess where this is going if your MySQL server shares resources with application rules of thumb no longer work in such an environment it's much harder to find the right number on the other hand it's usually less so important to find the right size and the good enough number is often good enough in a shared environment in any case first I check the actual size of the inner DB tables chances are you don't really need much memory remember this query from the earlier segment this will give you an idea how much memory you'd need for in a DB buffer pool if you want it to cache the entire data set and note that in many cases you don't need that much you only want to cache your working set actively use data if it all fits and say half the memory in the server great set it to the size of all of the energy be tables combined and forget it for some time if not let me teach you a very simple trick to determine if the energy be buffer pool is well sized using a server command line run the following what you see here is the number of reads from disk into the buffer pool per second these numbers above are pretty darn high luckily the server has an i/o device that can handle around 4,000 random i/o operations per second and if this was an OLTP system I would highly recommend to increase the inner DB buffer pool size and add more memory to the server if needed if you don't have access to the command line I suggest you get one as you're a lot more flexible there but if you want a graphical user interface alternative MySQL workbench is your friend under performance open the dashboard and you will see both in a DB buffer pool grids and also energy be discrete finally here's how you actually change the inner DB buffer pool size if you're already on MySQL 5.7 you are extremely lucky because that means you can change it online just run the following command as a super user and you're done well not exactly done you still need to change it in my send a file to but at least you will not need to restart the server as the inner DB buffer pool size will be resized automatically with something along the lines in the error log all earlier versions of Maskell do require a restart so one set inappropriate in will be buffer pool size in my CNF to restart my SQL Server 3 celebrate improved mascot performance you need to be log file size this sets the size of inner DBS redo log files which in MySQL world are often called simply transaction logs and right until mysql 5.6 dot 8 the default value of inner DB log file size which was 5 megabytes was the single biggest inner DB performance killer starting with Maskell 5.6 date the default was raised to 48 megabytes which for many intensive systems is still way too low anyways let's talk a little about the inner DB log file size so we have a better understanding of what it is how MySQL is using it and how you would have to tune it have you ever used an undo or redo function in a word processor image editor or virtually any editor for that matter I'm sure you have well guess what transactional databases have exactly the same thing well not exactly but the principles are the same and just like it's important for you to always have the ability to go back a few steps in your editing process so are the undo and redo functions important for a transactional database system why two reasons primarily one rolling back a transaction that's the undo to replaying committed transactions in case of a database crash and that's the radio here's how it works undo when you are using a transactional storage engine say in ODB and you modify a record the changes are not written to data file directly first they are written to a special file on disk called transaction log and at the same time they are also modified in memory the inner DB buffer pool this new energy be page that contains your modified record is now called dirty the original unmodified page is copied to a special area on disk called rollback segments so far so good now if someone or something interrupts a transaction with a rollback before it's committed undo operation needs to occur your record has to be restored to its original state as the changes were written to the data files yet this is pretty trivial in a DB just takes the old copy of the page from the rollback segments wive's the dirty page from the memory and marks in a transaction log that the transaction was rolled back so there you go data file was never modified and it's a good thing that it wasn't because you have cancelled any changes you made before even issuing a random write operation to flush that dirty page to disk redo when you commit the transaction however an inner DB approves your commit that is it returns from the commit call changes are ready to be written to the actual data files you'd think they are written to disk immediately at this point but that's not what happens why because doing so would be very inefficient instead the changes are only written to the transaction log this is very efficient sequential activity called redo logging while the modified record still lives in memory in the inner DB buffer pool as the dirty page for as long as time comes to flush it crash MySQL just crashed guess what happens now well if InnoDB had no redo log and it only kept dirty pages in memory all of the committed transactions that were not flush to disk yet would be gone forever quite a disaster if you consider that one of these transactions may have been salary transfer from a company account to yours luckily the changes are always written to the transaction log also known as redo log before the operations return from the call so all in a DB needs to do is find the last checkpoint in the redo log position that's been synchronized to disk and redo all of the modifications by rereading them to be modified data from disk and re running the same changes easy-peasy right well right but only on the surface underneath there's a lot of really complex stuff happening that you probably don't want to know about right now we can talk about it sometime later one thing you may want to know about though is how to sized in a DB log file size properly the rules are actually pretty simple small log files make writes slower and crash recovery faster large files make writes faster and crash recovery slower writes becomes slow with small redo log because the transaction logs act as a buffer for write and if you have a lot of write Maskull will be unable to flush the data to disk fast enough so write performance degrades large log files on the other hand give you a lot of room that can be used before flushing needs to happen that in turn allows inner DB to fill the pages more fully for example when you modify few records that are on the same page or in fact modify the same record several times and also in case of magnetic drives flush the dirty pages in a more sequential order as for the crash recovery larger redo log files means more data to be read and more changes to be redone before the server can start which is why it makes crash recovery slower finally let's stop how you can figure out the right size for the redo logs luckily you don't have to come up with the size that's exactly right here's the rule of thumb that we found to work like magic check that the total size of your redo logs fits in one to two hours worth of Rights during your busy period how do you know how much energy is writing here's one way to do it in this case based on 60-second sample in ODB is writing 2.6 gigabytes per hour so if in ODB lock files in group was not modified by default it is 2 which is the minimum number of redo log files that in Ruby need then by setting inaudible log file size to say 2,500 60 megabytes you will have exactly 5 gigabytes of redo log storage across the to redo log files how hard it will be to change the inaudible log file size and how large you can set it to depends greatly on the version of MySQL or percona or Murray DB server that you are using specifically if you are using version prior to 5.6 you can't simply change the variable and expect that the server will restart in fact it will stop but won't start funny I know so here's how you have to do it one change in a DB log file size in my CNF to stop my SQL Server 3 ensure MySQL had a clean shutdown for remove old log files 5 start my SQL Server it should take a bit longer to start because it is going to be creating new transaction log files final thing you should be aware of is that until quite recently that is until my ass called version 5.6 dot 2 at the total redo log size across all redo log files was limited to 4 gigabytes which was quite a significant performance bottleneck for right intensive SSD backed MySQL servers proko no server on the other hand supports 512 gigabytes since like percona server 500 something in other words before you set inaudible log file size to 2 gigabytes or more check if the version of MySQL you are running actually supports it inaudible flush log at transaction commit by default in ODB flash log a transaction commit is set to one which instructs in a DB to flush and sink after every transaction commit and if you are running auto commit then every single insert update or delete statement is a transaction commit sync is an expensive operation especially when you don't have a non-volatile write back cache as it involves the actual synchronous physical write to the disk so whenever possible I would recommend to avoid using this default configuration to alternative values for this variable are 0 and 2 0 means flush to disk but do not sing no actual IO is performed on commit to means don't flush and don't sing again no actual IO is performed on commit so if you have it set to 0 or to sync is performed once a second instead and the obvious disadvantage is that you may lose last-second worth of committed data yes you read that right those transactions would have committed but if the server loses power those changes never happened obviously for financial institutions such as banks it's a huge no-go most websites however can and do run with in a DB flush log a transaction commit equals 0 or 2 and have no issues even if the server crashes eventually after all just a few years ago many websites were using my eyes M which will lose up to 30 seconds worth of written data in case of crash not to mention the crazy slow table repair process finally what's the practical difference between 0 & 2 well performance wise the difference is negligible really because F flush to os cache is cheap read fast so it kind of makes sense to have it set to zero in which case if MySQL but not the whole machine crashes you do not lose any data as it will be in OS cache and sync to disk from there eventually by the way if you prefer durability over performance and have in Adobe Flash log at transaction commit set to one let me draw your attention to the next variable which is closely related sink bin log a lot has been written about sin bin log and its relationship with InnoDB flash log at transaction commit but let me simplify it for you for now if your mascot server has no slaves and you don't do backups set sing pin lock to 0 and be happy with a good performance if you do have slaves and you do backups but you don't mind losing a few events from the binary log in case of a master crash you may still want to use seeing bin log 0 for the sake of a better performance if you do have slaves and our backups and you do care about slaves consistency and our point in time recovery ability to restore your database to a specific point in time by using your latest consistent backups and binary locks and you are also running in Adobe Flash log at transaction commit equals 1 then you should seriously consider using sync bin log equals 1 problem is of course that sync bin log equals 1 has a pretty significant price now every single transaction is again synced to disk to the binary logs you'd think why not do both sync operations at once and you'd be right new versions of MySQL both 5.6 and 5.7 MariaDB and percona server already have a properly working group commit in which case the price for this sync bin log equals 1 is not that big but older versions of MySQL have a really significant performance penalty so be careful with that and watch your disk right so far so good good next in a DB flash method set in ODB flash method to all direct to avoid double buffering the only case you should not use all direct is when it is not supported by your operating system but if you are on Linux use all direct to enable direct IO direct IO means that inner DBS read and write calls are bypassing OS cache and are going directly to the i/o scheduler to get sent to the disks without direct IO double buffering happens because all database changes are first written to us cache and then sync to disk so you end up with the same data in inner DB buffer pool and in OS cache yes that means in the right intensive environment you could be losing up to almost 50 percent of memory especially if your buffer pool is capped at 50 percent of total memory and if not server could end up swapping due to high pressure on the OS cache in other words do set in a DB flash method equals all direct please in a DB buffer pool instances MySQL 5.5 introduced buffer pool instances as a means to reduce internal locking contention and improve my SQL scalability in version 5.5 this was known to improve the throughput to some degree only however my skull 5.6 was a big step up so while in mysql 5.5 you may want to be more conservative and have energy be buffer pool instances equals 4 on mysql 5.6 and 5.7 you may go with 18 to 16 buffer pool instances your mileage may vary of course but with most workloads that should give you best results oh and obviously do not expect this to make any difference to a single query response time the difference will only show with highly concurrent workloads that is those with many threads doing many things in my scale at the same time in a DB thread concurrency you may hear very often that you should just set inner DB thread concurrency equals 0 and forget it well that's only true if you have a light or moderate workload however if you're approaching the saturation point of your CPU or i/o subsystem and especially if you have occasional spikes when the system needs to operate properly when overloaded then I would highly recommend to tackle in a DB thread concurrency here's the thing inner DB has a way to control how many threads are executing in parallel let's call it a concurrency control mechanism and for the most part it is controlled by inner DB thread concurrency if you set it to zero concurrency control is off therefore nodb will be processing all requests immediately as they come in and as many as it needs to that is fine if you have thirty-two CPU cores and four requests but imagine you have four CPU cores and thirty-two CPU intensive requests if you let the thirty-two requests run in parallel you're asking for trouble because these 30 requests will only have four CPU cores they will obviously be at least eight times slower than usually in reality more than eight times slower of course but each of those requests will have its own external and internal logs which leaves great opportunities for all the queries to pile up to solve that you can control how many threads in a DB allows to execute at any given point in time other threads wait in a line up queue but it's not as simple first in first out queue it's much more interesting than that here's how it works a thread entering an inner DB Q is given a certain number of tickets equal to the value of inner DB concurrency tickets 500 by default on mysql 5.5 or earlier 5000 starting Maskell 5.6 then it waits in a queue until a slot becomes available for it finally it starts executing and every time the thread does a certain operation it loses one ticket and goes through a check for the number of remaining tickets for example a thread would lose a ticket every time a row is read inserted or updated and it would lose 300 tickets for a single read of 300 drugs now here's where it gets interesting as soon as the thread runs out of tickets that is the ticket count the creases to zero the thread is placed at the back of the queue and needs to wait until the Lott becomes available again assuming the current number of running threads is above the value of energy B thread concurrency that way the number of queries that are executing at any given point is limited yet long-running queries don't prevent quick queries from entering a queue for a very long time so you see we already have two variables in a DB thread concurrency which controls how many threads are allowed to run at the same time and in a DB concurrency tickets which controls how many tickets a thread is given every time it re-enters the queue there's one more variable related to this in a DB thread sleep delay which has a default value of 10,000 microseconds this sets how long you need to be thread sleep before joining the inner DBQ thus controlling the concurrency to a certain degree if you want to change the value for inner DB thread concurrency you can do it online by running the following command for most workloads and servers 8 is the good start and then you should only increase it gradually if you see the server keeps hitting the limit while hardware is underutilized to see where the threads stand at any given moment run show engine in a DB status and look for similar line in the row operations section both inner DB concurrency tickets and inner DB thread sleep delay can also be changed online if you'd like to try out different configuration for these Skip name resolve this is a funny one but I couldn't not mention it as it's still quite common to see it not being used essentially you want to add skip named resolved to avoid dns resolution on connection most of the time you will feel no impact when you change this because most of the time DNS servers work they work well and they also tend to cache results but when a DNS server will fail it could be really time consuming to figure out what are those unknown then' ticket connections doing on your server and why thinks all of the suddens seem slow so don't wait until this happens to you add this variable now and get rid of any host name based grants the only exception here is if you're using Haas file based name resolution or if your DNS servers will never fail in our DB i/o capacity and in a DB i/o capacity max here's what these two io capacity settings control in a nutshell in a DB i/o capacity controls how many right IO requests per second will Moscow do one flashing the dirty data in a DB i/o capacity max controls how many right IO operations per second will mask you'll do flushing the dirty data when it's under stress so first of all this has nothing to do with foreground reads once performed by select queries for reads Moscow will do the maximum number of i/o operations per second possible to return the results as soon as possible as for right maskull has background flushing cycles and during each cycle it checks how much data needs to be flushed and it will use no more than ina DB i/o capacity i/o operations per second to do the flushing that also includes change buffer merges change buffer is where secondary keys dirty pages are stored until they have flushed to disk second I need to clarify what under stress means this what maskull calls an emergency is a situation while MySQL is behind with flushing and it needs to flush some data in order to allow for new writes to come in then Moscow will use the inner DB i/o capacity max amount of i/o operations per second now here's the $100 question so what do you set these two best solution measure random write throughput of your storage and set inner DB i/o capacity max to the maximum you could achieve and in a DB our capacity to 50 to 75 percent of it especially if your system is right intensive often you can predict how many i/o operations your system can do especially if it has genetic drives for example eight 15k disks in raids ten can do about 1,000 random right IO operations per second so you could have inner DB i/o capacity set to 600 and inner DB i/o capacity max to 1,000 many cheap enterprise SSDs can do four thousand to ten thousand IO operations per second nothing bad will happen if you don't make it perfect but be worried that the default values of 200 and 400 respectively could be limiting you're right through it and consequently you may have occasional stalls for the flushing activity to catch up if that is the case you are either saturating your disks or you don't have high enough values for these variables you know to be stats on meta data if you're running Moscow 5.6 or 5.7 and you didn't change the default energy stats on metadata value you're all set on MySQL 5.5 or 5.1 however I highly recommend to turn this off that way commands like show table status and queries against information schema will be instantaneous instead of taking seconds to run and causing additional disk IO my former colleague Stephan come down from percona has written a very good blog post on this you can find a link to this blog post in the notes oh and note that starting with 5.1 dot 3 2 this is a dynamic variable so you don't need to restart MySQL just to disable that in a DB buffer pool dumped at shutdown and in a DB buffer pool load at startup it may seem the two variables are not really performance related but if you are occasionally restarting your MySQL server example to apply configuration changes they are when both are enabled the contents of mysql buffer pool more specifically cached pages are stored into a file upon shutdown and when you start moscow it starts a background thread that loads back the contents of buffer pool and improves the warm-up speed that way up to 3 to 5 times couple of things first it doesn't actually copy the contents of the buffer pool into a file up and shutdown it merely copies this tablespace IDs and page IDs enough information to locate the page on disk then it can load these pages really fast with large sequential reads instead of hundreds of thousands of small random reads second loading of the contents on startup happens in the background hence MySQL doesn't wait until the buffer pool contents are loaded and starts serving requests immediately so it's not as intrusive as it may seem third starting with MySQL 5.7 dot seven only 25% of least recently used buffer pool pages are dumped on shutdown but you have a total control over that use in your DB buffer pool dump PCT to control how many pages expressed in percents would you like to be dumped and restored I about 75 to 100 and forth while MySQL only supports this since my skull 5.6 in pecan a server you had this for quite a while now so if you want to stay on version 5.1 or 5.5 or if you are already using perc on a server check these two links that you will find in the notes as well in addy be adaptive hash index part if you're running a lot of select queries and everything else is perfectly in tune then adaptive hash index is going to be your next bottleneck adaptive hash indexes are dynamic indexes maintained internally by inner DB that improve performance for your most commonly used query patterns this feature can be turned off with a server restart but by default it is on and in all versions of MySQL in most cases it gives a nice boost to many types of Quays that is until you have too many creating the database at which point they start spending too much time waiting on the IHI locks and latches if you're on my skull 5.7 you won't have any issues with that you know DB adaptive hash index parts variable is there and it's set to eight by default so the adaptive hash index is split into eight partitions therefore there's no global mutex and you're good to go all my SQL version before 5.7 unfortunately have no control over the number of adaptive hash index partitions in other words there's one global mutex protecting the adaptive hash index and with many select queries you are constantly hitting this wall so if you're running 5.5 or 5.6 and you have thousands of select queries per second the easiest solution would be to switch to the same version of perc on a server and enable adaptive hash index partitions the variable in perc on a server is called energy be adaptive hash index partitions query cache type a lot of people think that query cache is great and you should definitely use it well sometimes it's true sometimes it is useful but it's only useful when you have a relatively light workload and especially if the workload is pretty much read-only with very few or virtually no rights if that's your workload set query cache type equals on and query cache size equals 256 megabytes and you're done note however you should never set the query cache size any higher or you will run into serious server stalls due to query cache invalidation I've seen this happen too many times and until someone figures out a way to split the global query cache mutex this will not go away now if you have an intensive workload then I would highly recommend this query cache size tuner written by one of very active Maskull community members damas meters us more seriously though you should not only set the query cache size equals zero but also it's very important that you set query cache type equals off and we start the server when you do that this way MySQL will stop using the query cache matrix for all requests even those that wouldn't use the query cache anyway by the way this one with Moscow 5.5 or newer you can't really disable the query cache mutex in the earlier versions of MySQL in percona server you can also disable it in version 5.1 so if you're still using the query cache and you shouldn't make these changes now because your queries are likely already suffering due to the query cache mutex contention in a DB checksum algorithm most mainstream CPUs nowadays support native crc32 instructions and MySQL can finally make use of that to improve the speed of calculating the inner be checksums significantly to enable that set the inner DB checksum algorithm equals crc32 this is available since Maskell 5.6 starting with moscow v dot 7.7 this is set by default check sums are calculated every single time a page or log entry is read or written so this is definitely huge oh and by the way this is totally safe to change on a server that already has tables created with checksum type in ODB in fact you can change it dynamically online while the server is still running table open cache instances starting with mysql 5 6 6 table cache can be split into multiple partitions and if you're running mysql 5.6 or newer you should definitely do that table cache is where the list of currently opened tables is stored and the mutex is locked whenever a table is opened or closed and in fact in a number of other cases even if that's an implicit temporary table and using multiple partitions definitely reduces potential contention here I've seen this lock open mutex issue in the wild too many times starting with Maskell 5 7 8 table open cache instances equals 16 is the default configuration and I'd say it is a definitely good starting point both on MySQL 5 6 & 5 7 in a DB read our threads and inner to be right I our threads I've placed this last because it's really not as important as it may seem first of all your MySQL is likely already using asynchronous i/o which on linux is supported since mysql 5.5 second both inner DB rely on threats and InnoDB right ir threats are only used for the background activity such as check pointing flashing dirty pages to disk change buffer merge operations and sometimes read I had activity so while it's not a key variable to tune aligning it to the number of bearing disks is still a good idea so for example on rate ten with eight disks you may want to have in will be read I or threads equals eight and you need to be right io threads equals four if you have an SSD well then just have it around 16 to 32 but do not expect much performance difference unless your server is extremely write heavy and this kayo is the bottleneck and we're done with the variables I wanted to make all these configuration variables as accessible as possible so I made a special my say enough with this recommended configuration and useful comments that will help you fine-tune it for your tastes visit speed eMERCOM slash 17 to download them it's quite amazing how much you can improve things with small changes over the vanilla my scope configuration but you want to know the real truth the truth is we haven't even started yes improving my SQL configuration will impact its performance greatly and it is surely the foundation of a well performing MySQL server however if you have a server that is otherwise tuned pretty well you shouldn't expect a few configuration changes to improve things drastically most of the time the real devil is in the queries occasionally I am running free webinars on query optimization and similar topics where you can learn more in depth about MySQL performance optimization and get your questions answered go to speedy me comm slash webinars to see a list of upcoming webinars and sign up participation is free of charge and you can watch a recording later if you sign up now let's move on to the final bonus part of this video in the early days of MySQL MySQL status was pretty much the only way to look at my ASCO performance therefore it was a pretty common practice among MySQL consultants and support engineers to look at MySQL status to understand what's happening with the server and even though now there are better ways to look at specific issues for example analyze query performance using extended sloper login perk on a server or performance schema starting with mysql 5.6 mysql status is still a good way to look at general metrics of the server however it is really important to look at it correctly which is what I want to show you now in this quick lesson I'm going to show you how to look at my SQL status the right way we will identify few key status variables to look at and you will learn what free tools you can use to make monitoring maskull status much easier how to look at mysql status first let me show you what is the wrong way to look at my SQL status run show global status in my SQL console or my SQL admin next in the command line and you will be overwhelmed with huge numbers these commands will show you the current count for each status variable oh and bear in mind that not all status variables are counters some are gauges displaying current value you will have to look at these separately so the right way to look at status variables is over a certain period of time for example if you run the following command you will get a total count for each counter now followed by a total counter sixty seconds later - C specifies how many iterations to show hence in this case counters will be displayed three times now this is still not very useful because for most variables you are interested in Delta rather than totals and I will show you two ways to get relative numbers one add - air to mask al admin and that will show you Delta right away to another more elegant way to get the same data is by using PT mixed although I should admit I never use it that way instead I would first write mask element output into a file and then I would read that file with PT mixed this allows me to get a different cut for the same data in Maskull admin dot txt file for example I can collect hundred samples every one second and then analyze the file with grep to look at gauges and use PT mixed to analyze counters key MySQL status variables to look at now let me list a few variables that are interesting to look at beware that we are focusing on performance only so we will not look at all 400 status counters I always look at these to understand the workload that server is currently dealing with this will give you an idea of how many commands per second the server is running at any given point so you can correlate these to any other counters temporary tables it's always a good idea to see how many temporary tables you have on disk vs. total number of temporary tables so you have to look at the following variables note that very often the reason on these temporary tables are created is not a too small temporary table size rather it's variable size columns that are used in the queries for example text or blob columns that can't be used in the fixed size temporary tables for these temporary table on disk will be used even if the temporary table will contain a single record otherwise many temporary tables can be avoided by reviewing the queries that create them and fixing in efficient execution plans by either indexes or changes in the queries handler counters these are internal operation counters often accounting for every single record access the most interesting ones you want to look at are the following ones for what they stand for please have a look at Maskell manual over here however these counters are much more interesting to look at when you're analyzing a behavior of a specific way for example you want to use session status counters in the following fashion this will give you a much better understanding of what a specific query is doing internally and potentially how you can optimize it in ODB counters NDB has plenty of counters to look at additionally you can get a whole lot deeper with Show engine in a DB status command here's the most typical things I would look at and why in a DB buffer pool page is flushed this shows number of pages flushed from buffer pool which is a good way to monitor flushing activity in a DB buffer pool reads number of disk i/o calls to read into the inner DB buffer pool see how close that is - how many random reads your disks can actually deliver and compare that to this number in a DB data F sings number of F sync calls executed see if it's not too high for your hardware specifically look at how many write operations can your disks do in a DB data pending these are gauges showing a number of pending F sync read or write calls and these could potentially show saturated IO resources in the DB data reads writes a number of random read/write disk i/o operations for data files specifically in a DB history Liz Lange gauge showing a number of transactions that haven't been cleaned up after in a TBI both merges number of insert buffer merge operations hi numbers here could explain intense iOS Pike's inner DB log Waits number of times log buffer was too small it's a good indicator inner DB log buffer size needs to be increased inner DB LS n current number of bytes written to the transaction log this helps you tune in a DB redo log files in addy be mutex OS waits if this is high you could be having internal mutex contention and by hi I mean that if it's few hundred or even close to 100 inner DB rows helps you understand internal activity number of rows read inserted deleted or updated because not always one insert will be inserting a single record in a DB rowlock time shows how much time is spent on logical locks note that some of these variables are only available in Percona server if you can't find it on your server check show engine inner DB status it's not as convenient but that's where you will find everything about energy operation now open stuff check these to understand if your file caches are sized decently ideally open tables and open table definitions should not be increasing much or at all gray cache you can monitor the query cache activity by looking at Q cache counters most important is the pair cue cash it's to come select counter to understand how many select queries out of total are served from the query cache although queue cashflow man prunes and queue cache inserts can be just as interesting to understand note however that even if you do CLO mem prunes do not increase the size of the query cache above 256 megabytes select counters there's a few interesting select counters to look at here's what they stand for select full join this shows a number of queries that made a table scan during a joint even if joint buffer was used these could be pretty bad queries in some cases but sometimes it's also tiny tables that are very fast to join anyway select full range join this shows number of joints that used a range search on a reference table these aren't common but they aren't always harmful either select range this is a very common range access pattern used by queries and it's for your information only select range check number of joints without keys with additional key usage check each time row is read good indicator of bad indexing rarely seen in the wild Oh select scan table scan on the first or the only table in the joint also shows bad indexing so if either select full joint select range check or select scan is relatively high chances are you have a pretty bad indexing and I highly recommend doing a query analysis then threads counters one of these counters is the main indicator of any performance issues in Maskull and I use it in my troubleshooting practice all the time here's what the threads counters show threads cached this is a gauge showing a current number of cached threads not super interesting threads connected this shows a number of threads currently connected to the server a lot of these could be sleeping threads and sleeping threads are very cheap to have so don't mind these too much either threads created this is a counter showing a number of threads created because the number of cash threads wasn't enough if this is high consider increasing the thread cash size and threads running finally this is the most interesting counter of them all it is a gauge showing a number of threads that are currently executing inside MySQL they could be doing anything from waiting in the inner DBQ to committing data to disk but the key thing is that if this number is occasionally abnormally high that is larger than the number of CPU cores or disk spindles that you have you are most likely having pretty serious Maskell performance issue see the following blog post to learn how you can deal with these finally let's look at some free tools that will make your life better when it comes to MySQL status monitoring cacti and zabbix if you're not using either of two I would highly recommend to give it a try they're both free however they are not useful for MySQL monitoring in itself you need to add some additional templates Percona has a really nice package for that PT max I have already mentioned PT max what I did not mention however is how you can get it well you can either install percona toolkit which is now included in a number of distributions - or you can download PT mixed as a stand-alone command-line utility which is what I often do when doing a consulting gig in a top it's a top like command line tool for monitoring MySQL status with some emphasis on InnoDB it is a really comprehensive tool and I highly recommend to check it out that's it for now thank you very much for watching if you found this useful make sure to subscribe as I have more masculine videos and tutorials coming oh and don't forget to share it with your community this was Armas at speed Emmie calm

Channel: Speedemy

Views: 95,424

Rating: 4.9380074 out of 5

Keywords: mysql, performance, tuning, my.cnf, configuration, percona, server, mariadb, mysql 5.7, webscalesql, innodb

Id: 0CqMv0ucqFA

Length: 82min 58sec (4978 seconds)

Published: Tue Mar 29 2016