Re: [PERFORM] Insert performance, what should I expect?

2004-10-20 Thread Andrew McMillan
On Wed, 2004-10-20 at 11:53 +1000, Brock Henry wrote: Test 1, For each import, I'm dropping all indexes and pkeys/fkeys, then importing, then adding keys and indexes. Then I've got successive runs. I figure the reindexing will get more expensive as the database grows? Sounds like the right

Re: [PERFORM] Free PostgreSQL Training, Philadelphia, Oct 30

2004-10-20 Thread Aaron Werman
I'm driving from Tenafly NJ and going to both sessions. If you're able to get to the George Washington Bridge (A train to 178th Street [Port Authority North] and a bus over the bridge), I can drive you down. I'm not sure right now about the return because I have confused plans to meet someone.

Re: [PERFORM] How to time several queries?

2004-10-20 Thread nd02tsk
It doesn't seem to work. I want a time summary at the end. I am inserting insert queries from a file with the \i option. This is the outcome: [7259] LOG: statement: INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27'); [7259] LOG: duration: 1.672 ms [7259] LOG: statement:

Re: [PERFORM] Which plattform do you recommend I run PostgreSQL

2004-10-20 Thread nd02tsk
Thank you. Tim hi, [EMAIL PROTECTED] wrote: Hello I am doing a comparison between MySQL and PostgreSQL. In the MySQL manual it says that MySQL performs best with Linux 2.4 with ReiserFS on x86. Can anyone official, or in the know, give similar information regarding PostgreSQL? Also,

Re: [PERFORM] Free PostgreSQL Training, Philadelphia, Oct 30

2004-10-20 Thread Aaron Mulder
If anyone is going to take the train all the way, please e-mail me offline. There is a train station relatively close to the event (NY to Philly then the R5 to Malvern), but it's not within walking distance, so we'll figure out some way to pick people up from there. Thanks, Aaron

[PERFORM] OS desicion

2004-10-20 Thread Tom Fischer
Hi List, I have a Dual-Xeon 3Ghz System with with GB RAM and an Adaptec 212ß SCSI RAID with 4 SCA Harddiscs. Our customer wants to have the Machine tuned for best Database performance. Which OS should we used? We are tending between Linux 2.6 or FreeBSD. The Database Size is 5GB and ascending.

Re: [PERFORM] OS desicion

2004-10-20 Thread Matt Clark
You are asking the wrong question. The best OS is the OS you (and/or the customer) knows and can administer competently. The real performance differences between unices are so small as to be ignorable in this context. The context switching bug is not OS-dependent, but varys in severity

Re: [PERFORM] how much mem to give postgres?

2004-10-20 Thread Josh Close
On Wed, 20 Oct 2004 00:35:31 -0400, Tom Lane [EMAIL PROTECTED] wrote: I suspect that fooling with shared_buffers is entirely the wrong tree for you to be barking up. My suggestion is to be looking at individual queries that are slow, and seeing how to speed those up. This might involve

Re: [PERFORM] how much mem to give postgres?

2004-10-20 Thread Josh Close
On Tue, 19 Oct 2004 22:23:24 -0700, Josh Berkus [EMAIL PROTECTED] wrote: There have been issues with Postgres+HT, especially on Linux 2.4. Try turning HT off if other tuning doesn't solve things. Otherwise, see: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html How would I turn

Re: [PERFORM] how much mem to give postgres?

2004-10-20 Thread Matt Clark
How would I turn that off? In the kernel config? Not too familiar with that. I have a 2 proc xeon with 4 gigs of mem on the way for postgres, so I hope HT isn't a problem. If HT is turned off, does it just not use the other half of the processor? Or does the processor just work as one unit?

Re: [PERFORM] Index not used in query. Why?

2004-10-20 Thread Contact AR-SD.NET
Is there a solution to make it faster? At the end I need only in the query the id_status =4 and 6, but if I write in the sql query (where condition) where id_status in (4,6), the explain says the same(the slow version). For example: SELECT count(o.id) FROM orders o

Re: [PERFORM] How to time several queries?

2004-10-20 Thread Matthew Nuzum
When I'm using psql and I want to time queries, which is what I've been doing for a little over a day now, I do the following: Select now(); query 1; query 2; query 3; select now(); This works fine unless you're doing selects with a lot of rows which will cause your first timestamp to scroll off

Re: [PERFORM] OS desicion

2004-10-20 Thread Josh Berkus
Tom, You are asking the wrong question. The best OS is the OS you (and/or the customer) knows and can administer competently. I'll have to 2nd this. The real performance differences between unices are so small as to be ignorable in this context. Well, at least the difference between

Re: [PERFORM] futex results with dbt-3

2004-10-20 Thread Mark Wong
On Sun, Oct 17, 2004 at 09:39:33AM +0200, Manfred Spraul wrote: Neil wrote: . In any case, the futex patch uses the Linux 2.6 futex API to implement PostgreSQL spinlocks. Has anyone tried to replace the whole lwlock implementation with pthread_rwlock? At least for Linux with recent

Re: [PERFORM] Insert performance, what should I expect?

