Vertica Blog

Vertica Blog

DBadmin

Hand writing the text: Helpful Tips

Tracking Raw Schema Size Over Time

James Knicely, Vertica Field Chief Technologist
September 25, 2019

There is no way to go back in time to see how large a schema was a day/month/year ago. But you can use the AUDIT function to get a “raw” size of a schema and track growth over time by storing the results from a daily audit. Using cron, you can run this daily. Helpful...
Focused young woman working at laptop in office

The Vertica Academy Is Open for Learning

Joy King, VP Product & GTM Strategy, Vertica
September 9, 2019

One of the greatest impacts from digital transformation, both from a personal and professional point of view, is not just the democratization of data - a very popular topic here at Vertica! - but the democratization of knowledge. Today, we are excited to announce the new Vertica Academy, a platform that offers self-paced, technical training...
hands sweeping into a dustpan

Easy Development Schema Cleanup (i.e., Have Vertica Drop a Bunch of Tables for You)

James Knicely, Vertica Field Chief Technologist
September 5, 2019

On a development database, I have a lot of tables in the PUBLIC schema having similar names starting with “test_”. I’d like to drop all of these tables quickly. To do that, I can have Vertica generate the DROP commands and then execute them for me! P.S. Be careful with this command! Make sure when...
Helpful Tips in blue text with magnifying glass

Investigate Data Collector Table Data Produced by Scrutinize

James Knicely, Vertica Field Chief Technologist
September 5, 2019

The diagnostics tool scrutinize collects a broad range of information from a Vertica cluster. Part of the output from scrutinize includes data collector table data. Once extracted, we can easily load the data from a data collector table file into a Vertica Flex Table for analysis! Let’s take a look at the data from the...
Cow peering around edge of frame with blue sky and clouds in shape of a comic book thought bubble.

Make a Cow Display Query Results in VSQL

James Knicely, Vertica Field Chief Technologist
August 19, 2019

I used to have fun with the amusing old Linux command Cowsay. It inserts any input into a word bubble and draws an ASCII cow to talk to you. You can make the cow display your query results in VSQL! Helpful Links: https://en.wikipedia.org/wiki/Cowsay https://www.vertica.com/docs/latest/HTML/Content/Authoring/ConnectingToVertica/vsql/Meta-Commands/MetaCommandReference.htm Have fun!
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...