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

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 trip to the co-lo facility. Thanks, Craig On

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

On Oct 9, 2012, at 3:24 AM, Tomas Vondra wrote: > On 9.10.2012 00:33, Evgeny Shishkin wrote: >>> >>> pgbench: Old server >>> >>>pgbench -i -s 100 -U test >>>pgbench -U test -c ... -t ... >>> >>>-c -t TPS >>> 5 2 3777 >>>10 1 2622 >>>20 5000 3759 >>

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

On 9.10.2012 00:33, Evgeny Shishkin wrote: >> >> pgbench: Old server >> >> pgbench -i -s 100 -U test >> pgbench -U test -c ... -t ... >> >> -c -t TPS >> 5 2 3777 >> 10 1 2622 >> 20 5000 3759 >> 30 5712 >> 40 2500 5953 >> 50 2000

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

On Mon, Oct 8, 2012 at 8:03 PM, Craig James wrote: >> > But again ... the two systems are identical. This can't explain it. >> >> Is the read-ahead the same in both systems? > > > Yes, as I said in the original reply (it got cut off from your reply): "Same > on both servers." Oh, yes. Google col

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

On 9.10.2012 01:03, Craig James wrote: > > > On Mon, Oct 8, 2012 at 3:50 PM, Claudio Freire > wrote: > > On Mon, Oct 8, 2012 at 7:48 PM, Craig James > wrote: > >> > # blockdev --getra /dev/sdb1 > >> > 256 > >> >

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

On 09/10/12 11:48, Craig James wrote: On Mon, Oct 8, 2012 at 3:44 PM, Claudio Freire wrote: On Mon, Oct 8, 2012 at 7:25 PM, Craig James wrote: But why? What have I overlooked? Do you have readahead properly set up on the new one? # blockdev --getra /dev/sdb1 256 It's probably this. 256

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

On Mon, Oct 8, 2012 at 3:50 PM, Claudio Freire wrote: > On Mon, Oct 8, 2012 at 7:48 PM, Craig James wrote: > >> > # blockdev --getra /dev/sdb1 > >> > 256 > >> > >> > >> It's probably this. 256 is way too low to saturate your I/O system. > >> Pump it up. I've found 8192 works nice for a system I

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

On Mon, Oct 8, 2012 at 7:48 PM, Craig James wrote: >> > # blockdev --getra /dev/sdb1 >> > 256 >> >> >> It's probably this. 256 is way too low to saturate your I/O system. >> Pump it up. I've found 8192 works nice for a system I have, 32000 I >> guess could work too. > > > But again ... the two sy

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

On Mon, Oct 8, 2012 at 3:44 PM, Claudio Freire wrote: > On Mon, Oct 8, 2012 at 7:25 PM, Craig James wrote: > >> > But why? What have I overlooked? > >> > >> Do you have readahead properly set up on the new one? > > > > > > # blockdev --getra /dev/sdb1 > > 256 > > > It's probably this. 256 is way

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

On Oct 9, 2012, at 2:44 AM, Claudio Freire wrote: > On Mon, Oct 8, 2012 at 7:25 PM, Craig James wrote: But why? What have I overlooked? >>> >>> Do you have readahead properly set up on the new one? >> >> >> # blockdev --getra /dev/sdb1 >> 256 > > > It's probably this. 256 is way too l

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

On Mon, Oct 8, 2012 at 7:25 PM, Craig James wrote: >> > But why? What have I overlooked? >> >> Do you have readahead properly set up on the new one? > > > # blockdev --getra /dev/sdb1 > 256 It's probably this. 256 is way too low to saturate your I/O system. Pump it up. I've found 8192 works nic

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

On Mon, Oct 8, 2012 at 7:06 PM, Craig James wrote: >> Sequential Input on the new one is 279MB/s, on the old 400MB/s. >> > > But why? What have I overlooked? Do you have readahead properly set up on the new one? -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To m

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

On Mon, Oct 8, 2012 at 1:27 PM, Craig Ringer wrote: > > If you already have appropriate indexes and have used `explain analyze` to > verify that the query isn't doing anything slow and expensive, it's > possible the easiest way to improve performance is to set up async > replication or log shippi

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

