Vertica 9.3 introduced JoinDefaultTupleFormat parameter : At DB level a big PROBLEM
Sudhakar_B
Vertica Customer ✭✭
Hello team,
I mentioned this in one of the other post https://forum.vertica.com/discussion/241996/specifying-length-of-varchar#latest. However, that thread went cold!
From my tests so far, I think JoinDefaultTupleFormat is a powerful feature that will help many customers. Especially when ingesting data from multiple live-sources into Vertica.
Both JFMT hint and JoinDefaultTupleFormat works VERY WELL at session level.
However when JoinDefaultTupleFormat parameter is set at DB level the entire cluster "SLOWS DOWN".
In one test attempt, on a 3-node cluster, The entire 64GB memory was consumed within 15 minutes and DMLs became extremely slow.
We had to restart the DB and System-Admins had to release the memory.
Has any one else experimented with this?
Any help and pointers will be helpful.
PS:
I found a repeatable use-case in Vertica VMware version 10.0.0 to demonstrate the effectiveness of the parameter JoinDefaultTupleFormat.
Please note that this use case can be repeated using simple select from v_catalog.log_queries
[dbadmin@localhost ~]$ vsql
Password:
Welcome to vsql, the Vertica Analytic Database interactive terminal.
Type: \h or \? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit
dbadmin=> select version(), node_address, sysdate() from nodes;
version | node_address | sysdate
-------------------------------------+--------------+---------------------------
Vertica Analytic Database v10.0.0-0 | 127.0.0.1 | 2020-12-28 11:13:38.19846
(1 row)
dbadmin=> select parameter_name, default_value from configuration_parameters
dbadmin-> where parameter_name ilike 'JoinDefault%';
parameter_name | default_value
------------------------+---------------
JoinDefaultTupleFormat | fixed
(1 row)
dbadmin=> alter session set JoinDefaultTupleFormat = 'fixed';
ALTER SESSION
dbadmin=>
dbadmin=> show session parameter JoinDefaultTupleFormat;
name | setting
------------------------+---------
JoinDefaultTupleFormat | fixed
(1 row)
dbadmin=>
dbadmin=> \timing
Timing is on.
dbadmin=> \o /dev/null
dbadmin=> profile select * from v_catalog.log_queries;
NOTICE 4788: Statement is being profiled
HINT: Select * from v_monitor.execution_engine_profiles where transaction_id=45035996273989883 and statement_id=1;
NOTICE 3557: Initiator memory for query: [on pool sysquery: 53491 KB, minimum: 53491 KB]
NOTICE 5077: Total memory required by query: [2241306 KB]
NOTICE 4788: Statement is being profiled
HINT: Select * from v_monitor.execution_engine_profiles where transaction_id=45035996273989883 and statement_id=2;
NOTICE 3557: Initiator memory for query: [on pool sysquery: 425180 KB, minimum: 425180 KB]
NOTICE 5077: Total memory required by query: [**2485156 KB**]
ERROR 3815: Join inner did not fit in memory [(v_catalog.vs_audit_categories x v_internal.dc_requests_completed) using vs_audit_categories_p and previous join (PATH ID: 2)]
dbadmin=>
dbadmin=>
dbadmin=> **alter session set JoinDefaultTupleFormat = 'variable';**
Time: First fetch (0 rows): 7.617 ms. All rows formatted: 7.650 ms
dbadmin=>
dbadmin=> show session parameter JoinDefaultTupleFormat;
Time: First fetch (1 row): 0.472 ms. All rows formatted: 0.490 ms
dbadmin=>
dbadmin=> \o /dev/null
dbadmin=> profile select * from v_catalog.log_queries;
NOTICE 4788: Statement is being profiled
HINT: Select * from v_monitor.execution_engine_profiles where transaction_id=45035996273989891 and statement_id=1;
NOTICE 3557: Initiator memory for query: [on pool sysquery: 54563 KB, minimum: 54563 KB]
NOTICE 5077: Total memory required by query: [**114479 KB**]
Time: First fetch (88 rows): **2229.579** ms. All rows formatted: 2242.888 ms
dbadmin=>
dbadmin=>