[PERFORM] is it possible to get the optimizer to use indexes with a like clause

2003-12-18 Thread Dave Cramer
It appears that the optimizer only uses indexes for = clause? Dave ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [PERFORM] is it possible to get the optimizer to use indexes

2003-12-18 Thread Dave Cramer
after vacuum verbose analyze, I still get explain select * from isppm where item_upc_cd like '06038301234'; QUERY PLAN --- Seq Scan on isppm (cost=1.00..19684.89 rows=2 width=791)

Re: [PERFORM] is it possible to get the optimizer to use indexes

2003-12-24 Thread Dave Cramer
Doug, Yes, it does depend on the locale, you can get around this in 7.4 by building the index with smart operators Dave On Thu, 2003-12-18 at 20:38, Doug McNaught wrote: Christopher Kings-Lynne [EMAIL PROTECTED] writes: It appears that the optimizer only uses indexes for = clause? The

Re: [PERFORM] Toooo many context switches (maybe SLES8?)

2004-04-18 Thread Dave Cramer
with the ServerWorks chipset on those motherboards. If anyone knows a high-end hardware+linux kernel geek I can corner, I'd appreciate it. Maybe I should contact OSDL ... -- Dave Cramer 519 939 0336 ICQ # 14675561 ---(end of broadcast)--- TIP 4: Don't

Re: [PERFORM] Wierd context-switching issue on Xeon

2004-04-18 Thread Dave Cramer
unregister YourEmailAddressHere to [EMAIL PROTECTED]) !DSPAM:4082feb7326901956819835! -- Dave Cramer 519 939 0336 ICQ # 14675561 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] Horribly slow hash join

2004-04-19 Thread Dave Cramer
an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly !DSPAM:40837183123741526418863! -- Dave Cramer 519 939 0336 ICQ # 14675561 ---(end of broadcast)--- TIP 5

Re: [PERFORM] Wierd context-switching issue on Xeon

2004-04-20 Thread Dave Cramer
] http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html !DSPAM:40851da1199651145780980! -- Dave Cramer

Re: [PERFORM] Toooo many context switches (maybe SLES8?)

2004-04-20 Thread Dave Cramer
cleanly !DSPAM:408535ce93801252113544! -- Dave Cramer 519 939 0336 ICQ # 14675561 ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [PERFORM] Wierd context-switching issue on Xeon

2004-04-20 Thread Dave Cramer
? Dave On Tue, 2004-04-20 at 12:59, Josh Berkus wrote: Anjan, Quad 2.0GHz XEON with highest load we have seen on the applications, DB performing great - Can you run Tom's test? It takes a particular pattern of data access to reproduce the issue. -- Dave Cramer 519 939 0336 ICQ

Re: [PERFORM] Wierd context-switching issue on Xeon

2004-04-21 Thread Dave Cramer
get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) !DSPAM:40866735106778584283649! -- Dave Cramer 519 939 0336 ICQ # 14675561 ---(end of broadcast)--- TIP 1: subscribe

Re: [PERFORM] Wierd context-switching issue on Xeon

2004-04-21 Thread Dave Cramer
? Is it exactly the same resource, or two resources that happen to have test-and-set flags in the same cache line? On Apr 20, 2004, at 7:41 PM, Dave Cramer wrote: I modified the code in s_lock.c to remove the spins #define SPINS_PER_DELAY 1 and it doesn't exhibit the behaviour

Re: [PERFORM] Wierd context-switching issue on Xeon patch for 7.4.1

2004-04-21 Thread Dave Cramer
attached. -- Dave Cramer 519 939 0336 ICQ # 14675561 Index: backend/storage/lmgr/s_lock.c === RCS file: /usr/local/cvs/pgsql-server/src/backend/storage/lmgr/s_lock.c,v retrieving revision 1.16 diff -c -r1.16 s_lock.c *** backend

Re: [PERFORM] Wierd context-switching issue on Xeon patch for 7.4.1

