pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedoptimized counting of web statistics
5+ messages / 3 participants
[nested] [flat]
* optimized counting of web statistics
@ 2005-06-28 20:39 Billy extyeightysix <exty86@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Billy extyeightysix @ 2005-06-28 20:39 UTC (permalink / raw)
To: pgsql-performance
Hola folks,
I have a web statistics Pg database (user agent, urls, referrer, etc)
that is part of an online web survey system. All of the data derived
from analyzing web server logs is stored in one large table with each
record representing an analyzed webserver log entry.
Currently all reports are generated when the logs are being analyzed
and before the data ever goes into the large table I mention above.
Well, the time has come to build an interface that will allow a user
to make ad-hoc queries against the stats and that is why I am emailing
the performance list.
I need to allow the user to specify any fields and values in a query.
For example,
"I want to see a report about all users from Germany that have flash
installed" or
"I want to see a report about all users from Africa that are using FireFox 1"
I do not believe that storing all of the data in one big table is the
correct way to go about this. So, I am asking for suggestions,
pointers and any kind of info that anyone can share on how to store
this data set in an optimized manner.
Also, I have created a prototype and done some testing using the
colossal table. Actually finding all of the rows that satisfy the
query is pretty fast and is not a problem. The bottleneck in the
whole process is actually counting each data point (how many times a
url was visited, or how many times a url referred the user to the
website). So more specifically I am wondering if there is way to store
and retrieve the data such that it speeds up the counting of the
statistics.
Lastly, this will become an open source tool that is akin to urchin,
awstats, etc. The difference is that this software is part of a suite
of tools for doing online web surveys and it maps web stats to the
survey respondent data. This can give web site managers a very clear
view of what type of people come to the site and how those types use
the site.
Thanks in advance,
exty
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: optimized counting of web statistics
@ 2005-06-28 20:43 Billy extyeightysix <exty86@gmail.com>
parent: Billy extyeightysix <exty86@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Billy extyeightysix @ 2005-06-28 20:43 UTC (permalink / raw)
To: pgsql-performance
> The bottleneck in the
> whole process is actually counting each data point (how many times a
> url was visited, or how many times a url referred the user to the
> website). So more specifically I am wondering if there is way to store
> and retrieve the data such that it speeds up the counting of the
> statistics.
This is misleading, the counting is being done by perl. so what is
happening is that I am locating all of the rows via a cursor and then
calculating the stats using perl hashes. no counting is being in the
DB. maybe it would be much faster to count in the db somehow?
exty
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: optimized counting of web statistics
@ 2005-06-28 21:55 Matthew Nuzum <mattnuzum@gmail.com>
parent: Billy extyeightysix <exty86@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Matthew Nuzum @ 2005-06-28 21:55 UTC (permalink / raw)
To: Billy extyeightysix <exty86@gmail.com>; +Cc: pgsql-performance
On 6/28/05, Billy extyeightysix <exty86@gmail.com> wrote:
> Hola folks,
>
> I have a web statistics Pg database (user agent, urls, referrer, etc)
> that is part of an online web survey system. All of the data derived
> from analyzing web server logs is stored in one large table with each
> record representing an analyzed webserver log entry.
>
> Currently all reports are generated when the logs are being analyzed
> and before the data ever goes into the large table I mention above.
> Well, the time has come to build an interface that will allow a user
> to make ad-hoc queries against the stats and that is why I am emailing
> the performance list.
Load your data into a big table, then pre-process into additional
tables that have data better organized for running your reports.
For example, you may want a table that shows a sum of all hits for
each site, for each hour of the day. You may want an additional table
that shows the sum of all page views, or maybe sessions for each site
for each hour of the day.
So, if you manage a single site, each day you will add 24 new records
to the sum table.
You may want the following fields:
site (string)
atime (timestamptz)
hour_of_day (int)
day_of_week (int)
total_hits (int8)
A record may look like this:
site | atime | hour_of_day | day_of_week | total_hits
'www.yoursite.com' '2005-06-28 16:00:00 -0400' 18 2 350
Index all of the fields except total_hits (unless you want a report
that shows all hours where hits were greater than x or less than x).
Doing:
select sum(total_hits) as total_hits from summary_table where atime
between now() and (now() - '7 days'::interval);
should be pretty fast.
You can also normalize your data such as referrers, user agents, etc
and create similar tables to the above.
In case you haven't guessed, I've already done this very thing.
I do my batch processing daily using a python script I've written. I
found that trying to do it with pl/pgsql took more than 24 hours to
process 24 hours worth of logs. I then used C# and in memory hash
tables to drop the time to 2 hours, but I couldn't get mono installed
on some of my older servers. Python proved the fastest and I can
process 24 hours worth of logs in about 15 minutes. Common reports run
in < 1 sec and custom reports run in < 15 seconds (usually).
--
Matthew Nuzum
www.bearfruit.org
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: optimized counting of web statistics
@ 2005-06-29 02:54 Matthew Nuzum <mattnuzum@gmail.com>
parent: Rudi Starcevic <tech@wildcash.com>
0 siblings, 0 replies; 5+ messages in thread
From: Matthew Nuzum @ 2005-06-29 02:54 UTC (permalink / raw)
To: Rudi Starcevic <tech@wildcash.com>; +Cc: pgsql-performance
On 6/29/05, Rudi Starcevic <tech@wildcash.com> wrote:
> Hi,
>
> >I do my batch processing daily using a python script I've written. I
> >found that trying to do it with pl/pgsql took more than 24 hours to
> >process 24 hours worth of logs. I then used C# and in memory hash
> >tables to drop the time to 2 hours, but I couldn't get mono installed
> >on some of my older servers. Python proved the fastest and I can
> >process 24 hours worth of logs in about 15 minutes. Common reports run
> >in < 1 sec and custom reports run in < 15 seconds (usually).
> >
> >
>
> When you say you do your batch processing in a Python script do you mean
> a you are using 'plpython' inside
> PostgreSQL or using Python to execut select statements and crunch the
> data 'outside' PostgreSQL?
>
> Your reply is very interesting.
Sorry for not making that clear... I don't use plpython, I'm using an
external python program that makes database connections, creates
dictionaries and does the normalization/batch processing in memory. It
then saves the changes to a textfile which is copied using psql.
I've tried many things and while this is RAM intensive, it is by far
the fastest aproach I've found. I've also modified the python program
to optionally use disk based dictionaries based on (I think) gdb. This
signfincantly increases the time to closer to 25 min. ;-) but drops
the memory usage by an order of magnitude.
To be fair to C# and .Net, I think that python and C# can do it
equally fast, but between the time of creating the C# version and the
python version I learned some new optimization techniques. I feel that
both are powerful languages. (To be fair to python, I can write the
dictionary lookup code in 25% (aprox) fewer lines than similar hash
table code in C#. I could go on but I think I'm starting to get off
topic.)
--
Matthew Nuzum
www.bearfruit.org
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: optimized counting of web statistics
@ 2005-06-29 17:17 Rudi Starcevic <tech@wildcash.com>
parent: Matthew Nuzum <mattnuzum@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Rudi Starcevic @ 2005-06-29 17:17 UTC (permalink / raw)
To: pgsql-performance
Hi,
>I do my batch processing daily using a python script I've written. I
>found that trying to do it with pl/pgsql took more than 24 hours to
>process 24 hours worth of logs. I then used C# and in memory hash
>tables to drop the time to 2 hours, but I couldn't get mono installed
>on some of my older servers. Python proved the fastest and I can
>process 24 hours worth of logs in about 15 minutes. Common reports run
>in < 1 sec and custom reports run in < 15 seconds (usually).
>
>
When you say you do your batch processing in a Python script do you mean
a you are using 'plpython' inside
PostgreSQL or using Python to execut select statements and crunch the
data 'outside' PostgreSQL?
Your reply is very interesting.
Thanks.
Regards,
Rudi.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-06-29 17:17 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-28 20:39 optimized counting of web statistics Billy extyeightysix <exty86@gmail.com>
2005-06-28 20:43 ` Billy extyeightysix <exty86@gmail.com>
2005-06-28 21:55 ` Matthew Nuzum <mattnuzum@gmail.com>
2005-06-29 17:17 ` Rudi Starcevic <tech@wildcash.com>
2005-06-29 02:54 ` Matthew Nuzum <mattnuzum@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox