Vertica troubleshooting teqniques

All the events are recorded in the vertica.log file .(this is the first place to look when troubleshooting comes) Types & Modes of logs : Vertica can enable more types of logs using the "select set_debug_log('TM','ALL')" command.

admintools log.

network logs.

db designer logs.

copy error logs.

Managing sessions

To see all active sessions:

  select * from session;

To close all session on DB:

  select close_all_sessions();

To close a specific session on DB:

  select close_sessions('session_id');
  

To close all connections from clients and just allow acess to dbadmin connections

  select set_config_parameter('MaxClientSessions',0);
  

Managing locks

To see all active locks on DB run :

  select * from locks;
  

Description of Lock types : S - share lock needed for select operations(serializabale mode only) I - insert lock X - exclusive lock T - tuple mover lock , used by Tuple Mover or Copy command U - usage locks O - owner locks Managing performance issues Query plans

- they indicate data flow during execution .

- verifiys that projections are used in query.

How can we get the query plan ? -adding the "EXPLAIN" command before the query is to be analized. -this way an output will be provided with the optimezer decisions. Ex:

	 vsql:>EXPLAIN select * from table_name;
	 

Query profileing

- will show where time is spent during a query .

- will identify the resource pools used .

- indicates the memory required by the process.

How can we get the query profileing ? -adding the "PROFILE" command before the query is to be analized. Ex:

	 vsql:>PROFILE select * from table_name;
	 

- an exit with the min memory req will be output to estimate the query needs. Spread service

- spread is an open source toolkit used in Vertica to perform high perf messaging .

- each node will have spread installed during database installation process.

- do not alter spread config.

Checking if spread is running :

	>: ps -ef | grep spread
	

Checking spread status :

	:> /etc/init.d/spread status
	

Start spread service :

	:> /etc/init.d/spread start -- options-[stop,start,restart,status]