Re: [PERFORM] Configuration Recommendations

On 4/25/12 2:29 PM, "Shaun Thomas" wrote: >On 04/25/2012 02:46 AM, John Lister wrote: > >> Hi, I'd be grateful if you could share any XFS performance tweaks as I'm >> not entirely sure I'm getting the most out of my setup and any >> additional guidance would be very helpful. > >Ok, I'll give thi

Re: [PERFORM] Configuration Recommendations

On 5/3/12 8:46 AM, "Craig James" wrote: >On Thu, May 3, 2012 at 6:42 AM, Jan Nielsen >wrote: >> Hi John, >> >> On Thu, May 3, 2012 at 12:54 AM, John Lister >> >> wrote: >>> >>> On 03/05/2012 03:10, Jan Nielsen wrote: >>> >>> >>> 300GB RAID10 2x15k drive for OS on local storage >>> */dev/sda1 R

Re: [PERFORM] The shared buffers challenge

So how far do you go? 128MB? 32MB? 4MB? Anecdotal and an assumption, but I'm pretty confident that on any server with at least 1GB of dedicated RAM, setting it any lower than 200MB is not even going to help latency (assuming checkpoint and log configuration is in the realm of sane, and connecti

Re: [PERFORM] hash semi join caused by "IN (select ...)"

On 5/17/11 12:38 AM, "Clemens Eisserer" wrote: >Hi, > >>> select from t1 left join t2 WHERE id IN (select ) >> >> Does it work as expected with one less join? If so, try increasing >> join_collapse_limit ... > >That did the trick - thanks a lot. I only had to increase >join_colla

Re: [PERFORM] Linux: more cores = less concurrency.

On 4/14/11 1:19 PM, "Claudio Freire" wrote: >On Thu, Apr 14, 2011 at 10:05 PM, Scott Carey >wrote: >> Huge Pages helps caches. >> Dual-Pivot quicksort is more cache friendly and is _always_ equal to or >> faster than traditional quicksort (its a provably imp

Re: [PERFORM] Linux: more cores = less concurrency.

On 4/13/11 9:23 PM, "Greg Smith" wrote: >Scott Carey wrote: >> If postgres is memory bandwidth constrained, what can be done to reduce >> its bandwidth use? >> >> Huge Pages could help some, by reducing page table lookups and making >> overall access

Re: [PERFORM] HashJoin order, hash the large or small table? Postgres likes to hash the big one, why?

On 4/13/11 10:35 AM, "Tom Lane" wrote: >Scott Carey writes: >>> On Oct 27, 2010, at 12:56 PM, Tom Lane wrote: >>> Because a poorly distributed inner relation leads to long hash chains. >>> In the very worst case, all the keys are on the same hash chain

Re: [PERFORM] HashJoin order, hash the large or small table? Postgres likes to hash the big one, why?

New email-client nightmares! Fixed below. I think. - Sorry for resurrecting this thread, but this has been in my outbox for months and I think it is important: >On Oct 27, 2010, at 12:56 PM, Tom Lane wrote: > > >> Scott Carey writes: >>Why does hashjoin b

Re: [PERFORM] HashJoin order, hash the large or small table? Postgres likes to hash the big one, why?

Sorry for resurrecting this thread, but this has been in my outbox for months and I think it is important: On Oct 27, 2010, at 12:56 PM, Tom Lane wrote: > Scott Carey writes: > > Why does hashjoin behave poorly when the inner relation is not > > uniformly distributed and the oute

Re: [PERFORM] Linux: more cores = less concurrency.

If postgres is memory bandwidth constrained, what can be done to reduce its bandwidth use? Huge Pages could help some, by reducing page table lookups and making overall access more efficient. Compressed pages (speedy / lzo) in memory can help trade CPU cycles for memory usage for certain memory se

Re: [PERFORM] Intel SSDs that may not suck

On 4/6/11 10:48 PM, "Greg Smith" wrote: >Since they're bragging about it there, the safe bet is that the older R2 >unit had no such facility. > >I note that the Z-Drive R2 is basically some flash packed on top of an >LSI 1068e controller, mapped as a RAID0 volume. In Linux, you can expose it as

Re: [PERFORM] Intel SSDs that may not suck

On 4/5/11 7:07 AM, "Merlin Moncure" wrote: >On Mon, Apr 4, 2011 at 8:26 PM, Greg Smith wrote: >> >> If you really don't need more than 120GB of storage, but do care about >> random I/O speed, this is a pretty easy decision now--presuming the >>drive >> holds up to claims. As the claims are rea

Re: [PERFORM] Intel SSDs that may not suck

-0700 (PDT) >>From: pgsql-performance-ow...@postgresql.org (on behalf of Andy >>) >>Subject: Re: [PERFORM] Intel SSDs that may not suck >>To: Merlin Moncure ,Scott Carey >> >>Cc: "pgsql-performance@postgresql.org" >>,Greg Smith >> >>

Re: [PERFORM] Intel SSDs that may not suck

On 4/6/11 2:11 PM, "Andy" wrote: > >--- On Wed, 4/6/11, Scott Carey wrote: > > >> I could care less about the 'fast' sandforce drives. >> They fail at a high >> rate and the performance improvement is BECAUSE they are >> using a large,

Re: [PERFORM] Intel SSDs that may not suck

On 3/29/11 7:32 AM, "Jeff" wrote: > >On Mar 29, 2011, at 10:16 AM, Jeff wrote: > >> Now that all sounds awful and horrible until you get to overall >> performance, especially with reads - you are looking at 20k random >> reads per second with a few disks. Adding in writes does kick it >> down

Re: [PERFORM] Intel SSDs that may not suck

On 3/29/11 7:16 AM, "Jeff" wrote: > >The write degradation could probably be monitored looking at svctime >from sar. We may be implementing that in the near future to detect >when this creeps up again. For the X25-M's, overcommit. Do a secure erase, then only partition and use 85% or so of t

Re: [PERFORM] Intel SSDs that may not suck

I have generation 1 and 2 Intel MLC drives in production (~150+). Some have been around for 2 years. None have died. None have hit the write cycle limit. We do ~ 75GB of writes a day. The data and writes on these are not transactional (if one dies, we have copies). But the reliability has bee

Re: [PERFORM] Shouldn't we have a way to avoid "risky" plans?

On 3/23/11 2:08 PM, "Claudio Freire" wrote: >On Wed, Mar 23, 2011 at 6:00 PM, Tom Lane wrote: >> Claudio Freire writes: >>> In my head, safer = better worst-case performance. >> >> If the planner starts operating on the basis of worst case rather than >> expected-case performance, the complai

Re: [PERFORM] How to best use 32 15k.7 300GB drives?

On 1/28/11 9:28 AM, "Stephen Frost" wrote: >* Scott Marlowe (scott.marl...@gmail.com) wrote: >> There's nothing wrong with whole table updates as part of an import >> process, you just have to know to "clean up" after you're done, and >> regular vacuum can't fix this issue, only vacuum full or

Re: [PERFORM] How to best use 32 15k.7 300GB drives?

On 1/28/11 9:00 AM, "Scott Marlowe" wrote: >On Fri, Jan 28, 2011 at 9:39 AM, Robert Schnabel >wrote: >> I can't do outside the database. So yes, once the upload is done I run >> queries that update every row for certain columns, not every column. >>After >> I'm done with a table I run a VACUU

Re: [PERFORM] How to best use 32 15k.7 300GB drives?

On 1/27/11 4:11 PM, "Alan Hodgson" mailto:ahodg...@simkin.ca>> wrote: On January 27, 2011, Robert Schnabel mailto:schnab...@missouri.edu>> wrote: > So my questions are 1) am I'm crazy for doing this, 2) would you change > anything and 3) is it acceptable to put the xlog & wal (and perhaps t

Re: [PERFORM] Fun little performance IMPROVEMENT...

On 1/21/11 12:23 PM, "gr...@amadensor.com" wrote: >> gr...@amadensor.com writes: >>> Here is the fun part. When running 8 threads spinning calculating >>> square >>> roots (using the stress package), the full scan returned consistently >>> 60% >>> faster than the machine with no load. >> >> P

Re: [PERFORM] "SELECT .. WHERE NOT IN" query running for hours

On 1/10/11 12:37 PM, "Kevin Grittner" wrote: >Scott Carey wrote: > >> Often, the best query plans result from 'LEFT JOIN WHERE right >> side is NULL' rather than NOT EXISTS however. I often get >> performance gains by switching NOT EXISTS queri

Re: [PERFORM] "SELECT .. WHERE NOT IN" query running for hours

On 1/7/11 1:29 AM, "??? " wrote: > >So my follow-up question on the subject is this: > >Are there any particular semantics for the "NOT IN" statement that cause >the correlated query to execute for every row of the outter query, as >opposed to the "NOT EXISTS" ? => select * from

Re: [PERFORM] Wrong docs on wal_buffers?

On Jan 6, 2011, at 10:58 AM, Josh Berkus wrote: > >> But I wonder if initdb.c, when selecting the default shared_buffers, >> shouldn't test with wal_buffers = shared_buffers/64 or >> shared_buffers/128, with a lower limit of 8 blocks, and set that as >> the default. > > We talked about bumping

Re: [PERFORM] Question: BlockSize > 8192 with FusionIO

On Jan 4, 2011, at 8:48 AM, Merlin Moncure wrote: > On Mon, Jan 3, 2011 at 9:13 PM, Greg Smith wrote: >> Strange, John W wrote: >>> >>> Has anyone had a chance to recompile and try larger a larger blocksize >>> than 8192 with pSQL 8.4.x? >> >> While I haven't done the actual experiment you're

Re: [PERFORM] CPUs for new databases

On Nov 26, 2010, at 2:30 PM, Greg Smith wrote: > > In addition to the memory issues, there's also thread CPU scheduling > involved here. Ideally the benchmark would pin each thread to a single > core and keep it there for the runtime of the test, but it's not there > yet. I suspect one sour

Re: [PERFORM] Defaulting wal_sync_method to fdatasync on Linux for 9.1?

On Nov 17, 2010, at 1:24 PM, Greg Smith wrote: > Scott Carey wrote: >> Did you recompile your test on the RHEL6 system? > > On both systems I showed, I checked out a fresh copy of the PostgreSQL > 9.1 HEAD from the git repo, and compiled that on the server, to make >

Re: [PERFORM] Defaulting wal_sync_method to fdatasync on Linux for 9.1?

On Nov 16, 2010, at 12:39 PM, Greg Smith wrote: > > $ ./test_fsync > Loops = 1 > > Simple write: >8k write 88476.784/second > > Compare file sync methods using one write: >(unavailable: open_datasync) >open_sync 8k write 1192.135/second >8k w

Re: [PERFORM] Defaulting wal_sync_method to fdatasync on Linux for 9.1?

On Nov 16, 2010, at 4:05 PM, Mladen Gogala wrote: > Josh Berkus wrote: >> On 11/16/10 12:39 PM, Greg Smith wrote: >> >>> I want to next go through and replicate some of the actual database >>> level tests before giving a full opinion on whether this data proves >>> it's worth changing the wal_sy

Re: [PERFORM] How to achieve sustained disk performance of 1.25 GB write for 5 mins

On Nov 17, 2010, at 10:48 AM, Scott Carey wrote: >> >> Off hand, I would suggest: >> >> 8x http://www.kingston.com/ssd/vplus100.asp (180MB/sec sustained write) >> stripped (RAID 0, you did say that you don't care about safety). That >> should be 1.44G

Re: [PERFORM] How to achieve sustained disk performance of 1.25 GB write for 5 mins

On Nov 17, 2010, at 7:28 AM, Digimer wrote: > On 11/17/2010 09:26 AM, Eric Comeau wrote: >> This is not directly a PostgreSQL performance question but I'm hoping >> some of the chaps that build high IO PostgreSQL servers on here can help. >> >> We build file transfer acceleration s/w (and use Po

Re: [PERFORM] MVCC performance issue

On Nov 12, 2010, at 9:13 AM, Kyriacos Kyriacou wrote: > My suggestion had just a single difference from what currently MVCC is > doing (btw I never said that MVCC is bad). > > NOW ===> on COMMIT previous version record is expired and the > new version record (created in new dynamically

Re: [PERFORM] MVCC performance issue

HOT also usually requires setting FILLFACTOR to something other than the default for your table, so that there is guaranteed room in the page to modify data without allocating a new page. If you have fillfactor=75, then basically this proposal is already done -- each page has 25% temp space for

Re: [PERFORM] Defaulting wal_sync_method to fdatasync on Linux for 9.1?

On Nov 7, 2010, at 6:35 PM, Marti Raudsepp wrote: > On Mon, Nov 8, 2010 at 01:35, Greg Smith wrote: >> Yes; it's supposed to, and that logic works fine on some other platforms. > > No, the logic was broken to begin with. Linux technically supported > O_DSYNC all along. PostgreSQL used fdatasync

Re: [PERFORM] Major Linux performance regression; shouldn't we be worried about RHEL6?

On Nov 5, 2010, at 1:19 PM, Josh Berkus wrote: > >> The serious problems with this appear to be (a) that Linux/Ext4 PG >> performance still hasn't fully recovered, and, (b) that RHEL6 is set to >> ship with kernel 2.6.32, which means that we'll have a whole generation >> of RHEL which is off-lim

Re: [PERFORM] HashJoin order, hash the large or small table? Postgres likes to hash the big one, why?

On Oct 26, 2010, at 8:48 PM, Tom Lane wrote: > Robert Haas writes: >> I'm also a bit suspicious of the fact that the hash condition has a >> cast to text on both sides, which implies, to me anyway, that the >> underlying data types are not text. That might mean that the query >> planner doesn't

Re: [PERFORM] Postgres insert performance and storage requirement compared to Oracle

On Oct 26, 2010, at 2:54 PM, Mladen Gogala wrote: > On 10/26/2010 5:27 PM, Jon Nelson wrote: >> start loop: >> populate rows in temporary table >> insert from temporary table into permanent table >> truncate temporary table >> loop >> >> I do something similar, where I COPY data to a tem

Re: [PERFORM] BBU Cache vs. spindles

On Oct 22, 2010, at 1:06 PM, Rob Wultsch wrote: > On Fri, Oct 22, 2010 at 12:05 PM, Kevin Grittner > wrote: >> Rob Wultsch wrote: >> >>> I would think full_page_writes=off + double write buffer should be >>> far superior, particularly given that the WAL is shipped over the >>> network to slave

Re: [PERFORM] Slow count(*) again...

On Oct 21, 2010, at 11:13 AM, Jesper Krogh wrote: > On 2010-10-21 06:47, Scott Carey wrote: > > On a wimpy disk, I/O bound for > sure. But my disks go 1000MB/sec. > > > No query can go fast enough for them. The best I've gotten is > > > 800MB/sec, on a

Re: [PERFORM] Slow count(*) again...

On Oct 12, 2010, at 11:58 AM, Tom Lane wrote: > Jesper Krogh writes: >> On 2010-10-12 19:07, Tom Lane wrote: >>> Anyway, if anyone is hot to make COUNT(*) faster, that's where to look. > >> Just having 32 bytes bytes of "payload" would more or less double >> you time to count if I read you test

Re: [PERFORM] HashJoin order, hash the large or small table? Postgres likes to hash the big one, why?

On Oct 18, 2010, at 8:43 PM, Tom Lane wrote: > Scott Carey writes: >> I consistently see HashJoin plans that hash the large table, and scan >> the small table. > > Could we see a self-contained test case? And what cost parameters are > you using, especially work_mem?

[PERFORM] HashJoin order, hash the large or small table? Postgres likes to hash the big one, why?

8.4.5 I consistently see HashJoin plans that hash the large table, and scan the small table. This is especially puzzling in some cases where I have 30M rows in the big table and ~ 100 in the small... shouldn't it hash the small table and scan the big one? Here is one case I saw just recently

Re: [PERFORM] Slow count(*) again...

On Oct 12, 2010, at 9:46 AM, Scott Carey wrote: > > On Oct 12, 2010, at 8:54 AM, wrote: > >> On Tue, 12 Oct 2010, Craig Ringer wrote: >> >>> On 10/12/2010 04:22 PM, da...@lang.hm wrote: >>> >>>> from a PR point of view, speeding up the t

Re: [PERFORM] Slow count(*) again...

On Oct 12, 2010, at 8:54 AM, wrote: > On Tue, 12 Oct 2010, Craig Ringer wrote: > >> On 10/12/2010 04:22 PM, da...@lang.hm wrote: >> >>> from a PR point of view, speeding up the trivil count(*) case could be >>> worth it, just to avoid people complaining about it not being fast. >> >> At the c

Re: [PERFORM] Slow count(*) again...

On Oct 12, 2010, at 8:39 AM, Dan Harris wrote: > On 10/11/10 8:02 PM, Scott Carey wrote: >> would give you a 1MB read-ahead. Also, consider XFS and its built-in >> defragmentation. I have found that a longer lived postgres DB will get >> extreme >> file

Re: [PERFORM] Slow count(*) again...

>> > > A count with any joins or filter criteria would still have to scan all > pages with visible tuples in them. So the visibility map helps speed up > scanning of bloated tables, but doesn't provide a magical "fast count" > except in the utterly trivial "select count(*) from tablename;" cas

Re: [PERFORM] Slow count(*) again...

On Oct 11, 2010, at 9:21 PM, Samuel Gendler wrote: On Mon, Oct 11, 2010 at 9:06 PM, Scott Carey mailto:sc...@richrelevance.com>> wrote: I can't speak to documentation, but it is something that helps as your I/O subsystem gets more powerful, and how much it helps depends m

Re: [PERFORM] Slow count(*) again...

I can't speak to documentation, but it is something that helps as your I/O subsystem gets more powerful, and how much it helps depends more on your hardware, which may have adaptive read ahead on its own, and your file system which may be more or less efficient at sequential I/O. For example ex

Re: [PERFORM] Slow count(*) again...

On Oct 11, 2010, at 7:02 PM, Scott Carey wrote: > > On Oct 10, 2010, at 10:14 AM, Mladen Gogala wrote: > >> >> SQL> show parameter db_file_multi >> >> NAME

Re: [PERFORM] Slow count(*) again...

On Oct 10, 2010, at 10:14 AM, Mladen Gogala wrote: > > SQL> show parameter db_file_multi > > NAME TYPEVALUE > --- > -- > db_file_multiblock_read_countinteger 16 > SQL> a

Re: [PERFORM] Query much faster with enable_seqscan=0

On Sep 22, 2010, at 6:36 AM, Ogden wrote: > > On Sep 21, 2010, at 2:34 PM, Ogden wrote: > >> >> On Sep 21, 2010, at 2:16 PM, Greg Smith wrote: >> >>> Joshua D. Drake wrote: PostgreSQL's defaults are based on extremely small and some would say (non production) size databases. As a ma

Re: [PERFORM] GPU Accelerated Sorting

On a similar note, is Postgres' Quicksort a dual-pivot quicksort? This can be up to 2x as fast as a normal quicksort (25% fewer swap operations, and swap operations are more expensive than compares for most sorts). Just google 'dual pivot quicksort' for more info. And before anyone asks --

Re: [PERFORM] Performance on new 64bit server compared to my 32bit desktop

On Aug 27, 2010, at 10:25 AM, Greg Smith wrote: > Scott Carey wrote: >> But the select count(*) query, cached in RAM is 3x faster in one system than >> the other. The CPUs aren't 3x different performance wise. Something else >> may be wrong here. >> >>

Re: [PERFORM] Performance on new 64bit server compared to my 32bit desktop

On Aug 19, 2010, at 11:25 AM, Greg Smith wrote: > Philippe Rimbault wrote: >> I've run "time pgbench -c 50" : >>server x64 : >>starting vacuum...end. >>transaction type: TPC-B (sort of) >>scaling factor: 1 >>query mode: simple >>number of clients: 50 >>

Re: [PERFORM] Advice configuring ServeRAID 8k for performance

Don't ever have WAL and data on the same OS volume as ext3. If data=writeback, performance will be fine, data integrity will be ok for WAL, but data integrity will not be sufficient for the data partition. If data=ordered, performance will be very bad, but data integrity will be OK. This is beca

Re: [PERFORM] Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD

On Aug 11, 2010, at 9:30 PM, Greg Smith wrote: > Scott Carey wrote: >> What is the likelihood that your RAID card fails, or that the battery that >> reported 'good health' only lasts 5 minutes and you lose data before power >> is restored? What i

Re: [PERFORM] Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD

On Aug 10, 2010, at 11:38 AM, Karl Denninger wrote: Scott Marlowe wrote: On Tue, Aug 10, 2010 at 12:13 PM, Karl Denninger wrote: ANY disk that says "write is complete" when it really is not is entirely unsuitable for ANY real database use. It is simply a matter of

Re: [PERFORM] Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD

On Aug 10, 2010, at 11:28 AM, Greg Smith wrote: > Brad Nicholson wrote: >> What about putting indexes on them? If the drive fails and drops >> writes on those, they could be rebuilt - assuming your system can >> function without the index(es) temporarily. > > Dumping indexes on SSD is one of

Re: [PERFORM] Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD

On Aug 10, 2010, at 9:21 AM, Greg Smith wrote: > Scott Carey wrote: >> Also, the amount of data at risk in a power loss varies between >> drives. For Intel's drives, its a small chunk of data ( < 256K). For >> some other drives, the cache can be over 30MB of ou

Re: [PERFORM] Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD

On Aug 7, 2010, at 11:49 PM, Michael March wrote: SSD's actually vary quite a bit with typical postgres benchmark workloads. You mean various SSDs from different vendors? Or are you saying the same SSD model might vary in performance from drive to drive? Model to model (more specifically, con

Re: [PERFORM] Advice configuring ServeRAID 8k for performance

On Aug 5, 2010, at 4:09 PM, Scott Marlowe wrote: > On Thu, Aug 5, 2010 at 4:27 PM, Pierre C wrote: >> >>> 1) Should I switch to RAID 10 for performance? I see things like "RAID 5 >>> is bad for a DB" and "RAID 5 is slow with <= 6 drives" but I see little on >>> RAID 6. >> >> As others said, R

