vsql -AXtq -F ' ' -f - <<-EOF | SELECT WIDTH_BUCKET(f, 0, 1, 100), COUNT(*) FROM public.norm GROUP BY 1 ORDER BY 1 ; EOF gnuplot -e "set term png ; plot '-' using 1:2 with boxes" > distr.png
And here is the result:
Example 2: Analyzing Vertica Catalog Size by node and time
First we extract the data needed for post-processing to separate data into different blocks (one per node):
vsql -AXtq -U dbadmin -o /tmp/catsize.dat -f - <<-EOF
SELECT
CASE
WHEN LAG(node,1,'') OVER(ORDER BY node, Hour) = '' THEN node || CHR(10)
WHEN LAG(node,1,'') OVER(ORDER BY node, Hour) <> node
THEN CHR(10) || CHR(10) || node || CHR(10)
ELSE '' END ||Hour::VARCHAR(32)
, catalog_size_mb
FROM (
SELECT
REGEXP_SUBSTR(node_name,'[^_]*$') AS node,
ROUND(time, 'HH') AS Hour,
SUM((total_memory_max_value - free_memory_min_value)
)/1024/1024 AS Catalog_Size_MB
FROM
dc_allocation_pool_statistics_by_hour
GROUP BY 1, 2
) input;
EOF
And then we use gnuplot to produce the graph:
gnuplot <<-EOF
set term png
set datafile separator "|"
set xdata time
set timefmt "%Y-%m-%d %H:%M:%S"
set format x "%m-%d\n%H:%M"
set output "catsize_by_hour.png"
nodes = 3
plot for [i=0:(nodes-1)] '/tmp/catsize.dat' index i u 1:2 w lines title columnhead(1)
EOF
The result is similar to the following:
About the Author
Phil Molea
Sr. Information Developer, Vertica
Phil developed technical documentation in the areas of security and diagnostics for the Vertica Analytics Platform that enabled companies to extract value from their data at the speed and scale they need to thrive in today’s economy.
(Sadly, Phil passed away recently. He will be missed.)
