jberkus

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

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

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

Amazon EC2

Small Instance

    m3.medium
    3.5GB RAM
    1 core
    EBS storage, 1000 IOPS

    R/W in-memory test

jberkus

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

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

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)

jberkus

from TwitterAPI import TwitterAPI
import psycopg2
from psycopg2.extras import Json

CONSUMER_KEY = 'Get'
CONSUMER_SECRET = 'your'
ACCESS_TOKEN_KEY = 'own'
ACCESS_TOKEN_SECRET = 'API key'

api = TwitterAPI(CONSUMER_KEY,

jberkus

#!/user/bin/env python

import psycopg2
import datetime
import random
import time
import sys

def randuser():
    return (int(random.random() * 100) + 1)

jberkus

-- function for doing dictionary-style replacement of varaibles
-- in a SQL string.  variables are marked in the text with ${var}
-- and replaced using a json dictionary

create or replace function replace_vars ( somestring text,
    vars JSON )
returns text
language plpgsql
immutable 
as