A Hacker's Diary

xlogdump 0.5.1 released

2011-11-15T19:02:00.001+09:00

The latest xlogdump has been released. This version allows users to lookup object names from object ids for built-in database objects by reading the lookup table file instead of the system catalog. ---------------------------------------------------------------------- 2011-11-15 Satoshi Nagayasu * Version 0.5.1 * Allows to lookup the object names for

Performance impact of the pg_stat_statements module (Updated: Nov-04-2011)

2011-10-28T23:07:00.012+09:00

When I tweeted that I thought pg_stat_statements should be integrated to the core a few weeks ago, Josh responded me that it would be up to submitting a patch and figuring out the performace overhead. So, before starting a patch work, I've decided to figure out the performance impact of the pg_stat_statements module. The right pic is the results from pgbench, averages from 5 times runs each,

Loading data into UNLOGGED tables, and considering ALTER TABLE

2011-10-22T23:12:00.004+09:00

UNLOGGED table has been introduced in 9.1, and it would improve INSERT/UPDATE/DELETE performance in several situations. At this time, I'm curious how it would work well on data loading. To load large amount of rows into PostgreSQL, WAL logging would be a performance penalty, especially under the situation that have several tables on different spindles, because WAL logging forces data loading

Index-only scans and heap block reads

2011-10-21T02:43:00.002+09:00

As you may know, the Index-only scans feature has been committed to the PostgreSQL repository and it's available to all the developers. I'm very curious about such performance feature, and always want to understand what would change the things better and how it works. So, I just tried the index-only scans to determine what would be changed. The right figure shows PostgreSQL index scans and

pgbench on UNLOGGED table(s), Round 2

2011-10-07T22:03:00.001+09:00

As one pointed out in a comment in the last post, I have tried additional pgbench runs for "synchronous_commit = off" and "fsync = off". The results are shown in the right pic. It shows that "sync_commit = off" and "fsync = off" are almost the same, and these are faster as much as "UNLOGGED", except a tiny behind. I think the tiny behind means the difference between "sync_commit = off (or

pgbench on UNLOGGED table(s)

2011-10-04T19:46:00.005+09:00

To satisfy my curiosity, I just tried a quick run for pgbench with UNLOGGED tables. Under the pgbench transaction model, a kind of tpc-b, changing the history table attribute to "UNLOGGED" did not contribute to the performance. However, changing all tables to "UNLOGGED" improved the performance *substantially*. :) The results shown in the right image are averages of running pgbench 10 times

xlogdump 0.5.0 released

2011-09-26T23:21:00.002+09:00

Hi, pg folks, xlogdump is a tool for extracting data from WAL segment files. I'm pleased to announce the latest version of xlogdump, 0.5.0, after a few weeks from the previous release 0.4.0. I think xlogdump would be helpful to understand PostgreSQL behaviors, and you can enjoy brand-new features in this version, so please try it if you're interested in it. In this version 0.5.0, xlogdump is

xlogdump 0.4.0

2011-09-04T13:13:00.002+09:00

I'm pleased to announce the latest release of xlogdump. xlogdump is a tool for extracting data from WAL segment files. Here is xlogdump README: https://github.com/snaga/xlogdump/blob/master/README.xlogdump xlogdump was originally developed by Tom Lane and Diogo Biazus around five years ago, but not be maintained these few years. So, I picked it up and have fixed some problems and enhanced. It

PostgreSQL Query Cache - "pqc"

2011-03-01T12:02:00.001+09:00

I would like to introduce a new open source software, PostgreSQL Query Cache, which enables to improve query performance extremely (10x~100x) by caching query results in front of backends. PostgreSQL Query Cache: waits connections on the different port from the clients. delegates queries in front of the backends, like a proxy. intercepts and caches SELECT query results. also manages lifecycle of

5 steps to implement a PostgreSQL replication system

2010-05-15T15:52:00.021+09:00

Yesterday, I tried to build a PostgreSQL master-slave replication system with new feature "Streaming Replication (SR)", which is coming in the next PostgreSQL major release 9.0 as a built-in replication solution, and it worked well on my servers. I feel it is very easy to configure, so I have decided to make some memos here for building a PostgreSQL master-slave replication system. Here are 5

PostgreSQL 9 TestFest Japan on Apr.4

2010-03-28T17:26:00.012+09:00

Some Japanese PostgreSQL developers are inspired by the SFPUG’s TestFest announce, and we are now planning to have our TestFest at 3am-10am Apr.4 in Tokyo/Japan, which is equivalent to 11am-6pm Apr.3 PST in SanFrancisco. PostgreSQL 9 is going to be a very exciting release, and we hope to try many new things around PostgreSQL 9. So we have decided to have our TestFest as an all-night event,

