Thread: postgresql having trouble under load
Hi There, I am new to postgresql and we are thinking of migrating from MS-SQL to postgresql, but I am having trouble getting postgresql to keep up with the load. I am using it to serve about 10000 images/hour (16/sec), so I am making that many connections to determine which image to serve each time. It seems to build up a lot of connections and then dissallow non-super users. My sysadmin has set the connection limit to above 1000. I am using php 4.3 on red hat 8 and postgresql 7.3. I explicitly use pg_close at the end of my PHP scripts, but it doesn't seem to have much impact. Is there anything I should be doing in a high load situation. I have just set up postgresql with default settings. Thanks in advance, Greg There are 10 kinds of people: ones that get binary, and ones that don't. This e-mail message (and any attachments) may contain information that is confidential to SportOdds Systems Pty Ltd and it's fully owned subsidiary, SuperOdds UK Ltd. If you are not the intended recipient you cannot print, retransmit, store, distribute or copy the message or attachments. In such a case, please notify the sender by return e-mail immediately and erase all copies of the message and attachments.You should only re-transmit or distribute this email or attachments if you are authorised to do so. Opinions, conclusions and other information in this message and attachments that do not relate to the official business of SportOdds (and/or SuperOdds) are neither given nor endorsed by it. SportOdds (and/or SuperOdds) has implemented anti-virus software, and whilst all care is taken, it is the recipient's responsibility to ensure that any attachments are scanned for viruses prior to use.
It sounds like you are using pg_pconnect. First, try using pg_connect and see if it can handle the load. If it can't, write back and I'll tell you how to configure apache / php / postgresql to handle the load you want with pg_pconnect. pg_pconnect is dangerous, and pg_close will NOT close its connections, ever. On Fri, 17 Oct 2003, Greg Reeves wrote: > Hi There, > > I am new to postgresql and we are thinking of migrating from MS-SQL to > postgresql, but I am having trouble getting postgresql to keep up with the > load. > > I am using it to serve about 10000 images/hour (16/sec), so I am making that > many connections to determine which image to serve each time. > > It seems to build up a lot of connections and then dissallow non-super > users. My sysadmin has set the connection limit to above 1000. > > I am using php 4.3 on red hat 8 and postgresql 7.3. > > I explicitly use pg_close at the end of my PHP scripts, but it doesn't seem > to have much impact. > > Is there anything I should be doing in a high load situation. I have just > set up postgresql with default settings. > > Thanks in advance, > Greg > > There are 10 kinds of people: > ones that get binary, and ones that don't. > > > > This e-mail message (and any attachments) may contain information that is > confidential to SportOdds Systems Pty Ltd and it's fully owned subsidiary, > SuperOdds UK Ltd. If you are not the intended recipient you cannot print, > retransmit, store, distribute or copy the message or attachments. In such a > case, please notify the sender by return e-mail immediately and erase all > copies of the message and attachments.You should only re-transmit or > distribute this email or attachments if you are authorised to do so. > Opinions, conclusions and other information in this message and attachments > that do not relate to the official business of SportOdds (and/or SuperOdds) > are neither given nor endorsed by it. SportOdds (and/or SuperOdds) has > implemented anti-virus software, and whilst all care is taken, it is the > recipient's responsibility to ensure that any attachments are scanned for > viruses prior to use. > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
On Fri, 2003-10-17 at 05:18, Greg Reeves wrote:
and high traffic website with dynamic content is caching. You can't possibly need to get 16
images per second from the database, you're not an on-line surgery room.
sense stuff.
Hmm, the website seems to be loading pretty slow as it is.Hi There, I am new to postgresql and we are thinking of migrating from MS-SQL to postgresql, but I am having trouble getting postgresql to keep up with the load.
Not to make wild assumptions, but that seems quite exaggerated. Are those live images or something? It looks like you could really use some caching. The key point in building a largeI am using it to serve about 10000 images/hour (16/sec), so I am making that many connections to determine which image to serve each time.
and high traffic website with dynamic content is caching. You can't possibly need to get 16
images per second from the database, you're not an on-line surgery room.
Read the configuration and tuning docs. They're not like exotic and uber-geek-only, but commonIt seems to build up a lot of connections and then dissallow non-super users. My sysadmin has set the connection limit to above 1000. I am using php 4.3 on red hat 8 and postgresql 7.3. I explicitly use pg_close at the end of my PHP scripts, but it doesn't seem to have much impact. Is there anything I should be doing in a high load situation. I have just set up postgresql with default settings.
sense stuff.
Thanks in advance, Greg There are 10 kinds of people: ones that get binary, and ones that don't. This e-mail message (and any attachments) may contain information that is confidential to SportOdds Systems Pty Ltd and it's fully owned subsidiary, SuperOdds UK Ltd. If you are not the intended recipient you cannot print, retransmit, store, distribute or copy the message or attachments. In such a case, please notify the sender by return e-mail immediately and erase all copies of the message and attachments.You should only re-transmit or distribute this email or attachments if you are authorised to do so. Opinions, conclusions and other information in this message and attachments that do not relate to the official business of SportOdds (and/or SuperOdds) are neither given nor endorsed by it. SportOdds (and/or SuperOdds) has implemented anti-virus software, and whilst all care is taken, it is the recipient's responsibility to ensure that any attachments are scanned for viruses prior to use. ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
-- Radu-Adrian Popescu CSA, DBA, Developer Aldratech Ltd. +40213212243 |