log_db_daemon(8)

1LOG_DB_DAEMON(8)      User Contributed Perl Documentation     LOG_DB_DAEMON(8)
2
3
4

NAME

6       log_db_daemon - Database logging daemon for Squid
7
8       Version 0.5.
9

SYNOPSIS

11       log_db_daemon DSN [options]
12

DESCRIPTION

14       This program writes Squid access.log entries to a database.  Presently
15       only accepts the squid native log format.
16
17       The script has been developed and tested in the following environment:
18
19       squid-2.7 Squid-3.2
20       mysql 5.0.26 and 5.1
21       perl 5.8.8
22       OpenSUSE 10.2
23

OPTIONS

25       DSN         Database DSN encoded as a path. This is sent as the
26                   access_log file path.
27
28                   Sample configuration:
29                     access_log daemon:/host/database/table/username/password
30                   squid
31
32                     to leave a parameter unspecified use a double slash:
33                     access_log daemon://database/table/username/password squid
34
35                   Default "DBI:mysql:database=squid"
36
37       --debug     Write debug info to stderr.
38

CONFIGURATION

40   Squid configuration
41       access_log directive
42
43       The path to the access log file is used to provide the database
44       connection parameters.
45
46         access_log daemon:/mysql_host:port/database/table/username/password squid
47
48       The 'daemon' prefix is mandatory and tells squid that the
49       logfile_daemon helper is to be used instead of the normal file logging.
50
51       The last parameter tells squid which log format to use when writing
52       lines to the log daemon.  Presently squid format is supported.
53
54       mysql_host:port
55           Host where the mysql server is running. If left empty, 'localhost'
56           is assumed.
57
58       database
59           Name of the database to connect to. If left empty, 'squid_log' is
60           assumed.
61
62       table
63           Name of the database table where log lines are stored. If left
64           empty, 'access_log' is assumed.
65
66       username
67           Username to use when connecting to the database. If left empty,
68           'squid' is assumed.
69
70       password
71           Password to use when connecting to the database. If left empty, no
72           password is used.
73
74       To leave all fields to their default values, you can use a single
75       slash:
76
77         access_log daemon:/ squid
78
79       To specify only the database password, which by default is empty, you
80       must leave unspecified all the other parameters by using null strings:
81
82         access_log daemon://///password squid
83
84       logfile_daemon directive
85
86       This is the current way of telling squid where the logfile daemon
87       resides.
88
89         logfile_daemon /path/to/squid/libexec/logfile-daemon_mysql.pl
90
91       The script must be copied to the location specified in the directive.
92
93   Database configuration
94       Let's call the database 'squid_log' and the log table 'access_log'. The
95       username and password for the db connection will be both 'squid'.
96
97       Database
98
99       Create the database:
100
101         CREATE DATABASE squid_log;
102
103       User
104
105       Create the user:
106
107         GRANT INSERT,SELECT,CREATE ON squid_log.* TO 'squid'@'localhost' IDENTIFIED BY 'squid';
108         FLUSH PRIVILEGES;
109
110       Note that only CREATE, INSERT and SELECT privileges are granted to the
111       'squid' user. This ensures that the logfile daemon script cannot change
112       or modify the log entries.
113
114       Table
115
116       The Daemon will attempt to initialize this table if none exists when it
117       starts.
118
119       The table created should look like:
120
121         CREATE TABLE access_log (
122           id                   INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
123           time_since_epoch     DECIMAL(15,3),
124           time_response        INTEGER,
125           ip_client            CHAR(15),
126           ip_server            CHAR(15),
127           http_status_code     VARCHAR(10),
128           http_reply_size      INTEGER,
129           http_method          VARCHAR(20),
130           http_url             TEXT,
131           http_username        VARCHAR(20),
132           http_mime_type       VARCHAR(50),
133           squid_hier_status    VARCHAR(20),
134           squid_request_status VARCHAR(20)
135         );
136

DATA EXTRACTION

