Thread: Disk acces
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,
"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
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 >
"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
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, >
[ 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
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 >