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

PostgreSQL 16

postgresql.org

Post reply on HN
Print view View original

61–70 of 89 posts

Re: PostgreSQL 16

#61

Post by mattashii »

ptrwis wrote: post #57
esaym wrote: post #53

Earlier quoted context omitted.

Curious what your use case is for wanting direct_io? Every DBA I've ever worked with when setting up a new database, the first thing they want to do is enable direct io. My worst experience was with IBM DB2 mounted over NFS talking to netapp. Performance complaints would come from customers and land on the CEO's desk. He'd go to the software team and tell them to fix it. They'd say the DB is slow. Then he'd go to the…

I'm thinking lower resource usage (no double caching of data), shorter path to data so I would expect fewer bad things might happen during commit, and better performance in terms of transactions per second. Otherwise, I can't explain it any better than one of the lead developers himself: https://www.postgresql.org/message-id/20210223100344.llw5an2...

The new debug_io_direct flag only triggers direct IO in very limited cases, and is only tangentially related to the AIO patchset discussed in that thread.

Note that the documentation on the config flag explicitly warns about not using it in production:

> Currently this feature reduces performance, and is intended for developer testing only.

Also note that very few things will actually do IO during commit - the only IO that I can think of are 1.) the WAL-logging of the commit (often small, a few 100 bytes at most), and 2.) replying to the COMMIT command (10s of bytes at most). It is quite unlikely that this will see much performance benefit from IO_DIRECT without further infrastructure inside PostgreSQL around io_uring and other async kernel IO apis.

Re: PostgreSQL 16

#62

Post by patmorgan23 »

zozbot234 wrote: post #18
pas wrote: post #13

Earlier quoted context omitted.

schools either never worked or stopped working. after all, look, almost all the people who went to school don't give a fuck about underperforming underfunded school systems. (or healthcare or ... or if they care they are ignorant and clueless about what to do with the problem, and easily fell prey to political dogma of some group.) it's simply time to stop worrying about it.

School systems are not underfunded. They're underperforming despite being overly funded. We're throwing good money after bad.

Teachers are under funded in the US. In many states if your lucky teachers wages top out at the median income. And they often have little to no funding for class room decorations or much beyond very basic materials.

If we don't pay our teachers decently how can we expect them to put in the effort to do the very difficult task of raising the next generation? (And yes teachers raise their students just as much as parents do)

Re: PostgreSQL 16

#63

Post by esaym »

mattashii wrote: post #56
esaym wrote: post #53

Earlier quoted context omitted.

Curious what your use case is for wanting direct_io? Every DBA I've ever worked with when setting up a new database, the first thing they want to do is enable direct io. My worst experience was with IBM DB2 mounted over NFS talking to netapp. Performance complaints would come from customers and land on the CEO's desk. He'd go to the software team and tell them to fix it. They'd say the DB is slow. Then he'd go to the…

> Did a minimum amount of tuning, mostly just turning off fsync for WAL writes That is not something I would suggest to people on production systems, as that would give you a good chance of data loss when the system halts. So, out of interest, were there any circumstances why turning off WAL fsync was considered a good choice in your situation?

I probably meant to say "synchronous_commit", which is how data is written to disk from the WAL. If you want full data guarantees, with regular hard drives, you'd be looking at less than 200 transactions per second. You set synchronous_commit to off, and suddenly you can do 10k transactions per second. You can tune when the WAL gets flushed to disk based on time and/or size. So you can set the amount of recent data loss you are comfortable with.

From the docs: "setting this parameter to off does not create any risk of database inconsistency: an operating system or database crash might result in some recent allegedly-committed transactions being lost, but the database state will be just the same as if those transactions had been aborted cleanly. So, turning synchronous_commit off can be a useful alternative when performance is more important than exact certainty"

Re: PostgreSQL 16

#64

Post by garenp »

esaym wrote: post #53
ptrwis wrote: post #49

I can't wait for direct I/O (now behind debug_io_direct setting).

Curious what your use case is for wanting direct_io? Every DBA I've ever worked with when setting up a new database, the first thing they want to do is enable direct io. My worst experience was with IBM DB2 mounted over NFS talking to netapp. Performance complaints would come from customers and land on the CEO's desk. He'd go to the software team and tell them to fix it. They'd say the DB is slow. Then he'd go to the…

The 1k packets you saw probably correspond to the default block size being used for the DB, that is a vestige of using spinning disks. That you were using NFS or any kind of networked filesystem is what I'd say is a performance hostile environment. Did no one think of just not using NFS?

Re: PostgreSQL 16

#65

Post by esaym »

ptrwis wrote: post #57
esaym wrote: post #53

Earlier quoted context omitted.

Curious what your use case is for wanting direct_io? Every DBA I've ever worked with when setting up a new database, the first thing they want to do is enable direct io. My worst experience was with IBM DB2 mounted over NFS talking to netapp. Performance complaints would come from customers and land on the CEO's desk. He'd go to the software team and tell them to fix it. They'd say the DB is slow. Then he'd go to the…

