Обсуждение: Q: regarding backends
Hi all, and sorry if I'm asking a question that has been answered before; has the PostgreSQL community ever considered different key/value backends (sort of like MySQL with its many different options)? We'd be very interested in seeing the effects of integrating LMDB [*] in terms of performance gains. Has this avenue been explored before? Thanks, Stephan [*] http://symas.com/mdb/
Stephan Fabel <sfabel@hawaii.edu> wrote: > has the PostgreSQL community ever considered different key/value > backends (sort of like MySQL with its many different options)? > > We'd be very interested in seeing the effects of integrating LMDB > in terms of performance gains. You might want to consider writing a Foreign Data Wrapper (FDW) to allow tables defined in LMDB to be accessed from PostgreSQL queries. http://wiki.postgresql.org/wiki/Foreign_data_wrappers -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Mon, 09 Dec 2013 06:20:41 -1000 Stephan Fabel <sfabel@hawaii.edu> wrote: > Hi all, > > and sorry if I'm asking a question that has been answered before; has the > PostgreSQL community ever considered different key/value backends (sort of like > MySQL with its many different options)? > > We'd be very interested in seeing the effects of integrating LMDB [*] in terms > of performance gains. Has this avenue been explored before? I have to say that I'm VERY happy that there's been little to no focus on supporting different backend storage in PostgreSQL. I am forced to manage a significant amount of data in MySQL. The number of restrictions in MySQL and the number of problems with MySQL that I can either directly or indirectly attribute to the decision to support multiple storage backends is phenominal. In my opinion, MySQL has far too much of a seperation betweeen MySQL itself and it's engines (innodb being the most common). This has resulted in: * Overly complex configuration * Performance issues * Overly complex diagnosis of performance issues * A brittle, unreliable system * Outright broken features (such as transactions that aren't guaranteed to be transactional) For me, this is a database system, not a reality TV show. I'd much rather have the focus stay on a tightly integrated, reliable system than have a bunch of weird choices that can improve my performance by .5% while causing unexpected breakage. As an example, the ARCHIVE engine in MySQL is frequently touted as being much more efficient storage than innodb, but it does this at the loss of indexing, foreign keys, and transactional integrety. Why don't the MySQL developers take time to make innodb's compression work better (it's horrible at this time) instead of creating a completely different engine that throws away important fuctionality? I suspect this is part of the reason the PG developers have avoided making dozens of storage engines and instead focused on making the one that we have the best one possible. -- Bill Moran <wmoran@potentialtech.com>
2013/12/10 Stephan Fabel <sfabel@hawaii.edu>: > Hi all, > > and sorry if I'm asking a question that has been answered before; has the > PostgreSQL community ever considered different key/value backends (sort of like > MySQL with its many different options)? > > We'd be very interested in seeing the effects of integrating LMDB [*] in terms > of performance gains. Has this avenue been explored before? There was talk of pluggable storage at this year's PGCon (PGUncon?): https://wiki.postgresql.org/wiki/2013UnconfPluggableStorage but even if the idea gains traction, it's not going to arrive any time soon. As Kevin mentions, FDWs might provide a good alternative. An example with key/value stores I've been citing recently is this one: http://blog.cloudflare.com/kyoto_tycoon_with_postgresql Regards Ian Barwick
On Tue, Dec 10, 2013 at 5:49 AM, Bill Moran <wmoran@potentialtech.com> wrote: > On Mon, 09 Dec 2013 06:20:41 -1000 Stephan Fabel <sfabel@hawaii.edu> wrote: > >> Hi all, >> >> and sorry if I'm asking a question that has been answered before; has the >> PostgreSQL community ever considered different key/value backends (sort of like >> MySQL with its many different options)? >> >> We'd be very interested in seeing the effects of integrating LMDB [*] in terms >> of performance gains. Has this avenue been explored before? > > I have to say that I'm VERY happy that there's been little to no focus on > supporting different backend storage in PostgreSQL. > > I am forced to manage a significant amount of data in MySQL. The number of > restrictions in MySQL and the number of problems with MySQL that I can > either directly or indirectly attribute to the decision to support multiple > storage backends is phenominal. In my opinion, MySQL has far too much of > a seperation betweeen MySQL itself and it's engines (innodb being the most > common). This has resulted in: > * Overly complex configuration > * Performance issues > * Overly complex diagnosis of performance issues > * A brittle, unreliable system > * Outright broken features (such as transactions that aren't guaranteed to > be transactional) This. mysql (not to bash, but...) has several misfeatures but storage backends have got to be the worst (query cache would be close second but at least you can turn that off): it hides the internal details of the record storage from the query planner and various other SQL level features such as RI. It's somewhat analogous to *only* having the FDW API (plus some extensions) to access data. Very much agree with Kevin: exotic storage can now live there and that's where you should be looking. It's going to have some severe constraints relative to what regular tables can do but that should slowly resolve over time. merlin
On Tuesday, December 10, 2013 06:49:01 AM you wrote: > On Mon, 09 Dec 2013 06:20:41 -1000 Stephan Fabel <sfabel@hawaii.edu> wrote: > > We'd be very interested in seeing the effects of integrating LMDB [*] in > > terms of performance gains. Has this avenue been explored before? > > I have to say that I'm VERY happy that there's been little to no focus on > supporting different backend storage in PostgreSQL. Fair enough... > For me, this is a database system, not a reality TV show. I'd much rather > have the focus stay on a tightly integrated, reliable system than have a > bunch of weird choices that can improve my performance by .5% while causing > unexpected breakage. Hardly .5%... - see http://symas.com/mdb/microbench/ I do agree, however, that it is definitively better to focus on one thing and do it right rather than get lost in a bunch of random choices. The reason I asked was that it is currently being adopted by a lot of other open source projects, so I was curious to see what the PostgreSQL community's take on it was. Cheers, Stephan
Stephan Fabel <sfabel@hawaii.edu> wrote: > On Tuesday, December 10, 2013 06:49:01 AM you wrote: >> I'd much rather have the focus stay on a tightly integrated, >> reliable system than have a bunch of weird choices that can >> improve my performance by .5% while causing unexpected breakage. > > Hardly .5%... - see http://symas.com/mdb/microbench/ I see that the benchmarks were run on a laptop with storage systems which are not typical of what one would see on a database server, so it is hard to predict how things would shake out on a real server. Those are some intriguing numbers, though. > I do agree, however, that it is definitively better to focus on > one thing and do it right rather than get lost in a bunch of > random choices. The reason I asked was that it is currently being > adopted by a lot of other open source projects, so I was curious > to see what the PostgreSQL community's take on it was. Did you look at the FDW page, and in particular the "black hole" template FDW? If someone familiar with LMDB wanted to fill in the stubs from that template to provide a working lmdbfdw extension, I would bet that there are people who would be interested in running a few of our standard benchmarks against real server hardware to see how it performs. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company