matthew-n

#!/bin/bash

#set -x
#set -e

page_size=`getconf PAGE_SIZE`
phys_pages=`getconf _PHYS_PAGES`
phys_memory_kb=$[ $phys_pages * $page_size / 1024 ]
kb_pct=$[ 1024 / 100]

matthew-n

Overview

table -> after trigger (U/I/D) -> eval quals -> write out (old_ctid, new_ctid, table_name, timestamp) to stg client process collects from stg table only one table_name selecting old_ctid and new_ctid for each

matthew-n

#!/bin/bash
set -x
set -e

sec_list="seq-read seq-write random-read random-write active-db"
rw_rate_list="75 80 85 90"
io_thread_list="1 4 8 16 24"

for class in $sec_list; do
  for ratio in $rw_rate_list; do

matthew-n

[global]
  ioengine=libaio
  direct=1
  invalidate=1
  verify=0
  ramp_time=2s
  randrepeat=0
  time_based
  runtime=5m
  filesize=2G

matthew-n

# import config.
# You can change the default config with `make cnf="config_special.env" build`
cnf ?= config.env
include $(cnf)
export $(shell sed 's/=.*//' $(cnf))

# import deploy config
# You can change the default deploy config with `make cnf="deploy_special.env" release`
dpl ?= deploy.env
include $(dpl)

matthew-n

DROP TABLE IF EXISTS pgbench_generic_log;
CREATE TABLE pgbench_generic_log (
  mtime timestamptz not null default now(),
  action char not null check (action in ('I', 'U', 'D')),
  username text not null,
  table_name text not null,
  row_data jsonb not null
);

CREATE INDEX ON pgbench_generic_log USING brin (mtime);

matthew-n

CREATE FUNCTION dbo.udf_ipv4_format (@addr binary(16) )
RETURNS varchar(20) with SCHEMABINDING AS
BEGIN 
return IIF(SUBSTRING(@addr,0,8)=0,
			CONCAT( 
				CAST(SUBSTRING(@addr,13,1) AS INT),'.',
				CAST(SUBSTRING(@addr,14,1) AS INT),'.',
				CAST(SUBSTRING(@addr,15,1) AS INT),'.',
				CAST(SUBSTRING(@addr,16,1) AS INT)
			),NULL)

matthew-n

/* original code from: http://davebland.com/a-faster-alternative-to-sp_helpdb */

DECLARE @t1 DATETIME;
DECLARE @t2 DATETIME;

SET @t1 = GETDATE();

CREATE TABLE #DBSize
(
    DatabaseName VARCHAR(200),

matthew-n

CREATE TABLE ##sample_set (
	sample_set_id INT NOT NULL IDENTITY(1,1),
	[database_id] INT NOT NULL,
	[database_name] NVARCHAR(128) NOT NULL,
	[table_id] INT NOT NULL,
	[table_name] NVARCHAR(128) NOT NULL,
	[column_id] INT NOT NULL,
	[column_name] NVARCHAR(128) NOT NULL,
	[stats_id] INT NULL,
	[status_name] NVARCHAR(128) NULL,

matthew-n

Setting up for minimal user (non-dba) permissions

One-time steps

USE master;
GO

CREATE CERTIFICATE diagnostics_cert
 ENCRYPTION BY PASSWORD = '5OClockSomewhere'