Postgresql status

[JD Begin]

Can anyone tell me what is the status of Postgresql? I see it in the Minix 3.1.x but not after. Did it ever work? Would a port to 3.2 be difficult?

[Thomas Cort]

Current status: it doesn't compile out of the box and there are no
Minix-specific patches in pkgsrc for postgresql.

It looks like postgresql 8.4 was ported to Minix a few years ago. I
assume it worked. Though, it doesn't look like the patches made it
upstream as postgresql 9.0's configure script doesn't recognize
Minix[1]. You can find the patches for 8.4 in bigports[2], Minix's
package system pre-pkgsrc. A lot has changed since then both in Minix
and postgresql, so the patches from 8.4 may not be applicable to the
lastest postgresql and the latest Minix. It looks like a port is
possible, but it probably won't be trivial.

Thomas

[1] http://www.few.vu.nl/~lsc300/pbulk-logs/postgresql90-client-9.0.13/configure.log
[2] http://git.minix3.org/?p=bigports.git;a=tree;f=postgresql-8.4.0;hb=HEAD

On Fri, Oct 25, 2013 at 9:19 AM, JD Begin <beg...@gmail.com> wrote:
> Can anyone tell me what is the status of Postgresql? I see it in the Minix
> 3.1.x but not after. Did it ever work? Would a port to 3.2 be difficult?
>

> --
> You received this message because you are subscribed to the Google Groups
> "minix3" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to minix3+un...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.

[Antoine LECA]

Thomas Cort wrote:
> It looks like postgresql 8.4 was ported to Minix a few years ago. I
> assume it worked. Though, it doesn't look like the patches made it
> upstream as postgresql 9.0's configure script doesn't recognize
> Minix[1]. You can find the patches for 8.4 in bigports[2], Minix's
> package system pre-pkgsrc. A lot has changed since then both in Minix
> and postgresql, so the patches from 8.4 may not be applicable to the
> lastest postgresql and the latest Minix. It looks like a port is
> possible, but it probably won't be trivial.

It seems there are several packages in pkgsrc, databases/postgresql84,
databases/postgresql90, and databases/postgresql92. At first sight I
thought it was about SQL dialect :o/ but it really seems to be related
to PostgreSQL versions! So one should be able to grab the patches done
against 8.4.0 version, and to "port" them to the databases/postgresql84
package (which is done against 8.4.17)
Perhaps (not sure) it is less work that trying to port directly the 9.x
versions, although the result will be less attractive (anyway, the
current lack of kernel threads in MINIX 3 is probably a handicap.)


Antoine

[Jan Wieck]

Please note that PostgreSQL 8.4 is currently the oldest supported
release and should be expected to go EOL next year. I would not
recommend wasting any efforts on getting that to run.

PostgreSQL does not use threads. It forks. The configure scripts only
tests for threads in order to compile the client library thread safe.

FYI, I used to be a major PostgreSQL contributor and core team member. I
also designed, implemented and still maintain the Slony1 replication
system for PostgreSQL and the company I work for is using both in all
mission critical applications. Whoever wants to attempt a port, feel
free to poke me with questions.


Regards,
Jan

--
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin

[JD Begin]

Thanks everyone. This is more or less what I expected, although that statement from Jan about Postgresql not using threads is a nice surprise.

[Jan Wieck]

On 10/25/13 13:14, JD Begin wrote:
>
> Thanks everyone. This is more or less what I expected, although that
> statement from Jan about Postgresql not using threads is a nice surprise.

Things are much better than I thought.

I hacked around a little bit and this is what I got so far:

> [postgres@asteroid ~]$ uname -a
> Minix asteroid 3.2.1 i686
> [postgres@asteroid ~]$ psql -h localhost -l
> List of databases
> Name | Owner | Encoding | Collate | Ctype | Access privileges
> -----------+----------+-----------+---------+-------+-----------------------
> postgres | postgres | SQL_ASCII | C | C |
> template0 | postgres | SQL_ASCII | C | C | =c/postgres +
> | | | | | postgres=CTc/postgres
> template1 | postgres | SQL_ASCII | C | C | =c/postgres +
> | | | | | postgres=CTc/postgres
> (3 rows)
>
> [postgres@asteroid ~]$ psql -h localhost -c "select version()"
> version
> ------------------------------------------------------------------------
> PostgreSQL 9.2.5 on i686-pc-minix, compiled by gcc (GCC) 4.4.6, 32-bit
> (1 row)

This is based on a PG 9.2.5 release tarball and some important stuff
doesn't work yet. Namely it seems to have problems with UNIX domain
sockets. As a consequence it can't start the stats collector and in turn
can't start the autovacuum daemon. I'll look into that later.

I can define functions in PL/pgSQL and call them. That's a good start
because it means that PG is happy with the dynamic loader.

This also looks promising:

> [postgres@asteroid ~]$ pgbench -n -c2 -t 1000 foo
> transaction type: TPC-B (sort of)
> scaling factor: 1
> query mode: simple
> number of clients: 2
> number of threads: 1
> number of transactions per client: 1000
> number of transactions actually processed: 2000/2000
> tps = 293.398519 (including connections establishing)
> tps = 294.117647 (excluding connections establishing)

Porting PG 9.3 will not be possible at this point because PostgreSQL has
moved from Sys-V shared memory to mmap() with MAP_SHARED, and apparently
Minix3 doesn't support MAP_SHARED yet. But I guess PG 9.2 will be more
than enough to get things rolling.

About the Unix domain sockets, the funny thing is that I can see the
socket as well as the .lock file in /tmp and they look fine. As said,
I'll look into that later. Thus far there is a PostgreSQL 9.2 server
running in my Minix3.2.1 VM and it can accept multiple concurrent
clients. That's good enough for 2 hours of hacking.

