Getting it working
15 Nov 2015
First, we need a RAM disk we can use. Happily, on Ubuntu,
the /run dir is a tmpfs mount, so let's use that!
$ su -
# mkdir -p /run/pgsql-9.4.5-data
# chown -R postgres:postgres /run/pgsql-9.4.5-data
# chmod 700 /run/pgsql-9.4.5-data
# su - postgres
$ /usr/local/pgsql-9.4.5/bin/initdb \
--pgdata=/run/pgsql-9.4.5-data \
--encoding=UTF8 \
--no-locale
Normally, we would edit postgresql.conf to tune our PostgreSQL instance to run optimally with our compter's resources. However, all data in /run/pgsql-9.4.5-data will be lost on the next reboot. So instead, let's specify all the settings at the command-line.
Here are the settings that we want to look at modifying from their defaults:
First, turn off fsync; it makes no sense on a ramdisk:
#fsync = on ==> fsync = off
Consider turning all of these memory settings down to their minimums, seeing as our whole dataset is already cached in RAM anyway:
shared_buffers = 128MB ==> shared_buffers = 128kB #temp_buffers = 8MB ==> temp_buffers = 800kB #work_mem = 4MB ==> work_mem = 64kB #maintenance_work_mem = 64MB ==> maintenance_work_mem = 1024kB
You should't be using WAL anyway (use unlogged tables) but try tuning this down to the minimum, too, except for checkpoint_segments, which may as well be as high as possible in case you accidentally use WAL:
#wal_buffers = -1 ==> wal_buffers = 32kB #checkpoint_segments = 3 ==> checkpoint_segments = 64
Obviously, random_page_cost is exactly the same as seq_page_cost in RAM, but additionally, they are the same as cpu_tuple_cost:
#seq_page_cost = 1.0 ==> seq_page_cost = 0.01 #random_page_cost = 4.0 ==> random_page_cost = 0.01
Try tuning down effective_cache_size, seeing as you should encourage direct reads from ramdisk:
#effective_cache_size = 4GB ==> effective_cache_size = 64kB
So here's what our command line looks like to start up postgres:
/usr/local/pgsql-9.4.5/bin/postgres \
-D /run/pgsql-9.4.5-data \
--fsync=off \
--shared-buffers=128kB \
--temp-buffers=800kB \
--work-mem=64kB \
--maintenance-work-mem=1024kB \
--wal-buffers=32kB \
--checkpoint-segments=64 \
--seq-page-cost=0.01 \
--random-page-cost=0.01 \
--effective-cache-size=64kB
Seeing as this is essentially a temporary database, we may as well use the built-in postgres database and postgres user instead of going through the bother of creating a different user and database.
We need to load our schema, so we can do that now.
# gvim /usr/local/pgsql-9.4.5/bin/ramdb.sql
-- Note that this might be run multiple times on the same
-- database, so make it idempotent.
-- Also, use unlogged tables to bypass the WAL to increase performance
create unlogged table if not exists sessions (
id uuid constraint session_pk primary key not null,
data text not null);
# chmod +x /usr/local/pgsql-9.4.5/bin/ramdb.sql # chown postgres:postgres /usr/local/pgsql-9.4.5/bin/ramdb.sql
And here's how we load it in:
/usr/local/pgsql-9.4.5/bin/psql \
-X \
-U postgres \
-d postgres \
-f /usr/local/pgsql-9.4.5/bin/ramdb.sql
Now we can log in and start using our in-memory PostgreSQL:
/usr/local/pgsql-9.4.5/bin/psql \
-U postgres \
-d postgres