Re: [PERFORM] Help tracking down problem with inserts slowing
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] Help tracking down problem with inserts slowing
Steve Wampler <[EMAIL PROTECTED]> writes: > Thanks, Tom. Are there any reasons why it would not appear?: Oh, I shoulda read the code more carefully. I was looking at the bottom of lazy_scan_index, where the printout is done, and failed to notice the test at the top: /* * If the index is not partial, skip the scan, and just assume it has * the same number of tuples as the heap. */ So for ordinary indexes, nothing will appear unless vacuum has actual work to do (that is, it recycled at least one dead tuple in the table). Short answer: update or delete some row in the table, and then try vacuum verbose. Alternatively, you can just look at the pg_class row for the index. relpages and reltuples will contain the info you are after ... and they are certainly up to date at this point ;-) regards, tom lane ---(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] tuning questions
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])
Re: [PERFORM] autovacuum daemon stops doing work after about an hour
> "MTO" == Matthew T O'Connor <[EMAIL PROTECTED]> writes: MTO> Yeah, FreeBSD testing would have been nice, but I don't have access to MTO> any FreeBSD boxes so. FWIW, with the fflush() added after that sleep, and the fix to the long long computation of sleep time to keep it from overflowing, pg_autovacuum has been working flawlessly on my FreeBSD 4.9 + PG 7.4.0 production server. I'm just still playing with tuning pg_autovacuum to keep it from vacuuming my busy tables *too* often. Just a question: will my test program show negative sleep 'diff' on your linux box? I can't imagine that it would give different results than on freebsd. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] tuning questions
Jack Coates <[EMAIL PROTECTED]> writes: > 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? There are quite a few performance-related patches between 7.3.2 and 7.3.4. Most of them should be in 7.3.4's favor but there are some places where we had to take a performance hit in order to have a suitably low-risk fix for a bug. You haven't told us enough about the problem to know if any of those cases apply, though. AFAIR you have not actually showed either the slow query or EXPLAIN ANALYZE results for it on the two boxes ... regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
