Re: Postgresql 8.0 beta 1 - strange cpu usage statistics and slow vacuuming

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Shelby Cain <alyandon@yahoo.com> writes:
> > From looking at vacuum.c I gathered
> vacuum_cost_delay
> > must be >0 to enable the feature - correct?
>
> Yeah, that's right --- delay=0 turns it off.  Weird.
>  Can anyone else
> reproduce the problem?
>

On a hunch, I turned fsync off and the performance
jumped up to around the same performance I was getting
running the cygwin 7.4.x port.  The system is a IBM
Netvista (P4 2.0 ghz w/256 megs of ram, IDE drive)
running Windows 2000 SP4.

I've attached a minimal test script that should
reproduce the issue.  However, please be aware that
I've yet to reproduce this issue on XP Pro SP 1
(p4-2.8G, 1g ram, IDE drive).  I'll try and see if I
can reproduce this on other systems and get back to
the list.

If anyone has access to Windows 2000 SP4 and wants to
run this test script I'd be very interested in hearing
your results.

Regards,

Shelby Cain



__________________________________
Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.
http://promotions.yahoo.com/new_mail drop table test1;
CREATE TABLE test1
(
  idnbr integer unique not null,
  num1 numeric(12,2),
  num2 numeric(12,2),
  text1 varchar(600)
);

create function fill_table(integer) returns integer as
'
DECLARE
  maxnum alias for $1;
  md5str text;
  startid integer;
BEGIN
  select coalesce(max(idnbr)+1,0) from test1 into startid;
  for i in 1..maxnum loop
      select substr(
    md5(random()) ||md5(random()) ||md5(random())
    ||md5(random()) ||md5(random()) ||md5(random())
    ||md5(random()) ||md5(random()) ||md5(random())
    ||md5(random()) ||md5(random()) ||md5(random())
    ||md5(random()) ||md5(random()) ||md5(random())
    ||md5(random()) ||md5(random()) ||md5(random()),
    1,(200+random()*400)::integer
    ) into md5str;
      insert into test1 values (startid+i,random()*10000, (random()*100)::integer, md5str);
  end loop;
  return maxnum;
END;
' language plpgsql;

truncate test1;
select fill_table(100000);
analyze test1;
update test1 set num1 = num1 + 0 where random() > 0.5;
-- These shouldn't take long at all for a table containing 100000 tuples + ~50000 dead tuples
vacuum verbose test1;
vacuum full verbose test1;