Re: [PERFORM] Large querie with several EXISTS which will be often runned

2003-06-28 Thread Shridhar Daithankar
On Saturday 28 June 2003 14:47, Bruno BAGUETTE wrote: Do you mean this query ? SELECT products_options_groups.pk_prdoptgrp_id,products_options_groups.prdoptgr p_name FROM products_options_groups WHERE EXISTS ( SELECT * FROM products_options_classification INNER JOIN

Re: [PERFORM] PostgreSQL vs. MySQL

2003-07-04 Thread Shridhar Daithankar
On Friday 04 July 2003 18:16, Michael Mattox wrote: I'm actually leaving this list but I can answer this question. Our results were with a single user and we were running Inodb. We were running on RedHat 8.0 / 9.0 with vanilla linux settings. That's funny, you make a statement that

Re: [PERFORM] PostgreSQL vs. MySQL

2003-07-04 Thread Shridhar Daithankar
On Friday 04 July 2003 17:57, Brian Tarbox wrote: I recently took a system from MySQL to Postgres. Same HW, SW, same data. The major operations where moderately complex queries (joins on 8 tables). The results we got was that Postgres was fully 3 times slower than MySql. We were on this

Re: [PERFORM] can multiple vacuums gain speed?

2003-07-04 Thread Shridhar Daithankar
On 2 Jul 2003 at 16:17, Mats Kling wrote: Hi all! I have a big trouble with a database and hope you can help out on how to improve the time vacuum takes. The database grovs to ~60Gb and after a 'vacuum full' it's ~31Gb, after about a week the database it up to 55-60Gb again and i

Re: [PERFORM] PostgreSQL vs. MySQL

2003-07-04 Thread Shridhar Daithankar
On Friday 04 July 2003 20:36, Rod Taylor wrote: 2. Postgresql uses shared memory being process based architecture. Mysql uses process memory being threaded application. It does not need kernel settings to work and usually works best it can. MySQL has other issues with the kernel due to

Re: [NOVICE] [PERFORM] Extreme high load averages

2003-07-10 Thread Shridhar Daithankar
On 10 Jul 2003 at 0:43, Martin Foster wrote: As for creating a new table, that in itself is a nice idea. But it would cause issues for people currently in the realm. Their posts would essentially dissapear from site and cause more confusion then its worth. No they won't. Say you have a

Re: [PERFORM] Tuning PostgreSQL

2003-07-13 Thread Shridhar Daithankar
On Sunday 13 July 2003 10:23, Ron Johnson wrote: On Fri, 2003-07-04 at 09:49, Shridhar Daithankar wrote: On 4 Jul 2003 at 16:35, Michael Mattox wrote: [snip] On a positive note, me and Josh are finishing a bare bone performance article that would answer lot of your questions. I am

Re: [PERFORM] Pgsql - Red Hat Linux - VS MySQL VS MSSQL

2003-07-13 Thread Shridhar Daithankar
On Sunday 13 July 2003 12:05, Balazs Wellisch wrote: Hi all, However, to be able to justify the move I will have to demonstrate that PostgreSQL is up to par with MSSQL and MySQL when it comes to performance. After having read through the docs and the lists it seems obvious that PostgreSQL is

Re: [PERFORM] Pgsql - Red Hat Linux - VS MySQL VS MSSQL

2003-07-14 Thread Shridhar Daithankar
On Monday 14 July 2003 01:21, Balazs Wellisch wrote: Unfortunatelly, compiling from source is not really an option for us. We use RPMs only to ease the installation and upgrade process. We have over a hundred servers to maintaine and having to compile and recompile software everytime a new

Re: [PERFORM] Sanity check requested

2003-07-14 Thread Shridhar Daithankar
On 14 Jul 2003 at 12:51, Nick Fankhauser wrote: Any thoughts? Is this a sane plan? Are there other parameters I should consider changing first? Well, everything seems to be in order and nothing much to suggest I guess. But still.. 1. 30 users does not seem to be much of a oevrhead. If

Re: [PERFORM] index / sequential scan problem

2003-07-17 Thread Shridhar Daithankar
On 17 Jul 2003 at 11:01, Fabian Kreitner wrote: psql (PostgreSQL) 7.2.2 perg_1097=# VACUUM ANALYZE ; VACUUM perg_1097=# EXPLAIN ANALYZEselect notiz_id, obj_id, obj_typ perg_1097-# fromnotiz_objekt a perg_1097-# where not exists perg_1097-# ( perg_1097(# select

Re: [PERFORM] index / sequential scan problem

2003-07-17 Thread Shridhar Daithankar
On 17 Jul 2003 at 13:12, Fabian Kreitner wrote: At 11:17 17.07.2003, Shridhar Daithankar wrote: How about where ma_id = 2001::integer and ma_pid = 1097::integer in above query? I dont really understand in what way this will help the planner but ill try. That is typecasting

Re: [PERFORM] index / sequential scan problem

2003-07-17 Thread Shridhar Daithankar
On 17 Jul 2003 at 14:50, Fabian Kreitner wrote: At 14:34 17.07.2003, you wrote: I expect you will find that the third query is also a lot faster that the first query. Im afraid, no. Database has been stopped / started right before this. perg_1097=# set enable_seqscan to true; SET

Re: [PERFORM] Sanity check requested

2003-07-18 Thread Shridhar Daithankar
On 17 Jul 2003 at 10:41, Nick Fankhauser wrote: I'm using ext2. For now, I'll leave this and the OS version alone. If I I appreciate your approach but it almost proven that ext2 is not the best and fastest out there. IMO, you can safely change that to reiserfs or XFS. Or course, testing is

Re: [PERFORM] Tuning PostgreSQL

2003-07-21 Thread Shridhar Daithankar
On 21 Jul 2003 at 10:31, Alexander Priem wrote: What I am thinking about is buying a server with the following specifications: * 1 or 2 Intel Xeon processors (2.4 GHz). * 2 Gigabytes of RAM (DDR/ECC). * Three 36Gb SCSI160 disks (10.000rpm) in a RAID-5 config, giving 72Gb storage space

Re: [PERFORM] Tuning PostgreSQL

2003-07-21 Thread Shridhar Daithankar
Hi Alexander , On 21 Jul 2003 at 11:23, Alexander Priem wrote: So the memory settings I specified are pretty much OK? As of now yes, You need to test with these settings and make sure that they perform as per your requirement. That tweaking will always be there... What would be good

Re: [PERFORM] Tuning PostgreSQL

2003-07-21 Thread Shridhar Daithankar
On 21 Jul 2003 at 19:27, Ang Chin Han wrote: [1] That is, AFAIK, from our testing. Please, please correct me if I'm wrong: has anyone found that different filesystems produces wildly different performance for postgresql, FreeBSD's filesystems not included? well, when postgresql starts

Re: [PERFORM] [GENERAL] Performance hit of foreign key constraints?

2003-07-23 Thread Shridhar Daithankar
On 23 Jul 2003 at 16:05, Jean-Christian Imbeault wrote: I have a database which is constantly being written to. A web server's log file (and extras) is being written to it. There are no deletions or updates (at least I think so :). As the web traffic increases so will the write intensity.

Re: [PERFORM] hardware performance and some more

2003-07-25 Thread Shridhar Daithankar
On 25 Jul 2003 at 16:38, Kasim Oztoprak wrote: this is kind of directory assistance application. actually the select statements are not very complex. the database contain 25 million subscriber records and the operators searches for the subscriber numbers or addresses. there are not much

Re: [PERFORM] hardware performance and some more

2003-07-25 Thread Shridhar Daithankar
On 24 Jul 2003 at 9:42, William Yu wrote: As far as I can tell, the performance impact seems to be minimal. There's a periodic storm of replication updates in cases where there's mass updates sync last resync. But if you have mostly reads and few writes, you shouldn't see this situation.

Re: [PERFORM] Optimization

2003-07-29 Thread Shridhar Daithankar
On 28 Jul 2003 at 12:27, Josh Berkus wrote: Unless you're running PostgreSQL 7.1 or earlier, you should be VACUUMing every 10-15 minutes, not every 2-3 hours. Regular VACUUM does not lock your database. You will also want to increase your FSM_relations so that VACUUM is more

Re: [PERFORM] Autovacuum

2003-07-29 Thread Shridhar Daithankar
On 29 Jul 2003 at 8:03, Christopher Browne wrote: Shridhar Daithankar [EMAIL PROTECTED] wrote: It is called as pgavd.. No, it is called pg_autovacuum pgavd was a previous attempt at this that was being distributed on gborg. Its parser ussage (I don't recall if it was just lex or whether

Re: [SQL] [PERFORM] EXTERNAL storage and substring on long strings

2003-08-04 Thread Shridhar Daithankar
On 4 Aug 2003 at 12:14, Scott Cain wrote: I forgot about searching--I suspect that application is why I faced opposition for shredding in my schema development group. Maybe I should push that off to the file system and use grep (or BLAST). Otherwise, I could write a function that would

Re: [PERFORM] OSDL Database Test Suite 3 is available on PostgreSQL

2003-08-04 Thread Shridhar Daithankar
On 4 Aug 2003 at 15:33, Manfred Koizar wrote: On 01 Aug 2003 11:04:10 -0700, Jenny Zhang [EMAIL PROTECTED] wrote: A sample OSDL-DBT3 test result report can be found at: http://khack.osdl.org/stp/276912/ Your comments are welcome, I could not get postgresql .conf so I will combine the

Re: [PERFORM] Some vacuum tuning help

2003-08-06 Thread Shridhar Daithankar
On 5 Aug 2003 at 8:09, Jeff wrote: I've been trying to search through the archives, but it hasn't been successful. We recently upgraded from pg7.0.2 to 7.3.4 and things were happy. I'm trying to fine tune things to get it running a bit better and I'm trying to figure out how vacuum output

Re: [PERFORM] How Many Inserts Per Transactions

2003-08-07 Thread Shridhar Daithankar
On 5 Aug 2003 at 12:28, Christopher Browne wrote: On Oracle, I have seen performance Suck Badly when using SQL*Load; if I grouped too many updates together, it started blowing up the rollback segment, which was a Bad Thing. And in that kind of context, there will typically be some sweet spot

Re: [PERFORM] Perfomance Tuning

2003-08-08 Thread Shridhar Daithankar
On 8 Aug 2003 at 12:28, mixo wrote: I have just installed redhat linux 9 which ships with Pg 7.3.2. Pg has to be setup so that data inserts (blobs) should be able to handle at least 8M at a time. The machine has two P III 933MHz CPU's, 1.128G RAM (512M*2 + 128M), and a 36 Gig hd with 1 Gig

[PERFORM] Simple filesystem benchmark on Linux 2.6

2003-08-12 Thread Shridhar Daithankar
http://kerneltrap.org/node/view/715 Might be interesting for people running 2.6. Last I heard, the anticipatory scheduler did not yield it's maximum throughput for random reads. So they said database guys would not want it right away. Anybody using it for testing? Couple of guys are running it

Re: [PERFORM] Some vacuum tuning help

2003-08-12 Thread Shridhar Daithankar
On 5 Aug 2003 at 10:29, Christopher Browne wrote: Shridhar Daithankar wrote: I agree, specifying per table thresholds would be good in autovacuum.. Which begs the question of what the future direction is for pg_autovacuum. There would be some merit to having pg_autovacuum throw

Re: [PERFORM] Perfomance Tuning

2003-08-14 Thread Shridhar Daithankar
On 11 Aug 2003 at 23:42, Ron Johnson wrote: On Mon, 2003-08-11 at 19:50, Christopher Kings-Lynne wrote: Well, yeah. But given the Linux propensity for introducing major features in minor releases (and thereby introducing all the attendant bugs), I'd think twice about using _any_ Linux

Re: [PERFORM] Some vacuum tuning help

2003-08-14 Thread Shridhar Daithankar
On 5 Aug 2003 at 14:15, Peter Childs wrote: On Tue, 5 Aug 2003, Shridhar Daithankar wrote: On 5 Aug 2003 at 8:09, Jeff wrote: I would suggest autovacuum daemon which is in CVS contrib works for 7.3.x as well.. Or schedule a vacuum analyze every 15 minutes or so.. I've just

Re: [PERFORM] query/table design help

2003-08-14 Thread Shridhar Daithankar
On Tuesday 05 August 2003 15:03, Ara Anjargolian wrote: I have a table permissions with the fields (party_id integer, permission varchar, key_name varchar, key_value integer) for which I need to a query to see if a person has permission to carry out a particular action. The query looks

Re: [PERFORM] PostgreSQL performance problem - tuning

2003-08-14 Thread Shridhar Daithankar
On 6 Aug 2003 at 15:42, Yaroslav Mazurak wrote: sort_mem = 131072 This sort_mem value is *very* large - that's 131MB for *each sort* that gets done. I'd suggest trying something in the range 1,000-10,000. What's probably happening with the error above is that PG is allocating ridiculous

[PERFORM] Insert performance

2003-08-16 Thread Shridhar Daithankar
Hi all, Couple of days ago, one of my colleague, Rahul Iyer posted a query regarding insert performance of 5M rows. A common suggestion was to use copy. Unfortunately he can not use copy due to some constraints. I was helping him to get maximum out of it. We were playing with a data set of

Re: [PERFORM] Query too slow

2003-08-25 Thread Shridhar Daithankar
On 25 Aug 2003 at 8:44, Stephan Szabo wrote: On Mon, 25 Aug 2003, Rhaoni Chiu Pereira wrote: Hi List, As I said before, I'm not a DBA yet , but I'm learning ... and I already have a PostgreSQL running, so I have to ask some help... I got a SQL as folows : ...

Re: [PERFORM] Sun vs a P2. Interesting results.

2003-08-27 Thread Shridhar Daithankar
On 26 Aug 2003 at 8:34, Jeff wrote: Could it just be that the sun sucks? (And for the record - same schema, nearly same query (modified for datetime syntax) on informix runs in 3 seconds). My impression is IPC on sun has higher initial latency than linux. But given that you also ran the

[PERFORM] Comparing postgresql7.4 CVS head on linux 2.4.20 and 2.6.0-test4

2003-08-27 Thread Shridhar Daithankar
Hi all, I did some benchmarking using pgbench and postgresql CVS head, compiled yesterday. The results are attached. It looks like 2.6.0-test4 does better under load but under light load the performance isn't that great. OTOH 2.4.20 suffer major degradation compare to 2.6. Looks like linux

Re: [PERFORM] The results of my PostgreSQL/filesystem performance tests

2003-08-28 Thread Shridhar Daithankar
On 26 Aug 2003 at 21:47, Bill Moran wrote: Hey all. I said I was going to do it, and I finally did it. As with all performance tests/benchmarks, there are probably dozens or more reasons why these results aren't as accurate or wonderful as they should be. Take them for what they are and

Re: [PERFORM] Queries sometimes take 1000 times the normal time

2003-08-28 Thread Shridhar Daithankar
On 28 Aug 2003 at 1:07, Anders K. Pedersen wrote: Hello, We're running a set of Half-Life based game servers that lookup user privileges from a central PostgreSQL 7.3.4 database server (I recently ported the MySQL code in Adminmod to PostgreSQL to be able to do this). The data needed

Re: [PERFORM] pgsql inserts problem

2003-08-28 Thread Shridhar Daithankar
On 27 Aug 2003 at 15:50, Tarhon-Onu Victor wrote: Hi, I have a (big) problem with postgresql when making lots of inserts per second. I have a tool that is generating an output of ~2500 lines per seconds. I write a script in PERL that opens a pipe to that tool, reads every

Re: [PERFORM] Comparing postgresql7.4 CVS head on linux 2.4.20 and 2.6.0-test4

2003-08-28 Thread Shridhar Daithankar
On 27 Aug 2003 at 19:00, Neil Conway wrote: On Wed, Aug 27, 2003 at 09:02:25PM +0530, Shridhar Daithankar wrote: IIRC in a kernel release note recently, it was commented that IO scheduler is still being worked on and does not perform as much for random seeks, which exaclty what database

Re: [PERFORM] Queries sometimes take 1000 times the normal time

2003-08-28 Thread Shridhar Daithankar
On 28 Aug 2003 at 10:02, Russell Garrett wrote: The web site queries will jump up one or two orders of magnitude (I have seen a normally 100ms query take in excess of 30 seconds) in duration at seemingly random points. It's not always when the transactions are committing, and it doesn't seem

Re: [PERFORM] Hardware recommendations to scale to silly load

2003-08-28 Thread Shridhar Daithankar
On 28 Aug 2003 at 11:05, Chris Bowlby wrote: On Tue, 2003-08-26 at 23:59, Ron Johnson wrote: What a fun exercises. Ok, lets see: Postgres 7.3.4 RH AS 2.1 12GB RAM motherboard with 64 bit 66MHz PCI slots 4 - Xenon 3.0GHz (1MB cache) CPUs 8 - 36GB 15K RPM as RAID10 on a 64 bit

Re: [PERFORM] Simple queries take forever to run

2003-08-28 Thread Shridhar Daithankar
On 28 Aug 2003 at 10:38, Michael Guerin wrote: IN(subquery) is known to run poorly in 7.3.x and earlier. 7.4 is generally much better (for reasonably sized subqueries) but in earlier versions you'll probably want to convert into an EXISTS or join form. Something else seems to be going on,

Re: [PERFORM] Hardware recommendations to scale to silly load

2003-08-29 Thread Shridhar Daithankar
On 29 Aug 2003 at 0:05, William Yu wrote: Shridhar Daithankar wrote: Be careful here, we've seen that with the P4 Xeon's that are hyper-threaded and a system that has very high disk I/O causes the system to be sluggish and slow. But after disabling the hyper-threading itself, our system

[PERFORM] 2.4 v/s 2.6 again.

2003-08-29 Thread Shridhar Daithankar
Hi all, I compared 2.6 with elevator=deadline. It did bring some improvement in performance. But still it does not beat 2.4. Attached are three files for details. I also ran a simple insert benchmark to insert a million record in a simple table with a small int and a varchar(30). Here are

Re: [PERFORM] PostgreSQL is slow...HELP

2003-09-03 Thread Shridhar Daithankar
On 3 Sep 2003 at 6:08, Azlin Ghazali wrote: Hi, I'm working on a project to make an application run on MySQL and PostgreSQL. I find that PostgreSQL runs up to 10 times slower than MySQL. For small records it is not much problems. But as the records grew (up to 12,000 records) the

Re: [PERFORM] PostgreSQL Reliability when fsync = false on Linux-XFS

2003-09-04 Thread Shridhar Daithankar
On 3 Sep 2003 at 23:36, Rod Taylor wrote: - the way PostgreSQL expects data to be written to disk without the fsync calls for things not to get corrupted in the event of a crash, and If you want the filesystem to deal with this, I believe it is necessary for it to write the data

Re: [PERFORM] SELECT's take a long time compared to other DBMS

2003-09-04 Thread Shridhar Daithankar
On 4 Sep 2003 at 0:48, Relaxin wrote: All of the databases that I tested the query against gave me immediate access to ANY row of the resultset once the data had been returned. Ex. If I'm currently at the first row and then wanted to goto the 100,000 row, I would be there immediately, and if

Re: [PERFORM] Serious issues with CPU usage

2003-09-08 Thread Shridhar Daithankar
On 8 Sep 2003 at 13:50, Andri Saar wrote: If this is the best you can get with postgres right now, then I'll just have to increase the frequency of VACUUMing, but that feels like a hackish solution :( Use a autovacuum daemon. There is one in postgresql contrib module. It was introduced

Re: [PERFORM] Attempt at work around of int4 query won't touch int8 index ...

2003-09-15 Thread Shridhar Daithankar
On 10 Sep 2003 at 22:44, Tom Lane wrote: James Robinson [EMAIL PROTECTED] writes: Is this just a dead end, or is there some variation of this that might possibly work, so that ultimately an undoctored literal number, when applied to an int8 column, could find an index? I think it's

Re: [PERFORM] Effective Cache Size

2003-09-17 Thread Shridhar Daithankar
On 17 Sep 2003 at 11:48, Nick Barr wrote: Hi, I have been following a thread on this list Inconsistent performance and had a few questions especially the bits about effective_cache_size. I have read some of the docs, and some other threads on this setting, and it seems to used by the

Re: [PERFORM] LIKE query running slow

2003-09-24 Thread Shridhar Daithankar
Garrett Bladow wrote: Recently we upgraded the RAM in our server. After the install a LIKE query that used to take 5 seconds now takes 5 minutes. We have tried the usual suspects, VACUUM, ANALYZE and Re-indexing. Any thoughts on what might have happened? What all tuning you have done? Have you

Re: [PERFORM] Indices arent being used

2003-09-26 Thread Shridhar Daithankar
[EMAIL PROTECTED] wrote: Hi guys Im running a Datawarehouse benchmark (APB-1) on PostgreSql. The objective is to choose which of the to main db (PostgreSQL, MySQL) is fastest. I've run into a small problem which I hope could be resolved here. I'm trying to speed up this query: select count(*)

Re: [PERFORM] [GENERAL] Tuning/performance question.

2003-09-28 Thread Shridhar Daithankar
On Sunday 28 September 2003 09:19, David Griffiths wrote: No difference. Note that all the keys that are used in the joins are numeric(10)'s, so there shouldn't be any cast-issues. Can you make them bigint and see? It might make some difference perhaps. Checking the plan in the meantime.. BTW

Re: [PERFORM] Tuning/performance issue....

2003-10-01 Thread Shridhar Daithankar
David Griffiths wrote: And finally, Here's the contents of the postgresql.conf file (I've been playing with these setting the last couple of days, and using the guide @ http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html to make sure I didn't have it mis-tuned):

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

2003-10-03 Thread Shridhar Daithankar
Dror Matalon wrote: 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

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

2003-10-06 Thread Shridhar Daithankar
Bruce Momjian wrote: OK, I beefed up the TODO: * Use a fixed row count and a +/- count with MVCC visibility rules to allow fast COUNT(*) queries with no WHERE clause(?) I can always give the details if someone asks. It doesn't seem complex enough for a separate TODO.detail

Re: [PERFORM] Postgres low end processing.

2003-10-06 Thread Shridhar Daithankar
Stef wrote: On Fri, 03 Oct 2003 12:32:00 -0400 Tom Lane [EMAIL PROTECTED] wrote: = What exactly is failing? And what's the platform, anyway? Nothing is really failing atm, except the funds for better hardware. JBOSS and some other servers need to be run on these machines, along with linux,

Re: [PERFORM] reindex/vacuum locking/performance?

2003-10-06 Thread Shridhar Daithankar
Jeff wrote: I'd be interested in tinkering with this, but I'm more interested at the moment of why (with proof, not antecdotal) Solaris is so much slower than Linux and what we cna do about this. We're looking to move a rather large Informix db to PG and ops has reservations about ditching Sun

Re: [PERFORM] planner doesn't use multicolumn index

2003-10-08 Thread Shridhar Daithankar
Adrian Demaestri wrote: We've a table with about 8 million rows, and we need to get rows by the value of two of its fields( the type of the fields are int2 and int4, the where condition is v.g. partido=99 and partida=123). We created a multicolumn index on that fields but the planner doesn't use

Re: [PERFORM] Presentation

2003-10-08 Thread Shridhar Daithankar
Jeff wrote: Let me know if there are blatant errors, etc in there. Maybe even slightly more subtle blatant errors :) Some minor nitpicks, * Slide 5, postgresql already features 64 bit port. The sentence is slightly confusing * Same slide. IIRC postgresql always compresses bytea/varchar. Not too

Re: [PERFORM] Compare rows

2003-10-08 Thread Shridhar Daithankar
Greg Spiegelberg wrote: The data represents metrics at a point in time on a system for network, disk, memory, bus, controller, and so-on. Rx, Tx, errors, speed, and whatever else can be gathered. We arrived at this one 642 column table after testing the whole process from data gathering, methods

[PERFORM] Linux filesystem shootout

2003-10-09 Thread Shridhar Daithankar
http://www.ussg.iu.edu/hypermail/linux/kernel/0310.1/0208.html Shridhar ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [PERFORM] Linux filesystem shootout

2003-10-09 Thread Shridhar Daithankar
Kaarel wrote: http://www.ussg.iu.edu/hypermail/linux/kernel/0310.1/0208.html Shridhar I feel incompetent when it comes to file systems. Yet everybody would like to have the best file system if given the choice...so do I :) Here I am looking at those tables seeing JFS having more green

Re: [PERFORM] OFFTOPIC: PostgreSQL vs MySQL

2003-10-10 Thread Shridhar Daithankar
David Griffiths wrote: Have you checked these pages? They've been posted on this list numerous times: http://techdocs.postgresql.org http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html Those are much more

Re: [PERFORM] Performance, vacuum and reclaiming space, fsm

2003-10-13 Thread Shridhar Daithankar
Seum-Lim Gan wrote: I have a table that keeps being updated and noticed that after a few days, the disk usage has growned to from just over 150 MB to like 2 GB ! Hmm... You have quite a lot of wasted space there.. I followed the recommendations from the various search of the archives, changed the

Re: [PERFORM] Another weird one with an UPDATE

2003-10-13 Thread Shridhar Daithankar
David Griffiths wrote: It's a slight improvement, but that could be other things as well. I'd read that how you tune Postgres will determine how the optimizer works on a query (sequential scan vs index scan). I am going to post all I've done with tuning tommorow, and see if I've done anything

Re: [PERFORM] Performance, vacuum and reclaiming space, fsm

2003-10-13 Thread Shridhar Daithankar
On Monday 13 October 2003 19:22, Seum-Lim Gan wrote: I am not sure I can do the full vacuum. If my system is doing updates in realtime and needs to be ok 24 hours and 7 days a week non-stop, once I do vacuum full, even on that table, that table will get locked out and any quiery or updates

Re: [PERFORM] go for a script! / ex: PostgreSQL vs. MySQL

2003-10-13 Thread Shridhar Daithankar
On Monday 13 October 2003 19:34, Vivek Khera wrote: SC == Sean Chittenden [EMAIL PROTECTED] writes: echo effective_cache_size = $((`sysctl -n vfs.hibufspace` / 8192)) I've used it for my dedicated servers. Is this calculation correct? SC Yes, or it's real close at least.

Re: [PERFORM] vacuum locking

2003-10-17 Thread Shridhar Daithankar
Rob Nagler wrote: It seems a simple vacuum (not full or analyze) slows down the database dramatically. I am running vacuum every 15 minutes, but it takes about 5 minutes to run even after a fresh import. Even with vacuuming every 15 minutes, I'm not sure vacuuming is working properly. There are

Re: [PERFORM] PostgreSQL data on a NAS device ?

2003-10-20 Thread Shridhar Daithankar
Alexander Priem wrote: About clustering: I know this can't be done by hooking multiple postmasters to one and the same NAS. This would result in data corruption, i've read... Only if they are reading same data directory. You can run 4 different data installations of postgresql, each one in its

Re: [PERFORM] Performance weirdness with/without vacuum analyze

2003-10-21 Thread Shridhar Daithankar
Harry Broomhall wrote: #effective_cache_size = 1000# typically 8KB each #random_page_cost = 4 # units are one sequential page fetch cost You must tune the first one at least. Try http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html to tune these parameters. 2) The EXPLAIN

Re: [PERFORM] PostgreSQL data on a NAS device ?

2003-10-21 Thread Shridhar Daithankar
Alexander Priem wrote: Dell PowerEdge 1750 machine with Intel Xeon CPU at 3 GHz and 4 GB of RAM. This machine will contain a PERC4/Di RAID controller with 128MB of battery backed cache memory. The O/S and logfiles will be placed on a RAID-1 setup of two 36Gb SCSI-U320 drives (15.000rpm). Database

Re: [PERFORM] Processors vs Memory

2003-10-22 Thread Shridhar Daithankar
Hilary Forbes wrote: If I have a fixed amount of money to spend as a general rule is it better to buy one processor and lots of memory or two processors and less memory for a system which is transactional based (in this case it's handling reservations). I realise the answer will be a

Re: [PERFORM] slow select

2003-10-26 Thread Shridhar Daithankar
Vivek Khera wrote: JB == Josh Berkus [EMAIL PROTECTED] writes: JB Actually, what OS's can't use all idle ram for kernel cache? I JB should note that in my performance docs FreeBSD. Limited by the value of sysctl vfs.hibufspace from what I understand. This value is set at boot based on

Re: [PERFORM] Various performance questions

2003-10-27 Thread Shridhar Daithankar
Dror Matalon wrote: On Mon, Oct 27, 2003 at 01:04:49AM -0500, Christopher Browne wrote: Most of the time involves: a) Reading each page of the table, and b) Figuring out which records on those pages are still live. The table has been VACUUM ANALYZED so that there are no dead records. It's

