ERROR: found xmin from before relfrozenxid
Hey, I'm trying to help a guy that is using pg9.6 but I'm not so familiar with the error message : ERROR: found xmin 16804535 from before relfrozenxid 90126924 CONTEXT: automatic vacuum of table db1.public.table_1" It seems that the error has started appearing two weeks ago. Data that I collected : -all the autovacuum params are set to default -SELECT relname, age(relfrozenxid) as xid_age, pg_size_pretty(pg_table_size(oid)) as table_size FROM pg_class WHERE relkind = 'r' and pg_table_size(oid) > 1073741824 ORDER BY age(relfrozenxid) DESC LIMIT 4; relname| xid_age | table_size ---+---+ table_1| 180850538 | 10 GB table_2 | 163557812 | 10 GB table_3 | 143732477 | 1270 MB table_4 | 70464685 | 3376 MB pg_controldata : Latest checkpoint's NextXID: 0:270977386 Latest checkpoint's NextOID: 25567991 Latest checkpoint's NextMultiXactId: 1079168 Latest checkpoint's NextMultiOffset: 68355 Latest checkpoint's oldestXID:77980003 Latest checkpoint's oldestXID's DB: 16403 Latest checkpoint's oldestActiveXID: 0 Latest checkpoint's oldestMultiXid: 1047846 Latest checkpoint's oldestMulti's DB: 16403 Latest checkpoint's oldestCommitTsXid:0 Latest checkpoint's newestCommitTsXid:0 It seems that the autovacuum cant vacuum table_1 and it has alot of dead_tuples. Moreover, it seems that the indexes are bloated. schemaname relname n_tup_upd n_tup_del n_tup_hot_upd n_live_tup n_dead_tup n_mod_since_analyze last_vacuum last_autovacuum last_analyze public table_1 0 5422370 0 38 109582923 10760701 I tried to vacuum the table (full,freeze) but it didnt help. I read about the wrap that can happen but to be honest I'm not sure that I understood id. What can I do to vacuum the table ? Can some one explain the logic behind the error message ? Thanks.
Re: SELECT performance drop
On Wed, Jan 23, 2019 at 6:51 AM Jim Finnerty wrote: > One thing that isn't helping is that you have a redundant predicate. The > selectivity of this predicate is also estimated too low, so removing the > redundant predicate might improve the estimate and change the plan: > > ( " > + " o.consumer IS NULL" > + ") OR ( " > + " o.consumer IS NOT NULL" > + " AND o.consumer > 0 > > remove "o.consumer IS NOT NULL AND", which is implied by o.consumer > 0. > This predicate should have been automatically removed, but the filter shown > in depesz shows that it was not. > Good point -- the new generated SQL is select order0_.id as id1_7_, order0_.created_by as created_2_7_, order0_.created_date as created_3_7_, order0_.last_modified_by as last_mod4_7_, order0_.last_modified_date as last_mod5_7_, order0_.consumer as consumer6_7_, order0_.market_id as market_14_7_, order0_.original as original7_7_, order0_.owner_id as owner_i15_7_, order0_.owner_target as owner_ta8_7_, order0_.price as price9_7_, order0_.session_id as session16_7_, order0_.side as side10_7_, order0_.supplier as supplie11_7_, order0_.type as type12_7_, order0_.units as units13_7_ from fm_order order0_ cross join fm_session session1_ where order0_.session_id=session1_.id and order0_.type='LIMIT' and session1_.original=7569 and ( order0_.consumer is null or order0_.consumer>0 and ( exists ( select 1 from fm_order order2_ cross join fm_session session3_ where order2_.session_id=session3_.id and order2_.id=order0_.consumer and session3_.original=7569 and order2_.type='LIMIT' and order2_.owner_id<>order0_.owner_id ) ) ) order by order0_.last_modified_date DESC; > If you can find out what the faster plan was, that would be helpful to > know. > which results in: https://explain.depesz.com/s/vGVo > > > > - > Jim Finnerty, AWS, Amazon Aurora PostgreSQL > -- > Sent from: > http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html > >
Benchmarking: How to identify bottleneck (limiting factor) and achieve "linear scalability"?
Hi, Please pardon me if this question is already answered in the documentation, Wiki, or the mailing list archive. The problem is, that I don't know the exact term to search for - I've tried searching for "linear scalability" and "concurrency vs performance" but didn't find what I was looking for. ## MAIN QUESTION pgbench -c 1 achieves approx 80 TPS pgbench -c 6 should achieve approx 480 TPS, but only achieves 360 TPS pgbench -c 12, should achieve approx 960 TPS, but only achieves 610 TPS If pgbench is being run on a 4c/8t machine and pg-server is being run on a 6c/12t machine with 32GB RAM [1], and the two servers are connected with 1 Gbit/s connection, I don't think either pgbench or pg-server is being constrained by hardware, right? *In that case why is it not possible to achieve linear scalability, at least till 12 concurrent connections (i.e. the thread-count of pg-server)?* What is an easy way to identify the limiting factor? Is it network connectivity? Disk IOPS? CPU load? Some config parameter? ## SECONDARY QUESTION *At what level of concurrent connections should settings like shared_buffers, effective_cache_size, max_wal_size start making a difference?* With my hardware [1], I'm seeing a difference only after 48 concurrent connections. And that too it's just a 15-30% improvement over the default settings that ship with the Ubuntu 18.04 package. Is this expected? Isn't this allocating too many resources for too little gain? ## CONTEXT I am currently trying to benchmark PG 11 (via pgbench) to figure out the configuration parameters that deliver optimum performance for my hardware [1] and workload [2] Based on https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server I've made the following relevant changes to the default PG config on Ubuntu 18.04: max_connection=400 work_mem=4MB maintenance_work_mem=64MB shared_buffers=12288MB temp_buffers=8MB effective_cache_size=16GB wal_buffers=-1 wal_sync_method=fsync max_wal_size=5GB autovacuum=off # NOTE: Only for benchmarking [1] 32 GB RAM - 6 core/12 thread - 2x SSD in RAID1 [2] SaaS webapp -- it's a mixed workload which looks a lot like TPC-B Thanks, Saurabh.
RE:SELECT performance drop
Hi, is there an index on fm_order(session_id,type) ? regards PAscal
Re: ERROR: found xmin from before relfrozenxid
Mariel Cherkassky writes: > Hey, > I'm trying to help a guy that is using pg9.6 but I'm not so familiar > with the error message : > ERROR: found xmin 16804535 from before relfrozenxid 90126924 > CONTEXT: automatic vacuum of table db1.public.table_1" 9.6.?... That error or a very similar one was fixed in a recent point release. HTH -- Jerry Sievers Postgres DBA/Development Consulting e: [email protected]
Re: SELECT performance drop
On Wed, 2019-01-23 at 10:28 -0700, Jan Nielsen wrote: > select > order0_.id as id1_7_, > order0_.created_by as created_2_7_, > order0_.created_date as created_3_7_, > order0_.last_modified_by as last_mod4_7_, > order0_.last_modified_date as last_mod5_7_, > order0_.consumer as consumer6_7_, > order0_.market_id as market_14_7_, > order0_.original as original7_7_, > order0_.owner_id as owner_i15_7_, > order0_.owner_target as owner_ta8_7_, > order0_.price as price9_7_, > order0_.session_id as session16_7_, > order0_.side as side10_7_, > order0_.supplier as supplie11_7_, > order0_.type as type12_7_, > order0_.units as units13_7_ > from > fm_order order0_ cross > join > fm_session session1_ > where > order0_.session_id=session1_.id > and order0_.type='LIMIT' > and session1_.original=7569 > and ( > order0_.consumer is null > or order0_.consumer>0 > and ( > exists ( > select > 1 > from > fm_order order2_ cross > join > fm_session session3_ > where > order2_.session_id=session3_.id > and order2_.id=order0_.consumer > and session3_.original=7569 > and order2_.type='LIMIT' > and order2_.owner_id<>order0_.owner_id > ) > ) > ) > order by > order0_.last_modified_date DESC; It might be more efficient to rewrite that along these lines: SELECT DISTINCT order0_.* FROM fm_order order0_ JOIN fm_session session1_ ON order0_.session_id = session1_.id LEFT JOIN fm_order order2_ ON order2_.id = order0_.consumer LEFT JOIN fm_session session3_ ON order2_.session_id = session3_.id WHERE coalesce(order2_.id, 1) > 0 AND /* all the other conditions */; Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: ERROR: found xmin from before relfrozenxid
Yeah 9.6 ! On Wed, Jan 23, 2019, 9:51 PM Jerry Sievers Mariel Cherkassky writes: > > > Hey, > > I'm trying to help a guy that is using pg9.6 but I'm not so familiar > > with the error message : > > ERROR: found xmin 16804535 from before relfrozenxid 90126924 > > CONTEXT: automatic vacuum of table db1.public.table_1" > > 9.6.?... > > That error or a very similar one was fixed in a recent point release. > > HTH > > -- > Jerry Sievers > Postgres DBA/Development Consulting > e: [email protected] >
