Visualizing Postgres in realtime
This is a modal window.
The media could not be loaded, either because the server or network failed or because the format is not supported.
Formal Metadata
| Title |
| |
| Title of Series | ||
| Number of Parts | 25 | |
| Author | ||
| Contributors | ||
| License | CC Attribution - NonCommercial - ShareAlike 3.0 Unported: You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal and non-commercial purpose as long as the work is attributed to the author in the manner specified by the author or licensor and the work or content is shared also in adapted form only under the conditions of this | |
| Identifiers | 10.5446/19067 (DOI) | |
| Publisher | ||
| Release Date | ||
| Language | ||
| Production Place | Ottawa, Canada |
Content Metadata
| Subject Area | ||
| Genre | ||
| Abstract |
|
PGCon 201325 / 25
4
5
6
11
13
15
16
17
18
20
23
25
00:00
Physical systemStreaming media1 (number)Covering spaceScheduling (computing)QuicksortCodeMereologyBitProgrammer (hardware)Data storage deviceComputer fontVisualization (computer graphics)LoginComputer animationXMLJSON
01:50
Metropolitan area networkDecimalLinear mapCurve fittingVisualization (computer graphics)Goodness of fitGraph (mathematics)NumberForceSet (mathematics)Arithmetic meanForcing (mathematics)QuicksortCross-correlationPoint (geometry)Ferry CorstenVarianceOperator (mathematics)StatisticsGame theoryLinear regressionComputer animation
03:16
LoginInformationPhysical systemWeb pageGraph (mathematics)MereologyMultiplication signVisualization (computer graphics)Computer data loggingSampling (statistics)Position operator
04:11
Physical systemTraffic reportingProjective planePattern languageExistential quantificationComplex systemSoftware bugInteractive televisionVisual systemWeb pageMultiplication signComputer animation
05:10
Metropolitan area networkGrand Unified TheoryGrand Unified TheoryFrequencyChannel capacityPlanningPhysical systemCycle (graph theory)Visualization (computer graphics)Goodness of fit3 (number)Sign (mathematics)WordComputer animation
05:50
Chemical equationMetropolitan area networkValue-added networkSheaf (mathematics)PredictabilityVisualization (computer graphics)Graph (mathematics)MereologyLine (geometry)Disk read-and-write headType theoryGraph (mathematics)Metric systemGraph coloringFrame problemGoodness of fitWebsiteWordChemical equationTrailMultiplication signNormal (geometry)Bus (computing)Game controllerTime seriesRight angleNetwork topologyGreatest elementPie chartPiComputer animationDiagram
08:28
Maxima and minimaSign (mathematics)NumberArithmetic meanConnected spaceCirclePie chartCartesian coordinate systemLine (geometry)Absolute valueGreatest elementComputer animationDiagram
09:19
Metropolitan area networkPosition operatorGame theoryLine (geometry)TheoryMultiplication signMathematical analysisMereologyWater vaporService (economics)HypermediaSurgerySource codeQuicksortAreaEngineering drawing
11:17
Metropolitan area networkGrand Unified TheoryGraph (mathematics)Water vaporVisualization (computer graphics)Theory of relativityPopulation densityMereologyPoint (geometry)Data conversionQuicksortDilution (equation)Greatest elementOffice suiteTrailLine (geometry)FreezingControl flowClique-widthExecution unitLevel (video gaming)Source codeMultiplication signAreaBranch (computer science)Computer animationEngineering drawingDiagram
13:40
Visual systemMaxima and minimaVisualization (computer graphics)Expert systemMappingGraph coloringCuboid1 (number)Goodness of fitPopulation densityBand matrixStress (mechanics)InformationComputer animation
14:39
Directed graphInformationPoint (geometry)Graph (mathematics)Group actionDifferent (Kate Ryan album)Noise (electronics)Goodness of fitConsistencyGraph (mathematics)1 (number)Latent heatFrame problemTime zoneVisualization (computer graphics)Clique-widthLine (geometry)Multiplication signComputer animation
16:27
Price indexPointer (computer programming)Metropolitan area networkMaxima and minimaLine (geometry)WebsitePoint (geometry)MultiplicationMathematical analysisMereologyServer (computing)Graph (mathematics)Wechselseitige InformationMultiplication signDifferent (Kate Ryan album)Execution unitFlow separationSubject indexingCartesian coordinate systemFreewareUniform resource locatorQuicksortXML
17:44
Structural loadMetropolitan area networkVisualization (computer graphics)Type theoryLine (geometry)Theory of relativityCausalityNegative numberFamilyProjective planeSequenceVideo projectorMultiplication signGraph (mathematics)Insertion lossQuicksortLaptopProcess (computing)Service (economics)DatabaseMetric systemSubject indexingStress (mechanics)DialectBit rateLoginPort scannerCache (computing)Program flowchart
19:48
Graph (mathematics)Bookmark (World Wide Web)AverageBimodal distributionLine (geometry)Multiplication signHistogramQuicksortWeb 2.0Physical systemNumberResponse time (technology)Group actionSoftware testingRepresentation (politics)Computer animation
21:05
ArmMetropolitan area networkNeumann boundary conditionMaxima and minimaInformationTouchscreenMathematicsSpacetimeGraph coloringDifferent (Kate Ryan album)Graph (mathematics)Position operatorNormal (geometry)PixelHorizonOverlay-NetzAreaEvent horizonProgram slicingSquare numberImage resolutionTouch typingComputer animation
22:43
Multiplication signTerm (mathematics)TheoryTouchscreenRight angleRandomizationElectric generatorHorizon
23:15
Statement (computer science)StatisticsStatement (computer science)Query languageState of matterComputer animation
24:01
System callOrder (biology)Data Encryption StandardLimit (category theory)Statement (computer science)StatisticsTotal S.A.CountingBinary fileBlock (periodic table)Field (computer science)HistogramRevision controlQuery languageMultiplication signFunction (mathematics)Different (Kate Ryan album)Logical constantSelectivity (electronic)Row (database)Scaling (geometry)Run time (program lifecycle phase)Total S.A.Group actionInformation overloadComputer animation
24:55
Metropolitan area networkAreaStatisticsExistential quantificationHistogramQuery language1 (number)QuicksortSequelInformationTable (information)Database transactionCache (computing)Bit rateMereologyGoodness of fitTurtle graphicsPrice indexMultiplication signStability theoryProduct (business)Process (computing)Program flowchartComputer animation
25:59
Metropolitan area networkData acquisitionCache (computing)Price indexSubject indexingArithmetic progressionConnected spaceTable (information)Metric systemState of matterPlotterQuery languageDatabaseQuicksortInformationGoodness of fitSource codeNumberComputer animation
26:57
Visual systemQuicksortVisualization (computer graphics)Multiplication signWhiteboardOnline helpJSONComputer animation
27:40
Derivation (linguistics)QuicksortMultiplication signData conversionContext awarenessError messageInformationWave packet
28:23
WebsiteDegree (graph theory)Line (geometry)Graph coloringGreatest elementSpecial unitary groupGreen computing
29:22
MathematicsGraph (mathematics)Greatest elementEndliche Modelltheorie1 (number)Context awarenessQuicksortCross-correlationMetric systemDiagram
30:02
Chi-squared distributionGoodness of fitGraph coloringDifferent (Kate Ryan album)Natural numberCategory of beingProbability density functionRule of inferenceVisualization (computer graphics)Quicksort1 (number)Computer animation
31:08
Set (mathematics)Physical systemDatabaseQuicksortVisualization (computer graphics)Visual systemFrequencyNumberExpert systemMultiplication signMobile appSummierbarkeitSound effectMereologyGene clusterStandard deviationExecution unitXMLComputer animation
32:33
Bit rateLogarithmVariable (mathematics)Data storage deviceLibrary catalogVisualization (computer graphics)Table (information)MultilaterationMultiplication signProcess (computing)WebsiteSemiconductor memoryLoginCountingStreaming mediaCartesian coordinate systemDivisorWeightBoundary value problemBlogInfinityError messageArithmetic meanDatabaseData loggerQuicksort1 (number)AlgorithmSpacetimeComputer animation
34:19
Physical systemBuildingTime seriesMultiplication signStrategy gameRotationTable (information)Data storage deviceSpacetimeQuicksortData managementReplication (computing)Extension (kinesiology)Personal digital assistantCategory of beingInterpreter (computing)Moving average
36:06
Escape characterMetropolitan area networkStorage area networkMaxima and minimaTable (information)Price indexStatisticsStatement (computer science)Cache (computing)System callDatabasePoint (geometry)Pattern languageHydraulic jumpMultiplication signInformationData storage deviceContent (media)Insertion lossTable (information)Query languageCausalitySubject indexingStatement (computer science)Slide ruleSolid geometryBlock (periodic table)FrequencyDimensional analysisMobile appConnected spaceCuboidStatisticsInheritance (object-oriented programming)Hash functionLibrary catalogWordState observerMetric systemBit rateCartesian coordinate systemHorizonWave packetLibrary (computing)Source codeIntegrated development environmentNetwork topologySound effectEvent horizonTimestampLoop (music)Demo (music)CodeCache (computing)Open sourceData typeDirac delta functionSystem callQuicksortComputer animationXML
Transcript: English(auto-generated)
00:00
So I'm going to start. So I'm going to talk about visualizing Postgres systems. My name is Will Leinrober. I work at Heroku for the Heroku Postgres team. But before I really get into this, I wanted to talk a little bit about the talk
00:22
itself and be a little meta. When I looked at the schedule for all the other talks, I saw that a couple of the things that I was intending to cover was already pretty well covered, such as generating stuff from log streams and doing advanced data storage with roll-up and all that.
00:45
But before I did that, I had put out an outline of the talk, all the things I wanted to talk about. And I looked at it, and it was kind of boring. So I took out the parts that I thought were kind of boring. And I went and thought about all the talks I've seen in the last couple of years and which ones
01:02
I remembered and which ones I thought were the most interesting. And a lot of them weren't super technical. They weren't full of code. They were more covered general topics that I was interested because I'm a programmer, a nerd, but it wasn't really specifically code or maybe not
01:21
engineering. And so I looked at the topic of what my talk was going to be on visualizing Postgres. I thought there was a perfect opportunity to go into sort of the history of data visualization. And so I went and researched that. And it was actually really interesting. So I hope you'll find it as interesting as I have.
01:42
As the talk goes on, I'll get more and more specific and more practical. But I'm going to start out with a short history lesson. But even before I get to that, I wanted to look at why do we want to visualize data in general.
02:01
You can just take it for granted like, oh, I need to have a graph for this. But there's some really good reasons why you would want to do good data visualization. This first one is sort of a parlor trick, but it does illustrate the point. This guy made these four sets of numbers, xy pairs.
02:22
And you look at the numbers, and you're just like, OK, that's a lot of numbers. So let's run some statistical analysis on it and see if that can tell us more about these sets of numbers. It turns out that each one of those pairs has the same. For the x, it has the same mean. For the y, it has the same mean. They both have the same variance.
02:41
The correlation between each of those four sets is exactly the same. And the linear regression is the same. So I guess all those four sets are the same. But when you graph it, you can see that they're completely different. Now, if you had a more broad set of statistical things, you could probably see that there, but just looking
03:01
at them really brings home the point of why it's useful to not just look at a set of numbers. Now, this is just sort of a statistical parlor trick game. But for day-to-day stuff as engineers, operations, the main thing I think that visualization can help for is in times of crisis.
03:22
You get paged in the middle of the night for a system going down. Ideally, whatever sent that page, it doesn't have enough, the information that's in the page shouldn't have enough information to solve it. Otherwise, you could have just automated it. Maybe you haven't gotten around to automating it yet, but for the interesting problems
03:41
that require human intelligence, whatever sent the page isn't enough. But if you have a good graph of your system, a nice chart, you can see at a glance much faster what's wrong than dropping through logs, finding out all the different parts where logs are in your system, if it's this log or other system logs
04:00
and trying to find what's going on. And in that sense, having good visualization of your system can lead to reduced downtime, which is a good thing. Related to that, oh, one thing, this is a system we have in our stairwell, so when there's an outage, this light lights up the stairwell and there's also these lights
04:21
that go up the sides of the walls. And this was actually a running light on the San Francisco airport, so we put it in our staircase, and that's a fun project. Another thing related to crisis is just anomalies in general. Maybe it hasn't, maybe something's going wrong that you haven't seen before,
04:41
that you haven't sent out of a page. You can notice some odd behavior and that can prompt investigatory work to see if something's about to go wrong. And the reason you can do that is because you can start seeing with visual systems the interactions between lots of complex systems
05:02
more than, because you don't have to know what you're looking for ahead of time. You can see strange patterns. Another good reason to visualize is for capacity planning. So you can tell if you should upgrade soon. You can look at cycles over days or weeks
05:22
and see if perhaps in the middle of the day you always have a busy period and so on and so forth. And that can increase robustness of your systems. But really, altogether, it removes gut feeling and guessing and puts your working with your systems into a more,
05:43
more science, I hesitate to use the word scientific, but more data-driven approach. So now my favorite section of the talk is the history lesson. I think you can, if you look to things that people have done in the past,
06:00
you can get good inspiration, you can get lessons of what they did and I think some of the stuff when I actually dug into it was actually pretty neat. So the first guy is William Playfair. So he was in the early 1800s and he made the first line chart,
06:23
the first pie graph, the first bar chart. Before him, they had graphs, but no one had used them yet to show data and try and explain stuff in a visual manner. And so this is one of the, I don't know if this is the first line chart,
06:41
but it's one of the first line charts. And this shows trade balances between Norway and Denmark and England. And this doesn't look that strange because the stuff that he did, everyone ended up using forever. There's a title at the top, there's a frame around the graph,
07:01
the axes are labeled, it has a caption, it has good use of color, and one of the interesting things is in the side of the book where this was published, he has an explanation of how to read the chart. It says if you want to see the exports for 1740, follow the line from 1740 up
07:23
and then over to the right to find out what it is. Like he had to write it out in words how to use it because it wasn't commonplace at that time. He also did a bunch of other crazy line charts. And I really like the cursive calligraphy type and title.
07:43
This one was showing during wars with the French how all of these other metrics that he was tracking were kind of normal, but then after these two wars at the top, things started, in his opinion, going way out of control. And that was pretty interesting.
08:01
This is the first bar chart that he's made. And this was interesting because the previous two were both time series. Like they had the years going along the bottom. This was his first graph that compared things without the aspect of time on the bottom. And again, the bar chart is still recognizable today
08:25
and that's pretty neat. And in 1801, he did the first pie chart there. This was showing population sizes and how much their government taxed them. And this is mostly good.
08:42
There's one thing that's misleading about this chart though which is unfortunate is he draws these bars on the top and then showing two of the numbers and then connects them with the line. But the slope of that line, you can see all the slopes, but the slope doesn't mean anything from the data. It's just because of how wide
09:00
the circle happened to be at the bottom. The absolute sign of that shows if there's more taxes or population, but the slope doesn't mean anything. So that's unfortunately misleading. So I guess people, even from the beginning, got some things wrong when they didn't think through it completely. My next favorite guy is John Snow.
09:21
And it's not the Game of Thrones character. So he was a physician in London and there was this cholera epidemic, this huge cholera outbreak. And at the time, germ theory was known, but not widely accepted. And that's the theory that there exists germs
09:41
that get you sick. And if you accept germ theory, then you might wash your hands or clean things before you do surgery. And so there was this epidemic of cholera and he sort of suspected that there's maybe a source for this. And so he started tracking where people died,
10:01
these black lines. And he noticed there was, a lot of them were around the center area on Broad Street. And it turns out there's a water pump there that got contaminated. And so everyone who was drinking from that got sick. And by, what had happened was is
10:21
they had cesspits at the time, which were actually just underneath your house where you, sort of like a not very good septic tank. And one of them, some child got cholera and his diapers went into the thing and that leaked into the well. They didn't, and so this,
10:43
with this analysis, they figured out that that was the pump and the government cleaned it, but they'd never at the time gave him credit because the thought of someone's diapers in the water was too outlandish for the time. And so it wasn't until later that everything was all credited. He has, if you go to the area now,
11:00
there's actually like a pump statue commemorating it. So he did save a bunch of lives. There was one also fun part of the story is that there was a monastery nearby where it says a brewery there. And none of the monks in that monastery got sick. And that's because they didn't drink the water. They only drank the beer that they made themselves.
11:21
So that's pretty awesome. The next guy is Charles Joseph Menard. He has an awesome contribution to visualizations and that is this chart, which, does anyone recognize this? Yeah, so it's, this is the awesome.
11:42
It needs, if you haven't seen it before, it needs explanation, but the density of how much is there is just great. Edward Tufte said it might be the best graph ever made. And so what it is is the invasion,
12:00
the Napoleon invasion of Moscow and then the retreat. And what's, you can just see at the left here, so the width of the line is how many troops are at that point. They go forward to Moscow and then back. And you can just see how devastating this invasion was
12:23
just by comparing the start with and what came back at the end. They started with, it says here, 422,000 and came back with 10. So at every point, you can see, okay, so we had a branch break off to protect some other area.
12:40
They go forward, at every point they lose some troops. They get to Moscow, they turn around and the black line back. And then the black line back, at the bottom here, they track the temperature. And this winter was freezing, freezing cold. The units are in here, it's not Celsius, it's the Rhine, something with an R.
13:02
I've not used that temperature measure. Yeah, yeah. Which, I did the conversion to Celsius because it's pretty close to Celsius. And so this line here starts at zero and then just gets colder to negative 30. And so the lines here map the temperature and every time the temperature goes down,
13:23
more people get lost. And crossing this river, you can tell, is just awful. So it's awesome that this graph has so much. You can just look at it and see the story of what had happened.
13:42
But then this wouldn't be complete without Edward Tufte, modern day contemporary visualization expert. So he has these four books and I recommend you go out and get these if any of this is interesting. What's really great about these books is that he wanted, he's a big believer
14:02
in having very dense, high bandwidth information. And he couldn't find a publisher that would print his books the way that he wanted, so he made his own publishing company. And the paper, when you flip through these books, is just really thick and the colors are all very great.
14:22
And so I've been reading through, in preparation of this talk, rereading through one of them and reading through a couple of the other ones. And I want to share a couple of the good ideas that I thought were some of the most valuable to doing something for visualization of Postgres. And one thing that he talks about a lot
14:41
is the data ink ratio of a graph. So you need, if you took the overall ink they used to print a graph, some of it goes to displaying the information and some of it goes to the frames or the grid lines or whatever. And his idea is that you should minimize
15:01
to as much as you can the extra ink that doesn't actually give any information. So if you have a really fat bar graph but the width actually isn't signifying anything, like that's too much ink because you only really need how high it is and things like that.
15:24
Another thing he goes on a lot about is making sure that your visualizations are honest. Some people intentionally mislead with trying to distort what's going on. But even more subtle than that, I know I've misled with graphs by not including the time zone.
15:43
And that's just, you don't know if this was, is this Pacific time or is this UTC? Like it's probably UTC but are we sure? And that just causes so much intentionally being dishonest or not intentionally being dishonest is not as bad but still not great.
16:01
And one of the last things is, you just had this as a random sentence in it. You didn't make a big point of it but I thought it was pretty great that information consists of differences that make a difference. You can have a lot of noise in your visualizations so if you minimize that and just keep it to the most important differences
16:21
then you can get your point across much faster and with much more clarity. And the thing that brought him to my attention in the first place is that he made sparklines and I thought, the first time I saw it I was like, oh wait, there's no axis so this is useless. But I didn't understand it at the point
16:40
until I actually read through his analysis of sparklines and if you go to this tiny URL he's actually posted that part of his chapter for free so you can read through it. And this is an example from it. I couldn't get it prettier but what it's showing is index funds versus mutual funds
17:00
and you can see that over time they all, except for that one which is awful, approach the same return. And so this sort of proves the point that index funds are better than mutual funds because they have a lower expense ratio and they all perform the same anyway.
17:21
And the way that you do that is by having his idea of small multiples and so it's the same graph several times and so you can compare the differences between them much better than if each one of these were like in a grid of six you couldn't really line them up because they weren't his idea of small multiples.
17:47
So since I've been doing some visualizations on Postgres there's been one type of chart that I think everyone, of course everyone knows and another one that I found that I like a lot that's fairly uncommon.
18:02
And so the one everyone knows is the line chart. This is a graph from a service called Librato. I'm just curious, does anyone use Librato? Oh, okay. So Librato's pretty awesome. It's a hosted metrics collection service so you can send metrics to it. I sort of have a love-hate relationship with it.
18:22
It does its job very well but it's sometimes hard to work with and I think they get a couple things wrong. But we use it for a lot of stuff internally because it, so we don't have to build our own stuff at Heroku. And this is showing,
18:42
can I do this? No, okay. So another reason Librato isn't that good is it's hard to read on projectors. It looks great on a laptop. But the,
19:01
so there's lines. One of the things I don't like about it is because it is a grid of six things, you can't line up the current time. And so you have to go through and see if something is related. This is, if you go to Librato.com, you can see this example.
19:20
It's a live data from the RubyGems Bumba project that we run on Heroku and it sends the logs off to Librato. And they have, they're calculating some stuff from Postgres here, their Postgres database, their follower read replicate database, what the cache rate is on that, what, how many index scans are happening
19:41
and how many sequence scans or sequential scans. And that's okay. But one of the things that you have to make sure that you do before you start shoving data in a line graph is you're probably reducing your data down to one or two numbers to fit it on a line.
20:03
And the natural thing to do is to just do the average. But a lot of times that's completely wrong. One of the much better things to do if you look at, before you make your graph, look at a histogram of the data and see if it happens to be normal,
20:21
like okay, then use just an average. But a lot of times I've seen when you actually look at the histogram of your data, there'll be a lot of really quick things. Let's say you're doing web requests. This is a really important one for not using the average response time because a lot of your requests are gonna be very fast.
20:42
But then a handful of requests are gonna be very long and that bimodal sort of thing makes the average not representative of all. You really wanna look at percent 95 or percent 99 response time to get a better idea of how your system's actually performing.
21:02
So this next graph is actually my favorite. You can see there's, sorry, there's a paper introducing it that goes into detail of how it's constructed and stuff but it's pretty simple to understand. The, if you take a chart like this
21:22
and slice it into bands, then overlay the bands on top so that then you just have color differences and then you can either swap the negative things up to be offset or mirror them down. And what happens is you squish all of that information
21:42
into a much smaller space without losing resolution. So you can see the top here, these top two graphs are if you just did a normal area thing and squished it down to 30 pixels. The, all the steep changes in data on top are lost when you squished it down to 30 pixels
22:02
and you don't get the same story as if you took it from a horizon and squished it down and squished it down and squished it down. And the reason this works is that as humans we're really good at using changes in colors for determining large differences in data and position for very fine differences in data.
22:23
If you have position for large changes then they're too far away to compare and if you're trying to use color for small changes you can't really tell the differences in color because there's too many changes. But using both position and color for what they're good for using the squished horizon chart
22:42
then you can get much more information on the screen without sacrificing the actual quality of the data. This is just a random sinusoidal kind of generation but it shows that you can really spend a lot of time looking at this
23:01
and not just trying to track down what it means but you can really absorb the entire picture at once and that's, I built a thing that I'll show later on in this talk using horizon charts but I wanted to explain the theory of them first. All right, so Postgres. We're at a Postgres conference. Let's start getting more and more practical.
23:23
My favorite thing is PGSTAT statements. Has anyone used PGSTAT statements from 9.2 on? Okay, about half. So for those of you who haven't, PGSTAT statements been around I think since 8.4 initially by Takahiro Itagaki
23:43
but in Postgres 9.2 that was just recently released September, Peter Gagan did some work to, great work to normalize the queries and so before it would, PGSTAT statements would show you all the queries you ran but if you did,
24:01
like this is an example of the output that you get from PGSTAT statements, before if you did select clip versions where clip ID equals one, that would show a different row than clip ID equals two, equals three, equals four but it's much more useful if the things are all canonicalized onto one query
24:20
because they have, they're the same query with just different constants. You can see there's a couple more fields that I've omitted but you can, it's really at a broad scale useful is the total running time for your query but then you can also see how many blocks were read, how many blocks were written, how many blocks were cached, how many were dirty and so forth.
24:42
So you can do a fun thing with using LPAD a little fun to look at the total time that all your queries are taken, bin them up into bins and make a histogram and with a new feature in Postgres 9.3
25:00
which I'm most excited about, the backslash watch command in psql, you can see sort of a live histogram of your queries in like these ones are taken zero to 50 milliseconds, 50 to 100 and 100 and you can see, watch this if you think something's wrong, you can see like oh a lot of my queries are taking much more longer than usual.
25:24
There's a bunch of Postgres tables that show a lot of good internals that are all very well documented in the documentation and this is one of the boring parts that I cut out because just going over the Postgres documentation of the tables I didn't think was that interesting.
25:43
So you can look at these tables, go through the documentation, they have a lot of great information on how many transactions you're doing per time, if you're doing a lot of rollbacks, if you have bloated tables or bloated indices, your cache hit rates and stuff are all good things to look at.
26:00
One thing I did again with watch is to pull out a bunch of these metrics, stick them together with union alls and then just watch them happen and I on purpose locked a couple of my tables to see what would happen if it would screw up my connections if things would blow up. Connections stayed but progress started going down.
26:24
A collection of these, if you're looking for example queries to do a lot of these introspection things, this tool was made for if you're using a Heroku Postgres database but if you just go through the source code all it's actually doing is doing introspection queries and running them with psql.
26:42
So if you just go to this project and look at the source code you can see a good number of example queries of how to get the sort of introspection on your database. Okay so you have all that information that you want to do and next you want to design sort of a visualization system
27:03
and unfortunately it's not easy. There's not, like I looked into some of the research on like human visualization and I sort of gave up because it's just all across the board. I couldn't find like a real consensus of like oh yeah this is exactly what you should do and this isn't. And so really you have to build something
27:22
and then see how it feels and iterate from there. And you know that can be a little tough if you're everyone's engineers if you can't steal some designers time to like help you. You can sort of, usually what I do is I see something that I like and then copy it. But one of the things that also makes it hard
27:42
is that data in and of itself is an information. Like if you just take a metric and shove it into graphite or librato that probably isn't enough for two reasons. One, other people won't have the same context of like what this means. You know you do because you found it and put it there and you can you know train them
28:01
but you know just having it you know it's not going to be self-explanatory. The other thing is the metric in and of itself probably doesn't work. You're going to need to do some sort of conversion, correlate it with some other metric or do typically like derivatives over time. Like you want to see how this metric is changing
28:21
since the last time. Another thing is it's really easy especially with like graphite to just have too much stuff going on. This is bad for a couple reasons. One, you can see with the green there
28:40
it's really spiky. They have like five, six different colors going on. You can't really piece together what's going on except okay there's spikes. And one of the things that on that Berkeley site that vis.berkeley they have another paper that says that your slants for charts should be at or as close to 45 degrees as you can.
29:04
And so the reason that this is bad is the aspect ratio is way off because these lines are almost vertical. If you instead made this much longer so that you could see the increases and decreases, if you increase the aspect ratio such that it's 45 degrees, then it would be much better.
29:21
And I found this and this example of the bottom by just searching like example of broader graph or sorry not a broader example. So this is graphite, example graphite graph and like went through and I'm like oh that's terrible. The another thing is like these stacked ones are bad because changes in the bottom stack
29:40
pushes everything else out. So you can see the bottom one fine but the you know cumulative changes make the top end there like worthless. This also is the same problem the Labrada one does is that they weren't you know you have to bounce back and forth and switch context to see any sort of correlation
30:00
between these other metrics. So just a brief thing about you know picking colors for your visualizations. I found a good PDF of color rules there that I made a tiny URL. But in short what you want to do is your background should be gray or muted
30:21
so that your colors can don't have to compete with that. You want when you're picking colors you want to use like sort of softer colors that you know maybe would be found in nature like you know we've evolved to you know in nature so those colors are you know pop out a little more. For similar categories you should pick one hue
30:43
and vary the saturation but if you have completely distinct categories you want to you know pick different colors but it's better if you try and keep the same saturation so that one color isn't like dominating your attention for the other ones for no reason that you you know you picked a bad color.
31:00
So if you try and keep the same saturation but change the hue then all the colors stand on like an equal footing. The other thing that makes designing sort of a visualization system hard is that everyone has different intense and different needs. So I'm working soon on a visualization tool
31:20
for my Postgres customers but you know many of you in this room are probably DBAs or contractors and you each have different needs like a DBA you have you know a small you know to medium number of Postgres databases that you know intimately that you know very well you would have a different set
31:41
like you are actually tuning really hard for performance and so you know some of these other things would be more valuable to you than a contractor who sees you know lots of databases over a long period of time and you want to get in there quick and see what's going on but you don't have like a deep intuitive sense
32:00
of the system sort of taken to the extreme I'm trying to build a visualization thing for lots and lots and lots of customers that I will probably never see their visualization but they will see it and they're not you know not necessarily you know at all Postgres experts they just want to know why is my site slow
32:23
or is it my fault, is it your fault, do I need to upgrade, do I need to fix my app and answering those questions is different than other things. So generating all this data is tricky there's you know all those catalogs
32:41
that you can use all those tables but a really great talk did anyone see Peter Eisenstrat's talk yesterday on the logging hooks? Like that was very good there's a lot of data in that that can be pulled out and you know stored for visualization later. What was similar to that that I thought was interesting
33:01
is at Heroku we provide a log stream for all of your applications and your database and this 12factor.net site is sort of like a manifesto of how we think applications should be made and one of those factors is that logs are a stream not files.
33:23
Just having a bunch of log files that you like process later is you know that doesn't work forever. Instead if you treat your logs as a stream of data that you can process over time you know maybe you store it maybe you do one of the things you do is store it but you know more importantly
33:41
you can process these logs over time and notice things like that. There's some great papers on processing you know on infinite streams of data getting stuff off and getting like means for things and this other paper from the boundary guys on space saving
34:01
this algorithm is pretty awesome you can bound do a statistical bound on how much you're willing to throw away for counting things. So you won't take infinite memory but you get accurate counts and the ones that aren't accurate you know exactly how much error there is. The other thing that makes building
34:22
some of these systems tricky is storage. Ronald's talk yesterday also went into some great things on how you can store you know lots of time series data and you know doing that properly is a talk in and of itself and there's a lot of things you can consider
34:41
doing roll up of you know data over time and so you can diminish the space and doing table rotation strategies. And I saw I'm pretty excited the PG what is it? PG rotation manager extension
35:01
that someone did a lightning talk on yesterday I really want to look into that. Yeah, yeah that looks pretty awesome so I had not heard of that before so I immediately went I'm gonna check that out because we're building some you know table rotation stuff and having that you know done for me would be pretty awesome. You can easily though go really way overboard with storage
35:22
one of the things that I think I you know I love the Librato but they take storing all that time data like extremely seriously like you store a metric with them and they replicate it off to like you know across the continent and do all this crazy replication stuff but you know honestly if I lose you know five minutes of data in the middle
35:42
like it's probably fine for this like it would suck if there was a problem and that happened to be in that same five minutes but it probably won't be. So I think you know you know realizing that sort of trade-off and then designing for that is interesting like you know typically you know we want to store everything perfectly and never lose anything but when you start generating so much data
36:02
it's probably okay to use some. So then I want to go into an example of a tool that you can use on a Postgres 9.2 plus database called Datascope. And this if you look at this this uses that horizon chart and the charting library I didn't mention
36:21
is called Cubism and it's built on top of D3. And so this uses that this is an example of looking at all my connection counts my cache at rate and some other metrics that were easy to just throw up in a for a quick demo. And so I had my app and I scaled it up at 8 p.m. there.
36:42
You can see my connection kind of you know went way high and all the workers sort of doing a bunch of stuff so you can see at that same time you can see the selects like jump up how long they're taking jumps up because of some contention the inserts go way high. And then you can see again I purposely locked the tables
37:01
so it couldn't do anything and you can see what effect that had on everything else. And because all of these are stacked on top of each other you can see how everything how all those events are correlated. And I felt bad you know doing a talk with no code at all so I put some code in.
37:21
So this the way that this works is that there's a worker loop that is connected all the time to two databases. One the one that it's observing and then the second where it's storing this data. And it does an observation collects some information from PG stat statements and some other you know catalog tables. It resets PG stat statements and then sleeps.
37:43
Peter Gagan said I probably shouldn't be resetting the statistics all the time but it wasn't the it's just the PG stat statement statistics and it was the easiest thing to do. You know instead I could you know calculate the deltas and that would be you know fine. So it gets all that information stores it as JSON
38:00
with a timestamp and JSON and then it uses a custom it exposes a custom Cubism data source out of the box Cubism can connect to your graphite or one other source that I don't use. Or you can make your own data source to give it any sort of metric that you want.
38:22
The schema on the storage thing is really simple just an index on created at and an ID the JSON data and whatever because as I was building this you know one of the great things about the JSON data type is I was you know figuring out what things I needed to store and that you know the data from PG stat statements
38:41
you know has a lot of nested things like for each query I want to store all this information and so that made a good a good thing because I wasn't going to be doing queries on you know anything inside of it. The worker is super simple it you know builds up this JSON hash and then stores it into the database.
39:02
Yeah it doesn't you know it's pretty simple. The this is an example of how it's collecting the PG stat statements data getting out the things that are from other users and then for each query getting out the query name
39:21
the calls and the total time. I should also throw in I just didn't have time to fit it on the slide but it stores the blocks read and all that. And so that's an open source thing you can you know take a look at it point out your database and see if you notice any patterns over time I'm going to be working more on that and you know getting more detailed information
39:42
like you could drill down on it you know all the data is there to do it where you could drill down on an individual query and see how that was performing over time. And thanks.