I'm thinking lower resource usage (no double caching of data), shorter path to data so I would expect fewer bad things might happen during commit, and better performance in terms of transactions per second. Otherwise, I can't explain it any better than one of the lead developers himself: https://www.postgresql.org/message-id/20210223100344.llw5an2...

Yes when direct io is brought up, it is usually followed with the "double buffering" argument. That is valid, but only if your disk speeds are well above 1,000MB/s. Outside of hardware like that, you are always going to be waiting on disk.

From Linus himself[0]

"The thing that has always disturbed me about O_DIRECT is that the whole interface is just stupid, and was probably designed by a deranged monkey on some serious mind-controlling substances"

[0] https://lkml.org/lkml/2002/5/11/58

Re: PostgreSQL 16

#66

Post by esaym »

garenp wrote: post #64
esaym wrote: post #53

Earlier quoted context omitted.

Curious what your use case is for wanting direct_io? Every DBA I've ever worked with when setting up a new database, the first thing they want to do is enable direct io. My worst experience was with IBM DB2 mounted over NFS talking to netapp. Performance complaints would come from customers and land on the CEO's desk. He'd go to the software team and tell them to fix it. They'd say the DB is slow. Then he'd go to the…

The 1k packets you saw probably correspond to the default block size being used for the DB, that is a vestige of using spinning disks. That you were using NFS or any kind of networked filesystem is what I'd say is a performance hostile environment. Did no one think of just not using NFS?

This was before 2012, AWS did not exist. The company had to find rackspace in a data center. Which we couldn't. One of the funding customers "loaned" us a couple of slots in their on premise data center which fit only a bladecenter and single netapp. NFS had advantages, you could dynamically resize live mount points, etc. Plus as I mentioned, using postgresql did away with our performance issues.

Re: PostgreSQL 16

#67

Post by ptrwis »

mattashii wrote: post #61
ptrwis wrote: post #57

Earlier quoted context omitted.

I'm thinking lower resource usage (no double caching of data), shorter path to data so I would expect fewer bad things might happen during commit, and better performance in terms of transactions per second. Otherwise, I can't explain it any better than one of the lead developers himself: https://www.postgresql.org/message-id/20210223100344.llw5an2...

The new debug_io_direct flag only triggers direct IO in very limited cases, and is only tangentially related to the AIO patchset discussed in that thread. Note that the documentation on the config flag explicitly warns about not using it in production: > Currently this feature reduces performance, and is intended for developer testing only. Also note that very few things will actually do IO during commit - the only I…

I know, but it's still nice to see progress in this area. Even PG17 would probably be too early to expect this work to be finished.

Re: PostgreSQL 16

#68

Post by efxhoy »

paulddraper wrote: post #50
adql wrote: post #45

Earlier quoted context omitted.

...okay ? What's the use case where data shrinks ? The data that will be "not removed" will just be used by new data. Only real use case is "we've loaded way too many data, removed it, and want to recover that space because we will never need it", and that is not enough to matter, as usually database have its own filesystem and most filesystems can't be shrunk online so any shrinking needs downtime

> What's the use case where data shrinks ? Parent literally provided one: deleting a column. Another is that it's very easy in PostgreSQL to bloat indexes. Load a bunch of data. Update (or delete) that data and now your index is bloated. The only resolution is to REINDEX (or VACUUM FULL).

The nice thing about index bloat is REINDEX has a CONCURRENTLY option, no need to block writes.

Re: PostgreSQL 16

#70

Post by ptrwis »

esaym wrote: post #65
ptrwis wrote: post #57

Earlier quoted context omitted.

I'm thinking lower resource usage (no double caching of data), shorter path to data so I would expect fewer bad things might happen during commit, and better performance in terms of transactions per second. Otherwise, I can't explain it any better than one of the lead developers himself: https://www.postgresql.org/message-id/20210223100344.llw5an2...

Yes when direct io is brought up, it is usually followed with the "double buffering" argument. That is valid, but only if your disk speeds are well above 1,000MB/s. Outside of hardware like that, you are always going to be waiting on disk. From Linus himself[0] "The thing that has always disturbed me about O_DIRECT is that the whole interface is just stupid, and was probably designed by a deranged monkey on some seri…

Have you read the entire thread you linked? People explained to Linus why direct IO is important to them. Besides, it's 20 years old and there were even no SSDs back then. The lack of direct IO in PG was one of (one of) the reasons why Uber moved to MySQL (https://www.uber.com/en-PL/blog/postgres-to-mysql-migration/, "The Buffer Pool" Section). With buffered IO you will likely store a lot of the same data in memory twice- once in DB's memory and then in page cache. Now you can just give the memory used by page cache directly to DB, because it knows better what and when it needs.
Post reply on HN