Re: performance with triggers depends on table size? - Mailing list pgsql-general

On Wed, Aug 14, 2002 at 09:35:31AM -0400, Tom Lane wrote:

> Interesting ... I'd have to guess that there is some external influence
> causing that.  Do you by any chance have a cron jon set up to do
> "vacuum"s periodically, and if so do the vacuums correspond to the
> points where the insertion time drops?

I can't seem to see any external influences; there is a once-a-night
vacuum run from cron, which fits with one of the sawtooth "drops",
but it still doesn't explain the other teeth. (And I checked the
postgres log for signs of other vaccum runs, there are none).

The database, and the machine it's running on, is otherwise quiescent.

After an afternoon of playing around, I found I can keep the performance
up (at least for 50000 rows) if I do

  BEGIN;
  INSERT INTO ac_event SELECT * FROM in_event WHERE eid < x AND eid > y;
  COMMIT;
  VACUUM ANALYZE event_stats;

in a tight loop, where the number of rows selected by x and y is very
small -- about 100 or less (and this is with the script not running
on the DB machine).

What I also noticed on verbose vacuums on the event_stats table is that
there's always a relatively big number of "Deleted" tuples, which has
some correlation to the number of rows copied in the previous insert;
interestingly, if x and y are 1000 apart, but there's "holes" in
in_event and fewer rows get copied, "Deleted" is less than the number
of rows copied, but with 1000 rows copied, it is at 1000 straight.

Where's the big number of "Deleted" tuples coming from? The relevant part
of the trigger function is below, the "DELETE" code path should never be
chosen (and from watching the sequence for the stat_id column, which
is completely in line with "count(*) from event_stats", it never is). Is
the update (on the non-indexed column "count" only) causing this?

I hope one of you gurus can make something out of this data...

regards,

cm.

  x_hour := date_trunc(''hour'', $5);
  SELECT INTO result stat_id, count FROM event_stats
   WHERE sensor_id = x_sensor_id AND ip_src = x_ip_src AND ip_dst = x_ip_dst
   AND sig_id = x_sig_id AND hour = x_hour AND type = x_type;
  IF NOT FOUND THEN
   INSERT INTO event_stats (sensor_id,ip_src,ip_dst,sig_id,hour,type,count)
    VALUES(x_sensor_id,x_ip_src,x_ip_dst,x_sig_id,x_hour,x_type,x_count);
  ELSE
   UPDATE event_stats SET count = count + x_count
    WHERE stat_id = result.stat_id;
   IF (result.count + x_count) = 0 THEN
    DELETE FROM event_stats WHERE stat_id = result.stat_id;
   END IF;
  END IF;

--
Christian Mock                          Wiedner Hauptstrasse 15
Senior Security Engineer                1040 Wien
CoreTEC IT Security Solutions GmbH      +43-1-5037273