jberkus

Amazon EC2

Small Instance

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

    R/W in-memory test

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

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

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

SELECT * FROM (
SELECT
  schemaname, tablename, 
  ROUND(CASE WHEN otta=0 THEN 0.0 ELSE ((sml.relpages/otta::numeric) * 100 - 100) END) AS tbloat,
  CASE WHEN relpages < otta THEN 0 ELSE round(bs*(sml.relpages-otta)::numeric / (1024^2)::numeric , 2 ) END AS wastedmb,
  ROUND(AVG(CASE WHEN iotta >= ipages THEN 0 
  		 WHEN iotta = 0 THEN 0 
		 ELSE (ipages/iotta::numeric) * 100 - 100 END), -1) AS idxbloat,
  SUM(CASE WHEN ipages < iotta THEN 0 ELSE round( bs*(ipages-iotta)::numeric / (1024^2)::numeric, 2 ) END) AS wastedidxmb
FROM (

jberkus

-- check for containment
-- i.e. index A contains index B
-- and both share the same first column
-- but they are NOT identical

WITH index_cols_ord as (
    SELECT attrelid, attnum, attname
    FROM pg_attribute
        JOIN pg_index ON indexrelid = attrelid
    WHERE indkey[0] > 0

jberkus

-- check for exact matches
WITH index_cols_ord as (
    SELECT attrelid, attnum, attname
    FROM pg_attribute
        JOIN pg_index ON indexrelid = attrelid
    WHERE indkey[0] > 0
    ORDER BY attrelid, attnum
),
index_col_list AS (
    SELECT attrelid,

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

WITH table_scans as (
    SELECT relid,
        tables.idx_scan + tables.seq_scan as all_scans,
        ( tables.n_tup_ins + tables.n_tup_upd + tables.n_tup_del ) as writes,
                pg_relation_size(relid) as table_size
        FROM pg_stat_user_tables as tables
),
all_writes as (
    SELECT sum(writes) as total_writes
    FROM table_scans

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: