Thread: Disk acces

Disk acces

From
"Anders R. Sveen"
Date:
I'm running PostgreSQL 7.0.3 on an Pentium 120 with IDE and 48MB RAM. No
monster, i know that. But i was wondering if anyone had some performance
tips?

We're building a new site wich uses the database for alot of it's pages.
It seems that when a hit occurs you get an eccessive amount of disk
access. It really slows the site down. Any idea?

Thanks,
Anders,


Re: Disk acces

From
Doug McNaught
Date:
"Anders R. Sveen" <anderssv@stud.ntnu.no> writes:

> I'm running PostgreSQL 7.0.3 on an Pentium 120 with IDE and 48MB RAM. No
> monster, i know that. But i was wondering if anyone had some performance
> tips?
>
> We're building a new site wich uses the database for alot of it's pages.
> It seems that when a hit occurs you get an eccessive amount of disk
> access. It really slows the site down. Any idea?

Sounds like "add more memory" is the main tip that people will give
you.  May not be possible, I know,

You don't say what language the site is written in but if it's
straight CGI you're paying for a fork/exec per page, plus connecting
to the database every time.  You may want to use PHP or something
similar that runs in the webserver and maintains a DB connection pool.
(I like Enhydra myself but it's way too heavyweight for your
machine).

-Doug

Re: Disk acces

From
"Mitch Vincent"
Date:
Hi Doug, your comments caught my eye and I thought I'd ask you something..
Are you speaking of using persistant connections with PHP? I'm not sure what
Enhydra is, so forgive me if this is totally off base..

We do a lot of development in PHP and only use PostgreSQL, I've tried and
tried to get persistant connections to work but every time I use them, very
strange things start to happen (variables disappearing and getting corruted
to name one).. It was said a while back on the list that the most probable
reason for that is that PHP isn't thread safe and that was probably the
cause.. Another strange, though not surprising thing that happens is that if
you begin a transaction in a PHP script and the script is terminated before
you comit or rollback (be that from the user clicing stop or a script/server
error), the transaction is left open for the next time that backend is
used -- it's caused a lot of problems.. I say it's not surprising because
that's exactly what I'd expect to happen, there just doesn't seem to be much
of a way to prevent it in some cases.. I've tried the ignore_user_abort but
it still happened quite a lot. (I'm spoiled with Ruby and ensure :-) )..

Have you had those kinds of problems and if so how did you overcome them?
Does Enhydra manage your database/web server pool?

Thanks!!

-Mitch

----- Original Message -----
From: "Doug McNaught" <doug@wireboard.com>
To: "Anders R. Sveen" <anderssv@stud.ntnu.no>
Cc: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Wednesday, January 31, 2001 12:39 PM
Subject: Re: Disk acces


> "Anders R. Sveen" <anderssv@stud.ntnu.no> writes:
>
> > I'm running PostgreSQL 7.0.3 on an Pentium 120 with IDE and 48MB RAM. No
> > monster, i know that. But i was wondering if anyone had some performance
> > tips?
> >
> > We're building a new site wich uses the database for alot of it's pages.
> > It seems that when a hit occurs you get an eccessive amount of disk
> > access. It really slows the site down. Any idea?
>
> Sounds like "add more memory" is the main tip that people will give
> you.  May not be possible, I know,
>
> You don't say what language the site is written in but if it's
> straight CGI you're paying for a fork/exec per page, plus connecting
> to the database every time.  You may want to use PHP or something
> similar that runs in the webserver and maintains a DB connection pool.
> (I like Enhydra myself but it's way too heavyweight for your
> machine).
>
> -Doug
>


Re: Disk acces

From
Doug McNaught
Date:
"Mitch Vincent" <mitch@venux.net> writes:

> We do a lot of development in PHP and only use PostgreSQL, I've tried and
> tried to get persistant connections to work but every time I use them, very
> strange things start to happen (variables disappearing and getting corruted
> to name one).. It was said a while back on the list that the most probable
> reason for that is that PHP isn't thread safe and that was probably the
> cause.. Another strange, though not surprising thing that happens is that if
> you begin a transaction in a PHP script and the script is terminated before
> you comit or rollback (be that from the user clicing stop or a script/server
> error), the transaction is left open for the next time that backend is
> used -- it's caused a lot of problems.. I say it's not surprising because
> that's exactly what I'd expect to happen, there just doesn't seem to be much
> of a way to prevent it in some cases.. I've tried the ignore_user_abort but
> it still happened quite a lot. (I'm spoiled with Ruby and ensure :-) )..

Well, I spoke from the point of view of someone who knows what PHP is
and knows that it's supposed to support persistent connections.  I've
never written a web page in PHP or run a site that uses it; sorry I
can't really help. ;( It's too bad, because PHP/PGSQL looks like it
should be a good combo for serving lightweight web applications.

> Have you had those kinds of problems and if so how did you overcome them?
> Does Enhydra manage your database/web server pool?

Yes, Enhydra is a Java web application server (open source) that does
DB connection pooling for you, gives you a GUI interface to lay out
your tables (if you want) which then generates both SQL and Java code
for you, and also has a very good HTML templating mechanism called
XMLC.  If you're interested in finding out more about it check out:

http://www.enhydra.org/

I moved from straight Perl/CGI to Java servlets and then to Enhydra,
which is why I've never used PHP.

-Doug

Re: Disk acces

From
"Roderick A. Anderson"
Date:
On Wed, 31 Jan 2001, Anders R. Sveen wrote:

> I'm running PostgreSQL 7.0.3 on an Pentium 120 with IDE and 48MB RAM. No
> monster, i know that. But i was wondering if anyone had some performance
> tips?

Get SCSI, add RAM, newer CPU, go for RAID!

> We're building a new site wich uses the database for alot of it's pages.
> It seems that when a hit occurs you get an eccessive amount of disk
> access. It really slows the site down. Any idea?
>
> Thanks,
> Anders,
>


Re: Re: Disk acces

From
Bruce Momjian
Date:
[ Charset ISO-8859-1 unsupported, converting... ]
> Hi Doug, your comments caught my eye and I thought I'd ask you something..
> Are you speaking of using persistant connections with PHP? I'm not sure what
> Enhydra is, so forgive me if this is totally off base..
>
> We do a lot of development in PHP and only use PostgreSQL, I've tried and
> tried to get persistant connections to work but every time I use them, very
> strange things start to happen (variables disappearing and getting corruted
> to name one).. It was said a while back on the list that the most probable
> reason for that is that PHP isn't thread safe and that was probably the
> cause.. Another strange, though not surprising thing that happens is that if
> you begin a transaction in a PHP script and the script is terminated before
> you comit or rollback (be that from the user clicing stop or a script/server
> error), the transaction is left open for the next time that backend is
> used -- it's caused a lot of problems.. I say it's not surprising because
> that's exactly what I'd expect to happen, there just doesn't seem to be much
> of a way to prevent it in some cases.. I've tried the ignore_user_abort but
> it still happened quite a lot. (I'm spoiled with Ruby and ensure :-) )..
>
> Have you had those kinds of problems and if so how did you overcome them?
> Does Enhydra manage your database/web server pool?

I just talked to PHP/Rasmus yesterday, and I will try to get this fixed
in the PHP PostgreSQL code soon.  It involves adding the ability to
ROLLBACK before handing the connection to a new user.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Re: Disk acces

From
Mitch Vincent
Date:
Super sweet... That is excellent.

-Mitch



On Thu, 1 Feb 2001, Bruce Momjian wrote:

> [ Charset ISO-8859-1 unsupported, converting... ]
> > Hi Doug, your comments caught my eye and I thought I'd ask you something..
> > Are you speaking of using persistant connections with PHP? I'm not sure what
> > Enhydra is, so forgive me if this is totally off base..
> >
> > We do a lot of development in PHP and only use PostgreSQL, I've tried and
> > tried to get persistant connections to work but every time I use them, very
> > strange things start to happen (variables disappearing and getting corruted
> > to name one).. It was said a while back on the list that the most probable
> > reason for that is that PHP isn't thread safe and that was probably the
> > cause.. Another strange, though not surprising thing that happens is that if
> > you begin a transaction in a PHP script and the script is terminated before
> > you comit or rollback (be that from the user clicing stop or a script/server
> > error), the transaction is left open for the next time that backend is
> > used -- it's caused a lot of problems.. I say it's not surprising because
> > that's exactly what I'd expect to happen, there just doesn't seem to be much
> > of a way to prevent it in some cases.. I've tried the ignore_user_abort but
> > it still happened quite a lot. (I'm spoiled with Ruby and ensure :-) )..
> >
> > Have you had those kinds of problems and if so how did you overcome them?
> > Does Enhydra manage your database/web server pool?
>
> I just talked to PHP/Rasmus yesterday, and I will try to get this fixed
> in the PHP PostgreSQL code soon.  It involves adding the ability to
> ROLLBACK before handing the connection to a new user.
>
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
>