On Wed, Feb 15, 2012 at 18:46, Asher Hoskins <asher@xxxxxxxxxx> wrote: > My problem is that the autovacuum system isn't keeping up with INSERTs and I > keep running out of transaction IDs. This is usually not a problem with vacuum, but a problem with consuming too many transaction IDs. I suspect you're loading that data with individual INSERT statements with no explicit transaction management -- which means every data-modifying query gets its own transaction ID. In most applications, the simplest solution is batching up lots of INSERTs (say, 10k) and run them all in a single transaction between BEGIN and COMMIT -- thus consuming just 1 transaction ID for 10k rows. You could also look at multi-row inserts or the COPY command to get better performance. Here's an overview of different data loading approaches: http://www.depesz.com/2007/07/05/how-to-insert-data-to-database-as-fast-as-possible/ But it's also possible that transaction IDs are consumed by other queries. Any PL/pgSQL procedure that makes use of exception handling consumes a transaction ID. So does every SAVEPOINT command. > I'm a complete newby when it comes to PostgreSQL system settings and it > isn't obvious to me what I'd need to change to improve the autovacuum. Is > there any way to manually freeze the rows of the table after they've been > inserted so that the autovacuum doesn't need to touch the table? VACUUM FREEZE is the command. :) You may need to tune the vacuum settings to be more aggressive if you want it to run faster (reducing cost_delay, increasing cost_limit). But I don't have much experience with these myself. http://www.postgresql.org/docs/8.4/static/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST Note that vacuum freeze is only necessary when you have a shortage of transaction IDs. Normally Postgres doesn't vacuum insert-only tables at all (it just analyzes them). VACUUM FULL is extremely inefficient in PostgreSQL 8.4 and older. > The server is running PostgreSQL 8.4.2 (under FreeBSD) at the moment but it > wouldn't be a problem to upgrade to 9.1 if that was helpful. Upgrading to 9.1 certainly lets you take advantage of several performance improvements, but I don't know how useful those are in your workload. ---- Additionally, it's often a good idea to partition large tables into smaller partitions (e.g. separate partition for each day/week worth of data). This way maintenance commands (VACUUM, ANALYZE) don't have to scan the whole huge table, but work with smaller individual tables, and you can drop chunks using a simple DROP TABLE. BUT it also has its problems: some types of query optimizations are impossible with partitioned tables (version 9.1 relaxed those limitations somewhat). So if you find your current performance to be sufficient, then this might not be worth doing. Regards, Marti -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
