[Postgres-xl-general] Aggregate functions on sharded and partitioned tables

Pavan Deolasee pavan.deolasee at gmail.com
Mon Jan 18 19:40:13 PST 2016


Hi Tobias,

Thanks for the report. I've pushed a fix for this issue. This was a
regression after we recently changed the format for GID used by PREPARE
TRANSACTION and I see this can happen when large number of
datanodes/coordinators are involved in a 2PC transaction.

Please test and let me know it solves the problem. I'm sorry, I don't have
a setup handy to test with a large number of nodes, but I'm planning to
repeat some of the tests later this month.

Thanks,
Pavan

On Mon, Jan 18, 2016 at 10:48 PM, Tobias Oberstein <
tobias.oberstein at gmail.com> wrote:

> Hi Pavan,
>
> I've applied that patch to the XL9_5_STABLE branch, rebuild XL and
> recreated the cluster.
>
> Unfortunately, when changing the DBA password, I get a "buffer overflow
> detected".
>
> I've attached the log and conf.
>
> Thanks for your help!
> /Tobias
>
>
>
> oberstet at bvr-sql18:~/scm/3rdparty/postgres-xl$ git remote -v
> origin  http://git.postgresql.org/git/postgres-xl.git (fetch)
> origin  http://git.postgresql.org/git/postgres-xl.git (push)
> oberstet at bvr-sql18:~/scm/3rdparty/postgres-xl$ git status
> Auf Branch XL9_5_STABLE
> Ihr Branch ist auf dem selben Stand wie 'origin/XL9_5_STABLE'.
> Änderungen, die nicht zum Commit vorgemerkt sind:
>   (benutzen Sie "git add <Datei>...", um die Änderungen zum Commit
> vorzumerken)
>   (benutzen Sie "git checkout -- <Datei>...", um die Änderungen im
> Arbeitsverzeichnis zu verwerfen)
>
>         geändert:       src/backend/nodes/equalfuncs.c
>         geändert:       src/backend/optimizer/util/pathnode.c
>         geändert:       src/include/nodes/nodes.h
>
> Unbeobachtete Dateien:
>   (benutzen Sie "git add <Datei>...", um die Änderungen zum Commit
> vorzumerken)
>
>         contrib/pgxc_ctl/pgxc_ctl
>         contrib/pgxc_ctl/pgxc_ctl_bash.c
>         contrib/pgxc_ctl/signature.h
>         src/bin/pg_xlogdump/barrierdesc.c
>
> keine Änderungen zum Commit vorgemerkt (benutzen Sie "git add" und/oder
> "git commit -a")
> oberstet at bvr-sql18:~/scm/3rdparty/postgres-xl$ git log -n 1
> commit 45593885b111bc9635ce45c2f85cc1c9ff333ed0
> Author: Pallavi Sontakke <pallavi.sontakke at 2ndQuadrant.com>
> Date:   Mon Jan 18 14:26:18 2016 +0530
>
>     Accept XL test output.
>
>     Fixes 17 tests.
>
>     Reasons:
>
>     timestamp Issue, some ERROR string differences,
>     remote subquery plan differences.
>
>     No support in XL for: TRIGGERS, correlated UPDATE,
>     SAVEPOINT, WHERE CURRENT OF, internal subtransactions,
>     complicated SELECT queries in plpgsql functions,
>     distribution column in child table to refer to non
>     distribution column in referenced table,
>     ORDER BY in subqueries, FOREIGN DATA WRAPPER, SERVER,
>     USER MAPPING
> oberstet at bvr-sql18:~/scm/3rdparty/postgres-xl$ git rev-parse HEAD
> 45593885b111bc9635ce45c2f85cc1c9ff333ed0
> oberstet at bvr-sql18:~/scm/3rdparty/postgres-xl$
>
>
>
>
>
> STATEMENT:  ALTER USER postgres WITH ENCRYPTED PASSWORD '***********';
> *** buffer overflow detected ***: postgres: pgxl postgres ::1(52117) ALTER
> ROLE terminated
> ======= Backtrace: =========
> /lib/x86_64-linux-gnu/libc.so.6(+0x78c4e)[0x7fcba8963c4e]
> /lib/x86_64-linux-gnu/libc.so.6(__fortify_fail+0x5c)[0x7fcba8a03e8c]
> /lib/x86_64-linux-gnu/libc.so.6(+0x116e80)[0x7fcba8a01e80]
> /lib/x86_64-linux-gnu/libc.so.6(+0x1163d9)[0x7fcba8a013d9]
> /lib/x86_64-linux-gnu/libc.so.6(_IO_default_xsputn+0x80)[0x7fcba89673a0]
> /lib/x86_64-linux-gnu/libc.so.6(_IO_vfprintf+0x4852)[0x7fcba8939472]
> /lib/x86_64-linux-gnu/libc.so.6(__vsprintf_chk+0x84)[0x7fcba8a01464]
> /lib/x86_64-linux-gnu/libc.so.6(__sprintf_chk+0x7d)[0x7fcba8a013bd]
> postgres: pgxl postgres ::1(52117) ALTER
> ROLE(PrePrepare_Remote+0xd8)[0x64e268]
> postgres: pgxl postgres ::1(52117) ALTER ROLE[0x4ee5a9]
> postgres: pgxl postgres ::1(52117) ALTER ROLE[0x4f186c]
> postgres: pgxl postgres ::1(52117) ALTER
> ROLE(CommitTransactionCommand+0xc5)[0x4f2625]
> postgres: pgxl postgres ::1(52117) ALTER ROLE[0x780622]
> postgres: pgxl postgres ::1(52117) ALTER
> ROLE(PostgresMain+0x17f8)[0x7822f8]
> postgres: pgxl postgres ::1(52117) ALTER ROLE[0x47a10e]
> postgres: pgxl postgres ::1(52117) ALTER
> ROLE(PostmasterMain+0xea1)[0x70dcd1]
> postgres: pgxl postgres ::1(52117) ALTER ROLE(main+0x439)[0x47c1a9]
> /lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0xf0)[0x7fcba890ba40]
> postgres: pgxl postgres ::1(52117) ALTER ROLE(_start+0x29)[0x47c229]
> ======= Memory map: ========
> 00400000-00b68000 r-xp 00000000 09:01 9701970  /opt/pgxl/bin/postgres
> 00d67000-00d68000 r--p 00767000 09:01 9701970  /opt/pgxl/bin/postgres
> 00d68000-00d76000 rw-p 00768000 09:01 9701970  /opt/pgxl/bin/postgres
> 00d76000-00dbf000 rw-p 00000000 00:00 0
> 01386000-013c4000 rw-p 00000000 00:00 0  [heap]
> 013c4000-015ae000 rw-p 00000000 00:00 0  [heap]
> 7f487069b000-7f48706b1000 r-xp 00000000 09:01 1050788
> /lib/x86_64-linux-gnu/libgcc_s.so.1
> 7f48706b1000-7f48708b0000 ---p 00016000 09:01 1050788
> /lib/x86_64-linux-gnu/libgcc_s.so.1
> 7f48708b0000-7f48708b1000 rw-p 00015000 09:01 1050788
> /lib/x86_64-linux-gnu/libgcc_s.so.1
> 7f48708b1000-7f48708bd000 r-xp 00000000 09:01 1049145
> /lib/x86_64-linux-gnu/libnss_files-2.21.so
> 7f48708bd000-7f4870abc000 ---p 0000c000 09:01 1049145
> /lib/x86_64-linux-gnu/libnss_files-2.21.so
> 7f4870abc000-7f4870abd000 r--p 0000b000 09:01 1049145
> /lib/x86_64-linux-gnu/libnss_files-2.21.so
> 7f4870abd000-7f4870abe000 rw-p 0000c000 09:01 1049145
> /lib/x86_64-linux-gnu/libnss_files-2.21.so
> 7f4870abe000-7fcba84ae000 rw-s 00000000 00:05 25943108  /dev/zero (deleted)
> 7fcba84ae000-7fcba88eb000 r--p 00000000 09:01 12063783
> /usr/lib/locale/locale-archive
> 7fcba88eb000-7fcba8aab000 r-xp 00000000 09:01 1049085
> /lib/x86_64-linux-gnu/libc-2.21.so
> 7fcba8aab000-7fcba8cab000 ---p 001c0000 09:01 1049085
> /lib/x86_64-linux-gnu/libc-2.21.so
> 7fcba8cab000-7fcba8caf000 r--p 001c0000 09:01 1049085
> /lib/x86_64-linux-gnu/libc-2.21.so
> 7fcba8caf000-7fcba8cb1000 rw-p 001c4000 09:01 1049085
> /lib/x86_64-linux-gnu/libc-2.21.so
> 7fcba8cb1000-7fcba8cb5000 rw-p 00000000 00:00 0
> 7fcba8cb5000-7fcba8ccd000 r-xp 00000000 09:01 1049166
> /lib/x86_64-linux-gnu/libpthread-2.21.so
> 7fcba8ccd000-7fcba8ecd000 ---p 00018000 09:01 1049166
> /lib/x86_64-linux-gnu/libpthread-2.21.so
> 7fcba8ecd000-7fcba8ece000 r--p 00018000 09:01 1049166
> /lib/x86_64-linux-gnu/libpthread-2.21.so
> 7fcba8ece000-7fcba8ecf000 rw-p 00019000 09:01 1049166
> /lib/x86_64-linux-gnu/libpthread-2.21.so
> 7fcba8ecf000-7fcba8ed3000 rw-p 00000000 00:00 0
> 7fcba8ed3000-7fcba8fda000 r-xp 00000000 09:01 1049126
> /lib/x86_64-linux-gnu/libm-2.21.so
> 7fcba8fda000-7fcba91d9000 ---p 00107000 09:01 1049126
> /lib/x86_64-linux-gnu/libm-2.21.so
> 7fcba91d9000-7fcba91da000 r--p 00106000 09:01 1049126
> /lib/x86_64-linux-gnu/libm-2.21.so
> 7fcba91da000-7fcba91db000 rw-p 00107000 09:01 1049126
> /lib/x86_64-linux-gnu/libm-2.21.so
> 7fcba91db000-7fcba91de000 r-xp 00000000 09:01 1049097
> /lib/x86_64-linux-gnu/libdl-2.21.so
> 7fcba91de000-7fcba93dd000 ---p 00003000 09:01 1049097
> /lib/x86_64-linux-gnu/libdl-2.21.so
> 7fcba93dd000-7fcba93de000 r--p 00002000 09:01 1049097
> /lib/x86_64-linux-gnu/libdl-2.21.so
> 7fcba93de000-7fcba93df000 rw-p 00003000 09:01 1049097
> /lib/x86_64-linux-gnu/libdl-2.21.so
> 7fcba93df000-7fcba93e6000 r-xp 00000000 09:01 1049172
> /lib/x86_64-linux-gnu/librt-2.21.so
> 7fcba93e6000-7fcba95e5000 ---p 00007000 09:01 1049172
> /lib/x86_64-linux-gnu/librt-2.21.so
> 7fcba95e5000-7fcba95e6000 r--p 00006000 09:01 1049172
> /lib/x86_64-linux-gnu/librt-2.21.so
> 7fcba95e6000-7fcba95e7000 rw-p 00007000 09:01 1049172
> /lib/x86_64-linux-gnu/librt-2.21.so
> 7fcba95e7000-7fcba960b000 r-xp 00000000 09:01 1049061
> /lib/x86_64-linux-gnu/ld-2.21.so
> 7fcba967a000-7fcba9681000 r--s 00000000 09:01 12060896
> /usr/lib/x86_64-linux-gnu/gconv/gconv-modules.cache
> 7fcba9681000-7fcba97f7000 rw-p 00000000 00:00 0
> 7fcba97f7000-7fcba97fb000 rw-p 00000000 00:00 0
> 7fcba97fd000-7fcba97fe000 rw-p 00000000 00:00 0
> 7fcba97fe000-7fcba9807000 rw-s 00000000 00:12 25943110
> /dev/shm/PostgreSQL.846930886
> 7fcba9807000-7fcba9808000 rw-s 00000000 00:05 26673153  /SYSV0052ded9
> (deleted)
> 7fcba9808000-7fcba980a000 rw-p 00000000 00:00 0
> 7fcba980a000-7fcba980b000 r--p 00023000 09:01 1049061
> /lib/x86_64-linux-gnu/ld-2.21.so
> 7fcba980b000-7fcba980c000 rw-p 00024000 09:01 1049061
> /lib/x86_64-linux-gnu/ld-2.21.so
> 7fcba980c000-7fcba980d000 rw-p 00000000 00:00 0
> 7fff829bd000-7fff829e1000 rw-p 00000000 00:00 0  [stack]
> 7fff829f5000-7fff829f7000 r--p 00000000 00:00 0  [vvar]
> 7fff829f7000-7fff829f9000 r-xp 00000000 00:00 0  [vdso]
> ffffffffff600000-ffffffffff601000 r-xp 00000000 00:00 0  [vsyscall]
>
>
>
>
>
> Am 15.01.2016 um 18:56 schrieb Pavan Deolasee:
>
>> Hi Philipp,
>>
>> Please test with the attached patch when you get opportunity and let me
>> know if this fixes the inefficient plans for inherited tables.
>>
>> Thanks,
>> Pavan
>>
>> On Wed, Jan 13, 2016 at 10:48 PM, Pavan Deolasee
>> <pavan.deolasee at gmail.com <mailto:pavan.deolasee at gmail.com>> wrote:
>>
>>
>>
>>     On Wed, Jan 13, 2016 at 4:16 PM, Philipp Wolf
>>     <philipp.wolf at record-evolution.de
>>     <mailto:philipp.wolf at record-evolution.de>> wrote:
>>
>>         Hi,
>>
>>         I have a question concerning partitionig of sharded tables,
>>         especially the behavior with aggregate functions.
>>         The goal would be to have a table which is distributed by hash
>>         (column a) and further more, each node is partitioned by
>>         column b.
>>         Here is a minimal example:
>>
>>         ---------- example ----------
>>         create table main_table (a int, b int) distribute by hash(a);
>>         ---
>>         create table child1_table (like main_table) inherits
>>         (main_table) distribute by hash(a);
>>
>>         alter table child1_table add constraint const check(b > 0 and b
>>         <= 10);
>>
>>         insert into child1_table (a, b) values
>>                  (1, 2), (1, 8), (2, 10);
>>         ----
>>         create table child2_table (like main_table) inherits
>>         (main_table) distribute by hash(a);
>>
>>         alter table child2_table add constraint const check(b > 10 and b
>>         <= 20);
>>
>>         insert into child2_table (a, b) values
>>                  (1, 20), (2, 11), (2, 18);
>>         ---------- end example ------
>>
>>         Looking at the query plan of a non partitioned table like
>>         child1_table with an aggregate function:
>>         explain select a, sum(b) from child1_table group by a;
>>
>>         "Remote Subquery Scan on all
>>
>> (node1shard1,node1shard2,node1shard3,node1shard4,node2shard1,node2shard2,....
>>         "  ->  HashAggregate  (cost=2.04..2.06 rows=2 width=8)"
>>         "        Group Key: a"
>>         "        ->  Seq Scan on child1_table  (cost=0.00..2.03 rows=3
>>         width=8)"
>>
>>         The aggregation is done on each node, which is reasonable.
>>
>>
>>         But, the query plans of the partitioned main_table when using
>>         aggregate functions look like this:
>>
>>         explain select a, sum(b) from main_table group by a;
>>
>>         "HashAggregate  (cost=304.19..304.21 rows=2 width=8)"
>>         "  Group Key: main_table.a"
>>         "  ->  Append  (cost=100.00..304.15 rows=7 width=8)"
>>         "        ->  Remote Subquery Scan on all
>>
>> (node1shard1,node1shard2,node1shard3,node1shard4,node2shard1,node2shard2,...
>>         "              ->  Seq Scan on main_table  (cost=0.00..0.00
>>         rows=1 width=8)"
>>         "        ->  Remote Subquery Scan on all
>>
>> (node1shard1,node1shard2,node1shard3,node1shard4,node2shard1,node2shard2,...
>>         "              ->  Seq Scan on child1_table  (cost=0.00..2.03
>>         rows=3 width=8)"
>>         "        ->  Remote Subquery Scan on all
>>
>> (node1shard1,node1shard2,node1shard3,node1shard4,node2shard1,node2shard2,...
>>         "              ->  Seq Scan on child2_table  (cost=0.00..2.03
>>         rows=3 width=8)"
>>
>>         The scan is done on each node and the results seem to be
>>         transferred to the coordinator, which does the aggregation.
>>         But, the desired behavior would be to do the aggregation on each
>>         node, which should perform much better.
>>
>>
>>     Thanks for pointing this out. Yeah, we can do far better than what
>>     we are doing currently.
>>
>>         Has anyone an idea how to achieve the aggregation on node level?
>>
>>
>>     May be you can try rewriting the query like this? Hmm, I know its a
>>     lot more work, but just wanted to bring up something that might work
>>     with the current limitations and until we get chance to improve
>> things.
>>
>>     explain select x.a, sum(x.sum) from (select a, sum(b) from only
>>     main_table group by a union all select a, sum(b) from child1_table
>>     group by a union all select a, sum(b) from child2_table group by a)
>>     x group by a;
>>
>>                                                      QUERY PLAN
>>
>> ----------------------------------------------------------------------------------------------------------
>>       HashAggregate  (cost=468.86..471.36 rows=200 width=12)
>>         Group Key: a
>>         ->  Append  (cost=100.00..465.39 rows=693 width=12)
>>               ->  Remote Subquery Scan on all (datanode_1,datanode_2)
>>       (cost=100.00..100.04 rows=1 width=12)
>>                     ->  HashAggregate  (cost=0.01..0.01 rows=1 width=8)
>>                           Group Key: main_table.a
>>                           ->  Seq Scan on main_table  (cost=0.00..0.00
>>     rows=1 width=8)
>>               ->  Remote Subquery Scan on all (datanode_1,datanode_2)
>>       (cost=195.85..214.05 rows=492 width=12)
>>                     ->  HashAggregate  (cost=95.85..100.77 rows=492
>> width=8)
>>                           Group Key: child1_table.a
>>                           ->  Seq Scan on child1_table
>>       (cost=0.00..71.23 rows=4923 width=8)
>>               ->  Remote Subquery Scan on all (datanode_1,datanode_2)
>>       (cost=143.90..151.30 rows=200 width=12)
>>                     ->  HashAggregate  (cost=43.90..45.90 rows=200
>> width=8)
>>                           Group Key: child2_table.a
>>                           ->  Seq Scan on child2_table
>>       (cost=0.00..32.60 rows=2260 width=8)
>>     (15 rows)
>>
>>     Thanks,
>>     Pavan
>>
>>     --
>>       Pavan Deolasee http://www.2ndQuadrant.com/
>>       PostgreSQL Development, 24x7 Support, Training & Services
>>
>>
>>
>>
>> --
>>   Pavan Deolasee http://www.2ndQuadrant.com/
>>   PostgreSQL Development, 24x7 Support, Training & Services
>>
>>
>>
>> ------------------------------------------------------------------------------
>> Site24x7 APM Insight: Get Deep Visibility into Application Performance
>> APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
>> Monitor end-to-end web transactions and take corrective actions now
>> Troubleshoot faster and improve end-user experience. Signup Now!
>> http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140
>>
>>
>>
>> _______________________________________________
>> Postgres-xl-general mailing list
>> Postgres-xl-general at lists.sourceforge.net
>> https://lists.sourceforge.net/lists/listinfo/postgres-xl-general
>>
>>
>


-- 
 Pavan Deolasee                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/pipermail/postgres-xl-general-postgres-xl.org/attachments/20160119/15ebe2a0/attachment.htm>


More information about the postgres-xl-general mailing list