[PERFORM] Some Performance Advice Needed
Hi, i recently run pgbench against different servers and got some results I dont quite understand. A) EV1: Dual Xenon, 2GHz, 1GB Memory, SCSI 10Krpm, RHE3 B) Dual Pentium3 1.4ghz (Blade), SCSI Disk 10Krmp, 1GB Memory, Redhat 8 C) P4 3.2GHz, IDE 7.2Krpm, 1GBMem, Fedora Core2 All did run only postgres 7.4.6 pgconf settings: max_connections = 100 shared_buffers = 8192 sort_mem = 8192 vacuum_mem = 32768 max_fsm_pages = 20 max_fsm_relations = 1 wal_sync_method = fsync wal_buffers = 64 checkpoint_segments = 10 effective_cache_size = 65536 random_page_cost = 1.4 /etc/sysctl.conf shmall and shmmax set to 768mb Runnig PGbench reported A) 220 tps B) 240 tps C) 510 tps Running hdparm reported A) 920mb/s (SCSI 10k) B) 270mb/s (SCSI 10k) C) 1750mb/s (IDE 7.2k) What I dont quite understand is why a P3.2 is twice as fast as a Dual Xenon with SCSI disks, A dual Xenon 2GHz is not faster than a dual P3 1.4Ghz, and the hdparm results also dont make much sense. Has anybody an explanation for that? Is there something I can do to get more performance out of the SCSI disks? Thanks for any advise Alex ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Some Performance Advice Needed
On Dec 23, 2004, at 9:27 AM, Alex wrote: Running hdparm reported A) 920mb/s (SCSI 10k) B) 270mb/s (SCSI 10k) C) 1750mb/s (IDE 7.2k) IDE disks lie about write completion (This can be disabled on some drives) whereas SCSI drives wait for the data to actually be written before they report success. It is quite easy to corrupt a PG (Or most any db really) on an IDE drive. Check the archives for more info. -- Jeff Trout [EMAIL PROTECTED] http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Some Performance Advice Needed
Alex wrote: Hi, i recently run pgbench against different servers and got some results I dont quite understand. A) EV1: Dual Xenon, 2GHz, 1GB Memory, SCSI 10Krpm, RHE3 B) Dual Pentium3 1.4ghz (Blade), SCSI Disk 10Krmp, 1GB Memory, Redhat 8 C) P4 3.2GHz, IDE 7.2Krpm, 1GBMem, Fedora Core2 Runnig PGbench reported A) 220 tps B) 240 tps C) 510 tps Running hdparm reported A) 920mb/s (SCSI 10k) B) 270mb/s (SCSI 10k) C) 1750mb/s (IDE 7.2k) What I dont quite understand is why a P3.2 is twice as fast as a Dual Xenon with SCSI disks, A dual Xenon 2GHz is not faster than a dual P3 1.4Ghz, and the hdparm results also dont make much sense. A few things to clear up about the P3/P4/Xeons. Xeons are P4s. Hence, a P4 2ghz will run the same speed as a Xeon 2ghz assuming all other variables are the same. Of course they aren't because your P4 is probably running unregistered memory, uses either a 533mhz or 800mhz FSB compared to the Xeon's shared 400mhz amongs 2 CPUs, running a faster non-smp kernel. Add all those variables up and it's definitely possible for a P4 3.2ghz to run twice as fast as a Dual Xeon 2ghz on a single-thread benchmark. (The corollary here is that in a multi-thread benchmark, the 2X Xeon can only hope to equal your P4 3.2.) P3s are faster than P4s at the same clock rate. By a lot. It's not really that surprising that a P3 1.4 is faster than a P4/Xeon 2.0. I've seen results like this many times over a wide range of applications. The only variable that is throwing off your comparisons are the hard drives. IDE drives have write caching on by default -- SCSI drives have it off. Use: hdparm -W0 /dev/hda to turn it off on the P4 system and rerun the tests then. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Some Performance Advice Needed
IDE disks lie about write completion (This can be disabled on some drives) whereas SCSI drives wait for the data to actually be written before they report success. It is quite easy to corrupt a PG (Or most any db really) on an IDE drive. Check the archives for more info. Do we have any real info on this? Specifically which drives? Is SATA the same way? What about SATA-II? I am not saying it isn't true (I know it is) but this is a blanket statement that may or may not be true with newer tech. From my experience with SATA controllers, write caching is controlled via the BIOS. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Memory leak tsearch2 VACUUM FULL VERBOSE ANALYZE
Pailloncy Jean-Gerard [EMAIL PROTECTED] writes:
I think I have a test case for 7.4.2
Try the attached patch.
It looked to me like there were some smaller leaks going on during COPY
and CREATE INDEX, which I will look into later --- but this seems to be
the problem for VACUUM FULL.
regards, tom lane
Index: vacuum.c
===
RCS file: /cvsroot/pgsql/src/backend/commands/vacuum.c,v
retrieving revision 1.263
diff -c -r1.263 vacuum.c
*** vacuum.c2 Oct 2003 23:19:44 - 1.263
--- vacuum.c23 Dec 2004 22:37:57 -
***
*** 2041,2046
--- 2041,2047
ExecStoreTuple(newtup, slot,
InvalidBuffer, false);
ExecInsertIndexTuples(slot,
(newtup.t_self),
estate, true);
+
ResetPerTupleExprContext(estate);
}
WriteBuffer(cur_buffer);
***
*** 2174,2179
--- 2175,2181
{
ExecStoreTuple(newtup, slot, InvalidBuffer,
false);
ExecInsertIndexTuples(slot, (newtup.t_self),
estate, true);
+ ResetPerTupleExprContext(estate);
}
} /* walk along
page */
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] LIMIT causes SEQSCAN in subselect
The fact that the estimator knows that the LIMIT is pointless because there are less rows in the subselect than the LIMIT will return is not something we want to count on; sometimes the estimator has innaccurate information. The UNIQUE index makes this more certain, except that I'm not sure that the planner distinguishes between actual UNIQUE indexes and columns which are estimated unique (per the pg_stats). And I think you can see in your case that there's quite a difference between a column we're CERTAIN is unique, versus a column we THINK is unique. I think a UNIQUE constraint can permit several 'different' NULL values... better say UNIQUE NOT NULL ? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Using LIMIT changes index used by planner
On Mon, 13 Dec 2004 17:43:07 -0500, Tom Lane [EMAIL PROTECTED] wrote: Sven Willenberger [EMAIL PROTECTED] writes: explain analyze select storelocation,order_number from custacct where referrer = 1365 and orderdate between '2004-12-07' and '2004-12-07 12:00:00' order by custacctid limit 10; why not create an index on referrer, orderdate ? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Caching of Queries
I've looked at PREPARE, but apparently it only lasts per-session - that's worthless in our case (web based service, one connection per data-requiring connection). You don't use persistent connections ??? Your problem might simply be the connection time overhead (also including a few TCP roudtrips). ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
