Обсуждение: Postgre SQL 7.1 cygwin performance issue.

Hi,

We are using PostgreSQL 7.1 cygwin installed on Windows 2000 (2 GB Memory,
P4).

We understand that the maximum connections that can be set is 64 in
Postgresql 7.1 version.

The performance is very slow and some time the database is not getting
connected from our application because of this.

Please advise us on how to increase the performance by setting any
attributes in configuration files ?.

Find enclosed the configuration file.

Thanks and regards,
Ravi


To post a message to the mailing list, send it to
  pgsql-performance@postgresql.org


-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]
Sent: Tuesday, August 22, 2006 5:32 PM
To: ravig3
Subject: 7E88-5CD9-AD0E : CONFIRM from pgsql-performance (subscribe)


__
The following request

  "subscribe pgsql-performance ravig3 <ravindran_g@hcl.in>"

was sent to
by ravig3 <ravindran_g@hcl.in>.

To accept or reject this request, please do one of the following:

1. If you have web browsing capability, visit

<http://mail.postgresql.org/mj/mj_confirm/domain=postgresql.org?t=7E88-5CD9-
AD0E>
   and follow the instructions there.

2. Reply to majordomo@postgresql.org
   with one of the following two commands in the body of the message:

    accept
    reject

   (The number 7E88-5CD9-AD0E must be in the Subject header)

3. Reply to majordomo@postgresql.org
   with one of the following two commands in the body of the message:

    accept 7E88-5CD9-AD0E
    reject 7E88-5CD9-AD0E

Your confirmation is required for the following reason(s):

  The subscribe_policy rule says that the "subscribe" command
  must be confirmed by the person affected by the command.


If you do not respond within 4 days, a reminder will be sent.

If you do not respond within 7 days, this token will expire,
and the request will not be completed.

If you would like to communicate with a person,
send mail to pgsql-performance-owner@postgresql.org.
DISCLAIMER
The contents of this e-mail and any attachment(s) are confidential and intended for the

named recipient(s) only. It shall not attach any liability on the originator or HCL or its

affiliates. Any views or opinions presented in this email are solely those of the author and

may not necessarily reflect the opinions of HCL or its affiliates. Any form of reproduction,

dissemination, copying, disclosure, modification, distribution and / or publication of this

message without the prior written consent of the author of this e-mail is strictly

prohibited. If you have received this email in error please delete it and notify the sender

immediately. Before opening any mail and attachments please check them for viruses and

defect.

"Ravindran G - TLS, Chennai." <ravindran_g@hcl.in> writes:
> We are using PostgreSQL 7.1 cygwin installed on Windows 2000 (2 GB Memory,
> P4).

Egad :-(

If you are worried about performance, get off 7.1.  Even if you are not
worried about performance, get off 7.1.  It *will* eat your data someday.

A native Windows build of PG 8.1 will blow the doors off 7.1/cygwin as
to both performance and reliability.

I know little about Windows versions, but I suspect people will tell you
that a newer version of Windows would be a good idea too.

            regards, tom lane

I would like to talk to one of the org member in postgre about this issue.
This is critical for us. Please help.

It will be great, if you could provide your contact number to discuss on
this.

Thank you in advance.

Regards, Ravi

-----Original Message-----
From: Ravindran G - TLS, Chennai.
Sent: Tuesday, August 22, 2006 6:09 PM
To: 'pgsql-performance@postgresql.org'
Subject: Postgre SQL 7.1 cygwin performance issue.
Importance: High


Hi,

We are using PostgreSQL 7.1 cygwin installed on Windows 2000 (2 GB Memory,
P4).

We understand that the maximum connections that can be set is 64 in
Postgresql 7.1 version.

The performance is very slow and some time the database is not getting
connected from our application because of this.

Please advise us on how to increase the performance by setting any
attributes in configuration files ?.

Find enclosed the configuration file.

Thanks and regards,
Ravi


To post a message to the mailing list, send it to
  pgsql-performance@postgresql.org


-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]
Sent: Tuesday, August 22, 2006 5:32 PM
To: ravig3
Subject: 7E88-5CD9-AD0E : CONFIRM from pgsql-performance (subscribe)


