install | Michael Paquier

Postgres feature highlight: pg_buffercache

Nov 272012

pg_buffercache is a PostgreSQL contrib module allowing to get an instant relation-based view of the shared buffer usage by querying the wanted server.

This can be pretty useful for performance analysis of queries on a given relation as it allows to have a look at how much a relation is cached. In the case of data cached for a given relation, you do not need to access data directly on disk to retrieve the data and can directly rely on the cache, so the data fetching is simply faster, by a factor of the order of 1000 (Shared memory/disk speed difference). Take care however that a shared lock is taken when analyzing the shared buffer content, so it can impact concurrent queries.

In order to install pg_buffercache from source code, you need to perform the following commands to install its related files.
cd contrib/pg_buffercache make install
Depending on your environment and the Postgres packages you installed, you might not need to do that of course. Once this is done the following files are installed in $INSTALL_FOLDER/share/extension.
ls $INSTALL_FOLDER/share/extension pg_buffercache--1.0.sql pg_buffercache.control pg_buffercache--unpackaged--1.0.sql

Then connect to your Postgres server and finish pg_buffercache installation with CREATE EXTENSION command.
postgres=# CREATE EXTENSION pg_buffercache; CREATE EXTENSION postgres=# \dx pg_buffercache List of installed extensions Name | Version | Schema | Description ----------------+---------+--------+--------------------------------- pg_buffercache | 1.0 | public | examine the shared buffer cache (1 row)

The view created after installation of the extension called pg_buffercache has several columns.

bufferid, the block ID in the server buffer cache
relfilenode, which is the folder name where data is located for relation
reltablespace, Oid of the tablespace relation uses
reldatabase, Oid of database where location is located
relforknumber, fork number within the relation
relblocknumber, age number within the relation
isdirty, true if the page is dirty
usagecount, page LRU (least-recently used) count

The buffer ID corresponds (surprisingly!) to the number of the buffer used by the relation. The total number of buffers available is defined by two things:

Size of a buffer block, this is defined by the option –with-blocksize when running configure. Default value if 8kB, which is sufficient in most of the situations, but you can go up to 32kB or down to 1kB depending on the situations. In order to change this value, it is necessary to recompile the code and rebuild a database server from the initdb step.
Number of shared buffer allocated for the system defined by shared_buffers in postgresql.conf. This can be changed at will by restarting the server.

For example, by using 128MB of shared_buffers with 8kB of block size, there are 16,384 buffers, so pg_buffercache has the same number of 16,384 rows.
With shared_buffers set at 256MB and block-size at 1kB, there are 262,144 buffers.

Let’s have a quick look at the feature with a pgbench database that has been already used with a 5-minute test. This simple query (given by the documentation) provides the number of buffers used by each relation of the current database.
postgres=# SELECT c.relname, count(*) AS buffers postgres=# FROM pg_buffercache b INNER JOIN pg_class c postgres=# ON b.relfilenode = pg_relation_filenode(c.oid) AND postgres=# b.reldatabase IN (0, (SELECT oid FROM pg_database postgres=# WHERE datname = current_database())) postgres=# GROUP BY c.relname postgres=# ORDER BY 2 DESC postgres=# LIMIT 10; relname | buffers -----------------------+--------- pgbench_history | 2515 pgbench_accounts | 1818 pgbench_accounts_pkey | 276 pgbench_tellers | 61 pgbench_branches | 61 pg_attribute | 22 pg_statistic | 11 pg_proc | 10 pg_class | 8 pg_proc_oid_index | 8 (10 rows)
Not only relation data, but also indexes are included in the image given. The server of this example used an amount of shared_buffer of 128MB, so all the relation data was completely cached.
The usage count of each buffer page is a good indication of how many times a buffer is used after being created. In case it is low, it means that the buffers do not survive a long time and that the cache hit ratio is low.

Redis: first steps, code fetch, install and server creation

redis No Responses »

Nov 142012

