Re: [PERFORM] inferior SCSI performance

2003-10-02 Thread Hannu Krosing
Christopher Browne kirjutas K, 01.10.2003 kell 19:21: The FS-related result appeared surprising, as the stories I had heard suggested that JFS hadn't been particularly heavily tuned on Linux, whereas XFS was supposed to be the speed demon. Gentoo linux recommends XFS only for SAN+fibre

Re: [PERFORM] inferior SCSI performance

2003-10-02 Thread Greg Stark
Unfortunately, while there are companies hawking SSDs, they are in the you'll have to talk to our salescritter for pricing category, which means that they must be ferociously expensive. :-(. the cheapest I found was the one with external backup power was ~1.8k$ for 2GB PCI device

Re: [PERFORM] inferior SCSI performance

2003-10-02 Thread Ang Chin Han
Andrew Sullivan wrote: Yes. If and only if you have a battery-backed cache. I know of no IDE drives that have that, but there's nothing about the spec which makes it impossible. http://www.ussg.iu.edu/hypermail/linux/kernel/0103.0/1084.html Relevant section: quote Maybe that is why there is a

[PERFORM] basket, eggs NAS (was eggs Re: [NOVICE] Ideal Hardware?)

2003-10-02 Thread Ron Johnson
On Wed, 2003-10-01 at 10:13, Jason Hihn wrote: We have an opportunity to purchase a new, top-notch database server. I am wondering what kind of hardware is recommended? We're on Linux platforms and kernels though. I remember a comment from Tom about how he was spending a lot of time debugging

Re: [PERFORM] advice on raid controller

2003-10-02 Thread Vivek Khera
PG == Palle Girgensohn [EMAIL PROTECTED] writes: PG Come to think of it, I guess a battery-backed cache will make fsync as PG fast as no fsync, right? So, the q was kinda stoopid... :-/ In my testing, yes, the battery cache makes fsync=true just about as fast as fsync=false. it was only about

Re: [PERFORM] advice on raid controller

2003-10-02 Thread Vivek Khera
RJ == Richard Jones [EMAIL PROTECTED] writes: RJ Hi, i'm on the verge of buying a MegaRAID SCSI 320-2 raid controller. RJ I need it to build a db server using 4x ultra320 scsi disks RJ i'm thinking raid 1+0 but will try with raid5 too and compare No specific tips on that particular RAID, but in

Re: [PERFORM] TPC-R benchmarks

