Обсуждение: gin creation and previous history of server
It seems that gin creation is triggering something nasty in the server that depends on previous history of the server. If I vacuum full than drop the index and recreate it even with maintenance_work_mem='200MB' index creation may take forever. Stopping the execution may make vacuuming very slow or stopping the server very slow etc... I know that the server may be cleaning the new partially created index etc... but gin creation even with 200MB of maintenance_work_mem is having a too strange and to big hit on the server. I can *occasionally* succede to create a gin index in reasonable time if I shut down the server, vacuum full and drop and recreate the index separately from the transaction that load the data. gist creation is *predictably* much faster even inside the transaction that load the data and doesn't have side effect outside index creation. Even when gin creation succede it is definitively slower than 3x gist/gin index creation looks more cpu bounded than memory bounded. I'm checking if I made some mistake in other cfg parameters that may have some impact on index creation... Any further clue? -- Ivan Sergio Borgonovo http://www.webthatworks.it
Ivan Sergio Borgonovo <mail@webthatworks.it> writes:
> It seems that gin creation is triggering something nasty in the
> server that depends on previous history of the server.
Can you put together a self-contained test case that illustrates this?
regards, tom lane
On Tue, 04 Nov 2008 10:33:26 -0500 Tom Lane <tgl@sss.pgh.pa.us> wrote: > Ivan Sergio Borgonovo <mail@webthatworks.it> writes: > > It seems that gin creation is triggering something nasty in the > > server that depends on previous history of the server. > > Can you put together a self-contained test case that illustrates > this? I'm trying... Tonight I just let my long transaction run all night. It has been running for about 10h and it blocked on index re-creation. During all this runs: shared_buffers='120MB'; maintenance_work_mem='200MB'; Stopped the server. dropped and recreated the index outside a transaction: ~6 minutes. Stopped the server, created a transaction that just drop and rebuild the index, still below 10 minutes. gist index creation outside the transaction ~2min gist index creation inside the transaction ~2min After transaction committed but other connection: vacuum full drop index recreate as gin index: ~6min I even: begin; drop index recreate all tsvectors create gin index commit; and times are still in the ~6min so it must be some other part of the transaction having side effects. In one batch (same connection): vacuum full; transaction don't recreate gin inside the transaction commit vacuum analyze; recreate gin: dead! stopped the server again in one batch (same connection): vacuum full; transaction don't recreate gin inside the transaction commit vacuum analyze; recreate gin: dead! OK... some clue... run the batch, close the transaction etc... ** open a new connection ** create the gin index... work! I noticed that when I open a new connection to build the index CPU usage doesn't go to 100%. I'm not completely convinced that it's just a problem of new connection I've no sufficient statistical data to prove correlation... but it is hard to collect since the transaction is long and made of several pieces, and I can record a failure just after waiting at least 6min for each index rebuild. I'll try further to see if I can collect more data to see if there is any chance there is a bug in PostgreSQL, but considering how much time it is required for each test, you'll have to be patient. Now I could avoid to drop the index at the beginning of the transaction. tsvectors are updated with a trigger and I'm disabling the trigger at the beginning of the transaction, so tsvectors aren't going to be changed during the transaction. But several rows are going to be deleted. I'm wondering if I leave the gin index there, without dropping it... will it have impact on performances considering that a LOT of rows are going to be deleted. So I could just drop and recreate the index in another connection inside a transaction. -- Ivan Sergio Borgonovo http://www.webthatworks.it
Ivan Sergio Borgonovo <mail@webthatworks.it> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Can you put together a self-contained test case that illustrates
>> this?
> I'm trying... Tonight I just let my long transaction run all night.
> It has been running for about 10h and it blocked on index
> re-creation.
I'd suggest just loading up some dummy data in a medium-size table and
seeing if you can reproduce the variance in index build time. It's
unlikely that you need a ten-hour test case for that. But there may
be some small detail of what you're doing that is necessary to show
the problem, so I'm not going to try to reproduce it here with no
concrete example to go on.
regards, tom lane
On Wed, 05 Nov 2008 10:53:38 -0500 Tom Lane <tgl@sss.pgh.pa.us> wrote: > Ivan Sergio Borgonovo <mail@webthatworks.it> writes: > > Tom Lane <tgl@sss.pgh.pa.us> wrote: > >> Can you put together a self-contained test case that illustrates > >> this? > > > I'm trying... Tonight I just let my long transaction run all > > night. It has been running for about 10h and it blocked on index > > re-creation. > > I'd suggest just loading up some dummy data in a medium-size table > and seeing if you can reproduce the variance in index build time. > It's unlikely that you need a ten-hour test case for that. But > there may be some small detail of what you're doing that is > necessary to show the problem, so I'm not going to try to > reproduce it here with no concrete example to go on. I'm still testing... I'm starting to think that it is not a coincidence that every time I rebuild the index in another *connection*, no matter what I did to the DB before, the index get rebuilt in around less than 7min (generally much less!). Nearly all the time the index is built in the same *connection* of the transaction, no matter if inside the transaction or not... it takes forever... I've started to disassemble the transaction and add to a new script a bit at a time. I can't conclude if it is some leak somewhere or a specific part of the transaction that has some side effect since: - starting from an empty transaction and adding back something works - taking away something from the whole transaction doesn't work so it can be something I still haven't stripped away or it could be I didn't contribute enough to the leak to trigger the problem. As soon as I'll be able to find out which part of the whole transaction (or if just the accumulation of operations made in it) trigger the problem I'll report it back. Meanwhile I'm putting more heavily under test the hypothesis that the problem is linked to connections, since that seems have solved the problem and found its more stable place in the code. -- Ivan Sergio Borgonovo http://www.webthatworks.it