Tag Info

Hot answers tagged

13

Eelke is almost certainly correct that your locking is blocking autovacuum. Autovacuum is designed to give way to user activity, deliberately. If those tables are locked, autovacuum cannot vacuum them. For posterity, however, I wanted to give an example set of settings for hyper-aggressive autovacuum, since the settings you gave don't quite do it. Note ...


13

VACUUM is only needed on updated or deleted rows in non-temporary tables. Obviously you're doing lots of INSERTs but it's not obvious from the description that you're also doing lots of UPDATEs or DELETEs. These operations can be tracked with the pg_stat_all_tables view, specifically the n_tup_upd and n_tup_del columns. Also, even more to the point, there ...


10

I see nothing in your question that auto-vacuum would not take care of. It largely depends on the pattern or your writing activities. You mention 3 million new rows per week, but INSERT (or COPY) is largely irrelevant for VACUUM. UPDATE and DELETE are relevant, especially if they pick random rows (as opposed to bulks of data most likely occupying pages ...


8

To return space to the OS, use VACUUM FULL. While being at it, I suppose you run VACUUM FULL ANALYZE. I quote the manual: FULL Selects "full" vacuum, which can reclaim more space, but takes much longer and exclusively locks the table. This method also requires extra disk space, since it writes a new copy of the table and doesn't release the ...


8

The key words here are: "heavily updated" "in the table for 2-3 hours". Point 1. is indication for a lower fill factor, while 2. is the opposite. It helps performance if multiple row versions are stored on the same data page. H.O.T. updates would achieve that. Read here or here. They need some wiggle room on the data page - like dead tuples or space ...


7

There are a least three major reasons for why you should upgrade to a more recent version, preferably to the current version 9.1. 1.) As has been mentioned by @a_horse_with_no_name the autovacuum mechanism has been improved in many places since version 8.2. 2.) You don't see frequent DELETEs, so the table bloat most probably comes from UPDATEs as @Milen ...


5

VACUUM does not necessarily make unused space available to the filesystem, it merely makes the blocks re-usable for further INSERTs (or UPDATEs). If I'm not mistaken the only way to also actively reduce the size on the file system would be a VACUUM FULL, but beware that needs an exclusive lock on the table(s). Do you expect the table to not get any new ...


5

Yes it is a locking issue. According to this page (non full) VACUUM needs SHARE UPDATE EXCLUSIVE access which is blocked by the lock level you are using. Are you certain you need this lock? PostgreSQL is ACID compliant so concurrent writes are in most cases not a problem as PostgreSQL will abort one of the transactions if a serialization violation would ...


5

This is very hard to determine. You can tune autovacuuming to be more agressive or to be milder. But when set to mild and it is lagging behind and the base I/O load is too high, it can happen that it never reaches a proper vacuumed state - then you see the process running and running and running. Furthermore, later PostreSQL editions have much improved ...


4

If you don't have concurrent transactions that would prohibit you from getting an exclusive lock on the table, I would: Select the (relatively few) surviving rows into a temporary table. Make sure you have enough RAM available for the temporary tables (for this session only). Read about temp_buffers in this related answer: Optimizing bulk update ...


4

