Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

How a single PostgreSQL config change improved slow query performance by 50x

amplitude.engineering

Post reply on HN
Print view View original

31–40 of 46 posts

Re: How a single PostgreSQL config change improved slow query performance by 50x

#31

Post by brianwawok »

yipenghuang wrote: post #26
quizotic wrote: post #3

The prescription of changing sequential_page_cost to equal random_page_cost is certainly reasonable for SSD, but I wonder if the underlying issues aren't somewhat deeper and more interesting. One difference between a sequential scan and an index scan is the amount of data being scanned. PostgreSQL stores information horizontally as rows and a sequential scan will have to read in all column values of all rows. An inde…

A sequential scan could also be faster if the join selectivity is poor. As an extreme example say if every id in event_type appeared in prop_keys for a given app. So scanning the index repeatedly would be a waste of time since you would have to scan the table anyway.

Which is the point of stats, right? To know when it's better to use an index vs just read the whole table because you need 50%+ of it anyway.

Re: How a single PostgreSQL config change improved slow query performance by 50x

#32

Post by ric2b »

pgaddict wrote: post #24
emilfihlman wrote: post #23

Why aren't these tuned automatically? Should be pretty easy.

It sounds simple until you actually try doing that. The thing is, reducing the costing to these two parameters is a significantly simplified model of what happens in practice. So you can't just run some I/O benchmark to measure random vs. sequential requests. For example the defaults that worked fine for a long time (seq_page_cost=1 and random_page_cost=4) certainly do not reflect the difference between random and se…

> For example the defaults that worked fine for a long time (seq_page_cost=1 and random_page_cost=4) certainly do not reflect the difference between random and sequential I/O on rotational devices (where the device can easily do 100MB/s in sequential access, but less than 1MB/s in random)

The postresql documentation explains why. They assume HDD random access is 40x slower than seq access but that you'll have a 90% cache hit rate, so random_page_cost=4 reflects 10% of 40x slower.

Re: How a single PostgreSQL config change improved slow query performance by 50x

#33

Post by ahachete »

aargh_aargh wrote: post #22
ahachete wrote: post #5

For those interested in postgresql.conf tuning, I did a recent presentation at two conferences recently, hope it's interesting. Slides: https://speakerdeck.com/ongres/postgresql-configuration-for-... [shamless plug]

There's a link to postgresqlco.nf in the description. Do you run the service? It doesn't accept a file being dropped. In fact, it doesn't seem to do anything (I looked at the HTML).

It's a WIP. It's coming, stay tuned... (yo may subscribe to get noticed).

Re: How a single PostgreSQL config change improved slow query performance by 50x

#34

Post by ahachete »

lccarrasco wrote: post #7
ahachete wrote: post #5

For those interested in postgresql.conf tuning, I did a recent presentation at two conferences recently, hope it's interesting. Slides: https://speakerdeck.com/ongres/postgresql-configuration-for-... [shamless plug]

They look great, thanks for sharing! Do you know if the talks were recorded?

pgconf.eu it wasn't recorded. HighLoad++ it was, but I think not published yet.

Re: How a single PostgreSQL config change improved slow query performance by 50x

#35

Post by agnivade »

power wrote: post #17
quizotic wrote: post #4

Earlier quoted context omitted.

+1 for the idea of tuning query optimization based on ML. I don't know of any DBMSs that take this advice, and that's remarkable in this day and age.

Postgres has a query optimizer that uses Genetic Algorithms: https://www.postgresql.org/docs/current/static/geqo.html Clarification: this is for planning how to execute a query, not for tuning the db settings

Wow, did not know that. TIL!

Re: How a single PostgreSQL config change improved slow query performance by 50x

#36

Post by pgaddict »

davidcuddeback wrote: post #30
danjoc wrote: post #2

Thanks for the tip. This probably affects more people than there are people who realize it. It would be really interesting if PG would use machine learning to discover this sort of tuning on its own.

PostgreSQL does have built-in support for a genetic algorithm based query optimizer. I haven't tried it yet, so can't comment on how well it works. Docs are here: https://www.postgresql.org/docs/9.6/static/geqo.html .

