71–80 of 89 posts
This is great! But I just installed the latest Debian with Postgres 15, haha. I don't even think I'm using any features past 11 (websearch_to_tsquery), so I'll need to research anything new that might be useful to me.
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…
I use io-uring with O_DIRECT at work and the performance graphs of TLB pressure are beautiful.
This is great! But I just installed the latest Debian with Postgres 15, haha. I don't even think I'm using any features past 11 (websearch_to_tsquery), so I'll need to research anything new that might be useful to me.
Our infra team decided to deploy v12 in the last year... But only for GitLab. We are still deploying with v9.6 for some other new projects.
Earlier quoted context omitted.
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…
But back to my first question, I am still curious what your workload is that you feel will benefit from direct io :)
Earlier quoted context omitted.
> 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.
Earlier quoted context omitted.
> 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 l…
That sounds like an anecdote from the time before SSDs.
> "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. "
If your application is told that the transactions were committed, but your DB actually hasn't, you got a problem, methinks.
I am so glad psql got \bind. What good does EXPLAIN do if you're not running the same (parameterized) queries that your app does? Very cool.
Earlier quoted context omitted.
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 l…
> If you want full data guarantees, with regular hard drives, you'd be looking at less than 200 transactions per second. That sounds like an anecdote from the time before SSDs. > "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…
Thing is DB work is all about tradeoffs, so you'll never be free of them.
As the parent explained, PostgreSQL actually lets you make a very good trade: you get full cached level performance but lose only a couple of transactions at most, instead of data corruption. If that's not ok for you, the leash can be tightened to lose nothing, but then your hardware had better keep up with your platform.
Earlier quoted context omitted.
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…
The thread is large, maybe I read the whole thing at one time, but the point of it is literally someone asking why using direct io is slower. That is the point I make, you can't just enable direct io (which by passes all the logic to speed up reads and writes) and expect increased performance without a lot of extra up front work. But back to my first question, I am still curious what your workload is that you feel wi…