[Thomas Cort]

> I hacked around a little bit and this is what I got so far:

Awesome work!

> About the Unix domain sockets...

There's an ABI break coming up soon-ish[1]. In the latest snapshot of
the 3.3 branch[2], unix domain sockets are split off into their own
server (from pfs) and there are several UDS fixes in there. Perhaps
those will improve the situation. Testing the 3.3 branch is a bit of a
pain right now (you basically need to build a custom install CD and
use it to re-install). When the branch is merged, a new install ISO
will be available at minix3.org and there will be a post in the Google
Group.

Thomas

[1] https://groups.google.com/d/msg/minix3/8ldFwTdrkr4/-Ys-TajM0HQJ
[2] http://git.minix3.org/?p=minix-lionel.git;a=shortlog;h=refs/heads/3.3.0g

[Jan Wieck]

On 10/25/13 19:24, Thomas Cort wrote:
>> I hacked around a little bit and this is what I got so far:
>
> Awesome work!
>
>> About the Unix domain sockets...
>
> There's an ABI break coming up soon-ish[1]. In the latest snapshot of
> the 3.3 branch[2], unix domain sockets are split off into their own
> server (from pfs) and there are several UDS fixes in there. Perhaps
> those will improve the situation. Testing the 3.3 branch is a bit of a
> pain right now (you basically need to build a custom install CD and
> use it to re-install). When the branch is merged, a new install ISO
> will be available at minix3.org and there will be a post in the Google
> Group.

I dug a little around in the PG code.

It isn't using UNIX domain sockets at that place. It gets a list of
possible addresses for "localhost" with AI_PASSIVE via getaddrinfo(),
then tries to create a DGRAM socket and bind it to what getaddrinfo()
returned, stopping at the first address that worked. Minix complains
"Can't assign requested address" on the bind(2) attempt. I didn't check
what exactly that requested address is. It should be 127.0.0.1:0, for
which the kernel is supposed to generate a random, unused port number.

If I can get this to work I will create a patch against the PG 9.2.5
tarball that makes it configure and build on Minix3.2.1. I feel I'm not
too far away from that.

[Thomas Cort]

> Minix complains "Can't assign requested address" on the bind(2) attempt

> I didn't check what exactly that requested address is. It should be
> 127.0.0.1:0

I don't know if this is still applicable, but the porting guide on the
wiki mentions something about listening on 127.0.0.1...

From the guide: "Because there is no dedicated loopback device, it is
not possible to have a server listening on localhost (127.0.0.1). If
you encounter that problem, you can listen on INADDR_ANY instead; but,
note that it works only if there is a network connection (note that,
for example, the X Window System cannot be started without one). Also,
think through the security implications of accepting non-local
connections. " --
http://wiki.minix3.org/en/DevelopersGuide/PortingGuide

Thomas

[Jan Wieck]

Thank you Sir, that got me a little further. Now it errors out at the
getsockname() call. It seems that the discussion below never made it
into the libc code. Looking at getsockname.c it still only handles tcp
and uds.

https://groups.google.com/forum/#!topic/minix3/g2dxAUIRVEI

Now of course, the implications of binding to INADDR_ANY are big and I
am more inclined to try solving this by creating another UNIX domain
socket in /tmp with permissions 0700 when #ifdef __minix.

In any case, this gets me one step further. Thank you.

[Jan Wieck]

On 10/25/13 21:37, Thomas Cort wrote:

>>From the guide: "Because there is no dedicated loopback device, it is
> not possible to have a server listening on localhost (127.0.0.1). If
> you encounter that problem, you can listen on INADDR_ANY instead; but,
> note that it works only if there is a network connection (note that,
> for example, the X Window System cannot be started without one). Also,
> think through the security implications of accepting non-local
> connections. " --
> http://wiki.minix3.org/en/DevelopersGuide/PortingGuide
>
> Thomas
>

This was exactly on spot. Aside from a few other things.

At this moment I can run a full "make installcheck" for the PostgreSQL
regression suite without material errors. There is one error because
Minix's dlopen() produces "no such file or directory" instead of the
expected "No such file or directory" message. Yeah, I had to look at
that twice myself.

I added UDP support to getsockname(), which made postmaster launch the
autovacuum daemon. Yay!!!

However ... PostgreSQL is not very happy on Minix 3.2.1 at this point.

Concurrent cross-process access of the same files (which is basically
the main task of a database), causes a LOT of errors like

ERROR: could not seek to block 1942 in file "base/57364/57377":
Undefined error: 0
CONTEXT: writing block 1942 of relation base/57364/57377

This happens even if I run just one single pgbench client.

I have to dig into more PG code to find out what this actually means.
Undefined error: 0 doesn't really sound right to begin with.


Anyhow, it compiles and does something ...

[Jan Wieck]

On 10/26/13 00:51, Jan Wieck wrote:
> I added UDP support to getsockname(), which made postmaster launch the
> autovacuum daemon. Yay!!!

First things first, this is adding support for UDP sockets to
getsockname(2) and getpeername(2). The commit can be cherry-picked into
3.2.1 as well.

https://github.com/wieck/minix/commit/30312dd70fc2213803d28152ab4e3854e3b4ffbd


Regards,

[Thomas Cort]

Hi Jan,

Thanks for the patch! I've submitted it for testing and code review;
you can follow the progress of your patch at
http://gerrit-minix.few.vu.nl/#/c/1073/

Thomas

[Jan Wieck]

On 10/26/13 00:51, Jan Wieck wrote:

