Need Suggestion for creation of reports using vertica.logs

Arvind_Kumar

Hi ,

 

I need suggestion , how we can generate reports using vertica.log .

 

Reports can be like tracing different Errors and warning in vertica ?

 

Just need suggestion to generate reports from vertica logs to know cause of issue and possible upcoming issues in vertica.

 

Any suggestion will be appreciated? 

 

Thanks in advance

[Deleted User]

Hi, 

 

The errors that you see in the vertica.log you can see it in table error_messages, do you need more?

 

Eugenia

Arvind_Kumar

Yes i need more details , like when node goes down , the reason for node down comes in vertica.log but not in error messages table.

 

second during load , reason for load fails comes in vertica.log but not in error table.

 

lot of things comes only in vertica.log which does not come in error tables.

 

 

Could you please suggest any reporting tool which can be used to generate these thing or do you have plan to come up with solution in vertica itself.

 

Many time happens we shutdown vertica as database but vertica process keep running on every node then we need to kill every vertica process.

victor_dileo

I use the system tables below to extract data into my own set of monitoring tables rather than monitor the Vertica log.

 

System tables that address some of your questions:

 

-- History of node states:

v_monitor.node_states

 

 

-- History of load events

v_internal.dc_load_events

 

 

-- Load fails (for example, joined with query_requests)

(see attachment - I copied a system view to do this)

 

-- Also take a look at:

dc_load_events

 

-- good refs for other ways of catpuring load errors:

https://my.vertica.com/docs/7.1.x/HTML/Content/Authoring/AdministratorsGuide/BulkLoadCOPY/CapturingLoadExceptionsAndRejections.htm

 

-- Monitor the amount of free disk space:

select min(pct_free) from
(select time::date as date
, current_database() as database
, node_name
, path
, device
, filesystem
, (used_bytes_end_value/(1024^3))::int as used_gb
, (free_bytes_end_value/(1024^3))::int as free_gb
, ((free_bytes_end_value + used_bytes_end_value) /(1024^3))::int as total
, (ROUND(free_bytes_end_value / (free_bytes_end_value + used_bytes_end_value) * 100)::int)::int as pct_free
from dc_storage_info_by_day
-- ***** Next line, replace my device & path values with your own *****
where (device = '/dev/md128' and path = '/vertica/data') -- md127 is the EC2 device.
and time::date = (select max(time::date) from dc_storage_info_by_day)
order by time desc, node_name
)X
where pct_free < 40 ;

 

 

-- My favorite Vertica ref - lots of good system tables here: 

http://vertica.tips/2014/06/02/under-the-hood-system-tables-and-views/

 

These solutions apply to  v7.1.2-1

Make sure you consult the docs for your own version.

 

Hope this Helps!

Let me know if you have questions.

 

 - Victor

 

 

 

 

Arvind_Kumar

Thanks Victor for your suggestion , i am working on this thing will come with more question when i will face problems

frivtn100

-- Monitor the amount of free disk space:

select min(pct_free) from
(select time::date as date
, current_database() as database
, node_name
, path
, device
, filesystem
, (used_bytes_end_value/(1024^3))::int as used_gb
, (free_bytes_end_value/(1024^3))::int as free_gb
, ((free_bytes_end_value + used_bytes_end_value) /(1024^3))::int as total
, (ROUND(free_bytes_end_value / (free_bytes_end_value + used_bytes_end_value) * 100)::int)::int as pct_free
from dc_storage_info_by_day
-- ***** Next line, replace my device & path values with your own *****
where (device = '/dev/md128' and path = '/vertica/data') -- md127 is the EC2 device.
and time::date = (select max(time::date) from dc_storage_info_by_day)
order by time desc, node_name
)X
where pct_free < 40 ;
Friv2
Friv7
Friv8
Friv9

RaghavA

Hi Vikas,

I agree with victor that it is good method to start.

In my opinion, there are 2 methods to achive this:

1) Use a shell/batch file to scan thru vertica.log to create a report. To take hints what to capture or include, have a look to Management Console. The email/notification bar in MC is a great way to start along with its criticality level.

Note : In vertica, there are few logging entries which are Tagged as INFO which is an indication of a problem.

2) You can query dc_load_events, query_profiles, errors table and few other system tables.

Regards,
Raghav Agrawal