fix trigger_log_entries_aggr

[matyaskopp]

this trigger:

CREATE OR REPLACE FUNCTION trigger_log_entries_aggr()
RETURNS TRIGGER AS
$$
declare
  period RECORD;
  period_start TIMESTAMP;
  period_end TIMESTAMP;
  endpoint INTEGER;
begin
  IF tg_op='INSERT' THEN
    -- loop period levels
    FOR period IN
      SELECT unnest(enum_range(null::period_levels)) as period
    LOOP
      SELECT INTO period_start,period_end p.period_start, p.period_end
        FROM log_aggr_period(new.time_local, period.period::period_levels) p;
      FOR endpoint IN
        SELECT endpoint_id FROM user_endpoints WHERE ip = new.remote_addr AND is_active IS TRUE -- TODO test start_date
      LOOP
        PERFORM log_aggr_new_entry(period.period::period_levels, period_start, period_end, new.unit, endpoint, new.service_id, 1, new.body_bytes_sent);
        PERFORM log_aggr_new_entry(period.period::period_levels, period_start, period_end, new.unit, endpoint, NULL, 1, new.body_bytes_sent);
      END LOOP;
      PERFORM log_ip_aggr_new_entry(period.period::period_levels, period_start, period_end, new.unit, new.body_bytes_sent, new.remote_addr, new.service_id);
      PERFORM log_ip_aggr_new_entry(period.period::period_levels, period_start, period_end, new.unit, new.body_bytes_sent, new.remote_addr, NULL);
      PERFORM log_ip_aggr_new_entry(period.period::period_levels, period_start, period_end, new.unit, new.body_bytes_sent, NULL, new.service_id);
      PERFORM log_ip_aggr_new_entry(period.period::period_levels, period_start, period_end, new.unit, new.body_bytes_sent, NULL, NULL);
    END LOOP;

    RETURN new;
  ELSE
    RETURN null;
  END IF;
END;
$$
LANGUAGE plpgsql;

has been temporarily replaced with:

CREATE OR REPLACE FUNCTION trigger_log_entries_aggr()
RETURNS TRIGGER AS
$$
declare
  period RECORD;
  period_start TIMESTAMP;
  period_end TIMESTAMP;
  endpoint INTEGER;
begin
  IF tg_op='INSERT' THEN
    -- loop period levels
    FOR period IN
      SELECT unnest(enum_range(null::period_levels)) as period
    LOOP
      SELECT INTO period_start,period_end p.period_start, p.period_end
        FROM log_aggr_period(new.time_local, period.period::period_levels) p;
--      FOR endpoint IN
--        SELECT endpoint_id FROM user_endpoints WHERE ip = new.remote_addr AND is_active IS TRUE -- TODO test start_date
--      LOOP
--        PERFORM log_aggr_new_entry(period.period::period_levels, period_start, period_end, new.unit, endpoint, new.service_id, 1, new.body_bytes_sent);
--        PERFORM log_aggr_new_entry(period.period::period_levels, period_start, period_end, new.unit, endpoint, NULL, 1, new.body_bytes_sent);
--      END LOOP;
      PERFORM log_ip_aggr_new_entry(period.period::period_levels, period_start, period_end, new.unit, new.body_bytes_sent, new.remote_addr, new.service_id);
      PERFORM log_ip_aggr_new_entry(period.period::period_levels, period_start, period_end, new.unit, new.body_bytes_sent, new.remote_addr, NULL);
      PERFORM log_ip_aggr_new_entry(period.period::period_levels, period_start, period_end, new.unit, new.body_bytes_sent, NULL, new.service_id);
      PERFORM log_ip_aggr_new_entry(period.period::period_levels, period_start, period_end, new.unit, new.body_bytes_sent, NULL, NULL);
    END LOOP;

    RETURN new;
  ELSE
    RETURN null;
  END IF;
END;
$$
LANGUAGE plpgsql;

[matyaskopp]

This has no effect on the performance.
The loading log files performance is 10.5 valid records per second.

[matyaskopp]