> Concurrent cross-process access of the same files (which is basically
> the main task of a database), causes a LOT of errors like
>
> ERROR: could not seek to block 1942 in file "base/57364/57377":
> Undefined error: 0
> CONTEXT: writing block 1942 of relation base/57364/57377
>
> This happens even if I run just one single pgbench client.
>
> I have to dig into more PG code to find out what this actually means.
> Undefined error: 0 doesn't really sound right to begin with.

This is bad news. This is not PG's fault.

These errors result from a call to lseek(..., SEEK_SET) returning a
different offset than was requested. The file in question is larger than
the requested offset and those offsets are only in the MB range, so
lseek(2) is totally sufficient. PostgreSQL avoids problems there by
splitting logical files into 1G segments.

I did a little printf debugging in servers/vfs/open.c do_lseek() and it
turns out that it does compute the correct newpos, but somehow the value
changes on its way into m_out.reply_l1. I got other errors from
PostgreSQL in the mean time, reporting unexpected data at the supposed
end of files and getting EOF on attempts to read data that should be
there. So do_lseek() is probably not the only function causing trouble.
My test code in do_lseek() checked that the output message's result
equals the job messages requested offset in the case of SEEK_SET. I got
a lot of output on the console, a lot more than PostgreSQL complained about.

The code in servers/vfs/open.c and include/minix/u64.h looks OK to me
though. I have my money on the C compiler in this case.

The PG 9.2 server I have working has some shortcuts in it, like
disabling AF_UNIX to mask some of the problems. But I will try to put
those changes into the pkgsrc PG 9.2 and then push that branch to github
so that someone with more compiler knowledge than I have can use it as a
test tool to hunt down this problem.


Regards,

[Thomas Veerman]

Is it possible PG mistakenly thinks it can use 64-bit file offsets (off_t)? This could cause PG to misinterpret the lseek return value. If so, MINIX 3.3.0 will fix this problem.

--
Thomas

[D.C. van Moolenbroek]

Hello!

On Saturday, October 26, 2013 10:28:47 PM UTC+2, JanW...@Yahoo.com wrote:

These errors result from a call to lseek(..., SEEK_SET) returning a
different offset than was requested.

Are you using the 3.2.1 stable release (git-972156d)? Sometime after the release, a return message corruption issue got fixed in VFS (git-cef94e0), and the symptoms of that issue sound pretty much like what you're describing. If this could be the case, you might want to try a newer snapshot from our snapshots page [1] - although that page seems to have gone missing at the moment somehow - or upgrade your installation through git [2].

Regards,
David

[1] http://download.minix3.org/iso/snapshot/
[2] http://wiki.minix3.org/en/DevelopersGuide/TrackingCurrent

[Jan Wieck]

No, that is not possible. As said, the offsets in question are in the MB
range and the printf()s inside of do_lseek() show clearly that the
values get screwed up right inside there.

[Jan Wieck]

On 10/26/13 17:09, D.C. van Moolenbroek wrote:
> Are you using the 3.2.1 stable release (git-972156d)? Sometime after the
> release, a return message corruption issue got fixed in VFS
> (git-cef94e0), and the symptoms of that issue sound pretty much like
> what you're describing.

Precisely what I was running on. R3.2.1 plus my own changes for
getsockname().

I checked out git-cef94e0 and the seek problems are gone at that commit
point. Thank you for spotting this.

[Jan Wieck]

I get the feeling that PostgreSQL is an excellent stress test for
Minix3. Now I can get a kernel panic on demand!

After David's comment I upgraded to current git master. Thanks to Thomas
and everyone involved to get the getsockname() patch in that speedy.
That got me to running PostgreSQL 9.2.5 without errors for some load. It
passes the regression test and can run pgbench with 1-3 clients quite
nicely. I even get a competitive performance (transactions per second)
out of it.

That means that I am definitely in reach for a successful Minix port of
PostgreSQL.

However, cranking up the load to 10 clients results in the attached
console. I did get that panic with 3.2.1 too, so this is certainly an
older bug. As said, PostgreSQL seems to be a good stress test.

[Jan Wieck]

On 10/26/13 21:34, Jan Wieck wrote:
> However, cranking up the load to 10 clients results in the attached
> console. I did get that panic with 3.2.1 too, so this is certainly an
> older bug. As said, PostgreSQL seems to be a good stress test.

Got another panic running 20 clients from a "remote" VM.

This is fun :)

[Jan Wieck]

The good news is that despite all those crashes, Minix3 has not yet
managed to corrupt my database. I take a kernel panic over a silent DB
corruption any day, because a silent corruption is the worst nightmare,
one that no ordinary human can imagine.

This means that Minix3 does honor disk write order with respect to
write(2) and fsync(2) requests properly. In the past couple of hours I
have seen PostgreSQL go through several minutes of crash recovery,
replaying megabytes of lost updates from the WAL. Every single time the
DB comes up in a consistent state.

For those who are interested (I know some here are), here is a brief
overview how PostgreSQL does IO and recovers from a system crash:

Table and index blocks (8K each) are managed in a shared memory area,
the "buffer cache". All changes to blocks in the cache are also recorded
in a sort of diff format in the Write Ahead Log (WAL). The WAL is
managed as a ring buffer in another shared memory area.

There are two background processes associated with these shared memory
areas. The WAL writer, constantly writing the WAL data out to disk, and
the Background Writer, constantly writing dirty blocks out to disk.

The problem here is that the Background Writer should NEVER get ahead of
the WAL Writer. That would spell disaster in the headline and corrupted
database in the fine print. You will soon see why.

