Thread: Daily crash
Hi there, Is there any performance tunning that i can make to the postgresql server to make the server more stable. In my case the main usage of the DB is from an website which has quite lots of visitors. In the last weeks the SQL server crashes every day ! Before complete crash the transaction start to work slower. I would appreciate any suggestion regarding this issue ! Thank you ! Catalin www.xclub.ro
On 27 Feb 2003 at 14:19, Catalin wrote: > Is there any performance tunning that i can make to the postgresql server to > make the server more stable. > In my case the main usage of the DB is from an website which has quite lots > of visitors. > In the last weeks the SQL server crashes every day ! Could you please post the database logs? This is weird as far as I can guess. Bye Shridhar -- I'm frequently appalled by the low regard you Earthmen have for life. -- Spock, "The Galileo Seven", stardate 2822.3
i'm afraid i don't have any logs. i have the default redhat instalation of postgres 7.0.2 which comes with no logging enabled. i will try to enable logging and post the logs to the list ! anyway in PHP when trying to connect to the crashed SQL server i get the error message: Too many connections... Catalin ----- Original Message ----- From: Shridhar Daithankar To: pgsql-performance@postgresql.org Sent: Thursday, February 27, 2003 2:24 PM Subject: Re: [PERFORM] Daily crash On 27 Feb 2003 at 14:19, Catalin wrote: > Is there any performance tunning that i can make to the postgresql server to > make the server more stable. > In my case the main usage of the DB is from an website which has quite lots > of visitors. > In the last weeks the SQL server crashes every day ! Could you please post the database logs? This is weird as far as I can guess. Bye Shridhar -- I'm frequently appalled by the low regard you Earthmen have for life. -- Spock, "The Galileo Seven", stardate 2822.3 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
On 27 Feb 2003 at 14:51, Catalin wrote: > i'm afraid i don't have any logs. > i have the default redhat instalation of postgres 7.0.2 which comes with no > logging enabled. > > i will try to enable logging and post the logs to the list ! > > anyway in PHP when trying to connect to the crashed SQL server i get the > error message: > Too many connections... Tell me. Does that sound like a crash? To me the server is well alive. And if you are using default configuration, you must be experiencing a real pathetic performance for a real world load. Try tuning the database. There are too many tips to put in one place. but editing /var/lib/data/postgresql/postgresql.conf ( I hope I am right, I am too used to do pg_ctl by hand. Never used services provided by disro.s) is first step. You need to read the admin guide as well. HTH Bye Shridhar -- Glib's Fourth Law of Unreliability: Investment in reliability will increase until it exceeds the probable cost of errors, or until someone insists on getting some useful work done.
On Thu, Feb 27, 2003 at 14:51:59 +0200, Catalin <catalin@cyber.ro> wrote: > i'm afraid i don't have any logs. > i have the default redhat instalation of postgres 7.0.2 which comes with no > logging enabled. You should upgrade to 7.2.4 or 7.3.2. (7.3 has schemas and that may make upgrading harder which is why you might consider just going to 7.2.4.) > i will try to enable logging and post the logs to the list ! > > anyway in PHP when trying to connect to the crashed SQL server i get the > error message: > Too many connections... You are going to want the number of allowed connections to match the number of simultaneous requests possible from the web server. Typically this is the maximum number of allowed apache processes which defaults to something like 150. The default maximum number of connections to postgres is about 32. You will also want to raise the number of shared buffers to about 1000 (assuming you have at least a couple hundred of megabytes of memory), not just to 2 times the new maximum number of connections. This may require to change the maximum amount of shared memory allowed by your operating system. Also take a look at increasing sort mem as well. You don't want this too high because each sort gets this much memory and in your situation it may be that you could have a lot of sorts runing at the same time (dpending on the types of queries being done).