Re: [PERFORM] Auto generate number in Postgres-9.1.

at's not how it works, normally. I'd suggest adding an ON TRUNCATE trigger to the table. -- Josh Berkus Containers & Databases Oh My! -- 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] DELETE takes too much memory

e rows? Incidentally, any time I get into deleting large numbers of rows, I generally find it faster to rebuild the table instead ... -- -- Josh Berkus Red Hat OSAS (any opinions are my own) -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription

Re: [PERFORM] array size exceeds the maximum allowed (1073741823) when building a json

.​ That's nice to hear. > Will this 1GO restriction is supposed to increase in a near future ?​ Not planned, no. Thing is, that's the limit for a field in general, not just JSON; changing it would be a fairly large patch. It's desireable, but AFAIK nobody is working on it. -- -- J

Re: [PERFORM] Query optimizer plans with very small selectivity estimates

es for previous threads. Also see Tomas's correlated stats patch submitted for 9.6. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- 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] shared-buffers set to 24GB but the RAM only use 4-5 GB average

On 10/06/2015 02:33 AM, FattahRozzaq wrote: > @Merlin Moncure, I got the calculation using pg_tune. And I modified > the shared_buffers=24GB and the effective_cache_size=64GB I really need to get Greg to take down pg_tune. It's way out of date. Probably, I should replace it. --

Re: [PERFORM] Queries Per Second (QPS)

to mention missing 5 years of performance improvements ... -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- 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] Strange query stalls on replica in 9.3.9

gnificantly, the seqscan query is also the most complex query run against the replica, so maybe the seqscan is irrelevant and it's being affected by planner issues? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@

[PERFORM] Strange query stalls on replica in 9.3.9

only takes 2.5 seconds to execute on the master. So even if the update is blocking the seq scans on the replica (and I can't see why it would), it should only block them for < 3 seconds. Anyone seen anything like this? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sen

Re: [PERFORM] 9.5alpha1 vs 9.4

after migration to 9.5. Thank you for testing! Can you re-run your tests with the fixed schema? How does it look? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your

Re: [PERFORM] Sudden connection and load average spikes with postgresql 9.3

e that. I think you have a driver, kernel, Linux memory management, or IO stack issue. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.or

Re: [PERFORM] Sudden connection and load average spikes with postgresql 9.3

on X and/or Y possibly overwriting the same rows on Z? * is that autovacuum a regular autovacuum, or is it "to prevent wraparound"? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make c

Re: [PERFORM] Sudden connection and load average spikes with postgresql 9.3

periodically. What does IO throughput look like before/during/after the stalls? The last was the cause the last time I dealt with a situation like yours; it turned out the issue was bad RAID card firmware where the card would lock up whenever the write-through buffer got too much pressure.

[PERFORM] Does anyone have python code which digests pgbench -r output?

ping a new public benchmark. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- 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] Row estimates off by two orders of magnitude with hstore

> custom ROWS clause > > *) try alternate indexing strategy such as jsonb/jsquery > > *) move out of hstore and into more standard relational strucure You forgot: *) Fund a PostgreSQL developer to add selectivity estimation and stats to hstore. -- Josh Berkus PostgreSQL Experts

Re: [PERFORM] ERROR: missing chunk number 0 for toast value 1821556134 in pg_toast_17881

ld have caused this particular error. Are you certain that this is a recent problem? Note that this error affects just one compressed value or row, so you're not losing other data, unless it's a symptom of an ongoing problem. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com

Re: [PERFORM] PostgreSQL disk fragmentation causes performance problems on Windows

On 05/21/2015 01:39 PM, Andres Freund wrote: > On 2015-05-21 11:54:40 -0700, Josh Berkus wrote: >> This has been talked about as a feature, but would require major work on >> PostgreSQL to make it possible. You'd be looking at several months of >> effort by a really goo

Re: [PERFORM] PostgreSQL disk fragmentation causes performance problems on Windows

d hacker, and then a whole bunch of performance testing. If you have the budget for this, then please let's talk about it because right now nobody is working on it. Note that this could be a dead end; it's possible that preallocating large extents could cause worse problems than the current f

Re: [PERFORM] Some performance testing?

(read-write) > > > performance with 3.18 (pgbench, size 100, 32 clients) > > 129 303 transactions per second (read only) > 16 895 transactions (read-write) Thanks for that data! I'm glad to see that 3.18 has improved so much. -- Josh Berkus PostgreSQL Experts Inc. h

Re: [PERFORM] Some performance testing?

