Taa Deem ! A few words about PostgreSQL and other things - Mot-clé - planet-postgresql

pgBadger 3 : How to use the parallel mode

damien — Tue, 12 Mar 2013 22:49:00 +0000

Just like I said in a previous blog post : the pgBadger projet is moving fast ! Version 3 was released a few days ago with a major improvements : With the new parallel mode, you can now launch mutiple pgBagder process at once !

In others word, pgBadger is not bound to one cpu anymore. If you have multiple cores, you can now uses them to give more power and decrease the processing time. All you have to do is add the "-j N" option in the command line, where N is the number of cores you want to use. For instance :

$ pgbadger -j 4 /var/log/postgresql/postgresql-2013-02-*

Quick benchmark : We had 5 log files with a total volume of 9.5 GB. Here's the results

-j 1 : 1h41m18s
-j 2 : 50m25s
-j 4 : 25m39s
-j 8 : 15m58s

However it's important to note that the parallel mode has a little drawback. With this method, some queries may be truncated. If you enable N cores, then result may differ in a maximum of N queries per log file.

However, this is a minor issue: parallel mode is interesting if you have millions of queries to analyze. And if you have millions of queries in a log file, you can afford to loose a few as it's quite unlikely that the lost queries would have changed the overall results.

However, to avoid this problem, you can use the pgBadger "per-file parallel mode" to analyze your logs but with lower performance than the standard parallel mode. To enable this behaviour, you have to use the "-J N" option instead of "-j N". In per-file mode, the performances start being really interesting when there's hundreds of small log files (e.g. 10MB rotation size limit) and with at least 8 cores.

We believe this new improvement will be a significant change for pgBadger users. If you have used the parallel mode, please send us feed back on our mailing list or via the pgBadger twitter account !

And by the way, this is not the only big feature in pgBadger v3 ! For the complete list of changes, please checkout the release note on github

And of course stay tuned.... because version 4 is coming fast :-)

pgBadger HTML5 overhaul : preview

damien — Thu, 03 Jan 2013 10:18:00 +0000

pgBadger is a powerful tool to optimize your PostgreSQL servers. Using your server log files, pgBadger will analyze the SQL traffic and produce a profusion of statistics (longest queries, read/write ratio, etc.). This is very helpful but the amount of data generated is so big that the pgbadger reports are hard to read and useful stats are often drown in the flood... Check out this sample pgBadger report to see the mess !

Since my previous blog post, we've been working hard to redefine the pgBadger user experience and create a new report template.

Here's the first draft of this new prototype (For now the draft is just static HTML code, we hope we'll have a live demo in the forthcoming weeks).

As you can we followed a few basic principles :

Split the big HTML tables in smaller tables and use tab navigation to switch :

Leave some blank spaces to make the report easier to read :

Hide big tables behind graphs, when possible :

Put "full details" in popup windows that can you can hide/show when you need it :

Emphasize "key values" using a bigger font size :

As you can see, there's nothing either out of the ordinary or very original here. But this new design will surely be easier to read and be more insightful. We hope it will attract new users, while being more helpful for the existing user base.

If you have any comment or ideas, please join the pgBadger mailing list and get involved !

pgBadger is moving fast

damien — Wed, 14 Nov 2012 14:44:00 +0000

When the pgBadger project started 6 month ago, our primary goal was to build a better performance tool for PostgreSQL. We also wanted it to be an open and dynamic project, built with the help of the Postgres community. So far the result is beyond our expectations : we've received dozens of user requests, bug reports and contributions.

Thanks to this community support, the project goes at a very fast pace ! Today Gilles Darold released pgBadger 2.2. This new version comes with a bunch of exciting new features:

Tsung support : This means you can now create benchmark scenarios based on the queries contained in your PostgreSQL logs
New parameters : For instance with --exclude-user you can now ignore a user in the generated reports. With --select-only you generate a report about the read queries...
Faster CSV log parsing (thanks to David Fetter)
Improved log format detection
And many bugfixes....

And there's more come ! We're currently working on two major enhancements :

Multi-threading is on it's way : The last limitations have been removed and there's now a clear path to implement parallel parsing of the log files. pgBadger is already quite fast, but this will make it even faster. Especially on servers with many CPUs.