When a transaction modifies data in a block, it first adds the "diff"
information to the WAL (shared memory). It then changes the block data
(also in shared memory) and it records the WAL position of its "diff" in
the block header. The WAL writer will pick up the WAL data from its
shared memory (ring buffer) and at certain points it will fsync() the
file. After that it will set a variable (in yet another shared memory
area) to that new position. The BackgroundWriter compares the WAL
position of any dirty block to that variable, and will not write it to
disk if it is too recent. This way no data block on disk can ever be
ahead of the WAL. PostgreSQL keeps changes to data files in memory until
their WAL records have not only been written, but fsync()'d to disk.

With that guarantee in place, only the WAL needs to make it to the disk
to be able to recover from a crash. Oh ... of course ... there is one
other thing ... checkpoints. A checkpoint is basically a WAL position.
You understand by now that the WAL is nothing more than a time line. A
checkpoint merely says "no data block on disk is older than this". That
checkpoint position is recorded in the control file. Older WAL can be
discarded.

So, when PostgreSQL recovers from a crash, it reads its own WAL from the
last checkpoint position and applies all the "diffs", that according to
the block headers didn't get written by the BackgroundWriter.

However, this all is working only if the OS and disk (firmware) never
lie about write ordering. Consumer grade disks are notorious about lying
when it comes to write caching. In the above scenario you can't have a
disk saying "I wrote that WAL record", then you write a data block, and
after a crash it has the new data block but says "you never gave me that
WAL record". Write caching on the disk controller level can lead to that.

Are you dizzy now? I was when I first tried to understand all this. It
certainly isn't simple, but PostgreSQL does get all this right with
hundreds of concurrent client connections and thousands of disk blocks
cached in shared memory.

Minix3 gets this right too. But I would like it to do so without a
kernel panic ....

[Thomas Veerman]

Hi Jan,

The second panic has to do with miscommunication between VFS and PFS. This has been fixed in the 3.3.0 branch. The first panic is in VFS' do_select and might be fixed in the 3.3.0 branch as there are some changes to the select code as well, but I doubt it.

Which steps did you take to compile Postgres and how do you run the pgbench? Maybe I can take a look at what's going on.

--

Thomas

[Jan Wieck]

Thomas,

On 10/27/13 10:07, Thomas Veerman wrote:
> Hi Jan,
>
> The second panic has to do with miscommunication between VFS and PFS.
> This has been fixed in the 3.3.0 branch. The first panic is in VFS'
> do_select and might be fixed in the 3.3.0 branch as there are some
> changes to the select code as well, but I doubt it.

The last tests I did on the HEAD/master branch of /usr/src. Is that the
3.3.0 development branch or not?

>
> Which steps did you take to compile Postgres and how do you run the
> pgbench? Maybe I can take a look at what's going on.

I worked from the postgresql-9.2.5 release tarball, not from pkgsrc. In
that I added a minix section to Makefile.shlib, created port files for
dynloader, and the minix specific port header in which I (for now)
disabled UNIX domain sockets and IPV6. A few extra #ifdef/#ifndef here
and there to get around things that aren't yet implemented in Minix,
like TCP_NODELAY and RLIMIT_CORE.

Then ./configure with options to add /usr/pkg/include and /usr/pkg/lib,
force the minix template and finally a normal gmake build using gcc44.

With that as a working example my plan is to attempt to do the actual
minix port of pkgsrc/postgresql92. I originally wanted to look into the
UNIX domain socket issues before that, but I can do it the other way
around so that you have a working (kernel panicking) PG92 sooner.


Regards,
Jan

> <mailto:minix3%2Bunsu...@googlegroups.com>.

> For more options, visit https://groups.google.com/groups/opt_out.
>
>
> --
> You received this message because you are subscribed to the Google
> Groups "minix3" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to minix3+un...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.

[Thomas Veerman]

Hi Jan,

No, that's not the 3.3.0 branch. Master is still at 3.2.1. 3.3.0 will introduce an ABI breakage and needs a lot of work still. It's therefore kept separate.

If you're willing to patch pkgsrc/postgres92 that would be awesome. Should I wait for that first?

--

Thomas

[Jan Wieck]

On 10/27/13 12:42, Thomas Veerman wrote:
> Hi Jan,
>

> No, that's not the 3.3.0 branch. Master is still at 3.2.1. 3.3.0 will
> introduce an ABI breakage and needs a lot of work still. It's therefore
> kept separate.

Where would that be then? I don't see a 3.3 branch in origin
(git.minix3.org/minix).

>
> If you're willing to patch pkgsrc/postgres92 that would be awesome.
> Should I wait for that first?

That might take a few moments longer. There seems to be something wrong
with devel/gettext-tools.

I can get you started with kernel debugging another way. Attached you
find a patch for PostgreSQL 9.2.5. With that you can create an "old
fashioned" PostgreSQL installation from the release source tarball. Download

http://ftp.postgresql.org/pub/source/v9.2.5/postgresql-9.2.5.tar.gz

then follow these steps:

* Add "postgres" with GID 30 to /etc/group (that should be there anyway)

* Create a home directory /usr/local/pgsql for user postgres, set a
login shell and create a login environment. Make sure that the
.profile (or equivalent) contains

PGDATA=/usr/local/pgsql/data ; export PGDATA
PATH=/usr/local/pgsql/bin:$PATH ; export PATH

* Login as user postgres. Using PostgreSQL doesn't require root access
to a machine. One can do it all with an unprivileged account.

tar xzf postgresql-9.2.5.tar.gz
cd postgresql-9.2.5
patch -p1 <.../pg925-minix.diff
./configure --prefix=/usr/local/pgsql --disable-thread-safety
gmake install
gmake -C contrib/pgbench install
initdb

