2

I have installed PostgreSQL 9.1 server on my production server. I have Changed all the configuration(postgresql.conf) according to system.

Everything has been working fine for a week.

After this suddenly, postgresql server becomes very slow. Even for count(*) query on a table.It is taking too much time.

After this I have done so many activities like:

  1. Monitor Load on system :- Normal within range of 0.5 to 1.5.

  2. Monitor No of users logged in application :- 200 to 400. Normal

  3. Recreated Index

  4. Kill the ideal transactions.

  5. Check Locks (No DeadLock Found)

  6. Application server restarted.

  7. Database server restarted.

After doing this all activities the performance of database server is not increased.

It is taking so much time for normal queries also.

Then I drop the database and recreated then Performance Increases

Everything working after recreating the database.

But after some days suddenly performance goes down.

Improve this question
1

1 Answer 1

Reset to default
2

This sounds like the active portion of the database is growing large enough that it doesn't fit in cache, causing actual disk access (which is orders of magnitude slower) for many of your reads. This is often caused by not vacuuming aggressively enough.

Other factors could be related to your configuration of PostgreSQL and the operating system. It's hard to give much advice without knowing:

  • exactly what version of PostgreSQL you're using (9.1 tells us the major release, but the minor release sometimes matters),

  • how you have PostgreSQL configured,

  • what OS you're running on,

  • what hardware you are using (cores, RAM, drive arrays, controllers), etc.

Part of that can be supplied by posting the results of running the query on this page:

http://wiki.postgresql.org/wiki/Server_Configuration

It might also help to select relname, relpages, and reltuples from pg_class for the tables involved and compare numbers when things are running well to when they are slow.

With the additional information, people should be able to make some pretty specific recommendations.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.