HTML5 Overhaul : For now pgBadger reports look pretty much like the ones produces by pgFouine : i.e. it's just basic HTML aimed for a desktop screen. This was fine in 2005, but now we need a responsive design ! Reports must be more interactive and readable on any devices. This is a tough job and as DBAs we have limited design skills :-) So we decided to hire a professional web design company to help us with : the French company art is code will work with us to build a smarter report.

Of course, we will involve the community while working on these two major improvements. If you want to participate and just keep in touch with the project, please join the pgBadger mailing list. In a few week, we will submit the first draft of the new report design.

In a nutshell : If you don't know what pgBadger is, give it a try ! If you already use it, please upgrade to version 2.2 ! And if you've already installed v2.2, stay tuned because version 3 is coming fast !

By the way, we're not alone... The whole postgres community seems to be buzzing with brand new performance tools ! Over the past few weeks, at least 3 interesting projects popped up :

datascope is a nice tool to visualize pg_stat_statements data
pg_stat_plans allows you to lists SQL statements by execution plan, not just by query.
pgOtter is yet another small mammal trying to parse your PostgreSQL logs ;-)

At a personal level, I'm quite happy to see this happening and I bet this is just the beginning of a great move of the community toward better user-friendly tools for the PostgreSQL user base.

PostgreSQL Session #4 : From Oracle to PostgreSQL

damien — Fri, 12 Oct 2012 10:06:00 +0000

As a nice coincidence the 4th PostgreSQL Session held Thursday, October 4 which was also the last day of Oracle Open World conference. Suffice to say that the two events didn't share the same definition of the word "Open"!

As for the previous sessions organized by Dalibo, the room was full to listen to the various speakers.

The morning speeches were devoted to migration tools themselves with three main methods: ora2pg, ETL or Foreign Data Wrapper:

Jean-Paul Argudo opened the conference with a keynote to remind thechallenges of RDMS migrations.
Guillaume Lelarge began with a brief presentation of the new features of PostgreSQL 9.2
Gilles Darold continued with a zoom on ora2pg , the Oracle to PostgreSQL migration tool
Marc Cousin described how to use Kettle (an open source ETL)
Laurenz Albe, from Vienna, Austria, presented oracle_fdw , a data wrapper that can read Oracle tables from PostgreSQL

The afternoon was an overview of the alternatives to traditional Oracle tools, including

Gabriele Bartolini, founder of 2ndQuadrant Italy, presented Barman , a backup tool that follows RMAN's footsteps
Jehan-Guillaume De Rorthais described the new features of pgBadger , a fast PostgreSQL log analyzer
Finally Julien Rouhaud concluded with a presentation of PGXC , a solution for symmetric replication ("Master-Master") which is a serious alternative for Oracle RAC.

All presentations are available in PDF format here. Most the slides are in French, except Jean-Paul, Gabriele and Laurenz talks: http://www.postgresql-sessions.org/en/4/

For my part, I presented the following conclusions during the closing keynote:

1 - There are multiple ways to migrate from Oracle to PostgreSQL: according to your needs and the complexity of your database schema, you can use different tools. ora2pg is specialized and easy to implement. Kettle and other ETL are very powerful and useful if you need to transform the data. And finally oracle_fdw is an original solution to migrate smoothly.

2 - There's still some missing features in the PostgreSQL ecosystem: including comprehensive tools for partitioning or free alternatives to OEM. This leads many observers to judge that «Oracle is ahead of PostgreSQL». For my part, rather than looking at the position of each RDBMS at a given moment, I prefer to look at the innovation rate and the dynamic of each project ... And this 1-day conference showed us that the PostgreSQL community is currently in an extremely fertile and creative period. This extraordinary growth is the assurance that PostgreSQL will be a dominant RDBMS in the years to come. In other words: the PostgreSQL project is perhaps behind Oracle right now, but given its current speed it will quickly reach and surpass the competition.

3 - As stated by Jean-Paul during the opening keynote, a DBMS migration is 10% about tools and 90% about human. The good news is that open source is all about putting users above technology. Indeed, when moving from a proprietary database to PostgreSQL, the total cost of ownership (TCO) changes its nature: you no longer pay expensive licenses to a monopolistic giant. Instead your budget is redirected to "real services" (support, training, consulting) provided by local companies that are close to you. Switching to free software is not only a technical change, it is also (and above all) a revolution in the business relationships.