nce is literally 2X to 5X different between kernels. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- 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] Some performance testing?

On 04/07/2015 09:46 AM, Mel Llaguno wrote: > FYI - all my tests were conducted using Ubuntu 12.04 x64 LTS (which I > believe are all 3.xx series kernels). If it's 3.2 or 3.5, then your tests aren't useful, I'm afraid. Both of those kernels have known, severe, memory managem

Re: [PERFORM] Some performance testing?

d, I can't swap out kernels. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Some performance testing?

All, I currently have access to a matched pair of 20-core, 128GB RAM servers with SSD-PCI storage, for about 2 weeks before they go into production. Are there any performance tests people would like to see me run on these? Otherwise, I'll just do some pgbench and DVDStore. -- Josh B

Re: [PERFORM] Bad cost estimate with FALSE filter condition

On 03/16/2015 11:26 AM, Tom Lane wrote: > Josh Berkus writes: >> So ... should I assume my diagnosis is correct? Haven't heard any other >> suggestions. > > I don't see any reason to think this is worth worrying about, or worth > spending planner cycles on to

Re: [PERFORM] MusicBrainz postgres performance issues

ernels 3.0 to 3.8 really needs to upgrade soon. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- 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] Bad cost estimate with FALSE filter condition

So ... should I assume my diagnosis is correct? Haven't heard any other suggestions. On 02/27/2015 05:28 PM, Josh Berkus wrote: > All: > > This got posted to pgsql-bugs, but got no attention there[1], so I'm > sending it to this list. > > Test case: > > cr

[PERFORM] Bad cost estimate with FALSE filter condition

the planner returns a fictitious cost for the whole query. Or is there something else at work here? [1] http://www.postgresql.org/message-id/20150225194953.2546.86...@wrigleys.postgresql.org -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing li

Re: [PERFORM] Reverse Key Index

/ > carefully, it also seems to work with index scan partially in case of > equality comparisons. Seems like a good use for SP-GiST. Go for it! -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)

Re: [PERFORM] Poor performance when deleting from entity-attribute-value type master-table

fact, I am just today dismantling an EAV database and normalizing it, and so far application throughput is up 500%) -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.po

Re: [PERFORM] working around JSONB's lack of stats?

On 02/02/2015 05:48 PM, Jim Nasby wrote: > On 2/1/15 3:08 PM, Josh Berkus wrote: >> I'm not clear on what you're suggesting here. I'm discussing how the >> stats for a JSONB field would be stored and accessed; I don't understand >> what that has to d

Re: [PERFORM] working around JSONB's lack of stats?

On 01/30/2015 05:34 PM, Jim Nasby wrote: > On 1/30/15 2:26 PM, Josh Berkus wrote: >> This would probably work because there aren't a lot of data structures >> where people would have the same key:value pair in different locations >> in the JSON, and care about it stats-w

Re: [PERFORM] Unexpected (bad) performance when querying indexed JSONB column

Seq Scan on articles (cost=0.00..2289.21 rows=33 width=427)" > " Filter: (data @> '{"locked": true}'::jsonb)" > --- Please send us the output of EXPLAIN ( ANALYZE ON, BUFFERS ON ) so that we can see what the query is actually doing, rather than just wha

Re: [PERFORM] working around JSONB's lack of stats?

On 01/28/2015 03:50 PM, Peter Geoghegan wrote: > On Wed, Jan 28, 2015 at 3:42 PM, Josh Berkus wrote: >> jsonb_col @> '[ "key1" ]' >> or jsonb_col ? 'key1' >> if in MCE, assign % from MCE >> otherwise assign 1% of non-MCE

Re: [PERFORM] working around JSONB's lack of stats?

On 01/28/2015 03:34 PM, Peter Geoghegan wrote: > On Wed, Jan 28, 2015 at 3:03 PM, Josh Berkus wrote: >> We already have most_common_elem (MCE) for arrays and tsearch. What if >> we put JSONB's most common top-level keys (or array elements, depending) >> in the MCE a

Re: [PERFORM] working around JSONB's lack of stats?

On 01/28/2015 11:48 AM, Tomas Vondra wrote: > On 27.1.2015 08:06, Josh Berkus wrote: >> Folks, >> > ... >> >> On a normal column, I'd raise n_distinct to reflect the higher >> selecivity of the search terms. However, since @> uses contsel, >

[PERFORM] working around JSONB's lack of stats?

