I'm using sqlite to do some analysis on very large web application log
files - approaching 3 million lines per day. And what a wonderful tool
it is! It has saved me from writing lots of custom scripts.
I have a perl script that parses an rsynced copy of this huge log file,
munges, converts, and adds new entries to my database.
The schema currently looks something like this - some of the columns
are there as a aid to development/debugging so I can see what's
going on:
CREATE TABLE 'log' (
ip VARCHAR,
datetimestring VARCHAR COLLATE NOCASE,
uid VARCHAR COLLATE NOCASE,
timestamp INTEGER,
operation VARCHAR COLLATE NOCASE,
target VARCHAR,
response INTEGER,
client VARCHAR COLLATE NOCASE,
completion VARCHAR COLLATE NOCASE,
port INTEGER,
connections INTEGER,
date INTEGER,
hour INTEGER,
min INTEGER,
sec INTEGER
)
I've been experimenting with indices as far as when the tradeoff in
size and speed of adding data is offset sufficiently by how fast I
need to get the results. For testing purposes, I created indexes
for all the columns used in the selects below.
Most of the queries I've done so far have been pretty straightforward
and it's worked very well. But, now I need to do one that's taking
too long. There's probably a better way than the one I'm using.
The problem is that I need to produce information for the timeslots
that are missing as well as those that are there.
For example, I need to be able to evaluate the effect of different
load balancers, so I want to compare the numbers of transactions on
all the ports. I need to do so on a second by second basis so I can
see the most detail and not an average.
I thought one way do do this would be with a self-join because
there are so many transactions that I'm pretty sure that all seconds
will be present in the table. I also considered creating a second
table derived from the first whenever it's updated that would have a
unix epoch entry for each second within the log file.
Here's what I tried for the first approach:
select
distinct t.timestamp,
t.port,
count(u.timestamp)
from
log t
left join
log u
on
t.timestamp = u.timestamp
and
t.timestamp >= 1246266000
and u.uid != "-"
and (u.response = 207
or u.response = 200)
and u.port = 8444;
The purpose of the join is to give me all of the timestamps - one for
each second - even those seconds that had no activity on that port -
and then use those for the query. But this query is taking a very,
very, very long time. I'm probably making some newbie mistake,
because that's what I am!
Is this a valid approach? Is there a better solution?
I am able to get results extremely quickly by using something like
this:
select
date,
hour,
min,
sec,
count(port)
from log
where
uid != "-"
and (response = 207
or response = 200)
and port = 8444
group by
timestamp
order by
timestamp;
but the problem is that I also need to know when the ports are not
busy and there are no log entries. Here are the last few lines of the
result:
2009-06-29|17|42|0|2
2009-06-29|17|42|7|1
2009-06-29|17|42|8|4
What I need to have in the results are entries for seconds 1-6 with a
value of 0, like this:
2009-06-29|17|42|0|2
2009-06-29|17|42|1|0
2009-06-29|17|42|2|0
2009-06-29|17|42|3|0
2009-06-29|17|42|4|0
2009-06-29|17|42|5|0
2009-06-29|17|42|6|0
2009-06-29|17|42|7|1
2009-06-29|17|42|8|4
Am I on the right path with the self-join? I also considered adding a
new table to the database that contained all the possible time values
in epoch seconds - I could easily do so with the script that creates
the database and adds the data. I could then left join this table
with the data table. But would doing so be substantially faster than
the self-join?
The examples above are for only one port - I need to do the same for
16 ports. I wrote a perl script to do queries for each second, but it was
taking about 1 minute to do queries for the 16 ports for a 1 second
interval - way too slow to be used to produce results for 24 hour periods.
Thanks for any advice! I learn a lot from this list.
Leo
--
View this message in context:
http://www.nabble.com/3-million-rows%2C-query-speeds%2C-and-returning-zero-for-rows-that-don%27t-exist-tp24299431p24299431.html
Sent from the SQLite mailing list archive at Nabble.com.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
