Duration 2:15:27

Seamless SQL optimization - Nikolay Samokhvalov: PGCon 2020

Nikolay Samokhvalov
Founder at Postgres.ai
  • Video
  • Table of contents
  • Video
PGCon 2020
May 26, 2020, Online, Berkeley, USA
PGCon 2020
Request Q&A
Seamless SQL optimization - Nikolay Samokhvalov: PGCon 2020
In cart
Add to favorites
I like 0
I dislike 0
In cart
  • Description
  • Transcript
  • Discussion

About speaker

Nikolay Samokhvalov
Founder at Postgres.ai

PostgreSQL user since 2005,Postgres consultant (2007-10 in Russia; since 2016 in the U.S.),briefly a hacker (XML data type and functions released in 8.3, 2008),long-term community activist (co-founded #RuPostgres in 2007, relaunched in 2015, 2200+ members on Meetup.com, member of program committees of Highload++, BackendConf, PgDay.ru, PGIBZ),built and sold 3 social networks, Postgres being the main DBMS in each,founder of Postgres.ai: helping companies boost development processes and improve product-to-market delivery speed via using thin cloning and smart CI/CD and SQL optimization tools.

View the profile

About the talk

There are two types of analysis and optimization.

The first, macro-analysis, is analyzing the workload as a whole. Usually, it is done using pg_stat_statements or pgBadger

The second one is micro-analysis and the central tool here is the EXPLAIN command.

And there is a huge gap between them, partially covered by auto_explain and pg_qualstats extensions.

In this tutorial, we learn how to establish a smooth and seamless SQL optimization process in your organization? Topics we'll cover:

What are the pros and cons of using pg_stat_statements compared to log analysis performed by pgBadger?

What are the key metrics in macro-analysis and how to choose the most applicable in each case (is it total time consumed by an SQL query group? or average timing, or maybe shared buffers hit and read by the query group per second?);

Closing the gap: how to switch from macro-analysis to micro-analysis (you identified a "bad" SQL group, how to start optimizing it and what is needed to make this process faster / more automated?).

How to simplify the process of adaption of using EXPLAIN command by a wide range of backend developers, what metrics matter and how (timing vs buffers involved).

EXPLAIN visualization technics and their pros and cons (explain.depesz.com, PEV, and explain.dalibo.com, explain.tensor.ru, FlameGraphs for EXPLAIN).

How to accumulate knowledge about SQL optimization, share it with teammates and improve collaboration.


You're good. Proceed as you wish. I'll be needed, but I'll be here until then. Hello, everyone. I'm, I'm neglecting my life and I'm very happy to present this tutorial today. It's pretty obvious that we are not meeting in person, but at least, we have some benefits of online such as I already provided to our. So talk. Some discussions for another conference in Moscow. So alive, I'm already jumped to a different conference and it's possible to combine several events in one day. As I said, I'm by the Glades to present this topic today

because it's it's a lot of interesting stuff and then I'm I hope you will find it. Useful to see what's about myself, I had occasionally in the area of databases originally and they go to New York City in in Russia, by the way. Sorry for a Russian accent and I've noticed that the ladies 7 speakers will talk will provide talks in Russian so you you should get used to it and I'm very proud of very fast growing Russian community. We need to find a good database system for a for our needs and I was briefly user of my sequel like 2

weeks or so. It was enough for me and I quickly I was lucky to meet good people like Alex baranov. Helped me to quickly jump to post system and since then I'm big fan of both of us and also quite active Community member I are on a report which has already more than 2,000 members and we have weekly meetings and we have a lot of activity, growing and growing. And of course it's not the only Russia from Tampa right now, I'm in California. This morning presenter to know that your tutorial

qq's. My co-host of RuPaul's Rescuers from Germany who have members in Australia and everywhere and also I'm I'm active in the Russian conference course. It's between that we decreased amount of activities this year but I hope it will return soon. And right now, right now I'm running new business. We actually we have a good client. Some of them growing like double of 2, x 2 volumes and transactions per second and the SummerSlam already. Multi-billion company has

some of them already went to wipe you off some of them preparing and we'll have clients and you have very good experience and with no problems with argon company, That's why a couple of years ago. I want you do you think it's called to say hi? And we'll try to automate everything. We should not automated and now we mostly concentrate and focus on improving the diversity of development using sing clowns and the high level of automation, I will talk a little bit about it in the end of our tutorial.

So what are goes today? First of all, I'm going to provide a lot of information. I'm going to describe a lot of tools. I don't expect from you that you remember every detail it's not needed. So please just focus on what is possible rather than on how to do things. So just adjust later, maybe if you have some problem, you will recall that. I just got it and you will return to the slides Orbeez video and Google. It more information and it will help. This is next. Next,

I'm going to provide you the new methodology I called it seamlessly. Skeletonization. This is what I developed and it's all about a high level of atomization. So please don't expect a lot of recipes. How to optimize these join or this? I don't know. Alexa tea if it won't, you will not get it today instead. Will have a high level of process process, how to build scalable process in your growing in growing company and how to build better process through to avoid gaps in it. So this is

what we will have today, and also please feel free to interrupt me. Anytime I'm very open to eat. If your resume, If You're Not Innocent, please join zoom and I will be glad to be interrupted and answer your questions anytime. And if you're focused Eye Care or maybe if your son, if you're developing some monitoring system or something like this you may find some ideas useful. Some of them might might not be new but anyway what I'm describing is currently playing in fast-growing organizations. So agenda

you stop for all from star star. From other viewing of countless Villages, including Auntie methodologist. Then we split our method to two parts, Michael microbe analysis, and Micron house and we will discuss how to switch between them. And we will discuss the various tools and metrics and song. And then finally, I will finalize it and the provide you other, you lot of what she misses calculation methodology is, it's it's more like we're more crowded than concrete set of tools.

It's a set of ideas and the Jordan Tower tutorial. We will interrupt our boring slides and do some practice exercises on on Pensacola. Let's talk with it actually. I already. I think I'll let you launch the this. Shear. So you, please follow this URL and I apologize, you will need to offer. You saw, you you need to log in using Google or get help or something. I didn't say, I felt to manage to get to get rid of these and get an update account because it's it's supposed to be a slow process, a very popular instrument for making

tutorials, if you don't know and you will get quite powerful virtual machine and we will play with it. You didn't spit Defiance not. Oh, so full of this URL. And I will I already actually I'm going to reload it and we need to wait like a minute or two while it's launching and the container is pulling power from Buttercup and song. Okay, let's start. Actually, while it starts English, change the order a little bit, I'm going to start to describe an entomologist. Just launched it

and believe it at Aziz. I will not answer any questions. Like, if you, if you fail to do something with just the Lord and wait a little bit because we have many people. So I will be not fast in the process, but I am not going to help with everyone with this tutorial. So so what kind of us we have right now and ribs are with a lot. I'll talk about, it's inherited from Brandon greck great Dachshund and books. If you didn't watch her or didn't read this, this book. So she has a couple of books on

the 110 film is fresh one. Exit YouTube and I just adopted it for our purposes specific topic for the devices on August in particular, my favorite topping that starts with your uncle. Or are there other mementos the database offerings? They tend to have this approach they just they don't have the ba and they don't care about the phone wants a lot and the Shakira just they just can add some CPU power or a memory and that's it. But of course this is Auntie methodology. Next is this light than two methods. So it's

it's the old story. When you lost some something you tend to find to search it under under a streetlight because this is where you can see at least see something. And in databases, we can, for example, to my scooter. So we know they they are slow instead of analyzing the whole picture. I'm moving from top to bottom and also we can work with that. Also we always tend to use to send Matt Mattox and approaches were already familiar with. So and this is of course not not good enough. We should

develop an advanced better skills to cover all areas of of the characteristics. And another man, antimatter example, if we have some problem, let's just increase her. So let's just do the food back in full grown in it or just let us increase our divorce statistics Target on and so on and so on or another one, very popular for new people who come to relational databases and of course you saw a lot of such The story of Saint, Joseph. Also, very, very good method and the sometimes we use it. Implicitly not, not not by wheel. So if, you know, if you're about to

explode, sometimes you get this. I like that about you slow application developers and of course, within to say oh it's not work, we should slow and sorry, it's called provider and network settings are totally fine. So this is also someone else so it's fun but it's not helping a lot. Let's check our our, okay, it's working. And I'm going, you can, you can press this little button and check and then at you, it's like they just copy paste and then you, okay, if this says relation does not exist, it means

that it's it's two people. So let's wait a little bit more. What kind of real useful? And the good methods we have from SLE world. We have several methods which were developed by Grandma, Brandon Gregg by Google, SRS golden signals. The help to analyze distributed systems from top to bottom and identify quicker, identify weak points. If something goes wrong about for databases sometimes, it's not enough. It's too high level. Stop detailed actually. It's not detailed so about. We still should consider them but we will

not talk about them. Today we will talk about different kind of approaches. Sometimes you you start imagine something and I realize that it's something is very slow. You like it if I did but queers and the you think it's a problem and somebody told that there's a big problem and and we are already going to apply at 6 to send the escalate escalate the deployment. And but then we asked, what would like we check money for and can see that it started not? I'm not today, not yesterday and we start asking, how long do you feel this problem? Like, how long are you see this? And

somebody told us like somebody says It's not that actually not that the first of all. So you lost a couple of hours with very English but you could work more like for Ali and slowly and the stress. So it's always good to start with questions. Why do you think that is a problem? How do you feel it? And when did it start? These are basic basic questions and any experienced a debate noses and use it a lot because before doing something it's better, to ask can save a lot of time.

So problem statement is very, very important. Let's check again. If the descendants already created. Yeah, that's great. Great. So we'll have something here. We have some of his revenge. Dubai sites, I suppose. It's like a gigabyte or two and we'll have background workload. I slightly modified is not the best way to speed your bench. Tobias and I slightly modified workout. Please don't press it right now with steps to and other. I just follow my, my steps first, this is quick intro, before we proceed with real

tutorial. So we have something and let's check, let's check. We see that, it's not that good. You have to ship to course. I suppose you're too busy for you here and what average is almost 10:00. So a lot of expensive selects and soul Okay, we, we want to identify this creature. Has any, anybody can join me in Zoom or, or in our see Channel? I see it. And advise me suggest something that could help me to identify its local is, what would, what would you use? Anyone. What was the question again Nikolai?

This is Glenn identify smoke. We're has it. We have very, very slow. I want to find a problem of problematic queries, SQL queries. Okay, okay, so you're able to check. It looks right? Chicken looks. Yeah, that's what I tell you. I love pgstats, statements, myself would start from. I will start from his assessments and then we'll check Lux. Let's check films produced standard Austin dorf do extension but you need to install it. Right. And please, if you like question to you, also a raise hand in Zoom who uses uses for

production, if you know how to raise hand in Zoom. I'm okay, I see a couple of friends already great. Okay, let's let's fetch top five queries by total time and we see number one, like highlighting is not gluten katikala. We see this guy, right? Sweetheart will have select account star. And this, we see that whenever I should say terrible, 19, seconds and maximum for 42 seconds. But it but it but but it, but whatever. Okay, this is about the question is how we do next? And he will second approach. Let's check log, because you

just a sentence with don't have parameters, right? And the problem with parameters that may affect how an Executor will work because of different parameters Melly, two different plants. This is very important and we will see you today. This is very big and important think. So we need to find some parameters to check the behavior of this. Let's check locks. It's little bit trickier because it's darker but I think I will do it. Let's check. Right? Okay, so this.

So we have this 11. Okay, good. We have this week, I have. But what's next We found the aggregator security were found. We found the Mercury. What's next? What, what, what would you do next? Look at indexes on the table. Here, you are working. So we need to try. And imagine this is a this is Brother auction. So we need, we need to create index and we need to ensure that it will help. So we need to somehow verify our index idea and if it's this production is not goes to check it right there.

We need to check it somewhere else. Right? So we we have problem with finding parameters. We have problem with checking before months. Probably with explain, right and look up because select is okay, but if it was update or some headache it wouldn't be good to check it on production and finally if you want to check to verify it looks idea. Of course on production is lady like it's not a good idea to do it on production environments. Less data. So I will show you okay. I will show you explain

just running Springs. Not it's not bad because it we don't execute query receives expensive sex, of course we might want to have index and later in tutorial, we will play this week. This example, nothing nothing. It's a simple example will play with it and try to identify this bro but procedural gaps in the process in the process optimization. So let's switch on two slides. and, We are these gaps. Let's talk about a little bit about this gift. Before we proceed. Let's leave our activities to two

separate separate sets of activity analysis. Is it? It's what we did. When we apply at Justice Edmonds, or we analyzed looks? Well, locks is like individual careers with Cindy. If we had to explain and nibbled, we will we will, we will catch like microphone Alyssa's mom plants, usually, we process the form using your bedroom. Or something else. But getting better has the most popular tool and we still wants to see the whole picture. So both approaches are Mark micronauts approach and second approach is Micron Alice is here, we applied

explain explain this is analyzing a single query. Hello. We don't care about other careers at this point and Lisa's, I caught this cycle microanalysis and by the way and the permutation of official pause music station also mentioned that if Iran explain on production. We also affected by background workload. And is also not good because if we want to check pure pure analyze it without any random coronavirus, we don't we don't control them. So we might have very different

results in different minutes so we want again we want to run it or not on production. This, these are two to two parts of our analysis. Are you really write so macro and micro? And identified also brought the steps Pro tips when we already performed. Our mechanic says what? Identified, a bath bath segments with groups. By the way, by all means mind means different things and we will discuss this topic also, not only total time but other Medics found some optimization by the way, in modern houses,

we may want to apply. Mark my crop soon. As I do this or macro simulation, we will talk to also a little bit about it. And then the protein process is to be able to ensure that other segments of our workload are not affected negatively because sometimes, for example, if we created all the Phoenix's with my slow down right operations. Sea Mar in Morrow, microanalysis. When we're around, explain in separate environment, we found solution again in my car noises when I decide apply Macro, for example, increase Workman globally, or

just created to create individual index. And so particular problem. Again, Pro step is to ensure that our quizzes are usually. Like it's very rare, I don't see it. A lot in companies is quite Advanced topic to have it forever change to have a listen to verify, then we only building such systems. Okay, this is how we like our very abstract look at tools and we have in form of graph. Some monitoring system or oral form of tables. My personal opinion, both are important grapes,

help us to understand storical perspective. So we can see where the problem started when it ended with, do you have spikes and so, but grass like details. So you can't really do much more to tell Stan. We will, I will just have our own instrument, our own to oppose check-up to just to give you an idea how many details useful details. You might know, you might have, if you are you stable approached by the game by the big topic it's it's worth having Morning to link with

my coronavirus news, I suppose because it's like standard the fact that right now with spikes and so long and now it's just also has several views if you are a big fan like me a big fan of physical, you're probably busy or just regular text all four of explain, but last month we see very well developing a new position tools of the old ones and new ones. I will at least I will show you and this is why I'm quite excited. Excited to see how this area was

music system is being developed. So and we will have this this Tower like space ride. So we have macroeconomics microanalysis. We have some tools that I of course, and we have methods, Michael methods may be applied in my coronavirus in mi coronavirus is in March, that's also might be applied in the micronauts, is in the micronauts is. It's like, it's not, it's not like it's like different, the X's Dimensions, different dimensions, but what kind of gas do we have here? So you guys

fill the Gap. Is it's hard to switch between macro and micro. Why? Because it doesn't have parameters. It doesn't have plan examples. So if you select something from tableware equals, something it might mean six pencils come in the scan depending on various sew-in sidewalk with a group. To absolutely different execution us. So we need to find parameters before we proceed with optimization ideas because what we use in a population parameter values, may not match referral production work load, and this is a big problem and I will show you how it goes. This is the

weather in there and what kind of ideas to do to close this Gap. Next Gap is where we are and explaining the lights again. I will show you that it's if its production, okay, stehlik, this is not a problem. It may be very expensive and the fact you'll be your buffer pool State and the person in general. But if it's something very like, for example deleting million rolls, it's not a good idea to check on production, believe me, completely bad idea. So it's better to The solution is simple, but hard to achieve simple in in Siri,

you just need this. Absolutely identical, identical environment and around everything there. But we have better solution, we will discuss at the camp. And finally filled the Gap is, how to thoroughly check changes with cat, how to, how to watch with your belt. How to change the like, Index idea how to reset a question to everyone. Where do you wish to see some big problem on production? Where do you run? So, maybe come cuddle. Me directions to you. Like, you create me, looks like a long time without bypassing migration tool science all its the

problem because we want to check without affecting production. We want what, what if our index was terribly wrong. That's why this, this this gaps actually, I believe our reasons why apple is eBay expertise is considered like black magic because his car to car to learn with. You need money to spend many years not to make mistakes because production my mistakes about the introduction or maybe very, very expensive. So we need better environment, sexual against all these to get number 2. Number 3, it's all about. better environment, brother stayed in

court development environments, testing testing Okay, what kind of market analysis tools? I will talk about sources of data for my coronavirus is I want to talk about monitoring tools because it will take 1 hour or so, it's with you, stop it, I will do some highlights. We have to just let activity, of course, this is what our like very like a central source of some proof and it's available by default. Of course, it's very standard box and we can check back and look at. We can see where is there. We can see a state. And thankfully, it seems like

5 years ago or so we can see where the vents and weight of an event types which is very exciting feature and still very underestimated by people who create monitoring system sound. So I will show some examples how to use it. So But what what kind of limitations to just pass? It has by default, it's only 100 1024x, you can adjust it but it's it's a liquid restart and I mean you're going to just leave it for query, but usually when you're ready doing with problem first

time you see that this is not adjusted so you need to deal with what you have and sometimes with complex. It's not enough at all. You don't see the full side of the family. Could be the next. It has some some Time stamps and you need to play with current. I'm usually the clock time stamp North now because call timestamp is providing like because it's not the transactional reading from page of selectivity. And finally, you have stayed in here, like big downside of state is is is that active

doesn't mean that it's really active, with me, it may mean that it's locked. And sometimes I see like, most monitoring tools. Don't distinguish, active doing some work and exit locked on some other query. And you need to analyze with went way to vent pipe to distinguish it and this is big problem. Like most monitoring tools. Just to show you active this number and the city and it's hard to distinguish is a show or was it like I owe you sure it's called to receive. So that's why I might be, you need to analyze. Wait, wait even type or seepage Alex

and analyze the login, you should have still walk song. And live in luxury can see some information about looks and then block of some salt and it's the standard extension available everywhere but you need to install it and it has interesting stuff. As far as food query battery. It has a good idea and it has a lot of metrics. And number of metrics is increasing in Forrest Gump. August 13th, we will have more Medics. I will show you later, but it's not enough to just a sentence is not

enough. Despite the fact that there is a lot of Matrix we still need more. That's why such attentions as you just have to take a squirrel starts and where it simply exists and if you're lucky enough and you're not in an obvious yet or origin, not yet. Not there. You may consider using these extensions to have a better view of the weather tomorrow. Based on Fitch's assessment all of his expenses. Also have good idea and you can join information from them. Or we could just ask provides information about physical

activity like just because by default and doesn't see if your actions with disc released at Jacobs unlock this problem and the provide this information and also about CPU time system and use of time you can distinguish has an extension from Polo team monitoring team. You can you can analyze where and join predicates. So and it will show you how what kind of parameters were used was trying to Swerve through frequently use and what what Romito's will not be quiet. Important to jump between

macro and micro analyst has an extension. If the information about the way to vent pipes and white event details like Stan's produce acceptance and finally also big source of Truth is August's real log you can see slow quit. Is there if we adjust walking directions to Edmond it's not a good idea to have a zero because zero with you and especially if you use his locker and John Aldean song system would be very slow like all those of my fault. So it's better to keep it like half a second or something else

is the weather in jumps off of the ability to get Observer observer effect. Because this ability That's what I do. Slow down your system and also has information about the block split is about can stop by just kind of tired, but the problem is it's impossible to split these streams of knowledge. Two different blocks. I hope some at some point, always will have a ability to control next. I already briefly describe a lot of a lot of information in Brewster TV 10 producer statements about what you really needs to remember is that we could use those

statements, you can analyze frequency its goals by the way. It's, I think it's not very good name in because some people tend to think it's like total of everything I like. Total of all work out the store. I'm only within the boundaries of particular, quit a group. So if you want to see the whole workout, you will you forget this little time and you will have total You pick a number of times spent and are you in terms of the amount of data fetched from

from the barber pole? It's this cold keeps and or or head to the buffer pool from the bench cash. Maybe from disc will not know it's called shed blocks. Read the same way for local. Local blocks which are used to enter and get in the case of a temporary tables and blocks which are used in the case of when will it's not enough memory. So it's fine like what me to smoke and some other stuff. It's not important, I'm excited for like 13, we will start to distinguish, it's already committed. By the way, we will

stop to distinguish timing splitting. You two, planning time and execution time. And this is why is good. Because sometimes, especially if we increased The 436 Target globally or for particular golems in the case of complex queries when they have quiet long playing time again here, quiet. Not like very successful naming I suppose because it means how many planets, how many planets happened? Not? How many plants we keep in memory multaq. How many different after class

with her? How many planes have sole kind of you need to check the station, to remember the meaning here, and this is very interesting, especially in the context of cloud adoption and things like Aurora wall, like short story, if you use Aurora, you pay for oil. And you like, a skillet musician is becoming very good profession again because it says a lot of money and Block has this Blockbuster saying provide an example of us were ten times more than you Sans ghosts and the solution was up to my scripts and the interesting that don't know, right?

Where is a car counted. As you know, if you have more memory, you catch better, you can last week but for us the count only writing to write a headlock. So these guys will help us in the future for Aurora. When when was Ghost 13 will be there, it's not because of Aurora adoption options slowly. So when we have these in Aurora to help us to save money, because we will quickly find Christmas trees produce a lot of, right at that look. And we will have to buy them and say some money. And as I mentioned these these extensions they are addition stupid. This is

this is this is also interesting addition it's actually it's provides history. Something out about the history of Active sessions so it's it's a sampling like every second. Just information from the zoo selectivity and the weekend later analyze sampled sampled weight event analysis. I will show you the way to go. So how we can see if we can't check because of activity, when you lie like select star from doing like which might you might consider our convenient, they are providing this information in

top like a manner. So. Up and did you send her? I find producer. I just sent more powerful more features, so I tend to use it. Sometimes not very frequently and the weekend. We will see what we will see you in awhile, you know? It's totaled, but it will refresh and Facebook activity, and show you some details. And like these are, these are Aquarius, and you see, it's like Singapore. It is somehow with my instinct is good to be, get them and maybe see what kind of way to vent

pipes, and we have and this is exactly how it appeared on Amazon Aurora then and then people started to implement its monitoring systems. So make sure this idea came from. I suppose, it came from Oracle world end when we had a lot of users. Migrating from Oracle, they elect this ability to see this kind of analysis and that's why I like de mandilon. Weight of a rental house has grown and police. Finally got it. Now we can have this. This is from this is controlled from

us. So you see that for example you could you see that we have a CPU bound to be with you because And this I think I owe right? So happy for you and I or and then till 6 something I am wicked Morsi. See you tomorrow, then are you? This is very good and we can we can select, we can see what you look beauteous. We're going to lies historically. Have adequate a group but what would you do if you are not on Aurora, but it's solution. It's this application reason in Java actual couple of them but

I think one was derived from another I asked you earlier if I can work with Oracle and focus and second one can work wonders with balls, guess that's why I pee in the naming. But but this list like I tend to before this one because it has more details for the specific data from one per second per second. I suppose you can, you can adjust it and then it's a good way to vent pipe information and provides it is a beautiful graphic and you can see the sea again, this time with a CPU bound activity. Where can I manually select this. Of time and see

top curious here and we can fetch to put it there and jump from a coronavirus is to my coronavirus is. But again you by default we will have on the 1024 limits for queries. So if you have cute. You may end up. We've got to do my Coronavirus. Salt and interesting that this to you can use it at hog. Like I need it right now. Let's check it. But what if you don't got to like one hour ago that had some problem in this case, of course is not possible. You will connect and see all the new data. In this

case, you need to have this extension cord, you just sent the email and this to understand that the recent analyses installed and fetch us information from the pastor from from this extension data and the image of the trolls. And this is what I found in in chunks of Open Source, open source tools, and I don't see good analysis of Wight events in monitoring. The Earth is only this also import monitoring, but it's, it's quite basic analysis. We cannot see. At least or at least I didn't find it. We cannot see if I don't find out how to see the least of birth quiz. How

to jump from micro to micro here, we have something like this, looks like a only the beginning and I hope this morning telling and other things start to develop this quite important and very, very useful for people coming from Oracle world to bullying. Let's finally do some steps. So please switch to get a cola if you looked in. And let's start a weave steps, number one, and two. Is very simple. You just check that everything works out, I already did.

Select now then now I'm checking that they're just outside once work works so I have something rude. Next step, step to sew at step to have. We got a check in the content of the juice that activity. So we see okay, we see a lot of happening and buy background. We see the most of them are silicone star and but we don't have no idea by the foals how long they are. So cute. What kind of things are happening. Anyways, so it's supposed to be a snapchat picture and we need to do some math with

time stamps and soul, soul to have some convenient to you can start using. Center. I wish I could do something serious drawback of a central unit widescreen, otherwise you won't see cury. So make it for either and you will see Life as we see, if life was. She was she a little select star? We see interesting information. How much memory was used, if you press shift, if you put Focus there with click and push gift be, you will see, for example, you'll see this guy of information, like I

almost at like. So you can combine these like a roll of you alone. Now, look at Greece County Executive and discontinuity quite interesting to may be useful. And what I like about this to, you don't need to install anything on on your server and you may use it for anything. And, of course, if you use it for RDS, you will not have recitals. Are you data disks? But you have a good activity. Do my coronavirus is you can ask not only position. You can ask her to record some

statements and then December will calculate between two snaps. It will provide you interesting insights about Digicel statements coronavirus cases Any questions so far, everything works for everyone. Sleep. We go to is tanning salon. I'm going to maybe two still very basic, I know. But more interesting things are coming. Okay, so if we talk about to do some statements, I definitely see former major approaches to analyze. It first is by total time, I always start with it because this is how we can reduce load water system. This is how we

can prepare to scale better if we have some qualities, which have a lot of total time videos and we managed to reduce those values. It means less CPU, thermal spending less resources are spent in general, so we just order by total time get top and top, five or top 10 and try to optimize each of these Guerrilla groups. Interesting point. We can when we have tomorrow is focusing on total time. We can approach several top strategies. For example we can see the

Everest meantime in time is good enough for example one millisecond and but we see that the frequency so golf is very very high. In this case will go to application developers and tell them. Do you really need to run to 2,000 times per second of such queries? Usually let their answer is no. This is what we wouldn't want our software. We're going to fix it and it's already win any more problems with you, but sometimes you see calls are okay. Like that sounded less than 2,000 per second and mean, times,

in this case, you want to minimize meantime. And meantime, it's 7, Timing. This is what I do. Maybe it's Mark macro or micro optimization steps. Sometimes you need to mix approaches by 2° average time and also decreased frequency as a result, total time will be mutually like they mutually in the overall next week. When we analyzed, for example, top hundred by total time, we still made me very, very, very fast queerest which are very, very frequent and why we need to bother

about. Because even if they are executed during like 5, 10 microseconds, if it's happening 5, * 5000 * 2, second. It's not good to have. So I frequent Celtics, select one, for example, of value, situation. Because of these queries and still produce. A lot of this is better to get rid of such benefit as well. So why did this, why this to stop and buy call separately? And I had situations when even top 50, very frequent likes you or 5,000 per second group because it was number 270 or something.

And next by meantime I don't know why people sometimes stuff with these. This is for our scalability, and overall performance is not the main approach, but I understand a situation when situations where it may be very helpful because And because if you have high noon time, even if total time as low as low, it's happening like a couple of times through minute. But meantime is like 10 seconds or so. Two seconds, it means that some users are experiencing having bad experience, very slow

work slowly. So I go visit roaches, like user-oriented, you try to help some users, sometimes those users, may my weight. For example, some analysts they work. They might wait, wait, a couple of minutes, but sometimes it's real external users that you don't want to lose them. Because they see you like 1 minute. But sometimes also this this this mean time is like ours. It's made you leave two bedroom house with master in case I go to work and related issues. And finally, this is what I attached a little bit too late to Amazon, and clouds, and when you, your

IO is is expensive. It might be expensive in terms of money, is maybe I'll Sixpence. If it jumps off. I'll let them see, again, if you have slowed, Jesus, it's a mistake to think of that. Everyone is what I do on SSD. A lot of is being produced every year and has reduced the number of terabytes. So we might have lost low disks. So you might want to see you top 10, for example, by Facebook thread, and try to minimize this, like these Critters, Okay. OK. Google

questions on. How can I install passion? The remote server has a job applications for you is totally most sorry, but I suppose it needs to a graphical. You are so it needs to be on your machine. I suppose, I don't know, I'm not sure about where abductions or something. Unfortunately. It's like I see it's very, very like not well developed area. Only tools are only like stopped being developed. And if you have interest here is very good area to start doing some open for security

and development tools. Definitely, Maybe have someone that that might be related to the next question is, doesn't passion. PG Sentinel bring much load to the system. Oh, I like an observer effect is my favorite topic. I don't know, exactly, of course, if you forgot Apple ID without position to know if you just installed Flash and use it, it means that you selected from ballistic, Silvertip example of a second, it's quite early, so I didn't do it yet. It's interesting question, then that'll be good to understand before

your life, sometimes, right now, just keep in mind that this thing exists. If you have problems with your mom, just wants to do something. If it's laid in bed, just use it. But if you want to use it on a regular basis, maybe it's worth of benchmarking and analyzing. And the trying to understand this sampling. Maybe want once per second is good for observability because you will have more precise data about its. Maybe it's because some cost-efficient for me. Okay.

I have cut the cord, the prices. I identify you need to question about Coca-Cola. I like this could be cool if you cannot find prices. Right now, I'm using speak of the cord that you can create tutorials on it. It's quiet quiet convenient. But I, so for example, of crunchy day, today also had some learning portal. You can see the hair tutorials and they they don't have this requirement to log in using it up or something. So I was also wondering if this when you want to pay them and you need to write their support, a lady

answered me about the was not like they're selling more those quite basic and then we'll Schumer. Okay, I need more questions related to optimization topic. No. Okay, let's proceed. Lakes quite complicated, a little star, but I consider this very important and sometimes people don't understand these metrics. It is a statement provides. So I started to really well on the stand, them only, when I created this, I created. Well, not my team, helped me a lot and continues. Helping me, we have this tool for open source for health checks and what it does. By the way, we could just toss it in

those who have also couple of other problems. First of all, we don't remember when it was raised that. So, if you just observed, asthmatics, you cannot understand anything because. Okay, we have time in blah, blah, blah, cytus II, 4 hours, what and what we need to understand when it was understand how many seconds, how many examples of processed the 2nd or something like this? So you might want to remember you visiting, time yourself, or you might want, and most toes. Do this have two Snapchats

and calculate Delta s for each method for each and then Right? And then you you might like this is one thing. And another thing is that So this is actually we're about to throw backs off to do you calculate 2 to get two Snapchats, for example, 10 minutes, between them, and then what I like decided to do is you can see this. So you need to Snapchat and you have these doubts, right? So you have goals during these ten minutes, we have this amount of calls. You have a system or CPU spend this amount

of milliseconds. In total, we process. The refresh this number of rows and we hit the number of bottles in the bathroom and read this number from page cash quite interesting. But next second songs, like I called several next. I broke revive is Delta's by observation. And we get like streams. You know how many goals per second we have it could be us like we're just the second. Okay, only took. What is the second very low? By the way, this is of the same example with an optimized query as we

have in Pensacola. So to spare second, we are spending eight seconds. Every second on to this abuse. This is, this is not linear at all. So when you have slowed, the death of his behavior is not linear. When everything becomes slow. It's like like to explode. This is what we are observing. We have we spending his second service, tech support on two CPUs, not good at all. And we are fetching to Ross Black, new music for every and receive 9860 K blocks, you need to multiply it

by 8 and you will have a model bise. You are a beast, like, stream of heating. Of course, we should understand that the same bathroom with multiple times during every second, this is normal and also a lot of things are happening from which case I suppose from a file system for from discs as well as several several is where we supposed to be shipped up a little bit. Of course of course when we / all calls by number of course we always get one this why I hear

you always say you want but where would you buy it total time? By number? Of course, we will get average duration for every cold. So it's why would I ever slept in separate rooms for this group for this group? It's important because this group is Major answer of the phones. Then we devised number for average number of Battleship and red for Liverpool cash performance. We see a lot of every query, she's a lot and reads, a lot from Oshkosh to go to the bathroom and we already see you didn't have this problem. We even leave out a

micro spect that we just to get one number. We need to process enormous amount of data, right? So we like in Texas, I suppose, because it is what helps us to minimize number of pages with you with And this is very important by the way. I will repeat it several times during her all about minimizing number of us. That's why I bother us. Information is always. Number. One is sometimes, sometimes it's more important than timing, which is not linear. But number of amount of work

to do is very stable weekend. Okay? Summer days in sorting but it's not happening. Like the next second, we won't have two times more. Butters for our Target is quite stable and if we just have Snapchat, is an amazing thing. You were close and we just analyze it. So it's like during the simulation, I personally consider. This is number one metric above total time. It's not, but this is what I say. This is my experience in my opinion. And the finally final Southborough it just percentage. Do we have received it? Like almost a hundred percent of old

old old old total time is associated with this acquity group. In the number, of course, is just 1.5%. So very low amount of leave the lot of time spent and the sheets. And again, here on this query results, back, whatever you talk too much. Like, this is what I usually do. I usually check this number to send understand the cover of every. What would I hear is historical data? Of course we need good morning for them to see when it started one at the end of the emails as I've described. Or no, Okay. Also

are going to get all of the word analysis. We just use. Work is good if you have, or am I the best if you like I love you using to help you out the sequel and the right functions functions. You always call the select even if they modify later. So this analysis will not work for functions but it works if your if your developers are using or relation of my think or something like this and you will see how I like, what, what is the ratio of Selix in total time? What is the ratio

of Selix in streams of heating and reading Butters from paderborn to Battery Park from pays cash, December 4th. So we can understand. Do you have if you have a ride heavy workload or its most And finally get to listen, can just have this one, big row, always sounded kind of cannabis and everywhere here but we understand our two key PS4 understand from Euro to process per second at its core processes. G3, stream, Sparkle, everything again. This is very, very, very good detailed like like this. So that's why we need to

have money rolling in the, it's good. If you have both approaches will show you his story, but only like top of Iceberg and this is how lies work. You can analyze it very well. So, I also also, I'm not going to describe your today morning to link. It is upset that it's another big topic, but I have a link here. This is our our, I mean, the Russian Community RuPaul's. Guess our new efforts to get good out of you out of what we have right now in chunks of pause this morning during and on this link you can find document. This is

like our community. We put a big list of what this morning and right now we are doing more photo analysis of everything like usual. Watch over things like these analyzed. Datadog a commuter and soul and we're checking every aspect of what we would need to see. Like I'm starting from Korean houses and going to check for in an insult. Also, please expect some materials in the future, it will be in English. So I'm going to publish. If you follow me on Twitter, I'm going to

share our findings and I think it would be helpful at for all people. Like right now we still in position when we get some too and we need to develop more and more on top of it because it's not enough. I hope in a couple of years, we'll have better situation and I hope all of you will have to understand what those samples are missing and what samples are best. So yeah, resizing browser is annoying. This is true right? Okay, so let's jump to Michael and Alice's and then just just let Michael know. It says it's up

my condolences is usually don't use explain explain this is very common tool. Is it standard install or and explain itself shows only what weather thought about or things right now about how to execute the query but it doesn't execute it. So we will not see actual metrics. We will see how long would it cost and expected amount of ribs. Explain Annalise is usually what people use to get to check it for you wish with serial numbers with your timing real, but by default we don't have bottoms big fan of Butters. Analysis

is optimization is more important than timing based analysis. Why? Because timing is our ultimate goal, tweenie. We want to hear very, very low numbers for timing, but when is it we have a lot of deviation. Well, since the instability in timing, we might have four cold cash or something else, will always show us that we have a lot of work to do So why like imagine we did some micronauts system. See some queer, slow or fast. Why can I see sea results? It's a separate topic. So if it's like one second, but

my Kronos this we see, it's only one microsecond is about to like kind of medicine milliseconds. But Marcus tells us that averages one second. We should start suspect. Of course, what can issues we need to analyze looking looking looking analysis and see which box, which transaction, and this is where we can lose lose time. Again, in the morning, we will see a lot of exercise session, but maybe some of them are doing nothing, just waiting to acquire some work. This is number one reason, you will not see it in my car. Now it is because of your alone. You have only one query, know

the ground for Club if it's not like I can see that the best approach when we don't do it on production but then A lot of work to do. This is my beer like the most popular reason it when we want us to my we just need to receive the quill. Has a lot of work to do. I am I skipped hear a situations when it's pure CPU. Work work. For example, you have some quick relationship. If you have your functions, it's not, it sometimes happens, but it's not that, usually a huge deal. With a lot of lot of lot of

data. This you will see with eyes and physical level is bothers. You need to put buffers War proceed. Why? I prefer Bob first? Because we may see that we faced only one role but we we need to meet at the police. Need to deal with thousand bottles because for example of bloat and analyzing both will show us a good time that we need to deal with loss else. We need, Lexus is not normal to deal with. Thousands of officers to just to get one row normally even if we have to

get to page one. So unless this is why the toast is involved. And so so that's why it's better to have both and buffers very important. So actually are you on the same amount of Ohio that needs to be done? Okay, and also explain impossible. Still haven't explained it on the Liars. Will get the information about how much you will date has been rather lost. Not only in we will have it. Not only at Montreux level talk to just not that much but also at the micro-level at

explain and please don't tell you my real easy to forget sometimes you just run. Explain why isn't that? If you see someday that will draw some conclusions and then suddenly realized, I need to run a twice to see you to see when Cassius are warmer. Maybe if I called Cashel said, no, just so usually, twice and wants to consider both situations and depending on how you're About to pass. By the way, if you see here and shed blocks red for example, in the report number

of this reported total number of Bloks dishwasher red is low or second. What if it's slow, it means that your databases which much you're working set of your data with your new pillow open. It's it's, it's fits the situation when you compare this number on this number, you see that's okay. Fixing us all followed by purple is high. So this is how we can check the buffer Pulp Fiction and this case, You want to care about like you Willa Jean memory. So you need to consider that you can, you should suppose that your bathroom. It is warmer

definitely twice because the second attempt will be closer to production any questions so far because it's like a lot of Matrix and soul. Okay, no. Okay, few words about observability problems. Joining microanalysis. This is this is easy to forget. It's my something that can additional several times. You sure on explaining why she will have different numbers will be the same because that's why I preferred, by the way. One more about the timing on this Grandpa from the communication and we should just get a table and a Thousand Trails

do count multiple times. This is predictable and then the same weeks been analyzed and needs to analyze all operations and the measure timing cover of them. So we all results are affected by the fact that won Top Chef, we want details, okay. Affected negatively. The result by. This is very important to remember because otherwise you might want to see me. You might expect very different if you expect the ninth in the second boss in production. It's 8. It's a good direction to be mistaken

but sometimes it'll be mistaken in this direction. And just a few hours ago from published a blog post about this particular problem, when they're still get the eight times difference for expanding the lights, in the AWS 700. I want them to tell send the considerations about this problem. It's cold spiralized may be lying to us through you. I would, I would say if it's almost always lying. The 4th at 3 6th and it's worth checking actually, I would adhere that. Don't know, if you are going to use explain, explain an

extension which will automatically lock. Not only us but also plans actually actual plans. I wish we had at the moment when it wasn't even able timing. This other cat is not what you want to have at all. So please measure your system, check this other cat Benchmark at 3, to do my calves benchmarks and then make decisions. And I was not surprised about this. Well as surprised and not surprised. I knew we listen to Just discussed it with Alvaro but what I'm what I'm I was super

surprised about is that even without timing you could consider only poppers with wonton big fan of Without buffer. So now this is Without Borders in with waffles. Also shows from a difference, so analyzing number of batter for each node. Also gives you some other cat negative impact so it's all. So if you use Auto explain even without timing only with bottles also check overhead on your system and just know that you had this because otherwise you might have surprises on the greater

performance. Okay, that's it about this topic. A few words about his relation to us before. We proceed with some practical work and I'm excited about County. Like we have good competition. This type of visualization. We have many years is from standard to which I personally don't use at all and the no chances that I will start using it. Due to bunch bunch of reasons. I'm big fan of a sequel, and the other is what station tools, but it has embedded ability to visualize explain. The

Cure is this. That's that's like Arrows with different. I'm not sure it's related to timing or buffers. I suppose it's time because My, my is Bruce's listening. I want, I want to convince bruised because right now we have discussion hackers mailing list about bottles, my personal strong opinion, that bothers being turn off, affects all of their ability to spend most of them don't care about but they don't include it into consideration. We don't have at the moment

we don't have it and visualization of my coronavirus is in his bed. We need to focus on what has more often. It's more. It's really useful and convenient and productive. I suspect this is about time. It will be different weeks. I'm not sure next old good. Splendiferous come from Cooper to the tub. So a lot of developers, I personally don't like a collective station block, but I see the musical like this. I cannot see that is color is more stronger than that like two times or three times for me but still it's very careful. I see a lot of people

like this is a default option to visualize explain output for many people. Quiet, new approaches, we had the internally in our team flame, graphs them to visualize with fine crafts idea. And we used before, sometimes, you see why. Because this is what I prefer. And I utilize it. So we have for two types of angles, usually bought her a fling, golf is the carrot of the game. From Brandon Greg's work of analyzing system, performance of Linux, and other personal systems, including those previously and song. We like this idea came to mine, two

different people. At the same time, I saw the invitation for Oracle. I saw also, did you fly in my period of several months ago? By the time we were about to sell a used? Because you don't have the important details about what is what? Shoot up there but still if it provides you very quickly. For example, you see that's XCOM and don'ts of timing. It was slightly more than they're compatible. Not 10x note to ex almost the same. You could very quickly. See it. It was scholarships car with

with its simple and usually bought for the flavor of boring because everything good is happening on lips on top and then they just got all the day then you just process it sometimes. Students to this robot here it was much less data than index. Can't write much less, but spent almost more than one more time. So it's quite interesting way of looking at my Coronavirus. Next, I really love this product and how it's like direction is being developed in from Dolly Bow company, they took was going to explain this location path and he walked

up to, and I don't like this at all. Why? Because this is my personal opinion, of course. But again, I cannot I cannot distinguish like this color is stronger than that to me. But they, they also like, realized this flame graph approaches good. They didn't like 90 degrees and the very wonderful thing. I like it a lot. You can choose timing on buffers here and see if he eats rats and you see his son rats in terms of office or in that in terms of time and which was the most expensive very quickly. You identify

the weakest points in your plan, right? So why That is expensive here. We don't have like a kiwi have Carlos. This is what I do. Like I hope they will develop this left Baltimore and Breitbart it, it's very promising. It's my opinion. Of course, I don't think everyone has the same opinion. So, check this out to, this is good. We also have good to say about today are partially. I mean in Russian folk music assistant. Unfortunately, I already publicly, please guys like that to English. This explain tons of things are. Are you? I think it's very, very,

very also powerful. They have bathrooms on the right. They started from a table, like visitation like I did more visual eyes and colorful approach and extended with information. So we we see here, it's colorful, but not not like that. Lisa has it and they have the reservation with the pre-op discussion and salt, but unfortunately it will be hard to you if you don't understand Russian. So I hope they will translate it. Okay, let's see. Finally switch to our tutorial. We have more steps to do much more. So let's proceed with step 3.

I'm going to also repeat it. We will do some work, let's launch physical sessions, not lost. I'm sorry, I was too slow. And with a couple of minutes, again that we can discuss something. If anyone has questions we can discuss topics like hour while it's launching a game. I see that many people do not agree with me and jumps of Bob Barker's, net Centric approach this, a baffle swear, I never used it a lot, it's not that important, but I suppose, it's because it's not like, it's a special at least and we need to make a default. So I hope it will

find more reasons to do it. And the default is very important because most people are just using the folks. It's always so like customization, it's for like four more advanced users only, Okay. Any questions? That's like half an hour. I think we will finish in time right now, while I while it's being Lunch. Right, I see you in chats that somebody sent a link to the library and its applications, but also underscore flame, wishes particular, invitation flame crafts.

For boys girls, explain the ultimate analysis. Wouldn't wait until we're just outside. Once this creates a hole in the ground is being prepared. Still at Sea with locks, There's a question about the temp files in the new Chad. Oh, I didn't check some chat. Okay, I'm going to fix this. Write them files. Could there be a situation, where a curious, sometimes creates them file them file, and sometimes does not while the video of what. Mammals, the same. I saw that occurred at a time. When the system was busy,

I ran the same query. When the system was not busy, it did not create them file. Maybe I remember throwing, but I just want to talk. I don't think it was exactly. So I suspect was not. So this thing is that I was created. When, what, when is not enough, we need more memory for all sorts of creation, or join attraction and it doesn't seem to work. My interesting point, that one query can consume consume multiple multiple times work map. It's May consume less than work Mom. So what one is not allocated William, if your

credit is very simple are very simple all the time and you can see found it and you can put one gigabyte of work, man, and if you multiply 602 to 1 Gigabyte, you if you don't have money to buy some memory adult but you will never release this limited because of the political connections and if you use some connection but also because quitters are not consumed in this amount of data because it might be consumed and multiple times in. This adds a lot of unpredictability,

it's hard to predict how to choose properly. I see that most of the systems are automated system. They put much more if you multiply like what my connections and if you consider that every Beck and consume multiple times but we don't have that song amount of memory about the what happens. I think I think what's happened in your case, you had different parameters. For example, if you have just i i g equals 1 yd equals 2, but try to update the row. If I do try to turn off the volume and update ID with ID 1 million times,

you will have huge blowout and if you check explaining alive buffets on the buffest will show you what you will. See you, when you bought for needed to be fetched to get this ID. Number one, I do. Number two, will still be like one buffer or if it's an okay, so you are too. Priscilla fetched from either basketball or baseball or softball. So yeah. I think you guys just different situations and different sets of data to analyze their, okay, still being prepared. Reitoca Walter.

But in Texas are created in Lexus. Okay, what are in? I see I see the question. What are your thoughts on the value of weight? Best tuning for Paws gas and its future development roads, icy. It's very promising and Oracle experience shows us. It's very, very convenient and just one graph can help you. Well, if it's incorrect, if and you can switch see the Caribbean Sea, history of particle equity, and if you don't have this meeting with office 2024, X3 Sports combined with statements

and by the way, in, first, you might find the patch about Eid to. This is very good bridge between macro and micro optimization an analysis analysis. So It's the same courage is being produced at. Once you see it and give it to you, do a weight belt weight events based analysis and you see all all the metrics I showed you, the check up, you see old methods, you can combine everything and this is very good overview of have it, right? But right now, we have to join. I meant to say

anything right now. To join you. My baby wants to use the library Gold by DJ analyze, develops and grades. And using this you can match and the create your own bridge between micro and macro noses. When do the stars in pictures of not caring about it, if you use you for the last night for the best you always do some Delta's between two Snapchats then you don't need to do that at all. Some people do this at and for Grandpa they just every night but I remember how it was painful but I'm changing

my tax exempt work for you. But adding new entry was very expensive and we got bleach Airsoft performance spikes like let him see that before because when you lose that we need to add a lot of Andres. I'm not sure about current limitations imposed on us, like 11 12, it's worth checking, but a couple of years ago it was a problem. That's who she was at the quite often. If you was at the door and not the exercise, it's fine about it for you is that the day

you might not notice it like a letter to Second. Sole traders to a block 2 seconds but it's not financially for you. Do it quite often. Okay. So I think it's like they're right good. So let's proceed with weave steps which types of three and four quickly. It's quite simple step. Number 3, we connect to our database Den one and we just sleep. Just let Steph months. We see that this bad guy on the top. We can scroll because pspg to install. Very good to come for the sequel.

So was good. It's on the left, is here. We have this Quizlet can stop and we don't want to leave me a text to Analyse the picture on using PG. So how will we optimize? Of course, we want to read less but has less blocks. We want to do less work, how to do this with Mark microanalysis and from what we see you there. Okay, we see that this value current date was checking. Let's check, it's okay, we see sequential scan and if we check it, we've explained analyze before us. We should be prepared to wait up after 1 minute, because we sorta like 42nd, with explain Alive by

Festival, be a longer as well just don't. So let's just wait a little bit. Oh, I must. I must go to talk about this, unless I'm not going to ask him details about morning, darling. Not only because it's longer. I know. I'm not very well prepared right now. In community will have working group National community and we're doing analysis of all the morning. It was imprisoned between the last, we will have good overview of them quite soon, I promise, I can, in my being a couple of months and then guys, better

go to Features. That's it, that's all I can say right now. Okay. So All received here that the number of bottles, you can read the future. We should * 8 gigabytes and 17 seconds. Span, of course, most of them are most of, by its most of all of myself and most of time and go spend for special scan. Let's proceed with optimization before we do it. This some more slots that one more slide quickly. We just right now we switch between macro and micro levels using

locks. You saw me and action, I just checked the logs and that's it. But let's talk about it at least important topic of the full experience to be a might just guess. For example, we see Sam querrey abstract one without parameters, contents of. Table NYC. Okay, this is what we see this is building value. We can see majority of rose has have through. Let's go through and this is Auntie Netta. I was bitten by this intimate multiple times. You you get through you optimize for very worst case, actually. But in practice, we don't have to do that or we don't we have only. Linda we

optimize in the case we are not having a production at all, not good. So guessing this is not a good idea. We can use statistics to try to guess this is more interesting and about 36 just static statistics like distribution of a loose end tables. It's not enough when you need to know what was used and here at the zoo. Extension, if you can install it because I'm not. Yes you can. You can install this very good, interesting approach. This is dynamic analysis of distribution in terms of usage. So this is what exactly What you need, then. You can, you can fax this

information from there but it's quite rare quite interesting. But rare thing to to heaven for simply then we can just observed. Adjust attic TV jumped right to face his homeland and cons for Bella's there. Sometimes we do it. But again, could you might be limited and it's maybe a problem happened. One hour ago and already lost this information. You don't have Slowpoke. We looking this is good thing and sometimes if we are dealing with kriti which is number one, by total time, we can easy, find examples in Oakwood. Oakwood is for example,

longer than 1 second is controlled by what means Russian statement or more than five hundred milliseconds? You see the Moon looks. So you just mind, you might find this money or you can, you can you use an automatic analysis and then try to match with is this house payments using, for example, to look very good Library. This is exactly what we do right now. We might, could you stop by the ocean from Collective from logs, with using feel physically physically

different versions of it for various languages, The same. We fix also, explain you cook in this case you will have blessed by the way, did you analyze cuz my future? I my strong opinion of that monitoring tools should deal with locks, but they are still weak in it. Not not like just a few of them try to analyze locks because it's hot. You need to find them. You need to fetch him if it's clouds are using API and so on, and so on. And sometimes fashion, looks is so heavy. All subs, will be able to observe, observe us again, but

if you can explain what nutrition, you miss you much too and you see plants. But not enough. Sometimes we have number one query in both by total time but in whenever it's quite fast and even next time is is is still below our threshold gold slogans. Mission statement what to do in this case and fortunately poses 12 has the answer and I remember this discussion it's I was I was already like glad to see this change. So imposing is 12 p.m. I say let's log 1% or 2% of all transactions, even if it is fast, let's look like, let's sample will have simple loading.

And you myself for simple login on his oxygen level with without plans, or a statement level with plans. If you use all to explain again, Observer observer effect should be in our mind always. So this is very powerful and I don't have a lot of experience with it because of his 12th is very rare beast on production yet. But it's very very promising and we will let it have its all weekend. We can already think that it's already in our pockets. So let's just use it one with its it's convenient. And finally we can we can increase. Of course the activity

criticize. It will usually in in real big stops and then we can use Thought I would mention them to do with simplink. They will show you some examples of plants that sometimes with plans, so do the procession itself, we identified. But query, we have particular example of today's date. Let's just do some in the bottom two were close. I'm going to run has come currently. I'm going to see if it's just because we don't want to block these guys who are. I'm being executed in background. What is if this is a standalone environment, you

roughly decrease duration of an index building 2 X 2 X longer Okay, let's wait a little bit and I'll notice that I'm doing in there someplace in Dickson expression because if we just having the Sunday, it will not work because our query uses expression here. So we need to have in the Sonic Special. Yeah, extensions. We need to add them to shut your driver is painful. Is it insured? But I think some extensions like I know if it's just us as men. Do this extension. They have your own like shirt, bathroom area. So they control it. Like

when you reproduced the statements, you are reading from that area and it's not like actual table, right? So right, you need to load it and Color statistics and sometimes Change is Gonna Be painful. Okay. Okay. Okay. Okay, it looks great and interesting point, if you read it, it looks Justin. Combs, no, no extra steps needed that in the expression for each expression. When you do they calculate statistics for Orbeez table of these expression are distributed, right? So how many this is, how many that dates. So we do this analyzed our table

Hey, we can proceed to the next step, or let's visit at statistics statistics to have fresh fresh. Look at 10:20 Modesto, two seconds because because we need to have no sleep. There's no sleep selected to sleep, like 10 seconds. So, so Blitz. Function. I just wait a little bit to gather more statistics into just because maybe if we check, maybe they are not there yet because they're not finished yet. So let's let's see. OK, Google, what we have. What we mean time is red,

one second. It's like the weather right now, but worst case is still slow. 21. Second fastest cases are very fast less than 1 millisecond. Cool. So we it looks like we have two miles but still have some bad guys, why? So Any ideas? I think because because I would like to discuss it in the very beginning, depending on who might have different plans. So that's why we we should not, we should consider various options for So let's compare now let's do my Kronos, it's

very fast. If I'm 11, well, five milliseconds, five milliseconds is fast. So we deal with only hand is 47 blocks and all of them are in the bathroom. You see I always look at the photo Baba first. Okay, I see I check on timing but then I suppose because for me it's the most important thing to understand how much work we have. Next, let's try another brother. And here I have to keep this date is is bad. It's very bad. And if we check logs. So now we have a slow stroll us, that exactly. This date is bad right now.

The only thing I could think of Nikolai without looking at the data is perhaps, it's the date of distribution. Most of the table has that date until the planner is deciding to do a couple tables. Can we ride? So we have we have special scan with half, a lot of buffers buff is getting red and it's it's not good. So which would have we have a table filled with a lot of videos with a lot of rules for that day, particularly. And if you want to look around, it's it's it's we cannot do it. We need to scan all of them. We can like use index but it will be all so slow because a lot of

work to do and usually he will need to send it to do. Some reason efficiently, we need to do to do some of the gation in advance and keep this date separate the counters, counters problem sometimes, so we have like, really huge Huge amount of a lake here. How many girls we've had here? We have 1 million rules, so it's it's, it's slow. So imagine we did some radios and so we don't have that much or at all his life to them that we could somehow together them.

Don't. Keep us here all week and we just left us. We don't if we start a run this workload it will not degrade fast. We will have good performance. Of course that's not right now. Everyone is fast an inclusion that's not right now. Look up very fast Heroes, by the way, if you want to exit from Pepsi Center of from this output / pspg, you need to rescue. Just if you don't know so wet right now, lives in everything is fast. Everything is good with have meantime 15 seconds. Next time, 6 and 8 milliseconds, not so good but still

make sure that's it's about like flower. But he basically has problems too like how to switch a. Let's see. Our new small tiny to an extra. It's the skulls of Michael has developed a zinc helps us to quickly see the visualization by default. Apple dip. So we just do you streak we have these we have this. I'll put with us and we'll have your oil was supposed to automatically and we have it on there. Here's how I love life in pieces. Quilt, already fifteen years or so. So if you like me, I can see the real small time, you do it. Listens to Output,

you see that would end this to all. So she's in pain and you can jump to visualization. You can switch to pick up to from Dolly bow. Using this for another $30 bow. Let's do it and see all of this. This plan using death 2 Seat. It's a Method Man blackout by the fold to talk shows timing. But remember my preferences and it shows my first good and the receipt of everything gray. So everything is so this it one more one more topic is left before we will finish. We have

only ten minutes so I have some extra topics to go into about what I want to talk to describe how we how we optimized, how to build weather in vitamins. If we want to optimize note in production, right? We put on our production, normal. Workout, affects our observations. We cannot wash Castle. Send check call Precious. We cannot check a mighty fine. Unless we use stockings action, robic and we, of course, it's not a good idea to check in even design Another good idea on the other hand, if you have time by size development

machine usually most developers have this only and they develop on a laptop on small machine that has like some pictures sometimes. Sometimes that you don't have plans and this is a huge gap between dbas who have access to production but they don't write the Casual coat and developers who are writing cold but they don't make a note, check? And to close is get what we developed. We are you using think loans. But based on the first album you can provide your own sink learning capabilities and extend our its open-source. You can extend out

of a cell is a c l. I a b i r r collection in Easton. Will always be open source so You can use this and have just one server. For example, you have done that I buy is that the best you can have another special. The base with special. Set up also Tantra terabytes, we've certify that the first production can be asked for it can be anything, but this is only the first and we continuously update its state. So it's kind of liquor is better to read it from from and then and then we can share with silver and allow multiple

people to use it at the same time. And we can, we can give a everyone here is Usher own full size. Our base and it's very interesting, it will have less shell Bobbers but she'll be surprisingly don't affect the plant structure and by first time. But his numbers so we can use explain their own such thing closed and we were like on high heels somebody's name of this supposed to be we are preparing disposable. It's easier to explain in a lie. Sabato, see if you want and we threw it

away. So don't throw it away by default. It will be thrown away after 2 hours. Automatically. So this is called out to buy slab and this final step is showing how to use it with a switch light switch turned and fled here and it has API key to work with our database. It's bigger than hear it. It's like, it has $2 by. This one is 5 in 150 and right now you can ask your own clone. Let's do an experimental. I never did it at it. If I just kill everyone who is in katakolo, by the way, if you're a chicken recording, this API key will expire on June 5th.

But right now, we can request to provision our own. I think we are limited by size of my money because I guess we have had the buffers one or two weeks on the amount of memory. I don't remember, maybe not like it like that, it might be nothing off, but it's tunable. Let's check. So we can say, people up a snapshot least, using this as Eli and see the list of Snapchat available right now, this guy is doing a snap shots. Every hour, I might be supposed to yesterday or something, because we decided to increase one

terabyte to be even more attractive. So we have two Snapchats, every you can request a new Snapchat, you can do some time machine capabilities, and walk back and forth and you can request. Actually, two clones and check if yesterday plants were better than today. It's not so you can check a regression of explain. Oh, okay. So, let me ask for least. I'm I'm going to check if if you have any clothes, maybe someone already created. Yeah. Okay. I see you some cold. So I'd appreciate it. Okay. I want to see who can see closed. So I'm going to ask my own

call. And the other three felt. Okay. We have some further. Sorry about that. This is but it's worked like a solid. Let's check graphical interface. If we have this working What is happening with our instance? Okay, let's grade clone with graphical interface. Yeah, I woke up something wrong there. Them always down. If some guys on my team I watching it's it's it's not good, but it happens of course, with such kind of demo. I'm so sorry, it's not good. I would have also tried both ways which Robots. Also I think also not working right, it's working. Okay, at least

child is using social by the way open source that can be used either. It's just like those on about February contemplates. Not very open source. We can sit on this. So we have both working either and slack or involve you like some people are developing. Also tell her, and the Discord. So this is like I call it several less explain developers, don't see some tricks, but it's everything is recorded about some of these developers may ask to get explain details. For example,

example, let's get this. Ribaldis, explain these select. It should be interesting to see it. Let's ask draw to run, explain, explain as I don't want you to explain it should use only Short. So just explain the screen and it will show us the plan in skilled format as usual, And we see some somebody with. She received detailed output of this girl outfit with buffers and the it's translate. Jason also it sounds like the two. It's like weather looking somewhere. If I go to buy

see if neither of us I've noticed that developers understand why it's much better if you if you tell them to read Wonder all you needed to deal with one gigabyte. It's much more powerful than telling that is some upstart for them. Not very experienced developers. It's more powerful to two bites and then everything is recorded, and we have some kind of his possession installed here as well. So I asked the developer to get cologne and implicitly so that's why I called it sound. Less explain developers, don't care about what it will be

executed and they get automatically their clone. If you check clothes, I think Join the club. I think I missed messed up with keys in critical desktop, unfortunately. So it worked here and we will receive. This is full size. Honda 250 gigs, think long, it took only a couple of seconds to provision. Actually remembers 1.5 seconds and this is available eyes open so you can install it and going to Stock Show and have better better environment for your experiments. First of all, Michael experiments using explaining the lights and since we use the same

Planet as in production and what man has the same plants that actually is the same in the same amount of bottles and you can create in Texas with Joe, you can do anything, you can end this separate environment and its uses. To buy that I slept in. You can give you a your test today Express to do some checks with full size that the best dick size and you don't spend the extra Penny adult. So it's the same disk because of single and in this case, are you it's a very fast cheap, very

useful. I legit cannot imagine how ironic splain without such approach because I'm production explain update, North Carolina. I can run, I can create any mixes and this is how we close this this final to get this other guy. I've described in the beginning. So we're almost out of time. I think, I'll just, I'm always right, so final final words about what we learned, what I tried to tell you about today. What is seamless optimization its unified approach when you have Micro and macro both

working? Uniformly, it without smooth, transition of your actions between them still a lot of work to do here and I hope if you can support this and you can review. This patch isn't coming fast, you can find it in my slides, please support, good idea patch it's quite important for a smooth transition between macro and micro. Next, we need to do some analysis and we need to run is constantly this method. Seems to me that it's helps to share knowledge among your colleagues and make scalable optimization a

process. Not black magic, only one guy knows it and that's what we see in organizations. Cool idea. That is a brooch that the best team started to grow. They started to collect a lot of De Leon Park. So, you can work with big size that device is not, not being worried, that they can put something else a mans work down because it's a separate independent environment. And finally a week, Alexa to Fox send, this is how you can collect your own knowledge base. And here, we have it already, but you can right

now. But you can sign in and we will contact you, and we are allowing to work with our products only after 30 minutes dammit where we ensure that it, but you understand details, but please feel free to sign up. We will contact you when we will continue his knowledge base and all such accusation sessions are recorded and we can return to our previous experience and strategy of all the colleagues and have knowledge based around optimization that don't explain everything. I think that's a hell of a lot of other materials. Let's keep them already.

Any questions so far? There's no rush. If you if you want to keep going, you can keep going or you can let your choice of a couple of more stuff out of the news. Sign up for a lady at the ties that have open source Tools in Google. Keep like open car mode. You can build your own starting to Stock Show using our tools and look how small tiny. But so very good Community, who have a external people who are contributing. This is good. This is all from our friends from that, I agreed to the idea presented earlier today. This is very, very

again, like look at our observability tools. So good to print out and put the wall and remember where we can get noticed our position at kkh producer statements, by the way, I will highlight the twos. Kick-Ass shoes, external like additional, unfortunately. It's not, it's not available on every environment, for example, and clouds whom. I expect not having it at all, but this is of Google to understand the system and how to collect jumps on it. And a couple

of likes recent from time to time. This is what people use. And they, they constantly telling me that they used timing best approach. I advertise battlespace approach and I've not like it's not gross at the box office also underestimated and the after I erase these couple of days ago because you didn't create the porch, I know that discussion send the Questionable decisions. But I asked, everyone who is may be participating and listening to me. Please keep in mind, the final goal. Final goal is please. Let's have bought those nipples by default, it's so important to spread this method of

analyzing. It's much more powerful than timing and purchase discount being disgusting. How can use both are important to have better, smooth process optimization, and then switching between between the different methods and actions. That's actually it. Thank you so much anymore questions. All right, I'll question about from the from the Anthem written from there. We have how it works. Cologne startup, you pause this instance, right? We have on the same machine will not

approach. Like when you post as long as the conversation with a production or anything, it has very small amount because we don't need the cash that are those. The only purpose of this mofo, Simpson's death, to fetch and replay of walls from external source. And then, bye-bye request, we will launch a probe is based on Snapchat with a dewclaw on the device will launch another form. Of course really no such as buffers but effective size was that exactly like on production even Assuming that's a, don't have sweat, so much memory on the server. And

this is how we share the same plant structure and it allows us to view my eyes on this machine so developers should expect. That's more of them. They have a cold or cash, Moto phone was before they're supposed to Face from disc or more often but structurally, in Buffalo, Buffalo Dental Works on this environment, completely You need to shut down clients doing a good job rate. I don't stop it again because if you have some visible Castroville G, you just fold prefold you do the cup facial? You like it. It's not production environment. So

you can afford doing it because I read this note very frequent. But so if you'd like, if we have, for example than this abuse, with my allow up to 10 developers to work in the same time that I'm not really noticing a selfie shallow. Well, they also would you say about my block you and you may want you with. We are facing a lot of interesting problems of new guy in this is this is special about production became very differently in the seven different problems and who is I'm very excited to sell them because it's like it it feels like Adventure and I

see how popular it's becoming very quickly developers love this product they say like it's magic I know I know it I see what is happening and I don't care about others. I can do anything I want. This is very, very convenient. Okay. Any more questions? Let's check this shop. Yes. The first call Darius, suppose Miss directory. It's up from Dan. Okay. So yes, it's just pulling in at the first post cuz you actually we just calling in to sit right, that you can just do that. And then another Post-Crescent stand for. I find that Tim smaller, bottle pastors,

and the we we encountered some people thought initially that the needs of the first Film Production to know if you have for lunch from there with Ruby cup fresh and we don't affect production. Let's this one last thing I would do because I have clients like a multibillion-dollar companies. So and we just launched. This isn't the first and we we saw on how come you're not only on Hacker News. People were skeptical about and we ended up implementing a dorsum of our doing. So we have

traction and right now, is it the first and tell them they are both like modules. So if you want them, Donuts, some and the price storage Call Jill. It's totally possible. And we can have a SIM cloning best on Corbin, Right? Along on our other. But the first cloning cloning. I called local. It's very fast, but it's happening locally. So it's coming on the same machine. If you do it, with other means, for example, Aurora has been clones. You like you very well have

different business. If you do something cloning based on some of the price storage Network, you're attached to the same Ocean or you may attach to different machines. There are plenty of options and possibilities. Explain anything. It's like only the beginning of this. But right now, the device is our best approach to any idea, okay? Hi, beautiful. To see how can it be improved? Well, I wasn't crazy it in size, I would probably consider maybe get some weed or fly diagram. For me, it's not convenient

at all. I will take this left diagram on this last one. I would make it bigger and put maybe some white sheer text of my my output of explain to see what they able, what was like, what was happening, where it's always thought about this approach. Also, like we would like we would mix text a little presentation off, explain output and graphical, just maybe with background feeling, this isn't a one idea. I I want to see you like I like what is Lady here are

convenient. I also think it would be useful maybe to have some Vision. I'd be I might be able to provide the output in form of sdg maybe to integrate with father to 1602 Justice Park. Like Rumple. We could put it somewhere to draw boats out of pots and dishes for BBQ wire brush on. This is like picture if you want details, go and see it there. That's basically what I think. What I saw here for this part. I don't use it a lot, but see, I didn't find the backups non-directional. Rose ghost. Okay. Nobody's well, by the way, I also think

it would be interesting. We will know, like it's not about this position, it's about your ability. Could it be that you will be to start using tables in Texas and they have additional operation and topple statistics? I think it could be also useful to extend. It is so statements and explain and have 36 about the doubles doubles. I like how many cc or updated. It's like a, I think might be useful because right now, it's kind of sad, but I think a separate from Gordon else's and you, okay, I see that this

table got this. This this characteristic for 4 called but I cannot start a match queries much disinformation to queries and how to understand what documents, right. So that's actually my considerations about the right. This is quite quite good. I think I might be able to discuss it. Thank you for question. I'm, I'm very glad that you joined during our session okay, I think I don't see any more questions to see if I'm not missing, no discussion. I see Okay.

Old Snapchat does not grant you that. Maybe we could when we do Snapchat using data by slap. Yes, we are right now. It depends where it's like we you can do you can put the same pants down and have no balls with sexy photo. And picture data is that sincere Tomic when will like this is what we do it right now or you can use it just a backup, start back up to do you do the trick to make post? Fix everything? It's we're discussing how to make Snapchats without a price on this that we're not using protection for this. So we can put down this

instance, is it? Have you ever nobody's working? Because this instance it's only purpose to Fashion reply was. Okay, thank you so much pleasure to present today. See you was right by my neckline. Okay. All right, if nothing else Nikolai, I'll just end this meeting.

Cackle comments for the website

Buy this talk

Access to the talk “Seamless SQL optimization - Nikolay Samokhvalov: PGCon 2020”
In cart


Get access to all videos “PGCon 2020”
In cart

Interested in topic “IT & Technology”?

You might be interested in videos from this event

September 28, 2018
app store, apps, development, google play, mobile, soft

Similar talks

Julien Rouhaud
Senior Member Of Technical Staff at VMware
+ 1 speaker
Tatsuro Yamada
Senior Database Engineer at NTT Comware
+ 1 speaker
In cart
Lukas Fittl
Founder at pganalyze
In cart

Buy this video


Access to the talk “Seamless SQL optimization - Nikolay Samokhvalov: PGCon 2020”
In cart

Conference Cast

With ConferenceCast.tv, you get access to our library of the world's best conference talks.

Conference Cast
712 conferences
28982 speakers
10987 hours of content