* edid ./data/postgresql.conf, uncomment and change the parameter
listen_addresses so that it reads listen_addresses = '*'

* You now can start the postgres server process in the foreground on
a console or xterm with the command

postmaster

* On a second console (or xterm) you can now setup and run pgbench.
Login as user postgres a second time, then

createdb bench
pgbench -i -s5 bench
pgbench -n -c1 -t1000 bench

After a few seconds you should get the performance stats for that run

* Now we're ready for kernel panic. Give it a sync for good measure and

pgbench -n -c20 -t1000 bench

Ka-Boom :)

* Let me know if this worked as advertised.

[Tom Chandler]

Just a FYI, I followed the instructions in this email, obtained the same results.  However, GREAT JOB and I am

sure the team will find a fix.

Cheers

Tom c

[Jan Wieck]

On 10/28/13 12:30, Tom Chandler wrote:
> Just a FYI, I followed the instructions in this email, obtained the same
> results. However, GREAT JOB and I am
> sure the team will find a fix.
>
> Cheers
> Tom c

Thanks for the feedback. Highly appreciated. Always good to know that
someone else can reproduce an error or crash.

I now have a working pkgsrc branch that results in a working
postgresql92-client and postgresql92-server. This is NOT ready to be
merged into pkgsrc/minix-master. I'm just putting it out here so people
can use pkgsrc to reproduce the problems.

The development branch for this can be found here:

https://github.com/wieck/minix-pkgsrc/tree/postgresql92

You need to be tracking current to use this. It will not compile/work
with the 3.2.1 stable CD install.

You want to build and install -client and -server packages instead of
the super-package postgresql92. That one pulls in all the doc stuff.
Assuming you have my git repository set up as remote "wieck":

cd /usr/pkgsrc
git checkout -b postgresql92 wieck/postgresql92
cd databases/postgresql92-client
bmake install clean
cd ../../databases/postgresql92-server
bmake install clean

Building the client part may fail because your perl5 or openssl installs
are too old. In that case "bmake replace" those packages and rerun the
install.

You are now ready to create the database root directory and start the
database server.

/usr/pkg/etc/rc.d/pgsql initdb
/usr/pkg/etc/rc.d/pgsql start

Create a database user and a test database for him.

su pgsql createuser -U pgsql -S testuser
su pgsql createdb -U pgsql -O testuser testdb

Now you can initialize and test this with pgbench.

pgbench -U testuser -i -s5 testdb
pgbench -U testuser -n -c1 -t1000 testdb

You should see a performace statistic telling you the TPS (transactions
per second). Congratulations, you have a working PostgreSQL 9.2.4
database installation on your Minix system.

Now you are ready to crash the system:

sync
sync
pgbench -U testuser -n -c20 -t1000 testdb


Happy kernel panic hunting :)
Jan

>
>
>
> On Sun, Oct 27, 2013 at 12:57 PM, Jan Wieck <JanW...@yahoo.com
> <mailto:JanW...@yahoo.com>> wrote:
>
> On 10/27/13 12:42, Thomas Veerman wrote:
> > Hi Jan,
> >
> > No, that's not the 3.3.0 branch. Master is still at 3.2.1. 3.3.0 will
> > introduce an ABI breakage and needs a lot of work still. It's
> therefore
> > kept separate.
>
> Where would that be then? I don't see a 3.3 branch in origin

> (git.minix3.org/minix <http://git.minix3.org/minix>).

> <mailto:minix3%2Bunsu...@googlegroups.com>.

> For more options, visit https://groups.google.com/groups/opt_out.
>
>
> --
> You received this message because you are subscribed to the Google
> Groups "minix3" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to minix3+un...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.

[Tom Chandler]

quick question.  I don't know a lot about internal postgres.  On my Minix3 system, when I start it with
postmaster, I get six (6) copies of postmaster running on my minix3.  On my ubuntu 12.04, I only get one
copy of it.

In addition, on my minix3 system, when I use psql, it starts a NEW copy of postmaster.  When I exit
psql, the postmaster task also ends.

THis may be due to Minix3, but It was something I thought strange, 6 copies of postmaster running.

Just food for thought.

Thank You
Tom C

[Jan Wieck]

On 10/29/13 10:29, Tom Chandler wrote:
> quick question. I don't know a lot about internal postgres. On my
> Minix3 system, when I start it with
> postmaster, I get six (6) copies of postmaster running on my minix3. On
> my ubuntu 12.04, I only get one
> copy of it.

Nope, you get multiple processes on ubuntu too. But ubuntu supports
changing the process title for ps, and they all show what that process
is doing. There should be a checkpointer, a stats collector, a writer, a
wal writer and the autovacuum launcher in addition to the actual
postmaster process.

>
> In addition, on my minix3 system, when I use psql, it starts a NEW copy
> of postmaster. When I exit
> psql, the postmaster task also ends.

For every client connection, the postmaster does fork what we call the
"backend". It has been that way forever to protect the data. As said
initially, PostgreSQL doesn't use threads. If anything goes wrong (crash
of a backend), the postmaster will ASAP kill all other existing backends
and restart the whole system, going through crash recovery. This is an
attempt to prevent whatever the crashed backend MAY have corrupted
inside the shared memory will not make it to disk. This is why in all
the 20 years, I've been using and developing PostgreSQL, I have not seen
a database corruption that wasn't caused by faulty hardware.

>
> THis may be due to Minix3, but It was something I thought strange, 6
> copies of postmaster running.

That one is normal. But the postmaster disappearing when you disconnect
is not. If that would be happening, you should not be able to connect
again. Can you?


