Re: [PERFORM] Help tracking down problem with inserts slowing

[Steve Wampler]

On Sun, 2003-12-07 at 09:52, Tom Lane wrote:
 Steve Wampler [EMAIL PROTECTED] writes:
  Hmmm, I have a feeling that's not as obvious as I thought...  I can't
  identify the index (named 'id_index') in the output of vacuum verbose.
 
 In 7.2, the index reports look like
   Index %s: Pages %u; Tuples %.0f.
 and should appear in the part of the printout that deals with their
 owning table.

Thanks, Tom.  Are there any reasons why it would not appear?:
-
farm.devel.configdb=# vacuum verbose attributes_table;
NOTICE:  --Relation attributes_table--
NOTICE:  Pages 1389: Changed 0, Empty 0; Tup 111358: Vac 0, Keep 0,
UnUsed 51.
Total CPU 0.00s/0.02u sec elapsed 0.03 sec.
NOTICE:  --Relation pg_toast_1743942--
NOTICE:  Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

farm.devel.configdb=# \d attributes_table
 Table attributes_table
 Column |   Type   |   Modifiers   
+--+---
 id | character varying(64)| not null
 name   | character varying(64)| not null
 units  | character varying(32)| 
 value  | text | 
 time   | timestamp with time zone | default now()
Indexes: id_index
Primary key: attributes_table_pkey
Triggers: trigger_insert
---

The odd thing is that I could have sworn it appeared yesterday...

-- 
Steve Wampler -- [EMAIL PROTECTED]
The gods that smiled on your birth are now laughing out loud.

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [PERFORM] tuning questions

[Jack Coates]

On Fri, 2003-12-05 at 17:22, Jack Coates wrote:
...
 That's it, I'm throwing out this whole test series and starting over
 with different hardware. Database server is now a dual 2GHz Xeon with
 2GB RAM  2940UW SCSI, OS and PG's logs on 36G drive, PG data on 9GB
 drive. Data is importing now and I'll restart the tests tonight.

Sorry to reply at myself, but thought I'd note that the performance is
practically unchanged by moving to better hardware and separating logs
and data onto different spindles. Although the disks are twice as fast
by hdparm -Tt, their behavior as shown by iostat and vmstat is little
different between dual and dev (single P4-2GHz/512MB/(2)IDE drives).
Dual is moderately faster than my first, IDE-based testbed (about 8%),
but still only 30% as fast as the low-powered dev.

I've been running vacuumdb --analyze and/or vaccuumdb --full between
each config change, and I also let the job run all weekend. Saturday it
got --analyze every three hours or so, Sunday it got --analyze once in
the morning. None of these vacuumdb's are making any difference.

Theories at this point, in no particular order:

a) major differences between my 7.3.4 from source (compiled with no
options) and dev's 7.3.2-1PGDG RPMs. Looking at the spec file doesn't
reveal anything glaring to me, but is there something I'm missing?

b) major differences between my kernel 2.4.18-14smp (RH8) and dev's
kernel 2.4.18-3 (RH7.3).

c) phase of the moon.

While SQL optimization is likely to improve performance across the
board, it doesn't explain the differences between these two systems and
I'd like to avoid it as a theory until the fast box can perform as well
as the slow box.

Any ideas? Thanks in advance,
-- 
Jack Coates, Lyris Technologies Applications Engineer
510-549-4350 x148, [EMAIL PROTECTED]
Interoperability is the keyword, uniformity is a dead end.
--Olivier Fourdan



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])