Cacher is the code snippet organizer for pro developers

# This could also be pplotted with multiplot. To use multiplot with last three plots, one per row,
# issue: `set multiplot layout 3,1` then follow that with the individual plot commands.
plot \
    "pagesandarc.dat" u (timecolumn(1, "%s")):2 w points title "ARC Size Normalized" pt 9 lc 3 ps 1, \
    "" u (timecolumn(1, "%s")):3 w points title "ARC Hits Normalized" pt 1 lc 2 ps 1, \
    "" u (timecolumn(1, "%s")):4 w points title "ARC Misses Normalized" pt 2 lc 7 ps 1, \
    "" u (timecolumn(1, "%s")):5 w points title "ARC Accesses Normalized" pt 6 lc 6 ps 1

plot \
    "pagesandarc.dat" u (timecolumn(1, "%s")):2 w points title "ARC Size Normalized" pt 9 lc 3 ps 1, \
    "" u (timecolumn(1, "%s")):5 w points title "ARC Accesses Normalized" pt 6 lc 4 ps 1
    
plot \
    "pagesandarc.dat" u (timecolumn(1, "%s")):4 w points title "ARC Misses Normalized" pt 2 lc 7 ps 1, \
    "" u (timecolumn(1, "%s")):5 w points title "ARC Accesses Normalized" pt 6 lc 6 ps 1

plot \
    "pagesandarc.dat" u (timecolumn(1, "%s")):3 w points title "ARC Hits Normalized" pt 1 lc 2 ps 1, \
    "" u (timecolumn(1, "%s")):5 w points title "ARC Accesses Normalized" pt 6 lc 6 ps 1

CREATE TABLE IF NOT EXISTS "mem" (
  "timestamp" integer PRIMARY KEY, "uptime" integer,
  "runtime" integer, "interval" integer,
  "availrmem" integer, "desfree" integer, 
  "desscan" integer, "econtig" integer, "fastscan" integer,
  "freemem" integer, "kernelbase" integer, "lotsfree" integer, "minfree" integer,
  "nalloc" integer, "nalloc_calls" integer, "nfree" integer, "nfree_calls" integer,
  "nscan" integer, "pagesfree" integer, "pageslocked" integer, "pagestotal" integer,
  "physmem" integer, "pp_kernel" integer, "slowscan" integer, "swap_alloc" integer,
  "swap_avail" integer, "swap_free" integer, "swap_resv" integer, "free" integer,
  "arc_c" integer, "arc_p" integer, "arc_hits" integer, "arc_misses" integer, 
  "arc_mfu_hits" integer, "arc_mru_hits" integer, "arc_mfu_misses" integer,
  "arc_mru_misses" integer, "arc_mfu_ghost_hits" integer, "arc_mru_ghost_hits" integer, 
  "arc_size_change" integer
);

CREATE INDEX IF NOT EXISTS idx_availrmem ON "mem" ("availrmem" ASC);
CREATE INDEX IF NOT EXISTS idx_freemem ON "mem" ("freemem" ASC);
CREATE UNIQUE INDEX IF NOT EXISTS idx_timestamp ON "mem" ("timestamp" ASC);
CREATE INDEX IF NOT EXISTS idx_pp_kernel ON "mem" ("pp_kernel" ASC);
CREATE INDEX IF NOT EXISTS idx_pageslocked ON "mem" ("pageslocked" ASC);
CREATE INDEX IF NOT EXISTS idx_arc_size_chage ON "mem" ("arc_size_change" ASC);
CREATE INDEX IF NOT EXISTS idx_arc_c ON "mem" ("arc_c" ASC);
CREATE INDEX IF NOT EXISTS idx_arc_p ON "mem" ("arc_p" ASC);

SELECT
	strftime('%s', timestamp, 'unixepoch','-6 hours') AS timestamp,
	(CAST(arc_size_change AS double) - subq.avArcChg) /
		subq.sdArcChg AS "Arc Size Normalized",
	(CAST(arc_hits AS double) - subq.avHits) /
		subq.sdHits AS "Arc Hits Normalized",
	(CAST(arc_misses AS double) - subq.avMisses) /
		subq.sdMisses AS "Arc Misses Normalized",
	(CAST(arc_hits + arc_misses AS double) - subq.avAccesses) /
		subq.sdAccesses AS "Arc Accesses Normalized"
	FROM (
		SELECT
			AVG(arc_size_change) as avArcChg,
			STDEV(arc_size_change) as sdArcChg,
			AVG(arc_hits) as avHits,
			STDEV(arc_hits) as sdHits,
			AVG(arc_misses) as avMisses,
			STDEV(arc_misses) as sdMisses,
			AVG(arc_hits + arc_misses) as avAccesses,
			STDEV(arc_hits + arc_misses) as sdAccesses
		FROM mem
	) subq, mem
ORDER BY timestamp