Finally, I would like to warmly thank all those who made this conference possible especially Laurentz Albe and Gabriele Bartolini.

The next PostgreSQL session will take place in early 2013. We are currently considering the theme of the day. Do not hesitate to submit your ideas and suggestions at contact@postgresql-sessions.org . And Follow us on twitter !

PostgreSQL Magazine needs you !

damien — Sun, 18 Mar 2012 23:03:00 +0000

The first official issue of PostgreSQL Magazine is on its way !

If you want to help us, now is the time ! We need reviewers. Proof-reading takes only a few minutes and it's a great way of contributing. It's an important job that gives the magazine its quality.

The review process based on a quite simple wiki. Each article is a wiki page. As a reviewer you just have to click "edit" and correct as many errors you can spot :-)

There are currently 4 articles waiting for review and few more to come by the end of the month.

If you're interested, please read the reviewer guidelines and subscribe to the pgmag mailing list.

We plan to release PostgreSQL Magazine #01 in April. And second issue is already scheduled for october 2012. Stay tuned !

For more details, please check : http://pgmag.org/

Write a Foreign Data Wrapper in 15 minutes (Part 1/2)

damien — Tue, 31 Jan 2012 22:49:00 +0000

Among the long list of new features of PostgreSQL 9.1 the new SQL/MED implementation is probably of the most underrated. SQL/MED is an extension of the SQL standard defines foreign data wrappers (FDW) that allow you to reach data located outside of your database, with regular SQL request (MED stands for Management of External Data). In a nutshell, SQL/MED is cool.

As far as i know, PostgreSQL and DB2 are the only major RDBMS providing an implementation of this standard. The beauty with PostgreSQL is that you can write your own data wrappers to connect your database to any storage you want ; other RDBMS, NoSQL storages, web services, whatever system that holds data and can deliver it.

Basically to Write a Foreign Data Wrapper you need to code six callback routines in C to explain how to plan and execute queries toward your new source of data.

PostgreSQL 9.1 has been released a few month ago, but there's already a bunch of FDW available (even though most of them are still beta). At the time, I'm writing this we already have http://wiki.postgresql.org/wiki/For..., so you can connect your PostgreSQL server to ; Oracle, MySQL, SQLlite, anything providing an ODBC driver, LDAP, couchdb, redis, Amazon S3 storage, CSV files, Twitter, Google Search, HTML pages, RSS feeds, ....

For an almost complete list, check out the PostgreSQL wiki ; http://wiki.postgresql.org/wiki/For...

And of course these wrappers are packaged as extension and most of them are available on PGXN and quite easy to install : http://pgxn.org/tag/fdw/

But there's more ! Among these FDW there's one called Multicorn. This particular extension allows you to write FDW in Python. A wrapper over another wrapper. Basically this means you don't have to know C to write your own FDW and you can use high-level libraries from the Python world.

Ok let's take a simple example. I'd like to query data contained in a web calendar like Google Calendar. Those data are available in ical files, with a specific format called iCalendar. So want i need to do is explain PostgreSQL how to reach and parse the files.

With Python and Multicorn, this is done with 20 lines of code : see icalfdw.py

And that's it.

Of course this is a basic implementation. We can do better with some optimizations and a few log handling. It's also important to keep in mind that foreign data wrappers have strong limitations... I'll talk about all that and other things in my next blog post.

In the meantime, if you're in Brussels this week-end for FOSDEM you can come to the PostgreSQL Devroom, I'll make a presentation of Multicorn along with its creator.

PG Conf Europe : Don't miss the party tonight !

damien — Wed, 19 Oct 2011 08:32:00 +0000

PostgreSQL Conference Europe 2011 is now entering in its second day. If you're one of the lucky attendees, in addition to the tremendous schedule of Day2 you are also invited to the traditional social event sponsored by Dalibo and OpenSCG. The party will start at 6:30 PM inside the Hotel Casa 400.

And thanks to Heroku, another party is planned for tomorrow ! This time, the event will happen in a bar close to the hotel.

