Re: [PERFORM] Postgres log(pg_logs) have lots of message
On Wednesday, April 10, 2013, Nik Tek wrote: Hi Bambi, Thank you the prompt reply. This table is very volatile, lot of inserts/updates happen on this tables(atleast 20~30 inserts/min). That number of inserts per minute is not all that many. I suspect that you have sessions which are holding open transactions (and thus locks) for much longer than necessary, and it is this idling on the locks, not the active insertions, that is causing the current problem. If this is true, you should try to find the idle-in-transaction connections and fix them, because even if they didn't cause this particular problem, they will cause other ones. When auto vacuum tries to run on this table, I get this warning. LOG: automatic vacuum of table DB1.nic.pvxt: could not (re)acquire exclusive lock for truncate scan You have at least 8 MB of empty space at the end of the table, but to remove it it needs to acquire a lock that it cannot get and so it gives up. Unfortunately it now gives up on the following autoanalyze as well. In 9.2.2 and before, it would also give up on reclaiming the free space, but would likely still do the autoanalyze, which is probably why you didn't see it before. Is there a way, I force it to happen, because the table/indexes statistics are becoming stale very quickly. This is something introduced in 9.2.3, and will probably be fixed whenever 9.2.5 comes out. In the mean time, a manual ANALYZE (But not a VACUUM ANALYZE, because would fail the same was autvac does) would fix the stats, but it would have to be repeated often as they would just get stale again. You could try a VACUUM FULL or CLUSTER if you can tolerate the lock it would hold on the table while it operates. The reason that might solve the problem for you is that it would clear out the empty space, and therefore future autovac won't see that empty space and try to truncate it. Depending on how your table is used, either more empty space could accumulate at the end of the table causing the problem to recur, or maybe it would fix the problem for good. Cheers, Jeff
Re: [PERFORM] PsqL8.3
On 11/04/13 22:09, Thiyagarajan, Palaniappan wrote: All, We have performance issue in Psql8.3 vacuum run. The CPU usage going 300+ and application getting slow. How do we avoid high cpu and smooth vacuum tables. I'm afraid this isn't nearly enough information for anyone to help. 1. Full version details, some idea of hardware and database size might be useful. Exactly when this happens etc. 2. Is this a manual vacuum or autovacuum? 3. Are you familiar with the manual page regarding the vacuum settings? http://www.postgresql.org/docs/8.3/static/runtime-config-autovacuum.html 4. If so, what changes have you made? 5. You are aware that 8.3 is end-of-life? And you are running 8.3.23 until you upgrade, aren't you? http://www.postgresql.org/support/versioning/ Typically you'd expect disk i/o to be the limiting factor with vacuum rather than CPU. However, it might just be that I've misunderstood your description. More details please. -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Segment best size
Hi guys. I compiled my postrges server (9.1.4) with default segment size (16MB). Should it be enough? Should I increase this size in compilation?
[PERFORM] Changing ORDER BY column slows query dramatically
Hi, (I have put the contents of this email at http://pastebin.com/6h49zrYE for better readibility) I have been circling around the following problem for a few hours and I am wondering if anyone has any suggestions about why the the second query is taking so much longer than the first. Both tables have similar row counts, all tables are partitions, though the queries are querying the partitions directly, not the parent tables. The 'DELIVERED' status comprises almost 100% of the rows in the notification_xxx tables. ANALYZE was run right before each query was issued. This test database is on EC2. PG 9.2.4, CentOS release 5.9, 70GB RAM. GUC changes listed at the bottom. Are there any other details I can supply? Note that the same behaviour occurred on 9.2.3, but I cannot say for sure for whether it occurred on 9.2.2. Dropping the random_page_cost to 1 helped the slow query (after it is cached, drops down to about 1.7s). Setting enable_mergejoin to false causes the slow query to choose a Nested Loop and shaves off about 30% of the execution time. -- Fast query, first run, uncached, ORDER BY nearly-unique tstamp_utc explain (analyze, buffers) SELECT e.id,e.device,e.engine,e.form,e.device_type_id,ncbs.delivery_tstamp_utc FROM event_20130404 e INNER JOIN notifications_20130404 ncbs ON (e.id = ncbs.event_id) WHERE TRUE AND e.date_utc = '2013-04-04' AND e.tstamp_utc BETWEEN '2013-04-04 10:00:00' AND '2013-04-04 18:00:00' AND e.org_id = 216471 AND ncbs.event_creation_tstamp_utc BETWEEN '2013-04-04 10:00:00' AND '2013-04-04 18:00:00' AND ncbs.status = 'DELIVERED' ORDER BY e.tstamp_utc desc offset 1 limit 100; Limit (cost=29639.24..29935.63 rows=100 width=50) (actual time=159.103..167.637 rows=100 loops=1) Buffers: shared hit=46588 read=1055 I/O Timings: read=63.416 - Nested Loop (cost=0.00..1882500.73 rows=635138 width=50) (actual time=0.240..159.693 rows=10100 loops=1) Buffers: shared hit=46588 read=1055 I/O Timings: read=63.416 - Index Scan Backward using event_20130404_tstamp_utc_org_id_idx on event_20130404 e (cost=0.00..315412.34 rows=1876877 width=42) (actual time=0.129..61.981 rows=10100 loops=1) Index Cond: ((tstamp_utc = '2013-04-04 10:00:00'::timestamp without time zone) AND (tstamp_utc = '2013-04-04 18:00:00'::timestamp without time zone) AND (org_id = 216471)) Filter: (date_utc = '2013-04-04'::date) Buffers: shared hit=6309 read=833 I/O Timings: read=40.380 - Index Scan using notifications_20130404_event_id_org_id_pk on notifications_20130404 ncbs (cost=0.00..0.82 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=10100) Index Cond: (event_id = e.id) Filter: ((event_creation_tstamp_utc = '2013-04-04 10:00:00'::timestamp without time zone) AND (event_creation_tstamp_utc = '2013-04-04 18:00:00'::timestamp without time zone) AND (status = 'DELIVERED'::text)) Buffers: shared hit=40279 read=222 I/O Timings: read=23.036 Total runtime: 170.436 ms (17 rows) -- Slow query, uncached, ORDER BY primary key explain (analyze,buffers) SELECT e.device,e.id,e.engine,e.form,e.device_type_id,ncbs.delivery_tstamp_utc FROM event_20130405 e INNER JOIN notifications_20130405 ncbs ON (e.id = ncbs.event_id) WHERE TRUE AND e.date_utc = '2013-04-05' AND e.tstamp_utc BETWEEN '2013-04-05 10:00:00' AND '2013-04-05 18:00:00' AND e.org_id = 216471 AND ncbs.event_creation_tstamp_utc BETWEEN '2013-04-05 10:00:00' AND '2013-04-05 18:00:00' AND ncbs.status = 'DELIVERED' ORDER BY e.id desc OFFSET 1 LIMIT 100; Limit (cost=14305.61..14447.99 rows=100 width=42) (actual time=13949.028..13950.353 rows=100 loops=1) Buffers: shared hit=2215465 read=141796 written=12128 I/O Timings: read=11308.341 write=116.673 - Merge Join (cost=67.31..887063.35 rows=622965 width=42) (actual time=13761.135..13933.879 rows=10100 loops=1) Merge Cond: (e.id = ncbs.event_id) Buffers: shared hit=2215465 read=141796 written=12128 I/O Timings: read=11308.341 write=116.673 - Index Scan Backward using event_20130405_id_pk on event_20130405 e (cost=0.00..612732.34 rows=1889715 width=34) (actual time=2076.812..2111.274 rows=10100 loops=1) Filter: ((tstamp_utc = '2013-04-05 10:00:00'::timestamp without time zone) AND (tstamp_utc = '2013-04-05 18:00:00'::timestamp without time zone) AND (date_utc = '2013-04-05'::date) AND (org_id = 216471)) Rows Removed by Filter: 1621564 Buffers: shared hit=1176391 read=113344 written=11918 I/O Timings: read=774.769 write=113.095 - Index Scan Backward using notifications_20130405_event_id_org_id_pk on notifications_20130405 ncbs (cost=0.00..258079.61 rows=2135847 width=16) (actual time=11684.312..11784.738 rows=11653 loops=1) Filter: ((event_creation_tstamp_utc = '2013-04-05 10:00:00'::timestamp without time zone)
Re: [PERFORM] Segment best size
On Apr 12, 2013, at 9:45 AM, Rodrigo Barboza wrote: Hi guys. I compiled my postrges server (9.1.4) with default segment size (16MB). Should it be enough? Should I increase this size in compilation? Unlike some default values in the configuration file, the compiled-in defaults work well for most people. -- 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] Segment best size
On Fri, Apr 12, 2013 at 2:52 PM, Ben Chobot be...@silentmedia.com wrote: On Apr 12, 2013, at 9:45 AM, Rodrigo Barboza wrote: Hi guys. I compiled my postrges server (9.1.4) with default segment size (16MB). Should it be enough? Should I increase this size in compilation? Unlike some default values in the configuration file, the compiled-in defaults work well for most people. Thanks!
Re: [PERFORM] Changing ORDER BY column slows query dramatically
On 04/12/2013 11:51 AM, brick pglists wrote: - Index Scan Backward using event_20130405_id_pk on event_20130405 e (cost=0.00..612732.34 rows=1889715 width=34) (actual time=2076.812..2111.274 rows=10100 loops=1) Filter: ((tstamp_utc = '2013-04-05 10:00:00'::timestamp without time zone) AND (tstamp_utc = '2013-04-05 18:00:00'::timestamp without time zone) AND (date_utc = '2013-04-05'::date) AND (org_id = 216471)) This right here is your culprit. The planner thinks it'll be faster to grab 100 rows by scanning your primary key backwards and filtering out the matching utc timestamps and other criteria. Since it doesn't show up in your GUC list, you should probably increase your default_statistics_target to 400 or more, analyze, and try again. The heuristics for the dates aren't complete enough, so it thinks there are few matches. If that doesn't work and you want a quick, but ugly fix for this, you can create the following index: CREATE INDEX event_20130406_id_desc_tstamp_utc_idx ON event_20130406 (id DESC, tstamp_utc); -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- 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] Changing ORDER BY column slows query dramatically
Hi Shaun, On Fri, Apr 12, 2013 at 12:59 PM, Shaun Thomas stho...@optionshouse.com wrote: On 04/12/2013 11:51 AM, brick pglists wrote: Since it doesn't show up in your GUC list, you should probably increase your default_statistics_target to 400 or more, analyze, and try again. The heuristics for the dates aren't complete enough, so it thinks there are few matches. If that doesn't work and you want a quick, but ugly fix for this, you can create the following index: CREATE INDEX event_20130406_id_desc_tstamp_utc_idx ON event_20130406 (id DESC, tstamp_utc); Thanks for your suggestions. Bumping up the default_statistics_target several times all the way to 4000 (ANALYZEd each time) did not help, however, adding the index you suggested helped with that query. It is still over a magnitude slower than the version that sorts by tstamp_utc, but it's a start. I created a similar index (CREATE INDEX event_20130406_id_desc_tstamp_utc_desc_idx ON event_20130406 (id DESC, tstamp_utc DESC)) where both columns were sorted DESCm and given the choice between those two, it chose the latter. Setting enable_mergejoin to false results in a plan much closer to the original fast one, and further changing cpu_tuple_cost up to 1 results in a query about 3x slower than the original fast one. The ORDER BY e.id query, with the new index, enable_mergejoin disabled, and cpu_tuple_cost bumped up to 1: Limit (cost=125386.16..126640.02 rows=100 width=42) (actual time=220.807..221.864 rows=100 loops=1) Buffers: shared hit=49171 read=6770 I/O Timings: read=44.980 - Nested Loop (cost=0.00..7734858.92 rows=616883 width=42) (actual time=110.718..213.923 rows=10100 loops=1) Buffers: shared hit=49171 read=6770 I/O Timings: read=44.980 - Index Scan using event_20130406_id_desc_tstamp_utc_desc_idx on event_20130406 e (cost=0.00..2503426.81 rows=1851068 width=34) (actual time=110.690..139.001 rows=10100 loops=1) Index Cond: ((tstamp_utc = '2013-04-06 10:00:00'::timestamp without time zone) AND (tstamp_utc = '2013-04-06 18:00:00'::timestamp without time zone)) Filter: ((date_utc = '2013-04-06'::date) AND (org_id = 216471)) Rows Removed by Filter: 1554 Buffers: shared hit=8647 read=6770 I/O Timings: read=44.980 - Index Scan using notification_counts_by_status_20130406_event_id_org_id_pk on notification_counts_by_status_20130406 ncbs (cost=0.00..1.83 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=10100) Index Cond: (event_id = e.id) Filter: ((event_creation_tstamp_utc = '2013-04-06 10:00:00'::timestamp without time zone) AND (event_creation_tstamp_utc = '2013-04-06 18:00:00'::timestamp without time zone) AND (status = 'DELIVERED'::text)) Buffers: shared hit=40524 Total runtime: 222.127 ms (17 rows) Still not at the ~90ms from the ORDER BY e.tstamp_utc DESC version, but not too bad. Now I need to figure out how I can get the best plan choice without monkeying around with enable_mergejoin and changing cpu_tuple_cost too much. If any more suggestions are forthcoming, I am all ears! -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Recommended Swap space
Hi, What is the recommended swap space for postgres or 9.0.11 or 9.2.3 on linux(3.0.58-0.6) RAM on the hox is 32GB. Thank you Nik
Re: [PERFORM] Recommended Swap space
My experience is that you're best off either with a swap space that matches or exceeds physical memory, or none at all. The linux kernel swap daemon (kswapd) gets confused and behaves badly with small swap spaces, especially the more memory you have. Good news is that hard drives are cheap and 32G isn't a lot of memory so I'd set it up for 32G + a tiny bit. Once a machine gets to larger memory sizes (128G and more) I just turn off swap. The really messed up bit is that the problems with the kswapd won't show up for weeks, months, or sometimes even longer. The symptoms of a kswapd problem is that swap is mostly full, but there's LOTS of free memory / kernel cache, and the kswapd is busily swapping in / out. Page faults skyrocket and the problem can last for a few minutes or a few hours, then clear up and not occur again for a long time. tl;dr: Either physical memory + a little or none. On Fri, Apr 12, 2013 at 3:05 PM, Nik Tek niktek2...@gmail.com wrote: Hi, What is the recommended swap space for postgres or 9.0.11 or 9.2.3 on linux(3.0.58-0.6) RAM on the hox is 32GB. Thank you Nik -- To understand recursion, one must first understand recursion.
Re: [PERFORM] Recommended Swap space
On Fri, Apr 12, 2013 at 6:15 PM, Scott Marlowe scott.marl...@gmail.com wrote: The really messed up bit is that the problems with the kswapd won't show up for weeks, months, or sometimes even longer. The symptoms of a kswapd problem is that swap is mostly full, but there's LOTS of free memory / kernel cache, and the kswapd is busily swapping in / out. Page faults skyrocket and the problem can last for a few minutes or a few hours, then clear up and not occur again for a long time. Isn't that a NUMA issue? -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Default value checkpoint_completion_target
Hi guys. I read in some forums that this parameter is better to set it to something like 0.7. Mine is default (0.5) and sometime it logs the message: WARNING: pgstat wait timeout Could there be any relation between this parameter and the warning message? Is it safe to set it to 0.7?
Re: [PERFORM] Recommended Swap space
No, I've had it happen with NUMA turned off. The NUMA issues are with zone_reclaim_mode. If you have it set to 1 (default on a lot of big machines) it can cause serious problems with performance as well. On Fri, Apr 12, 2013 at 3:21 PM, Claudio Freire klaussfre...@gmail.comwrote: On Fri, Apr 12, 2013 at 6:15 PM, Scott Marlowe scott.marl...@gmail.com wrote: The really messed up bit is that the problems with the kswapd won't show up for weeks, months, or sometimes even longer. The symptoms of a kswapd problem is that swap is mostly full, but there's LOTS of free memory / kernel cache, and the kswapd is busily swapping in / out. Page faults skyrocket and the problem can last for a few minutes or a few hours, then clear up and not occur again for a long time. Isn't that a NUMA issue? -- To understand recursion, one must first understand recursion.
Re: [PERFORM] Segment best size
On Friday, April 12, 2013, Rodrigo Barboza wrote: Hi guys. I compiled my postrges server (9.1.4) with default segment size (16MB). Should it be enough? Should I increase this size in compilation? To recommend that you deviate from the defaults, we would have to know something about your server, or how you intend to use it. But you haven't told us any of those things. Is there something about your intended use of the server that made you wonder about this setting in particular? Anyway, wal seg size is one of the last things I would worry about changing. Also, I suspect non-default settings of that parameter get almost zero testing (either alpha, beta, or in the field), and so would consider it mildly dangerous to deploy to production. Cheers, Jeff
Re: [PERFORM] slow bitmap heap scans on pg 9.2
On Thursday, April 11, 2013, Steve Singer wrote: I think the reason why it is picking the hash join based plans is because of Index Scan using table_b_1_ptid_orgid_ym_unq on table_b_1 b (cost=0.00..503.86 rows=1 width=10) (actual time=0.016..0.017 rows=1 loops=414249) Index Cond: ((a.id = a_id) AND (organization_id = 2) AND (year = 2013) AND (month = 3)) Filter: (product_id = 1) Trying to reason about how the planner estimates costs for the inner side of nested loops makes my head hurt. So before doing that, could you run explain (analyze,buffers) on both of these much simpler (but hopefully morally equivalent to this planner node) sql: select * from table_b_1_b where a_id = some plausible value and organization_id=2 and year=2013 and month=3 select * from table_b_1_b where a_id = some plausible value and organization_id=2 and year=2013 and month=3 and product_id=1 Of particular interest here is whether the estimate of 1 row is due to the specificity of the filter, or if the index clauses alone are specific enough to drive that estimate. (If you get many rows without the product_id filter, that would explain the high estimate.). Please run with the default cost parameters, or if you can't get the right plan with the defaults, specify what the used parameters were. Cheers, Jeff
