tag:blogger.com,1999:blog-124931812015-08-02T02:27:35.145+09:00A Hacker's DiarySome thoughts around PostgreSQL and OpenSource.Satoshi Nagayasuhttp://www.blogger.com/profile/09706093898823214408noreply@blogger.comBlogger30125tag:blogger.com,1999:blog-12493181.post-27164336572797913752015-05-21T18:23:00.002+09:002015-05-21T18:23:39.952+09:00[Postgres Toolkit] pt-table-usage/pt-index-usage commandsIn this entry, I would like to explain how to use pt-table-usage and pt-index-usage commands in the Postgres Toolkit which I introduced in the previous entry.
If you have never heard about Postgres Toolkit, please visit the previous entry.
A Hacker's Diary: Postgres Toolkit 0.2 released
pt-table-usage and pt-index-usage are the commands which can be used to obtain usages of the tables/indexes Satoshi Nagayasuhttp://www.blogger.com/profile/09706093898823214408noreply@blogger.com2tag:blogger.com,1999:blog-12493181.post-70345590334613986052015-05-12T09:34:00.000+09:002015-05-12T16:22:10.763+09:00Postgres Toolkit 0.2 releasedToday, we pleased to announce the release of Postgres Toolkit 0.2.
http://www.github.com/uptimejp/postgres-toolkit/
https://postgres-toolkit.readthedocs.org/
■What is "Postgres Toolkit"?
Postgres Toolkit is a collection of scripts and utilities which is intended to help PostgreSQL DBA to improve quality and productivity of their daily jobs and operations.
With having Postgres Toolkit, DBASatoshi Nagayasuhttp://www.blogger.com/profile/09706093898823214408noreply@blogger.com11tag:blogger.com,1999:blog-12493181.post-86814078110179104272014-06-14T18:48:00.000+09:002014-06-14T18:48:14.748+09:00Deploying Postgres-XL in 2-minutes with Chef/serverspecAs you may know, Postgres-XL, a MPP implementation of PostgreSQL, was released last month.
Postgres-XL | Open Source Scalable SQL Database Cluster http://www.postgres-xl.org/
Most of recent topics in the PostgreSQL development are related to implementing data warehouse. And Postgres-XL is getting attention.
However, such enhancement which consists of several PostgreSQL servers is a bit Satoshi Nagayasuhttp://www.blogger.com/profile/09706093898823214408noreply@blogger.com0tag:blogger.com,1999:blog-12493181.post-10285418826776090892013-07-14T11:21:00.000+09:002013-07-19T17:31:04.095+09:00monetdb_fdw: PostgreSQL meets column store. Finally.
As you may know, FDW is one of the advantages of PostgreSQL. There are variety of FDWs to federate different data sources (including PostgreSQL itself) with PostgreSQL.
And also, you may know everyone in the industry is talking about BigData and analytics everyday. We know that most of them must be a buzz though. :p
Yesterday, I released a brand-new FDW, monetdb_fdw, which allows you to Satoshi Nagayasuhttp://www.blogger.com/profile/09706093898823214408noreply@blogger.com3tag:blogger.com,1999:blog-12493181.post-20720740365222501412013-02-19T12:04:00.001+09:002013-02-19T13:20:08.760+09:00PostgreSQL Unconference in Tokyo
Last Saturday, we, Japan PostgreSQL Users Group, had the first PostgreSQL "Unconference" in Tokyo.
The event had about 40 participants and 17 talks in 4 hours, and it was successfully run by the participants.
As you may know, "conference" requires its host to arrange the program *much before* the event starts. As opposed to that, "unconference" doesn't.
"Unconference" is filled with many Satoshi Nagayasuhttp://www.blogger.com/profile/09706093898823214408noreply@blogger.com0tag:blogger.com,1999:blog-12493181.post-81240189322060858712013-01-01T17:10:00.000+09:002013-01-01T17:12:15.606+09:00Announcing pg_part extension, a table partitioning utility for PostgreSQLI'm pleased to announce my first PostgreSQL extension, pg_part extension, to take advantage of table (range) partitioning on PostgreSQL.
uptimejp/pg_part
https://github.com/uptimejp/pg_part
In the PostgreSQL world, the table (range) partitioning (a.k.a. Constraint Exclusion) is one of the advantages of PostgreSQL for analytical workloads, but using lots of DDL commands for this purpose still Satoshi Nagayasuhttp://www.blogger.com/profile/09706093898823214408noreply@blogger.com0tag:blogger.com,1999:blog-12493181.post-74728133308869669962012-06-01T14:21:00.001+09:002012-06-01T14:41:42.432+09:00pgstatview: Visualize your PostgreSQL in a minuteAs you all may know, understanding activities inside database management system is one of the important, but difficult and painful tasks for DBAs, not only for newbies, but also experienced DBAs. I saw many DBAs struggling with PostgreSQL because of such difficulties.
Today, I'd like to introduce a brand-new tool, pgstatview.
pgstatview (pre-alpha) http://www2.uptimeforce.com/pgstatview/
Satoshi Nagayasuhttp://www.blogger.com/profile/09706093898823214408noreply@blogger.com2tag:blogger.com,1999:blog-12493181.post-92078831126133296632012-03-06T00:51:00.012+09:002012-03-06T13:23:04.183+09:00Japan PostgreSQL Users Group received Achievement Award 2011 from DBSJToday, Japan PostgreSQL Users Group (JPUG) has been awarded Achievement Award 2011 from the Database Society of Japan (DBSJ), the largest (umbrella) database research community in Japan, because of its activities on database research, educating engineers and promoting database technology in Japan.
This is actually impressive thing in the JPUG history, and in my mind. I really appreciate many Satoshi Nagayasuhttp://www.blogger.com/profile/09706093898823214408noreply@blogger.com0tag:blogger.com,1999:blog-12493181.post-74804728437475030622012-01-30T09:47:00.014+09:002012-02-03T14:51:02.323+09:00PostgreSQL Conference 2012 on February 24 in JapanJapan PostgreSQL Users Group will be having an annual 1day technical conference, PostgreSQL Conference 2012, on February 24 in Tokyo. There will be two keynote sessions and 13 sessions on PostgreSQL.
(The photo was taken by @koyhoge at PostgreSQL Conference 2011)
PostgreSQL Conference 2012 - NPO法人 日本PostgreSQLユーザ http://www.postgresql.jp/events/pgcon2012/ (Google translated)
The opening keynote Satoshi Nagayasuhttp://www.blogger.com/profile/09706093898823214408noreply@blogger.com4tag:blogger.com,1999:blog-12493181.post-4795470274930114072011-11-15T19:02:00.001+09:002011-11-15T22:52:35.699+09:00xlogdump 0.5.1 releasedThe 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 <satoshi.nagayasu@gmail.com>
* Version 0.5.1
* Allows to lookup the object names forSatoshi Nagayasuhttp://www.blogger.com/profile/09706093898823214408noreply@blogger.com0tag:blogger.com,1999:blog-12493181.post-43316312820508465552011-10-28T23:07:00.012+09:002011-11-04T13:49:32.507+09:00Performance impact of the pg_stat_statements module (Updated: Nov-04-2011)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, Satoshi Nagayasuhttp://www.blogger.com/profile/09706093898823214408noreply@blogger.com5tag:blogger.com,1999:blog-12493181.post-86721498337006518052011-10-22T23:12:00.004+09:002011-10-22T23:28:22.662+09:00Loading data into UNLOGGED tables, and considering ALTER TABLEUNLOGGED 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 Satoshi Nagayasuhttp://www.blogger.com/profile/09706093898823214408noreply@blogger.com6tag:blogger.com,1999:blog-12493181.post-76791369348936560252011-10-21T02:43:00.002+09:002011-10-21T02:56:37.580+09:00Index-only scans and heap block readsAs 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 Satoshi Nagayasuhttp://www.blogger.com/profile/09706093898823214408noreply@blogger.com2tag:blogger.com,1999:blog-12493181.post-23383671859659555172011-10-07T22:03:00.001+09:002011-10-07T22:04:12.813+09:00pgbench on UNLOGGED table(s), Round 2As 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 Satoshi Nagayasuhttp://www.blogger.com/profile/09706093898823214408noreply@blogger.com2tag:blogger.com,1999:blog-12493181.post-88325735703669774402011-10-04T19:46:00.005+09:002011-10-05T00:30:23.996+09:00pgbench on UNLOGGED table(s)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 Satoshi Nagayasuhttp://www.blogger.com/profile/09706093898823214408noreply@blogger.com3tag:blogger.com,1999:blog-12493181.post-16764717190961394922011-09-26T23:21:00.002+09:002011-09-26T23:36:01.908+09:00xlogdump 0.5.0 releasedHi, 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 Satoshi Nagayasuhttp://www.blogger.com/profile/09706093898823214408noreply@blogger.com0tag:blogger.com,1999:blog-12493181.post-824337152421643622011-09-04T13:13:00.002+09:002011-09-21T13:38:04.067+09:00xlogdump 0.4.0I'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 Satoshi Nagayasuhttp://www.blogger.com/profile/09706093898823214408noreply@blogger.com2tag:blogger.com,1999:blog-12493181.post-82639665134016480052011-03-01T12:02:00.001+09:002011-03-01T12:02:47.784+09:00PostgreSQL Query Cache - "pqc"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 ofSatoshi Nagayasuhttp://www.blogger.com/profile/09706093898823214408noreply@blogger.com0tag:blogger.com,1999:blog-12493181.post-81388716241722908392010-05-15T15:52:00.021+09:002010-05-17T02:30:54.923+09:005 steps to implement a PostgreSQL replication systemYesterday, 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 Satoshi Nagayasuhttp://www.blogger.com/profile/09706093898823214408noreply@blogger.com9tag:blogger.com,1999:blog-12493181.post-67001422294494823322010-03-28T17:26:00.012+09:002010-04-01T21:52:48.264+09:00PostgreSQL 9 TestFest Japan on Apr.4Some 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, Satoshi Nagayasuhttp://www.blogger.com/profile/09706093898823214408noreply@blogger.com0tag:blogger.com,1999:blog-12493181.post-75930601781534338362009-12-28T04:05:00.016+09:002009-12-28T23:48:15.112+09:00PostgreSQL Cluster: “Too Many Projects” problem“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 Satoshi Nagayasuhttp://www.blogger.com/profile/09706093898823214408noreply@blogger.com2tag:blogger.com,1999:blog-12493181.post-81287096494211036472009-12-05T04:05:00.008+09:002009-12-05T04:53:34.500+09:00Back to the OpenSource world
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), andSatoshi Nagayasuhttp://www.blogger.com/profile/09706093898823214408noreply@blogger.com0tag:blogger.com,1999:blog-12493181.post-1144633651937074642006-04-10T10:34:00.000+09:002006-04-10T10:52:52.580+09:00New Explain FormatBruce 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,Satoshi Nagayasuhttp://www.blogger.com/profile/09706093898823214408noreply@blogger.com0tag:blogger.com,1999:blog-12493181.post-1144630894231075662006-04-10T09:56:00.000+09:002006-04-10T10:51:36.103+09:00PL/Java 1.2.0 compilation error on PG8.0.0 or 8.0.1According 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 Satoshi Nagayasuhttp://www.blogger.com/profile/09706093898823214408noreply@blogger.com2tag:blogger.com,1999:blog-12493181.post-1124583985570009162005-08-21T09:17:00.000+09:002005-08-21T09:27:13.820+09:00PCTFREE on PgI'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 Satoshi Nagayasuhttp://www.blogger.com/profile/09706093898823214408noreply@blogger.com0