More details on the conference website : http://2011.pgconf.eu/party/

Enjoy !

PostgreSQL Magazine #00 is out

damien — Fri, 20 May 2011 19:11:00 +0000

I'm very pleased to announce the release of the demo version of the so-called "PostgreSQL Magazine". The issue #00 is available in both online and paper versions. You can either :

Read it Online: http://pgmag.org/00/read
Buy the Print Edition: http://pgmag.org/00/buy
or Download the PDF: http://pgmag.org/00/download

Please consider this first issue as a "beta". Our goal is to get feedback and see if there's a public for such a magazine.

So if you've read the magazine and have some time to write an e-mail, we'd love to know what you think of this first attempt. More precisely, let us know :

If you would buy such a magazine on a regular basis ?
If you would recommend it to your friends or colleagues ?
If you would offer it to your clients as advocacy material ?
If you would write or help the magazine in any way ?
If you would be ok to see advertisement in the magazine ?
If you would like to have more pages in it ?
etc.

Please send your comments, suggestions and critics at contact@pgmag.org. A global recap of all the feedbacks will be published on http://www.pgmag.org in a few days.

Once again i'd like to tell my most sincere thanks to all the people involved in this project !

And to you, future readers of PostgreSQL Magazine : Enjoy !

Predicting Postgres 9.0 final release date...

damien — Wed, 01 Sep 2010 20:10:00 +0000

While the first candidate release of PostgreSQL 9.0 RC1 is just coming out , impatience increases and the question comes again and again : "When can we use version 9.0 in production ? " . Obviously, it is impossible to give a precise answer to this question because the delivery process of PostgreSQL versions is not based on a strict schedule (like Ubuntu for example) but on the quality and quantity of tests ...

So the best answer is: "Please test PostgreSQL 9.0 RC PostgreSQL yourself. Now ! And in case of problems, submit a bug report to developers. It is a simple way to participate to the community effort : the more testers we have, the sooner the stable version will be released !

However, this answer is not always satisfactory for the more impatient. So now, let's try to predict the release date, not with a crystal ball, but by analyzing the statistics of the release dates of the last 5 versions (8.0 to 8.4). I looked for the release dates for every beta and RC versions since August 9, 2004. This gives me the following graph :

We can see striking similarities between the 8.0 and 9.0: for both versions the period between beta1 and RC1 lasts about 118 days (116 for the 8.0, 120 for the 9.0), whereas the versions 8.1, 8.2, 8.3 and 8.4, this period lasts between 60 and 90 days ... Nothing very surprising about all this! Change in the first digit indicates a new generation and the arrival of features that have a strong impact on Postgres usage (eg: a port for windows version 8.0, the Hot Standby / streaming replication for the 9.0) . So it seems pretty logical that we need way more tests and bug fixes when we go from 7 to 8 and from 8 to 9 ....

Now we can use the data we have collected, especially the dates of the 8.0 release to predict the output of PostgreSQL 9.0 final version: It took 47 days between the release of version 8.0.0 and version 8.0RC1 final. If we follow the rhythm correlation with version 8.0, then for Postgres 9.0 the RC period should last 49 days. Then the final release date would be October 18

Of course, this is an abstract and naive approach... The delivery process of PostgreSQL is far too complex to be mathematically modeled in this way and I do not pretend to read the future :)

However this little exercise of statistics shows that the PostgreSQL project has a certain constancy in its progression and in its operation.

Moreover, we may also note that the test periods of PostgreSQL are very short (between 75 and 160 days). For such a big software (500,000 lines of code) this is exceptional! For example, the test period of Oracle 11g lasted over 10 months (September 2006 -> July 2007) ... Again, the explanation is quite logical: the Postgres source code is open and the beta-testers are numerous and very active !

PG Day Europe 2009 : One week later

damien — Tue, 17 Nov 2009 13:01:00 +0000

One full week has passed since the end of PG Day Europe 2009 and yet it's still hard to describe the intensity of these 2 days of meeting. Everything went so fast ! Here's some figures to : 25 talks, 32 speakers, 200 PostgreSQL fanboys and fangirls...

I had the chance to be part of the organization, so here's a bunch of lessons learned during this fantastic experience :

