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.
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
