Thread: Re: Re: [GENERAL] PHPBuilder article -- Postgres vs MySQL
>perhaps why, even at 5 clients, the page views he shows never went >significantly above 10/sec? I think alot of it has to do with the web server/db setup not pg. They are using Apache/PHP and looking at their code every page has the additional overhead of making the db connection. Now if they had used AOLserver with its persistent db connecction pooling scheme they may have faired better ;) _________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. Share information about yourself, create your own public profile at http://profiles.msn.com.
carl garland wrote: > > >perhaps why, even at 5 clients, the page views he shows never went > >significantly above 10/sec? > > I think alot of it has to do with the web server/db setup not pg. They are > using Apache/PHP and looking at their code every page has the additional > overhead of making the db connection. Now if they had used AOLserver with > its persistent db connecction pooling scheme they may have faired better ;) PHP has a persistent PostgreSQL open pg_pConnect(....) and it does make a difference. I use postgres as a music database back-end for a PHP web server. (Actually it is a web farm, with many instances of the database, one per web server) The one problem I have had with Postgres is its stubborn refusal to use an index. I understand the reasons why it won't, but it is wrong, so I sped it up by starting the backends with -fs. That may be the issue. On a side note: I'm not sure of the current workings of the vacuum and statistics vs indexing issue, I am new to this list, but I do have a 7.0.2 relevant observation: My music database has 50,000 arises and 210,000 albums. Many artists have only one or 2 entries in the albums table (for the youngsters, CD table ;-). About 34,000 have the integer key for "Various Artists" as their artist entry, and another few thousand have things like "Movie Soundtrack" and so on. When the statistics are computed, these relatively few records with a huge number of relations distort the statistics and make it impossible to get postgres to use an index on that table without the -fs switch. This is bad because it always forces use of an index, even when postgres would legitimately ignore it. -- http://www.mohawksoft.com
On Wed, 15 Nov 2000, carl garland wrote: > >perhaps why, even at 5 clients, the page views he shows never went > >significantly above 10/sec? > > I think alot of it has to do with the web server/db setup not pg. They are > using Apache/PHP and looking at their code every page has the additional > overhead of making the db connection. Now if they had used AOLserver with > its persistent db connecction pooling scheme they may have faired better ;) I doubt it. PostgreSQL has a higher connection startup overhead than MySQL, so if every view required a new database connection, it would been quite a detriment to the PostgreSQL scores. PHP can maintain persisitant connections. Unfortunately, this means that you end up with a database connection per httpd process. That really isn't a problem for PostgreSQL though, it just requires sufficent memory. No doubt that is what was being done. AOLServer isn't the only system that can pool database connections, so can servlets/JSP, ColdFusion, ASP, etc. No doubt AOLServer would be more widely accepted if it used something other than TCL. Tom
At 09:27 AM 11/15/00 -0800, Tom Samplonius wrote: > AOLServer isn't the only system that can pool database connections, so >can servlets/JSP, ColdFusion, ASP, etc. No doubt AOLServer would be more >widely accepted if it used something other than TCL. There are two separate modules that support Java in AOLserver: ns_tomcat which provides an identical interface as Apache tomcat (and no real advantages) and ns_java, which is coming out of the OpenACS project. ns_java exposes AOLserver's pooled, persistent database API to java. There's also support available for Python, though there's still a lot of work to be done to support the full AOLserver API (same's true of ns_java, actually). If you use ADP pages, your use of Tcl is typically restricted to snippets of code anyway, so I've never really understood the complaints about Tcl... - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Andrew McMillan wrote: > mlw wrote: > > > > My music database has 50,000 arises and 210,000 albums. Many artists > > have only one or 2 entries in the albums table (for the youngsters, CD > > table ;-). About 34,000 have the integer key for "Various Artists" as > > their artist entry, and another few thousand have things like "Movie > > Soundtrack" and so on. > > > > When the statistics are computed, these relatively few records with a > > huge number of relations distort the statistics and make it impossible > > to get postgres to use an index on that table without the -fs switch. > > > > This is bad because it always forces use of an index, even when postgres > > would legitimately ignore it. > > What about doing: > SET enable_seqscan TO 'Off'; > Just before the query in question? > > That way you'd only affect the single query. Possibly you could even > code to spot the two aberrant situations and not do it in those ones. I'd rather not pollute the application's SQL with postgres-isms. Not that I don't love postgres, but there are always critics looking for a reason to use Oracle or (gasp) MS-SQL. As for "code to spot.." I am fairly new to hacking postgres. (Though, I have been using it in various projects since ~1995), but I am excellent C/C++ guy, give me a pointer to where (a) statistics are calculated, and (b) where they are interpreted, and I would do that. Just a question, however, what is the feeling about the way statistics are currently being calculated? My feeling is that some sort of windowing algorithm be used to normalize the statistics to the majority of the entries in a table. It could be as simple as discarding the upper and lower 10% of the record stats, and use the remaining 80% for statistics. That would certainly take care of my problem (and others I am sure), and I'd be glad to write it. ;-) > > > Regards, > Andrew. > -- > _____________________________________________________________________ > Andrew McMillan, e-mail: Andrew@cat-it.co.nz > Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington > Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267
At 01:53 PM 11/15/00 -0500, markw wrote:
>I'd rather not pollute the application's SQL with postgres-isms. Not that I
>don't love postgres, but there are always critics looking for a reason to use
>Oracle or (gasp) MS-SQL.
Define some global variable with the name of the database being run (currently
only Postgres) and guard the SET statement with a conditional...
In the OpenACS project we've got little functions that return query snippets
called things like "db_nextval" that return either "sequence_name.nextval"
or "nextval('sequence_name')" depending on whether the code's running
under Oracle or Postgres. That helps us minimize differences in the source.
- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.
markw <markw@mohawksoft.com> writes:
> Just a question, however, what is the feeling about the way statistics are
> currently being calculated?
They suck, no question about it ;-)
> My feeling is that some sort of windowing
> algorithm be used to normalize the statistics to the majority of the entries
> in a table. It could be as simple as discarding the upper and lower 10% of
> the record stats, and use the remaining 80% for statistics.
I think what most of the discussion has focused on is building
histograms. The current upper-and-lower-bounds-only approach just
plain isn't enough data, even if you discard outliers so that the
data isn't actively misleading.
As far as the most-common-value issue goes, if you have one value that
is vastly more common than any other, I think it would be a real mistake
to throw away that information --- that would mean the planner would do
the wrong thing for queries that do involve that value. What we need
is to save info about several top-frequency values, maybe three or so,
not just one. Also the finding of those values needs to be much more
robust than it is currently.
See past discussions in pghackers --- there have been plenty...
regards, tom lane
> > > AOLServer isn't the only system that can pool database connections, so > >can servlets/JSP, ColdFusion, ASP, etc. No doubt AOLServer would be more > >widely accepted if it used something other than TCL. > >There's also support available for Python, though there's still a lot of >work to be done to support the full AOLserver API (same's true of ns_java, >actually). > >If you use ADP pages, your use of Tcl is typically restricted to snippets of >code anyway, so I've never really understood the complaints about Tcl... What about the php module? Does it take advantage of API? It seems to me your choice of web/application server is kind of dependent on the language you like. If you like perl/php use apache if you like tcl use aolserver, if you like java use tomcat,enhydra,orion (or whatever), if you like python use zope. I guess for the few people who like VB there is IIS/ASP. :wq Tim Uckun Due Diligence Inc. http://www.diligence.com/ Americas Background Investigation Expert. If your company isn't doing background checks, maybe you haven't considered the risks of a bad hire.
At 09:44 AM 11/21/00 -0700, Tim Uckun wrote: >What about the php module? Does it take advantage of API? I don't know. If not, though, there wouldn't be much point in using AOLserver, since the simple and efficient database API is the main attraction. So I think there's a pretty good chance it does. - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
At 07:50 PM 11/30/00 -0600, GH wrote: >On Thu, Nov 23, 2000 at 07:58:29AM -0800, some SMTP stream spewed forth: >> At 09:44 AM 11/21/00 -0700, Tim Uckun wrote: >> >> >What about the php module? Does it take advantage of API? >> >> I don't know. If not, though, there wouldn't be much point in using >> AOLserver, since the simple and efficient database API is the main >> attraction. So I think there's a pretty good chance it does. >> > >Through the course of another thread on the lists we have concluded that >PHP does not support the AOLServer (or any other similar) database API. >The "blockage" is that PHP includes its own database functions, albeit >they are based on the Postgres, MySQL, etc. APIs individually. > >I am considering looking into urging an integration of PHP and >AOLServer's connection pooling (for lack of a better word) stuff. Well, meanwhile I've gotten confirmation from folks in the PHP world (via an openacs forum) that it still isn't threadsafe, though there's an effort underway to track down the problems. I don't know how close to solving this they are. - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
On Thu, Nov 23, 2000 at 07:58:29AM -0800, some SMTP stream spewed forth: > At 09:44 AM 11/21/00 -0700, Tim Uckun wrote: > > >What about the php module? Does it take advantage of API? > > I don't know. If not, though, there wouldn't be much point in using > AOLserver, since the simple and efficient database API is the main > attraction. So I think there's a pretty good chance it does. > Through the course of another thread on the lists we have concluded that PHP does not support the AOLServer (or any other similar) database API. The "blockage" is that PHP includes its own database functions, albeit they are based on the Postgres, MySQL, etc. APIs individually. I am considering looking into urging an integration of PHP and AOLServer's connection pooling (for lack of a better word) stuff. *shrug* gh > > > - Don Baccus, Portland OR <dhogaza@pacifier.com> > Nature photos, on-line guides, Pacific Northwest > Rare Bird Alert Service and other goodies at > http://donb.photo.net.