__
The following request

  "subscribe pgsql-performance ravig3 <ravindran_g@hcl.in>"

was sent to
by ravig3 <ravindran_g@hcl.in>.

To accept or reject this request, please do one of the following:

1. If you have web browsing capability, visit

<http://mail.postgresql.org/mj/mj_confirm/domain=postgresql.org?t=7E88-5CD9-
AD0E>
   and follow the instructions there.

2. Reply to majordomo@postgresql.org
   with one of the following two commands in the body of the message:

    accept
    reject

   (The number 7E88-5CD9-AD0E must be in the Subject header)

3. Reply to majordomo@postgresql.org
   with one of the following two commands in the body of the message:

    accept 7E88-5CD9-AD0E
    reject 7E88-5CD9-AD0E

Your confirmation is required for the following reason(s):

  The subscribe_policy rule says that the "subscribe" command
  must be confirmed by the person affected by the command.


If you do not respond within 4 days, a reminder will be sent.

If you do not respond within 7 days, this token will expire,
and the request will not be completed.

If you would like to communicate with a person,
send mail to pgsql-performance-owner@postgresql.org.
DISCLAIMER
The contents of this e-mail and any attachment(s) are confidential and intended for the

named recipient(s) only. It shall not attach any liability on the originator or HCL or its

affiliates. Any views or opinions presented in this email are solely those of the author and

may not necessarily reflect the opinions of HCL or its affiliates. Any form of reproduction,

dissemination, copying, disclosure, modification, distribution and / or publication of this

message without the prior written consent of the author of this e-mail is strictly

prohibited. If you have received this email in error please delete it and notify the sender

immediately. Before opening any mail and attachments please check them for viruses and

defect.

Ravindran G - TLS, Chennai. wrote:
> I would like to talk to one of the org member in postgre about this issue.
> This is critical for us. Please help.
>
> It will be great, if you could provide your contact number to discuss on
> this.

Sure, we're happy to help.  You can contact several "org members" via
this mailing list.  What's your problem exactly?  If you're finding that
the 7.1 cygwin version is too slow, please consider migrating some
something more recent.  8.1 runs natively on Windows, no Cygwin required.
It's much faster and doesn't have that limitation on the number of
connections.

Please note that the name is "PostgreSQL" and is usually shortened to
"Postgres".  It's never "postgre".

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Thanks Alvaro.

We are using PostgreSQL 7.1 cygwin installed on Windows 2000.

We understand that the maximum connections that can be set is 64 in
Postgresql 7.1 version.

But our application is installed in 8 / 10 PC or more than that and it opens
multiple connections and it exceeds 64.

Because of this the subsequent connections are failed to connect with DB
from application.

Please advise us on how to resolve this ?.

--

Migrating to 8.1 may not be possible at this point of time due to some
reasons.

Regards, Ravi


-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@commandprompt.com]
Sent: Monday, August 28, 2006 7:02 PM
To: Ravindran G - TLS, Chennai.
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Postgre SQL 7.1 cygwin performance issue.


Ravindran G - TLS, Chennai. wrote:
> I would like to talk to one of the org member in postgre about this issue.
> This is critical for us. Please help.
>
> It will be great, if you could provide your contact number to discuss on
> this.

Sure, we're happy to help.  You can contact several "org members" via
this mailing list.  What's your problem exactly?  If you're finding that
the 7.1 cygwin version is too slow, please consider migrating some
something more recent.  8.1 runs natively on Windows, no Cygwin required.
It's much faster and doesn't have that limitation on the number of
connections.

Please note that the name is "PostgreSQL" and is usually shortened to
"Postgres".  It's never "postgre".

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
DISCLAIMER
The contents of this e-mail and any attachment(s) are confidential and intended for the

