From: | "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com> |
---|---|
To: | "Ulrich Wisser" <ulrich(dot)wisser(at)relevanttraffic(dot)se> |
Cc: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Need for speed 3 |
Date: | 2005-09-01 13:42:41 |
Message-ID: | 6EE64EF3AB31D5448D0007DD34EEB3417DD24C@Herge.rcsinc.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Ulrich wrote:
> Hi again,
>
> first I want to say ***THANK YOU*** for everyone who kindly shared
their
> thoughts on my hardware problems. I really appreciate it. I started to
> look for a new server and I am quite sure we'll get a serious hardware
> "update". As suggested by some people I would like now to look closer
at
> possible algorithmic improvements.
>
> My application basically imports Apache log files into a Postgres
> database. Every row in the log file gets imported in one of three (raw
> data) tables. My columns are exactly as in the log file. The import is
> run approx. every five minutes. We import about two million rows a
month.
>
> Between 30 and 50 users are using the reporting at the same time.
>
> Because reporting became so slow, I did create a reporting table. In
> that table data is aggregated by dropping time (date is preserved),
ip,
> referer, user-agent. And although it breaks normalization some data
from
> a master table is copied, so no joins are needed anymore.
>
> After every import the data from the current day is deleted from the
> reporting table and recalculated from the raw data table.
>
schemas would be helpful. You may be able to tweak the import table a
bit and how it moves over to the data tables.
Just a thought: have you considered having apache logs write to a
process that immediately makes insert query(s) to postgresql?
You could write small C program which executes advanced query interface
call to the server.
Merlin
From | Date | Subject | |
---|---|---|---|
Next Message | Ulrich Wisser | 2005-09-01 14:06:18 | Re: Need for speed 3 |
Previous Message | Ron | 2005-09-01 13:27:49 | Re: Poor performance on HP Package Cluster |