Re: [PERFORM] Performance

2011-04-26 Thread Claudio Freire
On Tue, Apr 26, 2011 at 7:30 AM, Robert Haas wrote: > On Apr 14, 2011, at 2:49 AM, Claudio Freire wrote: >> This particular factor is not about an abstract and opaque "Workload" >> the server can't know about. It's about cache hit rate, and the server >> can indeed measure that. > > The server ca

Re: [PERFORM] Time to put theory to the test?

2011-04-26 Thread J Sisson
On Mon, Apr 25, 2011 at 10:04 PM, Rob Wultsch wrote: > Tip from someone that manages thousands of MySQL servers: Use InnoDB > when using MySQL. Granted, my knowledge of PostgreSQL (and even MSSQL) far surpasses my knowledge of MySQL, but if InnoDB has such amazing benefits as being crash safe, an

Re: [PERFORM] Time to put theory to the test?

2011-04-26 Thread Kevin Grittner
J Sisson wrote: > Rob Wultsch wrote: >> Tip from someone that manages thousands of MySQL servers: Use >> InnoDB when using MySQL. > > Granted, my knowledge of PostgreSQL (and even MSSQL) far surpasses > my knowledge of MySQL, but if InnoDB has such amazing benefits as > being crash safe, and eve

Re: [PERFORM] Time to put theory to the test?

2011-04-26 Thread Scott Marlowe
On Tue, Apr 26, 2011 at 8:13 AM, J Sisson wrote: > On Mon, Apr 25, 2011 at 10:04 PM, Rob Wultsch wrote: >> Tip from someone that manages thousands of MySQL servers: Use InnoDB >> when using MySQL. > > Granted, my knowledge of PostgreSQL (and even MSSQL) far surpasses my > knowledge of MySQL, but

[PERFORM] tuning on ec2

2011-04-26 Thread Joel Reymont
I'm running pgsql on an m1.large EC2 instance with 7.5gb available memory. The free command shows 7gb of free+cached. My understand from the docs is that I should dedicate 1.75gb to shared_buffers (25%) and set effective_cache_size to 7gb. Is this correct? I'm running 64-bit Ubuntu 10.10, e.g

[PERFORM] optimizing a cpu-heavy query

2011-04-26 Thread Joel Reymont
Folks, I'm trying to optimize the following query that performs KL Divergence [1]. As you can see the distance function operates on vectors of 150 floats. The query takes 12 minutes to run on an idle (apart from pgsql) EC2 m1 large instance with 2 million documents in the docs table. The CPU i

Re: [PERFORM] Time to put theory to the test?

2011-04-26 Thread gnuoytr
Original message >Date: Tue, 26 Apr 2011 09:13:17 -0500 >From: pgsql-performance-ow...@postgresql.org (on behalf of J Sisson >) >Subject: Re: [PERFORM] Time to put theory to the test? >To: Rob Wultsch >Cc: "pgsql-performance@postgresql.org" > >On Mon, Apr 25, 2011 at 10:04 PM, Rob

Re: [PERFORM] Time to put theory to the test?

2011-04-26 Thread Tom Lane
J Sisson writes: > Granted, my knowledge of PostgreSQL (and even MSSQL) far surpasses my > knowledge of MySQL, but if InnoDB has such amazing benefits as being > crash safe, and even speed increases in some instances, why isn't > InnoDB default? It *is* default in the most recent versions (5.5 an

Re: [PERFORM] Time to put theory to the test?

2011-04-26 Thread Magnus Hagander
On Tue, Apr 26, 2011 at 17:51, Tom Lane wrote: > J Sisson writes: >> Granted, my knowledge of PostgreSQL (and even MSSQL) far surpasses my >> knowledge of MySQL, but if InnoDB has such amazing benefits as being >> crash safe, and even speed increases in some instances, why isn't >> InnoDB default

Re: [PERFORM] Time to put theory to the test?

2011-04-26 Thread Kenneth Marshall
On Tue, Apr 26, 2011 at 09:58:49AM -0500, Kevin Grittner wrote: > J Sisson wrote: > > Rob Wultsch wrote: > >> Tip from someone that manages thousands of MySQL servers: Use > >> InnoDB when using MySQL. > > > > Granted, my knowledge of PostgreSQL (and even MSSQL) far surpasses > > my knowledge of

Re: [PERFORM] Time to put theory to the test?

2011-04-26 Thread Josh Berkus
J, > Long story short, every single PostgreSQL machine survived the failure > with *zero* data corruption. I had a few issues with SQL Server > machines, and virtually every MySQL machine has required data cleanup > and table scans and tweaks to get it back to "production" status. Can I quote yo

Re: [PERFORM] Performance

2011-04-26 Thread Tomas Vondra
Dne 26.4.2011 07:35, Robert Haas napsal(a): > On Apr 13, 2011, at 6:19 PM, Tomas Vondra wrote: >> Yes, I've had some lectures on non-linear programming so I'm aware that >> this won't work if the cost function has multiple extremes (walleys / >> hills etc.) but I somehow suppose that's not the cas

[PERFORM] reducing random_page_cost from 4 to 2 to force index scan

2011-04-26 Thread Sok Ann Yap
Hi, I am using PostgreSQL 9.0. There is a salutations table with 44 rows, and a contacts table with more than a million rows. The contacts table has a nullable (only 0.002% null) salutation_id column, referencing salutations.id. With this query: SELECT salutations.id, salutations.name,

Re: [PERFORM] reducing random_page_cost from 4 to 2 to force index scan

2011-04-26 Thread Kevin Grittner
Sok Ann Yap wrote: > So, index scan wins by a very small margin over sequential scan > after the tuning. I am a bit puzzled because index scan is more > than 3000 times faster in this case, but the estimated costs are > about the same. Did I do something wrong? Tuning is generally needed to ge

Re: [PERFORM] reducing random_page_cost from 4 to 2 to force index scan

2011-04-26 Thread Merlin Moncure
On Tue, Apr 26, 2011 at 4:37 PM, Kevin Grittner wrote: > Sok Ann Yap wrote: > >> So, index scan wins by a very small margin over sequential scan >> after the tuning. I am a bit puzzled because index scan is more >> than 3000 times faster in this case, but the estimated costs are >> about the same