2003-10-02 Thread Oleg Lebedev
I ran VACUUM FULL ANALYZE yesterday and the re-ran the query with EXPLAIN ANALYZE. I got the same query plan and execution time. -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 4:20 PM To: Oleg Lebedev Cc: Josh Berkus; scott.marlowe; [EMAIL

[PERFORM] runtime of the same query in function differs on 2 degree!

2003-10-02 Thread Andriy Tkachuk
Hi folks. What's wrong with planner that executes my query in function?: (i mean no explanation but runtime) tele=# EXPLAIN analyze select calc_total(6916799, 1062363600, 1064955599); QUERY PLAN

Re: [PERFORM] TPC-R benchmarks

2003-10-02 Thread Oleg Lebedev
As Scott recommended, I did the following: # set enable_nestloop = false; # vacuum full analyze; After this I re-ran the query and its execution time went down from 2 hours to 2 minutes. I attached the new query plan to this posting. Is there any way to optimize it even further? What should I do

Re: [PERFORM] TPC-R benchmarks

2003-10-02 Thread scott.marlowe
Have you tried increasing the statistics target for those columns that are getting bad estimates yet and then turning back on enable_nestloop and rerunning analyze and seeing how the query does? The idea being to try and get a good enough estimate of your statistics so the planner stops

Re: [PERFORM] low cardinality column

2003-10-02 Thread Rod Taylor
On Thu, 2003-10-02 at 14:30, Rong Wu wrote: Hi, I have a select like this: SELECT MAX(transactionid) FROM cbntransaction WHERE transactiontypeid=0; For various reasons (primarily MVCC and the ability to make custom aggregates making it difficult) MAX() is not optimized in this fashion.

[PERFORM] count(*) slow on large tables

2003-10-02 Thread Dror Matalon
Hi, I have a somewhat large table, 3 million rows, 1 Gig on disk, and growing. Doing a count(*) takes around 40 seconds. Looks like the count(*) fetches the table from disk and goes through it. Made me wonder, why the optimizer doesn't just choose the smallest index which in my case is around

Re: [PERFORM] count(*) slow on large tables

2003-10-02 Thread Tomasz Myrta
Hi, I have a somewhat large table, 3 million rows, 1 Gig on disk, and growing. Doing a count(*) takes around 40 seconds. Looks like the count(*) fetches the table from disk and goes through it. Made me wonder, why the optimizer doesn't just choose the smallest index which in my case is around 60

Re: [PERFORM] TPC-R benchmarks

2003-10-02 Thread Oleg Lebedev
I was trying to get the pg_stats information to Josh and decided to recreate the indexes on all my tables. After that I ran vacuum full analyze, re-enabled nestloop and ran explain analyze on the query. It ran in about 2 minutes. I attached the new query plan. I am not sure what did the trick, but

[PERFORM] further testing on IDE drives

2003-10-02 Thread scott.marlowe
I was testing to get some idea of how to speed up the speed of pgbench with IDE drives and the write caching turned off in Linux (i.e. hdparm -W0 /dev/hdx). The only parameter that seems to make a noticeable difference was setting wal_sync_method = open_sync. With it set to either fsync, or

Re: [PERFORM] count(*) slow on large tables

2003-10-02 Thread Bruno Wolff III
On Thu, Oct 02, 2003 at 12:46:45 -0700, Dror Matalon [EMAIL PROTECTED] wrote: Please keep replies copied to the list. When would it happen that a tuple be invisible to the current transaction? Are we talking about permissions? They could be tuples that were changed by a transaction that

Re: [PERFORM] TPC-R benchmarks

2003-10-02 Thread scott.marlowe
On Thu, 2 Oct 2003, Oleg Lebedev wrote: I was trying to get the pg_stats information to Josh and decided to recreate the indexes on all my tables. After that I ran vacuum full analyze, re-enabled nestloop and ran explain analyze on the query. It ran in about 2 minutes. I attached the new

Thanks - Re: [PERFORM] low cardinality column

2003-10-02 Thread Rong Wu
Thanks, Rod, Josh and Bill, That' fantastic. have a nice day, rong :-) Rod Taylor wrote: On Thu, 2003-10-02 at 14:30, Rong Wu wrote: Hi, I have a select like this: SELECT MAX(transactionid) FROM cbntransaction WHERE transactiontypeid=0; For various reasons (primarily MVCC and the ability

Re: [PERFORM] count(*) slow on large tables

2003-10-02 Thread Jean-Luc Lachance
That's one of the draw back of MVCC. I once suggested that the transaction number and other house keeping info be included in the index, but was told to forget it... It would solve once and for all the issue of seq_scan vs index_scan. It would simplify the aggregate problem. Bruno Wolff III

Re: [PERFORM] further testing on IDE drives

2003-10-02 Thread scott.marlowe
On Thu, 2 Oct 2003, scott.marlowe wrote: I was testing to get some idea of how to speed up the speed of pgbench with IDE drives and the write caching turned off in Linux (i.e. hdparm -W0 /dev/hdx). The only parameter that seems to make a noticeable difference was setting wal_sync_method

Re: [PERFORM] runtime of the same query in function differs on 2 degree!

2003-10-02 Thread Gaetano Mendola
Andriy Tkachuk wrote: Hi folks. What's wrong with planner that executes my query in function?: (i mean no explanation but runtime) tele=# EXPLAIN analyze select calc_total(6916799, 1062363600, 1064955599); QUERY PLAN

[PERFORM] Is This My Speed Limit?

2003-10-02 Thread CN
Hi! It's just my curiosity. I wonder if there is any way to break my speed limit on AMD 450Mhz: Best Regards, CN --- --This table contains 1036 rows. CREATE TABLE table1 ( c1 VARCHAR(20) PRIMARY KEY, c2 char )WITHOUT OIDS; - --This table contains 9429 rows.

Re: [PERFORM] count(*) slow on large tables

2003-10-02 Thread Dror Matalon
I smell a religious war in the aii:-). Can you go several days in a row without doing select count(*) on any of your tables? I suspect that this is somewhat a domain specific issue. In some areas you don't need to know the total number of rows in your tables, in others you do. I also suspect

Re: [PERFORM] count(*) slow on large tables

2003-10-02 Thread Greg Stark
Christopher Browne [EMAIL PROTECTED] writes: It would be very hairy to implement it correctly, and all this would cover is the single case of SELECT COUNT(*) FROM SOME_TABLE; If you had a single WHERE clause attached, you would have to revert to walking through the tuples looking for the

Re: [PERFORM] TPC-R benchmarks

2003-10-02 Thread Josh Berkus
Oleg, I have another question. How do I optimize my indexes for the query that contains a lot of ORed blocks, each of which contains a bunch of ANDed expressions? The structure of each ORed block is the same except the right-hand-side values vary. Given the example, I'd do a multicolumn