Vertica Blog

Vertica Blog

Quick Tips

Business card that says Expert Tips,

Stop a Stubborn Query that Won’t Cancel

James Knicely, Vertica Field Chief Technologist
August 19, 2019

Sometimes a running SQL statement hangs and cannot be stopped using the CLOSE_SESSION or INTERRUPT_STATEMENT function. To stop it, first grab the culprit’s SESSION_ID, TRANSACTION_ID, and STATEMENT_ID from the QUERY_REQUESTS system table, then you can move it to a resource pool that has no resources! After the move and subsequent re-plan, the query will “usually”...
Compass rose with true north pointing to "Helpful Tips" text

Change Your User Password in VSQL with a Meta-Command

James Knicely, Vertica Field Chief Technologist
August 14, 2019

The \password VSQL meta-command can be used to change your database user’s password! The admin user can change the database password for any user: Helpful Link: https://www.vertica.com/docs/latest/HTML/Content/Authoring/ConnectingToVertica/vsql/Meta-Commands/MetaCommandReference.htm Have fun!
Hand holding old-fashioned brass balance scale on a black background

View the History of Connection Load Balance Operations

James Knicely, Vertica Field Chief Technologist
August 9, 2019

The Data Collector table DC_LOAD_BALANCE_OPERATIONS tracks the history of connection load balance operations. Helpful Links: https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/ManagingClientConnections/LoadBalancing/ConnectionLoadBalancing.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/Monitoring/Vertica/RetainingMonitoringInformation.htm Have fun!
Hand holding old-fashioned brass balance scale on a black background

Load Balance Older Clients (That You Can’t Upgrade for Some Weird Reason)

James Knicely, Vertica Field Chief Technologist
August 8, 2019

Native connection load balancing is a feature built into the Vertica Analytic Database server and client libraries as well as vsql. To load balance vsql you can use the - -enable-connection-load-balance or -C command line options. In really old versions of vsql those options did not exist. How do you load balance connections from those...
Hand holding old-fashioned brass balance scale on a black background

Testing the Native Load Balancer

James Knicely, Vertica Field Chief Technologist
August 7, 2019

Native connection load balancing is a feature built into the Vertica Analytic Database server and client libraries as well as vsql. The RUN_LOAD_BALANCE_POLICY function returns a string formatted as address:port that results from running the current Native Load Balancer policy. It’s a great tool for testing after enabling a new Load Balance Policy! Because the...
Disk usage, traffic, and other usage concerns

Diving into Disk Usage

Bryan Herger, Vertica Big Data Solution Architect at Micro Focus
August 6, 2019

Would you like to know how much disk space Vertica is using as it runs? This could be useful for capacity planning, monitoring trends, or debugging. Here are some ways to follow disk usage trends and also look at temporary events like Tuple Mover and Join Spills: Helpful Link: https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/ManageDiskSpace/ManagingDiskSpace.htm Have fun!
Vintage businessman concept wearing futuristic helmet at office

Find and Fix Issues from Vertica Query Events

Bryan Herger, Vertica Big Data Solution Architect at Micro Focus
August 5, 2019

Vertica offers tools like the Workload Analyzer in Management Console (MC) to tune up a Vertica Cluster, but there's a simple way to find and fix issues that Vertica observes and records if you aren't using MC. The query_events table captures optimization issues and suggests fixes. Let's take a look at my demo cluster, checking...
Busy traffic jam

Find the Busiest Hour in the Day for I/O

James Knicely, Vertica Field Chief Technologist
August 2, 2019

The IO_USAGE system table provides disk I/O bandwidth usage history for the system. You can query it to find out interesting IO statistics like which hour of the day has the most IO usage on your Vertica cluster! Helpful Link: https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/IO_USAGE.htm Have fun!
Tips and Tricks Orange Button

Theoretical Maximum Number of Rows Per Table Per Node

James Knicely, Vertica Field Chief Technologist
August 1, 2019

The Vertica Table size limit is documented to be 2^64 rows per node. Just how big is that number? That’s 18+ Sextillion rows! I’ve been tracking in a Vertica table, the number of text messages my daughter has sent/received since she first got a cell phone at the age of twelve. Now twelve years later,...

Streaming Data in One Line!

Bryan Herger, Vertica Big Data Solution Architect at Micro Focus
July 31, 2019

Remember that game show, "Name That Tune", where contestants were challenged to name a tune in as few notes as possible? Today's tip converts that for Vertica Big Data, showing how we can ingest streaming data in just one line! A simple way to stream data is to write CSV rows to a network socket....
Digital image of a trash can made of data points on blue background

Watch those Delete Vectors!

Bryan Herger, Vertica Big Data Solution Architect at Micro Focus
July 30, 2019

Vertica is very good at ingesting data, compressing it, and querying at high speed. The trade-off here is that the data is stored in large block files called ROS containers. These containers can grow to large sizes, sometimes over 10 GB, and this makes it impractical to decompress and edit the files during updates and...
Helpful Tips message on post-it note

Restore a Single Database View from a Backup

James Knicely, Vertica Field Chief Technologist
July 29, 2019

The Vertica Backup and Recovery (vbr) utility allows you to back up and restore either the full database, or one or more schema and table objects of interest. It’s a great tool for restoring a table if someone accidentally dropped it or deleted a significant amount of data by mistake. But what about a database...