phund

PSQL

Magic words:

psql -U postgres

Some interesting flags (to see all, use -h or --help depending on your psql version):

-E: will describe the underlaying queries of the \ commands (cool for learning!)
-l: psql will list all databases and then exit (useful if the user you connect with doesn't has a default database, like at AWS RDS)

Setting up and installing rbenv, ruby-build, rubies, rbenv-gemset, and bundler

This guide enables you to install (ruby-build) and use (rbenv) multiple versions of ruby, isolate project gems (gemsets and/or bundler), and automatically use appropriate combinations of rubies and gems.

TL;DR Demo

# Ensure system is in ship-shape.

aptitude install git zsh libssl-dev zlib1g-dev libreadline-dev libyaml-dev

For a project I am on I need to use a key-value store to converts file-paths to fixnum IDs. The dataset will typically be in the range of 100 000 to 1 000 000. These tests use 305 000 file paths to fixnum IDs.

The Different Key-Value stores tested are:

Daybreak: "Daybreak is a simple and very fast key value store for ruby" GDBM: GNU dbm. "a simple database engine for storing key-value pairs on disk." DBM: "The DBM class provides a wrapper to a Unix-style dbm or Database Manager library" PStore: "PStore implements a file based persistence mechanism based on a Hash. "

Out of these, all except Daybreak are in the Ruby standard library.

Tuning ZFS + Postgres to outperform EXT4 + Postgres

Please refer to ZFS 2-3x slower than EXT4 to see how ZFS defaults + Postgres defaults severely underperform EXT4 defaults + Postgres defaults (and also to know more about the system on which these benchmarks were performed). This page documents how to tune ZFS + Postgres to give better performance for the tpcb-like benchmark.

BIG FAT WARNING

Please do not copy these settings blindly because I am myself not clear on why/how these settings had the impact they did. For example, I cannot explain why full_page_writes=off independently did not give that much boost, nor did an optimized PG configuration. However, putting both of them together gave a 2-4x boost compared to baseline numbers.

	-- when you have a record which looks like this
	--
	-- id: 5,
	-- properties: {
	-- ages: [20, 30]
	-- }
	--
	-- it is a bit of a pain if you need to query based on the contents of the "ages" array inside the JSON object "properties"
	-- because PG currently lacks easy to use operators to work with JSON arrays

	ab \
	-n 1000 \
	-c 20 \
	-s 30 \
	-p post-data.txt \
	-T 'application/x-www-form-urlencoded; charset=UTF-8' \
	-v 3 \
	-H "X-Requested-With: XMLHttpRequest" \
	-H "X-Ajax-Referer: http://example.com" \
	-H "Accept-Encoding: gzip, deflate" \

	-- show running queries (pre 9.2)
	SELECT procpid, age(clock_timestamp(), query_start), usename, current_query
	FROM pg_stat_activity
	WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%'
	ORDER BY query_start desc;

	-- show running queries (9.2)
	SELECT pid, age(clock_timestamp(), query_start), usename, query
	FROM pg_stat_activity
	WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'

	history.replaceState && history.replaceState(
	null, '', location.pathname + location.search.replace(/[\?&]message=[^&]+/, '').replace(/^&/, '?')
	);