named recipient(s) only. It shall not attach any liability on the originator or HCL or its

affiliates. Any views or opinions presented in this email are solely those of the author and

may not necessarily reflect the opinions of HCL or its affiliates. Any form of reproduction,

dissemination, copying, disclosure, modification, distribution and / or publication of this

message without the prior written consent of the author of this e-mail is strictly

prohibited. If you have received this email in error please delete it and notify the sender

immediately. Before opening any mail and attachments please check them for viruses and

defect.

On 8/28/06, Ravindran G - TLS, Chennai. <ravindran_g@hcl.in> wrote:
> Thanks Alvaro.
>
> We are using PostgreSQL 7.1 cygwin installed on Windows 2000.
>
> We understand that the maximum connections that can be set is 64 in
> Postgresql 7.1 version.
>
> But our application is installed in 8 / 10 PC or more than that and it opens
> multiple connections and it exceeds 64.
>
> Because of this the subsequent connections are failed to connect with DB
> from application.
>
> Please advise us on how to resolve this ?.

I don't think you have any answer other than to migrate to a
better-supportable version of PostgreSQL.

The last release of 7.1 was in August 2001; you're using a version
that is now over five years old, with known "it'll eat your data"
problems.  That is why there have been some fifty-odd subsequent
releases.

The right answer is to arrange for an upgrade to a much less antiquated version.

You're going to be pretty well restricted to the 64 connections until
you upgrade to a more recent version.

