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.
Vertica Database Baseline
Moderator: NorbertKrupa
-
- Intermediate
- Posts: 163
- Joined: Tue Jun 19, 2012 2:44 pm
- Location: Rio de Janeiro
- Contact:
Vertica Database Baseline
trying so hard !!!
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: Vertica Database Baseline
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.
-
- Intermediate
- Posts: 163
- Joined: Tue Jun 19, 2012 2:44 pm
- Location: Rio de Janeiro
- Contact:
Re: Vertica Database Baseline
Thank you Norbert,
I think this would be a nice feature to be added in Vertica Management Console.
I think this would be a nice feature to be added in Vertica Management Console.
trying so hard !!!
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: Vertica Database Baseline
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.
Re: Vertica Database Baseline
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
> 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
Vertica Consultant, Zazz Technologies LLC
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: Vertica Database Baseline
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.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.
Checkout vertica.tips for more Vertica resources.
-
- Beginner
- Posts: 36
- Joined: Fri Feb 17, 2012 6:09 pm
- Contact:
Re: Vertica Database Baseline
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.
Then we can schedule this INSERT statement to run via crontab or similar to start building a long term history.
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;
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');