Thanks for the feedback,
Jan

> > <mailto:minix3%2Bunsu...@googlegroups.com
> <mailto:minix3%252Buns...@googlegroups.com>>.

[Tom Chandler]

Thank you for the response.  To clarify, I have 6 postmaster running.   I start psql, and the 7th postmaster starts.  When I
exit psql, the 7th postmaster ends.  The remaining 6 postmaster are still running.  I can turn around and issue psql again and it
starts the 7th postmaster, until I exit psql.  So I guess it is working correctly.

Thank you for the info.  Really interesting "STUFF".....

Thank You

Tom C

[Jan Wieck]

On 10/29/13 10:29, Tom Chandler wrote:

> When I exit
> psql, the postmaster task also ends.

This I cannot reproduce on my installation tracking current.

[Jan Wieck]

On 10/29/13 12:28, Tom Chandler wrote:
> Thank you for the response. To clarify, I have 6 postmaster running.
> I start psql, and the 7th postmaster starts. When I
> exit psql, the 7th postmaster ends. The remaining 6 postmaster are
> still running. I can turn around and issue psql again and it
> starts the 7th postmaster, until I exit psql. So I guess it is working
> correctly.

Yes, that is working as designed. The 7th postmaster process is actually
the backend handling your client connection. If you run multiple psql
commands in parallel from different terminals, you will actually see
number 8, 9, and so on too. And whenever PostgreSQL automatically
analyzes or vacuums a table, you have another process doing that (up to
3 in the default config).

PostgreSQL is very fork happy :) That is why it also likes connection
pooling very much. The fork() makes establishing a new client connection
very expensive. Connection pools like pgbouncer not only eliminate that
cost, but also keep the concurrency lower. A few very busy connections
are better for a database than many mostly idle ones.

That actually might be a clue for the crash. When pgbench is launched,
it establishes all client connections at once, so the postmaster is
going to fork(), fork(), fork(), ... in very rapid succession and all
those new processes will be closing and reopening tons of files. It
might be that flood of things going on that is overwhelming VFS.

>
> Thank you for the info. Really interesting "STUFF".....

NP,
Jan

> > > <mailto:minix3%2Bunsu...@googlegroups.com
> <mailto:minix3%252Buns...@googlegroups.com>
> > <mailto:minix3%252Buns...@googlegroups.com
> <mailto:minix3%25252Bun...@googlegroups.com>>>.

[Tom Chandler]

I am running tracking current as of Monday AM.  I just tested again and when I start psql, a NEW postmaster task starts.  When

I exit psql, the NEW postmaster task ends also.  The original 6 tasks are still running.

Thank You

Tom C\

--
You received this message because you are subscribed to the Google Groups "minix3" group.

[JD Begin]

Jan,

I'm following your instructions to track current and build Postgresql92. I'm a relative beginner at this (minix/git) so it's been interesting. I've tripped over everything, including Perl and Openssl. Openssl is rebuilding as we speak. 

My question is: is it normal for the build process to take hours? I'm running Minix under a VirtualBox VM and these builds take a LONG time. Openssl has been building for at least 2 hours. Postgresql-client failed (on the bad openssl) after quite a run as well. 

Am I missing something?

Anyway, thanks for the how-to, I can't wait for these builds to end and give it a go.

JD

>     <mailto:minix3%2Bu...@googlegroups.com>.

[Jan Wieck]

On 10/30/13 11:35, JD Begin wrote:
> Jan,
>
> I'm following your instructions to track current and build Postgresql92.
> I'm a relative beginner at this (minix/git) so it's been interesting.
> I've tripped over everything, including Perl and Openssl. Openssl is
> rebuilding as we speak.
>
> My question is: is it normal for the build process to take hours? I'm
> running Minix under a VirtualBox VM and these builds take a LONG time.
> Openssl has been building for at least 2 hours. Postgresql-client failed
> (on the bad openssl) after quite a run as well.
>
> Am I missing something?

The first time you build something from pkgsrc it drags in a lot of
dependencies. That does take a while because all those dependencies are
built from sources too.

You can speed up the process by analyzing the packages Makefile and
buildlink3.mk and installing/updating the dependencies via pkgin before
trying to build the package.

>
> Anyway, thanks for the how-to, I can't wait for these builds to end and
> give it a go.

Let us know how it goes,
Jan

> <javascript:>

> > send an email to minix3+un...@googlegroups.com <javascript:>
> > <mailto:minix3%2Bu...@googlegroups.com <javascript:>>.

> > For more options, visit
> https://groups.google.com/groups/opt_out

> <https://groups.google.com/groups/opt_out>.

> >
> >
> > --
> > You received this message because you are subscribed to the Google
> > Groups "minix3" group.
> > To unsubscribe from this group and stop receiving emails from it,
> send

> > an email to minix3+un...@googlegroups.com <javascript:>.

> > For more options, visit https://groups.google.com/groups/opt_out

> <https://groups.google.com/groups/opt_out>.

>
>
> --
> Anyone who trades liberty for security deserves neither
> liberty nor security. -- Benjamin Franklin
>
> --
> You received this message because you are subscribed to the Google
> Groups "minix3" group.
> To unsubscribe from this group and stop receiving emails from it, send

[JD Begin]

It might be a while before I'm running.. I had only allocated 5 gigs to my virtual hard drive (I know. Seemed like a good idea at the time) and my machine died because it ran out of disk space. I'll start over tonight. 

As for your suggestions about dependencies, I'll definitely give it a try.

JD

[JD Begin]

Almost there. At startup I get:

"Local packages (start): pgsql ulimit: internal error (n)"

"Starting pgsql"

