On Fri, Dec 4, 2009 at 4:03 PM, Andreas Thiel <andreas.thiel@xxxxxxxxxx> wrote: > Hi All, > > Maybe some questions are quite newbie ones, and I did try hard to scan > all the articles and documentation, but I did not find a satisfying > answer. > > I'm running PostgreSQL 8.3.6 on a 32-Bit Centos 4 machine (which I > probably should update to 64 Bit soon) Yeah, 64 bit is worth the migration. > I have the same information stored in an Oracle 10g DB which consumes > only 70G data and 2G for indexes. The schema may be better optimized, > but for sure there is a table with 4 billion rows inside as well. So > it's about 10x smaller in disk space than PgSQL. I wonder why. You've probably got a bloated data store. > Is such disk usage for indexes expected? What can I do to optimize? I > could not run yet a VACUUM on result_orig, as I hit into max_fsm_pages > limit (still trying to adjust that one). I tried REINDEX, it didn't > change anything. OK, you've got a problem with max_fsm_pages not being big enough, so pretty much anything you do vacuum wise is a wasted effort until you fix that. > ### My Issue No. 2: relpages and VACUUM > I have another table "test" which is - as starting point - created by > INSERTs and then UPDATE'd. It has the same columns and roughly the same > number of rows as table test_orig, but consumes 160 times the number of > pages. I tried VACUUM on this table but it did not change anything on > its relpages count. Maybe this is just because VACUUM without FULL does > not re-claim disk space, i.e. relpages stays as it is? I did observe > that after VACUUM, a REINDEX on this table did considerably shrink down > the size of its indexes (test_test_id, test_lt_id). Vacuum does NOT shrink tables. It reclaims free space to be reused. If you have a large table that's 95% dead space regular vacuum can't do anything for you. Note that not having a large enough free space map is likely the cause of your problems. > ### My Issue No 3: VACCUM FULL out of memory > I tried to do a VACCUM FULL on the two tables (test, result_orig) > mentioned above. In both cases it fails with a very low number on out of > memory like this: > > ERROR: out of memory > DETAIL: Failed on request of size 224. It's likely doing a LOT of other memory allocations before this one fails. 64 bit and more memory might help. > I use these kernel settings: > kernel.shmmni = 4096 > kernel.shmall = 2097152 > kernel.shmmax = 2147483648 None of those have anything to do with how much vacuum full can allocate really. > vm.overcommit_memory = 2 This will keep vacuum from allocating memory that may be available but is already "spoken for" so to speak. How much memory dos your machine have? > And these postgresql.conf settings: > shared_buffers = 512MB # min 128kB or > max_connections*16kB > temp_buffers = 128MB # min 800kB > max_prepared_transactions = 1024 # can be 0 or more > work_mem = 16MB # min 64kB > maintenance_work_mem = 256MB # min 1MB > max_stack_depth = 8MB # min 100kB > max_fsm_pages = 70000000 # min max_fsm_relations*16, 6 Wow, and you're still running out? Do you have autovacuum turned off or something? > What's going wrong here? I know, one should not use VACUUM FULL, but I > was curious to see if this would have any impact on relpages count > mentioned in Issue 2. Vacuum full is perfectly cromulent, assuming you know the downsides and avoid using it for regular periodic maintenance. For instance, my main production database had a drive go out a few days ago, and it held a query up for several days, during which vacuum couldn't reclaim space freed up during that time. I set a maintenance window last night and ran vacuum full plus reindex on a couple of tables that had gotten particularly bloated. > ###My Issue No. 4: Autovacuum > I have the feeling that Autovacuum is not really running, else why are > tables and indexes growing that much, especially "test" table? Could it be a single long running query or transaction is keeping vacuum from reclaiming free space? check pg_stat_activity for long running queries or transactions. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