On Mon, Oct 8, 2012 at 3:33 PM, Evgeny Shishkin wrote: > > On Oct 9, 2012, at 1:45 AM, Craig James wrote: > > One dramatic difference I noted via vmstat. On the old server, the I/O > load during the bonnie++ run was steady, like this: > > procs ---memory-- ---swap-- -io -

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

On Oct 9, 2012, at 1:45 AM, 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

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

One mistake in my descriptions... On Mon, Oct 8, 2012 at 2:45 PM, 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

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

>old: 2 x 4-core Intel Xeon E5620 >new: 4 x 4-core Intel Xeon E5606 http://ark.intel.com/compare/47925,52583 old: Xeon E5620 : 4 cores ; 8 Threads ; *Clock Speed : 2.40 GHz ; Max Turbo Frequency: 2.66 GHz* new: Xeon E5606 :4 cores ; 4 Threads ; Clock Speed : 2.13 GHz ; Max Tur

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

On Mon, Oct 8, 2012 at 3:09 PM, Claudio Freire wrote: > On Mon, Oct 8, 2012 at 7:06 PM, Craig James wrote: > >> Sequential Input on the new one is 279MB/s, on the old 400MB/s. > >> > > > > But why? What have I overlooked? > > Do you have readahead properly set up on the new one? > # blockdev --

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

On 10/08/2012 02:45 PM, 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-c

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

On Oct 9, 2012, at 2:06 AM, Craig James wrote: > > > On Mon, Oct 8, 2012 at 2:57 PM, Evgeny Shishkin wrote: > > On Oct 9, 2012, at 1:45 AM, Craig James wrote: > >> I tested both the RAID10 data disk and the RAID1 xlog disk with bonnie++. >> The xlog disks were almost identical in perform

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

On Mon, Oct 8, 2012 at 2:57 PM, Evgeny Shishkin wrote: > > On Oct 9, 2012, at 1:45 AM, Craig James wrote: > > I tested both the RAID10 data disk and the RAID1 xlog disk with bonnie++. > The xlog disks were almost identical in performance. The RAID10 pg-data > disks looked like this: > > Old serv

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

On Oct 9, 2012, at 1:45 AM, 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

[PERFORM] Two identical systems, radically different performance

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 Intel Xeon E5606 both: memory: 12 GB DD

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

On 10/08/2012 11:26 PM, Navaneethan R wrote: Hi all, I have 10 million records in my postgres table.I am running the database in amazon ec2 medium instance. EC2 usually means "My I/O performance is terrible" and "medium instance" means "I don't have enough RAM for caching to make up f

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

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 ec2 medium instance. I need to access the last week data from the table. It takes huge time to process the simple query.So, i throws time out exception

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

On 2012-10-08 10:26, Navaneethan R wrote: Hi all, I have 10 million records in my postgres table.I am running the database in amazon ec2 medium instance. I need to access the last week data from the table. It takes huge time to process the simple query.So, i throws time out exception err

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

On 10/08/2012 17:26, Navaneethan R wrote: Hi all, Hello, I have 10 million records in my postgres table.I am running the database in amazon ec2 medium instance. I need to access the last week data from the table. It takes huge time to process the simple query.So, i throws time out ex

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

On Mon, Oct 8, 2012 at 10:26 AM, Navaneethan R wrote: > Hi all, > > I have 10 million records in my postgres table.I am running the > database in amazon ec2 medium instance. I need to access the last week data > from the table. > It takes huge time to process the simple query.So, i throws

[PERFORM] Scaling 10 million records in PostgreSQL table

Hi all, I have 10 million records in my postgres table.I am running the database in amazon ec2 medium instance. I need to access the last week data from the table. It takes huge time to process the simple query.So, i throws time out exception error. query is : select count(*) from d

Re: [PERFORM] Strange behavior after upgrade from 9.0 to 9.2

On 08.10.2012 17:56, Tom Lane wrote: > Andrzej Zawadzki writes: >> On 08.10.2012 16:52, Tom Lane wrote: >>> [ counts... ] You've got nine base relations in that query. I think >>> you need to increase from_collapse_limit and/or join_collapse_limit. >> Bingo! Thank you! >> But... looks like in 9.

Re: [PERFORM] Strange behavior after upgrade from 9.0 to 9.2