Re: [PERFORM] Optimizing Performance

2003-10-27 Thread Shridhar Daithankar
Kamalraj Singh Madhan wrote: Hi, I'am having major performance issues with post gre 7.3.1 db. Kindly suggest all the possible means by which i can optimize the performance of this database. If not all, some ideas (even if they are common) are also welcome. There is no optimisation done to

Re: [PERFORM] Pg+Linux swap use

2003-10-31 Thread Shridhar Daithankar
Jeff wrote: On Thu, 30 Oct 2003 17:49:08 -0200 (BRST) alexandre :: aldeia digital [EMAIL PROTECTED] wrote: Both use: Only postgresql on server. Buffers = 8192, effective cache = 10 Well, I'm assuming you meant 1GB of ram, not 1MB :) Check a ps auxw to see what is running. Perhaps X is

Re: [PERFORM] Pg+Linux swap use

2003-10-31 Thread Shridhar Daithankar
Jeff wrote: On Fri, 31 Oct 2003 09:31:19 -0600 Rob Sell [EMAIL PROTECTED] wrote: Not being one to hijack threads, but I haven't heard of this performance hit when using HT, I have what should all rights be a pretty fast server, dual 2.4 Xeons with HT 205gb raid 5 array, 1 gig of memory. And it

Re: [PERFORM] Value of Quad vs. Dual Processor machine