Redis is an open source project providing key/value store features in a database server. This means that basically you can store in the database a value that has a given key and then retrieve the value using its key.
It supports advanced data types like strings, lists (elements sorted by insertion order), sets (unordered collection of elements) and sorted sets (collection of elements ordered by a key given by user). There are also other things supported like hashes or atomic integer incrementation. Feel free to have a look at the documentation about that.

This post will not deal with advanced aspects of Redis: this will be the subject of other articles. What is going to be described here is how to get Redis, run a server and then perform some simple operations.

Redis is honestly a famous project (5,000 followers on Github), so I am sure you will be able to install it easily whatever your environment.
For example, in the case of Archlinux, you only need to use a simple pacman command.
pacman -S redis
For CentOS:
yum install redis

But you honestly feel more what you do if you compile and install the code yourself from Github for example. So let’s get the code.
mkdir $REDIS_CODE cd $REDIS_CODE git init git remote add origin https://github.com/antirez/redis.git git fetch origin git checkout unstable
The unstable branch is the main development branch, similar to master in the case of postgresql. You can refer to other branches for stable releases like 2.6 or 2.8.

The server code is located in folder src/, but you first need to compile the dependencies or you will get errors of the following type:
$ make clang: error: no such file or directory: '../deps/hiredis/libhiredis.a' clang: error: no such file or directory: '../deps/lua/src/liblua.a' make[1]: *** [redis-server] Error 1 make: *** [all] Error 2

So here be sure to install first the dependencies as below.
cd deps make lua hiredis linenoise
Then finalize compilation.
cd $REDIS_CODE/src make

Finally install the binaries in a wanted folder.
make PREFIX=$REDIS_INSTALL install

Once installed, you will notice several binaries but the most important ones are redis-server (used to boot a server) and redis-cli (client to connect to a server).

In order to launch a server on default port 6379, simply launch this command, assuming that $REDIS_INSTALL is included in PATH:
redis-server

A Redis server can use a configuration file when booted, which can be specified like this:
redis-server /path/to/conf/redis.conf
There is also a template of redis.conf in the root tree of source code.

All the options of redis.conf can be specified via command line, here are some of them I find pretty useful for beginners.

–dir $DIR, to specify the directory where database dump file or log files are written to. The default value is “./”, so all the files are written in this case in the folder where redis-server is launched. I personally find that not really intuitive but…
–port $PORT_NUMBER, port number where server listens to. Default is 6379.
–logfile, name of file where logs are written. Default is stdout. Once again here I recommend using a clear file name combined with –dir to bring clarity to your database servers.

Then, in order to connect to the server, simply use redis-cli (see redis-cli –help for details about the options).
$ redis-cli redis 127.0.0.1:6379>

Then you are ready to operate on your server. Let’s do here a simple get/set.
redis 127.0.0.1:6379> set foo bar OK redis 127.0.0.1:6379> get foo "bar"

A last thing, I quickly wrote two scripts in case you are interested:

redis_compile, script that can be used to compile code, perform tests and do some other tricks
redis_start, script that can be used to set up a Redis cluster with master and slaves

Please note that those scripts do not have the granularity necessary for a use in production and they are only dedicated to development.

I have not yet discussed about the numerous features of Redis like things related to the cluster structure (master/slave replication, memory management), or data structures (lists, sets), but they will be covered in some future posts.

Postgres feature highlight: hstore

PostgreSQL 5 Responses »

Oct 222012

hstore is a PostgreSQL contrib module in core code for a pretty long time. Its code is located in contrib/hstore in source folder. It is particularly useful to store sets of key/value in a single table column.

Since Postgres 9.1, its installation needs to be done in two phases.
First install the hstore library, here done from the source code. Please note that your postgres package normally already contains it.
So after downloading the source code and installing the core, do the following commands.
cd $PG_SOURCE_ROOT cd contrib/hstore make install
At this point all the libraries and files related to hstore are installaed in $INSTALL_FOLDER/share/extension.
ls $INSTALL_FOLDER/share/extension hstore--1.0--1.1.sql hstore--1.1.sql hstore--unpackaged--1.0.sql hstore.control

