Managing Data Collection Logs

On startup, Vertica creates a DataCollector directory under the database catalog directory of each node. This directory contains one or more logs for individual components. For example:

[dbadmin@doch01 DataCollector]$ pwd
/home/dbadmin/VMart/v_vmart_node0001_catalog/DataCollector
[dbadmin@doch01 DataCollector]$ ls -1 -g Lock*
-rw------- 1 verticadba 2559879 Aug 17 00:14 LockAttempts_650572441057355.log
-rw------- 1 verticadba  614579 Aug 17 05:28 LockAttempts_650952885486175.log
-rw------- 1 verticadba 2559895 Aug 14 18:31 LockReleases_650306482037650.log
-rw------- 1 verticadba 1411127 Aug 17 05:28 LockReleases_650759468041873.log

The DataCollector directory also contains a pair of SQL template files for each component:

• CREATE_component_TABLE.sql provides DDL for creating a table where you can load Data Collector logs for a given component—for example, LockAttempts:

  [dbadmin@doch01 DataCollector]$ cat CREATE_LockAttempts_TABLE.sql
  \set dcschema 'echo ${DCSCHEMA:-dc}'
  CREATE TABLE :dcschema.dc_lock_attempts(
    "time" TIMESTAMP WITH TIME ZONE,
    "node_name" VARCHAR(128),
    "session_id" VARCHAR(128),
    "user_id" INTEGER,
    "user_name" VARCHAR(128),
    "transaction_id" INTEGER,
    "object" INTEGER,
    "object_name" VARCHAR(128),
    "mode" VARCHAR(128),
    "promoted_mode" VARCHAR(128),
    "scope" VARCHAR(128),
    "start_time" TIMESTAMP WITH TIME ZONE,
    "timeout_in_seconds" INTEGER,
    "result" VARCHAR(128),
    "description" VARCHAR(64000)
  );
  

• COPY_component_TABLE.sql contains SQL for loading (with COPY) the data log files into the table that the CREATE script creates. For example:

  [dbadmin@doch01 DataCollector]$ cat COPY_LockAttempts_TABLE.sql
  \set dcpath 'echo ${DCPATH:-$PWD}'
  \set dcschema 'echo ${DCSCHEMA:-dc}'
  \set logfiles '''':dcpath'/LockAttempts_*.log'''
  COPY :dcschema.dc_lock_attempts(
    LockAttempts_start_filler FILLER VARCHAR(64) DELIMITER E'\n',
    "time_nfiller" FILLER VARCHAR(32) DELIMITER ':',
    "time" FORMAT '_internal' DELIMITER E'\n',
    "node_name_nfiller" FILLER VARCHAR(32) DELIMITER ':',
    "node_name" ESCAPE E'\001' DELIMITER E'\n',
    "session_id_nfiller" FILLER VARCHAR(32) DELIMITER ':',
    "session_id" ESCAPE E'\001' DELIMITER E'\n',
    "user_id_nfiller" FILLER VARCHAR(32) DELIMITER ':',
    "user_id" FORMAT 'd' DELIMITER E'\n',
    "user_name_nfiller" FILLER VARCHAR(32) DELIMITER ':',
    "user_name" ESCAPE E'\001' DELIMITER E'\n',
    "transaction_id_nfiller" FILLER VARCHAR(32) DELIMITER ':',
    "transaction_id" FORMAT 'd' DELIMITER E'\n',
    "object_nfiller" FILLER VARCHAR(32) DELIMITER ':',
    "object" FORMAT 'd' DELIMITER E'\n',
    "object_name_nfiller" FILLER VARCHAR(32) DELIMITER ':',
    "object_name" ESCAPE E'\001' DELIMITER E'\n',
    "mode_nfiller" FILLER VARCHAR(32) DELIMITER ':',
    "mode" ESCAPE E'\001' DELIMITER E'\n',
    "promoted_mode_nfiller" FILLER VARCHAR(32) DELIMITER ':',
    "promoted_mode" ESCAPE E'\001' DELIMITER E'\n',
    "scope_nfiller" FILLER VARCHAR(32) DELIMITER ':',
    "scope" ESCAPE E'\001' DELIMITER E'\n',
    "start_time_nfiller" FILLER VARCHAR(32) DELIMITER ':',
    "start_time" FORMAT '_internal' DELIMITER E'\n',
    "timeout_in_seconds_nfiller" FILLER VARCHAR(32) DELIMITER ':',
    "timeout_in_seconds" FORMAT 'd' DELIMITER E'\n',
    "result_nfiller" FILLER VARCHAR(32) DELIMITER ':',
    "result" ESCAPE E'\001' DELIMITER E'\n',
    "description_nfiller" FILLER VARCHAR(32) DELIMITER ':',
    "description" ESCAPE E'\001'
  )  FROM :logfiles RECORD TERMINATOR E'\n.\n' DELIMITER E'\n';

Log Management Meta-Functions

You can manage Data Collector logs with Vertica meta-functions FLUSH_DATA_COLLECTOR and CLEAR_DATA_COLLECTOR. Both functions can specify a single component, or execute on all components:

• FLUSH_DATA_COLLECTOR waits until memory logs are moved to disk and then flushes the Data Collector, synchronizing the log with disk storage. For example, the following statement executes on all components:

  => SELECT flush_data_collector();
   flush_data_collector
  ----------------------
   FLUSH
  (1 row)

• CLEAR_DATA_COLLECTOR clears all memory and disk records from Data Collector tables and logs, and resets collection statistics in system table DATA_COLLECTOR. For example, the following statement executes on data collected for component ResourceAcquisitions:

  => SELECT clear_data_collector('ResourceAcquisitions');
   clear_data_collector
  ----------------------
   CLEAR
  (1 row)