Re: [PERFORM] postgresql 9.6 data directory fs becomes full
Mariel Cherkassky <mariel.cherkas...@gmail.com> writes: > I want to check something regarding postgresql performance during my > app is running. > > My app does the next things on 20 tables in a loop : > > 1.truncate table. > 2.drop constraints on table > 3.drop indexes on table > 4.insert into local_table select * from remote_oracle_table > 4.1.Recently I'm getting an error in this part : SQLERRM = could not extend >file "base/16400/124810.23": wrote only 4096 of 8192 bytes at block >3092001 > 5.create constraints on table > 6.create indexes on table. > > This operation runs every night. Most of the tables are small 500M-2G > but few tables are pretty big 24G-45G. > > My wals and my data directory are on different fs. My data directory > fs size is 400G. During this operation the data directory fs becomes > full. However, after this operation 100G are freed which means that > 300G are used from the 400g of the data directory fs. Something > regarding those sizes doesnt seems ok. > > When I check my database size : > > mydb=# SELECT > mydb-# pg_database.datname, > mydb-# pg_size_pretty(pg_database_size(pg_database.datname)) AS size > mydb-# FROM pg_database; > datname | size > ---+- > template0 | 7265 kB > mydb | 246 GB > postgres | 568 MB > template1 | 7865 kB > (4 rows) > > When I check all the tables in mydb database : > > mydb-#relname as "Table", > mydb-#pg_size_pretty(pg_total_relation_size(relid)) As "Size", > mydb-#pg_size_pretty(pg_total_relation_size(relid) - > pg_relation_size(relid)) as "External Size" > mydb-#FROM pg_catalog.pg_statio_user_tables ORDER BY > pg_total_relation_size(relid) DESC; > Table |Size| External Size > ---++--- > table 1| 45 GB | 13 GB > table 2| 15 GB | 6330 MB > table 3| 9506 MB| 3800 MB > table 4| 7473 MB| 1838 MB > table 5| 7267 MB| 2652 MB > table 6| 5347 MB| 1701 MB > table 7| 3402 MB| 1377 MB > table 8| 3092 MB| 1318 MB > table 9| 2145 MB| 724 MB > table 10| 1804 MB| 381 MB > table 11 293 MB | 83 MB > table 12| 268 MB | 103 MB > table 13| 225 MB | 108 MB > table 14| 217 MB | 40 MB > table 15| 172 MB | 47 MB > table 16| 134 MB | 36 MB > table 17| 102 MB | 27 MB > table 18| 86 MB | 22 MB >. > > In the data directory the base directory`s size is 240G. I have 16G > of ram in my machine. > > Waiting for help, thanks. You didn't say but if I can assume you're doing this work in a transaction... You understand that space is *not* freed by the truncate until commit, right? > > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Odd sudden performance degradation related to temp object churn
Peter Geoghegan <p...@bowt.ie> writes: > On Mon, Aug 14, 2017 at 12:53 PM, Jeremy Finzel <finz...@gmail.com> wrote: > >> This particular db is on 9.3.15. Recently we had a serious performance >> degradation related to a batch job that creates 4-5 temp tables and 5 >> indexes. It is a really badly written job but what really confuses us is >> that this job has been running for years with no issue remotely approaching >> this one. We are also using pgpool. > > Did you happen to notice that this occurred when you upgrading point > release? If so, what version did you move from/to? The system was last started back in November. Running 9.3.15. Not aware of any host system libs or whatever change recently but will investigate. > > -- > Peter Geoghegan -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Odd sudden performance degradation related to temp object churn
Scott Marlowe <scott.marl...@gmail.com> writes: > On Mon, Aug 14, 2017 at 2:46 PM, Jeremy Finzel <finz...@gmail.com> wrote: > >> On Mon, Aug 14, 2017 at 3:01 PM, Scott Marlowe <scott.marl...@gmail.com> >> wrote: >>> >>> On Mon, Aug 14, 2017 at 1:53 PM, Jeremy Finzel <finz...@gmail.com> wrote: >>> > >>> > Any insights would be greatly appreciated, as we are concerned not >>> > knowing >>> > the root cause. >>> >>> How are your disks setup? One big drive with everything on it? >>> Separate disks for pg_xlog and pg's data dir and the OS logging? IO >>> contention is one of the big killers of db performance. >> >> >> It's one san volume ssd for the data and wal files. But logging and memory >> spilling and archived xlogs go to a local ssd disk. >> >>> >>> Logging likely isn't your problem, but yeah you don't need to log >>> ERRYTHANG to see the problem either. Log long running queries temp >>> usage, buffer usage, query plans on slow queries, stuff like that. >>> >>> You've likely hit a "tipping point" in terms of data size. Either it's >>> cause the query planner to make a bad decision, or you're spilling to >>> disk a lot more than you used to. >>> >>> Be sure to log temporary stuff with log_temp_files = 0 in your >>> postgresql.conf and then look for temporary file in your logs. I bet >>> you've started spilling into the same place as your temp tables are >>> going, and by default that's your data directory. Adding another drive >>> and moving pgsql's temp table space to it might help. >> >> >> We would not have competition between disk spilling and temp tables because >> what I described above - they are going to two different places. Also, I >> neglected to mention that we turned on auto-explain during this crisis, and >> found the query plan was good, it was just taking forever due to thrashing >> just seconds after we kicked off the batches. I did NOT turn on log_analyze >> and timing but it was enough to see there was no apparent query plan >> regression. Also, we had no change in the performance/plan after >> re-analyzing all tables. > > You do know that temp tables go into the default temp table space, > just like sorts, right? Not so. This system has no defined temp_tablespace however spillage due to sorting/hashing that exceeds work_mem goes to base/pgsql_tmp which we have symlinked out to a local SSD drive. We do run a few of our other systems with temp_tablespace defined and for these the heap/index files do share same volume as other temp usage. Thx > > Have you used something like iostat to see which volume is getting all the IO? > >> >>> >>> Also increasing work_mem (but don't go crazy, it's per sort, so can >>> multiply fast on a busy server) >> >> >> We are already up at 400MB, and this query was using memory in the low KB >> levels because it is very small (1 - 20 rows of data per temp table, and no >> expensive selects with missing indexes or anything). > > Ahh so it doesn't sound like it's spilling to disk then. Do the logs > say yes or no on that? > > Basically use unix tools to look for where you're thrashing. iotop can > be handy too. -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)
Sheena, Prabhjot prabhjot.si...@classmates.com writes: Hi Ken/ Will I have checked the ulimit value and we are nowhere hitting the max 4096 that we have currently set. Is there any other explanation why we should be thinking of bumping it to like ulimit -n 5 ( Add ulimit -n 5 to the start of whatever you use to start pgbouncer (init script, etc..)) even though we are not reaching 4096 max value If I can assume you're running on linux, best you get limits readout from... /proc/$PID-of-bouncer-process/limits Best not to trust that run time env of interactive shell is same as where bouncer launched from. FWIW Regards Prabhjot Singh -Original Message- From: k...@rice.edu [mailto:k...@rice.edu] Sent: Thursday, June 18, 2015 11:10 AM To: Sheena, Prabhjot Cc: pgsql-performance@postgresql.org; pgsql-gene...@postgresql.org Subject: Re: [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site) On Thu, Jun 18, 2015 at 05:41:01PM +, Sheena, Prabhjot wrote: Here is the output of OS limits postgres@symds-pg:~ $ ulimit -a core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 790527 max locked memory (kbytes, -l) 32 max memory size (kbytes, -m) unlimited open files (-n) 4096 pipe size(512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 10240 cpu time (seconds, -t) unlimited max user processes (-u) 16384 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited Thanks Prabhjot I would bump your open files as was suggested in your pgbouncer start script. Regards, Ken -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Performance issues
Vivekanand Joshi vjo...@zetainteractive.com writes: So, here is the first taste of success and which gives me the confidence that if properly worked out with a good hardware and proper tuning, PostgreSQL could be a good replacement. Out of the 9 reports which needs to be migrated in PostgreSQL, 3 are now running. Report 4 was giving an issue and I will see it tomorrow. Just to inform you guys that, the thing that helped most is setting enable_nestloops to false worked. Plans are now not miscalculated. But this is not a production-suitable setting. So what do you think how to get a work around this? Consider just disabling that setting for 1 or a few odd queries you have for which they are known to plan badly. begin; set local enable_nestloops to false; select ...; commit/abort; I'd say never make that sort of setting DB or cluster-wide. Regards, Vivek -Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Tomas Vondra Sent: Tuesday, March 17, 2015 9:00 PM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Performance issues On 17.3.2015 16:24, Thomas Kellerer wrote: Tomas Vondra schrieb am 17.03.2015 um 15:43: On 17.3.2015 15:19, Thomas Kellerer wrote: Tomas Vondra schrieb am 17.03.2015 um 14:55: (2) using window functions, e.g. like this: SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY touchpoint_execution_id ORDER BY FROM max_creation_dt) AS rn FROM s_f_touchpoint_execution_status_history ) foo WHERE rn = 1 But estimating this is also rather difficult ... From my experience rewriting something like the above using DISTINCT ON is usually faster. How do you get the last record (with respect to a timestamp column) using a DISTINCT ON? You need to use order by ... desc. See here: http://sqlfiddle.com/#!15/d4846/2 Nice, thanks! Btw: your row_number() usage wouldn't return the latest row either. It would return the oldest row. Oh, right. I forgot the DESC in the window. -- Tomas Vondrahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Poor performance when deleting from entity-attribute-value type master-table
Andreas Joseph Krogh andr...@visena.com writes: Hi all. Using PG-9.4.0 I'm seeing this trying to delete from an entity-master table: *# explain analyze delete from onp_crm_entity where entity_id IN (select tmp.delivery_id from temp_delete_delivery_id tmp); QUERY PLAN --- Delete on onp_crm_entity (cost=0.43..5673.40 rows=1770 width=12) (actual time=7.370..7.370 rows=0 loops=1) - Nested Loop (cost=0.43..5673.40 rows=1770 width=12) (actual time=0.050..1.374 rows=108 loops=1) - Seq Scan on temp_delete_delivery_id tmp (cost=0.00..27.70 rows=1770 width=14) (actual time=0.014..0.080 rows=108 loops=1) - Index Scan using onp_crm_entity_pkey on onp_crm_entity (cost=0.43..3.18 rows=1 width=14) (actual time=0.010..0.011 rows=1 loops=108) Index Cond: (entity_id = tmp.delivery_id) Planning time: 0.314 ms Trigger for constraint onp_crm_activity_entity_id_fkey: time=4.141 calls=108 Trigger for constraint ... Trigger for constraint ... Trigger for constraint ... I have lots of tables referencing onp_crm_entity(entity_id) so I expect the poor performance of deleting from it is caused by all the triggers firing to check FKI-constraints. Are there any ways around this or do people simply avoid having FKs in schemas like this? The classic problem is that one/more of your referring tables is non-trivial in size and you are missing an index on the referring column(s). Insure that this condition does not exist before butchering your design :-) Thanks. -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com www.visena.com [cid] -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Incredibly slow restore times after 9.09.2 upgrade
jmcdonagh joseph.e.mcdon...@gmail.com writes: I just had a thought- I know some of these tables are in need of a vacuuming. Could it be that the dump is dumping a bunch of garbage that the restore has to sift through on the restore? I don't know enough details to know if this is a dumb thought or not. No. However it's true that the dump will take a bit longer having to scan a bloated table rather than a tight one. Dump will only output the live rows. psql or pg_restore whatever you're using on the target side will not have to step over any junk. HTH The restore to RDS took roughly the same amount of time. My next move is to try on a fast instance store, and also do a postgres 9 restore of a pure SQL dump, but that won't really be a great test since I use custom format. I'm assuming here that I can't take the custom dump from 9.2 and apply it to 9.0, or can I? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Incredibly-slow-restore-times-after-9-0-9-2-upgrade-tp5824701p5825052.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Measuring the execution time of functions within functions...
Mario Splivalo [EMAIL PROTECTED] writes: I have asked this before, but haven't noticed any response, so if there were any, I appologize for asking this again... I have a function that is called by middle-tier (java trough JDBC), and in postgres log I can see only the execution time of that function. I have no idea how long are functions insde taking time to execute. Since the function is written in plpgsql I tried to calculate the durations by using now() function, but realized that within the transaction now() always retunrs the same value. The good thing is that those RAISE NOTICE calls from within my function are logged as they're encountered, so, with carefully placed RAISE NOTICE calls I could see how much time are the -inside- functions taking. For instance: CREATE FUNCTION test_outer() RETURNS void AS $$BODY$$BEGIN RAISE NOTICE 'We start here' PERFORM SELECT someInternalFunction1(); RAISE NOTICE 'InternalFunction1 is done now.'; PERFORM SELECT someInternalFunction2(); RAISE NOTICE 'InternalFunction2 is done now.'; -- ... more code here END$$BODY$$ LANGUAGE 'plpgsql' Is there any other, maybe more convinient way to measure the 'inside' function performance? I also have a problem if the outer function is written in SQL, like this, for instance: See the timeofday() func which returns the actual time and is not frozen in the current transaction. You'll need to cast it to timestamp or other if wishing to do time arithmetic deltas on it. HTH -- --- Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant 305 321-1144 (mobilehttp://www.JerrySievers.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Sudden slowdown of Pg server
Hello; I am going through a post mortem analysis of an infrequent but recurring problem on a Pg 8.0.3 installation. Application code connects to Pg using J2EE pooled connections. PostgreSQL 8.0.3 on sparc-sun-solaris2.9, compiled by GCC sparc-sun-solaris2.8-gcc (GCC) 3.3.2 Database is quite large with respect to the number of tables, some of which have up to 6 million tuples. Typical idle/busy connection ratio is 3/100 but occationally we'll catch 20 or more busy sessions. The problem manifests itself and appears like a locking issue. About weekly throuput slows down and we notice the busy connection count rising minute by minute. 2, 20, 40... Before long, the app server detects lack of responsiveness and fails over to another app server (not Pg) which in turn attempts a bunch of new connections into Postgres. Sampling of the snapshots of pg_locks and pg_stat_activity tables takes place each minute. I am wishing for a few new ideas as to what to be watching; Here's some observations that I've made. 1. At no time do any UN-granted locks show in pg_locks 2. The number of exclusive locks is small 1, 4, 8 3. Other locks type/mode are numerous but appear like normal workload. 4. There are at least a few old 'IDLE In Transaction' cases in activity view 5. No interesting error messages or warning in Pg logs. 6. No crash of Pg backend Other goodies includes a bounty of poor performing queries which are constantly being optimized now for good measure. Aside from the heavy queries, performance is generallly decent. Resource related server configs have been boosted substantially but have not undergone any formal RD to verify that we're inthe safe under heavy load. An max_fsm_relations setting which is *below* our table and index count was discovered by me today and will be increased this evening during a maint cycle. The slowdown and subsequent run-away app server takes place within a small 2-5 minute window and I have as of yet not been able to get into Psql during the event for a hands-on look. Questions; 1. Is there any type of resource lock that can unconditionally block another session and NOT appear as UN-granted lock? 2. What in particular other runtime info would be most useful to sample here? 3. What Solaris side runtime stats might give some clues here (maybe?)( and how often to sample? Assume needs to be aggressive due to how fast this problem crops up. Any help appreciated Thank you -- --- Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant 305 321-1144 (mobilehttp://www.JerrySievers.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
