Capacity metrics in daily MySQL checks

Video in TIB AV-Portal: Capacity metrics in daily MySQL checks

Formal Metadata

Capacity metrics in daily MySQL checks
Alternative Title
Mysql And Friends - Capacity Metrics
Title of Series
CC Attribution 2.0 Belgium:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Release Date
Production Year

Content Metadata

Subject Area
Group action Metric system System call Structural load Multiplication sign Insertion loss Bit rate Mereology Information technology consulting Optical disc drive Sign (mathematics) Mathematics Bit rate Blog Radio-frequency identification Befehlsprozessor Physical system Boss Corporation Structural load Bit Instance (computer science) Twitter Connected space Process (computing) Befehlsprozessor Website Right angle Energy level Ideal (ethics) Metric system Physical system Reading (process) Writing Point (geometry) Service (economics) Chemical equation Channel capacity Workload Goodness of fit Cache (computing) Operator (mathematics) Utility software Statement (computer science) MiniDisc Boss Corporation Graph (mathematics) Coma Berenices Computer network Database Line (geometry) System call Word Query language Personal digital assistant
Metric system Structural load Multiplication sign Channel capacity 1 (number) Online help Insertion loss Parameter (computer programming) Discrete element method Number Product (business) Revision control Cache (computing) Bit rate Deadlock Different (Kate Ryan album) Operator (mathematics) Ideal (ethics) Statement (computer science) Scaling (geometry) Block (periodic table) Weight Coma Berenices Database Cartesian coordinate system Cache (computing) Software Query language Personal digital assistant MiniDisc Right angle Ideal (ethics) Table (information) Pressure Metric system
Server (computing) Metric system Thread (computing) Table (information) Computer file Firewall (computing) Multiplication sign Channel capacity Port scanner Client (computing) Vector potential Number Product (business) Order (biology) Goodness of fit Blog Average Semiconductor memory Selectivity (electronic) MiniDisc Physical system Point cloud Information Server (computing) Graph (mathematics) Stress (mechanics) Electronic mailing list Computer network Coma Berenices Basis <Mathematik> Connected space Subject indexing Software Query language Personal digital assistant Network topology Order (biology) Interface (computing) MiniDisc Right angle Ideal (ethics) Metric system Table (information) Physical system Stability theory Row (database)
Scripting language Web page Slide rule Scripting language Metric system Information Feedback Forcing (mathematics) Channel capacity Coma Berenices Basis <Mathematik> Inverse element Discounts and allowances Video game Normal (geometry) Metropolitan area network Physical system
Area Metric system Channel capacity Coma Berenices
you so will
be talking about how to how to measure my skill performance in a really easy way so as not to in deeply just to just to to get some signs of bad things that can happen with my skills which happen all the time and a let's was just need each other a line in his lab so I do my skill for a long long time and performance consulting do some operations were part of the customers and the it's it's really it's really amazing how way sometimes you can prevent some bad things just just look inside a little bit so on but if it happens it happens for all of so good when it happens like when you receive a lot in the middle of the nite from 1 of from from major duty it's it's a good way it's a good way of little Westway way when when you bosses calling in the middle of the nite or removal day when you're at the beach or were whatever you whatever you are um and the worst case when the customer calls you and tell OK my site is not my website is not working for like 3 hours and you don't it you don't really want to go 1 better at all times you just want to avoid these things the sole are how to how to do that and just by the way how how many of you didn't get it get the calls from the boss so you know and I can about well and it's it's a question how to how to structure you work with the with my skill you can be you can be reactive or you can be proactive you can react with a certain to all odds that you receive war you just can indeed in deeply little bits of the by date to do to just to just to see how our how my skill acting out how dataset grow up so you will not get any issues like out of the clean you still can get issues but we will look into way to prevent the so all of my skill has a lot of metrics that you can you can take a look on and there is no there there are lots of metrics on the system size and let's just well basically you can you can log in and see how how how how much abuse utilized how much I value is utilized you probably have some graphing tools of how many of you have cacti work or graphite or or something like this just take a lot of guys take a look how many hands do that do it actually because it's it's a good thing you the idea how my skill action wire 0 you know it's not working it's a it's a really good stuff when you see OK there a spike I spike in the morning probably related to micron job that around so I probably need to do to fix that or move it to the some other no word just just I don't know just remove the problem from job was replaced by a by something else problem with those of graphene and and stuff is doesn't give you any idea of the if it's or or group like you have 10 % CPU utilization is it good well probably because of severe loss at its session saturated but are the same time you may have everything just walked out and nothing more so because of because a lot in place and that's because the CPU can be used so that you need to looking really deeply into into what's going on in my skills site and so let's just see how what what my skill has in in in this in this matter so what we probably need to understand the general load my skill instance would probably want to see it understand our read write rate so how how much we rights are all right into the in into the database how much we we can we can we actually moved out from my skilled at some point just to just replace with some of the better better technology for it for it to your current workload I utilization is a key if you if you have meiosis rated yourself Colombian to do anything to certain queries alloxan way scanning can give a lot of pain and of connectivity and connectivity issues so comes up for unexpectedly very rare but unexpectedly and of course it's queries queries are all in in all databases this and it's not my skill which is slow it's it's a queries it I I know the people telling this all the time so what about the genera load you probably want to know how how much queries my skills service per 2nd and where it went when is peak time when is a slow time because on the slow time you can you can perform some changes you can to some of some operations without interfering with the with
the with high production traffic uh read write rate you can you can help you to understand how to to my skill properly and that went to went what went to scale when it when it's time to scale of it also can tell all of that so what we your query caches is used is not working anymore and you probably need to I need to do some of more cash in on the application side in and end of the more you write the more I O saturated so you you probably will will will need to know these numbers just just as a the idea pressure a there is a very few well there's a lot of there are lots of them metrics on my skill side that related to I O pressure but let's just a little we don't have much time so let's just talk about the really important ones are handler region are is is about come is about to move is related to random rates of of mostly on the broke my at my eyes affected as well I don't really remember about finding the stuff I think I think you guys will help me about them we got rid the the idea yeah but it's it's were so for my eyes and but for IT-TB it's alluded to live the different so on for how about how much data I need your rights can be used to 2 parameters can show you like uh I needed that data rights and I did and indeed to meet up there it is there it to others reading and read a which is the first one the 1st 1st 2 is about how much I O requests done so you need to to read and write data and to another to is how it is it is a size 2 you you probably need to focus on the ideal of number of I O operations because it's it's especially if you were taking if you don't use this is deep can be a problem of like really 8 any any of the usual usual this can can can serve you like what yeah yeah I love I love the guy so you usually need to to focus on the on the dialog of number used per 2nd just because it's so it's vital if you use this is the it's a more relaxed but the usual usual our disk cancer like 200 so I 0 per 2nd no more and you can easily start here I did another another things is how how I need to be log what works and how much a lot operations per 2nd so you utilize its its I O operations and it's number or by operations as well but now we go into the locks and also really painful we just we just had a had a situation when the database what went down because of the deadlock so of the and the I need to be a lot of low weights I think it's a I'm not this is a mistake it's about ideal how how it how many of you know how many and how how many way how many times my skill waiting on the on or on the log until all locus flushed so if you see a non non-zero value here you probably need to to fix that it's it's it's about it's about science of immediate blocks is still OK is silicates trying to all my still trying to walk table because of the difference of different reasons especially for my eyes and for my eyes and I every or right of our rights on the table every update every insert so every insert or delete will will walk the walk table but for IT-TB a walking you should not be shouldn't use is not very easy you need to alter table 2 with 2 with a walk walk it's and the new version is not every alter perform in the complete table or but but you can still you can still get get Table locked and an operational and this is really bad but in this case to to to see to see more details for us about I interview walks at especially deadlocks you need to to have you need to look at in the show engine I need to be status it will it will show you recent deadlocks and it will give you idea which table so affected and so on but that networking is
rarely a problem so most of the time someone said OK this is the network issue of list was just file of files supporting gets and into the network it's probably not true but network is really really stable but you still need to check it to make sure that everything is fine like our my skill has several several network related to she several related metrics like how many true from through so how many connections do we do we have like much these connections or just can connections on can't connection to the connections to my skill and you probably want to to to look on the on those graphs on a daily basis of stress created to give you give you an idea of how many the by use the rest how how many users connected and I kill has to create a threat but to serve it so if you have been the a big number here you probably need to to increase thread pool of threat catch on the on my skill side of sometimes you you can you want to see how much how much to can clients has been has been rejected sometimes it's OK like here you don't you do not close connection properly and the way timeout expired ended my skill just closing connection on on its side sometimes it's it's it's not that good especially for us will ask when users are not of indicated for dedicated cost hosting if you don't have firewalls set up properly you can you can get some more unwanted visitors and this is this is a good uh it's a good thing to to check to check it well not that not every day that still the and the most of the other issues far query weighted sold those those things that we would talk about is just general general our metrics of my skill sometimes you can say OK it's like average temperature in hospital and you you you will be you have the right but it's still the idea how how my skill actin insight of 4 of the queries you can you can narrow down and we should pretty much of Britain much instantly and
it will it will give you a lot of information like if you have uh there's select scan metric in my hillside if you see that the number of per 2nd of you if a small nonzero this is the main probably the main reason for you to offer you my skill server because this is a full-scale full table scan when you have small dataset it's OK it's very fast it's everything is in memory but it is data grow you can sometimes just have my still stuck on on on skinful table and happens because of you can use you you you can do this index on the table you need to create it and will be much better sometimes your query and created just not properly like sometimes use my skill can't can't handle little queries like this properly maybe maybe you use something like like this like infix infix index and is not supported by by my skill you you can only with the tree you can only use prefixes and then star of sometimes it's is and this is my favorite this is my favorite the end of this is a really nasty thing because operand will be calculated for each data for each row in a table and then my skill will start the whole dataset this is really good query if you want to kill my skill especially if you have several terabytes just just just right 111 production of and production I want to be responsible for this and even more so that if you have lot non-zero select full joint which means that you have joined and the 2nd table has full table scan for every row in the 1st table and the good thing is you probably you will not is this just just a right away because everything will be stopped and when the user stop complaining of really fast to put a table is another nasty things and I have but I'm running out of time how they can they can be they can be you this would be here and created because of several features like you if you use grew by a subqueries diffuse this thing plus order by in good case in that case and everything will be memory in the worst case it will go to the disk and then it will be really really really slow so all of generally metrics so that metrics are not enough you need to to have to take a look on the on queries and the look in world just summarizing that stuff well we we need to desist system reviews we need to do health checks like only of all on a daily basis if it's if it's not daily basis is at least on a weekly you need to know your system well you need to know your system better than my scaled us in this case in that case you will you will not hate any any issues unexpectedly and well walking through I I I do my skill culture from all the time is really worth is like look on this graph from from these were that's OK that's OK that's OK and I think it's really into it takes time so are their
tools to 1 to automated I I know that for common guys created a nice so nice to have that can show you most of the all most of the information you need on a daily basis I know a of the cortex provides provides advanced and it's the advanced statistics for for my school stuff and but they you don't need it it requires to install something on your system and sends that out for myself I created for poor man health check script that collects all the information like once a day and sends me e-mails a with
old information I'm not saying if you should use it or you should try that there are lots of tools that already exists it I just I just created for myself and the yeah so far I like it you can you can go to the top down and take a look at it if you like so what else to do the main thing is to know how my skill actin and you you want to want to buy to by good by that create life of Procomm the guys and inverse force it what it's worth it it's really really big but it's worth it every every single page I we have there is my skill troubleshooting book by status normal and she's here airports count yeah end of and finally you can't just walk through the slides and can take a look at compare what what what you might still shows to make sure everything is fine yeah thanks
thanks a lot any questions the and OK if you shoeshine about asking me questions about your your brightest and installation you can do it later like out of the room thank you what yeah the the
the and the last it so what we call the last let's let's go let's go fight the that let's have a white area the uncanny and and you know how to