Feeding 200 persons during 2 days is a real challenge !

Against all odds, the French visitors were likely to attend English lectures. About 80% of the audience consisted of French and despite my fears, language was not a barrier.

Participate in organizing such an event is a simple and easy way to contribute to the PostgreSQL project. There's nothing better to understand and help this community.

Whatever you do there will always be someone to take a compromising photo of you.

We still have a long way to go to achieve what the JPUG is doing

Of course, I take this opportunity to thank all the people that made this adventure possible: the staff, sponsors, speakers, ... The list is too long.

I do not know if I can participate in the organization PGDay Europe 2010 but I already cannot wait to be there !

Should PostgreSQL Europe organisation settle down in France ?

damien — Sun, 16 Sep 2007 16:17:00 +0000

hi folks !

Over the last few days, I've met almost every "key members" of the PostgreSQLFr association and asked if they would be OK to transform the association into an European purpose one.

The response is unanimous : they are not comfortable with the idea that the french association would "disappear". It represents for them 3 years of work : Lots of time and energy to build this user group from scratch. Changing the name and goal of the association would somewhat mess things up. Besides the association aims to provides help to **french speaking** users, which includes Canada, West Africa, etc. etc. Obviously this is out of the scope of an European user group.

By the way we still consider that the PostgreSQL Europe is a great project, but we think it would be better to have two separate entities.

Furthermore some of the member of the french "crew" are OK to provide help to create an European non-profit association under the french law. As i said previously it's a very common thing in France and i think this could be done within a few months.

However there are two things that seems important to us. First we'll need help from you :-) Once the association is declared we hope that people from every major country will get into the "administration board" and get involved into the association life. Our main concern is that managing this new organisation alone would only divide by two our time and energy. The second point is that the purpose of this new organisation has to be defined clearly and precisely before creating the association. Speaking with each other french members, we noticed that for the moment this purpose was pretty "blur" to us :-)

Based on these two things (involvement and clear purpose) we would be very happy to create an European association based on the french law.

Before going any further let me present the advantages of the french law association : not only is it very simple to create but it's also very strict about the "non-profit" aspect .Of course this doesn't mean that the association cannot earn money, sell goodies, pay people and such ... but it's clearly defined ( and also checked by the authorities ) that the money earned must be used only for the project. Any kind of commercial activities and any personal earning is firmly prohibited. I guess this is not unique, most of European countries may have such kind of laws . But my point of is that the french version fits perfectly our need.

Here's the text of law ( in English ) for those of us who are familiar with legal vocabulary : http://web.archive.org/web/20040615054216/www.justice.gouv.fr/anglais/europe/aassoc.htm

Now to answer to Andreas question about the next steps, here's my point of view :

1/ We need to make some kind of poll to see if creating PostgreSQL Europe under the french law is OK for every one, or if there are others possibilities.

2/ If the "french association" proposition is accepted then we'll have to write together the legal statutes in English ( i have samples of generic statutes we can adapt to our need ). Once the statutes are written, we can translate them in every languages and propose them for acceptance. A wiki could be a good tool for all that stuff, imho.

3/ Once the statutes are accepted, we'll have to decide what will be the address of the association. I think the best idea is to locate the association in Paris . We'll also have to name 3 persons that will constitute the "temporary Administration Board" of the association and make the official declaration to french authorities. This declaration will take a least one month to be "fully official" as french administration is not a very fast machine.

4/ Once the the declaration is fully done, we can open membership.

5/ In February 2008, we meet at FOSDEM to make the first"General Assembly" and replace the temporary Administration Board with a brand-new democratically elected one.

6/ After that the administration board meetings can be made via teleconference or IRC... However i think that meeting each other "in real life" once in a year is mandatory.

These 6 steps are based on my experience, on PostgreSQLFr members advices and on my interview with Tristan Nitot. Surely there are other ways to do this and some of them might be better than the one i exposed. What i like with this one is that it gets us on track and focused on a deadline : FOSDEM in February. That leaves us 5 months to build this thing up :-)

Any suggestions, critics or addendum to this proposition are more than welcome !

Regards,

-- damien clochard http://dalibo.org | http://dalibo.com