Thread: postgresql 7.4.6 slowing down
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
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: > > http://www.postgresql.org/docs/7.4/static/runtime-config.html > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > -- Best Regards, Frans Gunawan fransgunawan@gmail.com
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: > > > > http://www.postgresql.org/docs/7.4/static/runtime-config.html Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > 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.
Attachment
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 netllama@gmail.com LlamaLand http://netllama.linux-sxs.org
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: http://www.postgresql.org/docs/7.4/static/runtime-config.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/
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: http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php 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
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. >Is there somebody who experience the same problem? and what is the solution? > > Are you running vacuum and analyze regularly? > >Thank You, > > >Frans > >---------------------------(end of broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL