After very long discussions, on 10th of August, Tom Lane committed patch by Robert Haas:
Extend EXPLAIN to support output in XML or JSON format.
There are probably still some adjustments to be made in the details
of the output, but this gets the basic structure in place.
Continue reading Waiting for 8.5 – Machine readable EXPLAIN
On 3rd of August, Tatsuo Ishii committed patch by ITAGAKI Takahiro:
Multi-threaded version of pgbench contributed by ITAGAKI Takahiro,
reviewed by Greg Smith and Josh Williams.
Following is the proposal from ITAGAKI Takahiro:
Pgbench is a famous tool to measure postgres performance, but nowadays
it does not work well because it cannot use multiple CPUs. On the other
hand, postgres server can use CPUs very well, so the bottle-neck of
workload is *in pgbench*.
Multi-threading would be a solution. The attached patch adds -j
(number of jobs) option to pgbench. If the value N is greater than 1,
pgbench runs with N threads. Connections are equally-divided into
them (ex. -c64 -j4 => 4 threads with 16 connections each). It can
run on POSIX platforms with pthread and on Windows with win32 threads.
Here are results of multi-threaded pgbench runs on Fedora 11 with intel
core i7 (8 logical cores = 4 physical cores * HT). -j8 (8 threads) was
the best and the tps is 4.5 times of -j1, that is a traditional result.
$ pgbench -i -s10
$ pgbench -n -S -c64 -j1 => tps = 11600.158593
$ pgbench -n -S -c64 -j2 => tps = 17947.100954
$ pgbench -n -S -c64 -j4 => tps = 26571.124001
$ pgbench -n -S -c64 -j8 => tps = 52725.470403
$ pgbench -n -S -c64 -j16 => tps = 38976.675319
$ pgbench -n -S -c64 -j32 => tps = 28998.499601
$ pgbench -n -S -c64 -j64 => tps = 26701.877815
Is it acceptable to use pthread in contrib module?
If ok, I will add the patch to the next commitfest.
Continue reading Waiting for 8.5 – Multi-threaded pgbench
This post has been updated with new code that uses temporary table – the code is at the end of post!
There was this question on Stack Overflow.
For future reference: guy asked how to do session variables – i.e. something he could define once in session, and later reuse in standard sql queries – without modifying postgresql.conf – so usage of custom_variable_classes is forbidden 🙂
While I don't actually see why somebody would want to avoid modifying its postgresql.conf (short of “it's shared hosting and I don't have superuser privileges"), I thought that it will be rather simple, and at the same time, rather interesting.
So, let's do it:
Continue reading Getting session variables without touching postgresql.conf
Robert Haas wrote, and Tom Lane committed (on 2nd of August) patch which adds interesting capability:
ADD ALTER TABLE ... ALTER COLUMN ... SET STATISTICS DISTINCT
Log message is rather terse, so what does it exactly do?
Continue reading Waiting for 8.5 – hinting for number of distinct values
On 1st of August Tom Lane committed patch from Itagaki Takahiro:
Improve unique-constraint-violation error messages to include the exact
values being complained of.
In passing, also remove the arbitrary length limitation in the similar
error detail message for foreign key violations.
and later added an extension to it:
Department of second thoughts: let's show the exact key during unique index
build failures, too. Refactor a bit more since that error message isn't
spelled the same.
Continue reading Waiting for 8.5 – better messages for unique violation
On 29th of July ( I know, there is a long backlog in waiting for 8.5 series. I'm working on it :), Tom Lane committed patch by Dean Rasheed:
Support deferrable uniqueness constraints.
The current implementation fires an AFTER ROW trigger for each tuple that
looks like it might be non-unique according to the index contents at the
time of insertion. This works well as long as there aren't many conflicts,
but won't scale to massive unique-key reassignments. Improving that case
is a TODO item.
Continue reading Waiting for 8.5 – deferrable uniqueness