On Sat, Jul 24, 2021 at 11:23:25AM -0700, James Bottomley wrote: > On Sat, 2021-07-24 at 19:14 +0100, Matthew Wilcox wrote: > > On Sat, Jul 24, 2021 at 11:09:02AM -0700, James Bottomley wrote: > > > On Sat, 2021-07-24 at 18:27 +0100, Matthew Wilcox wrote: > > > > What blows me away is the 80% performance improvement for > > > > PostgreSQL. I know they use the page cache extensively, so it's > > > > plausibly real. I'm a bit surprised that it has such good > > > > locality, and the size of the win far exceeds my > > > > expectations. We should probably dive into it and figure out > > > > exactly what's going on. > > > > > > Since none of the other tested databases showed more than a 3% > > > improvement, this looks like an anomalous result specific to > > > something in postgres ... although the next biggest db: mariadb > > > wasn't part of the tests so I'm not sure that's > > > definitive. Perhaps the next step should be to t > > > est mariadb? Since they're fairly similar in domain (both full > > > SQL) if mariadb shows this type of improvement, you can > > > safely assume it's something in the way SQL databases handle paging > > > and if it doesn't, it's likely fixing a postgres inefficiency. > > > > I think the thing that's specific to PostgreSQL is that it's a heavy > > user of the page cache. My understanding is that most databases use > > direct IO and manage their own page cache, while PostgreSQL trusts > > the kernel to get it right. > > That's testable with mariadb, at least for the innodb engine since the > flush_method is settable. We're still not communicating well. I'm not talking about writes, I'm talking about reads. Postgres uses the page cache for reads. InnoDB uses O_DIRECT (afaict). See articles like this one: https://www.percona.com/blog/2018/02/08/fsync-performance-storage-devices/ : The first and most obvious type of IO are pages reads and writes from : the tablespaces. The pages are most often read one at a time, as 16KB : random read operations. Writes to the tablespaces are also typically : 16KB random operations, but they are done in batches. After every batch, : fsync is called on the tablespace file handle. (the current folio patch set does not create multi-page folios for writes, only for reads) I downloaded the mariadb source package that's in Debian, and from what I can glean, it does indeed set O_DIRECT on data files in Linux, through os_file_set_nocache(). > > Regardless of whether postgres is "doing something wrong" or not, > > do you not think that an 80% performance win would exert a certain > > amount of pressure on distros to do the backport? > > Well, I cut the previous question deliberately, but if you're going to > force me to answer, my experience with storage tells me that one test > being 10x different from all the others usually indicates a problem > with the benchmark test itself rather than a baseline improvement, so > I'd wait for more data. ... or the two benchmarks use Linux in completely different ways such that one sees a huge benefit while the other sees none. Which is what you'd expect for a patchset that improves the page cache and using a benchmark that doesn't use the page cache.