There is an alternative:  You could migrate to some Unix-like platform
(such as Linux or FreeBSD) where version 7.1.3 could in fact support
more than 64 connections.
--
http://www3.sympatico.ca/cbbrowne/linux.html
Oddly enough, this is completely standard behaviour for shells. This
is a roundabout way of saying `don't use combined chains of `&&'s and
`||'s unless you think Gödel's theorem is for sissies'.

am  Mon, dem 28.08.2006, um 19:30:44 +0530 mailte Ravindran G - TLS, Chennai. folgendes:
> Thanks Alvaro.
>
> We are using PostgreSQL 7.1 cygwin installed on Windows 2000.

*grrr*

>
> We understand that the maximum connections that can be set is 64 in
> Postgresql 7.1 version.
>
> But our application is installed in 8 / 10 PC or more than that and it opens
> multiple connections and it exceeds 64.
>
> Because of this the subsequent connections are failed to connect with DB
> from application.
>
> Please advise us on how to resolve this ?.

I'm not sure, but perhaps, pgpool can solve your problem.

>
> --
>
> Migrating to 8.1 may not be possible at this point of time due to some
> reasons.

Pity. 8.1 is *very* nice, and 7.1 *very* old, slow and out of
life-cycle.



>
> Regards, Ravi
>
>
> -----Original Message-----

Please, no top-posting with fullquote below.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Ravindran G - TLS, Chennai. wrote:
> Thanks Alvaro.
>
> We are using PostgreSQL 7.1 cygwin installed on Windows 2000.
>
> We understand that the maximum connections that can be set is 64 in
> Postgresql 7.1 version.

This is because of Cygwin limitations.

> But our application is installed in 8 / 10 PC or more than that and it opens
> multiple connections and it exceeds 64.
>
> Because of this the subsequent connections are failed to connect with DB
> from application.
>
> Please advise us on how to resolve this ?.

There's no solution short of upgrading.

> Migrating to 8.1 may not be possible at this point of time due to some
> reasons.

That's too bad :-(

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On 8/28/06, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> > Please advise us on how to resolve this ?.
>
> There's no solution short of upgrading.

That's a little too negative.  There is at least one alternative,
possibly two...

1.  Migrate the database to a Unix platform that does not suffer from
the Cygwin 64 connection restriction.  (If running Linux, it may be
necessary to look for an old release, as there were changes to GLIBC
at around the same time as 7.2 that don't play perfectly well with
7.1...)

2.  It is *possible* that pg_pool could be usable as a proxy that
limits the number of connections actually used.  I'm not sure how well
it'll play with 7.1, mind you...
--
http://www3.sympatico.ca/cbbrowne/linux.html
Oddly enough, this is completely standard behaviour for shells. This
is a roundabout way of saying `don't use combined chains of `&&'s and
`||'s unless you think Gödel's theorem is for sissies'.

On Mon, 2006-08-28 at 09:00, Ravindran G - TLS, Chennai. wrote:
> Thanks Alvaro.
>
> We are using PostgreSQL 7.1 cygwin installed on Windows 2000.
>
> We understand that the maximum connections that can be set is 64 in
> Postgresql 7.1 version.
>
> But our application is installed in 8 / 10 PC or more than that and it opens
> multiple connections and it exceeds 64.
>
> Because of this the subsequent connections are failed to connect with DB
> from application.
>
> Please advise us on how to resolve this ?.

As someone else mentioned, pg_pool might help here.  But you're kind of
fighting an uphill battle here.  I'm guessing that your effort will be
better spent on upgrading your db server than on trying to patch up the
system you have.

Is there any chance of changing your client app so it doesn't open so
many connections?  That would seem the easiest fix of all, if you have
access to that code.

Ravindran G - TLS, Chennai. wrote:
> Hi,
>
> We are using PostgreSQL 7.1 cygwin installed on Windows 2000 (2 GB Memory,
> P4).
>

I would strongly suggest moving to native 8.1 :). You will find your
life much better.


Joshua D. Drake


> We understand that the maximum connections that can be set is 64 in
> Postgresql 7.1 version.
>
> The performance is very slow and some time the database is not getting
> connected from our application because of this.
>
> Please advise us on how to increase the performance by setting any
> attributes in configuration files ?.
>
> Find enclosed the configuration file.
>
> Thanks and regards,
> Ravi
>
>
> To post a message to the mailing list, send it to
>   pgsql-performance@postgresql.org
>
>
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org]
> Sent: Tuesday, August 22, 2006 5:32 PM
> To: ravig3
> Subject: 7E88-5CD9-AD0E : CONFIRM from pgsql-performance (subscribe)
>
>
> __
> The following request
>
>   "subscribe pgsql-performance ravig3 <ravindran_g@hcl.in>"
>
> was sent to
> by ravig3 <ravindran_g@hcl.in>.
>
> To accept or reject this request, please do one of the following:
>
> 1. If you have web browsing capability, visit
>
> <http://mail.postgresql.org/mj/mj_confirm/domain=postgresql.org?t=7E88-5CD9-
> AD0E>
>    and follow the instructions there.
>
> 2. Reply to majordomo@postgresql.org
>    with one of the following two commands in the body of the message:
>
>     accept
>     reject
>
>    (The number 7E88-5CD9-AD0E must be in the Subject header)
>
> 3. Reply to majordomo@postgresql.org
>    with one of the following two commands in the body of the message:
>
>     accept 7E88-5CD9-AD0E
>     reject 7E88-5CD9-AD0E
>
> Your confirmation is required for the following reason(s):
>
>   The subscribe_policy rule says that the "subscribe" command
>   must be confirmed by the person affected by the command.
>
>
> If you do not respond within 4 days, a reminder will be sent.
>
> If you do not respond within 7 days, this token will expire,
> and the request will not be completed.
>
> If you would like to communicate with a person,
> send mail to pgsql-performance-owner@postgresql.org.
> DISCLAIMER
> The contents of this e-mail and any attachment(s) are confidential and intended for the
>
> named recipient(s) only. It shall not attach any liability on the originator or HCL or its
>
> affiliates. Any views or opinions presented in this email are solely those of the author and
>
> may not necessarily reflect the opinions of HCL or its affiliates. Any form of reproduction,
>
> dissemination, copying, disclosure, modification, distribution and / or publication of this
>
> message without the prior written consent of the author of this e-mail is strictly
>
> prohibited. If you have received this email in error please delete it and notify the sender
>
> immediately. Before opening any mail and attachments please check them for viruses and
>
> defect.
>
>
> ------------------------------------------------------------------------
>
> #
> # PostgreSQL configuration file
> # -----------------------------
> #
> # This file consists of lines of the form
> #
> #   name = value
> #
> # (The `=' is optional.) White space is collapsed, comments are
> # introduced by `#' anywhere on a line.  The complete list of option
> # names and allowed values can be found in the PostgreSQL
> # documentation.  The commented-out settings shown in this file
> # represent the default values.
>
> # Any option can also be given as a command line switch to the
> # postmaster, e.g., 'postmaster -c log_connections=on'. Some options
> # can be changed at run-time with the 'SET' SQL command.
>
>
> #========================================================================
>
>
> #
> #    Connection Parameters
> #
> tcpip_socket = true
> #ssl = false
>
> max_connections = 64
>
> #port = 5432
> #hostname_lookup = false
> #show_source_port = false
>
> #unix_socket_directory = ''
> #unix_socket_group = ''
> #unix_socket_permissions = 0777
>
> #virtual_host = ''
>
> #krb_server_keyfile = ''
>
>
> #
> #    Shared Memory Size
> #
> shared_buffers = 20000        # 2*max_connections, min 16
> #max_fsm_relations = 100    # min 10, fsm is free space map
> max_fsm_pages = 20000      # min 1000, fsm is free space map
> #max_locks_per_transaction = 64 # min 10
> #wal_buffers = 8            # min 4
>
> #
> #    Non-shared Memory Sizes
> #
> #sort_mem = 512             # min 32
> #vacuum_mem = 8192          # min 1024
>
>
> #
> #    Write-ahead log (WAL)
> #
> #wal_files = 0 # range 0-64
> wal_sync_method = open_sync   # the default varies across platforms:
> #               # fsync, fdatasync, open_sync, or open_datasync
> #wal_debug = 0             # range 0-16
> #commit_delay = 0          # range 0-100000
> #commit_siblings = 5       # range 1-1000
> #checkpoint_segments = 3   # in logfile segments (16MB each), min 1
> #checkpoint_timeout = 300  # in seconds, range 30-3600
> #fsync = true
>
>
> #
> #    Optimizer Parameters
> #
> #enable_seqscan = true
> #enable_indexscan = true
> #enable_tidscan = true
> #enable_sort = true
> #enable_nestloop = true
> #enable_mergejoin = true
> #enable_hashjoin = true
>
> #ksqo = false
>
> effective_cache_size = 5000  # default in 8k pages
> #random_page_cost = 4
> #cpu_tuple_cost = 0.01
> #cpu_index_tuple_cost = 0.001
> #cpu_operator_cost = 0.0025
>
>
> #
> #    GEQO Optimizer Parameters
> #
> #geqo = true
> #geqo_selection_bias = 2.0 # range 1.5-2.0
> #geqo_threshold = 11
> #geqo_pool_size = 0        # default based on #tables in query, range 128-1024
> #geqo_effort = 1
> #geqo_generations = 0
> #geqo_random_seed = -1     # auto-compute seed
>
>
> #
> #    Debug display
> #
> #silent_mode = false
>
> log_connections = true
> log_timestamp = true
> #log_pid = false
>
> #debug_level = 0 # range 0-16
>
> debug_print_query = true
> #debug_print_parse = false
> #debug_print_rewritten = false
> #debug_print_plan = false
> #debug_pretty_print = false
>
> # requires USE_ASSERT_CHECKING
> #debug_assertions = true
>
>
> #
> #    Syslog
> #
> # requires ENABLE_SYSLOG
> #syslog = 0 # range 0-2
> #syslog_facility = 'LOCAL0'
> #syslog_ident = 'postgres'
>
>
> #
> #    Statistics
> #
> #show_parser_stats = false
> #show_planner_stats = false
> #show_executor_stats = false
> #show_query_stats = false
>
> # requires BTREE_BUILD_STATS
> #show_btree_build_stats = false
>
>
> #
> #    Access statistics collection
> #
> #stats_start_collector = true
> #stats_reset_on_server_start = true
> #stats_command_string = false
> #stats_row_level = false
> #stats_block_level = false
>
>
> #
> #    Lock Tracing
> #
> #trace_notify = false
>
> # requires LOCK_DEBUG
> #trace_locks = false
> #trace_userlocks = false
> #trace_lwlocks = false
> #debug_deadlocks = false
> #trace_lock_oidmin = 16384
> #trace_lock_table = 0
>
>
> #
> #    Misc
> #
> #dynamic_library_path = '$libdir'
> #australian_timezones = false
> #authentication_timeout = 60    # min 1, max 600
> #deadlock_timeout = 1000
> #default_transaction_isolation = 'read committed'
> #max_expr_depth = 10000         # min 10
> #max_files_per_process = 1000   # min 25
> #password_encryption = false
> #sql_inheritance = true
> #transform_null_equals = false
>
>
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend


--

    === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
    Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

"Christopher Browne" <cbbrowne@gmail.com> writes:
> On 8/28/06, Alvaro Herrera <alvherre@commandprompt.com> wrote:
>> There's no solution short of upgrading.

> That's a little too negative.  There is at least one alternative,
> possibly two...

But both of those would probably involve work comparable to an upgrade.

There is another reason for not encouraging these folk to stay on 7.1
indefinitely, which is that 7.1 still has the transaction ID wraparound
problem.  It *will* --- not might, WILL --- eat their data someday.
Without knowing anything about their transaction rate, I can't say
whether that will happen tomorrow or not for many years, but insisting
on staying on 7.1 is a dangerous game.

            regards, tom lane

On 8/28/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Christopher Browne" <cbbrowne@gmail.com> writes:
> > On 8/28/06, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> >> There's no solution short of upgrading.
>
> > That's a little too negative.  There is at least one alternative,
> > possibly two...
>
> But both of those would probably involve work comparable to an upgrade.

We don't know what is preventing the upgrade; we haven't been told
anything about the details surrounding that.

> There is another reason for not encouraging these folk to stay on 7.1
> indefinitely, which is that 7.1 still has the transaction ID wraparound
> problem.  It *will* --- not might, WILL --- eat their data someday.
> Without knowing anything about their transaction rate, I can't say
> whether that will happen tomorrow or not for many years, but insisting
> on staying on 7.1 is a dangerous game.