"pg_ctl: could not start server"

"Examine the log output"

Any idea? Btw, where is that log?

JD

[JD Begin]

Ok, I got my Postgresql 9.2 running. Thanks Jan! This is great.

[JD Begin]

I executed the " pgbench -U testuser -n -c1 -t1000 testdb" on a VirtualBox running Debian and Postgresql 9.1.

I was pleasantly surprised to see that both Debian and Minix got the exact same tps (around 500 tps).  With only one client, Debian couldn't take advantage of it's extra processors, but I keep reading that Minix is at least 10% slower than Linux and in this particular case it just ain't true.

Of course, if I use 2 clients, things look better for Debian, with around 875 tps and still 500-ish for Minix. Still, Minix is holding its own.

I allocated 4 processors to that virtual Debian box. At 3 clients, I get around 1150 tps. Then Debian levels off (3 clients + 1 server = 4). At 4 clients, I get 1250 tps, and so on, maxing out at 1500-ish. Using the "thread" option (-j) doesn't seem to have a big impact on the results.

When I run the 20 client test that breaks Minix, Debian issued some extra messages but didn't really have a problem with it. 

postgres@debian64:/usr/local$ /usr/lib/postgresql/9.1/bin/pgbench -U postgres -n -c20 -t1000 testdb

2013-10-31 19:28:40 EDT LOG:  checkpoints are occurring too frequently (6 seconds apart)

2013-10-31 19:28:40 EDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".

2013-10-31 19:28:45 EDT LOG:  checkpoints are occurring too frequently (5 seconds apart)

2013-10-31 19:28:45 EDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".

transaction type: TPC-B (sort of)

scaling factor: 5

query mode: simple

number of clients: 20

number of threads: 1

number of transactions per client: 1000

number of transactions actually processed: 20000/20000

tps = 1526.694948 (including connections establishing)

tps = 1532.473810 (excluding connections establishing)

JD

[Jan Wieck]

Yes, these are known issues with the "very conservative" defaults for a
PostgreSQL "out of the box" installation.

This issue has been discussed literally "to death" on the PostgreSQL
mailing lists and trust me, you will not get this super conservative
attitude changed easily. I am a part of that "conservative attitude
gang", so try me if you want to, before getting rejected on the
"official" PostgreSQL channels.

The point is that if some package needs a PostgreSQL server as a
dependency, it is good enough to install it with minimal resources to
satisfy the dependency. It will work. Maybe it will not work fast, but
it will work.

If you on the other hand need to run PostgreSQL as a main component of
your infrastructure, you better know what you are doing here and provide
as well as configure the necessary resources. The PostgreSQL project has
a long history of getting bad reviews from people, who simply have zero
clue what they are doing. In the Oracle world, nobody cares about
anyone's opinion, unless they are somewhat certified. In the PostgreSQL
world, everyone who has used MySQL or SQLite before is considered a
database expert. Effing morons, if you ask me ... but I only have hacked
the PostgreSQL backend code for two decades, so what do I know?

</rant>

The thing is, those messages go away once you learn how to tailor your
PostgreSQL installation to your needs.

A MySquirrel or SQLighter installation may never warn you by giving you
that kind of message. That however doesn't mean they do a better job or
perform better. It just means they don't tell you, or they simply don't
have a concept of that sort of resource (which would scare the heck out
of me).


Regards,
Jan

> transaction type: TPC-B (sort of)
> scaling factor: 5
> query mode: simple
> number of clients: 20
> number of threads: 1
> number of transactions per client: 1000
> number of transactions actually processed: 20000/20000
> tps = 1526.694948 (including connections establishing)
> tps = 1532.473810 (excluding connections establishing)
>
> JD
>
> On Thursday, October 31, 2013 4:59:41 PM UTC-4, JD Begin wrote:
>
> Ok, I got my Postgresql 9.2 running. Thanks Jan! This is great.
>

> --
> You received this message because you are subscribed to the Google
> Groups "minix3" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to minix3+un...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.


--
Anyone who trades liberty for security deserves neither

liberty nor security. -- Benmin Franklin

[JD Begin]

I agree. Its hard to change people's attitude in our business. A lot of folks have made up their minds.

Little postcript to my previous numbers. If i starr my Debian box with only one processor, it runs around 1000 tps, nearly double Minix (and Debian mp).

[Jan Wieck]

On 11/01/13 07:43, JD Begin wrote:
> I agree. Its hard to change people's attitude in our business. A lot of folks have made up their minds.
>
> Little postcript to my previous numbers. If i starr my Debian box with only one processor, it runs around 1000 tps, nearly double Minix (and Debian mp).
>

Try setting synchronous_commit=off (that is not the same as the VERY
dangerous fsync=off).

synchronous_commit=off does not have the danger of database corruption.
It simply means that the database will not wait on commit until the WAL
record has been flushed to disk, but report COMMIT OK to the client
right after that WAL record was added to shared memory. If the database
or server crashes at that moment, the transaction will silently be lost.

Many applications can live with that behavior and an application can
change that setting from transaction to transaction. For a pgbench run
it can easily give you another 30-50% boost.


Regards,
Jan

--
Anyone who trades liberty for security deserves neither

liberty nor security. -- Benjamin Franklin

[Thomas Veerman]

The cause of the crash has already been fixed in the 3.3.0 branch [0]. A work around for 3.2.1 can be found at [1], but that will not be merged into 3.3.0. I wasn't aware it was already fixed in 3.3.0 when I wrote the patch for 3.2.1.

[0] http://gerrit-minix.few.vu.nl/#/c/950/

[1] http://gerrit-minix.few.vu.nl/#/c/1104/