2003-11-12 Thread Shridhar Daithankar
Fred Moyer wrote: One thing I learned after spending about a week comparing the Athlon (2 ghz, 333 mhz frontside bus) and Xeon (2.4 ghz, 266 mhz frontside bus) platforms was that on average the select queries I was benchmarking ran 30% faster on the Athlon (this was with data cached in memory so

Re: [PERFORM] Help with count(*)

2003-11-14 Thread Shridhar Daithankar
On Friday 14 November 2003 12:51, Rajesh Kumar Mallah wrote: Hi , my database seems to be taking too long for a select count(*) i think there are lot of dead rows. I do a vacuum full it improves bu again the performance drops in a short while , can anyone please tell me if anything worng

Re: [PERFORM] Join on incompatible types

2003-11-18 Thread Shridhar Daithankar
Laurent Martelli wrote: scott == scott marlowe [EMAIL PROTECTED] writes: [...] scott Note here: scott Merge Join (cost=1788.68..4735.71 rows=1 width=85) (actual scott time=597.540..1340.526 rows=20153 loops=1) Merge Cond: scott (outer.id = inner.id) scott This estimate is WAY off.

Re: [PERFORM] More detail on settings for pgavd?

2003-11-18 Thread Shridhar Daithankar
Josh Berkus wrote: Shridhar, I was looking at the -V/-v and -A/-a settings in pgavd, and really don't understand how the calculation works. According to the readme, if I set -v to 1000 and -V to 2 (the defaults) for a table with 10,000 rows, pgavd would only vacuum after 21,000 rows had

Re: [PERFORM] High Processor consumption

2003-11-18 Thread Shridhar Daithankar
Benjamin Bostow wrote: I am running RH 7.3 running Apache 1.3.27-2 and PostgreSQL 7.2.3-5.73. When having 100+ users connected to my server I notice that postmaster consumes up wards of 90% of the processor and I hardly am higher than 10% idle. I did notice that when I kill apache and postmaster

Re: [PERFORM] Join on incompatible types

2003-11-19 Thread Shridhar Daithankar
Laurent Martelli wrote: Shridhar == Shridhar Daithankar [EMAIL PROTECTED] writes: Shridhar Laurent Martelli wrote: [...] Should I understand that a join on incompatible types (such as integer and varchar) may lead to bad performances ? Shridhar Conversely, you should enforce strict

Re: [PERFORM] Join on incompatible types

2003-11-19 Thread Shridhar Daithankar
Laurent Martelli wrote: Shridhar == Shridhar Daithankar [EMAIL PROTECTED] writes: Shridhar I am stripping the analyze outputs and directly jumping to Shridhar the end. Shridhar Can you try following? Shridhar 1. Make all fields integer in all the table. I can't do this because

Re: [PERFORM] Join on incompatible types

2003-11-19 Thread Shridhar Daithankar
Laurent Martelli wrote: Shridhar == Shridhar Daithankar [EMAIL PROTECTED] writes: [...] Shridhar 2. Try following query EXPLAIN ANALYZE SELECT * from lists Shridhar join classes on classes.id=lists.value where Shridhar lists.id='16'::integer; Shridhar classes.id=lists.value::integer

Re: [PERFORM] More detail on settings for pgavd?

2003-11-19 Thread Shridhar Daithankar
Josh Berkus wrote: Shridhar, I was looking at the -V/-v and -A/-a settings in pgavd, and really don't understand how the calculation works. According to the readme, if I set -v to 1000 and -V to 2 (the defaults) for a table with 10,000 rows, pgavd would only vacuum after 21,000 rows had

Re: [PERFORM] High Processor consumption

2003-11-19 Thread Shridhar Daithankar
Benjamin Bostow wrote: I haven't modified any of the setting. I did try changing shmmax from 32MB to 256MB but didn't see much change in the processor usage. The init script that runs to start the server uses the following: su -l postgres -s /bin/sh -c /usr/bin/pg_ctl -D $PGDATA -p

Re: [PERFORM] More detail on settings for pgavd?

2003-11-20 Thread Shridhar Daithankar
Josh Berkus wrote: Shridhar, However I do not agree with this logic entirely. It pegs the next vacuum w.r.t current table size which is not always a good thing. No, I think the logic's fine, it's the numbers which are wrong. We want to vacuum when updates reach between 5% and 15% of total

Re: [HACKERS] [PERFORM] More detail on settings for pgavd?

2003-11-20 Thread Shridhar Daithankar
On Thursday 20 November 2003 20:00, Matthew T. O'Connor wrote: Shridhar Daithankar wrote: I will submit a patch that would account deletes in analyze threshold. Since you want to delay the analyze, I would calculate analyze count as deletes are already accounted for in the analyze threshold

Re: [HACKERS] [PERFORM] More detail on settings for pgavd?

2003-11-20 Thread Shridhar Daithankar
On Thursday 20 November 2003 20:29, Shridhar Daithankar wrote: On Thursday 20 November 2003 20:00, Matthew T. O'Connor wrote: Shridhar Daithankar wrote: I will submit a patch that would account deletes in analyze threshold. Since you want to delay the analyze, I would calculate analyze

Re: [HACKERS] [PERFORM] More detail on settings for pgavd?

2003-11-21 Thread Shridhar Daithankar
Matthew T. O'Connor wrote: But we track tuples because we can compare against the count given by the stats system. I don't know of a way (other than looking at the FSM, or contrib/pgstattuple ) to see how many dead pages exist. I think making pg_autovacuum dependent of pgstattuple is very good

Re: [PERFORM] Maximum Possible Insert Performance?

2003-11-23 Thread Shridhar Daithankar
William Yu wrote: My situation is this. We have a semi-production server where we pre-process data and then upload the finished data to our production servers. We need the fastest possible write performance. Having the DB go corrupt due to power loss/OS crash is acceptable because we can

Re: [PERFORM] Optimize

2003-11-25 Thread Shridhar Daithankar
Torsten Schulz wrote: Chester Kustarz wrote: On Mon, 24 Nov 2003, Torsten Schulz wrote: shared_buffers = 5000# 2*max_connections, min 16 that looks pretty small. that would only be 40MBytes (8k/page * 5000pages). http://www.varlena.com/GeneralBits/Tidbits/perf.html Ok, thats it. I've

Re: [PERFORM] Slow UPADTE, compared to INSERT

2003-12-05 Thread Shridhar Daithankar
Ivar Zarans wrote: On Fri, Dec 05, 2003 at 06:19:46PM +0530, Shridhar Daithankar wrote: is correct SQL, but not correct, considering PostgreSQL bugs. Personally I don't consider a bug but anyways.. You are the one facing problem so I understand.. Well, if this is not bug, then what

Re: [PERFORM] Performance problems with a higher number of clients

2003-12-10 Thread Shridhar Daithankar
Alfranio Correia Junior wrote: Postgresql configuration: effective_cache_size = 35000 shared_buffers = 5000 random_page_cost = 2 cpu_index_tuple_cost = 0.0005 sort_mem = 10240 Lower sort mem to say 2000-3000, up shared buffers to 10K and up effective cache size to around 65K. That should make it

Re: [PERFORM] Why is restored database faster?

2003-12-16 Thread Shridhar Daithankar
Neil Conway wrote: How can I get the original server to perform as well as the new one? Well, you have the answer. Dump the database, stop postmaster and restore it. That should be faster than original one. (BTW, SELECT count(*) FROM table isn't a particularly good DBMS performance

Re: [PERFORM] Why is restored database faster?

2003-12-17 Thread Shridhar Daithankar
On Thursday 18 December 2003 09:24, David Shadovitz wrote: Old server: # VACUUM FULL abc; VACUUM # VACUUM VERBOSE abc; NOTICE: --Relation abc-- NOTICE: Pages 1526: Changed 0, Empty 0; Tup 91528; Vac 0, Keep 0, UnUsed 32. Total CPU 0.07s/0.52u sec elapsed 0.60 sec. VACUUM New

  1   2   >