Re: [PERFORM] Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD

SSD's actually vary quite a bit with typical postgres benchmark workloads. Many of them also do not guarantee data that has been sync'd will not be lost if power fails (most hard drives with a sane OS and file system do). On Aug 7, 2010, at 4:47 PM, Michael March wrote: If anyone is intereste

Re: [PERFORM] Testing Sandforce SSD

On Aug 3, 2010, at 9:27 AM, Merlin Moncure wrote: > > 2) I've heard that some SSD have utilities that you can use to query > the write cycles in order to estimate lifespan. Does this one, and is > it possible to publish the output (an approximation of the amount of > work along with this would b

Re: [PERFORM] Testing Sandforce SSD

On Aug 2, 2010, at 7:26 AM, Merlin Moncure wrote: > On Fri, Jul 30, 2010 at 11:01 AM, Yeb Havinga wrote: >> After a week testing I think I can answer the question above: does it work >> like it's supposed to under PostgreSQL? >> >> YES >> >> The drive I have tested is the $435,- 50GB OCZ Verte

Re: [PERFORM] Testing Sandforce SSD

On Jul 26, 2010, at 12:45 PM, Greg Smith wrote: > Yeb Havinga wrote: >> I did some ext3,ext4,xfs,jfs and also ext2 tests on the just-in-memory >> read/write test. (scale 300) No real winners or losers, though ext2 >> isn't really faster and the manual need for fix (y) during boot makes >> it i

