I don't think I can volunteer on this end as I am
already actively volunteering for another open
project. I was hoping someone could take up on this
since one of the last threads mentionned we don't have
something substantial to present for 7.5 if June 1 is
dateline for code freeze. Pre-fork came
sdv mailer said:
Forking is expensive on many systems. Linux is a bit
better but still expensive compared to threads. On
Windows, creating process is much more expensive than
on Linux. Check this benchmark:
http://cs.nmu.edu/~randy/Research/Papers/Scheduler/understanding.html
Forking
On 4-May-04, at 2:18 PM, David Fetter wrote:
I'd like to be able to run large batch-process jobs for caching, but
limit the total resource utilization on them, something like nice(1).
Better still would be some way to put a max on total resources be
able to allocate from that.
Does PostgreSQL
Thanks for your real-time respone!
the problem was sloved after I upgrade the postgreSQL from 7.3.4 to 7.4.2.
by the way, is there any bug-tracking website for postgreSQL ?
I follow the [HOMEPAGE] - [DEVELOPERS] -find nothing relative to
bugzilla-like items,
follow the [GBROG] - it's
sdv mailer wrote:
We used to run persistent connection until the DB
servers got maxed out because of too many idle
connections sucking up all the memory. Web servers run
different loads than database servers and persistent
connections are notorious for crashing your DB.
And this translates
On Tue, May 04, 2004 at 11:21:18PM -0400, Bruce Momjian wrote:
Alvaro Herrera wrote:
I've whacked the subtrans patch enough so that the simple tests (i.e.
non concurrent) for tuple visibility work. I can create a table and
populate it in subtransactions, rollback or commit them
And preforking makes this different, how ? Perhaps having a pool of
processes ready to be handed a query to a specific database, where you
configure N connections to db1, M to db2 etc. still means lots of resource
usage. In effect a preforked database server *is* an idle connection, just
Christopher Kings-Lynne [EMAIL PROTECTED] writes:
Yup.. And some of us intend on wrapping every single statement in a
subtransaction so we can rollback on an error without aborting the main
transaction.
Point there being main transaction. What i'm saying is that the vast
majority of
TODO items completed:
o -ALTER TABLE ADD COLUMN does not honor DEFAULT and non-CHECK CONSTRAINT
o -ALTER TABLE ADD COLUMN column DEFAULT should fill existing
rows with DEFAULT value
o -Allow ALTER TABLE to modify column lengths and change to binary
Fabien COELHO [EMAIL PROTECTED] writes:
nspacl = aclitems_switch_grantor(nspacl, datdba)
Instead of having a hard coded list of template1 objects that need to be
chowned to the database owner. Perhaps there should be a special user like
dbowner which owns the schema and whatever other
Is there some solution whereby the common case (99.999% of transactions
won't be subtransactoins) is fast, and the uncommon case of being in a
subtransaction is slower?
I hope not, because for many of us there will be as many (if not more)
subtransactions than standard transactions.
--
Rod
* Bruce Momjian ([EMAIL PROTECTED]) wrote:
Do we still want this TODO?
o Allow columns to be reordered using ALTER ... POSITION i col1 [,col2];
have SELECT * and INSERT honor such ordering
I don't think so. As I remember it was part of doing logical attribute
numbers as
On Mon, May 03, 2004 at 11:59:45PM -0700, sdv mailer wrote:
Connection pooling (eg. SQLRelay) didn't work either
because we needed to connect to hundreds of DB servers
from each web server. Imagine having 200+ open
connections on the web server and how many more of
these connections remain
I wish modify COPY command for support of CSV files (dump/load files in
CSV format).
I think this option is very important from import data from spreedsheet
as OpenOffice/calc or M$/excel.
I have found this task in TODO list, also.
I've begin my work, modify COPY syntax in:
COPY tablename [ (
We run a multithreaded application that uses postgres 7.4 on Linux
2.4.18, dual cpu Xeon processor machine. We have occassional weird
crashes and have tried a lot of things to reproduce them in house, but
in vain. We do have coredumps and I have listed the backtraces and
their common
Hi,
I know the issue of pre-fork PostgreSQL has been discussed previously.
Someone mentionned pre-fork can be implemented when schemas become available
in PostgreSQL because there will be less of the need to run multiple
databases.
I think Oracle 7 uses pre-forking and it helps speed up the
I want to understand how Postgres organizes data and handles IO
operations so that I will better know how to optimize a Postgres
database server. I am looking for answers to specific questions and
pointers to where this stuff is documented.
How does Postgres organize its data? For example, is it
(I'm not on -hackers, but saw this in the archives)
Alvaro Herrera wrote:
On Fri, Apr 30, 2004 at 09:44:52PM -0400, Tom Lane wrote:
Alvaro Herrera alvherre ( at ) dcc ( dot ) uchile ( dot ) cl writes:
Is this expected? If so, why? I'd expect the prepared stmt to be
deallocated.
prepare.c
We noticed that one of our high-volume insert tables was being vacuumed
every time pg_autovacuum woke up. (Im running it with the default
threshold values, and a 900-second sleep cycle.) The table has a few
million rows in it. With debug = 2 on, here's what the pg_autovacuum
log reports for
Hi,
I know the issue of pre-fork PostgreSQL has been
discussed previously.
Someone mentionned pre-fork can be implemented when
schemas become available
in PostgreSQL because there will be less of the need
to run multiple
databases.
I think Oracle 7 uses pre-forking and it helps speed
up the
Stephen Frost [EMAIL PROTECTED] writes:
Does using table recreate break views built against the table?
Right now it just rejects the ALTER attempt:
regression=# create table t1 (f1 int);
CREATE TABLE
regression=# create view v1 as select * from t1;
CREATE VIEW
regression=# alter table t1 alter
Prem Gopalan wrote:
We run a multithreaded application that uses postgres 7.4 on Linux
2.4.18, dual cpu Xeon processor machine. We have occassional weird
crashes and have tried a lot of things to reproduce them in house, but
in vain. We do have coredumps and I have listed the backtraces and
On Wed, 2004-05-05 at 10:36, Bruce Momjian wrote:
TODO items completed:
o -ALTER TABLE ADD COLUMN does not honor DEFAULT and non-CHECK CONSTRAINT
o -ALTER TABLE ADD COLUMN column DEFAULT should fill existing
rows with DEFAULT value
o -Allow ALTER TABLE to
Umberto Zappi wrote:
I wish modify COPY command for support of CSV files (dump/load files in
CSV format).
I think this option is very important from import data from spreedsheet
as OpenOffice/calc or M$/excel.
I have found this task in TODO list, also.
I've begin my work, modify COPY
I know the issue of pre-fork PostgreSQL has been discussed previously.
Someone mentionned pre-fork can be implemented when schemas become available
Any chance of that happening for 7.5?
0 chance unless you have a patch ready now.
---(end of
On Sun, May 02, 2004 at 17:03:35 +0200,
Umberto Zappi [EMAIL PROTECTED] wrote:
I wish modify COPY command for support of CSV files (dump/load files in
CSV format).
I think this option is very important from import data from spreedsheet
as OpenOffice/calc or M$/excel.
I have found this task
It's been done already.
see http://developer.postgresql.org/todo.php and
http://developer.postgresql.org/docs/postgres/sql-copy.html
cheers
andrew
Umberto Zappi wrote:
I wish modify COPY command for support of CSV files (dump/load files in
CSV format).
I think this option is very important from
I'm talking about connecting to multiple database
servers on separate machines. Schemas don't apply
here.
How much work would it take to make a pre-fork smart
enough to open different databases on incoming
connection? How much of it can be modeled after
Apache?
Bruce Momjian wrote:
David Garamond wrote:
scott.marlowe wrote:
For me, the only features I'm likely to use in the upcoming releases are
nested transactions. While PITR is a great selling point, and the Windows
Port is something I do look forward to, having to do half my job
programming
Bruce Momjian wrote:
Do we still want this TODO?
o Allow columns to be reordered using ALTER ... POSITION i col1 [,col2];
have SELECT * and INSERT honor such ordering
I don't think so. As I remember it was part of doing logical attribute
numbers as a way to add ALTER COLUMN
Pre-fork does not equal to idle connections! Pre-fork
scales with database load where as persistent
connections scales with webserver load. A web server
that is heavily loaded but not necessarily performing
a lot of database activity will spawn hundreds of idle
database connections using
Rod Taylor [EMAIL PROTECTED] writes:
Cutting that count down to 10 idlers in total by having PostgreSQL
prefork a specific database would make a significant difference.
Well it would be 10 for each database. Since as has been pointed out before
loading the database is most of the delay.
If
Manfred Koizar [EMAIL PROTECTED] writes:
The straightforward pg_clog lookup is still in transam.c,
but has been deactivated:
* Now this func in shmem.c and gives quality answer by scanning
* PGPROC structures of all running backend. - vadim 11/26/96
What was the motivation for this change?
Stephen Frost wrote:
I don't think so. As I remember it was part of doing logical attribute
numbers as a way to add ALTER COLUMN TYPE, but because we now use table
recreate to implement this, it is unlikely we will ever add logical
attribute numbers (adds too much complexity to the code).
Or, you run several seperate Apache webservers. The ones that serve static
content or don't need database connections don't run with the ones that do.
And just like each idle Apache process uses memory and other resources,
each idle PostgreSQL connection does to. So managing the number of
sdv mailer wrote:
I'm talking about connecting to multiple database
servers on separate machines. Schemas don't apply
here.
How much work would it take to make a pre-fork smart
enough to open different databases on incoming
connection? How much of it can be modeled after
Apache?
I've not used it
On Wed, 2004-05-05 at 11:57, Greg Stark wrote:
Rod Taylor [EMAIL PROTECTED] writes:
Cutting that count down to 10 idlers in total by having PostgreSQL
prefork a specific database would make a significant difference.
Well it would be 10 for each database. Since as has been pointed out
[EMAIL PROTECTED] (Prem Gopalan) writes:
The dying process is postmaster. After these crashes the server is
missing from ps and no more new connections are possible. The backend
processes stay on till their connections close.
That behavior does sound like a postmaster crash --- but all the
Bruce Momjian [EMAIL PROTECTED] writes:
Wow, you can reference different column as part of the alter column.
Yeah, the USING is actually any arbitrary expression over the old table
row. (Getting that to work was a tad tricky...) So you can view this
as a full-table UPDATE operation that folds
sdv mailer wrote:
[snip]
Pre-fork will give MySQL one less argument to throw at
PostgreSQL.
I think optimizing is this area will speed up the
general case for everyone rather than optimizing a
feature that affects 10% of the users. On top of that,
it will make a strong marketing case because
Tom Lane wrote:
select * from another;
f1 | f2
+---
1 | one
2 | two
3 | three
(3 rows)
alter table another
alter f1 type text using f2 || ' more',
alter f2 type bigint using f1 * 10;
select * from another;
f1 | f2
+
one more |
This sounds very much like a memory problem. I would replace all of
the memory with another set of (preferably known good) memory and see
if the problems persist. Also look for other cores that may be
dropped. If there are several, memory is the likely cause. Be aware
that it will likely
On Tue, 04 May 2004 23:21:07 -0400, Tom Lane [EMAIL PROTECTED] wrote:
I thought we had devised a solution that did not require expansible
shared memory for this. Bruce, Manfred, do you recall how that went?
AFAIR we did not discuss TransactionIdIsInProgress() specifically.
Currently this
Jeff Boes wrote:
We noticed that one of our high-volume insert tables was being vacuumed
every time pg_autovacuum woke up. (Im running it with the default
threshold values, and a 900-second sleep cycle.) The table has a few
million rows in it. With debug = 2 on, here's what the pg_autovacuum
Robert Treat [EMAIL PROTECTED] writes:
On Wed, 2004-05-05 at 10:36, Bruce Momjian wrote:
Do we still want this TODO?
o Allow columns to be reordered using ALTER ... POSITION i col1 [,col2];
I think we should leave since it is still functionality that people will
want.
It's not that no one
I am seeing the following failure of initdb in CVS:
The files belonging to this database system will be owned by user postgres.
This user must also own the server process.
The database cluster will be initialized with locale C.
creating
Alvaro Herrera [EMAIL PROTECTED] writes:
(I'd like to avoid the pg_subtrans lookup in the non-subtransaction case,
but I don't see how to do that.)
Could we afford to make xids self-identifying? For instance, odd
numbers are base xacts, even numbers are sub xacts. This would in the
worst case
On Tue, May 04, 2004 at 11:21:07PM -0400, Tom Lane wrote:
Alvaro Herrera [EMAIL PROTECTED] writes:
So, the big question is, how do we do this? The most obvious way (to
me) is to keep the whole array inside the PGPROC struct.
...
The main downside is that it potentially
requires a lot
On Wednesday 05 May 2004 07:24 am, Rod Taylor wrote:
And preforking makes this different, how ? Perhaps having a pool of
processes ready to be handed a query to a specific database, where you
configure N connections to db1, M to db2 etc. still means lots of
resource usage. In effect a
Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] writes:
Wow, you can reference different column as part of the alter column.
Yeah, the USING is actually any arbitrary expression over the old table
row. (Getting that to work was a tad tricky...) So you can view this
as a full-table UPDATE
Hi list,
I'm in the process of porting an existing MS-SQL database to PostgreSQL.
The application uses OLE DB (and that's why I'm writing the OLE DB for
Postgresql). One of the requirements is that we will introduce as little
changes to the application. It has to be able to work with both
Bruce Momjian [EMAIL PROTECTED] writes:
I am seeing the following failure of initdb in CVS:
FATAL: invalid value for parameter client_encoding:
Hmm. Apparently the pg_unsetenv routine I put into initdb.c doesn't
work on your platform. Which is odd, because we've used exactly the
On Wed, 2004-05-05 at 13:48, Bruce Momjian wrote:
I am seeing the following failure of initdb in CVS:
FATAL: invalid value for parameter client_encoding:
I get the same thing.
---(end of broadcast)---
TIP 5: Have you checked our
On Wed, May 05, 2004 at 02:12:58PM -0400, Rod Taylor wrote:
Most of it has been. It's the duty cycle. As stated in another email,
only about 20% of the work a script does is database related -- which
occurs all at one time. Even when all Apache backends are active, a
large number of
Bruce Momjian wrote:
I am seeing the following failure of initdb in CVS:
The files belonging to this database system will be owned by user postgres.
This user must also own the server process.
The database cluster will be initialized with locale C.
On Wed, 5 May 2004, sdv mailer wrote:
Forking is quite fast on Linux but creating a new
process is still 10x more expensive than creating a
thread and is even worse on Win32 platform. CPU load
goes up because the OS needs to allocate/deallocate
memory making it difficult to get a steady
* Steve Atkins ([EMAIL PROTECTED]) wrote:
On Wed, May 05, 2004 at 02:12:58PM -0400, Rod Taylor wrote:
Most of it has been. It's the duty cycle. As stated in another email,
only about 20% of the work a script does is database related -- which
occurs all at one time. Even when all Apache
Bruce Momjian [EMAIL PROTECTED] writes:
I am seeing the following failure of initdb in CVS:
Okay, I did some more work on handling unsetenv cleanly ... give it
another try.
regards, tom lane
---(end of broadcast)---
TIP 7:
The fact that windows has a heavy process / lightweight thread design
means little to me, since I'll likely never deploy a production postgresql
server on it that needs to handle any serious load.
Yes but Solaris also has a heavy process / lightweight thread design.
J
On Wed, 2004-05-05 at 05:30, Rod Taylor wrote:
Yup.. And some of us intend on wrapping every single statement in a
subtransaction so we can rollback on an error without aborting the main
transaction.
That is exactly what is needed to achieve full Oracle DB2
compatibility.
I suggest that
Thanks, works.
---
Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] writes:
I am seeing the following failure of initdb in CVS:
Okay, I did some more work on handling unsetenv cleanly ... give it
another try.
[EMAIL PROTECTED] (James Thornton) writes:
I want to understand how Postgres organizes data and handles IO
operations so that I will better know how to optimize a Postgres
database server. I am looking for answers to specific questions and
pointers to where this stuff is documented.
If you
And, of course, most development environments (perl, php, java etc)
have their own language specific connection pooling solutions.
Yes, the one for php is what I was thinking of when I made my statement.
They work on a per backend basis as Apache does not allow for the type
of communication
On Wed, May 05, 2004 at 02:18:16PM -0400, Tom Lane wrote:
Manfred Koizar [EMAIL PROTECTED] writes:
. We could include a small number of subtransaction xids in PGPROC.
Yeah, I was just thinking that myself. If we only need to show open
subtrans xids, then the number you'd need would
I am wondering if there is a simple way to implement a read() blah and write() blah
function to a database.
The application I'm looking at is very simple and this is all that is required. In
the past I used a cursor - did the select, then retrieved the records one at a time.
Any suggestions?
On Wed, 5 May 2004, Rod Taylor wrote:
And, of course, most development environments (perl, php, java etc)
have their own language specific connection pooling solutions.
Yes, the one for php is what I was thinking of when I made my statement.
They work on a per backend basis as Apache does
I cannot tell if mod_pg_pool works across Apache forked backends or is
still bound to a single process. They state it is intended for sharing
connections across modules, so it is probably still backend specific.
Have you looked at sqlrealy.sourceforge.net? IT looks like it might do
Shachar Shemesh wrote:
MS-SQL has the capacity for both out variables from stored procedures,
as well as running several selects inside the procedures, and then
giving the results for all selects to the caller. Fortunetly for me,
that specific application doesn't run more than one select per
Rod Taylor wrote:
As a temporary step these types of things help. But it's still doesn't
really fix the problem of Apache not using real connection pooling.
Rod,
In principle, this should not be enormously hard to do - at least for
Unix where the methods of handing off file handles between
Alvaro Herrera [EMAIL PROTECTED] writes:
I think if we want to do nontrivial manipulations in PGPROC we should
make sure it's properly locked. Maybe it's a good time to implement the
locking suggested here? With a LWLock instead of a spinlock, of course;
we would need MaxBackends extra
I believe this has already been implemented in CVS...
Chris
Umberto Zappi wrote:
I wish modify COPY command for support of CSV files (dump/load files in
CSV format).
I think this option is very important from import data from spreedsheet
as OpenOffice/calc or M$/excel.
I have found this task in
Andrew Dunstan [EMAIL PROTECTED] writes:
Did we actually find a current system where it broke with a straight
putenv(LC_ALL)?
Well, the Single Unix Spec does not say that that works, and neither
does the HPUX man page for putenv, so you're going to have a hard time
convincing me that it's a
Yeah, the USING is actually any arbitrary expression over the old table
row. (Getting that to work was a tad tricky...) So you can view this
as a full-table UPDATE operation that folds in possible column type changes.
All I can say is three cheers for Tom and Rod on this one
Chris
Tom Lane wrote:
Manfred Koizar [EMAIL PROTECTED] writes:
The straightforward pg_clog lookup is still in transam.c,
but has been deactivated:
* Now this func in shmem.c and gives quality answer by scanning
* PGPROC structures of all running backend. - vadim 11/26/96
What was the
Alvaro Herrera wrote:
On Tue, May 04, 2004 at 11:21:18PM -0400, Bruce Momjian wrote:
Sorry I haven't gotten your patches in yet. Tom is working on some
other back patches.
I've been sloppy lately with #ifdef, because it takes some time to get
right and testing it takes even more time. I
Manfred Koizar wrote:
With subtransactions we'd have to fall back to checking pg_clog (and
pg_subtrans) in certain cases. There are lots of possible
implementations. Here are some ideas (just brainstorming):
. We could first scan the PGPROC array. If the xid is an active main
Tom Lane wrote:
Richard Huxton [EMAIL PROTECTED] writes:
Does that mean I'll want to disable triggers while I do this?
Hrm. Right now the code does not fire triggers at all, but that seems
wrong. However, I doubt that very many triggers could cope with update
events in which the old and
I've already tried pooling (SQLRelay) and persistent
connection (PHP). They may work for other people but
they do not work for me. I have already separated
static from database driven codes but you can never
balance web server load with database server load.
Pre-fork scales with database load and
Forking is expensive on many systems. Linux is a bit
better but still expensive compared to threads. On
Windows, creating process is much more expensive than
on Linux. Check this benchmark:
http://cs.nmu.edu/~randy/Research/Papers/Scheduler/understanding.html
Forking shouldn't be taken lightly
I'll pretend I didn't see that last comment on
Windows. I wouldn't want to disappoint the users who
are eagerly expecting the Win32 port to complete
including myself. ;-)
Having said that, I think it's more the reason to get
a working pre-fork for Win32. Don't you think so?
--- scott.marlowe
80 matches
Mail list logo