2004-04-22 Thread Dave Cramer
the average time it takes to get finished with the shared resource then this should reduce cs. Certainly more ideas are required here. Dave On Wed, 2004-04-21 at 22:35, Tom Lane wrote: Dave Cramer [EMAIL PROTECTED] writes: diff -c -r1.16 s_lock.c *** backend/storage/lmgr/s_lock.c 8 Aug 2003 21

Re: [PERFORM] Wierd context-switching issue on Xeon patch for 7.4.1

2004-04-22 Thread Dave Cramer
the CSes by about 40%. An improvement, but not a magic bullet. -- Dave Cramer 519 939 0336 ICQ # 14675561 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED

Re: [PERFORM] Wierd context-switching issue on Xeon

2004-05-01 Thread Dave Cramer
baseline measurement at 100) 250 500 1000 1500 2000 3000 5000 ... until you find an optimal level. Then report the results to us! Some results. The patch mentioned is what Dave Cramer posted to the Performance list on 4/21. A Perl script monitored vmstat 1 for 120

Re: [PERFORM] Wierd context-switching issue on Xeon

2004-05-02 Thread Dave Cramer
Default patch - 10 : 17297 21929 patch at 100 : 26825 87073 patch at 1000 : 37580 110849 -- Dave Cramer 519 939 0336 ICQ # 14675561 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] Wierd context-switching issue on Xeon patch for 7.4.1

2004-04-26 Thread Dave Cramer
23 0 -- Dave Cramer 519 939 0336 ICQ # 14675561 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html

Re: [PERFORM] Wierd context-switching issue on Xeon patch for 7.4.1

2004-04-27 Thread Dave Cramer
one at a time. This would take days of testing ... -- Dave Cramer 519 939 0336 ICQ # 14675561 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so

Re: [PERFORM] [JDBC] is a good practice to create an index on the oid?

2004-04-26 Thread Dave Cramer
! -- Dave Cramer 519 939 0336 ICQ # 14675561 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [JDBC] [PERFORM] is a good practice to create an index on the

2004-04-27 Thread Dave Cramer
)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html !DSPAM:408dcc51235334924183622! -- Dave Cramer 519 939 0336 ICQ # 14675561 ---(end of broadcast)--- TIP 3

Re: [JDBC] Cursors performance (was: Re: [PERFORM] Terrible

2004-07-09 Thread Dave Cramer
:40eefff6170301475214189! -- Dave Cramer 519 939 0336 ICQ # 14675561 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])

[PERFORM] help with query

2004-08-19 Thread Dave Cramer
. Is there a way to rewrite the top query to get the same results? I have already talked to Best Practical, and subqueries are not easily embraced. Dave -- Dave Cramer 519 939 0336 ICQ # 14675561 ---(end of broadcast)--- TIP 7: don't forget to increase your

Re: [PERFORM] help with query

2004-08-19 Thread Dave Cramer
' and LocalTarget = '17417' and (LocalBase = main.id or LocalTarget = main.id) ) ) Those are the only things I can think of to make it work, anyways. Dave Cramer wrote: RT uses a query like: SELECT distinct main.oid,main.* FROM Tickets main WHERE (main.EffectiveId = main.id

Re: [PERFORM] help with query

2004-08-19 Thread Dave Cramer
from Links where Type = 'MemberOf' and LocalTarget = '17417' and (LocalBase = main.id or LocalTarget = main.id) ) ) Those are the only things I can think of to make it work, anyways. Dave Cramer wrote: RT uses a query like: SELECT distinct main.oid,main.* FROM Tickets

Re: [PERFORM] Excessive context switching on SMP Xeons

2004-10-14 Thread Dave Cramer
on a tmpfs. -Bill Montgomery ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Dave Cramer 519 939 0336 ICQ # 14675561 www.postgresintl.com ---(end of broadcast)--- TIP 9: the planner

Re: [PERFORM] futex results with dbt-3

2004-10-20 Thread Dave Cramer
---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Dave Cramer www.postgresintl.com 519 939 0336 ICQ

Re: [PERFORM] Large Database Performance suggestions