Re: [PERFORM] Pooling in Core WAS: Need help in performance tuning.

On Jul 22, 2010, at 11:36 AM, Robert Haas wrote: > On Mon, Jul 12, 2010 at 6:58 AM, Craig Ringer > wrote: >> So rather than asking "should core have a connection pool" perhaps >> what's needed is to ask "what can an in-core pool do that an external >> pool cannot do?" > > Avoid sending every co

Re: [PERFORM] IDE x SAS RAID 0 on HP DL 380 G5 P400i controller performance problem

If you are using ext3, your performance on the RAID card may also improve if the postgres xlog is not on the same partition as the data. Otherwise, for every transaction commit, all of the data on the whole partition will have to be sync()'d not just the xlog. Also, what is the performance dif

Re: [PERFORM] Pooling in Core WAS: Need help in performance tuning.

On Jul 9, 2010, at 8:33 PM, Craig Ringer wrote: > On 10/07/2010 9:25 AM, Josh Berkus wrote: >> >>> It *is* the last place you want to put it, but putting it there can >>> be much better than not putting it *anywhere*, which is what we've >>> often seen. >> >> Well, what you proposed is an admis

Re: [PERFORM] performance on new linux box

On Jul 15, 2010, at 6:22 PM, Scott Marlowe wrote: > On Thu, Jul 15, 2010 at 10:30 AM, Scott Carey wrote: >> >> On Jul 14, 2010, at 7:50 PM, Ben Chobot wrote: >> >>> On Jul 14, 2010, at 6:57 PM, Scott Carey wrote: >>> >>>> But none of this ex

Re: [PERFORM] performance on new linux box

On Jul 15, 2010, at 12:35 PM, Ben Chobot wrote: > On Jul 15, 2010, at 9:30 AM, Scott Carey wrote: > >>> Many raid controllers are smart enough to always turn off write caching on >>> the drives, and also disable the feature on their own buffer without a BBU. >>

Re: [PERFORM] performance on new linux box

On Jul 14, 2010, at 7:50 PM, Ben Chobot wrote: > On Jul 14, 2010, at 6:57 PM, Scott Carey wrote: > >> But none of this explains why a 4-disk raid 10 is slower than a 1 disk >> system. If there is no write-back caching on the RAID, it should still be >> simil

Re: [PERFORM] performance on new linux box

But none of this explains why a 4-disk raid 10 is slower than a 1 disk system. If there is no write-back caching on the RAID, it should still be similar to the one disk setup. Unless that one-disk setup turned off fsync() or was configured with synchronous_commit off. Even low end laptop driv

Re: [PERFORM] Write performance

On Jun 24, 2010, at 6:16 AM, Janning wrote: > On Thursday 24 June 2010 14:53:57 Matthew Wakeling wrote: >> On Thu, 24 Jun 2010, Janning wrote: >>> We have a 12 GB RAM machine with intel i7-975 and using >>> 3 disks "Seagate Barracuda 7200.11, ST31500341AS (1.5 GB)" >> >> Those discs are 1.5TB, n

[PERFORM] ALTER Table and CLUSTER does adding a new column rewrite clustered? (8.4.3)

v. 8.4.3 I have a table that has several indexes, one of which the table is clustered on. If I do an ALTER TABLE Foo ADD COLUMN bar integer not null default -1; It re-writes the whole table. * Does it adhere to the CLUSTER property of the table and write the new version clustered? * Does it

Re: [PERFORM] raid10 write performance

On Jun 22, 2010, at 7:29 AM, Karl Denninger wrote: > Justin Graf wrote: >> >> On 6/22/2010 4:31 AM, Grzegorz Jaśkiewicz wrote: >> >>> Would moving WAL dir to separate disk help potentially ? >>> >>> >> >> Yes it can have a big impact. > WAL on a separate spindle will make a HUGE dif

