CYGWIN PostgreSQL problems - backend processes turn non responsive after a a couple hours of hard use.

Prasad,

In the future, please post to pgsql-cygwin(at)postgresql(dot)org instead of
sending private email, so that other can benefit too.

On Wed, Aug 01, 2001 at 03:20:29PM -0400, Anantha Prasad/NE/USDAFS wrote:
> Hi: Hoping that I am addressing this query to the right person. I have
> installed Postgresql 7.1.2 on Win2k server platform by downloading the
> binary from cygwin mirror. I have dll version 1.3.2 with popt, readline,
> win32api etc.
>
> When I try to do an initdb, the program gets stuck. I enclose below the
> debugging info. Any help will be greatly appreciated.

Did you forget to install cygipc and/or start ipc-daemon? Please read
the README file:

/usr/doc/Cygwin/postgresql-7.1.2.README

Thanks,
Jason

--
Jason Tishler
Director, Software Engineering Phone: 732.264.8770 x235
Dot Hill Systems Corp. Fax: 732.264.8798
82 Bethany Road, Suite 7 Email: jason(at)tishler(dot)net
Hazlet, NJ 07730 USA WWW: http://www.dothill.com

I'm running postgresql-7.1.2 on a Windows 2000 Advanced Server
box. It's the only applicaiton besides a few simple websites running
on the machine.

Cygwin folks: It does work---I can execute queries against it and
am even using some PG/plSQL stored procedures. But it gets slow,
even with regular vacumming, and more importantly, after a while
literally dozens of "postgresql.exe" processes appear in the
Task Manager, bogging down the system until I have to kill all
the processes and restart the database. This takes about a day
to happen, with less than 100 inserts over the course of eight
hours.

Granted, I have more tuning to do in my database and there may
be other bottlenecks between my application and the system. But
for a production enviroment, do I want to install PostGreSQL
as an NT Service? I have some documentation about doing the
same for the ipc-daemon (but it doesn't seem to work).

Right now, I have two batch files in the Startup folder which
call the IPC daemon, delete any .pid files, and start up the
postmaster. But this leaves windows all over the screen and
I am wondering---maybe other inefficencies?

So my question is two fold. 1) Is moving to NT services a
good idea and 2) Where can I learn more about setting this stuff
up?

Thanks,
Robby Slaughter

----- Original Message -----
From: "Robby Slaughter" <webmaster(at)robbyslaughter(dot)com>
Cc: "Pgsql-Cygwin" <pgsql-cygwin(at)postgresql(dot)org>
Sent: Wednesday, August 01, 2001 3:41 PM
Subject: [CYGWIN] Is PostgreSQL as an NT Service the next level?

> Cygwin folks: It does work---I can execute queries against it and
> am even using some PG/plSQL stored procedures. But it gets slow,
> even with regular vacumming, and more importantly, after a while
> literally dozens of "postgresql.exe" processes appear in the
> Task Manager, bogging down the system until I have to kill all
> the processes and restart the database.

As I understand it, there is postgres.exe process per connection. Perhaps
you could reduce the dozens of of processes by doing some connection
pooling. I'm using postgresql as the db for a J2EE app on 2000. My J2EE
platform does connection pooling for me and I never get more than 4
postgres.exe processes.

Also, what kind of "vacuuming" do you do? Just curious.

Russell

>As I understand it, there is postgres.exe process per connection. Perhaps
>you could reduce the dozens of of processes by doing some connection
>pooling. I'm using postgresql as the db for a J2EE app on 2000. My J2EE
>platform does connection pooling for me and I never get more than 4
>postgres.exe processes.

I turned on connection pooling in the Data Source control panel for the
PostgreSQL driver. I'll see if that helps.

>Also, what kind of "vacuuming" do you do? Just curious.

About once a day I open up a psql window and type "vacuum;" at the
prompt. Sometimes more often if the performance is slow. It does seem
to help but my performance is still very low.

Thanks,
Robby

Robby,

On Wed, Aug 01, 2001 at 04:41:50PM -0500, Robby Slaughter wrote:
> Cygwin folks: It does work---I can execute queries against it and
> am even using some PG/plSQL stored procedures. But it gets slow,
> even with regular vacumming, and more importantly, after a while
> literally dozens of "postgresql.exe" processes appear in the
> Task Manager, bogging down the system until I have to kill all
> the processes and restart the database. This takes about a day
> to happen, with less than 100 inserts over the course of eight
> hours.

Hmm... The above sounds a little fishy. I seem to run the regression
tests without any problems and my guess is that it does more than 100
inserts (and other SQL operations) in less that 5 minutes.

Terry Carlin from Great Bridge has been benchmarking PostgreSQL on many
platforms including Cygwin. Hopefully, he will post his results shortly.