Then connect to your Postgres server and finish hstore installation with CREATE EXTENSION command.
postgres=# CREATE EXTENSION hstore; CREATE EXTENSION postgres=# \dx hstore List of installed extensions Name | Version | Schema | Description --------+---------+--------+-------------------------------------------------- hstore | 1.1 | public | data type for storing sets of (key, value) pairs (1 row)
With a psql client, ‘\dx’ allows to check the list of extensions already installed on your server.

A new column type called hstore has been added. This is the column used to store the list of key/value pairs for the table.
Let’s take a table referencing a list of products as an example.
postgres=# create table products (id serial, characs hstore); CREATE TABLE
The insertion of data can be done with several methods. Here are some of them.
postgres=# -- common insertion postgres=# INSERT INTO products(characs) VALUES ('author=>Dave, date=>"Dec 2012", price=>"500", currency=>"dollar"'); INSERT 0 1 postgres=# -- array-based insertion postgres=# INSERT INTO products (characs) VALUES (hstore(array['author','date','stock'],array['Mike','Nov 2012','200'])); INSERT 0 1 postgres=# -- single-pair insertion postgres=# INSERT INTO products (characs) VALUES (hstore('author','Kim')); -- single-element INSERT 0 1 postgres=# SELECT * FROM products; id | characs ----+---------------------------------------------------------------------------- 1 | "date"=>"Dec 2012", "price"=>"500", "author"=>"Dave", "currency"=>"dollar" 2 | "date"=>"Nov 2012", "stock"=>"200", "author"=>"Mike" 3 | "author"=>"Kim" (3 rows)

Based on the existing fields, it is also possible to add or update values for a given key using a concatenate-based method.
Here is the update of a key “author”.
postgres=# UPDATE products SET characs = characs || 'author=>Sarah'::hstore where id = 1; UPDATE 1 postgres=# select * from products where id = 1; id | characs ----+----------------------------------------------------------------------------- 1 | "date"=>"Dec 2012", "price"=>"500", "author"=>"Sarah", "currency"=>"dollar" (1 row)
If the key updated is not present in existing list, it is simply added as a new element.

You can also delete single elements.
postgres=# UPDATE products SET characs = delete(characs,'price') where id = 1; UPDATE 1 postgres=# select * from products where id = 1; id | characs ----+------------------------------------------------------------- 1 | "date"=>"Dec 2012", "author"=>"Sarah", "currency"=>"dollar" (1 row)

SELECT query can also use key-based scan. Here for instance this query looks for the products with less than 300 stocks.
postgres=# SELECT id FROM products WHERE (characs->'stock')::int <= 300; id ---- 2 (1 row)
(OK, not the best way of doing for your application but this is just a scholar example!).

On top of that, hstore also supports gin and gist indexes for the operators @>, ?, ?& and ?, as well as btree and hash indexes for '='.
postgres=# create index products_index on products(characs); CREATE INDEX

Hope this gives a good introduction to hstore.

Clean install of MacOS Lion and Mountain Lion

MacOS No Responses »

Aug 142012

Each time a new OS is out, there are always cool new features you want to try and so you need absolutely to change the OS of your machine.
There are 2 ways to change the OS:

Upgrade the OS
Perform a clean install

Upgrading the OS is approximately OK for MacOS as each new release maintains a certain consistency in the system. But you have to keep in mind that each upgrade might let old pieces of files on your system and those rests might interact with the new features you got in.

Personally, I am more a fan of the solution consisting of making a new system entirely from scratch instead of upgrading as such rests from past OS versions can really become annoying in long term.
There are several ways to perform a clean install of MacOS, but one caught my attention by its facility (aimed for lazy guys, like the author of this article).

There is a small utility called Lion DiskMaker2, whose final version 2.0 is out since the beginning of August 2012, allowing you to create a bootable USB key or a disk that can be used to install a MacOS at machine start-up. This is honestly useful, as you can set up everything in a couple of clicks.

So, here are the main steps to perform the clean install based on this utility.

