Re: [PERFORM] Insert performance, what should I expect?
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 approach to me, if the tables are empty before the import. Successive Imports: 44,49,50,57,55,61,72 (seconds) = average 1051inserts/second (which now that I've written this seems fairly good) (A) Are you doing the whole thing inside a transaction? This will be significantly quicker. COPY would probably be quicker still, but the biggest difference will be a single transaction. (B) If you are starting with empty files, are you ensuring that the dead records are vacuumed before you start? I would recommend a vacuum full on the affected tables prior to the first import run (i.e. when the tables are empty). This is likely to be the reason that the timing on your successive imports increases so much. sort_mem = 4096 You probably want to increase this - if you have 1G of RAM then there is probably some spare. But if you actually expect to use 32 connections then 32 * 4M = 128M might mean a careful calculation is needed. If you are really only likely to have 1-2 connections running concurrently then increase it to (e.g.) 32768. max_fsm_relations = 300 If you do a vacuum full verbose; the last line will give you some clues as to what to set this (and max_fsm_pages) too. effective_cache_size = 16000 16000 * 8k = 128M seems low for a 1G machine - probably you could say 64000 without fear of being wrong. What does free show as cached? Depending on how dedicated the machine is to the database, the effective cache size may be as much as 80-90% of that. Can I expect it to go faster than this? I'll see where I can make my script itself go faster, but I don't think I'll be able to do much. I'll do some pre-prepare type stuff, but I don't expect significant gains, maybe 5-10%. I'd could happily turn off fsync for this job, but not for some other databases the server is hosting. You can probably double the speed - maybe more. Cheers, Andrew, - Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/PHYS: Level 2, 150-154 Willis St DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267 How many things I can do without! -- Socrates - signature.asc Description: This is a digitally signed message part
Re: [PERFORM] Free PostgreSQL Training, Philadelphia, Oct 30
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. /Aaron On Tue, 19 Oct 2004 14:43:29 -0400, Max Baker [EMAIL PROTECTED] wrote: On Wed, Oct 13, 2004 at 12:21:27PM -0400, Aaron Mulder wrote: All, My company (Chariot Solutions) is sponsoring a day of free PostgreSQL training by Bruce Momjian (one of the core PostgreSQL developers). The day is split into 2 sessions (plus a QA session): * Mastering PostgreSQL Administration * PostgreSQL Performance Tuning Registration is required, and space is limited. The location is Malvern, PA (suburb of Philadelphia) and it's on Saturday Oct 30. For more information or to register, see http://chariotsolutions.com/postgresql.jsp I'm up in New York City and would be taking the train down to Philly. Is anyone coming from Philly or New York that would be able to give me a lift to/from the train station? Sounds like a great event. Cheers, -m ---(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 -- Regards, /Aaron ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] How to time several queries?
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: INSERT INTO weather VALUES ('San Francisco', 46,
50, 0.25, '1994-11-27');
[7259] LOG: duration: 1.730 ms
[7259] LOG: statement: INSERT INTO weather VALUES ('San Francisco', 46,
50, 0.25, '1994-11-27');
[7259] LOG: duration: 1.698 ms
[7259] LOG: statement: INSERT INTO weather VALUES ('San Francisco', 46,
50, 0.25, '1994-11-27');
[7259] LOG: duration: 1.805 ms
[7259] LOG: statement: INSERT INTO weather VALUES ('San Francisco', 46,
50, 0.25, '1994-11-27');
[7259] LOG: duration: 1.670 ms
[7259] LOG: statement: INSERT INTO weather VALUES ('San Francisco', 46,
50, 0.25, '1994-11-27');
[7259] LOG: duration: 1.831 ms
[7259] LOG: statement: INSERT INTO weather VALUES ('San Francisco', 46,
50, 0.25, '1994-11-27');
[7259] LOG: duration: 1.815 ms
[7259] LOG: statement: INSERT INTO weather VALUES ('San Francisco', 46,
50, 0.25, '1994-11-27');
[7259] LOG: duration: 1.793 ms
[7259] LOG: statement: INSERT INTO weather VALUES ('San Francisco', 46,
50, 0.25, '1994-11-27');
[7259] LOG: duration: 1.660 ms
[7259] LOG: statement: INSERT INTO weather VALUES ('San Francisco', 46,
50, 0.25, '1994-11-27');
[7259] LOG: duration: 1.667 ms
[7259] LOG: statement: INSERT INTO weather VALUES ('San Francisco', 46,
50, 0.25, '1994-11-27');
[7259] LOG: duration: 1.754 ms
[7259] LOG: statement: INSERT INTO weather VALUES ('San Francisco', 46,
50, 0.25, '1994-11-27');
[7259] LOG: duration: 1.668 ms
[7259] LOG: statement: INSERT INTO weather VALUES ('San Francisco', 46,
50, 0.25, '1994-11-27');
[7259] LOG: duration: 1.688 ms
[7259] LOG: statement: INSERT INTO weather VALUES ('San Francisco', 46,
50, 0.25, '1994-11-27');
[7259] LOG: duration: 1.671 ms
[7259] LOG: statement: INSERT INTO weather VALUES ('San Francisco', 46,
50, 0.25, '1994-11-27');
[7259] LOG: duration: 1.787 ms
[7259] LOG: statement: INSERT INTO weather VALUES ('San Francisco', 46,
50, 0.25, '1994-11-27');
[7259] LOG: duration: 1.722 ms
[7309] LOG: statement: DELETE FROM weather;
[7309] LOG: duration: 11.314 ms
[7330] LOG: statement: INSERT INTO weather VALUES ('San Francisco', 46,
50, 0.25, '1994-11-27')
Tim
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [PERFORM] Which plattform do you recommend I run PostgreSQL
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, any links to benchmarking tests available on the internet between MySQL and PostgreSQL would be appreciated. http://www.potentialtech.com/wmoran/postgresql.php http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html http://candle.pha.pa.us/main/writings/pgsql/hw_performance/ http://database.sarang.net/database/postgres/optimizing_postgresql.html C. ---(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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Free PostgreSQL Training, Philadelphia, Oct 30
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 On Wed, 20 Oct 2004, Aaron Werman wrote: 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. /Aaron On Tue, 19 Oct 2004 14:43:29 -0400, Max Baker [EMAIL PROTECTED] wrote: On Wed, Oct 13, 2004 at 12:21:27PM -0400, Aaron Mulder wrote: All, My company (Chariot Solutions) is sponsoring a day of free PostgreSQL training by Bruce Momjian (one of the core PostgreSQL developers). The day is split into 2 sessions (plus a QA session): * Mastering PostgreSQL Administration * PostgreSQL Performance Tuning Registration is required, and space is limited. The location is Malvern, PA (suburb of Philadelphia) and it's on Saturday Oct 30. For more information or to register, see http://chariotsolutions.com/postgresql.jsp I'm up in New York City and would be taking the train down to Philly. Is anyone coming from Philly or New York that would be able to give me a lift to/from the train station? Sounds like a great event. Cheers, -m ---(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 -- Regards, /Aaron ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] OS desicion
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. Most SQL-Queries are Selects, the Tablesizes are beetween 300k and up to 10 MB. I've read the Hardware Performance Guide and the result was to take FreeBSD in the Decision too :) And what is on this Context Switiching Bug i have read in the Archive? Hope you can help me Regards Tom ---(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
Re: [PERFORM] OS desicion
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 across machine architectures (I understand it to be mostly P4/Athlon related, but don't take my word for it). M Tom Fischer wrote: 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. Most SQL-Queries are Selects, the Tablesizes are beetween 300k and up to 10 MB. I've read the Hardware Performance Guide and the result was to take FreeBSD in the Decision too :) And what is on this Context Switiching Bug i have read in the Archive? Hope you can help me Regards Tom ---(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 ---(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] how much mem to give postgres?
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 adding indexes, or tweaking the query source, or adjusting planner parameters, or several other things. EXPLAIN ANALYZE is your friend ... regards, tom lane Only problem is, a select count(1) is taking a long time. Indexes shouldn't matter with this since it's counting every row, right? The tables are fairly well indexed also, I could probably add a few more. If shared_buffers isn't the way to go ( you said 10k is the sweetspot ), then what about the effective_cache_size? I was suggested on the general list about possibly setting that to 75% of ram. Thanks. -Josh ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] how much mem to give postgres?
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 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? Also, I'm taking a look at that site right now :) -Josh ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] how much mem to give postgres?
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? 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 hack to overcome marchitactural limitations due to the overly long pipeline in the Prescott core./opinion. Really of most use for desktop interactivity rather than actual throughput. M ---(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] Index not used in query. Why?
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 INNER JOIN report r ON o.id=r.id_order INNER JOIN status s ON o.id_status=s.id INNER JOIN contact c ON o.id_ag=c.id INNER JOIN endkunde e ON o.id_endkunde=e.id INNER JOIN zufriden z ON r.id_zufriden=z.id INNER JOIN plannung v ON v.id=o.id_plannung INNER JOIN mpsworker w ON v.id_worker=w.id INNER JOIN person p ON p.id = w.id_person WHERE o.id_status in (4,6); The result for this query is also without index searches. I really have to make this query a little more faster. Suggestions? Regards, Andy. - Original Message - From: Tom Lane [EMAIL PROTECTED] To: Andrei Bintintan [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, October 19, 2004 7:52 PM Subject: Re: [PERFORM] Index not used in query. Why? Andrei Bintintan [EMAIL PROTECTED] writes: Hi to all! I have the following query. The execution time is very big, it doesn't use the indexes and I don't understand why... Indexes are not necessarily the best way to do a large join. If I use the following query the indexes are used: The key reason this wins seems to be that the id_status = 4 condition is far more selective than id_status 3 (the estimates are 52 and 36967 rows respectively ... is that accurate?) which means that the second query is inherently about 1/700th as much work. This, and not the use of indexes, is the fundamental reason why it's faster. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(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] How to time several queries?
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 the screen. -- Matthew Nuzum + Man was born free, and everywhere www.bearfruit.org : he is in chains, Rousseau +~~+ Then you will know the truth, and the TRUTH will set you free, Jesus Christ (John 8:32 NIV) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Monday, October 18, 2004 2:28 PM To: [EMAIL PROTECTED] Subject: [PERFORM] How to time several queries? Hello I posted this on the general list but think it would be more appropriate here. Sorry. I know it is possible to time isolated queries through the settting of the \timing option in psql. This makes PgSQL report the time it took to perform one operation. I would like to know how one can get a time summary of many operations, if it is at all possible. Thank you. Tim ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] OS desicion
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 Linux and BSD. There are substantial tradeoffs should you chose to use Solaris or UnixWare. The context switching bug is not OS-dependent, but varys in severity across machine architectures (I understand it to be mostly P4/Athlon related, but don't take my word for it). The bug is at its apparent worst on multi-processor HT Xeons and weak northbridges running Linux 2.4. However, it has been demonstrated (with lesser impact) on Solaris/Sparc, PentiumIII, and Athalon. Primarily it seems to affect data warehousing applications. Your choice of OS is not affected by this bug. -- Josh Berkus Aglio Database Solutions San Francisco ---(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] futex results with dbt-3
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 glibcs, pthread_rwlock is implemented with futexes, i.e. we would get a fast lock handling without os specific hacks. Perhaps other os contain user space pthread locks, too. Attached is an old patch. I tested it on an uniprocessor system a year ago and it didn't provide much difference, but perhaps the scalability is better. You'll have to add -lpthread to the library list for linking. I've heard that simply linking to the pthreads libraries, regardless of whether you're using them or not creates a significant overhead. Has anyone tried it for kicks? Mark ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Insert performance, what should I expect?
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 DBD::Pg and switch to the Pg driver for Perl instead (non-DBI compliant) which has functions similar to putline() that allow COPY to be used. COPY can be used with DBD::Pg, per a script I use: $dbh-do( COPY temp_obs_$band ( $col_list ) FROM stdin ); $dbh-func( join ( \t, @data ) . \n, 'putline' ); $dbh-func( \\.\n, 'putline' ); $dbh-func( 'endcopy' ); With sets of data from 1000 to 8000 records, my COPY performance is consistent at ~1 records per second. Cheers, Rob -- 10:39:31 up 2 days, 16:25, 2 users, load average: 2.15, 2.77, 3.06 Linux 2.6.5-02 #8 SMP Mon Jul 12 21:34:44 MDT 2004 pgp1zoDKUrU9u.pgp Description: PGP signature
Re: [PERFORM] OS desicion
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 'popular x86-based unices'.
Re: [PERFORM] futex results with dbt-3
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 atomic-test-and-set sequence than we do; lwlocks operations are not a basic atomic-test-and-set sequence. They are spinlock, several nonatomic operations, spin_unlock. Right, and it is the spinlock that is the problem. See discussions a few months back: at least on Intel SMP machines, most of the problem seems to have to do with trading the spinlock's cache line back and forth between CPUs. It's difficult to see how a futex is going to avoid that. regards, tom lane ---(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
Re: [PERFORM] Insert performance, what should I expect?
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 %50 of the time is spent in dbd:execute. 1) Drop DBD::Pg and switch to the Pg driver for Perl instead (non-DBI compliant) which has functions similar to putline() that allow COPY to be used. COPY can be used with DBD::Pg, per a script I use: $dbh-do( COPY temp_obs_$band ( $col_list ) FROM stdin ); $dbh-func( join ( \t, @data ) . \n, 'putline' ); $dbh-func( \\.\n, 'putline' ); $dbh-func( 'endcopy' ); Thanks for that. All of the conversations I've seen on the subject stated that DBD::Pg only supported standard DB features -- copy not amongst them. With sets of data from 1000 to 8000 records, my COPY performance is consistent at ~1 records per second. Well done. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] how much mem to give postgres?
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 hack to overcome marchitactural limitations due to the overly long pipeline in the Prescott core./opinion. Really of most use for desktop interactivity rather than actual throughput. 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 particularly well and I don't think there are any OSes that support it particularly well. /OT But don't write off using it in the future, when it's been improved at both the OS and the silicon levels. Cheers, Steve ---(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] how much mem to give postgres?
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 particularly well and I don't think there are any OSes that support it particularly well. /OT But don't write off using it in the future, when it's been improved at both the OS and the silicon levels. You are quite right of course - unfortunately the current Intel implementation meets nearly none of these criteria! As Rod Taylor pointed out off-list, IBM's SMT implementation on the Power5 is vastly superior. Though he's also just told me that Sun is beating IBM on price/performance for his workload, so who knows how reliable a chap he is... ;-) M ---(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] how much mem to give postgres?
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 not an obviously appropriate workload, Intel didn't implement it particularly well and I don't think there are any OSes that support it particularly well. /OT But don't write off using it in the future, when it's been improved at both the OS and the silicon levels. You are quite right of course - unfortunately the current Intel implementation meets nearly none of these criteria! Indeed. And when I said no OSes support it particularly well I meant the x86 SMT implementation, rather than SMT in general. As Rod pointed out, AIX seems to have decent support and Power has a very nice implementation, and the same is probably true for at least one other OS/architecture implementation. As Rod Taylor pointed out off-list, IBM's SMT implementation on the Power5 is vastly superior. Though he's also just told me that Sun is beating IBM on price/performance for his workload, so who knows how reliable a chap he is... ;-) :) Cheers, Steve ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] futex results with dbt-3
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 up, which of course is a complete waste of time. --dc-- Tom Lane wrote: 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 atomic-test-and-set sequence than we do; lwlocks operations are not a basic atomic-test-and-set sequence. They are spinlock, several nonatomic operations, spin_unlock. Right, and it is the spinlock that is the problem. See discussions a few months back: at least on Intel SMP machines, most of the problem seems to have to do with trading the spinlock's cache line back and forth between CPUs. It's difficult to see how a futex is going to avoid that. regards, tom lane ---(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#14675561 ---(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
[PERFORM] iostat question
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 2.4ghz with HT turned off 3Ware SATA RAID controller with 8 identical drives configured as 4 RAID-1 spindles 64MB RAM disk postgresql.conf differences to postgresql.conf.sample: tcpip_socket = true max_connections = 128 shared_buffers = 2048 vacuum_mem = 16384 max_fsm_pages = 5 wal_buffers = 128 checkpoint_segments = 64 effective_cache_size = 196000 random_page_cost = 1 default_statistics_target = 100 stats_command_string = true stats_block_level = true stats_row_level = true The database is spread over 5 spindles: /ram0 holds the busiest insert/update/delete table and assoc. indexes for temporary session data /sda5 holds the OS and most of the tables and indexes /sdb2 holds the WAL /sdc1 holds the 2nd busiest i/u/d table (70% of the writes) /sdd1 holds the single index for that busy table on/sdc1 Lately we have 45 connections open from a python/psycopg connection pool. 99% of the reads are cached. No swapping. And finally iostat reports: Device:rrqm/s wrqm/s r/s w/s rsec/s wsec/srkB/swkB/s avgrq-sz avgqu-sz await svctm %util /dev/sda50.01 3.32 0.01 0.680.16 32.96 0.0816.48 48.61 0.09 12.16 2.01 0.14 /dev/sdb20.00 6.38 0.00 3.540.01 79.36 0.0039.68 22.39 0.123.52 1.02 0.36 /dev/sdc10.03 0.13 0.00 0.080.271.69 0.13 0.84 24.06 0.13 163.28 13.75 0.11 /dev/sdd10.01 8.67 0.00 0.770.06 82.35 0.0341.18 107.54 0.09 10.51 2.76 0.21 The /sdc1's await seems awfully long compared to the rest to the stats. Jelle -- http://www.sv650.org/audiovisual/loading_a_bike.mpeg Osama-in-October office pool. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] create index with substr function
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 create an index on doc_urn column with using substr function like this: CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree (SUBSTR(doc_urn,10)); but there is an error: ERROR: parser: parse error at or near "10" at character 68 what's wrong for this SQL? As I have found some reference on the internet, I can't find anything wrong in this SQL. Thanks Ray
Re: [PERFORM] create index with substr function
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 PROTECTED] Sent: Thursday, October 21, 2004 10:57 AM Subject: Re: [PERFORM] create index with substr function 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 | not null doc_type | character varying(10)| not null doc_urn| character varying(20)| not null I want to create an index on doc_urn column with using substr function like this: CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree (SUBSTR(doc_urn,10)); but there is an error: ERROR: parser: parse error at or near 10 at character 68 what's wrong for this SQL? As I have found some reference on the internet, I can't find anything wrong in this SQL. What version are you using? If you're using anything previous to 7.4 then the above definately won't work and the only work around I know of is to make another function which takes only the column argument and calls substr with the 10 constant. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] create index with substr function
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 broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] create index with substr function
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 | not null doc_type | character varying(10)| not null doc_urn| character varying(20)| not null I want to create an index on doc_urn column with using substr function like this: CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree (SUBSTR(doc_urn,10)); but there is an error: ERROR: parser: parse error at or near 10 at character 68 what's wrong for this SQL? As I have found some reference on the internet, I can't find anything wrong in this SQL. What version are you using? If you're using anything previous to 7.4 then the above definately won't work and the only work around I know of is to make another function which takes only the column argument and calls substr with the 10 constant. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] create index with substr function
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: [PERFORM] create index with substr function 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 ---(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] create index with substr function
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
