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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Hi Merlin,

> schemas would be helpful.

right now I would like to know if my approach to the problem makes
sense. Or if I should rework the whole procedure of import and aggregate.

> Just a thought: have you considered having apache logs write to a
> process that immediately makes insert query(s) to postgresql?

Yes we have considered that, but dismissed the idea very soon. We need
Apache to be as responsive as possible. It's a two server setup with
load balancer and failover. Serving about ones thousand domains and
counting. It needs to be as failsafe as possible and under no
circumstances can any request be lost. (The click counting is core
business and relates directly to our income.)
That said it seemed quite save to let Apache write logfiles. And import
them later. By that a database downtime wouldn't be mission critical.


> You could write small C program which executes advanced query interface
> call to the server.

How would that improve performance?

Ulrich

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq