Thread: Estimate maintenance_work_mem for CREATE INDEX
(cross-posting admin and hackers)
Hello,
I wonder if I'm alone in my wish to have a way for estimating how much maintenance work memory would suffice to allocate for a session when creating an index and avoid spilling to disk?
Recently I had to re-create some indexes on a 9.6 server and I had some input on the on-disk index size: one was around 30 GB, the other -- a bit over 60 GB according to \di+ output. The total number of live tuples in the table itself was close to 1.3e+9, the table had an estimated 25% bloat.
I had some spare memory on the machine so I've given it 60 GB for maintenance_work_mem and expected that at least the smaller of the two will fit in memory completely. To my surprise that didn't suffice and both indexes were building with some disk spill.
Is anyone aware of a query to estimate the memory requirements for CREATE INDEX [CONCURRENTLY]?
I've looked in the postgres wiki, but didn't find anything to that end. Nor searching the archives of pgsql-admin did help.
I understand that there were some changes in recent releases related to memory allocation (e.g. allowing huge allocation in 9.4), but at least targeting 9.6 or 10 would make sense. There are also a lot of ways how one CREATE INDEX can be different from the other, but in the most simple case where you have fixed-width columns and building the full index (i.e. no WHERE clause), it should be possible.
Not hasting to look in the source to calculate all the sizeof()s yet: waiting on your reply and suggestions. ;-)
Cheers!
-- Oleksandr "Alex" Shulgin | Database Engineer | Zalando SE | Tel: +49 176 127-59-707
On Tue, Dec 19, 2017 at 10:47 AM, Oleksandr Shulgin <oleksandr.shulgin@zalando.de> wrote:
(cross-posting admin and hackers)Hello,I wonder if I'm alone in my wish to have a way for estimating how much maintenance work memory would suffice to allocate for a session when creating an index and avoid spilling to disk?Recently I had to re-create some indexes on a 9.6 server and I had some input on the on-disk index size: one was around 30 GB, the other -- a bit over 60 GB according to \di+ output. The total number of live tuples in the table itself was close to 1.3e+9, the table had an estimated 25% bloat.I had some spare memory on the machine so I've given it 60 GB for maintenance_work_mem and expected that at least the smaller of the two will fit in memory completely. To my surprise that didn't suffice and both indexes were building with some disk spill.Is anyone aware of a query to estimate the memory requirements for CREATE INDEX [CONCURRENTLY]?I've looked in the postgres wiki, but didn't find anything to that end. Nor searching the archives of pgsql-admin did help.I understand that there were some changes in recent releases related to memory allocation (e.g. allowing huge allocation in 9.4), but at least targeting 9.6 or 10 would make sense. There are also a lot of ways how one CREATE INDEX can be different from the other, but in the most simple case where you have fixed-width columns and building the full index (i.e. no WHERE clause), it should be possible.
Now I see I fail to mention this is the default btree index with all default options. Obviously other indexes can be very different in memory requirements.
Not hasting to look in the source to calculate all the sizeof()s yet: waiting on your reply and suggestions. ;-)
If there would be an option in the database itself to provide those estimation, we wouldn't even need to figure out estimation queries. "EXPLAIN CREATE INDEX" anyone?
Regards,
-- Oleksandr "Alex" Shulgin | Database Engineer | Zalando SE | Tel: +49 176 127-59-707
On Tue, Dec 19, 2017 at 10:47 AM, Oleksandr Shulgin <oleksandr.shulgin@zalando.de> wrote:
(cross-posting admin and hackers)Hello,I wonder if I'm alone in my wish to have a way for estimating how much maintenance work memory would suffice to allocate for a session when creating an index and avoid spilling to disk?Recently I had to re-create some indexes on a 9.6 server and I had some input on the on-disk index size: one was around 30 GB, the other -- a bit over 60 GB according to \di+ output. The total number of live tuples in the table itself was close to 1.3e+9, the table had an estimated 25% bloat.I had some spare memory on the machine so I've given it 60 GB for maintenance_work_mem and expected that at least the smaller of the two will fit in memory completely. To my surprise that didn't suffice and both indexes were building with some disk spill.Is anyone aware of a query to estimate the memory requirements for CREATE INDEX [CONCURRENTLY]?I've looked in the postgres wiki, but didn't find anything to that end. Nor searching the archives of pgsql-admin did help.I understand that there were some changes in recent releases related to memory allocation (e.g. allowing huge allocation in 9.4), but at least targeting 9.6 or 10 would make sense. There are also a lot of ways how one CREATE INDEX can be different from the other, but in the most simple case where you have fixed-width columns and building the full index (i.e. no WHERE clause), it should be possible.
Now I see I fail to mention this is the default btree index with all default options. Obviously other indexes can be very different in memory requirements.
Not hasting to look in the source to calculate all the sizeof()s yet: waiting on your reply and suggestions. ;-)
If there would be an option in the database itself to provide those estimation, we wouldn't even need to figure out estimation queries. "EXPLAIN CREATE INDEX" anyone?
Regards,
-- Oleksandr "Alex" Shulgin | Database Engineer | Zalando SE | Tel: +49 176 127-59-707
On 19 December 2017 at 10:00, Oleksandr Shulgin <oleksandr.shulgin@zalando.de> wrote: > If there would be an option in the database itself to provide those > estimation, we wouldn't even need to figure out estimation queries. > "EXPLAIN CREATE INDEX" anyone? You're not the first to propose something like that. I think an EXPLAIN ALTER TABLE would also be very handy -- it's currently impossible to tell without carefully reading the source code whether a given DDL change will require a full table scan, a full table rewrite, or just a quick meta data update (and even in that case what strength lock will be required). I think there are other utility statements that make interesting heuristic decisions that would be nice to be able to have some visibility into -- CLUSTER comes to mind. I'm not clear how you would determine how much memory is needed to sort a table without actually doing the sort though. So that would be more of an EXPLAIN ANALYZE wouldn't it? -- greg
On 19 December 2017 at 10:00, Oleksandr Shulgin <oleksandr.shulgin@zalando.de> wrote: > If there would be an option in the database itself to provide those > estimation, we wouldn't even need to figure out estimation queries. > "EXPLAIN CREATE INDEX" anyone? You're not the first to propose something like that. I think an EXPLAIN ALTER TABLE would also be very handy -- it's currently impossible to tell without carefully reading the source code whether a given DDL change will require a full table scan, a full table rewrite, or just a quick meta data update (and even in that case what strength lock will be required). I think there are other utility statements that make interesting heuristic decisions that would be nice to be able to have some visibility into -- CLUSTER comes to mind. I'm not clear how you would determine how much memory is needed to sort a table without actually doing the sort though. So that would be more of an EXPLAIN ANALYZE wouldn't it? -- greg
> On Dec 19, 2017, at 7:14 AM, Greg Stark <stark@mit.edu> wrote: > > I'm not clear how you would determine how much memory is needed to > sort a table without actually doing the sort though. So that would be > more of an EXPLAIN ANALYZE wouldn't it? I'd think EXPLAIN could use a quite rough approximation, say based on a rather sparse random sampling of values. EXPLAINANALYZE might involve a whole table scan, or might involve more dense sampling. -- Scott Ribe https://www.linkedin.com/in/scottribe/ (303) 722-0567
On Tue, Dec 19, 2017 at 3:15 PM Greg Stark <stark@mit.edu> wrote:
On 19 December 2017 at 10:00, Oleksandr Shulgin
<oleksandr.shulgin@zalando.de> wrote:
> If there would be an option in the database itself to provide those
> estimation, we wouldn't even need to figure out estimation queries.
> "EXPLAIN CREATE INDEX" anyone?
You're not the first to propose something like that. I think an
EXPLAIN ALTER TABLE would also be very handy -- it's currently
impossible to tell without carefully reading the source code whether a
given DDL change will require a full table scan, a full table rewrite,
or just a quick meta data update (and even in that case what strength
lock will be required). I think there are other utility statements
that make interesting heuristic decisions that would be nice to be
able to have some visibility into -- CLUSTER comes to mind.
Yes, that would be pretty handy.
I'm not clear how you would determine how much memory is needed to
sort a table without actually doing the sort though. So that would be
more of an EXPLAIN ANALYZE wouldn't it?
My idea would be to use statistic. So that EXPLAIN CREATE INDEX (or whatever the actual interface could be like) would benefit from up-to-date statistic produced by ANALYZE.
Based on the estimated number of rows in the table, average width of column(s) to index and taking into account the bookkeeping structures one should be able to arrive at a good guess for the amount of memory the backend would end up allocating (assuming it is available).
Having done that, as the first step, and using statistic again we could also infer (though, probably with less accuracy) memory requirements for building partial indexes. Functional indexes would be harder to tackle, I would think this is only possible if the return type(s) of the function(s) has all fixed width.
I didn't look in the code, but I imagine the procedure to read -> sort -> spill to tapes, if needed -> merge sort the tapes is generic to all index types, so this shouldn't be a breaking change for any user-defined indexes (is this already a thing?). OK, maybe it's only generic for B-Tree and BRIN, but not for GIN and GiST, to name a few. Damn, I gotta look in the code at some point. ;-)
To let me fantasize a little more, what I would also love to see is the estimated on-disk size for the resulting index, before starting to create it. This is obviously dependent on the actual index type and options, such as fill-factor, etc.
Cheers,
--
Alex
On Tue, Dec 19, 2017 at 3:15 PM Greg Stark <stark@mit.edu> wrote:
On 19 December 2017 at 10:00, Oleksandr Shulgin
<oleksandr.shulgin@zalando.de> wrote:
> If there would be an option in the database itself to provide those
> estimation, we wouldn't even need to figure out estimation queries.
> "EXPLAIN CREATE INDEX" anyone?
You're not the first to propose something like that. I think an
EXPLAIN ALTER TABLE would also be very handy -- it's currently
impossible to tell without carefully reading the source code whether a
given DDL change will require a full table scan, a full table rewrite,
or just a quick meta data update (and even in that case what strength
lock will be required). I think there are other utility statements
that make interesting heuristic decisions that would be nice to be
able to have some visibility into -- CLUSTER comes to mind.
Yes, that would be pretty handy.
I'm not clear how you would determine how much memory is needed to
sort a table without actually doing the sort though. So that would be
more of an EXPLAIN ANALYZE wouldn't it?
My idea would be to use statistic. So that EXPLAIN CREATE INDEX (or whatever the actual interface could be like) would benefit from up-to-date statistic produced by ANALYZE.
Based on the estimated number of rows in the table, average width of column(s) to index and taking into account the bookkeeping structures one should be able to arrive at a good guess for the amount of memory the backend would end up allocating (assuming it is available).
Having done that, as the first step, and using statistic again we could also infer (though, probably with less accuracy) memory requirements for building partial indexes. Functional indexes would be harder to tackle, I would think this is only possible if the return type(s) of the function(s) has all fixed width.
I didn't look in the code, but I imagine the procedure to read -> sort -> spill to tapes, if needed -> merge sort the tapes is generic to all index types, so this shouldn't be a breaking change for any user-defined indexes (is this already a thing?). OK, maybe it's only generic for B-Tree and BRIN, but not for GIN and GiST, to name a few. Damn, I gotta look in the code at some point. ;-)
To let me fantasize a little more, what I would also love to see is the estimated on-disk size for the resulting index, before starting to create it. This is obviously dependent on the actual index type and options, such as fill-factor, etc.
Cheers,
--
Alex
On Tue, Dec 19, 2017 at 11:14 PM, Greg Stark <stark@mit.edu> wrote: > You're not the first to propose something like that. I think an > EXPLAIN ALTER TABLE would also be very handy -- it's currently > impossible to tell without carefully reading the source code whether a > given DDL change will require a full table scan, a full table rewrite, > or just a quick meta data update (and even in that case what strength > lock will be required). I think there are other utility statements > that make interesting heuristic decisions that would be nice to be > able to have some visibility into -- CLUSTER comes to mind. An application of such things is attempting to estimate the amount of disk space needed when doing a schema upgrade, so that could be handy. -- Michael
On Tue, Dec 19, 2017 at 11:14 PM, Greg Stark <stark@mit.edu> wrote: > You're not the first to propose something like that. I think an > EXPLAIN ALTER TABLE would also be very handy -- it's currently > impossible to tell without carefully reading the source code whether a > given DDL change will require a full table scan, a full table rewrite, > or just a quick meta data update (and even in that case what strength > lock will be required). I think there are other utility statements > that make interesting heuristic decisions that would be nice to be > able to have some visibility into -- CLUSTER comes to mind. An application of such things is attempting to estimate the amount of disk space needed when doing a schema upgrade, so that could be handy. -- Michael