Re: [PERFORM] Version 7 question

2003-07-01 Thread scott.marlowe
On Tue, 1 Jul 2003, Michael Mattox wrote: My understanding is to use as much effect cache as possible, so figure out how much ram you need for your other applications OS and then give the rest to postgres as effective cache. What I learned to day is the shared_buffers 25% of RAM guideline.

Re: [PERFORM] Moving postgresql.conf tunables into 2003...

2003-07-03 Thread scott.marlowe
On Thu, 3 Jul 2003, Sean Chittenden wrote: What are the odds of going through and revamping some of the tunables in postgresql.conf for the 7.4 release? I was just working with someone on IRC and on their 7800 RPM IDE drives, their random_page_cost was ideally suited to be 0.32: a far cry

Re: [PERFORM] Extreme high load averages

2003-07-07 Thread scott.marlowe
On Sun, 6 Jul 2003, Martin Foster wrote: Shridhar Daithankar wrote: It gives hint to psotgresql how much file system cache is available in the system. You have 1GB memory and your application requirement does not exceed 400MB. So OS can use roughly 600MB for file system cache.

Re: [PERFORM] PostgreSQL vs. MySQL

2003-07-07 Thread scott.marlowe
On Mon, 7 Jul 2003, Brian Tarbox wrote: Oddly enough, the particular application in question will have an extremely small user base...perhaps a few simultainous users at most. As to the testing, I neglected to say early in this thread that my manager instructed me _not_ to do further

Re: [PERFORM] [pgsql-advocacy] About the default performance

2003-07-07 Thread scott.marlowe
I'm willing to help too. I'm basically a DBA / developer type, with mild C hacking skills (I develop in PHP, so my C coding is quite rusty nowadays.) If nothing else testing on different equipment / OSes. On Fri, 4 Jul 2003, Josh Berkus wrote: Kaarel: (cross-posted back to Performance

Re: [PERFORM] Extreme high load averages

2003-07-08 Thread scott.marlowe
On Mon, 7 Jul 2003, Matthew Nuzum wrote: A common problem is a table like this: create table test (info text, id int8 primary key); insert into test values ('ted',1); .. a few thousand more inserts; vacuum full; analyze; select * from test where id=1; will result in a seq

Re: [PERFORM] Moving postgresql.conf tunables into 2003...

2003-07-09 Thread scott.marlowe
On Wed, 9 Jul 2003, Kaarel wrote: Are you willing to say that the PostgreSQL database system should only be used by DBAs? I believe that Postgres is such a good and useful tool that anyone should be able to start using it with little or no barrier to entry. I quite agree. But there

Re: [PERFORM] index / sequential scan problem

2003-07-18 Thread scott.marlowe
On Fri, 18 Jul 2003, Tom Lane wrote: =?ISO-8859-1?Q?Dennis_Bj=F6rklund?= [EMAIL PROTECTED] writes: On Fri, 18 Jul 2003, Fabian Kreitner wrote: Adjusting the cpu_tuple_cost to 0.042 got the planner to choose the index. Doesn't sound very good and it will most likely make other queries

Re: [PERFORM] Sanity check requested

2003-07-18 Thread scott.marlowe
On Fri, 18 Jul 2003, Ang Chin Han wrote: Shridhar Daithankar wrote: 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

Re: [PERFORM] Tuning PostgreSQL

2003-07-22 Thread scott.marlowe
On Tue, 22 Jul 2003, Jim C. Nasby wrote: On Tue, Jul 22, 2003 at 03:27:20PM +0200, Alexander Priem wrote: Wow, I never figured how many different RAID configurations one could think of :) After reading lots of material, forums and of course, this mailing-list, I think I am going for

Re: [PERFORM] Mapping Database completly into memory

2003-07-28 Thread scott.marlowe
On Sun, 27 Jul 2003, Daniel Migowski wrote: Hallo pgsql-performance, I just wondered if there is a possibility to map my database running on a linux system completly into memory and to only use disk accesses for writes. I got a nice machine around with 2 gigs of ram, and my database at

Re: [PERFORM] Tuning PostgreSQL

2003-07-29 Thread scott.marlowe
On 29 Jul 2003, Ron Johnson wrote: On Tue, 2003-07-29 at 10:14, Vivek Khera wrote: GS == Greg Stark [EMAIL PROTECTED] writes: GS scott.marlowe [EMAIL PROTECTED] writes: GS But you have to actually test your setup in practice to see if it GS hurts. A big data warehousing system

