Re: PostgreSQL performance issues - Mailing list pgsql-hackers

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.