Download MacOS Mountain Lion 10.8.x or Lion 10.7 from the Apple Store.
Download and launch Lion DiskMaker2 to create a bootable drive.
DiskMaker2 will automatically detect the systems that can be installed on your drive. Then follow the instructions.
In case you wish to use a USB drive, use at least something with 8GB of space
Once the bootable drive is made, shutdown your machine.
While maintaining pushed the Option key on Keyboard, start your machine. You are redirected to the MacOS backup screen.
There are several disks listed like RecoveryHD, select the bootable drive you just built (its name has been defined when creating it with DiskMaker2)
Enter the disk utility and then delete completely the original disk of your machine
Install the OS of your USB key and follow the steps to finish this clean install

This description might lack of precisions, but installing a new MacOS version is really intuitive. Be only sure to press the Option key when restarting your machine!

Start a Postgres-XC cluster in (more or less) 10 commands

PostgreSQL 6 Responses »

May 262012

First you need to download the latest version of Postgres-XC from here.

Then open the tarball and install the binaries as you would do with a normal PostgresSQL.
configure --prefix=$INSTALL_FOLDER make make install
$INSTALL_FOLDER is the folder where to install the sources. In this post $PATH redirects to $INSTALL_FOLDER so no need to specify a folder when launching commands.

Next, the goal is to install a cluster when few simple commands. Once you are done, your cluster will have the same shape as picture below.

Assuming that you are familiar with Postgres-XC architecture, this cluster is made with 1 Coordinator (to which your application connects), 2 Datanodes (meaning that your table data can be distributed up to 2 nodes) and a GTM, mandatory unique component distributing transaction ID and snapshot in the cluster.
If you are not familiar with the architecture, you can still refer to documents located here. Among the documents available, the tutorial done at PGCon 2012 is a good beginning.
For simplicity’s sake, all the nodes are installed on a local machine.

Like PostgreSQL, each node of Postgres-XC needs a data folder. All of them are located in $DATA_FOLDER.
So let’s move in and initialize each node.
cd $HOME/pgsql initgtm -Z gtm -D gtm # Initialize GTM initdb -D datanode1 --nodename dn1 # Initialize Datanode 1 initdb -D datanode2 --nodename dn2 # Initialize Datanode 2 initdb -D coord1 --nodename co1 # Initialize Coordinator 1

Then you need to modify manually the port value of Datanode 1 and Datanode 2 in each postgresql.conf.
cd datanode1 # or `cd datanode2` vim postgresql.conf
Then change the line “#port = 5432″ by “port = 15432″ for Datanode 1, and “port = 15433″ for Datanode 2.

Then it is time to start up the cluster.
gtm -D gtm & # Start-up GTM postgres -X -D datanode1 -i & # Start Datanode 1 postgres -X -D datanode2 -i & # Start Datanode 2 postgres -C -D coord1 -i & # Start Coordinator 1

What remains is to set up the Coordinator to make him know about Datanode 1 and 2.
So connect to coordinator 1.
psql postgres
Then launch that to finish setting up cluster:
CREATE NODE dn1 WITH (TYPE='datanode', PORT=15432); CREATE NODE dn2 WITH (TYPE='datanode', PORT=15433); select pgxc_pool_reload();

And you are done.
Now you can connect to Coordinator 1 and test your newly-made cluster.
12 short commands have been enough once binaries have been installed.

Older Entries

Michael Paquier

Postgres feature highlight: pg_buffercache

Redis: first steps, code fetch, install and server creation

Postgres feature highlight: hstore

Clean install of MacOS Lion and Mountain Lion

Start a Postgres-XC cluster in (more or less) 10 commands

My Tweets

Follow Me

Links

Michael Paquier

Postgres feature highlight: pg_buffercache

Redis: first steps, code fetch, install and server creation

Postgres feature highlight: hstore

Clean install of MacOS Lion and Mountain Lion

Start a Postgres-XC cluster in (more or less) 10 commands

My Tweets

Follow Me

Links

Tags