Vertica Tableau Report

UJJWAL_RANA

Hi I wonder what is the solution in order to speed up the tableau report performance. Now a days reports open in 90 sec.  Before it used to open in 30 sec. kindly please advise

eli_revach

Hi ,
In most of the cases , this can be improved by defining the right projections for the right queries !

You need to define projections for the SQLs generated by Tableau .

You can query  the query_requests systen table in order  to collect the relevent queries .

You can used DBD with query specific mode  see http://h71056.www7.hp.com/bdc/documents/training/Database-Designer.pdf  , the idea is to   provide to it a list of SQLs in a text file , DBD will analyze the file and based on that he will recommend to you the right projection setup to address your query use case .

 

I hope you will find it useful

 

Thanks 

 

UJJWAL_RANA

Hi Eli Revach,

Actually I have changed  the memory size as following but despite of changing it as following I still see the performance issue only on some of the reports but not all.

 Changing MEMORY SIZE on the following pool :

1) Etlpool 60% 60%
2) Bipool 30 G 60G
3) Adhocpool 40% 40%
 

By the way is there any online url which provide steps with command in order to follow your foot steps. Kindly please advise ? Thought to share this too.

There is no resource rejections:

dbadmin=>
dbadmin=> select * from resource_rejections where last_rejected_timestamp >= (sysdate -1) limit 15;
node_name | pool_id | pool_name | reason | resource_type | rejection_count | first_rejected_timestamp | last_rejected_timestamp | last_rejected_value
-----------+---------+-----------+--------+---------------+-----------------+--------------------------+-------------------------+---------------------
(0 rows)

dbadmin=>

There are no queue.

dbadmin=>
dbadmin=> select * from resource_queues;
node_name | transaction_id | statement_id | pool_name | memory_requested_kb | priority | position_in_queue | queue_entry_timestamp
-----------+----------------+--------------+-----------+---------------------+----------+-------------------+-----------------------
(0 rows)


While view the report it uses the xyzbi usrs.

dbadmin=>
dbadmin=> select * from users where user_name='xyzbi';
user_id | user_name | is_super_user | profile_name | is_locked | lock_time | resource_pool | memory_cap_kb | temp_space_cap_kb | run_time_cap | all_roles | default_roles | search_path
-------------------+-----------+---------------+--------------+-----------+-----------+---------------+---------------+-------------------+--------------+-----------+---------------+-------------------------------------------------
45035997405778228 | xyzbi | f | default | f | | bipool | unlimited | unlimited | unlimited | | | xyz, public, v_catalog, v_monitor, v_internal
(1 row)

dbadmin=>

Thanks

Ujjwal

emoreno

Hi, 

Have you check the tableau - Vertica tips and techniques document ?

https://community.dev.hpe.com/t5/Vertica-Knowledge-Base/HP-Vertica-Integration-with-Tableau-Tips-and-Techniques/ta-p/231492

 

The document have a lot tips in how to work with Tableau and Vertica, maybe that could help. Please let us know. 

 

Eugenia

eli_revach

Hi
My recommendations above focus on starting with creating the right projections to the relevant queries . Do you validate that you have the right projections ?

UJJWAL_RANA

No I haven't Check Database Designer Projections

 

However I have come up with top 10 slow query. Attached is the email here with where you can see REQUEST_DURATION_MS

UJJWAL_RANA

Hi Emoreno,

Thanks for the url link. I went through pdf file and inside pdf file is see following content at page 36

 

The following query returns the 10 slowest queries from the system:

=> SELECT request FROM v_monitor.query_requests
WHERE request_type = 'QUERY' ORDER BY request_duration_ms
DESC LIMIT 10;
The following query returns the 10 most frequently

 

The output of this file is attached here with in excel file. However if you look at the excel file then most of the slow comand are from INSERT statement but i wanted to see  10 slowest queries from the system having only SELECT statement. Can you help me please ? What is the sql statement to check 10 slowest queries from the system having only SELECT statement. However i tried following statement but that did not work. select statment is there too but could not display the exact output which i am looking for.

 

SELECT request, request_duration_ms FROM v_monitor.query_requests
WHERE request_type = 'QUERY' and request ilike '%select%' ORDER BY request_duration_ms
DESC LIMIT 10;

 

Please help

 

 

 

[Deleted User]

did you tried by removing the % before the select ? that will bring you only the transaction that start with Select. 

Hope that helps. 

Eugenia