; '["math", "physics"]'::jsonb) Planning time: 0.093 ms Execution time: 7.632 ms On a normal column, I'd raise n_distinct to reflect the higher selecivity of the search terms. However, since @> uses contsel, n_distinct is ignored. Anyone know a clever

Re: [PERFORM] Strange choice of general index over partial index

uspect that it's the other issue with Tom mentioned, which is that 9.2 really doesn't take physical index size into account. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to yo

Re: [PERFORM] Strange choice of general index over partial index

can on > file_state. Apologies for the confusion. > > I'm thinking that I'm seeing the effect Tom has just mentioned. It's not using a bitmapscan in either case; it's a straight indexscan. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent

[PERFORM] Strange choice of general index over partial index

ecause the same query, using file_state, is 20X to 50X slower, because that index frequently gets pushed out of memory. What am I missing? Or is this potentially a planner bug for costing? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list

Re: [PERFORM] Re: [SQL] querying with index on jsonb slower than standard column. Why?

variadic return times from a function which don't match variadic input types. Returning a value as an actual numeric from JSONB would require returning a numeric from a function whose input type is text or json. So a known issue but one which would require a lot of replumbing to fix. -

Re: [PERFORM] Yet another abort-early plan disaster on 9.3

o I'll need to do some work to recreate the original bad plan circumstances. I'll keep you posted on how the patch works for that setup. It would be great to come up with a generic/public test for a bad abort-early situation. Ideas? -- Josh Berkus PostgreSQL Experts Inc. http://pgexpe

Re: [PERFORM] issue in postgresql 9.1.3 in using arrow key in Solaris platform

ne or libedit libraries for your platform. How did you build PostgreSQL? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- 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] Lock pileup causes server to stall

enced by a foreign key constraint". Oh, come on. We had hardly any problems with that patch! ;-) -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.po

Re: [PERFORM] Lock pileup causes server to stall

On 11/10/2014 01:40 PM, Alvaro Herrera wrote: > Josh Berkus wrote: >> All, >> >> pg version: 9.3.5 >> RHEL 6.5 >> 128GB/32 cores >> Configured with shared_buffers=16GB >> Java/Tomcat/JDBC application >> >> Server has an issue that whenever

Re: [PERFORM] Performance bug in prepared statement binding in 9.2?

ven that I'm seeing preposterously long BIND times (like 50 seconds), I don't think that's explained just by bad plans. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Lock pileup causes server to stall

27;ll have more information when I do: for example, is it ALL queries which are slow or just some of them? However, I thought this list would have some other ideas where to look. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pg

Re: [PERFORM] Performance bug in prepared statement binding in 9.2?

On 11/10/2014 11:11 AM, Tom Lane wrote: > Josh Berkus writes: >> On 11/10/2014 10:59 AM, Jeff Janes wrote: >>> On Mon, Nov 10, 2014 at 10:48 AM, Josh Berkus wrote: >>>> Did this patch every make it in? Or did it hang waiting for verification? &g

Re: [PERFORM] Performance bug in prepared statement binding in 9.2?

On 11/10/2014 10:59 AM, Jeff Janes wrote: > On Mon, Nov 10, 2014 at 10:48 AM, Josh Berkus wrote: > >> On 12/31/2013 09:55 AM, Tom Lane wrote: >>> Josh Berkus writes: >>>> Tom, >>>>> There's an abbreviated version of this argument in

Re: [PERFORM] 9.3 performance issues, lots of bind and parse log entries

Tory, Do you know if your workload involves a lot of lock-blocking, particularly blocking on locks related to FKs? I'm tracing down a problem which sounds similar to yours. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (

Re: [PERFORM] Performance bug in prepared statement binding in 9.2?

On 12/31/2013 09:55 AM, Tom Lane wrote: > Josh Berkus writes: >> Tom, >>> There's an abbreviated version of this argument in the comments in >>> my proposed patch at >>> http://www.postgresql.org/message-id/11927.1384199...@sss.pgh.pa.us >>

Re: [PERFORM] pgtune + configurations with 9.3

that was on older versions of Postgres). Yeah, pgTune is pretty badly out of date. It's been on my TODO list, as I'm sure it has been on Greg's. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgre

[PERFORM] 9.4 performance improvements test

672.821433 (excluding connections establishing) I suspect this is due to the improvements in writing less to WAL. If so, good work, guys! -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your su

Re: [PERFORM] Partitions and work_mem?

ing else? RH probably backpatches our fixes as they come out. They did in the past, anyway. I just had the impression from your original post that this was a new system; if so, it would make sense to build it on a version of Postgres which wasn't already EOL. -- Josh Berkus PostgreSQL Expert

