Re: [HACKERS] Improve the concurency of vacuum full table and select statement on the same relation
The lock upgrade for vacuum full table tends to cause deadlock with other lock upgrade transaction which is from AccessShareLock to lockmode > AccessShareLock. Tom Lane's concern is that it will cause vacuum full failed after do a lot of work. But If we can always let other transaction failed to break deadlock instead of vacuum full table, how about this lock upgrade solution? For example: we can enlarge the 'DeadlockTimeout' for vacuum full table transaction to avoid deadlock check. Jinyu Zhang regards At 2015-10-16 23:04:51, "Robert Haas" wrote: >On Thu, Oct 15, 2015 at 8:28 PM, Jim Nasby wrote: >> It's just how the authors of pg_repack decided to handle it. It seems pretty >> reasonable, since you probably don't want an errant DDL statement to cause >> the rollback of hours or days of pg_repack work. >> >> Ultimately, I don't think you'll find many people interested in working on >> this, because the whole goal is to never need VACUUM FULL or pg_repack. If >> you're clustering just for the sake of clustering, that has it's own set of >> difficulties that should be addressed. > >I think the topic of online table reorganization is a pretty important >one, actually. That is a need that we have had for a long time, >creates serious operational problems for users, and it's also a need >that is not going away. I think the chances of eliminating that need >completely, even if we rearchitected or heap storage, are nil. > >I think the bigger issue is that it's a very hard problem to solve. >pg_repack is one approach, but I've heard more than one person say >that, as C-3PO said about the asteroid, it may not be entirely stable. > >-- >Robert Haas >EnterpriseDB: http://www.enterprisedb.com >The Enterprise PostgreSQL Company
Re: [HACKERS] Patch: Optimize memory allocation in function 'bringetbitmap'
Update the patch_brin_optimze_mem according to your comment. At 2015-10-16 10:13:35, "Alvaro Herrera" wrote: >zhangjinyu wrote: > >> >>>>However I wonder if it would be simpler to have the dtup structure have >> >>>>the pointers, so that you can pass it as NULL in the first call and then >> >>>>followup calls reuse the one allocated in the first call. >> Jinyu: the memory is allocated from perRangeCxt and perRangeCxt will be >> reset in loop, >> so this way don't work. > >You're right. I think we can do better: have brin_deform_tuple accept >another argument of type BrinMemTuple *, which can be NULL. If NULL, >the function calls brin_new_memtuple to allocate a new one (just like >current code); if not NULL, that one is used. Have brin_new_memtuple >also allocate the values/allnulls/hasnulls arrays, which are now part of >the BrinMemTuple struct. Then, bringetbitmap calls brin_new_memtuple >once before changing to perRangeCxt, then reuse the returned inside the >loop (we probably need brin_memtuple_initialize to clear out the struct >at the end of each loop iteration). Other callers of brin_deform_tuple >just pass NULL to get the current behavior. > >-- >Álvaro Herrerahttp://www.2ndQuadrant.com/ >PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services > > >-- >Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-hackers patch_brin_optimize_mem Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Improve the concurency of vacuum full table and select statement on the same relation
>>it's approach to this is to summarily kill anything that attempts DDL on a >>table being repacked. Why? I am confused with it. Could you please explain this? Jinyu Zhang thanks At 2015-10-12 23:46:12, "Jim Nasby" wrote: >On 10/11/15 6:55 AM, Jinyu wrote: >> Are there other solutions to improve the concurency of vacuum >> full/cluster and select statement on the same relation? > >ISTM that if we were going to put effort into this it makes more sense >to pull pg_repack into core. BTW, it's approach to this is to summarily >kill anything that attempts DDL on a table being repacked. >-- >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
Re: [HACKERS] Improve the concurency of vacuum full table and select statement on the same relation
Get it now, thanks. The lock upgrade(from ExclusiveLock to AccessExclusiveLock) may result in deadlocks iff one transaction first takes an AccessShareLock and then takes a lock(lockmode > AccessShareLock) on the same relation. The single SQL statement can't take an AccessShareLock and then takes a lock(lockmode > AccessShareLock) on the same relation. In fact, there is lock upgrade in transaction block which includes multiple query.like this transaction block "start transaction; select query; DML/DDL", it is from AccessShareLock to lockmode > AccessShareLock. Now there may be deadlocks when run multiple transaction blocks even if no vacuum full. The some transaction will report error to break deadlock once deadlocks happen. So the vacuum full table may failed after doing lots of work in some time. In some scenes, there are not explicit transaction block (no lock upgrade from AccessShareLock to lockmode > AccessShareLock), the deadlocks rarely happens. perhaps we can provide an option for vacuum full to let user choose whether "cluster/vacuum full" block select statement for very short time. Are there other solutions to improve the concurency of vacuum full/cluster and select statement on the same relation? Jinyu Zhang, thanks At 2015-10-10 23:34:41, "Tom Lane" wrote: >Jinyu writes: >> Proposal: vacuum full table takes an ExclusiveLock on relation instead of >> AccessExclusiveLock at start. It can' block select statement before call >> function "finish_heap_swap". and select statement is safe because vacuum >> full table copys tuples from old relation to new relation before calling >> function "finish_heap_swap". But it must take an AccessExclusiveLock on >> relation when call function "finish_heap_swap" in order to block select >> statement on the same relation. > >> This solution can improve the concurency. the following shows the reasons. > >What it's more likely to do is cause the vacuum full to fail altogether, >after doing a lot of work. Lock upgrade is a bad thing because it tends >to result in deadlocks. > > regards, tom lane
[HACKERS] Improve the concurency of vacuum full table and select statement on the same relation
Now vacuum full table takes an AccessExclusiveLock on relation at start and select statement takes an AccessShareLock on relation. So 'vacuum full table' blocks select statement on the same table until it is committed and select statement block 'vacuum full table' until it is finished. The concurency is very very bad. Proposal: vacuum full table takes an ExclusiveLock on relation instead of AccessExclusiveLock at start. It can' block select statement before call function "finish_heap_swap". and select statement is safe because vacuum full table copys tuples from old relation to new relation before calling function "finish_heap_swap". But it must take an AccessExclusiveLock on relation when call function "finish_heap_swap" in order to block select statement on the same relation. This solution can improve the concurency. the following shows the reasons. 1. The Function 'copy_heap_data' which copys tuples from old relation to new relation takes most elapsed time of vacuum full table. And it takes an ExclusiveLock on relation when call function "copy_heap_data". So select statement on the same relation can't be blocked in the most elapsed time of vacuum full table. 2. The elapsed time of "finish_heap_swap" is very short, So the blocking time window is very short. This proposal can also improve the concurency of cluster table and select statement. Because the execution steps of cluster table is similar to vacuum full table. The select statement is safe before cluster table call function "finish_heap_swap". Please let me know if I miss something. Jinyu Zhang thanks
[HACKERS] Patch: Optimize memory allocation in function 'bringetbitmap'
BRIN Scan: Optimize memory allocation in function 'bringetbitmap'. We can allocate memory for some pointer before do long loop instead of allocating memory in long loop. Before optimizing code (warm run) postgres=# select count(*) from lineitem where l_orderkey=1; count --- 6 (1 row) Time: 456.219 ms After optimizing code (warm run) postgres=# select count(*) from lineitem where l_orderkey=1; count --- 6 (1 row) Time: 349.219 ms The following shows the DDL of this test case. CREATE TABLE LINEITEM ( L_ORDERKEYINTEGER NOT NULL, L_PARTKEY INTEGER NOT NULL, L_SUPPKEY INTEGER NOT NULL, L_LINENUMBER INTEGER NOT NULL, L_QUANTITYDECIMAL(15,2) NOT NULL, L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL, L_DISCOUNTDECIMAL(15,2) NOT NULL, L_TAX DECIMAL(15,2) NOT NULL, L_RETURNFLAG CHAR(1) NOT NULL, L_LINESTATUS CHAR(1) NOT NULL, L_SHIPDATEDATE NOT NULL, L_COMMITDATE DATE NOT NULL, L_RECEIPTDATE DATE NOT NULL, L_SHIPINSTRUCT CHAR(25) NOT NULL, L_SHIPMODE CHAR(10) NOT NULL, L_COMMENT VARCHAR(44) NOT NULL); copy lineitem from '/home/jinyu/mywork/dbgen/lineitem.tbl' delimiter '|'; create index brinLineitem on lineitem using brin(L_ORDERKEY) with(pages_per_range = 1); Jinyu Zhang 网易考拉iPhone6s玫瑰金5288元，现货不加价 patch_optimize_mem Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] BRIN Scan: Optimize memory allocation in function 'bringetbitmap'
BRIN Scan: Optimize memory allocation in function 'bringetbitmap'. We can allocate memory for some pointer before do long loop instead of allocating memory in long loop. Before optimizing code (warm run) postgres=# select count(*) from lineitem where l_orderkey=1; count --- 6 (1 row) Time: 456.219 ms After optimizing code (warm run) postgres=# select count(*) from lineitem where l_orderkey=1; count --- 6 (1 row) Time: 349.219 ms The following shows the DDL of this test case. CREATE TABLE LINEITEM ( L_ORDERKEYINTEGER NOT NULL, L_PARTKEY INTEGER NOT NULL, L_SUPPKEY INTEGER NOT NULL, L_LINENUMBER INTEGER NOT NULL, L_QUANTITYDECIMAL(15,2) NOT NULL, L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL, L_DISCOUNTDECIMAL(15,2) NOT NULL, L_TAX DECIMAL(15,2) NOT NULL, L_RETURNFLAG CHAR(1) NOT NULL, L_LINESTATUS CHAR(1) NOT NULL, L_SHIPDATEDATE NOT NULL, L_COMMITDATE DATE NOT NULL, L_RECEIPTDATE DATE NOT NULL, L_SHIPINSTRUCT CHAR(25) NOT NULL, L_SHIPMODE CHAR(10) NOT NULL, L_COMMENT VARCHAR(44) NOT NULL); copy lineitem from '/home/jinyu/mywork/dbgen/lineitem.tbl' delimiter '|'; create index brinLineitem on lineitem using brin(L_ORDERKEY) with(pages_per_range = 1); Jinyu Zhang
[HACKERS] Did we forget to unpin buf in function "revmap_physical_extend" ?
In function "revmap_physical_extend", should we add "ReleaseBuffer(buf);"
between line 438 and 439 ?
422 else
423 {
424 if (needLock)
425 LockRelationForExtension(irel, ExclusiveLock);
426
427 buf = ReadBuffer(irel, P_NEW);
428 if (BufferGetBlockNumber(buf) != mapBlk)
429 {
430 /*
431 * Very rare corner case: somebody extended the relation
432 * concurrently after we read its length. If this happens,
give
433 * up and have caller start over. We will have to evacuate
that
434 * page from under whoever is using it.
435 */
436 if (needLock)
437 UnlockRelationForExtension(irel, ExclusiveLock);
438 LockBuffer(revmap->rm_metaBuf, BUFFER_LOCK_UNLOCK);
439 return;
440 }
441 LockBuffer(buf, BUFFER_LOCK_EXCLUSIVE);
442 page = BufferGetPage(buf);
443
444 if (needLock)
445 UnlockRelationForExtension(irel, ExclusiveLock);
446 }
Jinyu,
regards
Re: [HACKERS] Performance Improvement by reducing WAL for Update Operation
I think sort by string column is lower during merge join, maybe comparing function in sort need be refined to save some cycle. It’s the hot function when do sort. Heikki Linnakangas 编写： >On 01/27/2014 07:03 PM, Amit Kapila wrote: >> I have tried to improve algorithm in another way so that we can get >> benefit of same chunks during find match (something similar to lz). >> The main change is to consider chunks at fixed boundary (4 byte) >> and after finding match, try to find if there is a longer match than >> current chunk. While finding longer match, it still takes care that >> next bigger match should be at chunk boundary. I am not >> completely sure about the chunk boundary may be 8 or 16 can give >> better results. > >Since you're only putting a value in the history every 4 bytes, you >wouldn't need to calculate the hash in a rolling fashion. You could just >take next four bytes, calculate hash, put it in history table. Then next >four bytes, calculate hash, and so on. Might save some cycles when >building the history table... > >- Heikki > > >-- >Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
