postgres application running slow

[mufeed_usman]

Greetings folks,

A client of the firm where I work sent a complaint stating that their application (banking software) developed in VB has begun to slow down when they excute certain queries. The DB is maintained using postgreSQL and the server is running on fedora. Nodes are all windows machines.

This is what I'd like to know - Is there anything that could be done on the server to overcome their stated problem?

Under normal circumstances what are all the things I need to do to tune a system running on fedora, like defrag in windows?

Regards,
Mufy

[james_in_denver]

Well, it most likely is not a Linux issue as much as it is a database issue.

I would recommend starting with the SQL code that is being executed on the front end. Make sure that as many queries as possible use indexes,

Also, it sounds as if performance for the database was acceptable at the begining of the project, and has slowed down? this is most likely due to increased volumes of data. One thing you can do to optimize Postgres is to place/move some of the indexes and/or tables on seperate disks. This can dramatically improve I/O performance.

If this database is used with a lot of records being inserted and deleted, you may need to run the "vacuumdb" command to "coalesce" free space in your tables.

The one place I would recommend starting with though is query tuning, make sure that you have the right indexes in place for the queries being executed.

[ewdi]

what version of postgree?if it's the latest do you guys enable UTF?

[Mossy]

1) Make sure you have vacuumed & analyzed your DB on a regular basis. Note that you can actually lose your entire DB due to the XID wraparound issue if you don't vacuum. (Maximum of 4 billion transactions before the IDs wrap around and your data is dead -- vacuum resets the counter each time.) This is roughly the schedule I use -- hourly analyze, nightly vacuum, weekly vacuum full. There's also an autovacuum daemon that detects when your tables have been updated enough to require analyze or vacuum.

2) Check your postgres configuration. The default config is designed to run under extremely old machines and runs very poorly in any sensible server environment. If nobody has ever touched the config, postgres is probably running in crippled mode. The following is a pretty good link to read up.

http://www.varlena.com/varlena/Gener...bits/perf.html

3) Check your hardware config. RAID-1 for OS + WAL, RAID-10 for Postgres database is the optimal solution. Some people say RAID-5 reaches parity with RAID-10 when you hit 8+ hard drives.

4) Examine the queries using the EXPLAIN ANALYZE command. Even if you have indexes on elements, it's possible they aren't being used due to data type mismatches or the statistics tell the query planner a seqscan would be faster. (Whether it actually is or not requires further analysis.)

Originally Posted by mufeed_usman

Greetings folks,
A client of the firm where I work sent a complaint stating that their application (banking software) developed in VB has begun to slow down when they excute certain queries. The DB is maintained using postgreSQL and the server is running on fedora. Nodes are all windows machines.