LOG_DB_DAEMON(8)

📦
version 7.7 · 27 versions
, in arch rolling

💅 LOG_DB_DAEMON(8) — the log_db_daemon girlies' guide

okay so bestie, welcome to the log_db_daemon page 💖 this is part of the User Contributed Perl Documentation and it's literally the database logging daemon for Squid. we're in section 8 (sysadmin land, respect it). let's get into it ✨

🌸 NAME

log_db_daemon — Database logging daemon for Squid 🦋

Version 0.5. (yes, bestie, Version 0.5, she's young, she's learning, be nice)

✨ SYNOPSIS

log_db_daemon DSN [options]

💜 DESCRIPTION

So what does she do? This program writes Squid access.log entries into a database. Periodt. Presently it only accepts the squid native log format — that's the whole vibe, no other formats invited to the party 🙈

The script has been developed and tested in the following environment, no cap:

  • squid-2.7 Squid-3.2
  • mysql 5.0.26 and 5.1
  • perl 5.8.8
  • OpenSUSE 10.2

she's been around the block, lowkey iconic for it 👑

🎀 OPTIONS

  • DSN — the Database DSN, encoded as a path. the way this works is it gets sent as the access_log file path. iykyk.

Sample configuration:

access_log daemon:/host/database/table/username/password squid

and to leave a parameter unspecified, use a double slash:

access_log daemon://database/table/username/password squid

Default is "DBI:mysql:database=squid" 🫶

  • --debug — Write debug info to stderr. that's it, that's the flag. use it when things are being weird 💀

🦋 CONFIGURATION

🌈 Squid configuration

access_log directive

The path to the access log file is used to provide the database connection parameters. like, the path is the config, she's multi-talented:

access_log daemon:/mysql_host:port/database/table/username/password squid

The daemon prefix is mandatory (fr fr, do NOT skip it) and it tells squid that the logfile_daemon helper is to be used instead of the normal file logging.

The last parameter tells squid which log format to use when writing lines to the log daemon. Presently squid format is supported.

  • mysql_host:port — Host where the mysql server is running. If left empty, 'localhost' is assumed.
  • database — Name of the database to connect to. If left empty, 'squid_log' is assumed.
  • table — Name of the database table where log lines are stored. If left empty, 'access_log' is assumed.
  • username — Username to use when connecting to the database. If left empty, 'squid' is assumed.
  • password — Password to use when connecting to the database. If left empty, no password is used.

To leave all fields to their default values, you can use a single slash — slay, minimalism:

access_log daemon:/ squid

To specify only the database password, which by default is empty, you must leave unspecified all the other parameters by using null strings (look at all those slashes, she ate):

access_log daemon://///password squid

logfile_daemon directive

This is the current way of telling squid where the logfile daemon resides.

logfile_daemon /path/to/squid/libexec/logfile-daemon_mysql.pl

The script must be copied to the location specified in the directive. yes, actually copied, don't just manifest it 🧚

💖 Database configuration

Let's call the database 'squid_log' and the log table 'access_log'. The username and password for the db connection will be both 'squid'. cute, matching set 🎀

Database

Create the database:

CREATE DATABASE squid_log;

User

Create the user:

GRANT INSERT,SELECT,CREATE ON squid_log.* TO 'squid'@'localhost' IDENTIFIED BY 'squid';
FLUSH PRIVILEGES;
Note that only CREATE, INSERT and SELECT privileges are granted to the 'squid' user. This ensures that the logfile daemon script cannot change or modify the log entries. she can write and read but she can NOT rewrite history 💅

Table

The Daemon will attempt to initialize this table if none exists when it starts. self-sufficient queen 🌸

The table created should look like:

CREATE TABLE access_log (
  id                   INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  time_since_epoch     DECIMAL(15,3),
  time_response        INTEGER,
  ip_client            CHAR(15),
  ip_server            CHAR(15),
  http_status_code     VARCHAR(10),
  http_reply_size      INTEGER,
  http_method          VARCHAR(20),
  http_url              TEXT,
  http_username        VARCHAR(20),
  http_mime_type       VARCHAR(50),
  squid_hier_status    VARCHAR(20),
  squid_request_status VARCHAR(20)
);

okay wait, careful bestie — in the original it's http_url TEXT, with the alignment. copy it exactly from the original, don't trust my vibes 🙈

🔥 DATA EXTRACTION

⭐ Sample queries

💫 Clients accessing the cache

SELECT DISTINCT ip_client FROM access_log;

💫 Number of request per day

SELECT
  DATE(FROM_UNIXTIME(time_since_epoch)) AS date_day,
  COUNT(*) AS num_of_requests
FROM access_log
GROUP BY 1
ORDER BY 1;

💫 Request status count

To obtain the raw count of each request status:

SELECT squid_request_status, COUNT(*) AS n
FROM access_log
GROUP BY squid_request_status
ORDER BY 2 DESC;

