[GENERAL] Fighting the autovacuumer (to prevent wraparound)
Hi all, We partition the data in postgres in a per-month basis and run a script to delete old partitions. Sometimes this script fails and the delete doesn't happen because of a deadlock, today I noticed that it was the autovacuumer that fighting with the script. Is it possible to stop the autovacuumer temporarily (and cancel any on going autovacuum) so that my script can remove the table that the autovacuumer wants to vacuum? I'm on 9.1.4 if it matter. Cheers, -- Michael Graham mgra...@bloxx.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fighting the autovacuumer (to prevent wraparound)
On Thu, 2013-10-03 at 11:53 -0700, bricklen wrote: On Thu, Oct 3, 2013 at 11:48 AM, Michael Graham mgra...@bloxx.com wrote: Hi all, We partition the data in postgres in a per-month basis and run a script to delete old partitions. Does delete = drop? No I'm truncating the tables for that partition (sorry I should have made that clear in my original message). Sometimes this script fails and the delete doesn't happen because of a deadlock, today I noticed that it was the autovacuumer that fighting with the script. Are you uninheriting the partition before attempting to get rid of it? Eg. ALTER TABLE your_partition NO INHERIT the_parent_table; I'm not no, will that help? I see that the autovacuumer is vacuuming the actual partition that I want to truncate to the parent but I do do an analyse on the parent table after the truncate but in the same transaction. Thanks, -- Michael Graham mgra...@bloxx.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Table containing only valid table names
Hi all,
I'm trying to create a table that contains only valid table names. I'm
currently using reglass for the field type which works fine for when you
are adding to the table (i.e. you can't add invalid tablesnames to the
table). But it is still possible to leave the table in an invalid state
after doing a drop table.
So for example:
= CREATE TABLE table_list (tablename regclass);
CREATE TABLE
= INSERT INTO table_list VALUES ('foo');
ERROR: relation foo does not exist
LINE 1: INSERT INTO table_list VALUES ('foo');
^
= CREATE TABLE foo (a int);
CREATE TABLE
= INSERT INTO table_list VALUES ('foo');
INSERT 0 1
= DROP TABLE foo;
DROP TABLE
= SELECT * FROM table_list;
tablename
---
122860
(1 row)
Does any one have any ideas that could be used to stop this from
happening? I'm not really in the position to have different users for
the modification of the table_list and the drops so I don't think I can
use different roles.
I'm pretty sure I can't do what I need as postgres doesn't support
triggers on DDL but maybe I'm wrong.
Cheers,
--
Michael Graham
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] unnest and string_to_array on two columns
Hi all, I'm trying to migrate an old (and sucky) schema to a new one and I'm having some difficulties coming up with a sane select. I have basically id, a, and b where a and b contain a list of flags like id | a| b | 1 | abc | abcdef | and what to convert this to multiple ids with single flags, like: id | a| b| -- 1 | a| a| 1 | b| b| 1 | c| c| 1 | NULL | d| 1 | NULL | e| 1 | NULL | f| My first attempt was SELECT id, unnest(string_to_array(a,NULL)), unnest(string_to_array(b,NULL)) FROM foo; But this causes the shorter string to be repeated until it is the same length as the shorter string. In the end I have managed to get the behaviour that I want but the select is horrible: SELECT COALESCE(aa.id,bb.id) AS id, aa.unnest AS a, bb.unnest AS b FROM ( SELECT *, row_number() OVER() FROM ( SELECT id,unnest(string_to_array(a,NULL)) FROM foo ) AS a ) AS aa FULL JOIN ( SELECT *, row_number() OVER() FROM ( SELECT id,unnest(string_to_array(b,NULL)) FROM foo ) AS b ) AS bb ON aa.row_number=bb.row_number AND aa.id=bb.id; So I was wondering if anyone had any better solutions. Thanks, -- Michael Graham mgra...@bloxx.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Vacuum as easily obtained locks
Hi all, I have an application that is reading from a queue table, as part of my testing I stressed the table to check performance, but after the test was completed I have the a very large empty table: SELECT relname, pg_size_pretty(pg_relation_size(oid)) AS size, reltuples::bigint FROM pg_class; relname | size | reltuples +--+--- logdata5queue | 142 GB | 0 From reading the documentation I see that postgres would return this space to that system after a normal vacuum if one or more pages at the end of a table become entirely free and an exclusive table lock can be easily obtained. What does easily obtained mean in this context? Would my applications constant polling of the queue mean that the lock could not be easily obtained? Cheers, -- Michael Graham mgra...@bloxx.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Vacuum as easily obtained locks
On Wed, 2011-08-03 at 09:03 -0500, Andy Colson wrote: Depending on how long you ran your test, and the conf settings, and the size of your database, autovacuum may never have even tried. I know that the vacuum is definitely running (in fact isn't it the vacuum that set the reltuples to 0?), the test was running for a number of weeks but this is the first time it has emptied the queue. You can take a look on pg_stat_all_tables, under the *vacuum columns, to see if it ever even ran. (a date in last_autovacuum would indicate a successful run, it wont show failures). It should, however, write something to the system log. I recall something like autovacuum canceled because...something or other type a message. Yeah it said it last ran yesterday (and is currently running now), but I did I notice in the log: 2011-08-02 19:43:35 BST ERROR: canceling autovacuum task 2011-08-02 19:43:35 BST CONTEXT: automatic vacuum of table traffic.public.logdata5queue Which is interesting if not particularly useful. While you are running a test, you could keep an eye on the log to see if you get any of those messages. I think that would indicate autovacuum could not get a lock. You can also watch pg_stat_activity during the test, current_query will show you what table is being vacuumed. I'm pretty certain the autovacuumer is running while the test is on going what I can't work out is under what circumstances it will be able to return unused space to the OS in when it can't. Cheers, -- Michael Graham mgra...@bloxx.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Vacuum as easily obtained locks
On Wed, 2011-08-03 at 10:17 -0400, Tom Lane wrote: Michael Graham mgra...@bloxx.com writes: Would my applications constant polling of the queue mean that the lock could not be easily obtained? Very possible, depending on what duty cycle is involved there. Hmm. The clients aren't that aggressive, especially when they failed to find data on a previous select, there are 4 clients, they each poll every 10 seconds and the select runs in 1ms. It might be worth noting that they don't ever disconnect from the server, but I assume that is not an issue for getting the AccessExclusiveLock on the table? My worry at the moment is that because the table is so large the vacuum takes a very long time to run (one has been running for 5hrs) and I assume it will continue to run until it is able to get the AccessExclusiveLock is so desperately wants. Cheers, -- Michael Graham mgra...@bloxx.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Vacuum as easily obtained locks
On Wed, 2011-08-03 at 11:40 -0400, Tom Lane wrote: The other problem is that once autovacuum has gotten the lock, it has to keep it for long enough to re-scan the truncatable pages (to make sure they're still empty). And it is set up so that any access to the table will kick autovacuum off the lock. An access pattern like that would very likely prevent it from ever truncating, if there are a lot of pages that need to be truncated. (There's been some discussion of modifying this behavior, but nothing's been done about it yet.) Ah! This looks like it is very much the issue. Since I've got around 150GB of data that should be truncatable and a select every ~2s. Just to confirm would postgres write: 2011-08-03 16:09:55 BST ERROR: canceling autovacuum task 2011-08-03 16:09:55 BST CONTEXT: automatic vacuum of table traffic.public.logdata5queue Under those circumstances? Cheers, -- Michael Graham mgra...@bloxx.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Rearranging simple where clauses
Hi, I was playing around with some sql in postgres and got to wondering why the optimiser can't figure out that rearranging some expressions can result in massive improvements in the queue plan. For example id + 5 100 compared with id 100 - 5. Is it simply that no one has go around to doing it or is there some deeper reasons? It's not really important I'm just curious. Cheers, -- Michael Graham mgra...@bloxx.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rearranging simple where clauses
On Wed, 2011-05-04 at 10:49 -0400, Tom Lane wrote: Well, it'd require a very large amount of type-specific/operator-specific knowledge, and it's not clear what would drive the planner towards doing useful rearrangements rather than counterproductive ones, and the number of real-world queries where it'd actually help doesn't seem to be that large. I've seen one or two complaints about that sort of thing, but it's way way down the list of serious problems. I did suspect that the answer would be that the difficulty out ways the benefit. But in terms of driving the planner don't we always want to be looking to move all the constants to one side of the expression since the planner seems to like those? -- Michael Graham mgra...@bloxx.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rearranging simple where clauses
On Wed, 2011-05-04 at 11:49 -0400, Tom Lane wrote: Well, you failed to show us any concrete examples of the cases you were looking at, but no I don't think the planner necessarily likes all the constants on one side. Most likely the win cases are where one side of a WHERE-condition operator exactly matches an index, so you'd need to be looking for places where rearrangement could make that happen. The reason I never showed you any was because I don't have any I was just curious. But yeah making one side match an index exactly is probably the biggest win. -- Michael Graham mgra...@bloxx.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Transaction wraparound vacuum synchronicity
Hi all, I have a database with a number of tables that are partitioned monthly, after that the tables are mostly read only (on rare occasions we may delete from a table but normally we just drop the partitions). Recently I've noticed that we have a lot of these tables are vacuumed around the same time, after a little big of digging I've realised that postgres is vacuuming them to stop xaction wrap around. So for example in a few million xactions (later today) postgres is going to want to vacuum 37 tables for just this reason. I know I can fiddle autovacuum_freeze_max_age and vacuum_freeze_min_age to change how regularly the tables have this occur, and I can do this on a per table basis in pg_autovacuum (yes this means I'm running an old version, version 8.2) but what I'm wondering is how other people are breaking this synchronisation? Should I add a random value to the freeze_max_age for all the old tables when I start a new month? Or do the same with the freeze_min_age? Perhaps I should just force a vacuum on some of the tables the break it? Cheers, -- Michael Graham mgra...@bloxx.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