Re: [PERFORM] Yet another abort-early plan disaster on 9.3

On 10/10/2014 04:16 AM, Greg Stark wrote: > On Thu, Oct 2, 2014 at 8:56 PM, Josh Berkus wrote: >> Yes, it's only intractable if you're wedded to the idea of a tiny, >> fixed-size sample. If we're allowed to sample, say, 1% of the table, we >> can get a MU

Re: [PERFORM] Partitions and work_mem?

kend ever used more than 3X work_mem. This is partly because the level of parallelism in postgres is extremely limited, so we can't actually sort 8 partitions at the same time. BTW, 8.4 is EOL. Maybe time to upgrade? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent vi

Re: [PERFORM] Yet another abort-early plan disaster on 9.3

where underestimating n_distinct produced a penalty. Now we do, and we ought to change algos. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- 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] Yet another abort-early plan disaster on 9.3

ing cost estimates based on what the worst case cost looks like, correct? That seemed to be your proposal from an earlier post. If so, we're in violent agreement here. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- 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] Yet another abort-early plan disaster on 9.3

On 09/26/2014 01:06 AM, Simon Riggs wrote: > On 23 September 2014 00:56, Josh Berkus wrote: > >> We've hashed that out a bit, but frankly I think it's much more >> profitable to pursue fixing the actual problem than providing a >> workaround like "ris

Re: [PERFORM] Yet another abort-early plan disaster on 9.3

ual problem than providing a workaround like "risk", such as: a) fixing n_distinct estimation b) estimating stacked quals using better math (i.e. not assuming total randomness) c) developing some kind of correlation stats Otherwise we would be just providing users with another knob there&#x

Re: [PERFORM] Yet another abort-early plan disaster on 9.3

On 09/19/2014 11:38 PM, Greg Stark wrote: > > On 19 Sep 2014 19:40, "Josh Berkus" <mailto:j...@agliodbs.com>> wrote: >> >> On 09/19/2014 10:15 AM, Merlin Moncure wrote: >> > On Wed, Sep 17, 2014 at 7:11 PM, Josh Berkus <mailto:j...@agliodbs.com

Re: [PERFORM] query a table with lots of coulmns

due to transmission time, not disk IO. Otherwise, please post your schema (well, a truncated version) and your queries. BTW, in cases like yours I've used a INT array instead of 500 columns to good effect; it works slightly better with PostgreSQL's compression. -- Josh Berkus PostgreSQ

Re: [PERFORM] Yet another abort-early plan disaster on 9.3

On 09/19/2014 10:15 AM, Merlin Moncure wrote: > On Wed, Sep 17, 2014 at 7:11 PM, Josh Berkus wrote: >> This is the core issue with abort-early plans; they depend on our >> statistics being extremely accurate, which we know they are not. And if >> they're wrong, the exec

Re: [PERFORM] postgres 9.3 vs. 9.4

which is a < 1MB database, and one client and 1 thread, which is an interesting test I wouldn't necessarily have done myself. I'll throw the same test on one of my machines and see how it does. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance

[PERFORM] Yet another abort-early plan disaster on 9.3

climbs by 1000X or more. Abort-early plans are inherently riskier than other types of query plans. What I'm not clear on is why upgrading from 9.1 to 9.3 would bring about this change. The stats are no more than 10% different across the version change. -- Josh Berkus PostgreSQL Experts Inc. http

Re: [PERFORM] Performance issue: index not used on GROUP BY...

nality of ddet_id, it might actually be slower to use the index). In addition, other folks on this thread have already pointed out the memory settings issues to you. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-perfo

Re: [PERFORM] Turn off Hyperthreading! WAS: 60 core performance with 9.3

On 08/22/2014 07:02 AM, Andres Freund wrote: > On 2014-08-21 14:02:26 -0700, Josh Berkus wrote: >> On 08/20/2014 07:40 PM, Bruce Momjian wrote: >>> Not sure how you can make such a blanket statement when so many people >>> have tested and shown the benefits of hyper-thr

Re: [PERFORM] Turn off Hyperthreading! WAS: 60 core performance with 9.3

On 08/21/2014 04:08 PM, Steve Crawford wrote: > On 08/21/2014 03:51 PM, Josh Berkus wrote: >> On 08/21/2014 02:26 PM, Scott Marlowe wrote: >>> I'm running almost the exact same setup in production as a spare. It >>> has 4 of those CPUs, 256G RAM, and is currently