138   Sample queries.
139       Clients accessing the cache
140             SELECT DISTINCT ip_client FROM access_log;
141
142       Number of request per day
143             SELECT
144               DATE(FROM_UNIXTIME(time_since_epoch)) AS date_day,
145               COUNT(*) AS num_of_requests
146             FROM access_log
147             GROUP BY 1
148             ORDER BY 1;
149
150       Request status count
151           To obtain the raw count of each request status:
152
153             SELECT squid_request_status, COUNT(*) AS n
154             FROM access_log
155             GROUP BY squid_request_status
156             ORDER BY 2 DESC;
157
158           To calculate the percentage of each request status:
159
160             SELECT
161               squid_request_status,
162               (COUNT(*)/(SELECT COUNT(*) FROM access_log)*100) AS percentage
163             FROM access_log
164             GROUP BY squid_request_status
165             ORDER BY 2 DESC;
166
167           To distinguish only between HITs and MISSes:
168
169             SELECT
170               'hits',
171               (SELECT COUNT(*)
172               FROM access_log
173               WHERE squid_request_status LIKE '%HIT%')
174               /
175               (SELECT COUNT(*) FROM access_log)*100
176               AS percentage
177             UNION
178             SELECT
179               'misses',
180               (SELECT COUNT(*)
181               FROM access_log
182               WHERE squid_request_status LIKE '%MISS%')
183               /
184               (SELECT COUNT(*) FROM access_log)*100
185               AS percentage;
186
187       Response time ranges
188             SELECT
189               '0..500',
190               COUNT(*)/(SELECT COUNT(*) FROM access_log)*100 AS percentage
191             FROM access_log
192             WHERE time_response >= 0 AND time_response < 500
193             UNION
194             SELECT
195               '500..1000',
196               COUNT(*)/(SELECT COUNT(*) FROM access_log)*100 AS percentage
197             FROM access_log
198             WHERE time_response >= 500 AND time_response < 1000
199             UNION
200             SELECT
201               '1000..2000',
202               COUNT(*)/(SELECT COUNT(*) FROM access_log)*100 AS percentage
203             FROM access_log
204             WHERE time_response >= 1000 AND time_response < 2000
205             UNION
206             SELECT
207               '>= 2000',
208               COUNT(*)/(SELECT COUNT(*) FROM access_log)*100 AS percentage
209             FROM access_log
210             WHERE time_response >= 2000;
211
212       Traffic by mime type
213             SELECT
214               http_mime_type,
215               SUM(http_reply_size) as total_bytes
216             FROM access_log
217             GROUP BY http_mime_type
218             ORDER BY 2 DESC;
219
220       Traffic by client
221             SELECT
222               ip_client,
223               SUM(http_reply_size) AS total_bytes
224             FROM access_log
225             GROUP BY 1
226             ORDER BY 2 DESC;
227

KNOWN ISSUES

229   Speed issues
230       The MyISAM storage engine is known to be faster than the InnoDB one, so
231       although it doesn't support transactions and referential integrity, it
232       might be more appropriate in this scenario. You might want to append
233       "ENGINE=MYISAM" at the end of the table creation code in the above SQL
234       script.
235
236       Indexes should be created according to the queries that are more
237       frequently run. The DDL script only creates an implicit index for the
238       primary key column.
239
240   Table cleanup
241       This script currently implements only the "L" (i.e. "append a line to
242       the log") command, therefore the log lines are never purged from the
243       table. This approach has an obvious scalability problem.
244
245       One solution would be to implement e.g. the "rotate log" command in a
246       way that would calculate some summary values, put them in a "summary
247       table" and then delete the lines used to calculate those values.
248
249       Similar cleanup code could be implemented in an external script and run
250       periodically independently from squid log commands.
251
252   Testing
253       This script has only been tested in low-volume scenarios (single
254       client, less than 10 req/s). Tests in high volume environments could
255       reveal performance bottlenecks and bugs.
256

AUTHOR

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

COPYRIGHT

262        * Copyright (C) 1996-2023 The Squid Software Foundation and contributors
263        *
264        * Squid software is distributed under GPLv2+ license and includes
265        * contributions from numerous individuals and organizations.
266        * Please see the COPYING and CONTRIBUTORS files for details.
267
268       Copyright (C) 2008 by Marcello Romani
269
270       This library is free software; you can redistribute it and/or modify it
271       under the same terms as Perl itself, either Perl version 5.8.8 or, at
272       your option, any later version of Perl 5 you may have available.
273

QUESTIONS

275       Questions on the usage of this program can be sent to the Squid Users
276       mailing list <squid-users@lists.squid-cache.org>
277

REPORTING BUGS

279       Bug reports need to be made in English.  See
280       https://wiki.squid-cache.org/SquidFaq/BugReporting for details of what
281       you need to include with your bug report.
282
283       Report bugs or bug fixes using https://bugs.squid-cache.org/
284
285       Report serious security bugs to Squid Bugs
286       <squid-bugs@lists.squid-cache.org>
287
288       Report ideas for new improvements to the Squid Developers mailing list
289       <squid-dev@lists.squid-cache.org>
290