2004-10-22 Thread Dave Cramer
)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your

Re: [PERFORM] How to speed-up inserts with jdbc

2004-11-10 Thread Dave Cramer
Insets / Seconds. How could we make it faster ? Inserting 1000 rows via INSERT AS SELECT is much faster. regards Michael ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Dave Cramer http

Re: [PERFORM] Insertion puzzles

2004-11-13 Thread Dave Cramer
use COPY FROM instead of INSERT ? And have you tested the performance with fsync on and off. -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan

Re: [PERFORM] Insertion puzzles

2004-11-14 Thread Dave Cramer
Actually, the most damning thing in this configuration I had missed earlier 256MB of ram ! Dave Josh Berkus wrote: Vivek, Redhat Linux7.2 RAM: 256MB postgres: 7.1.3 Um, you do realise that both RH 7.2 and PostgreSQL 7.1 are no longer supported but their respective communities? -- Dave

[PERFORM] Interaction between Free Space Map an alternate location for a database

2004-11-18 Thread Dave Cramer
Can someone explain how the free space map deals with alternate database locations? Given that the free space map is global, and it is ostensibly managing free disk space, how does it deal with tuples across disk locations ? Dave -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ

Re: [PERFORM] Query Performance and IOWait

2004-11-18 Thread Dave Cramer
Scottrade Financial Services (314) 965-1555 x 1513 Cell: (314) 369-2083 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561

Re: [PERFORM] Interaction between Free Space Map an alternate location

2004-11-18 Thread Dave Cramer
with tuples across disk locations ? Are you talking Tablespaces? -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561

Re: [PERFORM] Interaction between Free Space Map an alternate location

2004-11-18 Thread Dave Cramer
---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561

Re: [PERFORM] Postgres vs. MySQL

2004-11-25 Thread Dave Cramer
% of the queries, but for some reason was broken for non-MySQL databases) later, and we were down to 3-4 index scans, a few orders of magnitude faster. :-) /* Steinar */ -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561 ---(end of broadcast

Re: [PERFORM] Alternatives to Dell?

2004-12-02 Thread Dave Cramer
what you would pay for a comparable HP server (and Dell doesn't even offer Opteron). You can do the same with Monarch Computers. A 4u quad opteron. You can also pay a lot more, depends on the configuration. They have a very nice site for building a system as you want. -- Dave Cramer http

Re: [PERFORM] Low Performance for big hospital server ..

2005-01-02 Thread Dave Cramer
message can get through to the mailing list cleanly -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] Low Performance for big hospital server ..

2005-01-03 Thread Dave Cramer
commands go to [EMAIL PROTECTED] -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561 ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [PERFORM] Low Performance for big hospital server ..

2005-01-03 Thread Dave Cramer
? I presume you are vacuuming on a regular basis? Yes , vacuumdb daily. -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561

Re: [PERFORM] Very Bad Performance.

2005-01-03 Thread Dave Cramer
| 64 effective_cache_size | 393216 Thanks! Pallav ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561 ---(end of broadcast

Re: [PERFORM] Low Performance for big hospital server ..

2005-01-03 Thread Dave Cramer
to find out which queries are slow. Dave Amrit Thailand ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ

Re: [PERFORM] Low Performance for big hospital server ..

2005-01-03 Thread Dave Cramer
William Yu wrote: Dave Cramer wrote: William Yu wrote: [EMAIL PROTECTED] wrote: I will try to reduce shared buffer to 1536 [1.87 Mb]. 1536 is probaby too low. I've tested a bunch of different settings on my 8GB Opteron server and 10K seems to be the best setting. Be careful here, he

Re: [PERFORM] Low Performance for big hospital server ..

2005-01-05 Thread Dave Cramer
-- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561

Re: [PERFORM] Problems with high traffic

2005-01-06 Thread Dave Cramer
. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ

Re: [PERFORM] Problems with high traffic

2005-01-06 Thread Dave Cramer
kernel when RedHat finalizes their release. I am not familular with many of the logging features of postgres just the outputing the output to a file instead of /dev/null. Benjamin On Jan 6, 2005, at 5:06 PM, Dave Cramer wrote: Ben Well, we need more information pg version, hardware, memory, etc you

Re: [PERFORM] Problems with high traffic

2005-01-06 Thread Dave Cramer
to the 2.6 kernel when RedHat finalizes their release. I am not familular with many of the logging features of postgres just the outputing the output to a file instead of /dev/null. Benjamin On Jan 6, 2005, at 5:06 PM, Dave Cramer wrote: Ben Well, we need more information pg version, hardware

Re: [PERFORM] Low Performance for big hospital server ..

2005-01-06 Thread Dave Cramer
that your message can get through to the mailing list cleanly -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs

Re: [PERFORM] PostgreSQL vs. Oracle vs. Microsoft

2005-01-10 Thread Dave Cramer
of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose

Re: [PERFORM] PostgreSQL vs. Oracle vs. Microsoft

2005-01-10 Thread Dave Cramer
)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go

Re: [PERFORM] PostgreSQL vs. Oracle vs. Microsoft

2005-01-10 Thread Dave Cramer
process, even if it costs you the round trip. Dave Gary Doades wrote: Dave Cramer wrote: I'm curious, why do you think that's serious ? What do you really expect to do in the stored procedure ? Anything of consequence will seriously degrade performance if you select it in say a million rows. I'm

Re: [PERFORM] PostgreSQL vs. Oracle vs. Microsoft

2005-01-11 Thread Dave Cramer
connections that's a hundred .NET vm's or java vm's. Is the .NET VM shareable ? Dave Gary Doades wrote: Dave Cramer wrote: Ok, so one use case is to select a large number of rows and do some non-trivial operation on them. I can see where getting the rows inside the server process ( ie some procedural

Re: [PERFORM] Increasing RAM for more than 4 Gb. using postgresql

2005-01-17 Thread Dave Cramer
with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561

Re: [PERFORM] Increasing RAM for more than 4 Gb. using postgresql

2005-01-19 Thread Dave Cramer
your desire to choose an index scan if your joining column's datatypes do not match -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http

Re: [PERFORM] PostgreSQL clustering VS MySQL clustering

2005-01-20 Thread Dave Cramer
like Google do to get an incredible database in size and so quick access ? regards, -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561

Re: [PERFORM] PostgreSQL clustering VS MySQL clustering

2005-01-20 Thread Dave Cramer
... Am I clear ? Regards, -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561

Re: [PERFORM] PostgreSQL clustering VS MySQL clustering

2005-01-20 Thread Dave Cramer
This idea won't work with postgresql only one instance can operate on a datastore at a time. Dave Bruno Almeida do Lago wrote: I was thinking the same! I'd like to know how other databases such as Oracle do it. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL

Re: [PERFORM] Performance problem on delete from for 10k rows. May

2005-03-16 Thread Dave Cramer
under different conditions, so it might not mean much. No offense intended, but I remember doesn't carry as much weight as a documented example. ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Dave Cramer http

Re: [PERFORM] Hardware questions

2005-03-27 Thread Dave Cramer
Junaili, I'd suggest you don't buy a dell. The aren't particularly good performers. Dave Junaili Lie wrote: Hi guys, We are in the process of buying a new dell server. Here is what we need to be able to do: - we need to be able to do queries on tables that has 10-20 millions of records (around

Re: [PERFORM] How to improve db performance with $7K?

2005-03-29 Thread Dave Cramer
0m0.510s (the file was not in the cache) = about 52 MB/s (reiser3.6) So, you have a problem with your hardware... ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Dave Cramer http

Re: [PERFORM] How to improve db performance with $7K?

2005-04-02 Thread Dave Cramer
Yeah, 35Mb per sec is slow for a raid controller, the 3ware mirrored is about 50Mb/sec, and striped is about 100 Dave PFC wrote: With hardware tuning, I am sure we can do better than 35Mb per sec. Also WTF ? My Laptop does 19 MB/s (reading 10 KB files, reiser4) ! A recent desktop

Re: [PERFORM] Performance Tuning Article

2005-06-23 Thread Dave Cramer
the archives for xeon sooner or later you will bump into something relevant. -- Kind Regards, Keith ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings Dave Cramer [EMAIL PROTECTED] www.postgresintl.com ICQ

Re: [PERFORM] Performance Tuning Article

2005-06-23 Thread Dave Cramer
AFAIK, the problem was the buffer manager Dave On 23-Jun-05, at 9:46 AM, Radu-Adrian Popescu wrote: Dave Cramer wrote: My understanding is that it isn't particularly XEON processors that is the problem Any dual processor will exhibit the problem, XEON's with hyperthreading exacerbate

Re: [PERFORM] [JDBC] Performance problem using V3 protocol in jdbc driver

2005-08-17 Thread Dave Cramer
Barry, One way to do this is to turn logging on for calls over a certain duration log_duration in the config file. This will only log calls over n milliseconds. There's a tool called iron eye SQL that monitors JDBC calls. http://www.irongrid.com/ unfortunately I am getting DNS errors

Re: [PERFORM] Prepared statement not using index

2005-09-12 Thread Dave Cramer
The difference between the 7.4 driver and the 8.0.3 driver is the 8.0.3 driver is using server side prepared statements and binding the parameter to the type in setXXX(n,val). The 7.4 driver just replaces the ? with the value and doesn't use server side prepared statements. Dave On

Re: [PERFORM] Prepared statement not using index

2005-09-12 Thread Dave Cramer
On 12-Sep-05, at 9:22 AM, Guido Neitzer wrote: On 12.09.2005, at 14:38 Uhr, Dave Cramer wrote: The difference between the 7.4 driver and the 8.0.3 driver is the 8.0.3 driver is using server side prepared statements and binding the parameter to the type in setXXX(n,val). Would

Re: [PERFORM] Prepared statement not using index

2005-09-12 Thread Dave Cramer
It's added, just use the old protocol . Here are the connection parameters http://jdbc.postgresql.org/documentation/head/connect.html#connection- parameters Dave On 12-Sep-05, at 9:26 AM, Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 The difference between

Re: [PERFORM] How to determine cause of performance problem?

2005-09-23 Thread Dave Cramer
Joost, I presume you are using a relatively new jdbc driver. Make sure you have added prepareThreshold=1 to the url to that it will use a named server side prepared statement You could also use your mod 100 code block to implement batch processing of the inserts. see addBatch, in jdbc

Re: [PERFORM] Advice on RAID card

2005-09-25 Thread Dave Cramer
I would think software raid would be quite inappropriate considering postgres when it is working is taking a fair amount of CPU as would software RAID. Does anyone know if this is really the case ? Dave On 25-Sep-05, at 6:17 AM, Michael Ben-Nes wrote: I would consider Software Raid PFC

Re: [PERFORM] Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian??

2005-11-14 Thread Dave Cramer
Joost, I've got experience with these controllers and which version do you have. I'd expect to see higher than 50MB/s although I've never tried RAID 5 I routinely see closer to 100MB/s with RAID 1+0 on their 9000 series I would also suggest that shared buffers should be higher than 7500,

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-15 Thread Dave Cramer
Luke,Have you tried the areca cards, they are slightly faster yet.DaveOn 15-Nov-05, at 7:09 AM, Luke Lonergan wrote: I agree - you can get a very good one from www.acmemicro.com or www.rackable.com with 8x 400GB SATA disks and the new 3Ware 9550SX SATA RAID controller for about $6K with two

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Dave Cramer
On 17-Nov-05, at 2:50 PM, Alex Turner wrote: Just pick up a SCSI drive and a consumer ATA drive. Feel their weight. You don't have to look inside to tell the difference. At one point stereo manufacturers put weights in the case just to make them heavier. The older ones weighed more and

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Dave Cramer
On 18-Nov-05, at 1:07 AM, Luke Lonergan wrote: Greg, On 11/17/05 9:17 PM, Greg Stark [EMAIL PROTECTED] wrote: Ok, a more productive point: it's not really the size of the database that controls whether you're I/O bound or CPU bound. It's the available I/O bandwidth versus your CPU

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Dave Cramer
On 18-Nov-05, at 8:30 AM, Luke Lonergan wrote: Richard, On 11/18/05 5:22 AM, "Richard Huxton" dev@archonet.com wrote: Well, I'm prepared to swap Luke *TWO* $1000 systems for one $80,000 system if he's got one going :-) Finally, a game worth playing! Except it’s backward – I’ll show you 80

