Re: [PERFORM] select operations that generate disk writes

On 07/06/2012 02:20 PM, Pavel Stehule wrote: Hello 2012/7/6 CSS : So my question is, what else can generate writes when doing read-only operations? I know it sounds like a simple question, but I'm just not finding a concise answer anywhere. statistics http://www.postgresql.org/docs/9.1/int

Re: [PERFORM] select operations that generate disk writes

Hello 2012/7/6 CSS : > Hello, > > Time for a broad question. I'm aware of some specific select queries that > will generate disk writes - for example, a sort operation when there's not > enough work_mem can cause PG to write out some temp tables (not the correct > terminology?). That scenario

[PERFORM] select operations that generate disk writes

Hello, Time for a broad question. I'm aware of some specific select queries that will generate disk writes - for example, a sort operation when there's not enough work_mem can cause PG to write out some temp tables (not the correct terminology?). That scenario is easily remedied by enabling "

Re: [PERFORM] The need for clustered indexes to boost TPC-V performance

Hi Craig, I used the tool at depesz.com extensively during our early prototyping. It helped uncover ~10 problems that we solved by fixing issues in the code, adding or changing indexes, etc. Right now, I believe all our query plans look like what I would expect. Yes, you are right, I did miss t

Re: [PERFORM] SSDs again, LSI Warpdrive 2 anyone?

On 06/07/12 12:51, Mark Kirkwood wrote: A vendor has recommended the above drive to us - anyone have experience with it or its predecessor Warpdrive? http://www.storagereview.com/lsi_warpdrive_2_lp_display_idf_2011 http://www.storagereview.com/lsi_warpdrive_slp300_review The specs look quite g

Re: [PERFORM] Introducing the TPC-V benchmark, and its relationship to PostgreSQL

Yes, I hear you. TPC's usual mode of operation has been to release details after the benchmark is complete. But TPC does have a policy clause that allows publication of draft specifications to get public feedback before the benchmark is complete. Our 2012 TPC TC paper will have a lot of the high

[PERFORM] The overall experience of TPC-V benchmark team with PostgreSQL

Hello PGSQL fans, Looking back at my posts the past couple of days and the replies that I've got, I realized that I have failed to make one point clear: we are very pleased with what we have seen from PostgreSQL so far. Let me explain. At this point of developing or porting a benchmark on a new

Re: [PERFORM] Introducing the TPC-V benchmark, and its relationship to PostgreSQL

On 07/06/2012 11:22 AM, Reza Taheri wrote: Hi Greg, Yes, a single-instance benchmark is a natural fall-out from the TPC-V kit. Our coding team (4 people working directly on the benchmark with another 3-4 folks helping in various consulting capacities) is tasked with creating a multi-VM benchma

Re: [PERFORM] The need for clustered indexes to boost TPC-V performance

Just to be clear, we have a number of people from different companies working on the kit. This is not a VMware project, it is a TPC project. But I hear you regarding coming in from the cold and asking for a major db engine feature. I know that I have caused a lot of rolling eyes. Believe me, I h

Re: [PERFORM] Introducing the TPC-V benchmark, and its relationship to PostgreSQL

Hi Greg, Yes, a single-instance benchmark is a natural fall-out from the TPC-V kit. Our coding team (4 people working directly on the benchmark with another 3-4 folks helping in various consulting capacities) is tasked with creating a multi-VM benchmark. The benchmark is still missing the critic

Re: [PERFORM] The need for clustered indexes to boost TPC-V performance

On 07/06/2012 09:00 AM, Reza Taheri wrote: Well, I keep failing to send an email with an attachment. Do I need a moderator's approval? Probably. If so, it's really annoying that mailman isn't telling you this via a "held for moderation" auto-reply. It should be. We stuck with PGSQL 8.4 sin

[PERFORM] What would effect planning time?

I spent a good chunk of today trying to chase down why a query on one box ran in 110ms and on another, smaller box it ran in 10ms. There was no other activity on either box. Both boxes are PG9.1.1 RHEL 6.2 x64. the faster box is a smallish VM. the other box is a big 40core/256GB box. The plans b

Re: [PERFORM] MemSQL the "world's fastest database"?

On 07/01/2012 01:00 AM, Scott Marlowe wrote: Considering I can build a pgsql 8.4 machine with 256G RAM and 64 Opteron cores and a handful of SSDs or HW RAID that can do REAL 7k to 8k RW TPS right now for well under $10k, 20k TPS on an in memory database isn't all that impressive. Again, their T

Re: [PERFORM] The need for clustered indexes to boost TPC-V performance

