Обсуждение: RFC/WIP: adding new configuration options to TOAST
Looking for feedback to see if anyone sees any issues or has any suggestions on what I'm doing. The attached patch alters 3 things with regard to TOAST behavior: 1) Add a GUC target_compression_ratio: When attempting to compress a datum in the TOAST code, only stored the compressed version if it saves at least target_compression_ratio% space. 2) Add a constant COMPRESSION_TEST_SIZE: If a datum is larger than this size, initially COMPRESSION_TEST_SIZE bytes are compressed, and the entire datum is only compressed if the test compression indicates that it might be worth it. The goal is to avoid the CPU of compressing a large value that isn't going to save space anyway. 3) Add a GUC target_tuple_size: which exposes the "fit at least 4 tuples on a page" logic as a configurable value. The value is exposed as a maximum desired size (instead of the target tuples to fit on a page) because that seems like a more intuitive configuration option to me. If this seems to be on track, then my next step is to make these values configurable on a per-table basis. I'm tracking my work on github, if that's easier to review than the patch for anyone: https://github.com/williammoran/postgres -- Bill Moran
Вложения
On 3 November 2015 at 23:04, Bill Moran <wmoran@potentialtech.com> wrote: > > Looking for feedback to see if anyone sees any issues or has any > suggestions on what I'm doing. The attached patch alters 3 things > with regard to TOAST behavior: COMPRESSION_TEST_SIZE (2) seems useful. The other two mostly seem like options nobody's going to know are there, or know how to sensibly set if they do notice them. What's the driving reason behind those, the problem you're trying to solve? Why make them configurable per-table (or at all)? -- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On Tue, Nov 3, 2015 at 5:21 PM, Craig Ringer <craig@2ndquadrant.com> wrote: > On 3 November 2015 at 23:04, Bill Moran <wmoran@potentialtech.com> wrote: >> >> Looking for feedback to see if anyone sees any issues or has any >> suggestions on what I'm doing. The attached patch alters 3 things >> with regard to TOAST behavior: > > COMPRESSION_TEST_SIZE (2) seems useful. > > The other two mostly seem like options nobody's going to know are > there, or know how to sensibly set if they do notice them. What's the > driving reason behind those, the problem you're trying to solve? Why > make them configurable per-table (or at all)? I currently have a table with one column which has a median width of 500 bytes, a 90th percentile of 650 bytes, and makes up 75% of the table's size, and the column is rarely used, while the table itself is frequently seq scanned. I'd very much like to drive that column out of main and into toast. I think target_tuple_size would let me do that. (Per-column control would be even nicer, but I'd take what I can get) Cheers, Jeff
On 11/3/15 8:34 PM, Jeff Janes wrote: > I currently have a table with one column which has a median width of > 500 bytes, a 90th percentile of 650 bytes, and makes up 75% of the > table's size, and the column is rarely used, while the table itself is > frequently seq scanned. I'd very much like to drive that column out > of main and into toast. I think target_tuple_size would let me do > that. +1 on having a way to induce that behavior, as I've faced the same thing in the past. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com
On Tue, 3 Nov 2015 18:34:39 -0800 Jeff Janes <jeff.janes@gmail.com> wrote: > On Tue, Nov 3, 2015 at 5:21 PM, Craig Ringer <craig@2ndquadrant.com> wrote: > > On 3 November 2015 at 23:04, Bill Moran <wmoran@potentialtech.com> wrote: > >> > >> Looking for feedback to see if anyone sees any issues or has any > >> suggestions on what I'm doing. The attached patch alters 3 things > >> with regard to TOAST behavior: > > > > COMPRESSION_TEST_SIZE (2) seems useful. > > > > The other two mostly seem like options nobody's going to know are > > there, or know how to sensibly set if they do notice them. What's the > > driving reason behind those, the problem you're trying to solve? Why > > make them configurable per-table (or at all)? > > I currently have a table with one column which has a median width of > 500 bytes, a 90th percentile of 650 bytes, and makes up 75% of the > table's size, and the column is rarely used, while the table itself is > frequently seq scanned. I'd very much like to drive that column out > of main and into toast. I think target_tuple_size would let me do > that. That's exactly the use case. As it currently stands, any tuple smaller than about 2K will never be toasted. So if you have 1900 bytes of highly compressible text that is infrequently queried from the table whilst other columns are frequently accessed, there's no way to force it to be out of line from the main table, or be compressed. The two new configurables allow the DBA to make tradeoff decisions on CPU usage vs. storage efficiency. Since the TOAST code attempts to process the column that will provide the largest gain first, in your described use case you could calculate the size of the other columns, and set the target_tuple_size to just a bit larger than that, and that large column should get moved into the toast table in most or all cases (depending on how predictable the other sizes are) Compression is a similarly hard-coded value in current versions. I feel that allowing the DBA to control how much savings is required before incurring the overhead of compression is worthwhile, especially when considered on a per-table basis. For example, the compression on an archive table could be very aggressive, whereas compression on a frequently accessed table might only be justified if it saves a lot of space. How much space compression saves is highly dependent on the data being stored. I don't have anything remotely like statistical advice on how much improvement can actually be gained yet. Once I have per-table values implemented, it will be much, much easier to test the impact. It does sound like I need to spend a little more time improving the documentation to ensure that it's clear what these values achieve. > (Per-column control would be even nicer, but I'd take what I can get) Oddly, I hadn't considered getting as granualar as per-column, but now that you've got me thinking about it, it seems like a logical step to take. -- Bill Moran
On 4 November 2015 at 10:58, Bill Moran <wmoran@potentialtech.com> wrote: > On Tue, 3 Nov 2015 18:34:39 -0800 > Jeff Janes <jeff.janes@gmail.com> wrote: > >> On Tue, Nov 3, 2015 at 5:21 PM, Craig Ringer <craig@2ndquadrant.com> wrote: >> > On 3 November 2015 at 23:04, Bill Moran <wmoran@potentialtech.com> wrote: >> >> >> >> Looking for feedback to see if anyone sees any issues or has any >> >> suggestions on what I'm doing. The attached patch alters 3 things >> >> with regard to TOAST behavior: >> > >> > COMPRESSION_TEST_SIZE (2) seems useful. >> > >> > The other two mostly seem like options nobody's going to know are >> > there, or know how to sensibly set if they do notice them. What's the >> > driving reason behind those, the problem you're trying to solve? Why >> > make them configurable per-table (or at all)? >> >> I currently have a table with one column which has a median width of >> 500 bytes, a 90th percentile of 650 bytes, and makes up 75% of the >> table's size, and the column is rarely used, while the table itself is >> frequently seq scanned. I'd very much like to drive that column out >> of main and into toast. I think target_tuple_size would let me do >> that. > > That's exactly the use case. As it currently stands, any tuple smaller > than about 2K will never be toasted. So if you have 1900 bytes of > highly compressible text that is infrequently queried from the table > whilst other columns are frequently accessed, there's no way to force > it to be out of line from the main table, or be compressed. Ok, so that's the underlying issue to solve. Make smaller tuples TOASTable, especially when quite compressible. Shouldn't that be column-level, really? We have SET STORAGE at the moment. Would some sort of "FORCE" option to SET STORAGE EXTERNAL meet your needs? It'd potentially force small data out of line too, but that'd make sense for your rarely accessed use case. > The two new configurables allow the DBA to make tradeoff decisions on > CPU usage vs. storage efficiency. Since the TOAST code attempts to > process the column that will provide the largest gain first, in your > described use case you could calculate the size of the other columns, > and set the target_tuple_size to just a bit larger than that, and > that large column should get moved into the toast table in most or > all cases (depending on how predictable the other sizes are) I'm just concerned that this is another knob that 0.001% of the user base will know about and use correctly, 1% will use incorrectly based on some cargo-culted nonsense they pick up somewhere, and the rest will have no clue exists. We have more than a few of those already. The way you describe using a GUC here makes it sound like what you really want is just a column storage option, and that twiddling a GUC like this is a workaround to try to make one column more aggressively compressed and moved out of line without affecting the others. > Compression is a similarly hard-coded value in current versions. > I feel that allowing the DBA to control how much savings is required > before incurring the overhead of compression is worthwhile, especially > when considered on a per-table basis. For example, the compression > on an archive table could be very aggressive, whereas compression on > a frequently accessed table might only be justified if it saves a lot > of space. How much space compression saves is highly dependent on the > data being stored. Yes, I can see value in making attempts at compression more (or less) aggressive. In that regard it's a pity the pluggable compression support didn't go anywhere really, because the current algorithm is cpu-cheap at the cost of pretty poor compression ratios. >> (Per-column control would be even nicer, but I'd take what I can get) > > Oddly, I hadn't considered getting as granualar as per-column, but > now that you've got me thinking about it, it seems like a logical > step to take. I think per-column is really where it makes sense, if it's to be done at all. At least based on the use cases given. -- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On Wed, 4 Nov 2015 13:07:09 +0800 Craig Ringer <craig@2ndquadrant.com> wrote: > On 4 November 2015 at 10:58, Bill Moran <wmoran@potentialtech.com> wrote: > > On Tue, 3 Nov 2015 18:34:39 -0800 > > Jeff Janes <jeff.janes@gmail.com> wrote: > > > >> On Tue, Nov 3, 2015 at 5:21 PM, Craig Ringer <craig@2ndquadrant.com> wrote: > >> > On 3 November 2015 at 23:04, Bill Moran <wmoran@potentialtech.com> wrote: > >> >> > >> >> Looking for feedback to see if anyone sees any issues or has any > >> >> suggestions on what I'm doing. The attached patch alters 3 things > >> >> with regard to TOAST behavior: > >> > > >> > COMPRESSION_TEST_SIZE (2) seems useful. > >> > > >> > The other two mostly seem like options nobody's going to know are > >> > there, or know how to sensibly set if they do notice them. What's the > >> > driving reason behind those, the problem you're trying to solve? Why > >> > make them configurable per-table (or at all)? > >> > >> I currently have a table with one column which has a median width of > >> 500 bytes, a 90th percentile of 650 bytes, and makes up 75% of the > >> table's size, and the column is rarely used, while the table itself is > >> frequently seq scanned. I'd very much like to drive that column out > >> of main and into toast. I think target_tuple_size would let me do > >> that. > > > > That's exactly the use case. As it currently stands, any tuple smaller > > than about 2K will never be toasted. So if you have 1900 bytes of > > highly compressible text that is infrequently queried from the table > > whilst other columns are frequently accessed, there's no way to force > > it to be out of line from the main table, or be compressed. > > Ok, so that's the underlying issue to solve. Make smaller tuples > TOASTable, especially when quite compressible. > > Shouldn't that be column-level, really? > > We have SET STORAGE at the moment. Would some sort of "FORCE" option > to SET STORAGE EXTERNAL meet your needs? It'd potentially force small > data out of line too, but that'd make sense for your rarely accessed > use case. I'm not discounting the potential value of column-level tunables. But I don't feel that their potential value devalues table-level and cluster-wide tunables. As the code currently stands, TOASTing is completely skipped for tuples smaller than 2k, which means that FORCE* storage types would need to behave differently than other storage options. Not off the table, of course, but it seemed unintuitive to me. A lot of this was discussed previously in the threads linked here: https://github.com/williammoran/postgres/blob/master/README One important point is that it's not 100% clear that these tunables are worthwhile (as you mention) but there's no way to be sure until there is a prototype in place that can be used for testing ... which is what this is all about (at this point, anyway). Getting to the point where there are table-level tunables allows for convenient testing (i.e. having two tables with different values and the same data, and not having to restart/recreate them for each value to be tested). > > The two new configurables allow the DBA to make tradeoff decisions on > > CPU usage vs. storage efficiency. Since the TOAST code attempts to > > process the column that will provide the largest gain first, in your > > described use case you could calculate the size of the other columns, > > and set the target_tuple_size to just a bit larger than that, and > > that large column should get moved into the toast table in most or > > all cases (depending on how predictable the other sizes are) > > I'm just concerned that this is another knob that 0.001% of the user > base will know about and use correctly, 1% will use incorrectly based > on some cargo-culted nonsense they pick up somewhere, and the rest > will have no clue exists. We have more than a few of those already. From my perspective, I've seen people misunderstand and misuse the simplest of things, so I don't hold to the idea that just because its a little complicated means it's a bad idea. Certainly, it needs to be as understandable as possible. This page will certianly need a significant rewrite: http://www.postgresql.org/docs/9.5/static/storage-toast.html To me, the important factor is whether these tunables can make Postgres more valuable to a skilled administrator. If they can, then the fact that a cargo-cult exists to misuse them is simply entertainment to me. It ensures that companies like 2ndquadrant will always have work fixing other people's mistakes ;) > The way you describe using a GUC here makes it sound like what you > really want is just a column storage option, and that twiddling a GUC > like this is a workaround to try to make one column more aggressively > compressed and moved out of line without affecting the others. Not really. The fact that I've only listed one case doesn't mean it's the only one. Perhaps I need to enumerate all the potential benefits somewhere ... but when I first researched and proposed this project, I didn't see anywhere near the level of disagreement that I'm seeing now. I'll try to enumerate all the potential benefits and get them on a web page somewhere today. > > Compression is a similarly hard-coded value in current versions. > > I feel that allowing the DBA to control how much savings is required > > before incurring the overhead of compression is worthwhile, especially > > when considered on a per-table basis. For example, the compression > > on an archive table could be very aggressive, whereas compression on > > a frequently accessed table might only be justified if it saves a lot > > of space. How much space compression saves is highly dependent on the > > data being stored. > > Yes, I can see value in making attempts at compression more (or less) > aggressive. In that regard it's a pity the pluggable compression > support didn't go anywhere really, because the current algorithm is > cpu-cheap at the cost of pretty poor compression ratios. This work had to be done before pluggable compression could be implemented anyway. What value is there to switching compression algorithms if they never get used for tuples under 2k and you have no way to change that? On a related note, since I've been digging in this area of code I feel comfortable commenting that pluggable compression isn't terribly difficult to implement. The most significant work will probably be in actually implementing the various algorithms, not in making them pluggable. I've been considering making that my next project. > >> (Per-column control would be even nicer, but I'd take what I can get) > > > > Oddly, I hadn't considered getting as granualar as per-column, but > > now that you've got me thinking about it, it seems like a logical > > step to take. > > I think per-column is really where it makes sense, if it's to be done > at all. At least based on the use cases given. Per column is certianly possible, but as you mention, it's still not clear that these changes are valuable at all. Additionally, the code required to make per-column tunables will be mostly implemented in the process of making per-table tunables, and I can always just rip out the GUC definitions and clean up the code if that actually seems like the best result. Overall, I don't feel like you're actually disagreeing with anything I'm doing ... you're just wishing I was already further along. -- Bill Moran
On 4 November 2015 at 20:48, Bill Moran <wmoran@potentialtech.com> wrote: > On a related note, since I've been digging in this area of code I feel > comfortable commenting that pluggable compression isn't terribly difficult > to implement. The most significant work will probably be in actually > implementing the various algorithms, not in making them pluggable. I've > been considering making that my next project. IIRC the biggest roadblocks for pluggable compression were around binary upgrade and around the per-Datum or per-tuple space cost of identifying which algorithm is used. But I guess that's getting off-track. > Overall, I don't feel like you're actually disagreeing with > anything I'm doing ... you're just wishing I was already further > along. Indeed. I'm concerned about the extra complexity exposed to users and unsure about GUCs as an interface, but I can see being able to encourage earlier and more aggressive compression as potentially quite handy. -- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services