RE: getting Squid logfiles into a database

On Wed, 13 Jan 1999, Andrew S. Howell wrote:

> >>>>> "Williams" == Williams Jon <WilliamsJon@jdcorp.deere.com> writes:
>
> Williams> When we've looked at real-time logging to a database,
> Williams> the thing that kept us from doing it was the additional
> Williams> network overhead. Now, instead of having a request from
> Williams> the client, the request to the server, the response from
> Williams> the server, and the response to the client, we're also
> Williams> going to add the network traffic to send the log entry
> Williams> to a database server. You could get around this by
> Williams> putting the DB engine on the same box as the proxy, but
> Williams> then you've got the additional disk, CPU, and network
> Williams> overhead associated with the real-time ad-hoc queries.
>
> Depending on the platform, a couple of network cards could be fairly
> cheap, so you could just have a dedicated network for the log -> db
> traffic....

We've got 4 squid boxes, each having nice 100Mb cards on a 100Mb switch,
with the logging server also living on the switch with same ethernet card
type.

The logging server receives a per hour peak of roughly 880k entries,
and although we aren't using a single TCP connection to a database
(instead, it's a Perl program listening on UDP), I would imagine that
the overhead would be somewhat comparible because we do quite a bit of
sorting and classification of the data (the CPU on the logging box
since boot time is 56% idle on a P-133)

Both the squid boxes (which do a lot of ICP traffic) and the logging
machine are FreeBSD boxes, and the kernels have been compiled with lots
of extra network buffering - standard kernels were dropping a lot of
packets, but with the extra buffers, they all work very well:

udp:
        46140788 datagrams received
        0 with incomplete header
        0 with bad data length field
        0 with bad checksum
        14264 dropped due to no socket
        147731 broadcast/multicast datagrams dropped due to no socket
        261787 dropped due to full socket buffers
        0 not for hashed pcb
        45717006 delivered
        1768262 datagrams output
(up 15 days)

Cheers,

Chris Foote SE Net
Technical Manager 222 Grote Street
SE Network Access Adelaide SA 5000
e-mail chris@senet.com.au Australia
phone : (08) 8221 5221 PGP Public Key available from
fax: (08) 8221 5220 http://www.senet.com.au/PGP
support: (08) 8221 5792
Received on Tue Jan 12 1999 - 19:10:45 MST