Обсуждение: Management of Concurrent Clients

Hanan Bentaleb schrieb:
> Hi all,
>
>
>
> I am working on an application that involves multiple processes
> accessing and updating different databases: tables are split into 3
> different databases that are accessed by 3 different processes. This
> architectural decision was made in the past because it has been noticed
> that former revisions of postgres used to lock the whole database when a
> process performs an update (on a record) which prevents the other
> processes from accessing the database.

Which former revision should that have been? Was it postgres95
or was it postgresql yet?

> After some readings on postgres concurrent client management, I am
> planning on changing the database structure to make all application
> processes access a shared database (i.e. put all application tables in a
> single database that would be shared among the 3 processes).  I did not
> find in the documentation (at least in the current versions of postgres
> starting from revision 7) any specific problem related to doing this,
> comments on this problem will be appreciated.

Its very unclear what kind of problems you expect. Accessing postgresql
with lots of clients is a common practice. You can also use schemas
to isolate your applications in the same database if you need that.

If you want better advice, give more details on what your application
does and what exact problems you expect.

Regards
Tino

Hi Tino,

Thanks for your response! I did not provide more specific technical
details because I just came in to the project. So, I will try to provide
as much information as I can.

Former versions of postgresql used in this project are postgresql
(starting at 6). I will try to find out the exact revision number.

It has been reported to me that the main problem encountered with former
postgresql versions is that when a process performs an update (of a
record in any table), the whole database was locked which prevents the
other processes from accessing the database (to retrieve data for
instance). In other words, the process performing the update operation
had an exclusive access to the database. This had an important impact on
the system performance and justified the fact that tables were spread
into different databases.

Usually, granularity on locks is at the row or table level but maybe
there was a specific configuration that was used which made it so that
the entire database was locked. Before starting any major database
re-structuring, I want to make sure that are not specific cases where
this situation occurs.

Thanks!
Hanan





-----Original Message-----
From: Tino Wildenhain [mailto:tino@wildenhain.de]
Sent: Sunday, February 26, 2006 12:54 PM
To: Hanan Bentaleb
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Management of Concurrent Clients

Hanan Bentaleb schrieb:
> Hi all,
>
>
>
> I am working on an application that involves multiple processes
> accessing and updating different databases: tables are split into 3
> different databases that are accessed by 3 different processes. This
> architectural decision was made in the past because it has been
noticed
> that former revisions of postgres used to lock the whole database when
a
> process performs an update (on a record) which prevents the other
> processes from accessing the database.

Which former revision should that have been? Was it postgres95
or was it postgresql yet?

> After some readings on postgres concurrent client management, I am
> planning on changing the database structure to make all application
> processes access a shared database (i.e. put all application tables in
a
> single database that would be shared among the 3 processes).  I did
not
> find in the documentation (at least in the current versions of
postgres
> starting from revision 7) any specific problem related to doing this,
> comments on this problem will be appreciated.

Its very unclear what kind of problems you expect. Accessing postgresql
with lots of clients is a common practice. You can also use schemas
to isolate your applications in the same database if you need that.

If you want better advice, give more details on what your application
does and what exact problems you expect.

Regards
Tino

Hanan Bentaleb schrieb:
> Hi Tino,
>
> Thanks for your response! I did not provide more specific technical
> details because I just came in to the project. So, I will try to provide
> as much information as I can.
>
> Former versions of postgresql used in this project are postgresql
> (starting at 6). I will try to find out the exact revision number.
>
> It has been reported to me that the main problem encountered with former
> postgresql versions is that when a process performs an update (of a
> record in any table), the whole database was locked which prevents the
> other processes from accessing the database (to retrieve data for
> instance). In other words, the process performing the update operation
> had an exclusive access to the database. This had an important impact on
> the system performance and justified the fact that tables were spread
> into different databases.
>
> Usually, granularity on locks is at the row or table level but maybe
> there was a specific configuration that was used which made it so that
> the entire database was locked. Before starting any major database
> re-structuring, I want to make sure that are not specific cases where
> this situation occurs.

I wonder how they managed to lock the whole database. You can try
and lock a table but usually its virtually unlocked (thanks to
MVCC). And even 6.x had MVCC. You schould give us an example
what they do. Otherwise it sounds like a myth :)

Regards
Tino

Tino Wildenhain <tino@wildenhain.de> writes:
> Hanan Bentaleb schrieb:
>> It has been reported to me that the main problem encountered with former
>> postgresql versions is that when a process performs an update (of a
>> record in any table), the whole database was locked

> I wonder how they managed to lock the whole database.

I'd believe table-level locks; we used those in *really* old versions of
Postgres.  (According to the release notes, MVCC was added in PG 6.5
released 1999-06-09.)  I don't believe there ever was a facility that
would perform database-level locking at all.

Most PG hackers would call you certifiably insane if you were still
using a pre-MVCC version today.  On data reliability grounds alone,
anything older than 7.2 is simply unsafe because of the XID wraparound
problem (let alone plain old bugs, of which there were many).  If
you check the release history you will notice that 7.2.* was the first
release series that we continued to update after the initial release
of the next series.  This is not coincidental: it reflects community
judgment that 7.2 was the first release series you'd really want to use
for long-term production purposes.

            regards, tom lane