Just to see which tables qualify for autovacuum at all, the following query may be used (based on http://www.postgresql.org/docs/current/static/routine-vacuuming.html). Note however, that the query does not look for table specific settings: SELECT psut.relname, to_char(psut.last_vacuum, 'YYYY-MM-DD HH24:MI') as last_vacuum, ...


4

The space should be freed as soon as the index is dropped. Possible explanations for what you show above are: The index is in a tablespace on a different filesystem from the one you're checking. The OS/filesystem has some lag in providing up-to-date free space information. Something else is eating the free space as soon as it becomes available. Something ...


4

You're probably not running autovacuum. You might want to enable that. But more importantly, you should consider upgrading to the 9.1.x branch.


4

Increasing the number of autovacuum processes and reducing the naptime will probably help. Here is the configuration for a PostgreSQL 9.1 that I use on a server that stores backup information and as a result gets a lot of insert activity. http://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html autovacuum_max_workers = 6 # ...


4

VACUUM rewrites the entire block, efficiently packing the remaining rows and leaving a single contiguous block of free space (though this space isn't zeroed and the physical disk file might contain the remnants of deleted rows which of course are in no way visible to the database user). test schema: --#psql postgres postgres select oid from pg_database ...


4

VACUUM FULL rewrites the entire contents of the table into a new disk file with no extra space, allowing unused space to be returned to the operating system. This method also requires extra disk space, since it writes a new copy of the table and doesn't release the old copy until the operation is complete. Usually this should only be used when a ...


3

Since you don't have enough space to run a vacumm or rebuild, you can always rebuild your postgresql databases by restoring them. Restoring the databases, tables, indexes will free up space and defragment. Afterwards, you can setup automated maintenance to vacumm your databases on a regular basis. 1 Backup all of the databases on your postgresql server ...


3

1) If you don't count your own time as a resource, then you should always be able to hand-craft a vacuum schedule which uses fewer total resources than autovacuum does. If you do count your own time, this is almost surely not worthwhile. 2) Other than manually or algorithmically turning it on or off, no. Nor would it make sense to do that. A database ...


3

To check what CLUSTER does, I took a table fo mine from an earlier experiment which basically contained the first 10 million positive integers. I already deleted some rows and there is an other column as well but these only affect the actual table size, so it is not that interesting. First, having run VACUUM FULL on the table fka, I took its size: \dt+ ...


3

On one hand, you can have a look at one of my previous answers to see how you can keep a table size more or less steady. There you will find a solution with triggers - of course, this can be solved using a cron job as well. In the latter case I would first check if the row number exceeded a certain limit and the either delete the oldest rows or drop a ...


3

Here's a short concise answer. Vacuum full takes out an exclusive lock and rebuilds the table so that it has no empty blocks (we'll pretend fill factor is 100% for now). Vacuum freeze marks a table's contents with a very special transaction timestamp that tells postgres that it does not need to be vacuumed, ever. Next update this frozen id will disappear. ...


3

If you use anything but CLUSTER / VACUUM FULL / pg_repack you need to make sure there are no concurrent writes to the table. Take an exclusive lock on the table and do everything in a single transaction or, better yet, shut out all connections to avoid concurrent changes. TABLESPACE Yes, your last idea could work. Create a new tablespace on the other disk. ...


2

Before you fiddle with cron jobs and other curiosities you can adjust the autovacuum default settings just inside PostgreSQL. This can be done globally or individually for each table.


2

Because you haven't specified your PostgreSQL version it's hard to answer. VACUUM has been improved in many ways on newer versions. Presuming you're on 9.0 or 9.1 - so you don't need a max_fsm_pages setting, you have the improved VACUUM FULL, you have the visibility map, etc - then subsequent VACUUM operations should be quite fast. However, you should not ...


2

I don't know what resources you're getting this from. Not just that PgAdmin page given some of what you're saying. The information you're relying on is either outdated or incomplete; all this is pretty much unnecessary. Make sure that autovacuum is keeping up with the database workload and you're pretty much done. These days you should not generally need to ...


2

In current versions of PostgreSQL, you can look at the *pg_stat_activity* view to find autovacuum tasks. They will have *current_query* fields that start with "Mark autovacuum entries in pg_stat_activity that look like this: autovacuum: VACUUM t A query to count how many of those you have might look like this: SELECT count(*) FROM pg_stat_activity WHERE ...


2

I agree with ETL that there is no short answer. Size is not the only thing that matters - we run quite large PostgreSQL OLTP Databases (with some tables > 100.000.000 rows) under heavy load and currently we rely on autovacuum only. Yet, two things seem important to me: There seems to be a consensus, that autovacuum should never be switched off, unless you ...


2

For the general settings use: select * from pg_settings where name like 'autovacuum%' for table specific settings, check out the column reloptions in pg_class: select relname, reloptions from pg_class (you probably want to join that to pg_namespace to limit this to a specific schema).


2

1) I would have tried cluster followed by analyze. My only hesitation is I am not 100% sure what happened here. Is it possible there was some index corruption as well? Reindex might have helped? Given that the stats entries were way off, is it possible something was corrupt elsewhere regarding the relation's OID? 2) I have no idea. I have never seen ...



Only top voted, non community-wiki answers of a minimum length are eligible