Hello, I've thought it would be nice to index certain aspects of my
apache log files for analysis. I've used several different techniques
and have something usable now, but I'd like to tweak it one step
further.
My first performance optimization was to change the logformat into a
CSV format. I processed the logfiles with PHP and plsql stored
procedures. Unfortunately, it took more than 24 hours to process 1
days worth of log files.
I've now switched to using C# (using mono) to create hash-tables to do
almost all of the pre-processing. This has brought the time down to
about 3 hours. Actually, if I take out one step it brought the
process down to about 6 minutes, which is a tremendous improvement.
The one step that is adding 2.5+ hours to the job is not easily done
in C#, as far as I know.
Once the mostly-normalized data has been put into a table called
usage_raw_access I then use this query:
insert into usage_access select * ,
usage_normalize_session(accountid,client,atime) as sessionid
from usage_raw_access;
All it does is try to link pageviews together into a session.
here's the function:
create or replace function usage_normalize_session (varchar(12),
inet, timestamptz) returns integer as '
DECLARE
-- $1 = Account ID, $2 = IP Address, $3 = Time
RecordSet record;
BEGIN
SELECT INTO RecordSet DISTINCT sessionid FROM usage_access ua
WHERE ua.accountid = $1
AND ua.client = $2
AND ua.atime = ($3 - ''20 min''::interval)::timestamptz;
if found
then return RecordSet.sessionid;
end if;
return nextval(''usage_session_ids'');
END;'
language plpgsql;
And the table usage_access looks like this:
Table public.usage_access
Column| Type | Modifiers
-+--+---
[snip]
client | inet |
atime | timestamp with time zone |
accountid | character varying(12)|
sessionid | integer |
Indexes: usage_acccess_req_url btree (req_url),
usage_access_accountid btree (accountid),
usage_access_atime btree (atime),
usage_access_hostid btree (hostid),
usage_access_sessionid btree (sessionid)
usage_access_sessionlookup btree (accountid,client,atime);
As you can see, this looks for clients who have visited the same site
within 20 min. If there is no match, a unique sessionid is assigned
from a sequence. If there is a visit, the session id assigned to them
is used. I'm only able to process about 25 records per second with my
setup. My window to do this job is 3-4 hours and the shorter the
better.
Here is an explain analyze of the query I do (note I limited it to 1000):
EXPLAIN ANALYZE
insert into usage_access select * ,
usage_normalize_session(accountid,client,atime) as sessionid from
usage_raw_access limit 1000;
QUERY PLAN
--
Subquery Scan *SELECT* (cost=0.00..20.00 rows=1000 width=196)
(actual time=51.63..47634.22 rows=1000 loops=1)
- Limit (cost=0.00..20.00 rows=1000 width=196) (actual
time=51.59..47610.23 rows=1000 loops=1)
- Seq Scan on usage_raw_access (cost=0.00..20.00 rows=1000
width=196) (actual time=51.58..47606.14 rows=1001 loops=1)
Total runtime: 48980.54 msec
I also did an explain of the query that's performed inside the function:
EXPLAIN ANALYZE
select sessionid from usage_access ua where ua.accountid = 'XYZ' and
ua.client = '64.68.88.45'::inet and ua.atime = '2003-11-02
04:50:01-05'::timestamptz;
QUERY PLAN
--
Index Scan using usage_access_sessionlookup on usage_access ua
(cost=0.00..6.02 rows=1 width=4) (actual time=0.29..0.29 rows=0
loops=1)
Index Cond: ((accountid = 'XYZ'::character varying) AND (client =
'64.68.88.45'::inet) AND (atime = '2003-11-02 04:50:01-05'::timestamp
with time zone))
Total runtime: 0.35 msec
(3 rows)
What I'd really like to know is if someone knows a way to do any of
the following:
a: Make the INSERT into ... SELECT *,usage_access_sessionlookup().. work faster
b: Make the usage_access_sessionlookup() smarter,better,etc.
c: Do this in C# using a hash-table or some other procedure that
would be quicker.
d: Find an algorithm to create the sessionid without having to do any
database or hash-table lookups. As the dataset gets bigger, it won't
fit in RAM and the lookup queries will become I/O bound, drastically
slowing things down.
d: is my first choice.
For some reason I just can't seem to get my mind around the data. I
wonder if there's someway
