Re: [PERFORM] sniff test on some PG 8.4 numbers

2013-03-05 Thread Jon Nelson
On Tue, Mar 5, 2013 at 1:35 PM, Jon Nelson wrote: > > pgbench -h BLAH -c 32 -M prepared -t 10 -S > I get 95,000 to 100,000 tps. > > pgbench -h BLAH -c 32 -M prepared -t 10 > seems to hover around 6,200 tps (size 100) to 13,700 (size 400) Some followup: The read test goes (up to) 133K tps,

Re: [PERFORM] sniff test on some PG 8.4 numbers

2013-03-05 Thread Jon Nelson
On Tue, Mar 5, 2013 at 7:02 PM, Josh Berkus wrote: > >> Do these basically sniff right? > > Well, the read test seems reasonable. I'm impressed by the speed of the > write test ... how large is the raid card cache? > > And why 8.4? Can you try 9.2? 8.4 because it's what I've got, basically. I m

Re: [PERFORM] sniff test on some PG 8.4 numbers

2013-03-05 Thread Josh Berkus
> Do these basically sniff right? Well, the read test seems reasonable. I'm impressed by the speed of the write test ... how large is the raid card cache? And why 8.4? Can you try 9.2? > (NOTE: with barriers off, I get a slight increase - 10% - in the > read-write test, and a larger *decrease

Re: [PERFORM] Optimize SELECT * from table WHERE foreign_key_id IN (key1,key2,key3,key4...)

2013-03-05 Thread Joshua D. Drake
On 03/05/2013 03:51 PM, Niels Kristian Schjødt wrote: 3ms isn't slow Sorry, it's 3323ms! Can I do anything to optimize that query or maybe the index or something? your index is already used Okay this leaves me with - "get better hardware" or? What does explain analyze say versus just

Re: [PERFORM] Optimize SELECT * from table WHERE foreign_key_id IN (key1,key2,key3,key4...)

2013-03-05 Thread Niels Kristian Schjødt
Hi, thanks for answering. See comments inline. Den 05/03/2013 kl. 15.26 skrev Julien Cigar : > On 03/05/2013 15:00, Niels Kristian Schjødt wrote: >> Hi, >> >> I'm running a rails app, where I have a model called Car that has_many >> Images. Now when I tell rails to include those images, when qu

Re: [PERFORM] New server setup

2013-03-05 Thread Jeff Janes
On Tue, Mar 5, 2013 at 10:27 AM, Niels Kristian Schjødt < nielskrist...@autouncle.com> wrote: > Okay, thanks - but hey - if I put it at session pooling, then it says in > the documentation: "default_pool_size: In session pooling it needs to be > the number of max clients you want to handle at any

Re: [PERFORM] Are bitmap index scans slow to start?

2013-03-05 Thread Jeff Janes
On Wed, Feb 27, 2013 at 1:38 PM, Carlo Stonebanks < stonec.regis...@sympatico.ca> wrote: > > > >> Is the original query you posted part of the transform process, rather > than being the production query you run after the ETL is over? > > > > Neither, it is part of our auditing and maintenance

[PERFORM] sniff test on some PG 8.4 numbers

2013-03-05 Thread Jon Nelson
I was hoping to just get a "gut reaction" on some pgbench numbers I have, to see if I'm in the ballpark. OS: ScientificLinux 6.3, x86_64 Hardware: 4x real disks (not SSD) behind an LSI 9260 in raid10, Xeon E5-2680 with hyperthreading OFF, 128GB of RAM. Setup: postgresql 8.4.13, ext4, barriers ON,

Re: [PERFORM] New server setup

2013-03-05 Thread Niels Kristian Schjødt
Okay, thanks - but hey - if I put it at session pooling, then it says in the documentation: "default_pool_size: In session pooling it needs to be the number of max clients you want to handle at any moment". So as I understand it, is it true that I then have to set default_pool_size to 300 if I h

Re: [PERFORM] New server setup

2013-03-05 Thread Benjamin Krajmalnik
Set it to use session. I had a similar issue having moved one of the components of our app to use transactions, which introduced an undesired behavior. -Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Niels Kr

Re: [PERFORM] Are bitmap index scans slow to start?

2013-03-05 Thread Jeff Janes
On Thu, Feb 28, 2013 at 12:13 PM, Carlo Stonebanks < stonec.regis...@sympatico.ca> wrote: > < appropriate, that should make the queries run much faster, as elated > entries will be in the same or nearby blocks on disk. > > >>** ** > > ** ** > > Technically, yes. That would really help, but the

Re: [PERFORM] New server setup

2013-03-05 Thread Niels Kristian Schjødt
Thanks, that was actually what I just ended up doing yesterday. Any suggestion how to tune pgbouncer? BTW, I have just bumped into an issue that caused me to disable pgbouncer again actually. My web application is querying the database with a per request based SEARCH_PATH. This is because I use

Re: [PERFORM] New server setup

2013-03-05 Thread Scott Marlowe
On Tue, Mar 5, 2013 at 9:34 AM, Kevin Grittner wrote: > Niels Kristian Schjødt wrote: > >> So my question is, should I also get something like pgpool2 setup >> at the same time? Is it, from your experience, likely to increase >> my throughput a lot more, if I had a connection pool of eg. 20 >> co

Re: [PERFORM] New server setup

2013-03-05 Thread Kevin Grittner
Niels Kristian Schjødt wrote: > So my question is, should I also get something like pgpool2 setup > at the same time? Is it, from your experience, likely to increase > my throughput a lot more, if I had a connection pool of eg. 20 > connections, instead of 300 concurrent ones directly? In my exp

Re: [PERFORM] Optimize SELECT * from table WHERE foreign_key_id IN (key1,key2,key3,key4...)

2013-03-05 Thread Julien Cigar
On 03/05/2013 15:00, Niels Kristian Schjødt wrote: Hi, I'm running a rails app, where I have a model called Car that has_many Images. Now when I tell rails to include those images, when querying say 50 cars, then it often decides to use a SELECT * from images WHERE car_id IN (id1,id2,id3,id4…

[PERFORM] Optimize SELECT * from table WHERE foreign_key_id IN (key1,key2,key3,key4...)

2013-03-05 Thread Niels Kristian Schjødt
Hi, I'm running a rails app, where I have a model called Car that has_many Images. Now when I tell rails to include those images, when querying say 50 cars, then it often decides to use a SELECT * from images WHERE car_id IN (id1,id2,id3,id4…) instead of doing a join. Now either way it uses t