Andrzej Zawadzki writes: > On 08.10.2012 16:52, Tom Lane wrote: >> [ counts... ] You've got nine base relations in that query. I think >> you need to increase from_collapse_limit and/or join_collapse_limit. > Bingo! Thank you! > But... looks like in 9.0 this worked differently or option was sk

Re: [PERFORM] Strange behavior after upgrade from 9.0 to 9.2

On 08.10.2012 16:52, Tom Lane wrote: > Andrzej Zawadzki writes: >> I have no idea whats wrong. Looks like planer bad decision. > [ counts... ] You've got nine base relations in that query. I think > you need to increase from_collapse_limit and/or join_collapse_limit. > Bingo! Thank you! But... l

Re: [PERFORM] Strange behavior after upgrade from 9.0 to 9.2

Andrzej Zawadzki writes: > I have no idea whats wrong. Looks like planer bad decision. [ counts... ] You've got nine base relations in that query. I think you need to increase from_collapse_limit and/or join_collapse_limit. regards, tom lane -- Sent via pgsql-perform

Re: [PERFORM] [repost] Help me develop new commit_delay advice

On 6 September 2012 04:20, Greg Smith wrote: > On 08/02/2012 02:02 PM, Peter Geoghegan wrote: > I dug up what I wrote when trying to provide better advice for this circa > V8.3. That never really gelled into something worth publishing at the time. > But I see some similar patterns what what you'r

[PERFORM] UPDATE execution time is increasing

Hello! I would like to ask following question: I have created a table and I updated all records. And I executed this update command again and again Execution time was growing after each step. I cannot understand this behavior. First update command took 6 sec, 30th update (same) command took 3

Re: [PERFORM] UPDATE execution time is increasing

On Sun, Oct 7, 2012 at 3:49 PM, wrote: > Hello! > > I would like to ask following question: > I have created a table and I updated all records. > And I executed this update command again and again > Execution time was growing after each step. > I cannot understand this behavior. > First updat

Re: [PERFORM] Same query doing slow then quick

Sorry for the late answer, I was going through my e-mail backlog and noticed that this question hadn't been answered. On Thu, Sep 27, 2012 at 11:33 AM, Undertaker Rude wrote: > Oh ok. But what is this command doing ? i'm gonna runn it today. I'll keep > you posted. Here is some EXPLAIN ANALYZE fr

Re: [PERFORM] Guide to Posting Slow Query Questions

On Wed, Sep 26, 2012 at 11:11 PM, Jeff Janes wrote: > On Wed, Sep 12, 2012 at 11:40 PM, Ants Aasma wrote: >> I don't have any links for OS level monitoring, but with version 9.2 >> track_io_timing would do the job. > > I don't know how to advice people on how to use this to obtain > information o

Re: [PERFORM] Strange behavior after upgrade from 9.0 to 9.2

On 08.10.2012 12:15, Craig Ringer wrote: > On 10/08/2012 04:18 PM, Andrzej Zawadzki wrote: >> Hi! >> >> After upgrade (dump/restore/analyze) query (below) after some time is >> killed by kernel. > > What's `shared_buffers`? `work_mem`? shared_buffers = 64MB work_mem = 48MB effective_cache_size = 51

Re: [PERFORM] Strange behavior after upgrade from 9.0 to 9.2

On 10/08/2012 04:18 PM, Andrzej Zawadzki wrote: Hi! After upgrade (dump/restore/analyze) query (below) after some time is killed by kernel. What's `shared_buffers`? `work_mem`? https://wiki.postgresql.org/wiki/Server_Configuration https://wiki.postgresql.org/wiki/Slow_Query_Questions -- Cr

[PERFORM] Strange behavior after upgrade from 9.0 to 9.2

Hi! After upgrade (dump/restore/analyze) query (below) after some time is killed by kernel. Postgres creates a lot of tmp files: temporary file: path "base/pgsql_tmp/pgsql_tmp8949.2046", size 24576 temporary file: path "base/pgsql_tmp/pgsql_tmp8949.2045", size 24576 temporary file: path "base/pg

Re: [PERFORM] Same query doing slow then quick

Oh, thankx. I forgot to put the answer i got from another site. I was told to use box and point type and create an index on it and it works really well ! Rude - Last Territory Ou écouter ?http://www.deezer.com/fr/music/last-territory/the-last-hope-3617781 (Post-apocalyptic Metal)http://ww