Re: [PERFORM] Two identical systems, radically different performance

2012-10-09 Thread Yeb Havinga
On 2012-10-08 23:45, Craig James wrote: This is driving me crazy. A new server, virtually identical to an old one, has 50% of the performance with pgbench. I've checked everything I can think of. The setups (call the servers old and new): old: 2 x 4-core Intel Xeon E5620 new: 4 x 4-core

Re: [PERFORM] Two identical systems, radically different performance

2012-10-09 Thread David Thomas
On Mon, Oct 08, 2012 at 04:40:31PM -0700, Craig James wrote: Nobody has commented on the hyperthreading question yet ... does it really matter? The old (fast) server has hyperthreading disabled, and the new (slower) server has hyperthreads enabled. If hyperthreading is definitely

Re: [PERFORM] Two identical systems, radically different performance

2012-10-09 Thread Craig James
On Tue, Oct 9, 2012 at 9:14 AM, David Thomas da...@digitaldogma.org wrote: On Mon, Oct 08, 2012 at 04:40:31PM -0700, Craig James wrote: Nobody has commented on the hyperthreading question yet ... does it really matter? The old (fast) server has hyperthreading disabled, and the new

Re: [PERFORM] Two identical systems, radically different performance

2012-10-09 Thread Craig James
On Tue, Oct 9, 2012 at 9:02 AM, Shaun Thomas stho...@optionshouse.comwrote: On 10/08/2012 06:40 PM, Craig James wrote: Nobody has commented on the hyperthreading question yet ... does it really matter? The old (fast) server has hyperthreading disabled, and the new (slower) server has

[PERFORM] Hyperthreading (was: Two identical systems, radically different performance)

2012-10-09 Thread Craig James
I've confirmed that hyperthreading causes a huge drop in performance on a 2x4-core Intel Xeon E5620 2.40GHz system. The bottom line is: ~3200 TPS max with hyperthreading ~9000 TPS max without hyprethreading Here are my results. Hyprethreads (Run1) is out of the box with hyperthreads

Re: [PERFORM] Hyperthreading (was: Two identical systems, radically different performance)

2012-10-09 Thread Tom Lane
Craig James cja...@emolecules.com writes: I've confirmed that hyperthreading causes a huge drop in performance on a 2x4-core Intel Xeon E5620 2.40GHz system. The bottom line is: Interesting. I'll be upgrading to 8.4.14 and making more changes to postgres.conf based on feedback. The server

Re: [PERFORM] Two identical systems, radically different performance

2012-10-09 Thread Gavin Flower
On 09/10/12 12:40, Craig James wrote: Nobody has commented on the hyperthreading question yet ... does it really matter? The old (fast) server has hyperthreading disabled, and the new (slower) server has hyperthreads enabled. If hyperthreading is definitely NOT an issue, it will save me a

[PERFORM] Ways to speed up ts_rank

2012-10-09 Thread Shane Hathaway
Hello, First let me say thanks for a fantastic database system. The hard work this community has put into Postgres really shows. A client of mine has been using Postgres quite effectively for a while now, but has recently hit a performance issue with text index queries, specifically when

Re: [PERFORM] Hyperthreading (was: Two identical systems, radically different performance)

2012-10-09 Thread Shaun Thomas
On 10/09/2012 03:12 PM, Craig James wrote: ~3200 TPS max with hyperthreading ~9000 TPS max without hyprethreading That's really odd. We got almost the opposite effect on our X5645's. Also, there's no way your RAID is sustaining 9000TPS. Something here sounds fishy. -- Shaun Thomas

Re: [PERFORM] Scaling 10 million records in PostgreSQL table

2012-10-09 Thread Samuel Gendler
On Mon, Oct 8, 2012 at 1:25 PM, Navaneethan R n...@gridlex.com wrote: After created the index for WHERE clause WHERE dealer_id = 270001..It is performing better.I have more dealer ids Should I do it for each dealer_id? All you've really done is confuse the issue. Please read the wiki

Re: [PERFORM] Scaling 10 million records in PostgreSQL table

2012-10-09 Thread Daniel Farina
On Mon, Oct 8, 2012 at 1:25 PM, Navaneethan R n...@gridlex.com wrote: On Tuesday, October 9, 2012 1:40:08 AM UTC+5:30, Steve Crawford wrote: On 10/08/2012 08:26 AM, Navaneethan R wrote: Hi all, I have 10 million records in my postgres table.I am running the database in amazon

[PERFORM] Why am I getting great/terrible estimates with these CTE queries?

2012-10-09 Thread Tomas Vondra
Hi, I've been fighting with some CTE queries recently, and in the end I've ended up with two basic cases. In one case the CTEs work absolutely great, making the estimates much more precise, while in the other the results are pretty terrible. And I'm not sure why both of these behave the way they

Re: [PERFORM] Why am I getting great/terrible estimates with these CTE queries?

2012-10-09 Thread Tom Lane
Tomas Vondra t...@fuzzy.cz writes: I've been fighting with some CTE queries recently, and in the end I've ended up with two basic cases. In one case the CTEs work absolutely great, making the estimates much more precise, while in the other the results are pretty terrible. And I'm not sure why

Re: [PERFORM] Hyperthreading (was: Two identical systems, radically different performance)

2012-10-09 Thread Craig James
For your amusement ... I upgraded from 8.4.4 to 9.2.1 results. Threw away the DB completely and did a new init. Same hardware, postgres.conf and Linux as before. ra is blockdev --getra (both PGDATA and XLOG disks) walb is postgres.conf wal_buffers ra:8192 walb:1M ra:256 walb:1M

Re: [PERFORM] Why am I getting great/terrible estimates with these CTE queries?

2012-10-09 Thread Tomas Vondra
On 10.10.2012 01:09, Tom Lane wrote: Tomas Vondra t...@fuzzy.cz writes: I've been fighting with some CTE queries recently, and in the end I've ended up with two basic cases. In one case the CTEs work absolutely great, making the estimates much more precise, while in the other the results are