Thread: Re: useability of apache, PHP, Postgres for real business apps
Re: useability of apache, PHP, Postgres for real business apps
From
caldodge@fpcc.net (Calvin Dodge)
Date:
pvissenb@csc.com (Pieter Vissenberg) wrote in message news:<7ab85fc.0106272359.7bbbfbf2@posting.google.com>...
> All,
>
> Does anyone have experience with the use of PostgreSQL in combination
> with Apache and PHP in a real life business environment?
We're using those three for simple workflow docs right now (tracking
tasks and customer installs, as well as comments about those
operations), and planning on using it for customer data entry (once I
figure out the "best" way to translate our Access-based software to a
web-based format).
PHP 4 session management makes it really easy to create and use
session variables, so data filtering information (for example:
selecting only unfinished tasks) persists until the user actually
changes it ("select ALL tasks"), or until the browser is closed.
> > Does anyone have experience with the use of PostgreSQL in combination > > with Apache and PHP in a real life business environment? I've been writing really large applications in PHP/Apache/PostgreSQL for several years now and I find that they're a great combination... Of course we're talking about web based applications and I suppose some things don't lend themselves well to the web based environment though that hasn't stopped me yet :-) Good luck! -Mitch
On Thu, Jun 28, 2001 at 08:54:29AM -0700, Calvin Dodge wrote:
> pvissenb@csc.com (Pieter Vissenberg) wrote in message news:<7ab85fc.0106272359.7bbbfbf2@posting.google.com>...
> > All,
> >
> > Does anyone have experience with the use of PostgreSQL in combination
> > with Apache and PHP in a real life business environment?
I can't seem to find the original message at the moment, so I'll reply
to this one.
We use PostgreSQL/Apache/PHP for our internal bug database, our intranet,
and hopefully soon some shipping products. Works just fine. Everything is
solid for a few years now...
My only issue is that PHP suffers from some of the same managability
issues as perl in that it is very easy for one person to write code that
no other person understands, and not have any compiler or type-safety to
prevent this. I hope to be moving to .jsp soon in order to fix this by
creating a more structured development environment.
--
Adam Haberlach | "Pancakes don't make hardly any juice
adam@newsnipple.com | and bacon makes too much."
http://www.newsnipple.com | -- bug-eyed earl
'88 EX500 '00 >^< | http://youlook.org
On Thu, 28 Jun 2001, Adam Haberlach wrote: > My only issue is that PHP suffers from some of the same managability > issues as perl in that it is very easy for one person to write code that > no other person understands, and not have any compiler or type-safety to > prevent this. I hope to be moving to .jsp soon in order to fix this by > creating a more structured development environment. I hate to start religious war, but the language does not decide readability or maintainability of code. Code must be subject to peer review before put in production, whether it is written in C, Perl, PHP or Java. At one environment I worked, Java/JSP code was full of garbage. Sure, it is _readable_ but definitely not understandable. At a different environment, Perl was used as main language, but it was controlled and reviewed for understandability. -alex
Quoting Alex Pilosov (alex@pilosoft.com): > I hate to start religious war, but the language does not decide > readability or maintainability of code. Code must be subject to peer Hey, I can write FORTRAN in *any* language! :-) -- Paul Tomblin <ptomblin@xcski.com>, not speaking for anybody "You are installing Internet Explorer 4.0. Windows NT needs to overwrite all your system dll's and restart your computer. Note that this application is NOT part of the operating system. Good luck."
Hi, Tell me please, how to switch trigger , when connection to database was lost? Thanks for suggestions. Igor.
On Thursday, June 28, 2001, at 08:54 AM, Calvin Dodge wrote:
> pvissenb@csc.com (Pieter Vissenberg) wrote in message
> news:<7ab85fc.0106272359.7bbbfbf2@posting.google.com>...
>> All,
>> Does anyone have experience with the use of PostgreSQL in combination
>> with Apache and PHP in a real life business environment?
> We're using those three for simple workflow docs right now (tracking
> tasks and customer installs, as well as comments about those
> operations), and planning on using it for customer data entry (once I
> figure out the "best" way to translate our Access-based software to a
> web-based format).
I'm managing over $600K (USD) with this combo per month, across
400+ websites, for one of my clients, on one server, with weekly
data drops, pgp'd credit card transactions, weekly data
submission from 200 locations, etc. etc.... (Yikes. I could be
rich... er... uhm....nevermind. :-) )
> PHP 4 session management makes it really easy to create and use
> session variables, so data filtering information (for example:
> selecting only unfinished tasks) persists until the user actually
> changes it ("select ALL tasks"), or until the browser is closed.
FWIW: We don't use this, because of performance reasons. We
wrote our own session handling. Runs faster, and smoother, at
the cost of more programming hours. (It's a YMMV thing,
depending on server load and cost per programming hour.)
Some tips on using PG/PHP/Apache in a high-dollar business environment:
1. Treat it like any other accounting program. Separate user
entered data from system recorded data. Separate tables.
2. Use transactions. This will save time, money, and headaches.
3. Tune and optimize your code for doing as much as possible in
one pg_exec() statement. Multiple statements should be avoided
whenever possible. (let the db engine do the work, not PHP).
4. Decide upon numeric precision ahead of time (how many
decimals?) and code for that. Always. Without fail.
5. pgdump as frequently as you desire, and copy it off. Not
because it goes down (I had a MySQL-centric programmer ask if
the db had gone down, and was confused for a bit...because pg
*hasn't* gone down.), but because you always want to be able to
isolate a database at given breakpoints.
6. Did I mention having a backup server? It doesn't matter how
good the app is, lightning will nuke the best of 'em.
7. If you're on linux, recompile/adjust your kernel for massive
shm/sem space. Otherwise, you'll run out of backends when apache
needs 'em. the pg_pconnect php function needs a backend for each
apache process. 50 apache processes? 50 postmaster backends.
(One way around this: set your MaxRequest counter low to "rotate
the pool")
8. Investigate AOLserver. Good things coming from that camp on
connection pooling. (which takes care of the above issue)
9. If you're porting old PHP code, get the latest CVS copy or
>=4.0.6 which _should_ have the row grabbing option in pg
functions, which makes life much easier. It's barely documented,
but it works like mysql now.
10. A note about oid/currval/nextval: Ignore my PHP site
comments if you're using a recent version of pg.
11. If you haven't done accounting apps before, hire an
accountant/programmer. Pricey, until you learn the ropes, but
after that, the knowledge is invaluable. Double entry good.
12. Did I mention backups? Even inside of pg? Set up development
only tables, or a dev-only machine. Standard professional
development for monetary issues, but I figured I'd mention it.
13. Watch out for binary math. Many systems use it, so
compensate (and/or cast) accordingly.
14. Watch out for bad data. Never, ever, under any
circumstances, grant the web-server user too many rights ... or,
even worse, put an SQL statement in a GET. Filter, filter,
filter. Then filter again.
15. Force small data fields. (See above).
16. Timestamp every record. And never use "update", just insert
a reversing record. (See #11.)
17. Vaccum often. Daily.
HTH,
-Ronabop
--2D426F70|759328624|00101101010000100110111101110000
ron@opus1.com, 520-326-6109, http://www.opus1.com/ron/
The opinions expressed in this email are not necessarily those
of myself,
my employers, or any of the other little voices in my head.
On Fri, Jun 29, 2001 at 09:51:46AM +0400, some SMTP stream spewed forth: > Hi, > > Tell me please, how to switch trigger , when connection to > database was lost? Please explain what you mean by "switch trigger". Thank you. gh > > Thanks for suggestions. > > Igor.
I think he means "fire a trigger". Pasi GH <grasshacker@over-yonder.net> kirjoitti viestiss�:20010629011236.A86961@over-yonder.net... > On Fri, Jun 29, 2001 at 09:51:46AM +0400, some SMTP stream spewed forth: > > Hi, > > > > Tell me please, how to switch trigger , when connection to > > database was lost? > > Please explain what you mean by "switch trigger". > Thank you. > > gh > > > > > Thanks for suggestions. > > > > Igor. > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html
right = how to fire a trigger when connection was dropped. (sorry for my poor English) PS> I think he means "fire a trigger". PS> Pasi >> >> > >> > Thanks for suggestions. >> > >> > Igor.
<snip a whole lotta stuff> > 9. If you're porting old PHP code, get the latest CVS copy or > >=4.0.6 which _should_ have the row grabbing option in pg > functions, which makes life much easier. It's barely documented, > but it works like mysql now. > http://www.postgresql.org/search.mpl which function is that ? just got latest cvs copy and couldn't find it -
On Friday 29 June 2001 02:01, Ron Chmara wrote: > Some tips on using PG/PHP/Apache in a high-dollar business environment: > 7. If you're on linux, recompile/adjust your kernel for massive > shm/sem space. Otherwise, you'll run out of backends when apache > needs 'em. the pg_pconnect php function needs a backend for each > apache process. 50 apache processes? 50 postmaster backends. > (One way around this: set your MaxRequest counter low to "rotate > the pool") What if you have several databases? Does the maximum number of connections need to be the mximum number of httpd processes TIMES the number of databases? ouch. If so, does Linux's way of dealing with multiple processes using the same code mean that even if you do have hundreds of simultaneous DB connections, it won't necessarily use an exorbitant amount of memory? > 14. Watch out for bad data. Never, ever, under any > circumstances, grant the web-server user too many rights ... or, I'm wondering if I'm making that mistake. What rights should it have? Seems like it needs to be able to do pretty much anything with tables -- insert, update, delete, etc. -- Like to travel? http://TravTalk.org Micah Yoder Internet Development http://yoderdev.com
On Fri, Jun 29, 2001 at 03:15:15PM -0400, some SMTP stream spewed forth: > On Friday 29 June 2001 02:01, Ron Chmara wrote: > > > Some tips on using PG/PHP/Apache in a high-dollar business environment: > > > 7. If you're on linux, recompile/adjust your kernel for massive > > shm/sem space. Otherwise, you'll run out of backends when apache > > needs 'em. the pg_pconnect php function needs a backend for each > > apache process. 50 apache processes? 50 postmaster backends. > > (One way around this: set your MaxRequest counter low to "rotate > > the pool") > > What if you have several databases? Does the maximum number of connections > need to be the mximum number of httpd processes TIMES the number of > databases? ouch. Not exactly. Pretend the following: You have 3 database users (usernames) using persistent connections. In the simplest case, you need 3 PostgreSQL backends to support one each of those users. That means you need 3 backends per httpd process. 10 concurrent httpd clients => 30 PostgreSQL backends. Yep, ouch. In an extreme case, you might want to check out AOLServer for its pooling stuff. Technically, you only need enough ram to support the maximum number of concurrent backends -- 10 httpd process -> 10 backends, because (in most cases) you will have one user connecting from each httpd. There would be some severe overhead when excavating them from swap, though. > If so, does Linux's way of dealing with multiple processes using the same > code mean that even if you do have hundreds of simultaneous DB connections, > it won't necessarily use an exorbitant amount of memory? gh
> Not exactly.
> Pretend the following:
> You have 3 database users (usernames) using persistent connections.
> In the simplest case, you need 3 PostgreSQL backends to support one each
> of those users. That means you need 3 backends per httpd process.
> 10 concurrent httpd clients => 30 PostgreSQL backends.
Well I only need to connect from one database user -- nobody. But my
pg_pconnect string specifies the database to use -- pg_pconnect("dbname=otg
host=localhost port=5432");
And there are (or will be) other databases on the system, but all will
probably connect from nobody. Does pconnect use the same connection for
different databases if it is from the same user?
> Yep, ouch. In an extreme case, you might want to check out AOLServer for
> its pooling stuff. Technically, you only need enough ram to support the
Might not be a bad idea. Does it support PHP and virtual hosts and all that
as well as Apache?
--
Like to travel? http://TravTalk.org
Micah Yoder Internet Development http://yoderdev.com
On Fri, Jun 29, 2001 at 05:26:08PM -0400, some SMTP stream spewed forth:
> > Not exactly.
> > Pretend the following:
> > You have 3 database users (usernames) using persistent connections.
> > In the simplest case, you need 3 PostgreSQL backends to support one each
> > of those users. That means you need 3 backends per httpd process.
> > 10 concurrent httpd clients => 30 PostgreSQL backends.
>
> Well I only need to connect from one database user -- nobody. But my
> pg_pconnect string specifies the database to use -- pg_pconnect("dbname=otg
> host=localhost port=5432");
>
> And there are (or will be) other databases on the system, but all will
> probably connect from nobody. Does pconnect use the same connection for
> different databases if it is from the same user?
I apologize for not being clear, entirely my fault:
No, each user/database pair uses a connection. Same user, different
database -> another connection.
> > Yep, ouch. In an extreme case, you might want to check out AOLServer for
> > its pooling stuff. Technically, you only need enough ram to support the
>
> Might not be a bad idea. Does it support PHP and virtual hosts and all that
> as well as Apache?
I have no idea. I have never used it in production. AOLServer has
native TCL support, which is kind of cool if you are into that sort
of thing. It is threaded, so reliability is on PHP's side of the fence.
Last I heard, (long time ago, by the way) PHP's threading worked, but was
not 100%. The PHP people probably have that all sorted out by now, so you
should be fine.
It does support virtual host and PHP, but it is threaded, so a comparison
to Apache would be a bit odd. Everything I have heard claims that
AOLServer is hella-fast and efficient. It is definitely worth taking a
weekend to checkout (which, unfortunately, is where my experience with it
ends). If nothing else, the connection pooling should make up for any
other speed issues.
gh
> --
> Like to travel? http://TravTalk.org
> Micah Yoder Internet Development http://yoderdev.com
>
On Fri, Jun 29, 2001 at 08:17:01PM -0500, some SMTP stream spewed forth: > On Fri, Jun 29, 2001 at 05:26:08PM -0400, some SMTP stream spewed forth: > > It does support virtual hosts. I apologize, on a second look at the docs this appears to be untrue. I suggest you look into it yourself or ask someone who is more familiar. I cannot trust myself to translate the docs to you correctly. gh
GH <grasshacker@over-yonder.net> writes: > I have no idea. I have never used it in production. AOLServer has > native TCL support, which is kind of cool if you are into that sort > of thing. It is threaded, so reliability is on PHP's side of the fence. > Last I heard, (long time ago, by the way) PHP's threading worked, but was > not 100%. The PHP people probably have that all sorted out by now, so you > should be fine. As a data point, I found that AOLServer wasn't too terribly stable for me. I was using OpenACS (a big complicated database-intensive app written in TCL) and every couple of days one of the AOLServer instances would go nuts and suck up all the memory in the system, which would in turn kill a random Postgres backend the next time it tried to allocate memory for a checkpoint or whatever. Restarting AOLServer from a cronjob every night solved the problem, but it left a bad taste in my mouth. This was five or six months ago so things may be better now. It might also be fine with a less complicated app. -Doug -- The rain man gave me two cures; he said jump right in, The first was Texas medicine--the second was just railroad gin, And like a fool I mixed them, and it strangled up my mind, Now people just get uglier, and I got no sense of time... --Dylan