Re: [PERFORM] Turn off Hyperthreading! WAS: 60 core performance with 9.3

g a 3.2 kernel right now. I could probably get a later > model kernel on it even. You know about the IO performance issues with 3.2, yes? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make chan

Re: [PERFORM] Turn off Hyperthreading! WAS: 60 core performance with 9.3

On 08/21/2014 02:11 PM, Bruce Momjian wrote: > On Thu, Aug 21, 2014 at 02:02:26PM -0700, Josh Berkus wrote: >> On 08/20/2014 07:40 PM, Bruce Momjian wrote: >>> On Wed, Aug 20, 2014 at 12:13:50PM -0700, Josh Berkus wrote: >>>> On a read-write test, it&#x

Re: [PERFORM] Turn off Hyperthreading! WAS: 60 core performance with 9.3

On 08/20/2014 07:40 PM, Bruce Momjian wrote: > On Wed, Aug 20, 2014 at 12:13:50PM -0700, Josh Berkus wrote: >> On a read-write test, it's 10% faster with HT off as well. >> >> Further, from their production machine we've seen that having HT on >> causes the ma

Re: [PERFORM] Turn off Hyperthreading! WAS: 60 core performance with 9.3

rther, from their production machine we've seen that having HT on causes the machine to slow down by 5X whenever you get more than 40 cores (as in 100% of real cores or 50% of HT cores) worth of activity. So we're definitely back to "If you're using PostgreSQL, turn off Hyperthr

Re: [PERFORM] 60 core performance with 9.3

Mark, Is the 60-core machine using some of the Intel chips which have 20 hyperthreaded virtual cores? If so, I've been seeing some performance issues on these processors. I'm currently doing a side-by-side hyperthreading on/off test. -- Josh Berkus PostgreSQL Experts Inc. http://pge

[PERFORM] Optimization idea for long IN() lists

Folks, So one thing we tell users who have chronically long IN() lists is that they should create a temporary table and join against that instead. Other than not having the code, is there a reason why PostgreSQL shouldn't do something like this behind the scenes, automatically? -- Josh B

[PERFORM] Why you should turn on Checksums with SSDs

Explained here: https://www.usenix.org/system/files/conference/fast13/fast13-final80.pdf 13 out of 15 tested SSD's had various kinds of corruption on a power-out. (thanks, Neil!) -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (

Re: [PERFORM] High rate of transaction failure with the Serializable Isolation Level

e! Any idea when it will be available? Our community could really use some updated benchmark tooling ... -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.po

Re: [PERFORM] PGSQL 9.3 - billion rows

ation do I need ? How much RAM ? > - If not, would it be better to think about a cluster or other ? > - (Have you any idea to optimize this table ?) Consider also trying cstore_fdw: https://github.com/citusdata/cstore_fdw -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent

Re: [PERFORM] NFS, file system cache and shared_buffers

Linux kernel module. NFS on Solaris/Illumos is a different story. Not sure about FreeBSD. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- 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] Revisiting disk layout on ZFS systems

AM? 2) is this a DW workload, where most writes are large writes? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- 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] HFS+ pg_test_fsync performance

d run some different sizes of pgbench. I'd be particularly interested in the performance of ZFS tuning options on Linux ... -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subsc

Re: [PERFORM] HFS+ pg_test_fsync performance

we probably would have done more design changes!" HFS+ was written in about 6 months, and is largely unimproved since its release in 1995. Ext2 doesn't perform too well, either; the difference is that Linux users have alternative filesystems available. -- Josh Berkus PostgreSQL Exper

Re: [PERFORM] Connection pooling - Number of connections

f a buffer of ready connections to deal with the next peak when it comes in. That also means that even if the pool is a fixed size, you want to rotate in and out the actual sessions, so that they don't hang onto maximum virtual memory indefinitely. -- Josh Berkus PostgreSQL Experts Inc. http://p

Re: [PERFORM] Planner performance extremely affected by an hanging transaction (20-30 times)?

vel did test it and reported that it successfully alleviates his > real-world problem. So I'm now inclined to commit this. Objections? None from me. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@pos

Re: [PERFORM] Time of query result delivery

environment. Beyond that, I can't speculate. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- 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] Time of query result delivery

I'm really not clear on what you're trying to measure here. If you're doing "time" from your PC, then network transmission time completely dominates your reponse time ... and that can be affected by all kinds of random variables. -- Josh Berkus PostgreSQL Experts Inc. http://

Re: [PERFORM] One huge db vs many small dbs