PostgreSQL Cluster: “Too Many Projects” problem

2009-12-28T04:05:00.016+09:00

“Too Many Projects” problem The “too many projects” problem of PostgreSQL Cluster was raised and discussed in the PostgreSQL Clustering Developer Meeting at the JPUG Conference. Nowadays, the PostgreSQL developer community has many clustering/replication projects, but many of them are not production ready. Though, new project is starting, and old project is still unmature. I think this is one

Back to the OpenSource world

2009-12-05T04:05:00.008+09:00

Finally, I’m back to the OpenSource world. In 2004, I started working as a PostgreSQL developer and database engineer. While I was working as a PostgreSQL engineer, I was joining several PostgreSQL cluster projects, including Slony-II. Unfortunately, some were not happen, but it gave me great experiences. At the same time, I was working as a director of Japan PostgreSQL Users Group (JPUG), and

New Explain Format

2006-04-10T10:34:00.000+09:00

Bruce has just added a new todo item. - Allow EXPLAIN output to be more easily processed by scriptsI think the well-formed xml is a better format to express the tree structure of the explain output. I don't know xml can be processed easily by *scripts*, but it is easy for the utility programs using xml processing libraries. And xml elements can have many attributes which can be used to hold rows,

PL/Java 1.2.0 compilation error on PG8.0.0 or 8.0.1

2006-04-10T09:56:00.000+09:00

According to the PL/Java release page, PL/Java 1.2.0 still has full support for 8.0.x.However, PL/Java 1.2.0 can't be compiled with 8.0.0 or 8.0.1, because a number of arguments of DefineCustomIntVariable() has been changed between 8.0.1 and 8.0.2. So I got a following error on 8.0.1. PL/Java 1.2.0 can be compiled only on 8.0.2 or later. Should it be fixed in future release?gcc -c -Wno-long-long

PCTFREE on Pg

2005-08-21T09:17:00.000+09:00

I've done a quick hack for a space reservation in a page to use same page on tuple updating. It's similar to Oracle's PCTFREE stuff. heap_update() can use a (reserved) free space on the same page, but heap_insert() can't.I allocated 1024 bytes for reserve space in each page, then it shows an improvement on the pgbench score 10% or more on my box.-------------- normal -------------------starting

CRC64 and MMX

2005-08-14T23:44:00.000+09:00

I've done experimental implementation of CRC64 routines using MMX 64 bit registers.It makes COMP_CRC64() 10~30% faster than the marco written in C on my PentiumIII laptop.But my Pentium4 box shows the MMX code is slower 2~3 times than plain C code.What's happened in the processor?#define COMP_CRC64_MMX(crc, data, len) do { uint64 __crc0 = (crc).crc0; unsigned char *__data = (

Documents for OSS RDBMS evaluation

2005-05-29T19:21:00.000+09:00

Interesting documents have been published from the Northeast Asia OSS Promotion Forum.http://www.ipa.go.jp/software/open/forum/NEAforum.htmlI hope these documents can be useful for the OSS RDBMS guys.Interesting for you?

'IN PROGRESS' transactions at the end of recoverying.

2005-05-02T15:22:00.000+09:00

If there are 'IN PROGRESS' transactions at the end of recoverying, what can the recoverying node do? If the 'IN PROGRESS' transaction is found, the recoverying RM can't determine it's going to be committed or aborted. The RM can't know even it is the end of transaction or not (it's still continuing).So the recoverying RM must ask another node what to do for 'IN PROGRESS' transactions.When asking,

Transaction isolation on recovery

2005-05-02T14:26:00.000+09:00

Recovery can be realized only replaying WS log sequencially? I'm not sure that.If there is a WS log sequence as below.(1) XID=101, INSERT INTO t1 VALUES ( 1, 'Name 1' );(2) XID=102, INSERT INTO t1 VALUES ( 2, 'Name 2' );(3) XID=102, ABORT;(4) XID=101, COMMIT;(2) and (3) must be ignored when the RM recover. But if the recovery session replays this sequence in a single transaction,(1) XID=501,

Some thoughts on recovery

2005-05-01T15:32:00.000+09:00

1.) Recovery ScenariosI think there are two types of the recovery scenario.- Adding a new node to the cluster.- Adding a crashed node to the cluster.The difference is, a crashed node is still having old snapshot when it crashed, so we can recovery with only WS log replaying.However, if the older WS log has gone by reusing log files, and no one has enough older WS data required for recovering a