Playing with PostgreSQL and pgpool

LPI-Japan to start PostgreSQL certfication

2011-06-14T18:52:00.000-07:00

LPI-Japan, a non-profit distributor of LPIC(Linux Professional Institute Certification) in Japan will start "OSS-DB" exam from July 1st, 2011. LPI-Japan is known as one of the largest distributor of LPIC in the world(according to LPI-Japan they have distributed 164k LPIC so far).

http://www.oss-db.jp/news/press/20110608_04.shtml

According to LPI-Japan, OSS-DB will be ready for several open source databases in the future. However the initial version will only support PostgreSQL(!)

Representatives from Fujitsu, Hitach, Miracle Linux, NEC, NEC Soft., NTT, and SRA OSS attended the press announcement event.

I hope OSS-DB will significantly contribute to making PostgreSQL more popular in Japan.

The 2011 off the Pacific coast of Tohoku Earthquake

2011-03-14T17:46:00.000-07:00

Thanks to everyone who sent me emails regarding the 2011 off the Pacific coast of Tohoku Earthquake in Japan.

Fortunately I, my family and employees are all fine. My home town is in Tohoku area and my mother, brother and sister, who are living there, are still sufferings from lack of water, gasoline and gas etc. even though they did not have the tsunami.

Needless to say about those who had the tsunami. According to the goverment it is likely more than 10,000 people died. I would like to pray for the repose of their soul.

We also worry about the Fukushima nuclear powerplant which has serious problems caused by the tsunami and the earthquake. This also heavily affects the electricity in other area including Tokyo. Due to lack of electiricity we are forced to have a power cut. Trains/metros in Tokyo are revolving only 50% comparing before.

The most important thing we could do here is keeping on our businesses to make money and resources to help Tohoku and Japan. I'm sure we can rehabilitate Japan.

Again I would like to thank to everyone who care about us.

Visiting Brussels

2011-03-05T15:07:00.000-08:00

After visiting Paris, I moved to Brussels to join FOSDEM, the largest open source conference in Europe.

I gave a 45-minute talk abut PostgreSQL and pgpool-II.

After finishing the talk we enjoyed a small tour of the beautifull city.

This is the famous "Grand Place".

There are nice Art Nouveau caffes in the city.

I bought some laces in a old lace shop. They are hand-made and very nice.

There is a great chuch which has a big pipe organ. I was so impressed and bought a CD, recorded the acuall play of the organ!

Visiting Paris

2011-02-13T04:10:00.000-08:00

On February 3rd 2011, I gave my first ta lk in Paris.
The conference was called "PostgreSQL session", sponsored and organized by a French PostgreSQL company Dalibo. Dalibo started to give a series of PostgreSQL seminar in Paris and the conference I joined was the first one.

Damien Clochard of Dalibo was the organizer and he did an excellent job. Especially the room used for the conference was pretty impressive for me. At a first it looked very old, but actually not, according to Jean-Paul Argudo of Dalibo. The room was designed by an artist so that it looked very old.

The first picture is David Fetter, talking about dblink.

The second picture is Philippe Beaudoin of Bull. He talked about an interesting migration case in French government.

The lunch was served as described in the menu left. The meals were so delicious, I forgot to take pictures of them:-)

I would like to thank to all the people who organized and joined this great conference!

After finishing the session, I enjoyed a small sightseeing of course.

In summary, the conference was great, meal was great and my trip was great. I hope to visit Paris again!

Playing with PHP PDO

2010-12-27T20:04:00.000-08:00

PHP PDO(PHP Data Object) allows you to access database including PostgreSQL within PHP scripts. Good thing with PDO is, you do not need to heavily modify your PHP script even if you change the database product you want to use. This resembles to JDBC driver or Perl DBI. Moreover you could use database product dependent interface. Which means you can enjoy the full power of PostgreSQL with sacrificing portability of course. There's no free lunch. In summary, PHP PDO is great and I would like to recommend to anyone who is thinking about serious database programming in PHP.

One day I came across a complaint from pgpool-II user. According to him, his PHP client was blocked by pgpool-II while trying to connect to it. Ok, this is a typical situation when user misconfigured num_init_children and I thought he tried to connect pgpool-II concurrently with more than num_initi_children sessions. But his answer is, no. I suspected the PDO internal but I was too lazy to look into PDO source code. Instead I use strace to see what PDO is doing. To make the long story short, PDO releases the connection to pgpool-II long after "$dbh = null;"($dbh is the "handle" of the connection. This should release the connection). So if his script continues and connects to pgoool-II again after he thinks that he releases the connection, the script actually has two concurrent connections to pgpool-II. Of course this will create more connections than he thought.

I think the reason why many people do not come up with similar complaint is, their script usually create just one connection and release it at the end of the script.

Tutorial to pgpool-II plus Streaming replication

2010-11-04T01:47:00.000-07:00