Re: [PERFORM] Hardware/OS recommendations for large databases (

2005-11-18 Thread Dave Cramer
Luke,Interesting numbers. I'm a little concerned about the use of blockdev —setra 16384. If I understand this correctly it assumes that the table is contiguous on the disk does it not ?DaveOn 18-Nov-05, at 10:13 AM, Luke Lonergan wrote: Dave, On 11/18/05 5:00 AM, "Dave Cramer" [EMAIL

Re: [PERFORM] Hyperthreading slows processes?

2005-11-20 Thread Dave Cramer
Yeah, it's pretty much a known issue for postgres Dave On 20-Nov-05, at 4:46 PM, Craig A. James wrote: This article on ZDNet claims that hyperthreading can *hurt* performance, due to contention in the L1/L2 cache by a second process: http://news.zdnet.co.uk/0,39020330,39237341,00.htm Has

Re: [PERFORM] effizient query with jdbc

2005-12-22 Thread Dave Cramer
The problem is you are getting the entire list back at once. You may want to try using a cursor. Dave On 15-Dec-05, at 9:44 AM, [EMAIL PROTECTED] wrote: Hi, I have a java.util.List of values (1) which i wanted to use for a query in the where clause of an simple select statement.

Re: [PERFORM] Migration study, step 1: bulk write performance optimization

2006-03-20 Thread Dave Cramer
Others are reporting better performance on 8.1.x with very large shared buffers. You may want to try tweaking that possibly as high as 20% of available memory Dave On 20-Mar-06, at 9:59 AM, Mikael Carneholm wrote: Ok, here's the deal: I am responisble for an exciting project of evaluating

Re: [PERFORM] PostgreSQL performance issues

2006-08-30 Thread Dave Cramer
On 30-Aug-06, at 7:35 AM, Willo van der Merwe wrote: Luke Lonergan wrote: Currently the load looks like this: Cpu0 : 96.8% us, 1.9% sy, 0.0% ni, 0.3% id, 0.0% wa, 0.0% hi, 1.0% si Cpu1 : 97.8% us, 1.6% sy, 0.0% ni, 0.3% id, 0.0% wa, 0.0% hi, 0.3% si Cpu2 : 96.8% us, 2.6%

Re: [PERFORM] performance problems.

2006-08-30 Thread Dave Cramer
On 30-Aug-06, at 10:10 AM, Vivek Khera wrote: On Aug 30, 2006, at 5:29 AM, Matthew Sullivan wrote: The hardware is a Compaq 6400r with 4G of EDO RAM, 4x500MHz Xeons and a Compaq RAID 3200 in RAID 5 configuration running across 3 spindles (34G total space). The OS is FreeBSD

Re: [PERFORM] Postgress memory leak with JBoss3.2.6 and large DB

2006-08-31 Thread Dave Cramer
On 31-Aug-06, at 1:54 PM, Tom Lane wrote: Indika Maligaspe [EMAIL PROTECTED] writes: The problem is when we are querying a specific set of table (which all tables having over 100K of rows), the Postgres user process takes over or close 700MB of memory. This is just to return 3000 odd

Re: [PERFORM] High concurrency OLTP database performance tuning

2006-08-31 Thread Dave Cramer
On 31-Aug-06, at 11:45 AM, Cosimo Streppone wrote: Good morning, I'd like to ask you some advice on pg tuning in a high concurrency OLTP-like environment. The application I'm talking about is running on Pg 8.0.1. Under average users load, iostat and vmstat show that iowait stays well under

Re: [PERFORM] performance problems.

2006-08-31 Thread Dave Cramer
On 31-Aug-06, at 2:15 PM, Vivek Khera wrote: On Aug 30, 2006, at 7:48 PM, Dave Cramer wrote: Actually unless you have a ram disk you should probably leave random_page_cost at 4, shared buffers should be 2x what you have here, maintenance work mem is pretty high effective cache should

Re: [PERFORM] increasing shared buffers: how much should be removed from OS filesystem cache?

2006-09-01 Thread Dave Cramer
Guillaume 1G is really not a significant amount of memory these days, That said 6-10% of available memory should be given to an 8.0 or older version of postgresql Newer versions work better around 25% I'm not sure what you mean by mechanically removed from effective_cache effective cache

Re: [PERFORM] increasing shared buffers: how much should be removed from OS filesystem cache?

2006-09-01 Thread Dave Cramer
On 1-Sep-06, at 3:49 PM, Merlin Moncure wrote: On 01 Sep 2006 19:00:52 +0200, Guillaume Cottenceau [EMAIL PROTECTED] wrote: Hi, I've been looking at the results from the pg_statio* tables, to view the impact of increasing the shared buffers to increase performance. I think 'shared

Re: [PERFORM] database bloat,non removovable rows, slow query etc... [RESOLVED]

2006-09-02 Thread Dave Cramer
Matteo, On 2-Sep-06, at 4:37 AM, Matteo Sgalaberni wrote: On Fri, Sep 01, 2006 at 01:35:20PM -0400, Tom Lane wrote: Matteo Sgalaberni [EMAIL PROTECTED] writes: Ok. I stopped all clients. No connections to this database. When you say this database, do you mean the whole postmaster

Re: [PERFORM] increasing shared buffers: how much should be removed from OS filesystem cache?

2006-09-04 Thread Dave Cramer
On 4-Sep-06, at 8:07 AM, Guillaume Cottenceau wrote: Dave Cramer pg 'at' fastcrypt.com writes: Guillaume 1G is really not a significant amount of memory these days, Yeah though we have 2G or 4G of RAM in our servers (and not only postgres running on it). That said 6-10% of available

Re: [PERFORM] increasing shared buffers: how much should be removed

2006-09-05 Thread Dave Cramer
On 5-Sep-06, at 9:31 AM, Merlin Moncure wrote: On 9/1/06, Joshua D. Drake [EMAIL PROTECTED] wrote: I think 'shared buffers' is one of the most overrated settings from a performance standpoint. however you must ensure there is enough for things the server does besides caching. It

Re: [PERFORM] Xeon Woodcrest/Dempsey vs Opteron Socket F/940 with postgresql and some SAS raid-figures

2006-09-08 Thread Dave Cramer
Hi, Arjen, On 8-Sep-06, at 1:51 AM, Arjen van der Meijden wrote: Hi, We've been running our webapp database-benchmark again on mysql and postgresql. This time using a Fujitsu-Siemens RX300 S3 machine equipped with a 2.66Ghz Woodcrest (5150) and a 3.73Ghz Dempsey (5080). And compared

Re: [PERFORM] Xeon Woodcrest/Dempsey vs Opteron Socket F/940 with postgresql and some SAS raid-figures

2006-09-08 Thread Dave Cramer
On 8-Sep-06, at 8:44 AM, Arjen van der Meijden wrote: Dave Cramer wrote: Hi, Arjen, The Woodcrest is quite a bit faster than the Opterons. Actually... With Hyperthreading *enabled* the older Dempsey- processor is also faster than the Opterons with PostgreSQL. But then again

Re: [PERFORM] Poor performance on seq scan

2006-09-13 Thread Dave Cramer
On 13-Sep-06, at 6:16 AM, Laszlo Nagy wrote: I have had extremely bad performance historically with onboard SATA chipsets on Linux. The one exception has been with the Intel based chipsets (not the CPU, the I/O chipset). This board has Intel chipset. I cannot remember the exact type

Re: [PERFORM] sql-bench

2006-09-13 Thread Dave Cramer
All of the tuning parameters would affect all queries shared buffers, wal buffers, effective cache, to name a few --dc-- On 13-Sep-06, at 8:24 AM, yoav x wrote: Hi I am trying to run sql-bench against PostgreSQL 8.1.4 on Linux. Some of the insert tests seems to be ver slow For example:

Re: [PERFORM] sql-bench

2006-09-13 Thread Dave Cramer
wrote: So why are these queries so slow in PG? --- Dave Cramer [EMAIL PROTECTED] wrote: All of the tuning parameters would affect all queries shared buffers, wal buffers, effective cache, to name a few --dc-- On 13-Sep-06, at 8:24 AM, yoav x wrote: Hi I am trying to run sql-bench against

Re: [PERFORM] sql-bench

2006-09-14 Thread Dave Cramer
Have you tuned postgresql ? You still haven't told us what the machine is, or the tuning parameters. If you follow Merlin's links you will find his properly tuned postgres out performs mysql in every case. --dc-- On 14-Sep-06, at 2:55 AM, yoav x wrote: You can use the test with InnoDB by

Re: [PERFORM] Vacuums on large busy databases

2006-09-14 Thread Dave Cramer
On 14-Sep-06, at 11:23 AM, Francisco Reyes wrote: My setup: Freebsd 6.1 Postgresql 8.1.4 Memory: 8GB SATA Disks Raid 1 10 spindles (2 as hot spares) 500GB disks (16MB buffer), 7200 rpm Raid 10 Raid 2 4 spindles 150GB 10K rpm disks Raid 10 shared_buffers = 1 shared buffers should be

Re: [PERFORM] Vacuums on large busy databases

2006-09-14 Thread Dave Cramer
Francisco On 14-Sep-06, at 1:36 PM, Francisco Reyes wrote: Dave Cramer writes: What is effective_cache set to ? Increasing this seems to have helped significantly a web app. Load times seem magnitudes faster. Increased it to effective_cache_size = 12288 # 96MB What is a reasonable

Re: [PERFORM] Vacuums on large busy databases

2006-09-14 Thread Dave Cramer
Francisco On 14-Sep-06, at 4:30 PM, Francisco Reyes wrote: Dave Cramer writes: What is a reasonable number? I estimate I have at least 1 to 2 GB free of memory. You are using 6G of memory for something else ? Right now adding up from ps the memory I have about 2GB. Have an occassional

Re: [PERFORM] Vacuums on large busy databases

2006-09-14 Thread Dave Cramer
On 14-Sep-06, at 7:50 PM, Francisco Reyes wrote: Dave Cramer writes: personally, I'd set this to about 6G. This doesn't actually consume memory it is just a setting to tell postgresql how much memory is being used for cache and kernel buffers Gotcha. Will increase further. regarding

Re: [PERFORM] Opteron vs. Xeon benchmark

2006-09-23 Thread Dave Cramer
On 23-Sep-06, at 9:00 AM, Guido Neitzer wrote: I find the benchmark much more interesting in comparing PostgreSQL to MySQL than Intel to AMD. It might be as biased as other benchmarks but it shows clearly something that a lot of PostgreSQL user always thought: MySQL gives up on concurrency ...

Re: [PERFORM] Opteron vs. Xeon benchmark

2006-09-23 Thread Dave Cramer
On 23-Sep-06, at 9:49 AM, Guido Neitzer wrote: On 9/23/06, Dave Cramer [EMAIL PROTECTED] wrote: 1) The database fits entirely in memory, so this is really only testing CPU, not I/O which should be taken into account IMO I don't think this really is a reason that MySQL broke down on ten

Re: [PERFORM] New hardware thoughts

2006-10-20 Thread Dave Cramer
Ben, On 20-Oct-06, at 3:49 AM, Ben Suffolk wrote: Hello all, I am currently working out the best type of machine for a high volume pgsql database that I going to need for a project. I will be purchasing a new server specifically for the database, and it won't be running any other

  1   2   >