Tracing PostgreSQL performance

Video in TIB AV-Portal: Tracing PostgreSQL performance

Formal Metadata

Tracing PostgreSQL performance
Title of Series
Number of Parts
CC Attribution - ShareAlike 3.0 Unported:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal and non-commercial purpose as long as the work is attributed to the author in the manner specified by the author or licensor and the work or content is shared also in adapted form only under the conditions of this license.
Release Date
Production Place
Ottawa, Canada

Content Metadata

Subject Area
Word Process (computing) Computer animation State of matter Weight Bit Database Musical ensemble
Point (geometry) Dependent and independent variables Mapping Information Multiplication sign Electronic mailing list Similarity (geometry) Database Complete metric space Rule of inference Event horizon Field (computer science) Computer programming Response time (technology) Computer animation Different (Kate Ryan album) Term (mathematics) Summierbarkeit Physical system
Point (geometry) Multiplication sign Weight Interface (computing) Sheaf (mathematics) Database Measurement Computer programming Wave packet Connected space Response time (technology) Computer animation Bridging (networking) Quicksort
Computer animation Bit Cycle (graph theory)
Point (geometry) Sequel Variety (linguistics) Multiplication sign Weight Interface (computing) Workstation <Musikinstrument> Database Mereology Event horizon Response time (technology) Computer animation Personal digital assistant Different (Kate Ryan album) Right angle Software testing Quicksort Figurate number Traffic reporting Oracle Task (computing) Row (database)
Purchasing Area Time zone Information Multiplication sign Weight Content (media) Database Mereology Event horizon Approximation 2 (number) Product (business) Computer animation Different (Kate Ryan album) Buffer solution Computer hardware Video game Website Cuboid Software testing Figurate number Oracle
Point (geometry) Slide rule Link (knot theory) Robot Multiplication sign Insertion loss Checklist Event horizon Element (mathematics) Product (business) Revision control Bridging (networking) Profil (magazine) File system Cuboid Software testing Enterprise architecture Weight Interface (computing) Digitizing Reflection (mathematics) Shared memory Database Kernel (computing) Computer animation Personal digital assistant Duality (mathematics) Statement (computer science) Configuration space Window
Curve Statistics Group action Multiplication sign Direction (geometry) Sampling (statistics) Division (mathematics) Cartesian coordinate system System call Revision control Kernel (computing) Computer animation Phase transition MiniDisc Right angle Utility software Object (grammar) Resultant Position operator Form (programming) Spacetime Stability theory Physical system
Adventure game Programmer (hardware) Functional (mathematics) Computer animation Multiplication sign Chain Green's function Summierbarkeit Raw image format Traffic reporting Position operator Physical system
Arithmetic mean Kernel (computing) Computer animation Computer file Variety (linguistics) Software developer Tracing (software) Task (computing) Physical system
Perfect group Plastikkarte Database System call Event horizon Tracing (software) Revision control Computer animation Integrated development environment Internet service provider Office suite Information security Resultant
Point (geometry) Complex (psychology) Pattern recognition Dynamical system Overhead (computing) Software developer View (database) Multiplication sign Weight Interface (computing) Sheaf (mathematics) Shared memory Parameter (computer programming) Mereology Timestamp Symbol table Computer animation Office suite Information security Traffic reporting Resource allocation Resultant Spacetime Task (computing)
Dynamical system Overhead (computing) Multiplication sign Interface (computing) Weight Shared memory Data storage device Sound effect Database Library catalog Mereology Response time (technology) Computer animation Different (Kate Ryan album) Video game Energy level Summierbarkeit Logic gate Local ring Oracle
Point (geometry) Functional (mathematics) Information Latin square Multiplication sign Moment (mathematics) Database Parameter (computer programming) Mereology Field (computer science) Arithmetic mean Computer animation Video game Integer Service-oriented architecture Position operator Row (database)
Point (geometry) Variety (linguistics) View (database) Multiplication sign Decision theory Function (mathematics) Login Mereology Event horizon Mechanism design Roundness (object) Different (Kate Ryan album) Semiconductor memory Fiber (mathematics) Physical system Computer architecture Information Weight Gradient Sampling (statistics) Shared memory Measurement Radical (chemistry) Type theory Process (computing) Computer animation Personal digital assistant Buffer solution Right angle Service-oriented architecture Resultant
Area Shift operator Cross-platform Cellular automaton Multiplication sign Physical law Bit Attractor Computer programming Revision control Mixture model Computer animation Right angle Resultant
this talk just a couple of words about the problem exists and then I try to talk a bit about how it works and the commercial databases because it is exists and that and we should be aware of how works just a bit about i've individual but most terrible told about Oracle weight interfaces band atom deposit rests with current state of the history of attempts to implement something like that involves processes and they existed tools and then some agenda what's next so the goal of my
talk I actually to explain that a problem exists then explain that what already we can do that with this and suggest some kind of fraud map cultural things actually of these some recipes for debate will be very brief because we're limited to the for the 5 minutes and I decided to prepare a separate talk with a complete list of recipients and submitted to the sum of the upcoming conferences because it's really a huge topic to cover a whole different system that performs an hour how you can safely use it with your database and the final 4 rules is to summarize all this information to keep it in 1 place and finally submit the hardest to make things better so what's this tracing
can what's so about of the problem is that for a customer who also Philippa doesn't matter of the problem that the database is usually the response time and usually the use of it should doesn't care what happens on inside the database thought to solve the problem did they actually must know what exactly happens inside and how much time it takes some stage of these full response for example which have some long sequential scan on something like that something like that for that purpose did they must have some tools tool profiling or tracing of their database what is the difference basically between these 2 terms imagine we have some program execution of full and we have some points in which we can measure for example time and so are we just refining using some tool to measure which all this points to take the most time but the tracing is a bit of similar but the difference with tracing some event cures and we can measure at which time was taken by this event and this is actually the the both approaches are quite useful but the tracing is actually very useful for DBH will improve vitamins performance and of course we need their historical data for tracing can provide and because the usual our question from customer or from the field is something a curator at 2 o'clock in the morning and what we can do about that now so just some
example not related to the database but it's useful to understand the problem of this is to roll section which I can take to reach from program work through a flight to Ottawa for the bridge across this 1 is the fast train and this 1 this full training but if I measure just amazement many of the of fast 1 will be better because it takes about 40 minutes less than the slow ones but if I look inside I find that this 1 has a connection here and 1 of the sides of the connection can be longer than 10 minutes or 20 minutes of these is a very busy railroad pop and if concurrent here I can lose my connection and wait in our our that's why it's quite impossible not just to measure the time between 2 points a and b wants to know how addiction what's and this Ideologie is actually behind their
Oracle weight interface which is some sort of a new approach to performance measurement OK in all times of how it usually works customer asks that we have a full response time and the usually starts to mumble something like OK cool temperatures a K and a K but probably someone will someone and this is not there actually is the proper answer because this
is all about the guessing because then we have no proper diagnostic tools we must with then actually to about it you know that any of the judges just to be just a bit more closely related to the ground just
imagine optimizing signal choir Roosevelt explain analyze and this is exactly what we do now always some performance issues and cycles Grossman talking about this performance of on performance of like that this is exactly the same case or in the old
times in oracle and before from 7 all 12 of the station was pretty the same they have some ratios because there are several basic wishes to example guess traitor and if this ratio is in some doubt on of the values everything is a case if it is although these values that's bad thing and actually it was some sort of check least they need to know see everything is a king and then try to analyze their customer and what to do and this is the different approach because even in those times what the customer I had a problem with the response time not with the ratio actually doesn't care about the ratios of after the
Oracle this article there from the article introduces awaits interfaces 1st it was about something about a 100 events and it was quite useful but still very useful effectively weight event is a bottleneck at some point then the database test awaits spends time and so actually this is the performance bottleneck for the purpose of arising lot of 4 different counters figures which are just record and the information and after that a variety of different tools to analyze its to use sequel to produce reports and actually to the right to different parts of this task 1 to collect the data you want a and the 2nd 1 to implement effective reporting about that and using the collected data are this 2nd part could implement both profiling and tracing and that's quite useful because you can just do the both tasks and so anecdotally of that that was and and the undocumented feature and the time and I definitely knew that someone in this of history could tell the story has a lot of the 1 more prove that this is highly desirable so thing for the database is that immediately explored to that there is a good example of that it is exists and the of major walk around how to how to use it and there was some tutorials and those times and so on and so on and so it's steadily became very popular because imagine that you have the world work at all on the chair ain't said this is a big story what they actually they report I will return to that that initially at the end of
and that's an oracle example and this is the active session history of that's a part of photosystem global area that's actually the site and buffer to reach different Oracle sessions which are doing something with the data executed something they put the information about this event and and I once upon a time the special on some purchases supplement these BAFA and get some adult information about most of which events for example they can see here's some quite understandable philosophers people things in spite of this is all Oracle and and you can see that this is a time weights in the seconds in these I believe I and this is quite useful because you can see that somewhere in your database instance there is some kind of contention in some segments for example and you can just try to figure out there to use in this you actually there are a lot of information about that and you cannot only be spot just dig through their this weight events and understand what is this to confuse the public actually a different weight events I measured differently and some of them with a more this approximate approach that means they are not exact but looked at time stands just intervals or something like that some of them are measured in different and MS microseconds seconds and then there is some approximation about that that's quite not an easier the dual figure out how it works without region huge books about that but it's quite useful because if you can see that you have 10 times so 100 times difference in waiting for some event that means that you have a problem with with this is that exact part of your book will real life
example of from my personal experience in sometimes of referred understand oracle their own Oracle on the brand new hardware and their work tests to put in production box of there's something strange about that from time to time all updates was stuck in and nothing actually shows that the problem is with the sky or something like that most significant or problems using external tools for our little
bots using trace the weight interface I found that there are a lot as a growing use weights they're not exactly introduced and elements version and not only because of that we could manage that had window forget to configure a synchronous idea to the classical system and cannot take in imagine which another met at a loss to do so because normally Enterprise sales are clustered file systems are not user-friendly about diagnosing such fixed and the database itself actually must to correct such problems and to help the base to now understand what's going on individual situation is pretty the same box I personally think that they try to move bridge the gap between Oracle and themselves because they started to introduce weight based etiology of performance striking in 9 . 5 for Linux Unix Windows Live and it was not a lot of useful events what for example in 2 versions 10 . 9 and went 1 this is actually the link and I published the slides and you can just go and see that the manual you know about that that there are a lot of 1 2 reviews and a lot of events and they are highly configurable and quite useful for performance that knowledge but just just in case the situation with digital
meanwhile in public health and but this kernel not not so funny reflection and all of and I will call for all any weight interface as cities and now that means we are still trying to figure out what happened what happens is some checklists normally we try to see if there are some terrible mistakes and configuration and if not which try to figure out which queries sexually full and that's a huge benefit that we have be just of statements tool find this quiries and now several and other tools find problems but still what we need to be and to bring our experience to understand what is going on but the good point is that now we have some things that looks like the sharing dual during weight interface for example the gist of a writer and this times in the at least so as to exist some external profile and tracing tools which at least it's possible to use them because some profiling tools of actually to dangers to using some kind of production and with the database actually trouble freedom of performance problems should be done in production because it's quite another new that after implement them and test and so
what I mentioned just under 2 right and I just imagine if it is not exist like it was in all times you have for example some results from with disk utilization and what you need then guess of what's going on at what cost of the problems with your describe your OK if you're well experience a deviate against see these spikes and guess that's that's looks like problem with checkpoints but using in in the just of iterative you can definitely say that this is a form problem with checkpoints in in new versions you actually can say that's something I was in seeing phase we spent some time in the right places and so on and so on but still it's not perfect because for example you know you need just set statistics sample data but wait until you have enough statistics and that is not the thing they chose you to make things better foster but it's it's it's it's a good step forward
to proper proper direction the 2nd thing is external tracing tools did trace of this system top on Linux curve and what they can do 2 things basically they can they can all space probes and they can use space probes cars space probes for example you need to see if you have a lot of S sinks in your system and which application actually issues this it's the probabilities of course there's probably your 3rd party because of their something like that but but indeed the interesting thing is the space props because the problem with the positive school performance but they are actually often end user space and you need to trace some calls inside posters and see what happens what I I don't object layout kernel space probes safer because they are precompiled and if something happens doesn't happen because for example some groups system top contain division by 0 from time to time that's and and and say that it's very stable tool kernel space probe will not work and if you are using user space probe within both because for example you can seriously damage your posters and that's that that's an
example of a short example of the use of the props I just I would per from example because I think it's more convenient than system top out at least about whether they have rated the same functionality of something and it's better to do with distant up something better with perfect but at least it's convenient so you can basically reduces the problem of fault 1 and every function which is decorated as are declared as external and that that the benefit of because you can just trace the execution of this and the chains of ecution and represent the report in some graphical way and at least the simplest problem I hear that I have here shows us that we have a lot of white middle position and in while running sum bitch adventure that's simple of example also you
can just trace and profile any of your acquiring just wouldn't inside their goal with 4 basic well and rapid into their ERF starts and you'll see on what you're really spend time the is a good tutorial i believe for made by great green go on 2nd programmable we've lots of examples how to use that as but the problem is
aware of all these tools like you can see here for
example it's not an easy to start with because you need to know
all of the meaning of all these things and so this same sexually not for DVDs they're actually
for optimal developers because you need to special background to understand that and it's quite is a task because of the kernel development of Linux is very rapid now and they change of their preferred their documentation of behind and it's very uneasy task to make the best of our there and it is really easy to take a system down a variety of opportunities at least because this trace file is put in the file and that is a problem you can simply get so many traces the events of that you you have problems with this performance in quite the simple
example but you can for growth of some wild cards are then you are analyzing something calls and repair perfect self will take more resources than we range and was itself so that's all I would say that's not in the same safe fate beside so
that if you're working with some security-aware production environment for example and related industrial you need to or on the thing that requires specific a new version of carol and you need of root of for the register the props and to run the steps for example this is this is the thing that it's not easy to pursue your security officers to 0 you to do that and this is actually the 1st and the last thing that they're both more providers so then traces they actually it would have preferred some see schools or something like that but this is not the result like I was at Oracle select with then you can simply see that some meaningful events inside the database are waiting for something that's a different thing that is the hose all over the world not realize that you now the picture the you know what you know the people that you know that there's a very good point and
so at the same time this is still there useful many did asexually use that like and weight and face and I will say that then you have some strange performance problems especially in MPEG bonds of for example which is the report about the diagnostics tools and if you try to see something inside user space that's the that's on the tool you have that's why you need to do it and that's why you need to have about with about symbols and so our maybe not so secure refrains from the point of view all for security officer but there are examples than incidentally someone discovered that what the paraphrase and that actually leads to a significant improvement so full of political performance that's of example of of this part by Robert cast as he describes that and just imagine what will be the eve we can beat it easier way to find such bottlenecks up on the large user base will be quite useful for both physical development itself and the 2nd argument is that that several people strive to implement something like these that means that these features are highly appreciated by the community and by the data so that a brief
history all some attempts to build the first one was by Greg Smith I believe that was the first one in by the time timestamp actually but that doesn't matter sections of the problem with this attempt from my point of view is that it was too complex and because of that it was not an easy task to implement that and it was not an easy task to make people understand that this a useful thing like explains that 1 of the matters of that was if you try to implement some interface and for Oracle weight interface and since it is very complex and the result was the complex of the 2nd problem with this fight was that actually those times of posters has no dynamic shared memory allocation all ground and it was not an easy task to try to implement something like that and the 2nd 1 was by no sufficient material so my excluded from my spelling give something that would I believe she's so had a conference it please and here and again it was very straightforward to attempt to implement this because if you try to look something you just start recognition of the time and then you get the results and after performance analyzing on the part it was I believe rejected because of significant performance overhead but the idea is still variable because it shows that we need some such function so of the bottle this obliges actually held in a much to to choose some kind approach then was the situation from both feudal little old the book wait times with quiries and but I believe that was some problems with the design of this because somebody wants to see it and to explain some what it wants to see tuple someone who wants to see the BG Capello and that's why it was successful and try to implement the dynamic alliterative tracing and say about that's I will say it in more detail but 1st let me
summarize some of the problems with those attempts made thereon something about something about it looks like this is the problem of the major problem is 1 that it is very complex and everybody operates some performance of effect this is a problem actually about if we try to minimize the performance overhead probably the benefit of better performance analyzing will be in the huge and that is the benefit of because of a gate we cannot be sure that time of day 1 and everyday life that it will procures but if we should that from time to time it's related little overhead but we can diagnose things better and finally our database will have not so long response time and the 2nd problem and this problem is actually the sum of the oracle was also face this problem because they have different approaches to a store is data that help to store because for example if you have no dynamic shared memory it's impossible to store a lot of data that but 1
of the fundamental problems for me is that often people do not understand how local oracle weight interface walks out that and mean that people confusion about the difference between a good trace and wait interface itself uh as I said a couple slides ago the 1st part is is to collect the data presumably number intrusive and the 2nd approach is to do something with this data but if you try to go to those involved in the same time it will be it anyway very significant overhead because then you select and something from the PG catalog and at the same time from the sol level will be the problem so all
up once again white like for but because all we need to summon to point to the such complex dust and so if I try to implement something some counters and 1 and every part of positive goal they can't that will be I think quite impossible after the 1st so posting into high so there will be a 0 1 tells and arguments of wires should not do that and uh you know the the Latin is that the health of them will direct 2000 in the hands so the this important thing is that the light and it pulls away designer protecting the critical pieces of phosphorus and so if we see that Kuwait we're waiting and in this particular place so that means we have some of that information about performance so maybe it's a good starting point and and experience could be expunged tour of a possible for chrissake so
idea would have thought likely to walk idea and we can when we started to wait where I believe there are 2 functions and political points at point C in which are about that we just put the small integer to the brokering and from time to time from the ground work here we just put all the precarious and can if the is an idea providing of mean was 1 of the row of ministers of that in this field and then we just storing some way and that and initial idea and if you see that with some speed there i a lot of such but it looks so that means we have a problem that the benefit is that we have the exact name of life medical and we can say what is actually happens at the moment in the database what's the results of
this attack problems of this era problem was actually to find the difference between brokering and brokering now but it was I would say more technical think of manageable the 2nd 2 things were quite was more important usually of this part will return a lot of strange light weight it looks as if you see what we have in include a variety of 39 named like that it works but it's is it to get something like 10 thousand something like that but the problem is that by design and besides of that's quite with the clocks for half all of the logs which are protected a shared buffers and we need to control and away because they actually confusing little picture what we could not see anything because once upon a time would account can have about 10 thousand of these like radicals because we have lecture 1st so all we need to to put a tool of our performance for use something like ours and forget about that there is no proper mechanism to count them right now because there are a lot of them the 2nd problem is the different results from tracing days using the system topple perfect in this case distant out but finally I managed to find out that it is a k by the method of analyzing and that there were there is a nice guy how to use system up with Oracle and you can just see which exactly which exact weight event it is right now in this bar for underneath the decision history and if you a Q the this view of you assume a different result and it is not a complete different but it's slightly different and actually the middle of this is that Oracle could not also could not measure at the time of the day in exact manner they have 0 2 rounds of MS we have to use approximate timestamps and stop and the 3rd problem which is more fundamental from my point of view is that we still need to know gets wait time somehow because without you them because there without waiting time so it's not so useful it's obviously know my suggestion is that can be calculated this wait time based just on the 7th grade for example in this topic are important but we can every 10 it out of the slave issue of that time of day and then just at 10 ms nanoseconds if it's a it's samples every 10 ms that will be a a very rough data data uh but architecture that can get us some picture of our whole C is likely to are spread into the time and the time and then those type of data could be still useful so
my agenda and finally is a tool to this and watch some time measurement and are treacherous submitted and cover with some use cases which shows that the variety exact cases then it can help to improve both critical performance for example right in right or or write-ahead log and something like that actually I have a couple of them right now and the 2nd thing is that if we have something approximate we need prior to implement something more precise bought we need a mechanism to turn it off and probably the good idea is to keep some array all white rated talk information inside each worker and implement the mechanism of of output in these data and to keep some more consistent snapshot maybe not nonintrusive but it can be still useful tool for all for if we have some information based on their pictures that overlooks the you know then we can to Deke fiber and it still will be better than using professors up for this so that's something to do about that and if you have questions or some suggestions you are welcome to ask and discuss the days so for example you know all know yes very nice of you use all all that that that's why these that's where this is not what you will see all of them you made it is the so that and actually the use case that these we see it as just that a little lost and found that OK we have some problems with something and then we can see that speed then we get the speed and just q is a buffer for more precise information about that actually it shouldn't be that of as the 1st step for example at least to be into shared memory and so on and so on they could just get it from the process memory and see what is actually had so this is quite the same mechanism but insiders and to work to keep them all and posted so on the side of a conference usually then collect them we're where's the people and actually some as something strong here
was fixed while this conference goes hand in hand
because they actually come with for a rigid version of the batch and with this talk radio but 1 and every day I talk with someone who knows a bit about that and need to improve some things so I think the next week or this week a robust attractors tool for our review and discussion maybe 1 more question here is that if we can implement some atonement away to get at the time of the day as far as and all this thing is not the article cross-platform there are some ways to get the precise time but it is 1 1 of the more you work with the law of the of the we'll just a shift that some cells of the things that he is over and so as a result of the results of the contribution to the mixture over the over everybody but it is a straightforward and other people on the left and the right of 1st of America but not in the middle point that so we have not get in the way that in this also here in Mexico viewing it as 1 of the 1st thing is that I was not now all of it but if I'm out of time and I like to thanks some people at least Bruce seminaries fool and discussed this last year and area that could have been a great program so where we discussed that this conference a lot and the thank you and thank you for that and and so