Equars People

I wrote a small tool, postgresqlgraph, to monitor and plot the statistics of a postgresql database, using rrdtool.

The tool is able to monitor every database created inside a postgresql installation.

The parameters plotted for each database in the graph are:

• numbackends: number of backends:
• xact_commit: transactions committed;
• xact_rollback: transactions rolled back;
• blks_read: blocks read from the disk;:
• blks_hit: blocks hit in the cache;
• tup_returned: tuples returned (only in postgresql 8.3)
• tup_fetched: tuples fetched (only in postgresql 8.3)
• tup_inserted: tuples inserted (only in postgresql 8.3)
• tup_updated: tuples updated (only in postgresql 8.3)
• tup_deleted: deleted (only in postgresql 8.3)

For each parameter the graph shows the maximum and average values, and plots the derivative (rate change).

The software is published under GPLv3 .

A demo is available here.

Please feel free to submit me bugs, critics, improvements, or tell me if you find the tool of any worthwhileness.

This blog runs on mephisto. In a standard installation, the database is not optimized. A few indexes (for postgresql, but I think that the could esaily adapted for other database engines) may optimize the performance (YMMV):

1
2
3
4
5
6
7
8

CREATE INDEX cached_pages_url ON cached_pages (site_id, url);
CREATE INDEX contents_type ON contents(site_id, type);
CREATE INDEX tags_name ON tags(name);
CREATE INDEX cached_pages_url2 ON cached_pages (site_id, url,id);
CREATE INDEX contents_pub_at ON contents(published_at);
CREATE INDEX contents_pub_at_type ON contents(published_at, type);
CREATE INDEX contents_perma_type ON contents(site_id,type,permalink, published_at);
CREATE INDEX contents_approv ON contents( article_id,approved, type );

Problem: you have two server in a HA architecture (active/passive) with heartbeat. You want to sync a Postgresql database between the two servers, without using a SAN.

Solution:

• install DRBD and share a partition, e. g. dev/drbd0, where you will store the data directory of postgresql. Mount this partition on the active server, e.g. under /ha;

• create the postgresql data dir under the mountpoint, stop postgresql, and copy the whole content of /var/lib/postgresql in /ha/var/lib/postgresql:

  server1:~# mount /dev/drbd0 /ha
  server1:~# mkdir -p /ha/var/lib/postgresql
  server1:~# /etc/init.d/postgresql-7.4 stop
  server1:~# cp -R /var/lib/postgresql /ha/var/lib/postgresql
  

• install drblinks, which creates a symbolic link of a given directory to the drbd partition. Put in /etc/drbdlinks.conf

   ...
   mountpoint('/ha')
   link('/var/lib/postgresql/')
   ...
  

• start drblinks. Now /var/lib/postgresql should be a symbolic link to /ha/var/lib/postgresql, while the old /var/lib/postgresql has been moved to /var/lib/postgresql.drbdlinks

   server1:~# drbdlinks start
   server1:~# ls -l /var/lib
  ...
  lrwxrwxrwx 1 root     root         23 2007-03-16 16:54 postgresql -> /ha/var/lib/postgresql/
  drwxr-xr-x 3 postgres postgres   4096 2007-03-16 16:03 postgresql.drbdlinks
  ...
  

• Start postgresql and verify that everything is working.

• Now remove the init script of postgresql. That's because at boot the postgresql's init-script has a precedence over the init script of heartbeat, thus starting postgresql with the real /var/lib/postgresql as data directory. Then heartbeat starts, starting drbdlinks, and moves the data directory, creating the symbolic link, while postgresql is running. This creates a lot of inconsistencies in the database. Meanwhile, you want heartbeat taking care of starting or stopping postsgresql after drbdlinks.

    server1:~# mv /etc/init.d/postgresql-7.4  /etc/ha.d/resource.d/
    server1:~# update-rc.d postgresql-7.4 remove
               Removing any system startup links for /etc/init.d/postgresql-7.4 ...
              /etc/rc0.d/K19postgresql-7.4
              /etc/rc1.d/K19postgresql-7.4
              /etc/rc2.d/S19postgresql-7.4
              /etc/rc3.d/S19postgresql-7.4
              /etc/rc4.d/S19postgresql-7.4
              /etc/rc5.d/S19postgresql-7.4
              /etc/rc6.d/K19postgresql-7.4
     server1:~# cat /etc/ha.d/haresources
     server1 drbddisk::r0 Filesystem::/dev/drbd0::/ha::ext3 ...  drbdlinks ... postgresql-7.4
  

• Repeat the same procedure for server2. Beware that the file /etc/ha.d/haresources should be identical on both servers.

Now drbd takes care of syncronizing the partition and thus the database, while heartbeat takes care of starting and stopping the instances of postgresql on both servers.