Re: [PERFORM] requested shared memory size overflows size_t

On Jun 16, 2010, at 1:53 PM, Alvaro Herrera wrote: > Excerpts from Tom Lane's message of lun jun 14 23:57:11 -0400 2010: >> Scott Carey writes: >>> Great points. There is one other option that is decent for the WAL: >>> If splitting out a volume is n

Re: [PERFORM] requested shared memory size overflows size_t

On Jun 14, 2010, at 7:06 PM, Greg Smith wrote: > I really cannot imagine taking a system as powerful as you're using here > and crippling it by running through a VM. You should be running Ubuntu > directly on the hardware, ext3 filesystem without LVM, split off RAID-1 > drive pairs dedicated

Re: [PERFORM] requested shared memory size overflows size_t

On Jun 14, 2010, at 11:53 AM, Tom Wilcox wrote: > > > max_connections=3 > effective_cache_size=15GB > maintenance_work_mem=5GB > shared_buffers=7000MB > work_mem=5GB > maintenance_work_mem doesn't need to be so high, it certainly has no effect on your queries below. It would affect vacuum,

Re: [PERFORM] planner costs in "warm cache" tests

It is still best to have random_page_cost to be slightly larger (~50%) than sequential_page_cost, because even when entirely in RAM, sequential reads are faster than random reads. Today's CPU's do memory prefetching on sequential access. Perhaps try something like 0.3 and 0.2, or half that. Y

Re: [PERFORM] prepared query performs much worse than regular query

On May 21, 2010, at 8:26 PM, Matthew Wakeling wrote: > On Fri, 21 May 2010, Richard Yen wrote: >> Any ideas why the query planner chooses a different query plan when using >> prepared statements? > > This is a FAQ. Preparing a statement makes Postgres create a plan, without > knowing the value

Re: [PERFORM] old server, new server, same performance

On May 14, 2010, at 3:52 PM, Scott Marlowe wrote: > 2010/5/14 Piotr Legiecki : >> So what is the problem? My simple 'benchmarks' I have done with pgAdmin in >> spare time. >> >> pgAdmin is the latest 1.8.2 on both D and E. >> Using pgAdmin on my (D) computer I have run SELECT * from some_table;

Re: [PERFORM] Replacing Cursors with Temporary Tables

On Apr 21, 2010, at 1:16 PM, Eliot Gable wrote: > I have previously discussed my very long PL/PGSQL stored procedure on this > list. However, without getting into too many details, I have another > performance-related question. > > The procedure currently uses cursors to return multiple result

Re: [PERFORM] Very high effective_cache_size == worse performance?

On Apr 20, 2010, at 12:22 PM, Scott Marlowe wrote: > On Tue, Apr 20, 2010 at 12:47 PM, David Kerr wrote: >> On Tue, Apr 20, 2010 at 02:15:19PM -0400, Robert Haas wrote: >> - On Tue, Apr 20, 2010 at 2:03 PM, David Kerr wrote: >> - > that thought occured to me while I was testing this. I ran a va

Re: [PERFORM] Re: HELP: How to tame the 8.3.x JDBC driver with a biq guery result set

On Apr 15, 2010, at 1:01 PM, Dave Crooke wrote: > On Thu, Apr 15, 2010 at 2:42 PM, Dave Crooke wrote: > Hey folks > > I am trying to do a full table scan on a large table from Java, using a > straightforward "select * from foo". I've run into these problems: > > 1. By default, the PG JDBC drive

Re: [PERFORM] stats collector suddenly causing lots of IO

> > I have had some 'idle in transaction' connections hanging out from time to > time that have caused issues on this machine that could explain the above > perma-bloat. That is one thing that could affect the case reported here as > well. The worst thing about those, is you can't even force

Re: [PERFORM] stats collector suddenly causing lots of IO

On Apr 16, 2010, at 9:48 AM, Tom Lane wrote: > Josh Kupershmidt writes: >> On Fri, Apr 16, 2010 at 11:41 AM, Tom Lane wrote: >>> Wow. Well, we have a smoking gun here: for some reason, autovacuum >>> isn't running, or isn't doing its job if it is. If it's not running >>> at all, that would ex

Re: [PERFORM] 3ware vs. MegaRAID

On Apr 7, 2010, at 11:13 PM, Greg Smith wrote: > Scott Carey wrote: >> * Change the linux 'readahead' block device parameter to at least 4MB (8192, >> see blockdev --setra) -- I don't know if there is a FreeBSD equivalent. >> > I haven't tested

