Re: PostgreSQL performance issues

Tiago J. Adami schreef:
> Hi all, I'm working for a brazillian company developing and
> maintaining a ERP sw that uses PostgreSQL as it main OLTP database
> system. We're just to start the migration to IBM DB2 because of many
> performance issues. I searched the solution for these performance
> problems, and can't find anything on the other web foruns. I'll put
> them in this post as topics, but first I'll describe how's the ERP
> database's schema: - More than 200 tables; - About 10 tables with
> about 10,000 transactions and 15,000 new rows per day; - These 10
> tables has at least 12 table indexes and 3 triggers; - Many of our
> customer servers uses AMD64 processors running Red Hat Enterprise
> (with EXT3), but we have some using Xeon processors and Windows 2003
> Server (NTFS), and the issues still the same; - All servers have at
> least 1 Gb of dedicated RAM, with no virtualization; - All servers
> uses at least 2 disks on RAID 0 (Ultra-SCSI disks); - Database
> encoding: LATIN 1. *The issue topics:* 1) As the database grows on our
> customers, lower performance occurs. After one week of use, the I/O on
> database is extremely high. It appears that VACUUM FULL and/or VACUUM
> ANALYZE doesn't work on this databases. 2) We have a very complex view
> mount on other views. When we cancel a simple SELECT on this top-level
> view (expecting return a max. of 100 rows for example) the PostgreSQL
> process starts a infinite loop (we left more than 4 days and the loop
> doesn't stops), using 100% of all processors on the server. 3) On
> these servers, the disk usage grows very small than the records loaded
> into database. For example, after restoring a backup, the database DIR
> have about 40 Gb (with all indexes created). After one week of use,
> and about 500,000 new records on tables, the database size grows to
> about 42 Gb, but on Windows 2003 Server we can see the high
> fragmentation of disk (maybe on linux this occurs too). 4) VACUUM FULL
> and/or VACUUM ANALYZE appears to doesn't work perfectly. *The
> temporary (but extensive) solution:* I have seem that one action could
> solve this problems for a short time. It is as follows: 1) Create a
> database dump into a .SQL plain text file; 2) Drop the OTLP original
> database; 3) Create a new database using the original name; 4)
> Restores the .SQL file with psql. The cost of use PostgreSQL database
> on our sw came to a very high levels, so we're evaluating the same
> database schema and data on other databases as IBM DB2 9, and these
> issues doesn't work. However, we need solve this problems on PgSQL, as
> exists many customers who will not migrate to DB2. Anyone can help me?
> ------------------------------------------------------------------------
> View this message in context: PostgreSQL performance issues
> <http://www.nabble.com/PostgreSQL-performance-issues-tf4670379.html#a13341797>
> Sent from the PostgreSQL - hackers mailing list archive
> <http://www.nabble.com/PostgreSQL---hackers-f780.html> at Nabble.com.
a) I didn't see a reindex in your mail. That's why a backup and a
restore work and a vacuum doesn't
http://www.postgresql.org/docs/current/static/sql-reindex.html
Do this at least daily with that many inserts

b) Which version do you run of postgres? pg82 is a lot faster then
previous versions, pg83 will be again a lot faster when it comes out

c) Fragmentation won't happen on linux as far as I know, but if you want
to be faster why do you use the slowest filesystem ever?
ReiserFS is a lot better. On windows => frequent defragmenting, it's
said that with NTFS it's not necessary anymore, but I don't believe it.