I have written a tutorial to pgpool-II using PostgreSQL 9.0's streaming replication. The tutorial is designed to use minimum resource: a Linux box. On the box, pgpool-II, pgpoolAdmin(a PHP based pgpool-II GUI admin tool) and two PostgreSQL 9.0 instances.

Though the tutorial system is surprisingly simple, you could learn:

- How pgpool-II detects PostgreSQL failure and automatically promote a standby server to primary server

- How pgpool-II re-sync the failed node by using "online recovery" capability

Please enjoy!

pgpool-II 3.0 officially released

2010-09-12T02:23:00.000-07:00

After nearly one month testing, we finally sent out pgpool-II 3.0 to the door on September 10, 2010. On that day SRA OSS, Inc. celebrated its 5th Anniversary. We had several talks there including Masao Fujii, the author of Streaming replication of PostgreSQL 9.0. Another talk was given by Toshihiro Kitagwa, the major developer of pgpool-II, explaining how Streaming replication/Hot standby can be work with pgpool-II.
About one hundred people joined the session. I was very happy to report "pgpool-II is out today" to them.

The details of pgpool-II 3.0 can be found at: http://lists.pgfoundry.org/pipermail/pgpool-general/2010-September/002946.html

From now on I would like to explain new features of pgpool-II 3.0, probably one feature per one blog entry.

Today's topic is the "white function list" and "black function list".

Background: pgpool-II needs to know the usage of functions in SELECT statements which will write to database. If such SELECT is given, pgpool-II should not do load balance. Instead it sends the SELECT to al DB nodes(in replication mode) or sends to the master node (in master/slave mode).

Typical queries are:

SELECT nextval('foo_sequence');
SELECT setval('foo_sequence', 100);

In previous version pgpool-II found nextval and setval in hard coded way. Problem is, user might want to create their own functions which does write. The work around was to use SQL comment. For example:

/*NO LOAD BALANCE*/ SELECT my_writing_function();

This is not only unpolished but forcing users to rewrite SQL statements in existing applications. In pgpool-II 3.0, you can use black function list in such cases:

black_function_list = 'nextval,setval,my_writing_function'

White_function_list works in exactly opposite way. Any functions not listed in the white_function_list are regarded as being write to database.

Pgpool-II 3.0 also enhances the way to search functions in SELECT. Before it only searchs in the top level of SELECT. That is, if those functions are used in subquery or some such, it cannot detect the existence of the functions. Pgpool-II 3.0 does a recursive search against he parse tree and can find those functions anywhere.

Pgpool-II 3.0/pgpoolAdmin 3.0 beta1 released

2010-08-17T19:53:00.000-07:00

After 3 months of hard work, pgpool-II 3.0/pgpoolAdmin 3.0 beta1 is finally out.
(details)

Please join the beta test program. Comments and suggestions are welcome!

Many people helped us in making this reality. Especially I would like to thank to:

Guillaume Lelarge: enhanced pcp commands, lots of patches, French translation and more...
Masao Fujii: gave us valuable info about streaming replication of PostgreSQL 9.0
Simon Riggs: gave us valuable info about Hot standby of PostgreSQL 9.0
Toshihiro Kitagawa: did hard work on enhancing extended protocol processing
Nozomi Anzai: updated/enhanced internal of pgpoolAdmin

PostgreSQL 9.0 and pgpool-II endurance test

2010-08-15T03:57:00.000-07:00

I have started to run an endurance test of PostgreSQL 9.0beta4 and pgpool-II 3.0-dev on Aug 12 17:47.
The hardware: two dual-core opteron 2.2GHz, 2GB mem.
One is used for the streaming replication primary server and the other is used for the standby server.
The load is provided by pgbench -N -c 10 (10 concurent users) -C (each time the transaction starts, pgbench connects to pgpool), which runs on the primary server.

Up to now the test has lasted for about 88 hours. No errors, no OOM killer run because of memory leak. Totally 291 million transactions have been processed.

Here is some TPS data. "excluding" means excluding connect/disconnect time. "including" means including connect/disconnect time. So it's not supprising that "excluding" is far better than "including". This is the reson why you want connection pooling.

select avg(excluding) as excluding_avg,
stddev(excluding) as excluding_stddev,
avg(including) as including_avg,
stddev(including) as including_stddev
from endurance_test;


-[ RECORD 1 ]----+-----------------
excluding_avg    | 1290.55333121045
excluding_stddev | 18.6107550266789
including_avg    | 916.442996303828
including_stddev | 10.3926488799762

pgpool-II and Hot Standby

2010-06-15T02:54:00.000-07:00

PostgreSQL 9.0 will have a great feature called "Hot Standby". Unlike warm standby, which is just waiting for in case of master server goes down, it gives an access to the standby server for lots of useful purposes such as complex reporting query. However only read queries and other several commands are allowed to be sent to the standby server. According to the PostgreSQL 9.0 beta2 document, this is the list of allowed queries:

Query access - SELECT, COPY TO including views and SELECT rules
Cursor commands - DECLARE, FETCH, CLOSE
Parameters - SHOW, SET, RESET
Transaction management commands
BEGIN, END, ABORT, START TRANSACTION
SAVEPOINT, RELEASE, ROLLBACK TO SAVEPOINT
EXCEPTION blocks and other internal subtransactions
LOCK TABLE, though only when explicitly in one of these modes: ACCESS SHARE, ROW SHARE or ROW EXCLUSIVE.
Plans and resources - PREPARE, EXECUTE, DEALLOCATE, DISCARD
Plugins and extensions - LOAD

No problem for PostgreSQL experts of course. But I doubt it is super easy for ordinary PostgreSQL users to know which query is allowed and which is not just looking at the list.

pgpool-II 3.0 (yes, that will be the new version number) will give you small help. Pgpool parses the query and automatically redirect it to the standby server if needed. If you have more than one standby servers, pgpool will choose one of them and send the query to it. The queries not allowed for the standby server, will be sent to the primary server. In short, you do not need to worry about the complex rule above if you use pgpool-II 3.0 + Hot Standby.

Please note that there is a limitation here. Pgpool parses the query by using "raw parser" which understands the syntax of the query but does not understand the "depth psychology" aspect of the query. For example, if a SELECT calls a custom C function which modifies the database, pgpool will send it to the standby server because it does not know that the SELECT actually does write.

It will be great that PostgeSQL's function does have a signature that confess it is going to write...

Talk with the author of Streaming Replication

2010-06-09T20:41:00.000-07:00

Yesterday I talked with Masao Fujii, the author of Streaming Replication. Streaming Replication(SR) is a long awaited feature, "built in replication" of PostgreSQL. The great feature will come with PostgreSQL 9.0, which is supposed to be released this Summer. With SR, pgpool-II will provide two important features: load balancer and failover control. Since SR is a master/slave type replication system, the standby server (slave) do not accept write queries such as INSERT, DELETE, UPDATE, CREATE TABLE and so on. Any write query sent to pgpool will be redirected to the primary server (master) and users do not need to worry about it. On the other hand, as SELECT can be executed both on the primary and the standby, pgpool will choose one of them and send the SELECT to it. This technique is known as "load balancing" and this will significantly improve the over all performance.

One thing we were worried about was, what if the connection between the primary server and the standby server goes down? There's no easy way for pgpool to know it. If pgpool does not know that, SELECT can be sent to the standby whose data is far behind the primary. Apparently this is what users want to avoid. To overcome the problem, pgpool checks the transaction log number (LSN) on both the primary and the standby. If standby's LSN is too small comparing with the one on the primary, we might have a problem. So pgpool stops the load balancing and send SELECT only to the primary. How behind is "behind" should be defined by new directive in pgpool.conf. Good thing with this is, the technique will also detects the delay because of the heavy load of the standby.

We will continue to discuss how to improve the usability of SR and pgpool-II.

A mail client using PostgreSQL

2010-06-07T03:01:00.000-07:00

Do you know sylpheed? It's an open source mail client and has been
used by many people, who are using more than 30 different languages,
all over the world for ten years now.

Sylpheed was born in Japan and has been developed by Mr. Yamamoto, who
has been working for our company since 2006. Recently he has made a
commercial version of Sylpheed called "Sylpheed Pro".

The most interesting feature with Sylpheed Pro is, it comes with
PostgreSQL's nice full text search engine. If you install Sylpheed
Pro, you have PostgreSQL running on your computer. Any mail received
by Sylpheed Pro is automatically stored into PostgreSQL database so
that it can be used later to execute full text search on your
mails. How fast is it? Usually you can find the mail what you are
looking for within 1 second out of 10,000 mails. Sylpheed Pro even
can take care of attached documents including PDF, Word, Excel,
OpenOffice files. It extracts the text part from those files.

At present, Sylpheed Pro is sold in Japan only. However you could
download a trial version of Sylpheed Pro and test it since Sylpheed
Pro is a multi-lingual software. So if you do not care that the
download page is written in Japanese, please try it from here.

Hint:

There are six text boxes you need to fill in.

[Last name] [Fist name]
[Last name] [Fist name](please repeat)
[email address]
[company name]
[you can leave it blank]

then click *left* button (right button is "clear"!)

Refactoring pgpool-II

2010-06-04T16:59:00.000-07:00

Pgpool was born in 2003. When it was reborn as pgpool-II in 2007, we had made a big change of its architecture. For example, we gave a capability to pgpool to use more than 2 DB nodes.

In 2010, we are refactoring pgpool-II again: disentangling the spaghetti code(there are tones of global variables, which is a bad sign), moving some codes from a file to more appropriate one and so on. I believe the work will make pgpool-II more solid, easier to understand by new developers.

Of course we are not just refactoring pgpool-II. We will add nice features including adaption for Streaming replication/Hot Standby, which is one of the greatest features of upcoming PostgreSQL 9.0.

We hope the work will be finished by August.

Stay tuned.