Re: [PERFORM] 3ware vs. MegaRAID

On Apr 6, 2010, at 9:49 AM, Ireneusz Pluta wrote: > Greg Smith pisze: >> >> The MegaRAID SAS 84* cards have worked extremely well for me in terms >> of performance and features for all the systems I've seen them >> installed in. I'd consider it a modest upgrade from that 3ware card, >> speed

Re: [PERFORM] Database size growing over time and leads to performance impact

On Apr 1, 2010, at 1:42 PM, Tom Lane wrote: > Scott Carey writes: >> Still off topic: > >> Will CLUSTER/VF respect FILLFACTOR in 9.0? > >> As far as I can tell in 8.4, it does not. > > Works for me, in both branches. > I stand corrected. I must have do

Re: [PERFORM] Database size growing over time and leads to performance impact

On Mar 31, 2010, at 1:47 PM, Robert Haas wrote: > On Wed, Mar 31, 2010 at 4:37 PM, Scott Carey wrote: >> On Mar 27, 2010, at 6:35 AM, Andy Colson wrote: >>> >>> Dont "VACUUM FULL", its not helping you, and is being removed in newer >>> versions

Re: [PERFORM] Database size growing over time and leads to performance impact

On Mar 27, 2010, at 6:35 AM, Andy Colson wrote: > > Dont "VACUUM FULL", its not helping you, and is being removed in newer > versions. > Off topic: How is that going to work? CLUSTER doesn't work on tables without an index. I would love to be able to CLUSTER on some column set that doesn't

Re: [PERFORM] why does swap not recover?

On Mar 26, 2010, at 4:57 PM, Richard Yen wrote: > Hi everyone, > > We've recently encountered some swapping issues on our CentOS 64GB Nehalem > machine, running postgres 8.4.2. Unfortunately, I was foolish enough to set > shared_buffers to 40GB. I was wondering if anyone would have any insigh

Re: [PERFORM] Block at a time ...

On Mar 22, 2010, at 4:46 PM, Craig James wrote: > On 3/22/10 11:47 AM, Scott Carey wrote: >> >> On Mar 17, 2010, at 9:41 AM, Craig James wrote: >> >>> On 3/17/10 2:52 AM, Greg Stark wrote: >>>> On Wed, Mar 17, 2010 at 7:32 AM, Pierre C wrote: &g

Re: [PERFORM] pg_dump far too slow

On Mar 21, 2010, at 8:50 AM, David Newall wrote: > Tom Lane wrote: >> I would bet that the reason for the slow throughput is that gzip >> is fruitlessly searching for compressible sequences. It won't find many. >> > > > Indeed, I didn't expect much reduction in size, but I also didn't expect

Re: [PERFORM] Block at a time ...

On Mar 17, 2010, at 9:41 AM, Craig James wrote: > On 3/17/10 2:52 AM, Greg Stark wrote: >> On Wed, Mar 17, 2010 at 7:32 AM, Pierre C wrote: I was thinking in something like that, except that the factor I'd use would be something like 50% or 100% of current size, capped at (say) 1 GB. >

Re: [PERFORM] shared_buffers advice

On Mar 11, 2010, at 12:39 AM, Greg Smith wrote: > > Giving all the buffers to the database doesn't work for many reasons: > -Need a bunch leftover for clients to use (i.e. work_mem) > -Won't be enough OS cache for non-buffer data the database expects > cached reads and writes will perform well o

Re: [PERFORM] 10K vs 15k rpm for analytics

On Mar 9, 2010, at 4:39 PM, Scott Carey wrote: > > On Mar 8, 2010, at 11:00 PM, Greg Smith wrote: > > * At least with CentOS 5.3 and thier xfs version (non-Redhat, CentOS extras) > sparse random writes could almost hang a file system. They were VERY slow. > I hav

Re: [PERFORM] 10K vs 15k rpm for analytics

On Mar 8, 2010, at 11:00 PM, Greg Smith wrote: > Scott Carey wrote: >> For high sequential throughput, nothing is as optimized as XFS on Linux yet. >> It has weaknesses elsewhere however. >> > > I'm curious what you feel those weaknesses are. The recent add

Re: [PERFORM] 10K vs 15k rpm for analytics

On Mar 2, 2010, at 2:10 PM, wrote: > On Tue, 2 Mar 2010, Scott Marlowe wrote: > >> On Tue, Mar 2, 2010 at 2:30 PM, Francisco Reyes >> wrote: >>> Scott Marlowe writes: >>> Then the real thing to compare is the speed of the drives for throughput not rpm. >>> >>> In a machine, simmil

  1   2   3   4   >