Skip to content

Instantly share code, notes, and snippets.

View jberkus's full-sized avatar
💭
Catching up and prepping for Kubecon Shanghai

Josh Berkus jberkus

💭
Catching up and prepping for Kubecon Shanghai
View GitHub Profile
@jberkus
jberkus / gist:9815260
Created March 27, 2014 18:54
Flexible Freeze outline
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)
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 August 29, 2015 14:01
Flexible Freeze Script, Version 0.1
'''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 October 8, 2014 21:28
finding needed indexes -- early draft
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
jberkus / gist:1f286e0ec70f19fbb92c
Created October 17, 2014 17:31
JSON test for pgbadger
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
jberkus / gist:d05db3629e8c898664c4
Last active August 29, 2015 14:08
multixact bug?
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 August 29, 2015 14:14
EC2 sizing for benchmarks.
Amazon EC2
Small Instance
m3.medium
3.5GB RAM
1 core
EBS storage, 1000 IOPS
R/W in-memory test
@jberkus
jberkus / gist:f076ab4e680a3a509313
Created March 28, 2015 18:13
pl/python circular quartiles function
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:81ad58603c2697bdd8fb
Last active August 29, 2015 14:17
SQL circular quartiles function
create type numeric_quartiles_plus as (
min float,
q05 float,
q10 float,
q25 float,
q50 float,
q75 float,
q90 float,
q95 float,
max float,
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)