for separate databases. This will mean lots of additional storage space -- the per-DB overhead by itself will be 100GB -- but otherwise you'll be grappling with the issues involved in having a million tables, which Joe Conway outlined. But if you don't have shared tables, your huge s

Re: [PERFORM] Postgresql in a Virtual Machine

we were entertaining the idea of running our Postgres database > on our VM farm alongside our application vm's. We are planning to run a > few Postgres synchronous replication nodes. Biggest pitfall here is IO performance configuration. I can't give you specific advice without knowing

Re: [PERFORM] Performance bug in prepared statement binding in 9.2?

their problems. We can't really determine > what to do without that information. Unfortunately, the original reporter of this issue will not be available for testing for 2-3 weeks, and I haven't been able to devise a synthetic test which clearly shows the issue. -- Josh Berkus Postgr

Re: [PERFORM] postgresql recommendation memory

gt; max_connections = 350 > shared_buffers = 8GB Try dropping shared_buffers to 2GB. We've seen some issues on certain systems with 8GB shared buffers. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make c

Re: [PERFORM] Adding foreign key constraint holds exclusive lock for too long (on production database)

table grew. However -- I'm not 100% sure of this, but it seems to have > jumped recently (from 3-4 minutes to 7 minutes). > * > http://www.postgresql.org/message-id/20030323112241.w14634-100...@megazone23.bigpanda.com Probably the table just got larger than RAM. -- Josh Berkus Postgre

[PERFORM] Logic of lowering seq_page_cost for SSD?

What did you find? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- 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] Planner performance extremely affected by an hanging transaction (20-30 times)?

ght be right here. Any thoughts on a fix for this we could get into 9.2.5? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- 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] Planner performance extremely affected by an hanging transaction (20-30 times)?

llowed to check the clog until after > you verify the transaction is no longer in progress, otherwise you open up > race conditions. In this particular case, I'd argue that we don't care about race conditions -- it's a plan estimate. We certainly care about them a lot less than l

Re: [PERFORM] Why is n_distinct always -1 for range types?

On 09/19/2013 01:47 PM, Josh Berkus wrote: > Test: > > 1. create a table with a range type column. > 2. insert 1000 identical values into that column. > 3. analyze > 4. n-distinct will still be listed as -1 (unique) for the column. > > Why? > Anyone? -- Josh Be

Re: [PERFORM] Planner performance extremely affected by an hanging transaction (20-30 times)?

ystem-wide contention, because it takes the > ProcArrayLock, once per row which needs to be checked. So you have 20 > processes all fighting over the ProcArrayLock, each doing so 1000 times per > query. Why do we need a procarraylock for this? Seems like the solution would be not to take

[PERFORM] Why is n_distinct always -1 for range types?

Test: 1. create a table with a range type column. 2. insert 1000 identical values into that column. 3. analyze 4. n-distinct will still be listed as -1 (unique) for the column. Why? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql

Re: [PERFORM] Performance bug in prepared statement binding in 9.2?

f the index" optimization? It's the story of our lives: we can't optimize anything without deoptimizing something else. Dammit. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To

Re: [PERFORM] Performance bug in prepared statement binding in 9.2?

sure about. Does that mean that 8.4 was unsafe, or that this is something which *could* be fixed in later versions? I'm also confused as to why this would affect BIND time rather than EXECUTE time. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mail

Re: [PERFORM] Need some basic information

> We are using Hibernate in Java to interact with Postgres 9.1. > > Can you please suggest some test cases or some issues which may hamper us? Port your application and run your smoke tests? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performanc

Re: [PERFORM] ORDER BY, LIMIT and indexes

for page 10,000 is a bot screen-scraping your site, anyway. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- 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 bug in prepared statement binding in 9.2?

repeatedly. Per earlier on this thread, that can bloat to 200X time required for a BIND, and it's definitely PostgreSQL-side. I'm trying to produce a test case which doesn't involve the user's application. However, hints on other things to analyze would be keen. -- Josh Berkus Po

Re: [PERFORM] Re: bgwriter autotuning might be unnecessarily penalizing bursty workloads

| 0.00 bgwriter_halt_potential | 0.00 buffer_allocation_ratio | 0.288 And your query, with some rounding added: -[ RECORD 1 ]---+-- alloc_mbps | 0.116 checkpoint_mbps | 0.340 clean_mbps | 0.000 backend_mbps| 0.056 write_mbps | 0.396 -- Josh Berkus PostgreSQL Experts

  1   2   3   4   5   6   7   8   9   10   >