pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDaily crash
11+ messages / 6 participants
[nested] [flat]
* Daily crash
@ 2003-02-27 12:19 Catalin <catalin@cyber.ro>
2003-02-27 12:24 ` Re: Daily crash Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
0 siblings, 1 reply; 11+ messages in thread
From: Catalin @ 2003-02-27 12:19 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Daily crash
2003-02-27 12:19 Daily crash Catalin <catalin@cyber.ro>
@ 2003-02-27 12:24 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-02-27 12:51 ` Re: Daily crash Catalin <catalin@cyber.ro>
0 siblings, 1 reply; 11+ messages in thread
From: Shridhar Daithankar @ 2003-02-27 12:24 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Daily crash
2003-02-27 12:19 Daily crash Catalin <catalin@cyber.ro>
2003-02-27 12:24 ` Re: Daily crash Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
@ 2003-02-27 12:51 ` Catalin <catalin@cyber.ro>
2003-02-27 13:03 ` Re: Daily crash Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-02-27 13:30 ` Re: Daily crash Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 11+ messages in thread
From: Catalin @ 2003-02-27 12:51 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Daily crash
2003-02-27 12:19 Daily crash Catalin <catalin@cyber.ro>
2003-02-27 12:24 ` Re: Daily crash Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-02-27 12:51 ` Re: Daily crash Catalin <catalin@cyber.ro>
@ 2003-02-27 13:03 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
1 sibling, 0 replies; 11+ messages in thread
From: Shridhar Daithankar @ 2003-02-27 13:03 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Daily crash
2003-02-27 12:19 Daily crash Catalin <catalin@cyber.ro>
2003-02-27 12:24 ` Re: Daily crash Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-02-27 12:51 ` Re: Daily crash Catalin <catalin@cyber.ro>
@ 2003-02-27 13:30 ` Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 11+ messages in thread
From: Bruno Wolff III @ 2003-02-27 13:30 UTC (permalink / raw)
To: Catalin <catalin@cyber.ro>; +Cc: pgsql-performance
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).
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [Performance] Daily Crash
@ 2003-02-27 14:44 Aspire Something <sank89@sancharnet.in>
2003-02-27 14:53 ` Re: [Performance] Daily Crash Catalin <catalin@cyber.ro>
0 siblings, 1 reply; 11+ messages in thread
From: Aspire Something @ 2003-02-27 14:44 UTC (permalink / raw)
To: pgsql-performance
Dear Catalin,
What I understand from ur post is,
1. U are using PHP for connection. what version ??? assuming u are on PHP
version >4.2.X
2. Ur Postgresql server is not tuned to handle loads.
What U can try is ......
1.Make sure that ur PHP scripts close the connection when transaction is
complete (HOW??? see after pg_connect
U see a pg_close function of PHP)
a. Use pg_pconnect and forget about pg_connect and pg_close
2. Increse the limit of connection to be made to PostgreSQL this can be
done as said by Shridhar the default is 32
3. For God sake Upgrade to PostgreSQL 7.3.2 and PHP 4.3.1 you are missing a
lot with that old versions.
Regards,
V Kashyap
================================
Some people think it's holding on that makes one strong;
sometimes it's letting go.
================================
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [Performance] Daily Crash
2003-02-27 14:44 Re: [Performance] Daily Crash Aspire Something <sank89@sancharnet.in>
@ 2003-02-27 14:53 ` Catalin <catalin@cyber.ro>
2003-02-27 16:53 ` Re: [Performance] Daily Crash Josh Berkus <josh@agliodbs.com>
2003-02-27 18:51 ` Re: [Performance] Daily Crash philip johnson <philip.johnson@atempo.com>
0 siblings, 2 replies; 11+ messages in thread
From: Catalin @ 2003-02-27 14:53 UTC (permalink / raw)
To: pgsql-performance
i have php 4.2.3
i use pg_pConnect and no pg_Close.
thanks for yours advices.
i will upgrade to postgresql 7.2.3 asap
and see if there are improvments !
thanks again !
Catalin
----- Original Message -----
From: Aspire Something
To: pgsql-performance@postgresql.org
Sent: Thursday, February 27, 2003 4:44 PM
Subject: Re: [PERFORM] [Performance] Daily Crash
Dear Catalin,
What I understand from ur post is,
1. U are using PHP for connection. what version ??? assuming u are on PHP
version >4.2.X
2. Ur Postgresql server is not tuned to handle loads.
What U can try is ......
1.Make sure that ur PHP scripts close the connection when transaction is
complete (HOW??? see after pg_connect
U see a pg_close function of PHP)
a. Use pg_pconnect and forget about pg_connect and pg_close
2. Increse the limit of connection to be made to PostgreSQL this can be
done as said by Shridhar the default is 32
3. For God sake Upgrade to PostgreSQL 7.3.2 and PHP 4.3.1 you are missing a
lot with that old versions.
Regards,
V Kashyap
================================
Some people think it's holding on that makes one strong;
sometimes it's letting go.
================================
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [Performance] Daily Crash
2003-02-27 14:44 Re: [Performance] Daily Crash Aspire Something <sank89@sancharnet.in>
2003-02-27 14:53 ` Re: [Performance] Daily Crash Catalin <catalin@cyber.ro>
@ 2003-02-27 16:53 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 11+ messages in thread
From: Josh Berkus @ 2003-02-27 16:53 UTC (permalink / raw)
To: Catalin <catalin@cyber.ro>; pgsql-performance
Catalin,
> thanks for yours advices.
> i will upgrade to postgresql 7.2.3 asap
> and see if there are improvments !
Um, that's 7.2.4. 7.2.3 has a couple of bugs in it.
Also, you are going to have to edit your postgresql.conf file per the
suggestions already made on this list.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [Performance] Daily Crash
2003-02-27 14:44 Re: [Performance] Daily Crash Aspire Something <sank89@sancharnet.in>
2003-02-27 14:53 ` Re: [Performance] Daily Crash Catalin <catalin@cyber.ro>
@ 2003-02-27 18:51 ` philip johnson <philip.johnson@atempo.com>
2003-02-27 20:19 ` Re: [Performance] Daily Crash Catalin <catalin@cyber.ro>
1 sibling, 1 reply; 11+ messages in thread
From: philip johnson @ 2003-02-27 18:51 UTC (permalink / raw)
To: Catalin <catalin@cyber.ro>; pgsql-performance
pgsql-performance-owner@postgresql.org wrote:
> i have php 4.2.3
> i use pg_pConnect and no pg_Close.
>
> thanks for yours advices.
> i will upgrade to postgresql 7.2.3 asap
> and see if there are improvments !
>
> thanks again !
>
> Catalin
>
> ----- Original Message -----
> From: Aspire Something
> To: pgsql-performance@postgresql.org
> Sent: Thursday, February 27, 2003 4:44 PM
> Subject: Re: [PERFORM] [Performance] Daily Crash
>
>
> Dear Catalin,
>
>
> What I understand from ur post is,
> 1. U are using PHP for connection. what version ??? assuming u are on
> PHP version >4.2.X
> 2. Ur Postgresql server is not tuned to handle loads.
>
> What U can try is ......
>
> 1.Make sure that ur PHP scripts close the connection when
> transaction is complete (HOW??? see after pg_connect
>
> U see a pg_close function of PHP)
> a. Use pg_pconnect and forget about pg_connect and pg_close
> 2. Increse the limit of connection to be made to PostgreSQL this can
> be done as said by Shridhar the default is 32
> 3. For God sake Upgrade to PostgreSQL 7.3.2 and PHP 4.3.1 you are
> missing a lot with that old versions.
>
>
>
> Regards,
> V Kashyap
>
> ================================
> Some people think it's holding on that makes one strong;
> sometimes it's letting go.
> ================================
>
>
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 2: you can get off all
> lists at once with the unregister command (send "unregister
> YourEmailAddressHere" to majordomo@postgresql.org)
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 5: Have you checked our
> extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
you can use no persistent link to database
see in php.ini
pgsql.allow_persistent = Off
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [Performance] Daily Crash
2003-02-27 14:44 Re: [Performance] Daily Crash Aspire Something <sank89@sancharnet.in>
2003-02-27 14:53 ` Re: [Performance] Daily Crash Catalin <catalin@cyber.ro>
2003-02-27 18:51 ` Re: [Performance] Daily Crash philip johnson <philip.johnson@atempo.com>
@ 2003-02-27 20:19 ` Catalin <catalin@cyber.ro>
2003-02-28 13:22 ` Re: [Performance] Daily Crash Catalin <catalin@cyber.ro>
0 siblings, 1 reply; 11+ messages in thread
From: Catalin @ 2003-02-27 20:19 UTC (permalink / raw)
To: pgsql-performance
how often should i run VACUUM to keep the thing tunned ?
Catalin
----- Original Message -----
From: philip johnson
To: Catalin ; pgsql-performance@postgresql.org
Sent: Thursday, February 27, 2003 8:51 PM
Subject: RE: [PERFORM] [Performance] Daily Crash
pgsql-performance-owner@postgresql.org wrote:
> i have php 4.2.3
> i use pg_pConnect and no pg_Close.
>
> thanks for yours advices.
> i will upgrade to postgresql 7.2.3 asap
> and see if there are improvments !
>
> thanks again !
>
> Catalin
>
> ----- Original Message -----
> From: Aspire Something
> To: pgsql-performance@postgresql.org
> Sent: Thursday, February 27, 2003 4:44 PM
> Subject: Re: [PERFORM] [Performance] Daily Crash
>
>
> Dear Catalin,
>
>
> What I understand from ur post is,
> 1. U are using PHP for connection. what version ??? assuming u are on
> PHP version >4.2.X
> 2. Ur Postgresql server is not tuned to handle loads.
>
> What U can try is ......
>
> 1.Make sure that ur PHP scripts close the connection when
> transaction is complete (HOW??? see after pg_connect
>
> U see a pg_close function of PHP)
> a. Use pg_pconnect and forget about pg_connect and pg_close
> 2. Increse the limit of connection to be made to PostgreSQL this can
> be done as said by Shridhar the default is 32
> 3. For God sake Upgrade to PostgreSQL 7.3.2 and PHP 4.3.1 you are
> missing a lot with that old versions.
>
>
>
> Regards,
> V Kashyap
>
> ================================
> Some people think it's holding on that makes one strong;
> sometimes it's letting go.
> ================================
>
>
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 2: you can get off all
> lists at once with the unregister command (send "unregister
> YourEmailAddressHere" to majordomo@postgresql.org)
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 5: Have you checked our
> extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
you can use no persistent link to database
see in php.ini
pgsql.allow_persistent = Off
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [Performance] Daily Crash
2003-02-27 14:44 Re: [Performance] Daily Crash Aspire Something <sank89@sancharnet.in>
2003-02-27 14:53 ` Re: [Performance] Daily Crash Catalin <catalin@cyber.ro>
2003-02-27 18:51 ` Re: [Performance] Daily Crash philip johnson <philip.johnson@atempo.com>
2003-02-27 20:19 ` Re: [Performance] Daily Crash Catalin <catalin@cyber.ro>
@ 2003-02-28 13:22 ` Catalin <catalin@cyber.ro>
0 siblings, 0 replies; 11+ messages in thread
From: Catalin @ 2003-02-28 13:22 UTC (permalink / raw)
To: pgsql-performance
so i did the server upgrade to postgresql 7.3.2
and tunned it like you ppl said.
it works great.
plus my DB on postgresql 7.0 had 640 MB, including indexes, etc (files)
now it has only 140MB.
this is much better !
thank you for your help !
Catalin
----- Original Message -----
From: Catalin
To: pgsql-performance@postgresql.org
Sent: Thursday, February 27, 2003 10:19 PM
Subject: Re: [PERFORM] [Performance] Daily Crash
how often should i run VACUUM to keep the thing tunned ?
Catalin
----- Original Message -----
From: philip johnson
To: Catalin ; pgsql-performance@postgresql.org
Sent: Thursday, February 27, 2003 8:51 PM
Subject: RE: [PERFORM] [Performance] Daily Crash
pgsql-performance-owner@postgresql.org wrote:
> i have php 4.2.3
> i use pg_pConnect and no pg_Close.
>
> thanks for yours advices.
> i will upgrade to postgresql 7.2.3 asap
> and see if there are improvments !
>
> thanks again !
>
> Catalin
>
> ----- Original Message -----
> From: Aspire Something
> To: pgsql-performance@postgresql.org
> Sent: Thursday, February 27, 2003 4:44 PM
> Subject: Re: [PERFORM] [Performance] Daily Crash
>
>
> Dear Catalin,
>
>
> What I understand from ur post is,
> 1. U are using PHP for connection. what version ??? assuming u are on
> PHP version >4.2.X
> 2. Ur Postgresql server is not tuned to handle loads.
>
> What U can try is ......
>
> 1.Make sure that ur PHP scripts close the connection when
> transaction is complete (HOW??? see after pg_connect
>
> U see a pg_close function of PHP)
> a. Use pg_pconnect and forget about pg_connect and pg_close
> 2. Increse the limit of connection to be made to PostgreSQL this can
> be done as said by Shridhar the default is 32
> 3. For God sake Upgrade to PostgreSQL 7.3.2 and PHP 4.3.1 you are
> missing a lot with that old versions.
>
>
>
> Regards,
> V Kashyap
>
> ================================
> Some people think it's holding on that makes one strong;
> sometimes it's letting go.
> ================================
>
>
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 2: you can get off all
> lists at once with the unregister command (send "unregister
> YourEmailAddressHere" to majordomo@postgresql.org)
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 5: Have you checked our
> extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
you can use no persistent link to database
see in php.ini
pgsql.allow_persistent = Off
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2003-02-28 13:22 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-27 12:19 Daily crash Catalin <catalin@cyber.ro>
2003-02-27 12:24 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-02-27 12:51 ` Catalin <catalin@cyber.ro>
2003-02-27 13:03 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-02-27 13:30 ` Bruno Wolff III <bruno@wolff.to>
2003-02-27 14:44 Re: [Performance] Daily Crash Aspire Something <sank89@sancharnet.in>
2003-02-27 14:53 ` Re: [Performance] Daily Crash Catalin <catalin@cyber.ro>
2003-02-27 16:53 ` Re: [Performance] Daily Crash Josh Berkus <josh@agliodbs.com>
2003-02-27 18:51 ` Re: [Performance] Daily Crash philip johnson <philip.johnson@atempo.com>
2003-02-27 20:19 ` Re: [Performance] Daily Crash Catalin <catalin@cyber.ro>
2003-02-28 13:22 ` Re: [Performance] Daily Crash Catalin <catalin@cyber.ro>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox