Vertica Database Baseline

Moderator: NorbertKrupa

adrian.oprea
Intermediate
Intermediate
Posts: 163
Joined: Tue Jun 19, 2012 2:44 pm
Location: Rio de Janeiro
Contact:

Vertica Database Baseline

Post by adrian.oprea » Mon Mar 23, 2015 5:48 pm

Hi all,

I am trying to create some kind of baseline in Vertica so i can see how my cluster is doing in the long run and also have a better idea of how new modules.
Is there anybody who did this in the past or you guys have some general rules on how i can do this ?
Thank all.
trying so hard !!!

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Vertica Database Baseline

Post by NorbertKrupa » Tue Mar 24, 2015 3:29 pm

I would use something like Graphite/Collectl to send CPU/IO/Memory/Requests hourly/twice a day/daily. This will allow you to look at your stats over time.
Checkout vertica.tips for more Vertica resources.

adrian.oprea
Intermediate
Intermediate
Posts: 163
Joined: Tue Jun 19, 2012 2:44 pm
Location: Rio de Janeiro
Contact:

Re: Vertica Database Baseline

Post by adrian.oprea » Tue Mar 24, 2015 8:21 pm

Thank you Norbert,
I think this would be a nice feature to be added in Vertica Management Console.
trying so hard !!!

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Vertica Database Baseline

Post by NorbertKrupa » Tue Mar 24, 2015 9:10 pm

Well, you could just increase the retention for those data collection components. There might be some value in seeing those in MC, but I haven't seen much interest in historical performance.
Checkout vertica.tips for more Vertica resources.

scutter
Master
Master
Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

Re: Vertica Database Baseline

Post by scutter » Tue Mar 24, 2015 9:19 pm

MC performs poorly enough without adding all that extra retention on DC tables ;-)

> I haven't seen much interest in historical performance

As someone who troubleshoots and tunes clusters on a daily basis, having history is key. All sorts of history.

—Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Vertica Database Baseline

Post by NorbertKrupa » Tue Mar 24, 2015 10:41 pm

scutter wrote:> I haven't seen much interest in historical performance

As someone who troubleshoots and tunes clusters on a daily basis, having history is key. All sorts of history.
You're right. As a client, I was interested in seeing query performance over time. Not so much CPU/IO/Memory. Starting fresh, not so much.
Checkout vertica.tips for more Vertica resources.

doug_harmon
Beginner
Beginner
Posts: 36
Joined: Fri Feb 17, 2012 6:09 pm
Contact:

Re: Vertica Database Baseline

Post by doug_harmon » Wed Mar 25, 2015 5:43 pm

I've created a SQL Script to capture what is in the v_monitor.query_requests table, which keeps a rolling history of queries and their run times. Since this table keeps a rolling history, it needs to be backed up to another history table for long term storage on a regular basis. This allows us to identify long running queries and provide a baseline for measuring performance improvements. This could be expanded to other v_monitor tables as desired.

First we create the table.

Code: Select all

----------------------------------------------------------------------------------
-- Purpose: Creates a table history.query_requests 
--          that will be used to store a history of queries in 
--          v_monitor.query_requests.
-- Assumptions: This runs under the dbadmin account.
--              A schema called history already exists, or is created using:
--              CREATE SCHEMA history;
----------------------------------------------------------------------------------

CREATE TABLE history.query_requests 
(
    node_name varchar(128) ENCODING RLE,
    user_name varchar(128) ENCODING RLE,
    session_id varchar(128),
    request_id int  ENCODING COMMONDELTA_COMP,
    transaction_id int  ENCODING COMMONDELTA_COMP,
    statement_id int  ENCODING COMMONDELTA_COMP,
    request_type varchar(128) ENCODING RLE,
    request varchar(64000),
    request_label varchar(128) ENCODING RLE,
    search_path varchar(64000)  ENCODING RLE,
    memory_acquired_mb float,
    success boolean  ENCODING RLE,
    error_count int  ENCODING RLE,
    start_timestamp timestamptz ENCODING DELTARANGE_COMP,
    end_timestamp timestamptz ENCODING DELTARANGE_COMP,
    request_duration_ms int ENCODING DELTARANGE_COMP
 )
 ORDER BY 
         user_name,
         node_name,
         request,
         start_timestamp,
         request_duration_ms,
         end_timestamp,
         transaction_id 
SEGMENTED BY hash(transaction_id) ALL NODES KSAFE 1;

Then we can schedule this INSERT statement to run via crontab or similar to start building a long term history.

Code: Select all

----------------------------------------------------------------------------------
-- Purpose: Creates a history in the table history.query_requests 
--          from the rolling history in v_monitor.query_requests.
-- Assumptions: This runs under the dbadmin account.
--              Schedule this to run every 30 minutes via crontab or similar.
----------------------------------------------------------------------------------

INSERT /*+ direct  */
INTO history.query_requests
(
    node_name
   ,user_name
   ,session_id
   ,request_id
   ,transaction_id
   ,statement_id
   ,request_type
   ,request
   ,request_label
   ,search_path
   ,memory_acquired_mb
   ,success
   ,error_count
   ,start_timestamp
   ,end_timestamp
   ,request_duration_ms
)
SELECT
    node_name
   ,user_name
   ,session_id
   ,request_id
   ,transaction_id
   ,statement_id
   ,request_type
   ,request
   ,request_label
   ,search_path
   ,memory_acquired_mb
   ,success
   ,error_count
   ,start_timestamp
   ,end_timestamp
   ,request_duration_ms
FROM v_monitor.query_requests
WHERE is_executing=false
  AND transaction_id <> 0 
  AND request_type IN ('LOAD','QUERY','TRUNCATE','UTILITY','DDL')  
  AND HASH 
  (
    node_name
   ,user_name
   ,session_id
   ,request_id
   ,transaction_id
   ,statement_id
   ,request_type
   ,request
   ,request_label
   ,search_path
   ,memory_acquired_mb
   ,success
   ,error_count
   ,start_timestamp
   ,end_timestamp
   ,request_duration_ms
  )
NOT IN (
	SELECT HASH(
			    node_name
			   ,user_name
			   ,session_id
			   ,request_id
			   ,transaction_id
			   ,statement_id
			   ,request_type
			   ,request
			   ,request_label
			   ,search_path
			   ,memory_acquired_mb
			   ,success
			   ,error_count
			   ,start_timestamp
			   ,end_timestamp
			   ,request_duration_ms
			)  AS hash_value
	FROM history.query_requests
	WHERE start_timestamp >= (SELECT MIN(start_timestamp)
	                            FROM v_monitor.query_requests)
);

SELECT ANALYZE_STATISTICS('history.query_requests');


Post Reply

Return to “Vertica Database Administration”