Re: [PERFORM] Query Performance SQL Server vs. Postgresql

Hello, there should be a problem in a statistic, they are out of reality. Please, try to use a DISTINCT OF operator now - maybe a statistic will be better. Next - try to increase a work_mem. Hash join is untypically slow in your comp. Regards Pavel Stehule 2010/11/17 Humair Mohammed : > Yes st

Re: [PERFORM] Anyone seen this kind of lock pileup?

Josh Berkus writes: > Ok, I've collected more data. Looks like the case I was examining was > idiosyncratic; most of these lock pile-ups involve 400 or more locks > waiting held by around 20 different backends. Given this, taking 3 > seconds to sort that all out doesn't seem that unreasonable.

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

Eric Comeau wrote: Ideally 1 large file, but it may have to be multiple. We find that if we send multiple files it just causes the disk to thrash more so we get better throughput by sending one large file. If it's really one disk, sure. The problem you're facing is that your typical drive co

Re: [PERFORM] Anyone seen this kind of lock pileup?

>> What's odd about this is that the resulting "lock pileup" takes a >> mysterious 2-3.5 seconds to clear, despite the fact that none of the >> connections are *doing* anything during that time, nor are there >> deadlock errors. In theory at least, the locks should clear out in >> reverse ord

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

On 11/17/10 22:11, Eric Comeau wrote: *) what kind of data do you expect to be writing out at this speed? Large Video files ... our s/w is used to displace FTP. *) how many transactions per second will you expect to have? Ideally 1 large file, but it may have to be multiple. We find that i

Re: [PERFORM] Query Performance SQL Server vs. Postgresql

Humair Mohammed writes: > Yes strange indeed, I did rerun ANALYZE and VACCUM. Took 70 seconds to rerun > the query. Results from EXPLAIN ANALYZE below: > "Hash Join (cost=16212.30..52586.43 rows=92869 width=17) (actual > time=43200.223..49502.874 rows=3163 loops=1)"" Hash Cond: (((pb.id)::text

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 > sure I was pulling in the

Re: [PERFORM] Query Performance SQL Server vs. Postgresql

There are no indexes on the tables either in SQL Server or Postgresql - I am comparing apples to apples here. I ran ANALYZE on the postgresql tables, after that query performance times are still high 42 seconds with COALESCE and 35 seconds with IS DISTINCT FROM. Here is the execution plan from

Re: [PERFORM] Query Performance SQL Server vs. Postgresql

Yes strange indeed, I did rerun ANALYZE and VACCUM. Took 70 seconds to rerun the query. Results from EXPLAIN ANALYZE below: "Hash Join (cost=16212.30..52586.43 rows=92869 width=17) (actual time=43200.223..49502.874 rows=3163 loops=1)"" Hash Cond: (((pb.id)::text = (pg.id)::text) AND ((pb.ques

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

On 10-11-17 12:28 PM, Merlin Moncure wrote: On Wed, Nov 17, 2010 at 9: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

Re: [PERFORM] Anyone seen this kind of lock pileup?

On 11/17/10 18:37, Josh Berkus wrote: All, Having an interesting issue on one 8.4 database. Due to poor application design, the application is requesting 8-15 exclusive (update) locks on the same row on parallel connections pretty much simultaneously (i.e. < 50ms apart). What's odd about this i

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

Jon Nelson wrote: One thing to note is that where on a disk things sit can make a /huge/ difference - depending on if Ubuntu is /here/ and RHEL is /there/ and so on can make a factor of 2 or more difference. The outside tracks of most modern SATA disks can do around 120MB/s. The inside tracks ar

Re: [PERFORM] Query Performance SQL Server vs. Postgresql

I thought that I've seen an announcement about the SQL Server for Linux on 04/01/2005? I cannot find the link right now, but I am quite certain that there was such an announcement. From: pgsql-performance-ow...@postgresql.org To: Tomas Vondra Cc: pgsql-perform

Re: [PERFORM] Anyone seen this kind of lock pileup?

> Hmm ... can you extract a test case? Or at least strace the backends > involved? No, and no. Strace was the first thing I thought of, but I'd have to somehow catch one of these backends in the 3 seconds it's locked. Not really feasible. It might be possible to construct a test case, dependi

Re: [PERFORM] Anyone seen this kind of lock pileup?

Josh Berkus writes: > Having an interesting issue on one 8.4 database. Due to poor > application design, the application is requesting 8-15 exclusive > (update) locks on the same row on parallel connections pretty much > simultaneously (i.e. < 50ms apart). > What's odd about this is that the

Re: [PERFORM] Query Performance SQL Server vs. Postgresql

I have to concur. Sql is written specifially and only for Windows. It is optimized for windows. Postgreal is writeen for just about everything trying to use common code so there isn't much optimization because it has to be optimized based on the OS that is running it. Check out your config and s

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

On Wed, Nov 17, 2010 at 3: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 sure I was > pulling in t

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

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 sure I was pulling in the appropriate kernel headers. I wasn't aware of exactly how

Re: [PERFORM] Query Performance SQL Server vs. Postgresql

Dne 17.11.2010 05:47, Pavel Stehule napsal(a): > 2010/11/17 Humair Mohammed : >> >> There are no indexes on the tables either in SQL Server or Postgresql - I am >> comparing apples to apples here. I ran ANALYZE on the postgresql tables, Actually no, you're not comparing apples to apples. You've pr

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.44GB/sec write, minus overhead. > > Can get c

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

[PERFORM] Anyone seen this kind of lock pileup?

All, Having an interesting issue on one 8.4 database. Due to poor application design, the application is requesting 8-15 exclusive (update) locks on the same row on parallel connections pretty much simultaneously (i.e. < 50ms apart). What's odd about this is that the resulting "lock pileup"

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

On Wed, Nov 17, 2010 at 9: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 PostgreSQL as our database) > but we need to

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

On 11/17/10 15:26, 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 PostgreSQL as our database) but we need to build a test server th

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

On Wed, 17 Nov 2010 09:26:56 -0500 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 PostgreSQL as our > database) but we ne

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

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 PostgreSQL as our database) but we need to build a test serv

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

On Wednesday 17 November 2010 15:26:56 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 PostgreSQL as our > database) but we ne

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

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 PostgreSQL as our database) but we need to build a test server that can handle a sustained write thro