This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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: |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
'''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. |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Amazon EC2 | |
Small Instance | |
m3.medium | |
3.5GB RAM | |
1 core | |
EBS storage, 1000 IOPS | |
R/W in-memory test |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create type numeric_quartiles_plus as ( | |
min float, | |
q05 float, | |
q10 float, | |
q25 float, | |
q50 float, | |
q75 float, | |
q90 float, | |
q95 float, | |
max float, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create type numeric_quartiles_plus as ( | |
min float, | |
q05 float, | |
q10 float, | |
q25 float, | |
q50 float, | |
q75 float, | |
q90 float, | |
q95 float, | |
max float, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) |
OlderNewer