> Granted, I have more tuning to do in my database and there may
> be other bottlenecks between my application and the system. But
> for a production enviroment, do I want to install PostGreSQL
> as an NT Service? I have some documentation about doing the
> same for the ipc-daemon (but it doesn't seem to work).
>
> Right now, I have two batch files in the Startup folder which
> call the IPC daemon, delete any .pid files, and start up the
> postmaster. But this leaves windows all over the screen and
> I am wondering---maybe other inefficencies?
>
> So my question is two fold. 1) Is moving to NT services a
> good idea and 2) Where can I learn more about setting this stuff
> up?

I run both ipc-daemon and postmaster as NT services. ipc-daemon can be
installed as follows:

$ ipc-daemon --install-as-service

and postmaster as follows:

$ cygrunsrv --install postmaster --path /usr/bin/postmaster --args "-D /usr/share/postgresql/data -i" --dep ipc-daemon --termsig INT --user postgres --shutdown

Note the following:

1. To get proper postmaster shutdown semantics (i.e., automatic deletion
of postmaster.pid) then you will need to use a Cygwin snapshot from 7/28/01
or later.
2. I have only tested this when running ipc-daemon under the LocalSystem
account and postmaster under my account (i.e., jt) which is also a member
of the local administrators group. YMMV with another account such as
postgres (i.e., a non-privileged account).

See the following:

http://www.neuro.gatech.edu/users/cwilson/cygutils/V1.1/cygipc/
/usr/doc/Cygwin/cygrunsrv.README

for more information on cygipc and cygrunsrv respectively.

Jason

--
Jason Tishler
Director, Software Engineering Phone: 732.264.8770 x235
Dot Hill Systems Corp. Fax: 732.264.8798
82 Bethany Road, Suite 7 Email: jason(at)tishler(dot)net
Hazlet, NJ 07730 USA WWW: http://www.dothill.com

----- Original Message -----
From: "Jason Tishler" <jason(at)tishler(dot)net>

> Note the following:
>
> 1. To get proper postmaster shutdown semantics (i.e., automatic deletion
> of postmaster.pid) then you will need to use a Cygwin snapshot from
7/28/01
> or later.

Jason,

I'm not familiar with the deployment process...Has that snapshot been pushed
out to the Cygwin ftp sites yet, or do we need to get it from the "snapshots
web page"?

Thanks,

Russell

Russell,

On Mon, Aug 06, 2001 at 11:26:17AM -0600, Russell Black wrote:
> I'm not familiar with the deployment process...Has that snapshot been pushed
> out to the Cygwin ftp sites yet, or do we need to get it from the "snapshots
> web page"?

As with any mirroring setup, it usually takes sometime for propagation.
But, the 7/28/01 snapshot should be out and about by now.

I just checked my favorite Cygwin mirror:

ftp://mirrors.rcn.net/mirrors/sources.redhat.com/cygwin/snapshots/

and the 7/28/01 and later snapshots have already arrived.

Jason

--
Jason Tishler
Director, Software Engineering Phone: 732.264.8770 x235
Dot Hill Systems Corp. Fax: 732.264.8798
82 Bethany Road, Suite 7 Email: jason(at)tishler(dot)net
Hazlet, NJ 07730 USA WWW: http://www.dothill.com

I am running a series of standard commercial benchmarks against
PostgreSQL under CYGWIN.

I start out connecting 1 user, then 10, 20, 30, 40, 50, etc until I get
to 100.

Everything is fine until I get to 40 to 50 users. At some point during
this benchmark, the backend processes seem to get out of sync with the
clients.
I use a commercial product called Benchmark Factory. We have used this
product to benchmark PostgreSQL, Oracle, MySQL, and SQL Server. It
connects to the database via ODBC. I know from a year's worth of
experience that the problem is not inside of the Benchmark Factory. This
is what I have observed.

When the number of clients get to about 50 plus or minus a few, the
benchmark runs, but when it starts to wind down, the backends are still
processing, seem to be waiting for some response from the backend that
never comes. I left it go last night and after 12 hours, the benchmark
still had not finish winding down. It normaly takes about 9 min to start
up a cycle, run the benchmark, and wind down.

I did a netstat -a on the server box and got about 50 ports that their
status was ESTABLISHED. When I did the netstat -a on the client box, I
got 50 ports that had a status of ESTABLISHED, but also got 50 ports that
were LISTENING. The client software seems to be waiting on messages from
the backend that never comes. Or maybe the message was sent, but got
lost.

I don't know how to describe the problem any better. Does someone have
any ideas how I might provide some better info?

BTW, Up through 40 users, PostgreSQL under CYGWIN using the TPC-C
benchmark performed very much the same as Linux PostgreSQL on the exact
hardware.

Thanks

Terry Carlin
VP Q/A
Great Bridge