postgresql 7.4.6 slowing down

[Frans]

I'm using Fedora Core 2, apache 2.0 and postgresql 7.4.6.
Recently, the application is slowing down. When I check the process
using "top", sometimes postmaster process time is increasing and the
process is never end, I think this cause the application slowing down
and sometimes this process make the server slowing down and I have to
restart postmaster.
Is there somebody who experience the same problem? and what is the solution?


Thank You,


Frans

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [email]majordomo@postgresql.org[/email])

[Lonni J Friedman]

How often are you running vacuumdb ?


On Mon, 31 Jan 2005 08:28:28 +0700, Frans <fransgunawan@gmail.com> wrote:

> I'm using Fedora Core 2, apache 2.0 and postgresql 7.4.6.
> Recently, the application is slowing down. When I check the process
> using "top", sometimes postmaster process time is increasing and the
> process is never end, I think this cause the application slowing down
> and sometimes this process make the server slowing down and I have to
> restart postmaster.
> Is there somebody who experience the same problem? and what is the solution?

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~
L. Friedman [email]netllama@gmail.com[/email]
LlamaLand [url]http://netllama.linux-sxs.org[/url]

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

[url]http://archives.postgresql.org[/url]

[Michael Fuhr]

On Mon, Jan 31, 2005 at 08:28:28AM +0700, Frans wrote:

>
> I'm using Fedora Core 2, apache 2.0 and postgresql 7.4.6.
> Recently, the application is slowing down. When I check the process
> using "top", sometimes postmaster process time is increasing and the
> process is never end, I think this cause the application slowing down
> and sometimes this process make the server slowing down and I have to
> restart postmaster.

Have you investigated what queries are running? See configuration
settings like stats_command_string and log_min_duration_statement
in the "Run-time Configuration" section of the "Server Run-time
Environment" chapter of the documentation:

[url]http://www.postgresql.org/docs/7.4/static/runtime-config.html[/url]

--
Michael Fuhr
[url]http://www.fuhr.org/~mfuhr/[/url]

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Frans]

last vacuumdb was 5 or 6 months ago, but the transactions is not big
(about 500-1000 transaction a day). The postmaster did not eating up
memory, but eating up processor process.


On Sun, 30 Jan 2005 22:13:49 -0700, Michael Fuhr <mike@fuhr.org> wrote:

> On Mon, Jan 31, 2005 at 08:28:28AM +0700, Frans wrote:

> >
> > I'm using Fedora Core 2, apache 2.0 and postgresql 7.4.6.
> > Recently, the application is slowing down. When I check the process
> > using "top", sometimes postmaster process time is increasing and the
> > process is never end, I think this cause the application slowing down
> > and sometimes this process make the server slowing down and I have to
> > restart postmaster.

>
> Have you investigated what queries are running? See configuration
> settings like stats_command_string and log_min_duration_statement
> in the "Run-time Configuration" section of the "Server Run-time
> Environment" chapter of the documentation:
>
> [url]http://www.postgresql.org/docs/7.4/static/runtime-config.html[/url]
>
> --
> Michael Fuhr
> [url]http://www.fuhr.org/~mfuhr/[/url]
>

--
Best Regards,

Frans Gunawan
[email]fransgunawan@gmail.com[/email]

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Richard Huxton]

Frans wrote:

> I'm using Fedora Core 2, apache 2.0 and postgresql 7.4.6.
> Recently, the application is slowing down.

You don't say what the application is.

> When I check the process
> using "top", sometimes postmaster process time is increasing and the
> process is never end,

The postmaster is supposed to run all the time - this is called a
"daemon" in Unix terms. It is responsible for receiving new connections
and handling them. Backends handling queries will look something like:
postgres: richardh lamp [local] idle
This is user richardh on database lamp connecting locally with no query
in action.
Now, if the processor-time-used column is increasing rapidly that would
be unusual, but it should be running always.

> I think this cause the application slowing down

You haven't provided any convincing evidence of this - not yet.

> and sometimes this process make the server slowing down and I have to
> restart postmaster.

This should not be required. Ever.

> Is there somebody who experience the same problem? and what is the solution?

You'll need to work through the following steps:
1. Check there is nothing in the logs (messages/httpd/postgresql) that
looks "bad" when the application is slow.

2. Check your configuration settings for apache/postgresql - make sure
they are reasonable for the workload. If you're not sure what settings
you want for postgresql, perhaps read the performance tuning article here:
[url]http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php[/url]

3. Check the output of "vmstat 1" when the server is busy and see what
happens as it gets less busy. Especially check the disk i/o readings, as
this is frequently the limiting factor in a database system.

Once you have a good idea of what is happening, post again and we'll see
how we can help.
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

[url]http://www.postgresql.org/docs/faq[/url]

[Martijn van Oosterhout]

On Mon, Jan 31, 2005 at 01:20:39PM +0700, Frans wrote:

> last vacuumdb was 5 or 6 months ago, but the transactions is not big
> (about 500-1000 transaction a day). The postmaster did not eating up
> memory, but eating up processor process.

There's your problem. If you don't vacuumdb, then the files will get
bigger and bigger and postgresql gets slower and slower. You probably
need to do a VACUUM FULL and a full REINDEX to get everything back to
normal...

Maybe you should install the autovacuum if you're not going to schedule
it normally...

> On Sun, 30 Jan 2005 22:13:49 -0700, Michael Fuhr <mike@fuhr.org> wrote:

> > On Mon, Jan 31, 2005 at 08:28:28AM +0700, Frans wrote:

> > >
> > > I'm using Fedora Core 2, apache 2.0 and postgresql 7.4.6.
> > > Recently, the application is slowing down. When I check the process
> > > using "top", sometimes postmaster process time is increasing and the
> > > process is never end, I think this cause the application slowing down
> > > and sometimes this process make the server slowing down and I have to
> > > restart postmaster.

> >
> > Have you investigated what queries are running? See configuration
> > settings like stats_command_string and log_min_duration_statement
> > in the "Run-time Configuration" section of the "Server Run-time
> > Environment" chapter of the documentation:
> >
> > [url]http://www.postgresql.org/docs/7.4/static/runtime-config.html[/url]

Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> [url]http://svana.org/kleptog/[/url]

> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see [url]http://www.gnupg.org[/url]

iD8DBQFB/jS4Y5Twig3Ge+YRAopZAJ4/JByuRQTxjGG5PPwwe7IB0UVhGwCgmtbj
27drwm9Wpa/ApRngZZ5BGdQ=
=PXY9
-----END PGP SIGNATURE-----