Skip to content

Instantly share code, notes, and snippets.

Catching up and prepping for Kubecon Shanghai

Josh Berkus jberkus

Catching up and prepping for Kubecon Shanghai
View GitHub Profile
jberkus / gist:9815260
Created March 27, 2014 18:54
Flexible Freeze outline
View gist:9815260
Config File:
Series of time windows for "low traffic" points.
e.g. "Sunday 1am-4am"
Postgres connection info for 1 database
vacuum settings
min_freeze threshold (default 1,000,000)
View gist:5b16ddef1f6f64bcf8ac
1) alter postgresql.conf
checkpoint_segments = 100
checkpoint_completion_target = 0.9
appropriate shared_buffers
2) initialize pgbench with pgbench -s 1000 -i bench
do one run on the SSD, and if that looks good, one on the HDD
3) run pgbench test with:
jberkus /
Last active August 29, 2015 14:01
Flexible Freeze Script, Version 0.1
'''Flexible Freeze script for PostgreSQL databases
Version 0.3
(c) 2014 PostgreSQL Experts Inc.
Licensed under The PostgreSQL License
This script is designed for doing VACUUM FREEZE or VACUUM ANALYZE runs
on your database during known slow traffic periods. If doing both
vacuum freezes and vacuum analyzes, do the freezes first.
jberkus / gist:de7cfdd3b6e0b187f63d
Created October 8, 2014 21:28
finding needed indexes -- early draft
View gist:de7cfdd3b6e0b187f63d
SELECT schemaname, relname,
seq_scan as table_scans,
idx_scan as index_scans,
pg_size_pretty(pg_relation_size(relid)) as table_size,
n_tup_ins + n_tup_del + n_tup_upd + n_tup_hot_upd as write_activty
FROM pg_stat_user_tables
WHERE seq_scan > 1000
AND seq_scan > ( idx_scan / 10 )
AND pg_relation_size(relid) > ( 16000000 )
ORDER BY pg_relation_size(relid) desc;
jberkus / gist:1f286e0ec70f19fbb92c
Created October 17, 2014 17:31
JSON test for pgbadger
View gist:1f286e0ec70f19fbb92c
2014-10-14 18:28:49.330 UTC,"postgres","postgres",30471,"[local]",543d6b47.7707,1,"CREATE TABLE",2014-10-14 18:28:23 UTC,1/0,0,LOG,00000,"duration: 35.077 ms statement: create table jsontest ( id serial, somejson jsonb );",,,,,,,,,"psql"
2014-10-14 18:33:37.956 UTC,"postgres","postgres",30471,"[local]",543d6b47.7707,2,"INSERT",2014-10-14 18:28:23 UTC,1/9,0,ERROR,22P02,"invalid input syntax for type json","Token ""="" is invalid.",,,,"JSON data, line 1: { ""doc"" =...","insert into jsontest ( somejson ) values ( '{ ""doc"" = ""af42342e6"", ""content"": ""there''s """"something"""" I want to tell you"" }' );",44,,"psql"
2014-10-14 18:33:56.307 UTC,"postgres","postgres",30471,"[local]",543d6b47.7707,3,"INSERT",2014-10-14 18:28:23 UTC,1/10,0,ERROR,22P02,"invalid input syntax for type json","Expected "","" or ""}"", but found """"something"""".",,,,"JSON data, line 1: ..."" : ""af42342e6"", ""content"": ""there's """"something""...","insert into jsontest ( somejson ) values ( '{ ""doc"" : ""af42342e6"", ""content
jberkus / gist:d05db3629e8c898664c4
Last active August 29, 2015 14:08
multixact bug?
View gist:d05db3629e8c898664c4
Multixact numbers:
Latest checkpoint's NextXID: 0/1143490804
Latest checkpoint's NextOID: 371144030
Latest checkpoint's NextMultiXactId: 165978104
Latest checkpoint's NextMultiOffset: 798828566
Latest checkpoint's oldestXID: 945761490
Latest checkpoint's oldestXID's DB: 370038709
Latest checkpoint's oldestActiveXID: 1143490803
Latest checkpoint's oldestMultiXid: 123452201
jberkus / gist:2d614fa3eccbad83d008
Last active August 29, 2015 14:14
EC2 sizing for benchmarks.
View gist:2d614fa3eccbad83d008
Amazon EC2
Small Instance
1 core
EBS storage, 1000 IOPS
R/W in-memory test
jberkus / gist:f076ab4e680a3a509313
Created March 28, 2015 18:13
pl/python circular quartiles function
View gist:f076ab4e680a3a509313
create type numeric_quartiles_plus as (
min float,
q05 float,
q10 float,
q25 float,
q50 float,
q75 float,
q90 float,
q95 float,
max float,
jberkus / gist:81ad58603c2697bdd8fb
Last active August 29, 2015 14:17
SQL circular quartiles function
View gist:81ad58603c2697bdd8fb
create type numeric_quartiles_plus as (
min float,
q05 float,
q10 float,
q25 float,
q50 float,
q75 float,
q90 float,
q95 float,
max float,
View gist:7ddda7d124e027788d05
benchcall = "{bdir}/{pgb} -T {ttime} -c {clients} -j {jobs} -s {scale} -r -n {scriptfiles} -p {port} -U {user} -h {host} {dbname} > {results}.results".format(bdir=abench,pgb=pgbench,ttime=numsec,clients=numclients,jobs=numthreads,scale=numrows,scriptfiles=filelist,dbname=db,results=abench,port=dbport,user=dbuser,host=dbhost)