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
Block or report user

Report or block jberkus

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
@jberkus
jberkus / gist:9815260
Created Mar 27, 2014
Flexible Freeze outline
View gist:9815260
Specification:
BASIC VERSION
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
jberkus / flexible_freeze.py
Last active Aug 29, 2015
Flexible Freeze Script, Version 0.1
View flexible_freeze.py
'''THIS SCRIPT HAS BEEN REPLACED WITH THE PROJECT AT https://github.com/jberkus/flexible-freeze'''
'''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
jberkus / gist:de7cfdd3b6e0b187f63d
Created Oct 8, 2014
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;
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
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
jberkus / gist:2d614fa3eccbad83d008
Last active Aug 29, 2015
EC2 sizing for benchmarks.
View gist:2d614fa3eccbad83d008
Amazon EC2
Small Instance
m3.medium
3.5GB RAM
1 core
EBS storage, 1000 IOPS
R/W in-memory test
@jberkus
jberkus / gist:81ad58603c2697bdd8fb
Last active Aug 29, 2015
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,
@jberkus
jberkus / gist:f076ab4e680a3a509313
Created Mar 28, 2015
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,
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)
You can’t perform that action at this time.