Re: [PERFORM] Tuning PostgreSQL

2003-07-29 Thread scott.marlowe
On 29 Jul 2003, Ron Johnson wrote: On Tue, 2003-07-29 at 11:18, scott.marlowe wrote: On 29 Jul 2003, Ron Johnson wrote: On Tue, 2003-07-29 at 10:14, Vivek Khera wrote: GS == Greg Stark [EMAIL PROTECTED] writes: GS scott.marlowe [EMAIL PROTECTED] writes: GS But you

Re: [PERFORM] Tuning PostgreSQL

2003-07-29 Thread scott.marlowe
On 29 Jul 2003, Ron Johnson wrote: On Tue, 2003-07-29 at 14:00, scott.marlowe wrote: On 29 Jul 2003, Ron Johnson wrote: On Tue, 2003-07-29 at 11:18, scott.marlowe wrote: On 29 Jul 2003, Ron Johnson wrote: On Tue, 2003-07-29 at 10:14, Vivek Khera wrote: GS == Greg

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

2003-08-04 Thread scott.marlowe
On Mon, 4 Aug 2003, 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, | effective_cache_size | 1000 With 4GB of memory

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

2003-08-04 Thread scott.marlowe
On Mon, 4 Aug 2003, 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, | effective_cache_size | 1000 With 4GB of memory

Re: [osdldbt-general] Re: [PERFORM] OSDL Database Test Suite 3 is