The idea is that up until some number of relations (8 by default, IIRC) the join tree is searched exhaustively, then it switches to the genetic algorithm. So it's kinda automatic.

Re: How a single PostgreSQL config change improved slow query performance by 50x

#37

Post by pgaddict »

ric2b wrote: post #32
pgaddict wrote: post #24

Earlier quoted context omitted.

It sounds simple until you actually try doing that. The thing is, reducing the costing to these two parameters is a significantly simplified model of what happens in practice. So you can't just run some I/O benchmark to measure random vs. sequential requests. For example the defaults that worked fine for a long time (seq_page_cost=1 and random_page_cost=4) certainly do not reflect the difference between random and se…

> For example the defaults that worked fine for a long time (seq_page_cost=1 and random_page_cost=4) certainly do not reflect the difference between random and sequential I/O on rotational devices (where the device can easily do 100MB/s in sequential access, but less than 1MB/s in random) The postresql documentation explains why. They assume HDD random access is 40x slower than seq access but that you'll have a 90% c…

Not entirely. The documentation says you can interpret it that way, not that it's how the numbers were determined.

AFAIK it's much more "We're using those numbers as defaults because they seem to be working well," rather than "We did extensive benchmarking and these are the right values!"

You can measure how much slower random I/O is fairly easily. But the question is how to derive PostgreSQL cost parameters from that. Should you use the same assumption about 90% cache hit ratio (why?) or should you use some different value?

Re: How a single PostgreSQL config change improved slow query performance by 50x

#38

Post by pgaddict »

pja wrote: post #9
stubish wrote: post #6

You also get good results tweaking this particular knob if you have large amounts of RAM. If your blocks are almost always in OS cache, you are almost never going to make random seeks even if the PostgreSQL planner thinks you are.

That feels like something the database could do for itself - checking whether a range of mmap()ed blocks are actually in memory or not is a single syscall. I guess for large indexes the overhead of walking the page tables is going to be large though, so it’s not necessarily going to be a net win.

But you don't know which blocks you'll need at planning time, so you can't really check that.

You could of course check if the total database size is within RAM, but it's much more common to have database much larger than RAM (say 1TB on a machine with 128GB of RAM), but the actual working set (recent data processed by queries) is much smaller.

Re: How a single PostgreSQL config change improved slow query performance by 50x

#39

Post by pgaddict »

cesarb wrote: post #29
quizotic wrote: post #3

The prescription of changing sequential_page_cost to equal random_page_cost is certainly reasonable for SSD, but I wonder if the underlying issues aren't somewhat deeper and more interesting. One difference between a sequential scan and an index scan is the amount of data being scanned. PostgreSQL stores information horizontally as rows and a sequential scan will have to read in all column values of all rows. An inde…

PostgreSQL uses 8K pages, so it won't read less than that. IIRC, it also has its own cache of recently read pages, so it won't have to read the same page over and over.

You can rebuild it with smaller pages, including 4kB, which may be beneficial for various reasons. The packages however stick to 8kB.

And yes, the database has it's own cache (aka shared buffers), on top of page cache (filesystem cache).

Re: How a single PostgreSQL config change improved slow query performance by 50x

#40

Post by pgaddict »

rpedela wrote: post #28
pgaddict wrote: post #27

Unfortunately the author does not say some pretty basic things - which PostgreSQL version, how much data, how much of it fits into RAM, what storage (and hardware in general) ... If I understand it correctly, PostgreSQL was using the default configuration. Which is rather inefficient, and is more about "must start everywhere". Decreasing random_page_cost makes sense if you have storage that can handle random I/O well…

Setting random_page_cost = 1 is pretty common advice for SSD and works well in my experience. Typical advice for HDD RAID or SCSI is random_page_cost = 2 and SSDs are faster than them.

I don't know who recommends random_page_cost=1, but IMNSHO it's a bit silly. Even SSDs handle sequential I/O better than random I/O. Values between 1.5 and 2.0 are more appropriate. I wouldn't really recommend 1.0 except when you know the data fits into RAM. There are other options that affect costs of random I/O, e.g. effective_cache_size.
Post reply on HN