PyCon 2014

Cómo mejorar el rendimiento de PostgreSQL

Craig Kerstiens  · 




Extracto de la transcripción automática del vídeo realizada por YouTube.

okay good afternoon hike on I hope you enjoyed your lunch and the poster session there and the jobs fair I am just a couple of announcements before we get started here and I would just like to remind all the auction winners to pick up their winnings if you

want an item at the auction last night please go down to the registration desk right now to pick it up it will be closing soon and secondly I'd like to mention the Sprint's after the conference ends will be holding development Sprint's Sprint's

or a great chance to get your first patch accepted to your favorite library and meet the authors and get introduced the open source contribution so stick around after the closing messages for an introduction to the sprint or just show up here on Monday and

if you want to lead a sprint please prepare a two minute description to get people excited about sprinting on your project it's our okay well it says two minutes on you okay it is now 20 seconds okay I am to get people excited about sprinting on your project

you'll be able to present that after the lightning talks okay I am so next up we have Craig kirstens and he's going to be telling us about postgres performance for humans take it away Thanks hi everyone so I'm Craig car scenes I work at Heroku

I run product for a couple of teams for our data team which runs Heroku postgres and our languages team I basically have the job of doing whatever the engineers don't want to do is the way I describe it so really quickly to start off some some quick and

shameless plugs and throughout this talk it's gonna be a little bit of a shotgun approach i'm going to throw kind of a lot of various information at you a lot to get through so it'll kind of be a little bit disparate at times but some shameless

plugs i curate an email newsletter called post for us weekly if you're an end user an app developer and use postgres hopefully it has useful content i blogged a good bit postgres guide is kind of a guide for you know getting started up to some interesting

cool things in Postgres if you're on a map Mac use postgres top don't use macports or homebrew if you've already got it great keep using it but if you run into problems with it which you will give postgres tap a try and then I work at Heroku we

run a lot of postgres so usually when I give quite a few talks and usually when i talk about you know postgres and why it's great i put up a slide that looks kind of like this this is all the awesome features in Postgres if you don't know about something

on the slide google it every single thing here you should probably know about and use or have the ability to use it sometime to make this easy for for twitter this is a quote by a co-worker basically post-crisis becoming more and more of a data platform then

it is just a relational database there's full-text search there she is there's about everything you could want to do in there and it's really becoming a much more flexible platform overall so kind of getting into a little bit more of the the meat

when you think about database works loads there's kind of two ways there's a lap and a little teepee so oltp is essentially web apps that's you know web applications Shango flask small writes and reads transactional workload olap is business reporting

bi large report over a lot of data you're looking at MPP type solutions here and I'm essentially not going to talk about any of this really in the talk so if you're looking at this a you know to get kind of advice on data warehousing stuff happy

to shot afterwards but hopefully most of the people in this room where our web app developers actually it's show hands inside the case for most people developing web apps ok cool so right off against things good on setup and config I'm not going to

spend a whole lot of time here since you only have to do it once another short of it is if you're on Amazon use Heroku or go check out this talk last year I think from Pike on um it's definitely been giving it a lot of Jenga conferences postgresql

when it's not your day job by Christophe Pettis it's a great talk basically there's about 30 configs you need to set up once once you do it you're done so start there and essentially don't worry about it because amazon is sort of virtualized

infrastructure on top of virtualized infrastructure on top of more virtualization it's a little different if you're on any other cloud a lot of them are a good bit different you get a little bit more predictability and disk and that kind of thing the

best bet is postgresql when it's not your day job so you're setting a bunch of configs they're a little different it'll basically get you up and running and then if you're on real hardware high performance postgresql this looks a little

old now it's based on postgres 90 but if you've got SSDs fusion-io really kind of any real hardware this is still really applicable I think it's being upgraded for updated 493 but essentially this is where you want to start and there's a link

to the slides as well so um any time I come and look at a new database the first thing I want to do is like take the 30,000 foot view of how is this doing doesn't need more hardware is it just screwed how about are we doing and the number one thing that

any web app should care about is the the cash so postgres is really good at keeping frequently accessed data in memory I know you think you're going to be better at cashing your data than postgres will you won't go ahead and try it's really good

at this so essentially you're going to aim for a pretty high like ninety-nine percent cache hit ratio that means it's serving data out of memory ninety-nine percent of the time and one percent attending disk to get that you run this super simple query

and it's going to give you something that looks like this basically don't try to memorize the query you figure out what it's doing it's crazy internal post-press stuff some of the columns actually don't make sense but that's going to

give you you know the cache hit ratio and like I said ninety-nine percent or higher is what you're aiming for if you don't have that you probably want to throw more memory at the box that's the easiest way to start scaling your database so index

hit is another one how often are you using indexes for not so this is definitely important for web applications if you're talking about bi and reporting applications it's less common but what I want to see is you know heavy index usage I want things

[ ... ]

Nota: se han omitido las otras 3.221 palabras de la transcripción completa para cumplir con las normas de «uso razonable» de YouTube.