> On May 20, 2019, at 5:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Well, you're the only one who's seen this problem, and none of the
> rest of us have any idea how to reproduce it. So if you want something
> to get done in a timely fashion, it's up to you to show us a test case.
So, we had success.
We looked in to maintenance_work_mem.
By default, this value is 64MB.
Our test on AWS was against a small machine, 2 CPU, 16G.
My VM is however many cores it gets (not that it matters) and 4G of RAM.
My VM used the 64MB value for maintenance_work_mem. The AWS small VM used 247M.
We killed the rebuild on the small machine, it was pushing 67 hours.
I was running a load on my machine, and it was still making progress over night, but was at, like 17 hours. But it was
moving,but had only loaded 2.2M rows in that time.
We grabbed one of the huge AWS instances. 64 CPU, 488G of ram. Just, silly.
But, that’s fine — I’m good with silly.
It’s mainteance_work_mem was ~8G.
And we loaded all of my files on that instance in about 5.5 hours, about 9M rows per hour.
So, obviously, maintenance_work_mem was the smoking gun. Since I don’t know the details of GIN indexing, its not clear
tome how the maintenance_work_mem is utilized with GIN index builds, but, obviously 64M is “not enough”, nor is 247M.
And8G is certainly enough.
We’re cautious just setting these values “to 11” because of the fact that multiple connections can utilize them, so it
seemsto me that it’s important that they be “generous enough”, but not overly generous.
So this is good news, no bug, and, perhaps, left to its own devices, the DB would have eventually built this index.
Whetherit would have done so before universal heat death, is a different question.
Can anyone discuss how the maintenance_work_mem is utilized during GIN index creation? On our production systems, this
valueis set to 1G. And we don’t seem to have any problems for day to day work. This is a very busy table, and we have
thousandsof inserts/updates daily which seem to proceed well enough. I have not tried to rebuild this index on this
system,so I can’t say if 1G is enough to rebuild this index efficiently or not. But its efficient enough for our
transactionload.
Now, that said, should I ever be in that situation of having to recover this table like this, I’d have no problem
crankingthat value up high since it would be the only real connection on the system anyway.
But I’m still curious how the memory is utilized during index builds just to have a better understanding of the nuances
ofthe system.
Thanks all for your help.