Doing only hour period aggregation !!!

CREATE OR REPLACE FUNCTION trigger_log_entries_aggr()
RETURNS TRIGGER AS
$$
declare
  period RECORD;
  period_start TIMESTAMP;
  period_end TIMESTAMP;
  endpoint INTEGER;

begin
  IF tg_op='INSERT' THEN
    -- loop period levels
--    FOR period IN
--      SELECT unnest(enum_range(null::period_levels)) as period
--    LOOP
      SELECT INTO period_start,period_end p.period_start, p.period_end
--        FROM log_aggr_period(new.time_local, period.period::period_levels) p;
        FROM log_aggr_period(new.time_local, 'hour'::period_levels) p; -- hour period hardcoded
--      FOR endpoint IN
--        SELECT endpoint_id FROM user_endpoints WHERE ip = new.remote_addr AND is_active IS TRUE -- TODO test start_date
--      LOOP
--        PERFORM log_aggr_new_entry(period.period::period_levels, period_start, period_end, new.unit, endpoint, new.service_id, 1, new.body_bytes_sent);
--        PERFORM log_aggr_new_entry(period.period::period_levels, period_start, period_end, new.unit, endpoint, NULL, 1, new.body_bytes_sent);
--      END LOOP;
      -- PERFORM log_ip_aggr_new_entry(period.period::period_levels, period_start, period_end, new.unit, new.body_bytes_sent, new.remote_addr, new.service_id);
      PERFORM log_ip_aggr_new_entry('hour'::period_levels, period_start, period_end, new.unit, new.body_bytes_sent, new.remote_addr, new.service_id); -- hour period hardcoded
      -- PERFORM log_ip_aggr_new_entry(period.period::period_levels, period_start, period_end, new.unit, new.body_bytes_sent, new.remote_addr, NULL);
      PERFORM log_ip_aggr_new_entry('hour'::period_levels, period_start, period_end, new.unit, new.body_bytes_sent, new.remote_addr, NULL); -- hour period hardcoded
      -- PERFORM log_ip_aggr_new_entry(period.period::period_levels, period_start, period_end, new.unit, new.body_bytes_sent, NULL, new.service_id);
      PERFORM log_ip_aggr_new_entry('hour'::period_levels, period_start, period_end, new.unit, new.body_bytes_sent, NULL, new.service_id); -- hour period hardcoded
      -- PERFORM log_ip_aggr_new_entry(period.period::period_levels, period_start, period_end, new.unit, new.body_bytes_sent, NULL, NULL);
      PERFORM log_ip_aggr_new_entry('hour'::period_levels, period_start, period_end, new.unit, new.body_bytes_sent, NULL, NULL); -- hour period hardcoded
--    END LOOP;
    RETURN new;
  ELSE
    RETURN null;
  END IF;
END;
$$
LANGUAGE plpgsql;

[matyaskopp]

The result of #33 (comment) is

• 10times slower
• using 4 cores instead of one

[matyaskopp]

Aggregate only full pair (IP, service)

• approx 1.2 records/second (on 12 minutes runtime sample)
• 4 CPU cores used

CREATE OR REPLACE FUNCTION trigger_log_entries_aggr()
RETURNS TRIGGER AS
$$
declare
  period RECORD;
  period_start TIMESTAMP;
  period_end TIMESTAMP;
  endpoint INTEGER;

begin
  IF tg_op='INSERT' THEN
      SELECT INTO period_start,period_end p.period_start, p.period_end
        FROM log_aggr_period(new.time_local, 'hour'::period_levels) p; -- hour period hardcoded
      PERFORM log_ip_aggr_new_entry('hour'::period_levels, period_start, period_end, new.unit, new.body_bytes_sent, new.remote_addr, new.service_id); -- hour period hardcoded
    RETURN new;
  ELSE
    RETURN null;
  END IF;
END;
$$
LANGUAGE plpgsql;

[matyaskopp]

Temporarily dropped whole trigger -> no improvement

DROP TRIGGER log_files_lines_read_aggr ON log_file_entries RESTRICT;