Re: [HACKERS] 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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
Simon Riggs schreef: On Fri, 2007-10-12 at 07:17 +0100, Simon Riggs wrote: On Fri, 2007-10-12 at 01:24 -0400, Alvaro Herrera wrote: Michael Paesold escribió: Simon Riggs wrote: Hmm, I am not sure we are there, yet. Autovacuum does take extra care to vacuum tables nearing xid wrap-around, right? It even does so when autovacuum is disabled in the configuration. So in case a vacuum is needed for that very reason, the vacuum should *not* be canceled, of course. So we don't really need the information, whether the AV worker is doing VACUUM or ANALYZE, but whether it is critical against xid wrap-around. Could that be done as easily as in Alvaro's patch for distinguishing vacuum/analyze? Alvaro? Yes, I think it is easy to mark the is for xid wraparound bit in the WorkerInfo struct and have the cancel work only if it's off. However, what I think should happen is that the signal handler for SIGINT in a worker for xid wraparound should not cancel the current vacuum. Instead turn it into a no-op, if possible. That way we also disallow a user from cancelling vacuums for xid wraparound. I think he can do that with pg_cancel_backend, and it could be dangerous. I think that is dangerous too because the user may have specifically turned AV off. That anti-wraparound vacuum might spring up right in a busy period and start working its way through many tables, all of which cause massive writes to occur. That's about as close to us causing an outage as I ever want to see. We need a way through that to allow the user to realise his predicament and find a good time to VACUUM. I never want to say to anybody nothing you can do, just sit and watch, your production system will be working again in no time. Restart? no that won't work either. I think the best way to handle this is to have two limits. First limit attempts to autovacuum, but can be cancelled. When we hit second limit, sometime later, then autovacuum cannot be cancelled. That would give us a breathing space if we need it. Just a few thoughts: 1) In the postgresql.conf you can define if you use autovacuum. You make a parameter that states a time of day. If autovacuum is canceled once and not performed manually before that time, then it executes at that time (or just after the next system restart after that time). So you ensure that it isn't delayed indefinitely and you execute it on a time the database is normally not under a heavy load. As a standard value you could take 2am in the morning or so. 2) I you can cancel an autovacuum that way, could you prevent it by a statement to start executing in the first place, and then restart execution by another statement. There are a few situations where vacuuming is entirely pointless Example: a) Everyone logs out, upgradeprocedure of db is started b) drop indexes c) add tables/change tables/add columns/change columns d) convert data e) drop tables/drop columns f) add indexes g) vacuum full analyze h) Everyone starts new app BTW: I like pg83, allready looking for implementation when it hits the shelves...
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
Simon Riggs schreef: On Fri, 2007-10-12 at 11:44 +0200, Michael Paesold wrote: Simon Riggs wrote: On Fri, 2007-10-12 at 01:24 -0400, Alvaro Herrera wrote: Yes, I think it is easy to mark the is for xid wraparound bit in the WorkerInfo struct and have the cancel work only if it's off. However, what I think should happen is that the signal handler for SIGINT in a worker for xid wraparound should not cancel the current vacuum. Instead turn it into a no-op, if possible. That way we also disallow a user from cancelling vacuums for xid wraparound. I think he can do that with pg_cancel_backend, and it could be dangerous. I think that is dangerous too because the user may have specifically turned AV off. That anti-wraparound vacuum might spring up right in a busy period and start working its way through many tables, all of which cause massive writes to occur. That's about as close to us causing an outage as I ever want to see. We need a way through that to allow the user to realise his predicament and find a good time to VACUUM. I never want to say to anybody nothing you can do, just sit and watch, your production system will be working again in no time. Restart? no that won't work either. You are probably right that VACUUM going full-steam is a bad idea in most situations. Except for anti-wraparound vacuum, cancellation seems the most reasonable thing to do. Because autovacuum will usually pickup the table in time again. Yeh, if we do have to do the second emergency anti-wraparound, then that should be at full speed, since there's nothing else to do at that point. The only problem I would see is if someone has an application that does a lot of schema changes (doesn't sound like a good idea anyway). In that case they would better issue manual vacuums on such tables. I can't see a use case for regular DDL as part of an application, on an otherwise integral table (lots of updates and deletes). As part of an application there's no use. As part of an upgrade between 2 different versions of that application there is. And that's exactly the kind of situation where temporary disabling autovacuum could become handy.
Re: [HACKERS] Possible bugreport 8.3 beta1 on Win32: Looking like a deadlock with AutoVacuum
Alvaro Herrera schreef: Deblauwe Gino wrote: OS: Windows XP Pro SP2 CPU: AMD Athlon 64 3500+ RAM: 2GB DB: PostgreSQL 8.3beta1, compiled by Visual C++ build 1400 I've come to the conclusion that it seems like a deadlock occurs when dropping a column in a table the same moment that table is autovacuumed. Example: ALTER TABLE bondetail DROP COLUMN btw; (user=gino, 16252 records) deadlocks with VACUUM ANALYZE public.bondetail; (user=postgres) Does it really deadlock, or is it just locked waiting for the vacuum to finish? If it deadlocks you should get a message about it and a transaction rollback. Otherwise you should be able to see the ungranted lock in pg_locks. Also it's not clear if autovacuum is involved, or you invoked the VACUUM ANALYZE manually. Can you clarify? No it just looks like a deadlock on first sight. It just takes a very long time. In this case, it takes 10 minutes instead of 5 seconds to execute the query. I was only able to reproduce this on 'ALTER TABLE x DROP COLUMN y;' queries. Those things happen while upgrading our software to a newer version. The more common instructions (SELECT/INSERT/UPDATE/DELETE) work fine the same as adding/changing columns/tables. Greetings Deblauwe Gino
[HACKERS] Possible bugreport 8.3 beta1 on Win32: Looking like a deadlock with AutoVacuum
OS: Windows XP Pro SP2 CPU: AMD Athlon 64 3500+ RAM: 2GB DB: PostgreSQL 8.3beta1, compiled by Visual C++ build 1400 I've come to the conclusion that it seems like a deadlock occurs when dropping a column in a table the same moment that table is autovacuumed. Example: ALTER TABLE bondetail DROP COLUMN btw; (user=gino, 16252 records) deadlocks with VACUUM ANALYZE public.bondetail; (user=postgres) If you wait a very long time, it goes on, the quick method is to cancel the VACUUM command. If you need some more info, let me know. Greetings Deblauwe Gino ---(end of broadcast)--- TIP 6: explain analyze is your friend