Fair enough.  I would only suggest these workarounds as a way of
getting a bit of temporary "breathing room" before doing the upgrade.

These should at best be considered temporary workarounds, because
there are around 50 releases that have been made since 7.1.3.  All but
a handful of those releases (namely 7.2.0, 7.3.0, 7.4.0, 8.0.0, and
8.1.0) were created because of discovering "eat your data" problems of
one variety or another.
--
http://www3.sympatico.ca/cbbrowne/linux.html
Oddly enough, this is completely standard behaviour for shells. This
is a roundabout way of saying `don't use combined chains of `&&'s and
`||'s unless you think Gödel's theorem is for sissies'.

On 8/28/06, Christopher Browne <cbbrowne@gmail.com> wrote:
> On 8/28/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > "Christopher Browne" <cbbrowne@gmail.com> writes:
> > > On 8/28/06, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> > >> There's no solution short of upgrading.
> >
> > > That's a little too negative.  There is at least one alternative,
> > > possibly two...
> >
> > But both of those would probably involve work comparable to an upgrade.
>
> We don't know what is preventing the upgrade; we haven't been told
> anything about the details surrounding that.

be sure and check out
http://archives.postgresql.org/pgsql-hackers/2006-08/msg00655.php.
(read the entire thread)  moving off 7.1 is a great idea, but it may
or may not solve the connection the problem (its windows) :).

merlin