Re: [GENERAL] preventing ERROR: multixact "members" limit exceeded
On Tue, May 17, 2016 at 10:40 AM Alvaro Herrera wrote: > In 9.4, not really. In 9.5 there's a function mxid_age() that gives you > the age of a multixact, so you'd grab the oldest from > pg_database.datminmxid and compute the age of that one. Going from the > oldest multi to the oldest offset cannot be done without an additional > function, however. It's much easier to keep track of the oldest file in > $PGDATA/pg_multixact/members/; what you really need to care about is the > size of the "hole" between the newest and the oldest files there. Once > newest starts to stomp on oldest, you're screwed. Thank you Alvaro. We're beginning to plan our rollout of 9.5.
Re: [GENERAL] preventing ERROR: multixact "members" limit exceeded
On Mon, May 16, 2016 at 6:18 PM Alvaro Herrera wrote: > Not really. Your best bet is to reduce the > autovacuum_multixact_freeze_min_age limit, so that vacuums are able to > get rid of multixacts sooner (and/or reduce > autovacuum_multixact_freeze_table_age, so that whole-table vacuuming > takes place earlier). Thank you very much. I will adjust those settings. Is there a way, something similar to keeping an eye on `age(relfrozenxid)`, that I can watch this and keep an eye on it before it becomes a problem?
[GENERAL] preventing ERROR: multixact "members" limit exceeded
This is Postgres 9.4.4. Custom settings are [in this gist]( https://gist.github.com/skehlet/47c7f92daa0bd3d1a3aee2bb001da140). This is a new one for me, one of our bigger (~2.2TB) databases started having the following error: > Caused by: org.postgresql.util.PSQLException: ERROR: multixact "members" limit exceeded > Detail: This command would create a multixact with 2 members, but the remaining space is only enough for 0 members. > Hint: Execute a database-wide VACUUM in database with OID 33554 with reduced vacuum_multixact_freeze_min_age and vacuum_multixact_freeze_table_age settings. We followed those VACUUM instructions, and now everything's back to normal. Now it's just about preventing this. Our best guess at this point is the autovacuums aren't working fast enough. Sure enough this instance has our old values for: autovacuum_vacuum_cost_delay: 20ms autovacuum_vacuum_cost_limit: 200 We've since started using: autovacuum_vacuum_cost_delay: 10ms autovacuum_vacuum_cost_limit: 2000 We'll be updating those settings as soon as possible. Just looking for some expert eyes on this problem. Are we on the track track? I.e. is making the autovacuumer run more aggressively our best bet to avoid this issue? Thank you, Steve
Re: [GENERAL] which db caused postgres to stop serving due to wraparound prevention?
Maybe my custom settings are relevant. Here they are in a gist: https://gist.github.com/skehlet/08aeed3d06f1c35bc780 On Thu, Mar 17, 2016 at 11:47 AM Steve Kehlet wrote: > Sorry, seems like such a noob problem, but I'm stumped. This is postgres > 9.4.5. I'll post my custom settings if desired but I don't think they're > needed. > > We recently had an issue where the autovacuumer wasn't starting because > postgres couldn't resolve the hostname 'localhost' (we had bad perms on > /etc/hosts). We're still working on getting that fixed on all affected > boxes. > > In the meantime: today, one particular database unexpectedly stopped > serving with this error: > > 2016-03-17 12:31:52 EDT [5395]: [787-1] ERROR: database is not accepting > commands to avoid wraparound data loss in database with OID 0 > 2016-03-17 12:31:52 EDT [5395]: [788-1] HINT: Stop the postmaster and > vacuum that database in single-user mode. > You might also need to commit or roll back old prepared > transactions. > > What has me confused is I ran the following command to keep an eye on > this, and it seemed fine, the max(age(datfrozenxid)) was only about 330 > million: > > postgres=# select datname,age(datfrozenxid) from pg_database; > datname |age > ---+--- > mydb | 330688846 > postgres | 215500760 > template1 | 198965879 > template0 | 146483694 > mydb2 | 175585538 > (5 rows) > > We shutdown postgres, started it in single user mode, and VACUUMed each > database. Then postgres started up fine, and the crisis is averted, for now. > > However my understanding must be wrong: I thought we could just look > around for max(age(datfrozenxid)), make sure it's "low" (<2 billion), and > be sure that this wouldn't happen. What am I misunderstanding? > > And then, I don't know which db has OID 0? > > postgres=# SELECT oid,datname from pg_database; > oid | datname > +--- > 16422 | mydb > 12921 | postgres > 1 | template1 > 12916 | template0 > 1575433129 | mydb2 > (5 rows) > > Thank you for your help! > >
[GENERAL] which db caused postgres to stop serving due to wraparound prevention?
Sorry, seems like such a noob problem, but I'm stumped. This is postgres 9.4.5. I'll post my custom settings if desired but I don't think they're needed. We recently had an issue where the autovacuumer wasn't starting because postgres couldn't resolve the hostname 'localhost' (we had bad perms on /etc/hosts). We're still working on getting that fixed on all affected boxes. In the meantime: today, one particular database unexpectedly stopped serving with this error: 2016-03-17 12:31:52 EDT [5395]: [787-1] ERROR: database is not accepting commands to avoid wraparound data loss in database with OID 0 2016-03-17 12:31:52 EDT [5395]: [788-1] HINT: Stop the postmaster and vacuum that database in single-user mode. You might also need to commit or roll back old prepared transactions. What has me confused is I ran the following command to keep an eye on this, and it seemed fine, the max(age(datfrozenxid)) was only about 330 million: postgres=# select datname,age(datfrozenxid) from pg_database; datname |age ---+--- mydb | 330688846 postgres | 215500760 template1 | 198965879 template0 | 146483694 mydb2 | 175585538 (5 rows) We shutdown postgres, started it in single user mode, and VACUUMed each database. Then postgres started up fine, and the crisis is averted, for now. However my understanding must be wrong: I thought we could just look around for max(age(datfrozenxid)), make sure it's "low" (<2 billion), and be sure that this wouldn't happen. What am I misunderstanding? And then, I don't know which db has OID 0? postgres=# SELECT oid,datname from pg_database; oid | datname +--- 16422 | mydb 12921 | postgres 1 | template1 12916 | template0 1575433129 | mydb2 (5 rows) Thank you for your help!
Re: [GENERAL] how to investigate GIN fast updates and cleanup cycles?
I wanted to follow up and report we altered the index in question and turned off fastupdate and we have not seen the issue I originally reported since. Just for the record I did: ALTER INDEX my_index SET (fastupdate=off); SET vacuum_cost_delay=0; VACUUM VERBOSE my_table_with_that_index; We looked into the performance impact on the inserts/updates and decided it was better for us to take the tiny hit on them and thus avoid these occasional blockages caused by the pending list cleanup cycle. Thank you Jeff and Tom for the help! I have a number of todos still from this too, including adjusting our autovacuum settings and looking into improving that blocked/blocking query. On Fri, Aug 28, 2015 at 1:44 PM Steve Kehlet wrote: > On Fri, Aug 28, 2015 at 1:23 PM Jeff Janes wrote: > >> You should RESET the autovacuum_vacuum_scale_factor for the table. You >> don't want it to be vacuumed aggressively, just autoanalyzed aggressively. >> Sorry if my copy-paste error led you astray on that. >> > > No problem, done, thank you. > > There is a bulk load going on right now so a lot of tables are needing >>> vacuuming. I really need to increase my autovacuum_max_workers. >>> >> >> But those workers all share the same IO throttling amongst themselves. >> Increasing it mostly just gives you more workers all working more slowly. >> > > Ah, you're right, that won't help. > > >> Assuming your IO subsystem can handle it, you are better off lowering >> autovacuum_vacuum_cost_delay, which can be done without a server restart >> (although the change won't take full effect until the existing workers go >> away and restart). I also set vacuum_cost_page_hit and >> vacuum_cost_page_miss to zero and rely exclusively on vacuum_cost_page_dirty >> to do the throttling. >> > > Thank you for these great suggestions, I will play with them. > >>
Re: [GENERAL] how to investigate GIN fast updates and cleanup cycles?
On Fri, Aug 28, 2015 at 1:23 PM Jeff Janes wrote: > You should RESET the autovacuum_vacuum_scale_factor for the table. You > don't want it to be vacuumed aggressively, just autoanalyzed aggressively. > Sorry if my copy-paste error led you astray on that. > No problem, done, thank you. There is a bulk load going on right now so a lot of tables are needing >> vacuuming. I really need to increase my autovacuum_max_workers. >> > > But those workers all share the same IO throttling amongst themselves. > Increasing it mostly just gives you more workers all working more slowly. > Ah, you're right, that won't help. > Assuming your IO subsystem can handle it, you are better off lowering > autovacuum_vacuum_cost_delay, which can be done without a server restart > (although the change won't take full effect until the existing workers go > away and restart). I also set vacuum_cost_page_hit and > vacuum_cost_page_miss to zero and rely exclusively on vacuum_cost_page_dirty > to do the throttling. > Thank you for these great suggestions, I will play with them. >
Re: [GENERAL] how to investigate GIN fast updates and cleanup cycles?
On Fri, Aug 28, 2015 at 11:18 AM Tom Lane wrote: > It looks like a VACUUM will do the cleanup during the first ginbulkdelete > call, so you could probably handle this by running a manual "VACUUM > VERBOSE" with the smallest possible maintenance_work_mem, and canceling it > as soon as you see something reported about the GIN index. Since I'm back to running VACUUM VERBOSE by hand, can you clarify for me how reducing maintenance_work_mem (currently 512MB) will speed it up? Will it work in smaller chunks? So just do something like: set maintenance_work_mem = '32MB'; VACUUM VERBOSE my_table'; How do I determine the smallest value possible? Just start small (32MB?) and see if it fails, and increase until it doesn't?
Re: [GENERAL] how to investigate GIN fast updates and cleanup cycles?
On Fri, Aug 28, 2015 at 10:27 AM Tom Lane wrote: > Steve Kehlet writes: > > Yeah head scratch. That is really weird. Still gathering data, any way I > > can see for sure when these cleanup cycles are occurring? > > contrib/pgstattuple has a function that can report the current size of the > pending list for a GIN index. If you were to capture that every so often, > it'd be pretty easy to confirm or refute whether these episodes are > associated with list cleanup. > Thank you, I found it, this could be very helpful, we'll try it out on some dev boxes.
Re: [GENERAL] how to investigate GIN fast updates and cleanup cycles?
On Fri, Aug 28, 2015 at 12:10 PM Jeff Janes wrote: > Did you change the system-wide autovacuum_analyze_scale_factor? If so, > don't do that. You can use a table's storage parameters to set a custom > autovacuum_analyze_scale_factor just for individual tables. So just the > table with the troublesome gin index: > No I did it just to the problematic table: ALTER TABLE my_table SET (autovacuum_vacuum_scale_factor=0); ALTER TABLE my_table SET (autovacuum_analyze_scale_factor=0); My system-wide autovacuum_analyze_threshold is 50 so I think that's ok. There is a bulk load going on right now so a lot of tables are needing vacuuming. I really need to increase my autovacuum_max_workers. > > Killing existing vacuums won't help (at least not until change your system > wide setting back and so the above instead) because it will likely just > restart on the same table it was on when you killed it. > You're right, I gave up trying, and am back running a manual VACUUM. It's slow, but I can't bounce the db right now.
Re: [GENERAL] how to investigate GIN fast updates and cleanup cycles?
On Fri, Aug 28, 2015 at 10:42 AM Jeff Janes wrote: > Or what I usually do in a case like this is clone the database to a >>> test/QA server then run pg_upgrade to get that running on 9.5, then hope >>> what I learn transfers back to production. >> >> I'll save this great idea. > But the symptoms you describe are exactly what I expect from these clean > up problems, so I would just assume that that is the problem. > > The easiest solution is to turn of fastupdate for that index. Each update > will then be individually slower, but you won't have the periodic lock up > you currently do. > That would be fine and we will try this. > Vacuum is overkill (and can be extremely slow to run a large gin index), > you just need to get it to autoanalyze by changing the per-table setting of > "autovacuum_vacuum_scale_factor" to zero and instead using > Did you mean autovacuum_analyze_scale_factor or does it not matter? I'm trying to force an autovacuum/autoanalyze this way but unfortunately for me I have autovacuum_max_workers at the default of 3 and there are apparently many tables in line for autovacuuming in front of the table I want :-(. I'm playing whack-a-mole killing them and hoping the table I want will come up. Note that a manual ANALYZE will *not* clear the pending list, it has to be > an autoanalyze. > This is a brain bender, I didn't know there were differences, and this eats away a little bit at my confidence in understand things, but I'll just accept it for now. > (Manual VACUUM will clear the pending list, but you might have trouble > getting manual VACUUM to complete fast enough) > You are exactly right the manual VACUUM is taking forever. >
Re: [GENERAL] how to investigate GIN fast updates and cleanup cycles?
On Fri, Aug 28, 2015 at 10:11 AM Tom Lane wrote: > Steve Kehlet writes: > > This is Postgres 9.4.4. I am troubleshooting some occasional (every 3-4 > > hours) slowness with UPDATEs on a table that has a GIN index on a JSONB > > column. During these episodes, UPDATEs that normally take < 1sec take > > upwards of 2-4 minutes, and all finish simultaneously, like they were all > > blocked on something and finally got released. > > Hm ... have you tried checking pg_locks to see if they're blocked on > something identifiable? > Yes, I should have mentioned that, I have a cronjob going every minute dumping out [blocked/blocking queries]( https://gist.github.com/skehlet/fbf5f52e18149e14e520) and nothing has shown up related to these queries (there were some other normal unrelated results, so I believe the job+query itself are working). After several incidents I believe it would have logged something. > You might be right that this is caused by flushing the GIN pending list, > but I thought that that was not supposed to block concurrent insertions. > What I'd expect to see is *one* insert taking significantly longer than > normal, but no effect on concurrent operations. Also, 2-4 minutes sounds > much longer than should be needed to flush a 10MB pending list, anyway. Yeah head scratch. That is really weird. Still gathering data, any way I can see for sure when these cleanup cycles are occurring?
[GENERAL] how to investigate GIN fast updates and cleanup cycles?
This is Postgres 9.4.4. I am troubleshooting some occasional (every 3-4 hours) slowness with UPDATEs on a table that has a GIN index on a JSONB column. During these episodes, UPDATEs that normally take < 1sec take upwards of 2-4 minutes, and all finish simultaneously, like they were all blocked on something and finally got released. My team and I have done the usual stuff looking at the system itself and its underlying infrastructure, and we don't think that's it. It does seem to be localized to just this table. We're reading about GIN fast updates and noted that: As of PostgreSQL 8.4, GIN is capable of postponing much of this work by > inserting new tuples into a temporary, unsorted list of pending entries. > When the table is vacuumed, or if the pending list becomes too large > (larger than work_mem), the entries are moved to the main GIN data > structure using the same bulk insert techniques used during initial index > creation. This greatly improves GIN index update speed, even counting the > additional vacuum overhead. Moreover the overhead work can be done by a > background process instead of in foreground query processing. > The main disadvantage of this approach is that searches must scan the list > of pending entries in addition to searching the regular index, and so a > large list of pending entries will slow searches significantly. Another > disadvantage is that, while most updates are fast, an update that causes > the pending list to become "too large" will incur an immediate cleanup > cycle and thus be much slower than other updates. I am wondering if the UPDATEs are hitting some blockage on the table due to this cleanup cycle. Is this a possibility, and if so how can I investigate this? What queries can I run or stats can I check to see if this pending list is approaching work_mem, or we're hitting this cleanup cycle? Is there any logging I can enable to see when these cleanups are occurring? One test we are doing right now is running VACUUMs continuously on the table to see if this slowness stops happening. We should know within a few hours. If this seems to help, we'll try turning off FASTUPDATE on the index. Any other ideas or suggestions would be appreciated, thanks! My non-default postgres settings are [here in a gist]( https://gist.github.com/skehlet/fd4945f9c9ce6ed075f1).
Re: [HACKERS] [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1
Just wanted to report that I rolled back my VM to where it was with 9.4.2 installed and it wouldn't start. I installed 9.4.4 and now it starts up just fine: > 2015-06-12 16:05:58 PDT [6453]: [1-1] LOG: database system was shut down at 2015-05-27 13:12:55 PDT > 2015-06-12 16:05:58 PDT [6453]: [2-1] LOG: MultiXact member wraparound protections are disabled because oldest checkpointed MultiXact 1 does not exist on disk > 2015-06-12 16:05:58 PDT [6457]: [1-1] LOG: autovacuum launcher started > 2015-06-12 16:05:58 PDT [6452]: [1-1] LOG: database system is ready to accept connections > done > server started And this is showing up in my serverlog periodically as the emergency autovacuums are running: > 2015-06-12 16:13:44 PDT [6454]: [1-1] LOG: MultiXact member wraparound protections are disabled because oldest checkpointed MultiXact 1 does not exist on disk **Thank you Robert and all involved for the resolution to this.** > With the fixes introduced in this release, such a situation will result in immediate emergency autovacuuming until a correct oldestMultiXid value can be determined Okay, I notice these vacuums are of the "to prevent wraparound" type (like VACUUM FREEZE), that do hold locks preventing ALTER TABLEs and such. Good to know, we'll plan our software updates accordingly. Is there any risk until these autovacuums finish?
Re: [HACKERS] [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1
On Fri, Jun 5, 2015 at 11:47 AM Andres Freund wrote: > But I'd definitely like some > independent testing for it, and I'm not sure if that's doable in time > for the wrap. > I'd be happy to test on my database that was broken, for however much that helps. It's a VM so I can easily revert back as needed. I'm just losing track of all the patches, and what's committed and what I need to manually apply :-). I was about to test what's on REL9_4_STABLE. Let me know if I should do this. Thanks so much everyone.
Re: [HACKERS] Re: [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1
On Fri, May 29, 2015 at 12:08 PM Robert Haas wrote: > OK, here's a patch. > I grabbed branch REL9_4_STABLE from git, and Robert got me a 9.4-specific patch. I rebuilt, installed, and postgres started up successfully! I did a bunch of checks, had our app run several thousand SQL queries against it, had a colleague check it out, and it looks good. Looking at top and ps, I don't see anything funny (e.g. no processes spinning cpu, etc), things look normal. Let me know if I can provide anything else.
Re: [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1
On Wed, May 27, 2015 at 3:21 PM Alvaro Herrera wrote: > I think a patch like this should be able to fix it ... not tested yet. > Thanks Alvaro. I got a compile error, so looked for other uses of SimpleLruDoesPhysicalPageExist and added MultiXactOffsetCtl, does this look right? + (!InRecovery || SimpleLruDoesPhysicalPageExist(MultiXactOffsetCtl, pageno))) It compiled then, but it didn't seem to change anything: postgres gave the same errors and still would not start up. I'm recompiling it now just to be sure I didn't make any mistakes. I have to leave for the day in a few minutes so I thought I'd check on the above argument with you. Thanks again!
[GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1
I have a database that was upgraded from 9.4.1 to 9.4.2 (no pg_upgrade, we just dropped new binaries in place) but it wouldn't start up. I found this in the logs: waiting for server to start2015-05-27 13:13:00 PDT [27341]: [1-1] LOG: database system was shut down at 2015-05-27 13:12:55 PDT 2015-05-27 13:13:00 PDT [27342]: [1-1] FATAL: the database system is starting up .2015-05-27 13:13:00 PDT [27341]: [2-1] FATAL: could not access status of transaction 1 2015-05-27 13:13:00 PDT [27341]: [3-1] DETAIL: Could not open file "pg_multixact/offsets/": No such file or directory. 2015-05-27 13:13:00 PDT [27340]: [1-1] LOG: startup process (PID 27341) exited with exit code 1 2015-05-27 13:13:00 PDT [27340]: [2-1] LOG: aborting startup due to startup process failure stopped waiting I poked around in pg_multixact/offsets and there are lots of other files in there, just not . I tried launching postgres in standalone mode to try some debugging but it yields the same error. AFAICT, there's nothing wrong with hardware, it's a VM, and it's been working okay. About a week ago we upgraded it from 9.3.5 to 9.4.1 via pg_upgrade --link, which worked amazingly well. I found [this report from a couple days ago]( https://bugs.archlinux.org/task/45071) from someone else that looks like the same problem. I put this box's [postgresql.conf up on a gist]( https://gist.github.com/skehlet/3589b0d83f2cafe19624). What can I try next? Thanks!
Re: [GENERAL] delaying autovacuum freeze via storage params?
On Tue, May 26, 2015 at 7:27 PM Alvaro Herrera wrote: > See the docs about the freeze max age storage parameter -- the per-table > setting can decrease the global setting but not increase it. Thanks Alvaro, that explains it. I found it in the docs: "Note that autovacuum will ignore attempts to set a per-tableautovacuum_freeze_max_age larger than the system-wide setting (it can only be set smaller)."
[GENERAL] delaying autovacuum freeze via storage params?
Hello, I'd like to postpone an "autovacuum: VACUUM public.mytable (to
prevent wraparound)" and handle it manually at another time. I thought I
could set these storage parameters on the large table in question
("mytable") like this:
ALTER TABLE mytable SET (
autovacuum_freeze_min_age=1000,
autovacuum_freeze_table_age=8,
autovacuum_freeze_max_age=10
);
then kill the running autovacuum process, but unfortunately it just keeps
coming back:
mydb | 22734 | 05/26/2015 23:29:10 | autovacuum: VACUUM public.mytable
(to prevent wraparound)
I also tried setting the toast.autovacuum* storage parameters, as well as a
postgres reload, but neither made any difference.
Here is the table's age:
mydb=> select age(relfrozenxid) from pg_class where oid =
'mytable'::regclass;
age
---
203065951
(1 row)
So 203 million, which is just past the 200 million default (which is why I
started looking at this). But it's less than the 1 billion I set the
storage param to.
Here is \d+:
mydb=> \d+ mytable
Table "public.mytable"
Column |Type | Modifiers
| Storage | Stats target | Description
-+-+---+--+--+-
(removed al lthis)
Options: autovacuum_freeze_min_age=1000,
autovacuum_freeze_table_age=8,
autovacuum_freeze_max_age=10, toast.autovacuum_enabled=false,
toast.autovacuum_freeze_min_age=1000,
toast.autovacuum_freeze_table_age=8,
toast.autovacuum_freeze_max_age=10
I'm guessing I'm missing something here, can anyone offer suggestions or
ideas on what I should try next?
Here is my version:
mydb=> SELECT version();
version
---
PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2
20080704 (Red Hat 4.1.2-44), 64-bit
(1 row)
and I put my [custom settings in a gist](
https://gist.github.com/skehlet/bcdbc1ce1f212d3b7bb2).
Thanks!
Re: [GENERAL] finding tables about to be vacuum freezed
On Wed, May 6, 2015 at 7:24 PM Jeff Janes wrote: > I've booked-marked these but haven't really looked into them to any > extent. It would be awesome if you put the SQL one somewhere on > http://wiki.postgresql.org. That way it is easier to find, and anyone > who finds it can contribute explanations, corrections, and update it to > keep up with changes to the database. > I'll see if I can do this! > I don't have any experience with 3.5TB databases, but I certainly think > that that is something to worry about. > We did hit the autovacuum_freeze_max_age threshold on Saturday and the autovacuumer has been running for days now, slowly cranking through each table. Fortunately, I had autovacuum_vacuum_cost_delay set so the IO impact isn't bad: no complaints from the customer, and our ops group says the IO load is okay. So Postgres is just quietly doing its thing. This has clearly happened numerous times before, and explains a few mysterious incidents in the past where a nightly analyze script has hung for several days. It's really great to understand this better now. > There are two main problems you are likely to encounter (from what I > know): > > One is that the autovacuum scheduler deals poorly with a database > exceeding autovacuum_freeze_max_age. It forces all available to autovacuum > resources to be directed to that database, starving any other database of > attention. If you have multiple active databases, by the time one database > has been frozen enough to no longer exceed autovacuum_freeze_max_age, the > other one(s) might be horribly bloated. If your cluster only has one > active database in it, this won't be a problem. The one that gets all the > attention is the one that needs all the attention. But if you have > multiple active databases in your cluster, this could be a problem. > Fortunately in this case it's just one database, but good to know. > The other problem is that autovac takes a fairly strong lock out on the > table while it is vacuuming it. Normally it relinquishes the lock once it > realizes someone else is waiting on it. But in the case of a forced > full-table scan (either autovacuum_freeze_max_age or > vacuum_freeze_table_age is exceeded), it refuses to relinquish the lock. > This means that any process which needs a strong-ish table lock (add > column, drop column, create index, drop index, cluster, truncate, reindex, > etc.) is going to block for potentially a very very long time. This is > only a problem if you actually have such processes. If all you do is > select, insert, update, delete, none of those things will be blocked. > You're right, that was the exact problem that got me going down this path. Some of our guys were doing a software update and some CREATE INDEX operations it wanted to do were blocked by the autovacuumer. Fortunately, we don't do software updates all the time, but it's falling onto me to figure out how to make the autovacuumer not do its thing in the middle of future software updates :-). I might do ALTER TABLES on all tables to temporarily increase their autovacuum_freeze_max_age before, and undo it after. Kind of hacky, but it should work. > So if you have lull time at night, it would be a good idea to preemptively > vacuum tables approaching autovacuum_freeze_max_age (and also exceeding > vacuum_freeze_table_age). I wouldn't even do VACUUM FREEZE, just VACUUM. > I'll investigate this. I found Josh Berkus' excellent articles, [Freezing your tuples off]( http://www.databasesoup.com/2012/09/freezing-your-tuples-off-part-1.html) and the nightly [Flexible Freeze]( https://github.com/pgexperts/flexible-freeze) program that came out that. I'll investigate using this so we can better control when this happens. Although, given that the IO load even during production hours isn't making anyone scream, I might just leave it alone. But aware that, be default setting, autovac is highly throttled for IO, > while regular vacuum is entirely unthrottled for IO. So if nighttime is not > completely idle but only relatively less busy of user activity, you might > want to evaluate what level of throttling is appropriate. > Thanks Jeff for all your help and insight.
Re: [GENERAL] finding tables about to be vacuum freezed
On Wed, May 6, 2015 at 9:46 AM Jeff Janes wrote: > vacuum_freeze_table_age controls when it promotes a vacuum *which is > already going to occur* so that it scans the whole table. It doesn't > specially schedule a vacuum to occur. When those tables see enough > activity to pass autovacuum_vacuum_scale_factor then the vacuum that > occurs will get promoted to be a full scan. > Thank you Jeff, that really helps understand this. > If they never see that amount of activity, then the tables will continue > to age until autovacuum_freeze_max_age, at which point a vacuum will get > launched specifically for the purpose of advancing relfrozenxid. > So based on this, I created this query to show autovacuum and analyze data, including if the next autovacuum will be a freeze; and how close the table is to being force-freezed. This borrows heavily from queries I found at Heroku ( https://github.com/heroku/heroku-pg-extras/blob/master/lib/heroku/command/pg.rb ): https://gist.github.com/skehlet/36aad599171b25826e82 My output looks like: https://gist.github.com/skehlet/592b00c1e4b381c83099 I'm thinking I'm in for a world of pain when all my tables in my 3.5TB db simultaneously hit autovacuum_freeze_max_age, and I think I'm about 83% of the way there. What do you think? I'm thinking I should start doing a VACUUM FREEZE on tables at night to head this off. >
[GENERAL] finding tables about to be vacuum freezed
Hello, recently one of my tables needed a vacuum (to prevent wraparound)
and of course it happened at a really bad time, so since then I've been
learning about how Transaction ID Wraparound works and its associated
parameters.
I'm trying this query to see how close my tables are to hitting the
vacuum_freeze_table_age threshold (150M in my case):
SELECT
relname,
age(relfrozenxid) as xid_age,
ROUND(100.0 * age(relfrozenxid) /
current_setting('vacuum_freeze_table_age')::numeric, 1) || '%' AS "% til
vacuum freeze"
FROM
pg_class
WHERE relkind = 'r';
For now, assume my tables have no storage parameters that override the
defaults.
I was surprised at the results, almost all my tables look like:
my_table | 160589343 | 107.1%
Or about 160m transactions old. I would have thought with my current
settings:
vacuum_freeze_min_age = 50m
vacuum_freeze_table_age = 150m
autovacuum_freeze_max_age = 200m
that the autovacuumer would have already forced a vacuum freeze on all
these tables. According to the docs, "a whole table sweep is forced if the
table hasn't been fully scanned for vacuum_freeze_table_age minus
vacuum_freeze_min_age transactions" which would be 100m transactions.
I'm guessing my understanding here is wrong. What did I miss?
Just in case it matters, here's my current txid:
mydb=# select txid_current();
txid_current
--
1485894081
(1 row)
Thanks!
Re: [GENERAL] hot_standby_feedback vs. max_standby_archive_delay/max_standby_streaming_delay?
Thank you for the guidance! So far so good with max_standby_archive/streaming_delay, no cancellations.
[GENERAL] hot_standby_feedback vs. max_standby_archive_delay/max_standby_streaming_delay?
Our queries on our Standby are getting cancelled and so we're investigating how to prevent this. The standby is for running periodic reporting queries only, we don't care if it falls behind a little bit, we just set this guy up to reduce the load on the Primary. While researching there appear to be several different parameters that can help solve this. It sounds like we don't need hot_standby_feedback or vacuum_defer_cleanup_age, we really just want to pause things on the standby to let it run its queries. So we're going to try applying max_standby_archive_delay and max_standby_streaming_delay to 1h or so. We're also looking at pg_xlog_replay_pause(), although this is less desirable because we don't want to have to ask the people running reports to remember to pause and resume things. Can anyone confirm we're on the right track or provide further guidance? Thanks so much.
Re: [GENERAL] pg_dump slower than pg_restore
On Thu, Jul 3, 2014 at 10:04 AM, David Wall wrote: > I'm running PG 9.3.4 on CentOS 6.4 and noted that backing up my database > takes much longer than restoring it. > Are you dumping to a slower disk/storage than the database is using? What does top -c look like during the dump vs. the restore? I.e. can you identify the differing bottlenecks in the two cases and dig in further from there. My backup script runs vacuumlo, then vacuum, then analyze, then pg_dump > --format=c --oids $DB How long does the backup take if you don't vacuum/analyze? On some of my dbs that would be a huge part of the time. Honestly I'd pull the vacuum/analyze out of the backup and into a different (nightly) task, there's no real benefit doing as part of the backup task, other than just getting it done. Analyzing after the restore would be a better time to do it, to freshen up the statistics.
Re: [GENERAL] Spurious Stalls
> > The cpu utilization increases to nearly 100%, in user space, and stays > there, until the database is restarted. > > postgres 1323 47.1 2.3 6667212 6087388 ? Rs 00:00 276:00 \_ postgres: bitbucket bitbucket 172.17.10.1(5114) SELECT I see you have long-query logging enabled, what was this query doing? It seems like the oddball from your ps output, it was taking half your CPU. Or did you have to kill the db before it logged anything out. If so, while debugging something with a memory problem here we set up a cronjob to log out all running queries every minute, before the oom-killer would start killing stuff, maybe you could catch the culprit next time. For the pids that you included strace outputs for, curious, why did you pick them? Were they using a lot of CPU? Except for the postmaster one (81372), I didn't see them in the ps output.
Re: [GENERAL] is there a way to firmly cap postgres worker memory consumption?
On Tue, Apr 8, 2014 at 6:32 PM, Tom Lane wrote: > Well, here's the problem: > > ExprContext: 812638208 total in 108 blocks; 183520 free (171 > > chunks); 812454688 used > > So something involved in expression evaluation is eating memory. > Looking at the query itself, I'd have to bet on this: > > >ARRAY_TO_STRING(ARRAY_AGG(MM.ID::CHARACTER VARYING), ',') > > My guess is that this aggregation is being done across a lot more rows > than you were expecting, and the resultant array/string therefore eats > lots of memory. You might try replacing that with COUNT(*), or even > better SUM(LENGTH(MM.ID::CHARACTER VARYING)), just to get some definitive > evidence about what the query is asking to compute. > The devs have moved on and want to stick with their new query, so I'll just chalk this up to a bad query and let it go. But I'm glad to have learned a few new tricks, thanks. > Meanwhile, it seems like ulimit -v would provide the safety valve > you asked for originally. Thank you Amador and Tom for the ulimit solution, that's exactly what I needed.
Re: [GENERAL] is there a way to firmly cap postgres worker memory consumption?
I should add I had to move testing to a different VM, with less RAM and different tunings; they are here: https://gist.github.com/skehlet/10207086 On Tue, Apr 8, 2014 at 4:08 PM, Steve Kehlet wrote: > On Tue, Apr 8, 2014 at 12:23 PM, Tom Lane wrote: > > Once you've got a ulimit in place so that malloc eventually fails with >> ENOMEM, the backend process should print a memory context dump on stderr >> when it hits that. Make sure your logging setup captures the process >> stderr someplace (logging_collector works for this, syslog does not). >> Post the dump here when you've got it. >> > > Thank you. For some reason I couldn't get it to trip with "ulimit -d > 51200", but "ulimit -v 1572864" (1.5GiB) got me this in serverlog. I hope > this is readable, if not it's also here: > https://gist.github.com/skehlet/10206741 > > TopMemoryContext: 123760 total in 16 blocks; 2720 free (7 chunks); 121040 > used > TableSpace cache: 8192 total in 1 blocks; 3216 free (0 chunks); 4976 used > Type information cache: 24240 total in 2 blocks; 3744 free (0 chunks); > 20496 used > Operator lookup cache: 24576 total in 2 blocks; 11888 free (5 chunks); > 12688 used > TopTransactionContext: 8192 total in 1 blocks; 7368 free (0 chunks); 824 > used > MessageContext: 4186112 total in 9 blocks; 633960 free (19 chunks); > 3552152 used > JoinRelHashTable: 8192 total in 1 blocks; 624 free (0 chunks); 7568 > used > Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 > used > smgr relation table: 24576 total in 2 blocks; 9808 free (4 chunks); > 14768 used > TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0 chunks); > 32 used > Portal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used > PortalMemory: 8192 total in 1 blocks; 7888 free (0 chunks); 304 used > PortalHeapMemory: 1024 total in 1 blocks; 816 free (0 chunks); 208 used > ExecutorState: 566208 total in 12 blocks; 195256 free (2 chunks); > 370952 used > TupleSort: 17817440 total in 36 blocks; 9326832 free (8801 > chunks); 8490608 used > TupleSort: 17500568 total in 86 blocks; 12016040 free (2896 > chunks); 5484528 used > TupleSort: 17616584 total in 86 blocks; 9975520 free (3649 > chunks); 7641064 used > TupleSort: 10825056 total in 14 blocks; 3896400 free (45082 > chunks); 6928656 used > TupleSort: 17459720 total in 67 blocks; 8495600 free (2226 > chunks); 8964120 used > TupleSort: 10899360 total in 23 blocks; 2112448 free (23706 > chunks); 8786912 used > TupleSort: 11908776 total in 15 blocks; 8344064 free (145642 > chunks); 3564712 used > printtup: 0 total in 0 blocks; 0 free (0 chunks); 0 used > ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used > ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used > ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used > ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used > ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used > ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used > ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used > ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used > ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used > ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used > ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used > ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used > ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used > ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used > ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used > ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used > ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used > ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used > ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used > ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used > ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used > ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used > ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used > ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used > ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used > ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used > ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used > ExprContext: 0 total in 0 bloc
Re: [GENERAL] is there a way to firmly cap postgres worker memory consumption?
On Tue, Apr 8, 2014 at 12:23 PM, Tom Lane wrote: > Once you've got a ulimit in place so that malloc eventually fails with > ENOMEM, the backend process should print a memory context dump on stderr > when it hits that. Make sure your logging setup captures the process > stderr someplace (logging_collector works for this, syslog does not). > Post the dump here when you've got it. > Thank you. For some reason I couldn't get it to trip with "ulimit -d 51200", but "ulimit -v 1572864" (1.5GiB) got me this in serverlog. I hope this is readable, if not it's also here: https://gist.github.com/skehlet/10206741 TopMemoryContext: 123760 total in 16 blocks; 2720 free (7 chunks); 121040 used TableSpace cache: 8192 total in 1 blocks; 3216 free (0 chunks); 4976 used Type information cache: 24240 total in 2 blocks; 3744 free (0 chunks); 20496 used Operator lookup cache: 24576 total in 2 blocks; 11888 free (5 chunks); 12688 used TopTransactionContext: 8192 total in 1 blocks; 7368 free (0 chunks); 824 used MessageContext: 4186112 total in 9 blocks; 633960 free (19 chunks); 3552152 used JoinRelHashTable: 8192 total in 1 blocks; 624 free (0 chunks); 7568 used Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used smgr relation table: 24576 total in 2 blocks; 9808 free (4 chunks); 14768 used TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0 chunks); 32 used Portal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used PortalMemory: 8192 total in 1 blocks; 7888 free (0 chunks); 304 used PortalHeapMemory: 1024 total in 1 blocks; 816 free (0 chunks); 208 used ExecutorState: 566208 total in 12 blocks; 195256 free (2 chunks); 370952 used TupleSort: 17817440 total in 36 blocks; 9326832 free (8801 chunks); 8490608 used TupleSort: 17500568 total in 86 blocks; 12016040 free (2896 chunks); 5484528 used TupleSort: 17616584 total in 86 blocks; 9975520 free (3649 chunks); 7641064 used TupleSort: 10825056 total in 14 blocks; 3896400 free (45082 chunks); 6928656 used TupleSort: 17459720 total in 67 blocks; 8495600 free (2226 chunks); 8964120 used TupleSort: 10899360 total in 23 blocks; 2112448 free (23706 chunks); 8786912 used TupleSort: 11908776 total in 15 blocks; 8344064 free (145642 chunks); 3564712 used printtup: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 812638208 total in 108 blocks; 183520
Re: [GENERAL] is there a way to firmly cap postgres worker memory consumption?
> > Did you check if your OS allows you to limit memory usage per user / > process basis? > I'll definitely dig more into this. I found some stuff saying it isn't as easy as one would expect, i.e. not just a ulimit and done. Or maybe it is, in this case. I will play around. > Did you either profiled or debugged it to see what is going on? > I would love to learn more about how to do this, to get to the bottom of the memory usage. I can google around, or can you suggest any reads? > When memory consumption grows , Doesn't ever swap out? > Yes, it consumes all swap.
[GENERAL] is there a way to firmly cap postgres worker memory consumption?
I'm running into some scenarios where errant postgres queries are consuming all memory and bringing down production boxes. I'm running Postgres 9.3.4 on CentOS 5.x VMs under XenServer. My custom config is here: https://gist.github.com/skehlet/9984666 The incidents may be related to reports of an array_agg memory leak, and another recent incident seems like it was due to the xpath() function provided by --with-xml. There's some more information on the former in my post at: http://dba.stackexchange.com/questions/62587/why-does-this-query-cause-the-postgres-worker-process-to-consume-so-much-memory, if it's interesting. I'd like to dig into that further, maybe on another thread. But for now, the devs are asking me for some way to put a cap on a postgres query's total memory consumption. I'm familiar with the available settings on the "Resource Consumption" docs (and you can see my settings in my gist above, including work_mem turned way down to 1MB for testing), but it seems like there are things like Materialize that remain uncappable, since they're not constrained by the shared_buffers and work_mem limits. For example in my post to dba.stackexchange above, I found by doing a "set enable_material=false;" I could stop a particularly nasty query from consuming all memory, narrowing the problem somewhat, but that query is one we have to live with in the near term. With all that laid out, can anyone help with the following questions: - Is there any way to set to total memory cap on a worker processes memory consumption? - Are there other (even undocumented) settings to cap memory usage? - Other ideas? In the meantime, to stop the bleeding I'm looking into using the postgres feature to coerce the OOM killer to kill rogue child processes first (- DLINUX_OOM_ADJ). Unfortunately, another setback, even the latest kernel on CentOS 5.x doesn't allow non-root processes to write to /proc/self/oom_adj, so it isn't working. So I'll either need to patch the CentOS kernel and rebuild, move to a newer kernel, or maybe do something hacky like run a background process to reassign the oom_adj value for all postgres workers found. Thanks for any help/ideas!
