> Hi Merlin,
> > 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.
hm. well, it may be possible to do this in a fast and safe way but I
understand your reservations here, but I'm going to spout off my opinion
anyways :).
If you are not doing this the following point is moot. But take into
consideration you could set a very low transaction time out (like .25
seconds) and siphon log entries off to a text file if your database
server gets in trouble. 2 million hits a month is not very high even if
your traffic is bursty (there are approx 2.5 million seconds in a
month).
With a direct linked log file you get up to date stats always and spare
yourself the dump/load song and dance which is always a headache :(.
Also, however you are doing your billing, it will be easier to manage it
if everything is extracted from pg and not some conglomeration of log
files, *if* you can put 100% faith in your database. When it comes to
pg now, I'm a believer.
> > You could write small C program which executes advanced query
interface
> > call to the server.
>
> How would that improve performance?
The functions I'm talking about are PQexecParams and PQexecPrepared.
The query string does not need to be encoded or decoded and is very
light on server resources and is very low latency. Using them you could
get prob. 5000 inserts/sec on a cheap server if you have some type of
write caching in place with low cpu load.
Merlin