--

Thomas

[Jan Wieck]

On 11/05/13 05:26, Thomas Veerman wrote:
> The cause of the crash has already been fixed in the 3.3.0 branch [0]. A
> work around for 3.2.1 can be found at [1], but that will not be merged
> into 3.3.0. I wasn't aware it was already fixed in 3.3.0 when I wrote
> the patch for 3.2.1.

Thomas,

interesting, from the comment on the patch I understand that PostgreSQL
is apparently using concurrent select(2) calls on the same file
descriptors. I wasn't aware it is doing that.

Will the workaround be included in 3.2.2 (if there are plans for a 3.2.2)?

I also found a problem but not yet a workaround for UNIX domain sockets.
The thing there is that Minix seems to be the only platform that does
have UNIX domain sockets, but does not support all features on them,
like SO_ERROR. PostgreSQL has an abstraction layer on top of sockets and
treats INET and UNIX domain identical. Since Minix has SO_ERROR defined,
it will use it on both types.

My suggestion would be to disable UNIX domain sockets in the PG
configuration for Minix until Minix supports these features.

I will apply that workaround [1]. If PostgreSQL works stable after that
I will clean up my port changes and push another pkgsrc branch to github.

[Thomas Veerman]

On Nov 5, 2013, at 2:53 PM, Jan Wieck <JanW...@Yahoo.com> wrote:

> On 11/05/13 05:26, Thomas Veerman wrote:
>> The cause of the crash has already been fixed in the 3.3.0 branch [0]. A
>> work around for 3.2.1 can be found at [1], but that will not be merged
>> into 3.3.0. I wasn't aware it was already fixed in 3.3.0 when I wrote
>> the patch for 3.2.1.
>
> Thomas,
>
> interesting, from the comment on the patch I understand that PostgreSQL
> is apparently using concurrent select(2) calls on the same file
> descriptors. I wasn't aware it is doing that.

That might be my phrasing. I meant that multiple processes are doing a select(2) on the same file (which is not the same as a file descriptor; that's just a handle pointing to a file) at the same time.

>
> Will the workaround be included in 3.2.2 (if there are plans for a 3.2.2)?

There are no plans for a 3.2.2. But I think that 3.3.0 branch will soonish be merged with master (that's not to say there will be release).

>
> I also found a problem but not yet a workaround for UNIX domain sockets.
> The thing there is that Minix seems to be the only platform that does
> have UNIX domain sockets, but does not support all features on them,
> like SO_ERROR. PostgreSQL has an abstraction layer on top of sockets and
> treats INET and UNIX domain identical. Since Minix has SO_ERROR defined,
> it will use it on both types.
>
> My suggestion would be to disable UNIX domain sockets in the PG
> configuration for Minix until Minix supports these features.

I don't think that adding support for this is very hard. Do you only need SO_ERROR or is there more? At a glance all needed infrastructure is already there. Only the access to the data has to be implemented. However, the server responsible for UDS has been changed significantly in the 3.3.0 branch, so it'll have to wait.

--
Thomas

[Jan Wieck]

On 11/05/13 09:22, Thomas Veerman wrote:
>
> On Nov 5, 2013, at 2:53 PM, Jan Wieck <JanW...@Yahoo.com> wrote:
>
>> On 11/05/13 05:26, Thomas Veerman wrote:
>>> The cause of the crash has already been fixed in the 3.3.0 branch [0]. A
>>> work around for 3.2.1 can be found at [1], but that will not be merged
>>> into 3.3.0. I wasn't aware it was already fixed in 3.3.0 when I wrote
>>> the patch for 3.2.1.

I applied that patch and "tracking-current" with that patch survived a
20-client, even a 30-client pgbench run. But it again failed with a
50-client run, see attached screen shot.

>> I also found a problem but not yet a workaround for UNIX domain sockets.
>> The thing there is that Minix seems to be the only platform that does
>> have UNIX domain sockets, but does not support all features on them,
>> like SO_ERROR. PostgreSQL has an abstraction layer on top of sockets and
>> treats INET and UNIX domain identical. Since Minix has SO_ERROR defined,
>> it will use it on both types.
>>
>> My suggestion would be to disable UNIX domain sockets in the PG
>> configuration for Minix until Minix supports these features.
>
> I don't think that adding support for this is very hard. Do you only need SO_ERROR or is there more? At a glance all needed infrastructure is already there. Only the access to the data has to be implemented. However, the server responsible for UDS has been changed significantly in the 3.3.0 branch, so it'll have to wait.

With all the changes, I'd rather wait for 3.3.0 before looking at that.
Right now the database is working (for some definition of that) with
IPV6 and UNIX_DOMAIN disabled. That is a pretty big step forward and
puts it almost en-par with most Windows installations (except for the
crash on high load).

Since I can still panic the 3.2.1 kernel even with your patch applied, I
think it is best to leave it where it is at this point and revisit the
port once 3.3.0 is available.

As said, I will clean up the hacked port and push a new branch to
github. This may take a day or two as I am a bit swamped with actual "work".

[Thomas Veerman]

On Nov 5, 2013, at 5:57 PM, Jan Wieck <JanW...@Yahoo.com> wrote:

I applied that patch and "tracking-current" with that patch survived a
20-client, even a 30-client pgbench run. But it again failed with a
50-client run, see attached screen shot.

I can't reproduce that. See attached screenshot for 3.2.1 with my patch. I verified 50 clients on 3.3.0 as well. I did run into a system limit a couple of times (ENFILE) and a consequent crash of a backend process. The benchmark would then stop prematurely and report results. The backend recovers and I could run the benchmark again.

--

Thomas