jberkus

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)

jberkus

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

'''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

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)