2004-10-20 Thread Robert Creager
When grilled further on (Tue, 19 Oct 2004 22:12:28 -0400), Rod Taylor [EMAIL PROTECTED] confessed: I've done some manual benchmarking running my script 'time script.pl' I realise my script uses some of the time, bench marking shows that %50 of the time is spent in dbd:execute. 1) Drop

Re: [PERFORM] OS desicion

2004-10-20 Thread Matt Clark
The real performance differences between unices are so small as to be ignorable in this context. <> Well, at least the difference between Linux and BSD. There are substantial tradeoffs should you chose to use Solaris or UnixWare. Yes, quite right, I should have said

Re: [PERFORM] futex results with dbt-3

2004-10-20 Thread Tom Lane
Manfred Spraul [EMAIL PROTECTED] writes: Tom Lane wrote: The bigger problem here is that the SMP locking bottlenecks we are currently seeing are *hardware* issues (AFAICT anyway). The only way that futexes can offer a performance win is if they have a smarter way of executing the basic

Re: [PERFORM] Insert performance, what should I expect?

2004-10-20 Thread Rod Taylor
On Wed, 2004-10-20 at 12:45, Robert Creager wrote: When grilled further on (Tue, 19 Oct 2004 22:12:28 -0400), Rod Taylor [EMAIL PROTECTED] confessed: I've done some manual benchmarking running my script 'time script.pl' I realise my script uses some of the time, bench marking shows that

Re: [PERFORM] how much mem to give postgres?

2004-10-20 Thread Steve Atkins
On Wed, Oct 20, 2004 at 03:07:00PM +0100, Matt Clark wrote: You turn it off in the BIOS. There is no 'other half', the processor is just pretending to have two cores by shuffling registers around, which gives maybe a 5-10% performance gain in certain multithreaded situations. opinionA

Re: [PERFORM] how much mem to give postgres?

2004-10-20 Thread Matt Clark
OT Hyperthreading is actually an excellent architectural feature that can give significant performance gains when implemented well and used for an appropriate workload under a decently HT aware OS. IMO, typical RDBMS streams are not an obviously appropriate workload, Intel didn't implement it

Re: [PERFORM] how much mem to give postgres?

2004-10-20 Thread Steve Atkins
On Wed, Oct 20, 2004 at 07:16:18PM +0100, Matt Clark wrote: OT Hyperthreading is actually an excellent architectural feature that can give significant performance gains when implemented well and used for an appropriate workload under a decently HT aware OS. IMO, typical RDBMS streams are

Re: [PERFORM] futex results with dbt-3

2004-10-20 Thread Dave Cramer
Forgive my naivete, but do futex's implement some priority algorithm for which process gets control. One of the problems as I understand it is that linux does (did ) not implement a priority algorithm, so it is possible for the context which just gave up control to be the next context woken

[PERFORM] iostat question

2004-10-20 Thread jelle
Hello All, I have an iostat question in that one of the raid arrays seems to act differently than the other 3. Is this reasonable behavior for the database or should I suspect a hardware or configuration problem? But first some background: Postgresql 7.4.2 Linux 2.4.20, 2GB RAM, 1-Xeon

[PERFORM] create index with substr function

2004-10-20 Thread Ray
Hi All, I have a table in my postgres: Table: doc Column | Type | Modifiers ---+-+---doc_id| bigint | not nullcomp_grp_id| bigint | not nulldoc_type| character varying(10)| not nulldoc_urn | character varying(20)| not null I want to

Re: [PERFORM] create index with substr function

2004-10-20 Thread Ray
Thank you all kindly response. : ) I am currently using postgres 7.3, so any example or solution for version after 7.4 if i want to create an index with substr function??? Thanks, Ray - Original Message - From: Stephan Szabo [EMAIL PROTECTED] To: Ray [EMAIL PROTECTED] Cc: [EMAIL

Re: [PERFORM] create index with substr function

2004-10-20 Thread Tom Lane
Ray [EMAIL PROTECTED] writes: CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree (SUBSTR(doc_urn,10)); ERROR: parser: parse error at or near 10 at character 68 This will work in 7.4, but not older releases. regards, tom lane ---(end of

Re: [PERFORM] create index with substr function

2004-10-20 Thread Stephan Szabo
On Thu, 21 Oct 2004, Ray wrote: Hi All, I have a table in my postgres: Table: doc Column |Type | Modifiers ---+-+--- doc_id | bigint | not null comp_grp_id | bigint

Re: [PERFORM] create index with substr function

2004-10-20 Thread Ray
sorry it doesn't works, as my postgres is 7.3 not 7.4. any other alternative solution for version after 7.4?? Thank Ray : ) - Original Message - From: Rosser Schwarz [EMAIL PROTECTED] To: Ray [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, October 21, 2004 11:34 AM Subject: Re:

Re: [PERFORM] create index with substr function

2004-10-20 Thread Rosser Schwarz
while you weren't looking, Ray wrote: CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree (SUBSTR(doc_urn,10)); CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree ((SUBSTR(doc_urn,10))); You need an additional set of parens around the SUBSTR() call. /rls -- :wq