To calculate the percentage of each request status:

SELECT
  squid_request_status,
  (COUNT(*)/(SELECT COUNT(*) FROM access_log)*100) AS percentage
FROM access_log
GROUP BY squid_request_status
ORDER BY 2 DESC;

To distinguish only between HITs and MISSes:

SELECT
  'hits',
  (SELECT COUNT(*)
  FROM access_log
  WHERE squid_request_status LIKE '%HIT%')
  /
  (SELECT COUNT(*) FROM access_log)*100
  AS percentage
UNION
SELECT
  'misses',
  (SELECT COUNT(*)
  FROM access_log
  WHERE squid_request_status LIKE '%MISS%')
  /
  (SELECT COUNT(*) FROM access_log)*100
  AS percentage;

💫 Response time ranges

SELECT
  '0..500',
  COUNT(*)/(SELECT COUNT(*) FROM access_log)*100 AS percentage
FROM access_log
WHERE time_response >= 0 AND time_response < 500
UNION
SELECT
  '500..1000',
  COUNT(*)/(SELECT COUNT(*) FROM access_log)*100 AS percentage
FROM access_log
WHERE time_response >= 500 AND time_response < 1000
UNION
SELECT
  '1000..2000',
  COUNT(*)/(SELECT COUNT(*) FROM access_log)*100 AS percentage
FROM access_log
WHERE time_response >= 1000 AND time_response < 2000
UNION
SELECT
  '>= 2000',
  COUNT(*)/(SELECT COUNT(*) FROM access_log)*100 AS percentage
FROM access_log
WHERE time_response >= 2000;

💫 Traffic by mime type

SELECT
  http_mime_type,
  SUM(http_reply_size) as total_bytes
FROM access_log
GROUP BY http_mime_type
ORDER BY 2 DESC;

💫 Traffic by client

SELECT
  ip_client,
  SUM(http_reply_size) AS total_bytes
FROM access_log
GROUP BY 1
ORDER BY 2 DESC;

💀 KNOWN ISSUES

🐌 Speed issues

The MyISAM storage engine is known to be faster than the InnoDB one, so although it doesn't support transactions and referential integrity, it might be more appropriate in this scenario. You might want to append "ENGINE=MYISAM" at the end of the table creation code in the above SQL script.

bestie, read that again: no transactions, no referential integrity. faster, but you're giving up safety nets. know what you're signing up for 💅

Indexes should be created according to the queries that are more frequently run. The DDL script only creates an implicit index for the primary key column. so like, if your queries are slow, that's a you problem, add your own indexes 🫶

🧹 Table cleanup

This script currently implements only the "L" (i.e. "append a line to the log") command, therefore the log lines are never purged from the table. This approach has an obvious scalability problem. yeah. she only appends. she does not delete. the table just grows forever. 😭

One solution would be to implement e.g. the "rotate log" command in a way that would calculate some summary values, put them in a "summary table" and then delete the lines used to calculate those values.

Similar cleanup code could be implemented in an external script and run periodically independently from squid log commands.

🧪 Testing

This script has only been tested in low-volume scenarios (single client, less than 10 req/s). Tests in high volume environments could reveal performance bottlenecks and bugs. so like... be aware. she's not battle-tested at scale, ngl 🙈

👑 AUTHOR

This program was written by Marcello Romani <marcello.romani@libero.it>, Amos Jeffries <amosjeffries@squid-cache.org>

 * Copyright (C) 1996-2026 The Squid Software Foundation and contributors
 *
 * Squid software is distributed under GPLv2+ license and includes
 * contributions from numerous individuals and organizations.
 * Please see the COPYING and CONTRIBUTORS files for details.

Copyright (C) 2008 by Marcello Romani

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.8.8 or, at your option, any later version of Perl 5 you may have available. 💖

🫶 QUESTIONS

Questions on the usage of this program can be sent to the Squid Users mailing list <squid-users@lists.squid-cache.org> — they're nice, go ask, don't suffer in silence ✨

🐛 REPORTING BUGS

Bug reports need to be made in English. See https://wiki.squid-cache.org/SquidFaq/BugReporting for details of what you need to include with your bug report.

Report bugs or bug fixes using https://bugs.squid-cache.org/

Report serious security bugs to Squid Bugs <squid-bugs@lists.squid-cache.org>

Report ideas for new improvements to the Squid Developers mailing list <squid-dev@lists.squid-cache.org>

🔥 security stuff goes to the bugs address, not the public tracker. fr fr, respect the process.

🌈 SEE ALSO

and that's the whole page bestie 🎀 go log some stuff, hydrate, and remember: she only appends, so clean your tables 💜

perl v5.42.2 · 2026-08-25 · LOG_DB_DAEMON(8) — mwah, that's the version, the date, and the manual name all in one cute sign-off 🦋✨

📦 where this page comes from