ALTER_LOCATION_SIZE

Eon Mode only

Resizes the depot on one node, all nodes in a subcluster, or all nodes in the database.

If you reduce the size of the depot, Vertica may be forced to evict data from the depot often to make room for more recently-requested data. This behavior leads to more accesses to communal storage causing slower performance and higher costs due to access charges.

Syntax

ALTER_LOCATION_SIZE( 'location', '[target]', 'size')

Parameters

location

The location to resize. Valid options are:

  • The literal string depot. This argument applies to the node's current depot, no matter what its path. This is usually the argument you want to use, even if you are altering just a single node's depot.
  • The absolute path of the depot in the Linux filesystem. If you change the depot size on multiple nodes and specify a path, the path must be identical on all affected nodes . By default, this is not the case, as the node's name is typically this path. For example, the default depot path for node 1 in a database named verticadb is /vertica/data/verticadb/v_verticadb_node0001_depot.
target

The node or nodes on which to change the depot, one of the following:

  • Name of an individual node.
  • Name of a subcluster. The function resizes depots on all nodes of the specified subcluster.
  • Empty string. The function resizes all depots in the database.
size

Valid only if the storage location usage type is set to DEPOT, specifies the maximum amount of disk space that the depot can allocate from the storage location's file system.

You can specify size in two ways:

  • integer%: Percentage of storage location disk size.
  • integer{K|M|G|T}: Amount of storage location disk size in kilobytes, megabytes, gigabytes, or terabytes.

However you specify this value, the depot size cannot be more than 80 percent of disk space of the file system where the depot is stored. If you specify a value that is too large, Vertica warns you of this limitation and automatically changes the value to 80% of the size of the file system.

Privileges

Superuser

Examples

On all database nodes, increase the depot's size to 80 percent of file system.

=> SELECT node_name, location_label, location_path, max_size 
   FROM storage_locations WHERE location_usage = 'DEPOT' ORDER BY 1;
      node_name       | location_label  |                   location_path                    |  max_size   
----------------------+-----------------+----------------------------------------------------+-------------
 v_verticadb_node0001 | auto-data-depot | /vertica/data/verticadb/v_verticadb_node0001_depot |  4982631424
 v_verticadb_node0002 | auto-data-depot | /vertica/data/verticadb/v_verticadb_node0002_depot |  4982631424
 v_verticadb_node0003 | auto-data-depot | /vertica/data/verticadb/v_verticadb_node0003_depot |  4982631424

=> SELECT alter_location_size('depot', '','80%');
 alter_location_size 
---------------------
 depotSize changed.
(1 row)

=> SELECT node_name, location_label, location_path, max_size 
   FROM storage_locations WHERE location_usage = 'DEPOT' ORDER BY 1;
      node_name       | location_label  |                   location_path                    |  max_size   
----------------------+-----------------+----------------------------------------------------+-------------
 v_verticadb_node0001 | auto-data-depot | /vertica/data/verticadb/v_verticadb_node0001_depot |  6643508224
 v_verticadb_node0002 | auto-data-depot | /vertica/data/verticadb/v_verticadb_node0002_depot |  6643508224
 v_verticadb_node0003 | auto-data-depot | /vertica/data/verticadb/v_verticadb_node0003_depot |  6643508224

Change the depot size to 75% of the filesystem size for all nodes in the analytics subcluster.

=> SELECT subcluster_name,subclusters.node_name,storage_locations.max_size 
	FROM subclusters INNER JOIN storage_locations 
	ON subclusters.node_name = storage_locations.node_name 
	WHERE storage_locations.location_usage='DEPOT';
	
  subcluster_name   |      node_name       |  max_size  
--------------------+----------------------+------------
 default_subcluster | v_verticadb_node0001 | 4982631424
 default_subcluster | v_verticadb_node0002 | 4982631424
 default_subcluster | v_verticadb_node0003 | 4982631424
 analytics          | v_verticadb_node0004 | 4982631424
 analytics          | v_verticadb_node0005 | 4982631424
 analytics          | v_verticadb_node0006 | 4982631424
 analytics          | v_verticadb_node0007 | 4982631424
 analytics          | v_verticadb_node0008 | 4982631424
 analytics          | v_verticadb_node0009 | 4982631424
(9 rows)

=> SELECT ALTER_LOCATION_SIZE('depot','analytics','75%');
 ALTER_LOCATION_SIZE 
---------------------
 depotSize changed.
(1 row)

=> SELECT subcluster_name,subclusters.node_name,storage_locations.max_size 
	FROM subclusters INNER JOIN storage_locations 
	ON subclusters.node_name = storage_locations.node_name 
	WHERE storage_locations.location_usage='DEPOT';
	
  subcluster_name   |      node_name       |  max_size   
--------------------+----------------------+-------------
 default_subcluster | v_verticadb_node0001 |  4982631424
 default_subcluster | v_verticadb_node0002 |  4982631424
 default_subcluster | v_verticadb_node0003 |  4982631424
 analytics          | v_verticadb_node0004 | 31580921856
 analytics          | v_verticadb_node0005 | 31580921856
 analytics          | v_verticadb_node0006 | 31580921856
 analytics          | v_verticadb_node0007 | 31580921856
 analytics          | v_verticadb_node0008 | 31580921856
 analytics          | v_verticadb_node0009 | 31580921856
(9 rows)

See Also