Re: [PERFORM] Performance on new 64bit server compared to my 32bit desktop

2010-08-19 Thread Greg Smith
Greg Smith wrote: Since your smaller system has 2GB of RAM and the larger one 32GB, try this instead: pgbench -i -s 2000 pgbench -c 24 -T 60 -S pgbench -c 24 -T 300 Oh, and to at least give a somewhat more normal postgresql.conf I'd recommend you at least make the following two changes befor

Re: [PERFORM] Performance on new 64bit server compared to my 32bit desktop

2010-08-19 Thread Greg Smith
Philippe Rimbault wrote: I've run "time pgbench -c 50" : server x64 : starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 1 query mode: simple number of clients: 50 number of transactions per client: 10 number of tra

Re: [PERFORM] Performance on new 64bit server compared to my 32bit desktop

2010-08-19 Thread Scott Marlowe
On Thu, Aug 19, 2010 at 4:23 AM, Philippe Rimbault wrote: >> So how are the disks setup anyway? >> > > Thanks for your reply ! > > The server use a HP Smart Array P410 with a Raid 5 array on Sata 133 disk. If you can change that to RAID-10 do so now. RAID-5 is notoriously slow for database use,

Re: [PERFORM] yet another q

2010-08-19 Thread Tom Lane
Samuel Gendler writes: > fast plan: http://explain.depesz.com/s/iZ > slow plan: http://explain.depesz.com/s/Dv2 Your problem here is that it's switching from hash aggregation to sort-and-group-aggregate once it decides that the number of aggregate groups won't fit in work_mem anymore. While you

Re: Fwd: [PERFORM] Vacuum Full + Cluster + Vacuum full = non removable dead rows

2010-08-19 Thread Tom Lane
Alexandre de Arruda Paes writes: > Below, the pg_prepared_xacts result. OK, so you don't have any prepared transactions, but you're still not showing us the full content of pg_stat_activity. Just out of curiosity, how many rows does "select count(*) from tp93t" think there are?

Re: Fwd: [PERFORM] Vacuum Full + Cluster + Vacuum full = non removable dead rows

2010-08-19 Thread Kevin Grittner
Alexandre de Arruda Paes wrote: > 2010/8/18 Tom Lane >> There's an open transaction somewhere that VACUUM is preserving >> the tuples for. This transaction need not ever have touched the >> table, or ever intend to touch the table --- but VACUUM cannot >> know that, so it saves any tuples that

Re: [PERFORM] in-memory sorting

2010-08-19 Thread Tom Lane
Samuel Gendler writes: > Answered my own question. Cranking work_mem up to 350MB revealed that > the in-memory sort requires more memory than the disk sort. Yeah. The on-disk representation of sortable data is tighter than the in-memory representation for various reasons, mostly that we're will

Re: Fwd: [PERFORM] Vacuum Full + Cluster + Vacuum full = non removable dead rows

2010-08-19 Thread Alexandre de Arruda Paes
Hi Tom, Below, the pg_prepared_xacts result. The only way to restore the table is with TRUNCATE. Vacuum, Vacuum full, cluster not help and subsequent updates will become slow and slow. carmen=# select * from vlocks where relname='tp93t'; select * from pg_stat_activity where usename='webpa'; sel

Re: [PERFORM] Performance on new 64bit server compared to my 32bit desktop

2010-08-19 Thread Philippe Rimbault
On 19/08/2010 12:23, Philippe Rimbault wrote: On 19/08/2010 11:51, Scott Marlowe wrote: On Thu, Aug 19, 2010 at 2:07 AM, Philippe Rimbault wrote: Hi, I'm having a strange performance result on a new database server compared to my simple desktop. The configuration of the new server : -

Re: [PERFORM] Performance on new 64bit server compared to my 32bit desktop

2010-08-19 Thread Philippe Rimbault
On 19/08/2010 11:51, Scott Marlowe wrote: On Thu, Aug 19, 2010 at 2:07 AM, Philippe Rimbault wrote: Hi, I'm having a strange performance result on a new database server compared to my simple desktop. The configuration of the new server : - OS : GNU/Linux Debian Etch x86_64 - kerne

Re: [PERFORM] Performance on new 64bit server compared to my 32bit desktop

2010-08-19 Thread Scott Marlowe
On Thu, Aug 19, 2010 at 2:07 AM, Philippe Rimbault wrote: > Hi, > > I'm having a strange performance result on a new database server compared to > my simple desktop. > > The configuration of the new server : >    - OS : GNU/Linux Debian Etch x86_64 >    - kernel : Linux 2.6.26-2-vserver-amd64 #1 S

[PERFORM] Performance on new 64bit server compared to my 32bit desktop

2010-08-19 Thread Philippe Rimbault
Hi, I'm having a strange performance result on a new database server compared to my simple desktop. The configuration of the new server : - OS : GNU/Linux Debian Etch x86_64 - kernel : Linux 2.6.26-2-vserver-amd64 #1 SMP Sun Jun 20 20:40:33 UTC 2010 x86_64 GNU/Linux (tests ar

Re: [PERFORM] in-memory sorting

2010-08-19 Thread Scott Marlowe
On Thu, Aug 19, 2010 at 1:06 AM, Samuel Gendler wrote: > Incidentally, if I set values on the connection before querying, is there an > easy way to get things back to default values or will my code need to know > the prior value and explicitly set it back?  Something like reset work_mem; -- Sen

Re: [PERFORM] in-memory sorting

2010-08-19 Thread Samuel Gendler
On Thu, Aug 19, 2010 at 12:06 AM, Samuel Gendler wrote: > Incidentally, if I set values on the connection before querying, is there > an easy way to get things back to default values or will my code need to > know the prior value and explicitly set it back? Something like > > > set work_mem = '5

Re: [PERFORM] in-memory sorting

2010-08-19 Thread Samuel Gendler
Incidentally, if I set values on the connection before querying, is there an easy way to get things back to default values or will my code need to know the prior value and explicitly set it back? Something like set work_mem = '512MB' query set value = 'default' or maybe BEGIN; set work_mem='