2003-08-04 Thread scott.marlowe
On 4 Aug 2003, Jenny Zhang wrote: On Mon, 2003-08-04 at 06:33, Manfred Koizar wrote: | effective_cache_size | 1000 With 4GB of memory this is definitely too low and *can* (note that I don't say *must*) lead the planner to wrong decisions. I changed the default to

Re: [PERFORM] PostgreSQL performance problem - tuning

2003-08-11 Thread scott.marlowe
On Thu, 7 Aug 2003, Yaroslav Mazurak wrote: Hi All! Richard Huxton wrote: On Wednesday 06 August 2003 08:34, Yaroslav Mazurak wrote: sort_mem = 131072 This sort_mem value is *very* large - that's 131MB for *each sort* that It's not TOO large *for PostgreSQL*.

Re: [PERFORM] Perfomance Tuning

2003-08-14 Thread scott.marlowe
On Fri, 8 Aug 2003, 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. Nothing has to be done to tune postgresql to handle this, 8 Meg blobs are no problem as far as I

Re: [PERFORM] How can I Improve performance in Solaris?

2003-08-14 Thread scott.marlowe
On Wed, 13 Aug 2003, ingrid martinez wrote: Floes table looks like this Table flows Column| Type | Modifiers --+--+--- flidload | bigint | not null

Re: [PERFORM] Perfomance Tuning

2003-08-14 Thread scott.marlowe
On 11 Aug 2003, 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 feature

Re: [PERFORM] How to efficiently duplicate a whole schema?

2003-08-14 Thread scott.marlowe
On Wed, 6 Aug 2003, Tom Lane wrote: scott.marlowe [EMAIL PROTECTED] writes: On Wed, 6 Aug 2003, Tom Lane wrote: One obvious question is whether you have your foreign keys set up efficiently in the first place. As a rule, the referenced and referencing columns should have identical

Re: [PERFORM] Perfomance Tuning

2003-08-14 Thread scott.marlowe
On Fri, 8 Aug 2003, Andrew Sullivan wrote: On Fri, Aug 08, 2003 at 09:40:20AM -0700, Jonathan Gardner wrote: Redhat puts ext3 on by default. Consider switching to a non-journaling FS (ext2?) with the partition that holds your data and WAL. I would give you exactly the opposite advice:

Re: [PERFORM] partitioning for postgresql

2003-08-14 Thread scott.marlowe
On Wed, 6 Aug 2003, Wilson A. Galafassi Jr. wrote: hello!!! what is suggested partitioning schema for postgresql?? the size of my db is 5BG and i have 36GB scsi disk! The first recommendation is to run Postgresql on a RAID set for reliability. I'm assuming you're building a machine and

Re: [PERFORM] How can I Improve performance in Solaris?

2003-08-14 Thread scott.marlowe
More than likely you are suffering from an affliction known as type mismatch. This is listed as tip 9 here on the performance list (funny, it was sent at the bottom of your reply :-) What happens is that when you do: select * from some_table where id=123; where id is a bigint the query

Re: [PERFORM] PostgreSQL performance problem - tuning

2003-08-14 Thread scott.marlowe
On Thu, 7 Aug 2003, Yaroslav Mazurak wrote: scott.marlowe wrote: On Thu, 7 Aug 2003, Yaroslav Mazurak wrote: Shridhar Daithankar wrote: That's a nice theory, but it doesn't work out that way. About every two months someone shows up wanting postgresql to use all the memory

Re: [PERFORM] How Many Inserts Per Transactions

2003-08-14 Thread scott.marlowe
On Tue, 5 Aug 2003, Trevor Astrope wrote: I was wondering if anyone found a sweet spot regarding how many inserts to do in a single transaction to get the best performance? Is there an approximate number where there isn't any more performance to be had or performance may drop off? It's

Re: [PERFORM] How to efficiently duplicate a whole schema?

2003-08-14 Thread scott.marlowe
On Wed, 6 Aug 2003, Tom Lane wrote: Sebastien Lemieux [EMAIL PROTECTED] writes: All the time is taken at the commit of both transaction. Sounds like the culprit is foreign-key checks. One obvious question is whether you have your foreign keys set up efficiently in the first place. As a

Re: [PERFORM] Perfomance Tuning

2003-08-14 Thread scott.marlowe
On Tue, 12 Aug 2003, Neil Conway wrote: On Tue, Aug 12, 2003 at 12:52:46AM -0400, Bruce Momjian wrote: I don't use Linux and was just repeating what I had heard from others, and read in postings. I don't have any first-hand experience with ext2 (except for a laptop I borrowed that

Re: [PERFORM] Perfomance Tuning

2003-08-14 Thread scott.marlowe
On Tue, 12 Aug 2003, 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 feature until it's been through a major version (e.g.

Re: [PERFORM] Perfomance Tuning

2003-08-14 Thread scott.marlowe
On Mon, 11 Aug 2003, Bruce Momjian wrote: scott.marlowe wrote: On Fri, 8 Aug 2003, Andrew Sullivan wrote: On Fri, Aug 08, 2003 at 09:40:20AM -0700, Jonathan Gardner wrote: Redhat puts ext3 on by default. Consider switching to a non-journaling FS (ext2?) with the partition

Re: [PERFORM] Tests

2003-08-25 Thread scott.marlowe
On Fri, 22 Aug 2003, Josh Berkus wrote: Also another test I'd really like to see is hardware raid (Adaptec, LSI) against Linux software raid, and 5-disk RAID 5 against 4-disk RAID 1+0. It would be nice to cross those, so you have RAID5 sw vs RAID5 hw, vs RAID1+0 sw vs RAID1+0 hw. Now if

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

2003-08-28 Thread scott.marlowe
On 27 Aug 2003, matt wrote: I'm wondering if the good people out there could perhaps give me some pointers on suitable hardware to solve an upcoming performance issue. I've never really dealt with these kinds of loads before, so any experience you guys have would be invaluable. Apologies in

Re: [PERFORM] Best tweak for fast results.. ?

2003-08-28 Thread scott.marlowe
On Tue, 26 Aug 2003, JM wrote: need input on parameter values on confs... our database is getting 1000 transactions/sec on peak periods.. sitting on RH 7.3 2.4.7-10smp RAM: 1028400 SWAP: 2040244 1: Upgrade your kernel. 2.4.7 on RH3 was updated to 2.4.18-24 in March, and the 2.4.18

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

2003-08-28 Thread scott.marlowe
On 27 Aug 2003, matt wrote: You probably, more than anything, should look at some kind of superfast, external storage array Yeah, I think that's going to be a given. Low end EMC FibreChannel boxes can do around 20,000 IOs/sec, which is probably close to good enough. You mentioned

Re: [PERFORM] opinion on RAID choice

2003-08-28 Thread scott.marlowe
On Thu, 28 Aug 2003, Vivek Khera wrote: I just ran a handful of tests on a 14-disk array on a SCSI hardware RAID card. SNIP Has anyone else done similar tests of different RAID levels? What were your conclusions? Yes I have. I had a 6 disk array plus 2 disks inside my machine (this was

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

2003-08-28 Thread scott.marlowe
On Thu, 28 Aug 2003, Sean Chittenden wrote: What it still leaves quite open is just what happens when the OS has more than one disk drive or CPU to play with. It's not clear what happens in such cases, whether FreeBSD would catch up, or be left further in the dust. The traditional

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

2003-08-28 Thread scott.marlowe
Just to add to the clutch here, also check your bdflush settings (if you're on linux) or equivalent (if you're not.) Many times the swapping algo in linux can be quite bursty if you have it set to move too many pages at a time during cleanup / flush. ---(end of

Re: [PERFORM] opinion on RAID choice

2003-08-29 Thread scott.marlowe
On Thu, 28 Aug 2003, Vivek Khera wrote: sm == scott marlowe scott.marlowe writes: sm My experience has been that once you get past 6 disks, RAID5 is faster sm than RAID1+0. Any opinion on stripe size for the RAID? That's more determined by what kind of data you're gonna be handling

Re: [PERFORM] Selecting random rows efficiently

2003-09-03 Thread scott.marlowe
Can you just create an extra serial column and make sure that one is always in order and no holes in it? (i.e. a nightly process, etc...)??? If so, then something like this truly flies: select * from accounts where aid = (select cast(floor(random()*10)+1 as int)); My times on it on a

Re: [PERFORM] FreeBSD page size (was Re: The results of my

2003-09-04 Thread scott.marlowe
On Wed, 3 Sep 2003, Vivek Khera wrote: SC == Sean Chittenden [EMAIL PROTECTED] writes: I need to step in and do 2 things: SC Thanks for posting that. Let me know if you have any questions while SC doing your testing. I've found that using 16K blocks on FreeBSD SC results in about an 8%

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

2003-09-04 Thread scott.marlowe
On Wed, 3 Sep 2003, Relaxin wrote: I have a table with 102,384 records in it, each record is 934 bytes. Using the follow select statement: SELECT * from table PG Info: version 7.3.4 under cygwin on Windows 2000 ODBC: version 7.3.100 Machine: 500 Mhz/ 512MB RAM / IDE HDD Under

Re: [PERFORM] slow plan for min/max

2003-09-08 Thread scott.marlowe
On Sun, 7 Sep 2003, Pailloncy Jean-Gérard wrote: Asking a question about why max(id) is so much slower than select id order by id desc limit 1, Pailloncy said: I ask for the same thing. That's better ! This is a Frequently asked question about something that isn't likely to change any time

Re: [PERFORM] slow plan for min/max

2003-09-08 Thread scott.marlowe
On Mon, 8 Sep 2003, Neil Conway wrote: On Mon, 2003-09-08 at 11:56, scott.marlowe wrote: Basically, Postgresql uses an MVCC locking system that makes massively parallel operation possible, but costs in certain areas, and one of those areas is aggregate performance over large sets. MVCC

Re: [PERFORM] Need advice about triggers

2003-09-09 Thread scott.marlowe
On Tue, 9 Sep 2003, Mindaugas Riauba wrote: Hello, I have small table (up to 1 rows) and every row will be updated once per minute. Table also has before update on each row trigger written in plpgsql. But trigger 99.99% of the time will do nothing to the database. It will just

Re: [PERFORM] [GENERAL] how to get accurate values in pg_statistic

2003-09-11 Thread scott.marlowe
On Thu, 11 Sep 2003, Tom Lane wrote: Christopher Browne [EMAIL PROTECTED] writes: The right answer for most use seems likely to involve: a) Getting an appropriate number of bins (I suspect 10 is a bit small, but I can't justify that mathematically), and I suspect that also, but I

Re: [PERFORM] [GENERAL] how to get accurate values in pg_statistic

2003-09-12 Thread scott.marlowe
On Thu, 11 Sep 2003, Christopher Browne wrote: [EMAIL PROTECTED] (scott.marlowe) writes: On Thu, 11 Sep 2003, Tom Lane wrote: Christopher Browne [EMAIL PROTECTED] writes: The right answer for most use seems likely to involve: a) Getting an appropriate number of bins (I suspect 10

Re: [PERFORM] Inconsistent performance

2003-09-15 Thread scott.marlowe
On Mon, 15 Sep 2003, Joseph Bove wrote: Stephan, I've run explain analyze a number of times and have gotten results between 5.5 and 7.5 seconds Attached is a typical output QUERY PLAN - Aggregate (cost=9993.92..9993.92 rows=1 width=0)

Re: [PERFORM] Inconsistent performance

2003-09-15 Thread scott.marlowe
On Mon, 15 Sep 2003, scott.marlowe wrote: On Mon, 15 Sep 2003, Joseph Bove wrote: Stephan, I've run explain analyze a number of times and have gotten results between 5.5 and 7.5 seconds Attached is a typical output QUERY PLAN

Re: [PERFORM] Is there a reason _not_ to vacuum continuously?

2003-09-17 Thread scott.marlowe
On Wed, 17 Sep 2003, Matt Clark wrote: *** THE QUESTION(S) *** Is there any reason for me not to run continuous sequential vacuum analyzes? At least for the 6 tables that see a lot of updates? I hear 10% of tuples updated as a good time to vac-an, but does my typical count of 3 indexes per

Re: [PERFORM] advice on raid controller

2003-09-29 Thread scott.marlowe
I've used the megaraid / LSI cards in the past and they were pretty good in terms of reliability, but the last one I used was the 328 model, from 4 years ago or so. that one had a battery backup option for the cache, and could go to 128 Meg. We tested it with 4/16 and 128 meg ram, and it was

Re: [PERFORM] advice on raid controller

2003-09-29 Thread scott.marlowe
On 29 Sep 2003, Will LaShell wrote: On Mon, 2003-09-29 at 06:48, scott.marlowe wrote: I've used the megaraid / LSI cards in the past and they were pretty good in terms of reliability, but the last one I used was the 328 model, from 4 years ago or so. that one had a battery backup option

Re: [PERFORM] inferior SCSI performance

2003-10-01 Thread scott.marlowe
On Wed, 1 Oct 2003, Andrew Sullivan wrote: On Wed, Sep 17, 2003 at 04:46:00PM -0400, Michael Adler wrote: So the quesiton is whether it is ever sensible to use write-caching and expect comparable persistence. Yes. If and only if you have a battery-backed cache. I know of no IDE drives

Re: [PERFORM] TPC-R benchmarks

2003-10-01 Thread scott.marlowe
On Tue, 30 Sep 2003, Oleg Lebedev wrote: I continue struggling with the TPC-R benchmarks and wonder if anyone could help me optimize the query below. ANALYZE statistics indicate that the query should run relatively fast, but it takes hours to complete. I attached the query plan to this

Re: [PERFORM] Optimizing = and = for numbers and dates

2003-10-01 Thread scott.marlowe
On Wed, 1 Oct 2003, Dimitri Nagiev wrote: here goes the EXPLAIN ANALYZE output: template1=# VACUUM analyze mytable; VACUUM template1=# explain analyze select * from mytable where mydate='2003-09-01'; QUERY PLAN

Re: [PERFORM] Optimizing = and = for numbers and dates

2003-10-01 Thread scott.marlowe
Oh, to followup on my previously sent post, make sure you've got effective_cache_size set right BEFORE you go trying to set random_page_cost, and you might wanna run a select * from table to load the table into kernel buffer cache before testing, then also test it with the cache cleared out

Re: [PERFORM] TPC-R benchmarks

2003-10-01 Thread scott.marlowe
) Index Cond: (outer.ps_suppkey = supplier.s_suppkey) Total runtime: 6674724.23 msec (28 rows) -Original Message- From: Oleg Lebedev Sent: Wednesday, October 01, 2003 12:00 PM To: Josh Berkus; scott.marlowe Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] TPC-R

Re: [PERFORM] TPC-R benchmarks

2003-10-02 Thread scott.marlowe
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 to make this query run fast without hurting the performance of the other queries? Thanks. Oleg -Original Message- From: scott.marlowe [mailto:[EMAIL

[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] 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

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] Is This My Speed Limit?

2003-10-03 Thread scott.marlowe
On Thu, 2 Oct 2003, CN wrote: Hi! It's just my curiosity. I wonder if there is any way to break my speed limit on AMD 450Mhz: You're most likely I/O bound, not CPU bound here. So, if you want better speed, you'll likely need a better storage subsystem. ---(end of

Re: [PERFORM] Postgres low end processing.

2003-10-03 Thread scott.marlowe
On Fri, 3 Oct 2003, Ron Johnson wrote: On Fri, 2003-10-03 at 12:52, 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.

Re: [PERFORM] Shopping for hardware

2003-10-06 Thread scott.marlowe
On Mon, 6 Oct 2003, Jason Hihn wrote: Ok, I asked this on [novice], but I was told it's be better to post it here... I've got some money to spend on a new servers. The biggest concern is the PostgreSQL database server that will be the company. (*Everyone* uses the database server in some

Re: [PERFORM] Linux filesystem shootout

2003-10-09 Thread scott.marlowe
On Thu, 9 Oct 2003, Shridhar Daithankar wrote: 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

Re: [pgsql-advocacy] [PERFORM] OFFTOPIC: PostgreSQL vs MySQL

2003-10-09 Thread scott.marlowe
On Thu, 9 Oct 2003, Josh Berkus wrote: David Griffiths wrote: With regards to #1, I'd like to specifically mention tuning - the docs at http://www.postgresql.org/docs/7.3/static/runtime-config.html http://www.postgresql.org/docs/7.3/static/runtime-config.html give a Have you checked

Re: [PERFORM] PostgreSQL vs MySQL

2003-10-09 Thread scott.marlowe
On Thu, 9 Oct 2003, Jeff wrote: On Thu, 9 Oct 2003, David Griffiths wrote: 1) the MySQL docs are better (sorry - I found them easier to read, and more comprehensive; I had an easier time finding the answers I needed) Huh. I had the opposite experience. Each to his own. I think

Re: [PERFORM] further testing on IDE drives

2003-10-10 Thread scott.marlowe
Nope, write-cache enabled by default. On Thu, 9 Oct 2003, Bruce Momjian wrote: How did this drive come by default? Write-cache disabled? --- scott.marlowe wrote: On Thu, 2 Oct 2003, scott.marlowe wrote: I

Re: [PERFORM] further testing on IDE drives

2003-10-10 Thread scott.marlowe
On Thu, 9 Oct 2003, Bruce Momjian wrote: 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

Re: [PERFORM] further testing on IDE drives

2003-10-10 Thread scott.marlowe
On Fri, 10 Oct 2003, Josh Berkus wrote: Bruce, Yes. If you were doing multiple WAL writes before transaction fsync, you would be fsyncing every write, rather than doing two writes and fsync'ing them both. I wonder if larger transactions would find open_sync slower? Want me to

Re: [PERFORM] further testing on IDE drives

2003-10-10 Thread scott.marlowe
On Fri, 10 Oct 2003, Josh Berkus wrote: Bruce, Yes. If you were doing multiple WAL writes before transaction fsync, you would be fsyncing every write, rather than doing two writes and fsync'ing them both. I wonder if larger transactions would find open_sync slower? Want me to

Re: [PERFORM] further testing on IDE drives

2003-10-14 Thread scott.marlowe
On Tue, 14 Oct 2003, Tom Lane wrote: scott.marlowe [EMAIL PROTECTED] writes: open_sync was WAY faster at this than the other two methods. Do you not have open_datasync? That's the preferred method if available. Nope, when I try to start postgresql with it set to that, I get this error

Re: [SQL] [PERFORM] sql performance and cache

2003-10-14 Thread scott.marlowe
On Tue, 14 Oct 2003, Wei Weng wrote: On Sat, 11 Oct 2003, Christopher Kings-Lynne wrote: I have two very similar queries which I need to execute. They both have exactly the same from / where conditions. When I execute the first, it takes about 16 seconds. The second is executed

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

2003-10-20 Thread scott.marlowe
On Mon, 20 Oct 2003, Alexander Priem wrote: Hi all, Does anyone have any experience with putting PostgreSQL data on a NAS device? I am asking this because a NAS device is much cheaper to set up than a couple of SCSI disks. I would like to use a relatively cheap NAS device which uses

Re: [PERFORM] Tuning for mid-size server

2003-10-21 Thread scott.marlowe
On Tue, 21 Oct 2003, Josh Berkus wrote: Anjan, Pretty soon, a PowerEdge 6650 with 4 x 2Ghz XEONs, and 8GB Memory, with internal drives on RAID5 will be delivered. Postgres will be from RH8.0. How many drives? RAID5 sucks for heavy read-write databases, unless you have 5+ drives. Or

Re: [PERFORM] Tuning for mid-size server

2003-10-21 Thread scott.marlowe
On Tue, 21 Oct 2003, Josh Berkus wrote: Scott, Also, if it's a read only environment, RAID5 with n drives equals the performance of RAID0 with n-1 drives. True. Josh, you gotta get out more. IA32 has supported 4 gig ram for a long time now, and so has the linux kernel. It uses a

Re: [PERFORM] Tuning for mid-size server

2003-10-21 Thread scott.marlowe
On Tue, 21 Oct 2003, Josh Berkus wrote: Anjan, From what I know, there is a cache-row-set functionality that doesn't exist with the newer postgres... What? PostgreSQL has always used the kernel cache for queries. Concurrent users will start from 1 to a high of 5000 or more, and

[PERFORM] RAID controllers etc... was: PostgreSQL data on a NAS device ?

2003-10-21 Thread scott.marlowe
On 21 Oct 2003, Will LaShell wrote: On Tue, 2003-10-21 at 08:40, scott.marlowe wrote: SNIP So that brings up my question, which is better, the Perc4 or Perc3 controllers, and what's the difference between them? I find Dell's tendency to hide other people's hardware behind their own

Re: [PERFORM] Tuning for mid-size server

2003-10-21 Thread scott.marlowe
On Tue, 21 Oct 2003, Andrew Sullivan wrote: On Tue, Oct 21, 2003 at 11:51:02AM -0700, Josh Berkus wrote: Of course, if you have anecdotal evidence to the contrary, then the only way to work this would be to have OSDL help us sort it out. Nope. I too have such anecdotal evidence that

Re: [PERFORM] RAID controllers etc... was: PostgreSQL data on aNAS

2003-10-23 Thread scott.marlowe
On Thu, 23 Oct 2003, Alexander Priem wrote: I have been searching (www.lsil.com) for this megaraid_2 driver you mentioned. What kind of MegaRaid card does the Perc4/Di match? Elite1600? Elite1650? I picked Elite1600 and the latest driver I found was version 2.05.00. Is this one OK for

Re: [PERFORM] My own performance/tuning qa

2003-10-24 Thread scott.marlowe
On Fri, 24 Oct 2003, Vivek Khera wrote: AL == Allen Landsidel [EMAIL PROTECTED] writes: AL Well I had the vacuums running every 15 minutes for a while.. via a AL simple cron script I wrote just to make sure no more than one vacuum AL ran at once, and to 'nice' the job.. but performance on

Re: [PERFORM] PostgreSQL 7.4beta5 vs MySQL 4.0.16 with

2003-10-29 Thread scott.marlowe
On Thu, 30 Oct 2003 [EMAIL PROTECTED] wrote: So its not just PostgreSQL that is suffering from the bad SQL but MySQL also. But the question is my does PostgreSQL suffer so badly ?? I think not all developers write very nice SQLs. Its really sad to see that a fine peice of work

Re: [PERFORM] vacuum locking

2003-10-30 Thread scott.marlowe
On Wed, 29 Oct 2003, Rob Nagler wrote: Greg Stark writes: SELECT a, (SELECT name FROM t2 WHERE t2.f2 = t1.f2) FROM t1 GROUP BY f2 This doesn't solve the problem. It's the GROUP BY that is doing the wrong thing. It's grouping, then aggregating. But at least in the

Re: [PERFORM] vacuum locking

2003-10-30 Thread scott.marlowe
On Thu, 30 Oct 2003, Rob Nagler wrote: scott.marlowe writes: t2 was 'vacuum full'ed and analyzed, right? Just guessing. Fresh import. I've been told this includes a ANALYZE. You should probably run analyze by hand just to be sure. If the planner is using an index scan on a table

Re: [PERFORM] *very* slow query to summarize data for a month ...

2003-11-11 Thread scott.marlowe
On 11 Nov 2003, Greg Stark wrote: Marc G. Fournier [EMAIL PROTECTED] writes: On Tue, 11 Nov 2003, Greg Stark wrote: Actually you might be able to get the same effect using function indexes like: create index i on traffic_log (month_trunc(runtime), company_id) had actually

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

2003-11-11 Thread scott.marlowe
On Tue, 11 Nov 2003, Rod Taylor wrote: On Tue, 2003-11-11 at 18:32, Chris Field wrote: We are getting ready to spec out a new machine and are wondering about the wisdom of buying a quad versus a dual processor machine. Seing as how postgres in not a threaded application, and this server

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

2003-11-12 Thread scott.marlowe
As another post pointed out, you need to set cflags to get optimization under Solaris on that flavor of Postgresql. Also, Postgresql tends to get its best performance from the free unixes, Linux and BSD. those are available for Sun Sparcs, but postgresql in 64 bit mode on those boxes is still

Re: [PERFORM] tuning questions

2003-12-04 Thread scott.marlowe
On Thu, 4 Dec 2003, Jack Coates wrote: Another problem is that performance of the 6 million row job is decent if I stop the job and run a vacuumdb --analyze before letting it continue; is this something that 7.4 will help with? vacuumb --analyze doesn't seem to have much effect on the 18

Re: [PERFORM] tuning questions

2003-12-04 Thread scott.marlowe
On Thu, 4 Dec 2003, Josh Berkus wrote: Scott, Just to add to what the others have said here, you probably want to run the pg_autovacuum daemon in the background. It comes with 7.4 but will work fine with 7.3. I don't recommend using pg_autovacuum with a data transformation task.

Re: [PERFORM] tuning questions

2003-12-04 Thread scott.marlowe
On Thu, 4 Dec 2003, Jack Coates wrote: On Thu, 2003-12-04 at 12:27, Richard Huxton wrote: On Thursday 04 December 2003 19:50, Jack Coates wrote: I'm trying to set Postgres's shared memory usage in a fashion that allows it to return requested results quickly. Unfortunately, none of

Re: [PERFORM] [ADMIN] ODBC Driver generates a too big windows swap file and

2003-12-12 Thread scott.marlowe
On Fri, 12 Dec 2003, Rhaoni Chiu Pereira wrote: Hi, is there a switch in your pgsql/odbc connector to enable cursors? If so, try turning that on. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ?

Re: [PERFORM] a lot of problems with pg 7.4

2003-12-16 Thread scott.marlowe
On Sat, 13 Dec 2003, Kari Lavikka wrote: Hi! We have been running a rather busy website using pg 7.3 as the database. Peak hitrate is something like 120 request / second without images and other static stuff. The site is a sort of image gallery for IRC users. I evaluated pg 7.4 on our

Re: [PERFORM] general peformance question

2003-12-22 Thread scott.marlowe
On Thu, 18 Dec 2003, Conny Thimren wrote: Hi, This a kind of newbie-question. I've been using Postgres for a long time in a low transction environment - and it is great. Now I've got an inquiry for using Postgresql in a heavy-load on-line system. This system must handle something like 20

Re: [PERFORM] Index creation

2004-01-07 Thread scott.marlowe
On Wed, 7 Jan 2004, Eric Jain wrote: Any tips for speeding up index creation? I need to bulk load a large table with 100M rows and several indexes, some of which span two columns. By dropping all indexes prior to issuing the 'copy from' command, the operation completes 10x as fast (1.5h

Re: [PERFORM] COUNT Pagination

2004-01-13 Thread scott.marlowe
On Tue, 13 Jan 2004, David Shadovitz wrote: We avert the subsequent execution of count(*) by passing the value of count(*) as a query parameter through the link in page numbers. Mallah, and others who mentioned caching the record count: Yes, I will certainly do this. I can detect

Re: [PERFORM] 100 simultaneous connections, critical limit?

2004-01-14 Thread scott.marlowe
On Wed, 14 Jan 2004, Jón Ragnarsson wrote: I am writing a website that will probably have some traffic. Right now I wrap every .php page in pg_connect() and pg_close(). Then I read somewhere that Postgres only supports 100 simultaneous connections (default). Is that a limitation? Should I use

Re: [PERFORM] 100 simultaneous connections, critical limit?

2004-01-14 Thread scott.marlowe
On Wed, 14 Jan 2004, Adam Alkins wrote: scott.marlowe wrote: A few tips from an old PHP/Apache/Postgresql developer. 1: Avoid pg_pconnect unless you are certain you have load tested the system and it will behave properly. pg_pconnect often creates as many issues as it solves

Re: [PERFORM] COUNT Pagination

2004-01-14 Thread scott.marlowe
On Wed, 14 Jan 2004, Rajesh Kumar Mallah wrote: scott.marlowe wrote: On Tue, 13 Jan 2004, David Shadovitz wrote: We avert the subsequent execution of count(*) by passing the value of count(*) as a query parameter through the link in page numbers. Mallah, and others who

Re: [PERFORM] Overlaping indexes

2004-02-02 Thread scott.marlowe
On Mon, 2 Feb 2004, Tomasz Myrta wrote: Dnia 2004-02-02 15:46, U?ytkownik Rigmor Ukuhe napisa3: Hi, I have many indexes somehow overlaping like: ... btree (STATUS, VISIBLE, NP_ID); ... btree (STATUS, VISIBLE); is perfomance gained by more exact index worth overhead with managing

Re: [PERFORM] Overlaping indexes

2004-02-02 Thread scott.marlowe
On Mon, 2 Feb 2004, Tomasz Myrta wrote: Dnia 2004-02-02 19:30, U¿ytkownik scott.marlowe napisa³: Not entirely, since it only has to sort two columns, it will be smaller, and will therefore be somewhat faster. Can you say something more about it? Will it be enough faster to keep them

  1   2   >