Getting it working
15 Nov 2015
First, we need a RAM disk we can use. Happily, on Ubuntu,
/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