On 07/03/2012 07:13 PM, Reza Taheri wrote: Is the PGSQL community willing to invest in a feature that a) has been requested by many others already; and b) can make a huge difference in a benchmark that can lend substantial credibility to PGSQL performance? Larger PostgreSQL features usually get

Re: [PERFORM] Introducing the TPC-V benchmark, and its relationship to PostgreSQL

On 07/03/2012 07:08 PM, Reza Taheri wrote: TPC-V is a new benchmark under development for virtualized databases. A TPC-V configuration has: - multiple virtual machines running a mix of DSS, OLTP, and business logic apps - VMs running with throughputs ranging from 10% to 40% of the total system

Re: [PERFORM] The need for clustered indexes to boost TPC-V performance

On 07/06/2012 08:41 AM, Andy Colson wrote: You're running on windows then? Server is 96Gig ram, 8 cores, (dell poweredge T610). with two powervault MD1120 NAS's? Thankfully they're running Pg on Linux (RHEL 6) . It seems that tests to date have been run against 8.4 which is pretty elderly,

Re: [PERFORM] The need for clustered indexes to boost TPC-V performance

Well, I keep failing to send an email with an attachment. Do I need a moderator's approval? Yes, running on VMs and a lower bin processor. With the virtualization overhead, etc., I figure we would be running right around 2/3 of the Dell throughput if we were running the same DBMS. I sent the f

Re: [PERFORM] The need for clustered indexes to boost TPC-V performance

On 07/06/2012 04:52 AM, Samuel Gendler wrote: On Thu, Jul 5, 2012 at 1:37 PM, Reza Taheri > wrote: I provided more config details in an earlier email. I hate to disagree, but unless I didn't get a message sent to the list It looks like that might be the ca

[PERFORM] SSDs again, LSI Warpdrive 2 anyone?

A vendor has recommended the above drive to us - anyone have experience with it or its predecessor Warpdrive? http://www.storagereview.com/lsi_warpdrive_2_lp_display_idf_2011 http://www.storagereview.com/lsi_warpdrive_slp300_review The specs look quite good, and the cards have capacitors on the

Re: [PERFORM] The need for clustered indexes to boost TPC-V performance

First: Please do try 9.2 beta if you're upgrading from 8.4. It'll be out as a final release soon enough, and index only scans may make a /big/ difference for the problem you're currently having. Looking at your configuration I have a few comments, but it's worth noting that I don't work with h

Re: [PERFORM] The need for clustered indexes to boost TPC-V performance

On Thu, Jul 5, 2012 at 1:37 PM, Reza Taheri wrote: > > I provided more config details in an earlier email. > > ** ** > > > I hate to disagree, but unless I didn't get a message sent to the list, you haven't provided any details about your postgresql config or otherwise adhered to the guidelin

Re: [PERFORM] The need for clustered indexes to boost TPC-V performance

Hi Samuel, The SSDs were used as a cache for the spinning drives. Here is a 30-second iostat sample representative of the whole run: avg-cpu: %user %nice %system %iowait %steal %idle 24.870.00 12.54 62.390.000.20 Device: rrqm/s wrqm/s r/s w/s

Re: [PERFORM] The need for clustered indexes to boost TPC-V performance

On Thu, Jul 5, 2012 at 12:13 PM, Reza Taheri wrote: > Hi Robert, > Yes, the same concept. Oracle's IOT feature is used often with TPC > benchmarks. > > Reza, it would be very helpful if you were to provide the list with a lot more information about your current software and hardware configuration

Re: [PERFORM] The need for clustered indexes to boost TPC-V performance

Hi Robert, Yes, the same concept. Oracle's IOT feature is used often with TPC benchmarks. Thanks, Reza > -Original Message- > From: Robert Klemme [mailto:shortcut...@googlemail.com] > Sent: Thursday, July 05, 2012 5:30 AM > To: Reza Taheri > Cc: pgsql-performance@postgresql.org; Andy Bond

Re: [PERFORM] The need for clustered indexes to boost TPC-V performance

Hi Daniel, Yes, it sounds like GIT will take us half the way there by getting rid of much of the index I/O if we cluster the tables. We can set the fillfactor parameter to keep tables sorted after updates. I am not sure what impact inserts will have since the primary key keeps growing with new i

Re: [PERFORM] The need for clustered indexes to boost TPC-V performance

On Wed, Jul 4, 2012 at 1:13 AM, Reza Taheri wrote: > Checking online, the subject of clustered indexes for PostgreSQL comes up > often. PGSQL does have a concept called “clustered table”, which means a > table has been organized in the order of an index. This would help with > sequential accesses