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

SYNOPSIS

9       log_db_daemon DSN [options]
10

DESCRIPTION

12       This program writes Squid access.log entries to a database.  Presently
13       only accepts the squid native format
14
15       DSN     Database DSN encoded as a path. This is sent as the access_log
16               file path.
17
18               Sample configuration:
19                 access_log daemon:/host/database/table/username/password
20               squid
21
22                 to leave a parameter unspecified use a double slash:
23                 access_log daemon://database/table/username/password squid
24
25               Default "DBI:mysql:database=squid"
26
27       --debug Write debug messages to Squid stderr or cache.log
28

DESCRIPTION

30       This module exploits the new logfile daemon support available in squid
31       2.7 and 3.2 to store access log entries in a MySQL database.
32

CONFIGURATION

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

VERSION INFORMATION

132       This document refers to "log_db_daemon" script version 0.5.
133
134       The script has been developed and tested in the following environment:
135
136       squid-2.7 Squid-3.2
137       mysql 5.0.26 and 5.1
138       perl 5.8.8
139       OpenSUSE 10.2
140

DATA EXTRACTION

142   Sample queries.
143       Clients accessing the cache
144             SELECT DISTINCT ip_client FROM access_log;
145
146       Number of request per day
147             SELECT
148               DATE(FROM_UNIXTIME(time_since_epoch)) AS date_day,
149               COUNT(*) AS num_of_requests
150             FROM access_log
151             GROUP BY 1
152             ORDER BY 1;
153
154       Request status count
155           To obtain the raw count of each request status:
156
157             SELECT squid_request_status, COUNT(*) AS n
158             FROM access_log
159             GROUP BY squid_request_status
160             ORDER BY 2 DESC;
161
162           To calculate the percentage of each request status:
163
164             SELECT
165               squid_request_status,
166               (COUNT(*)/(SELECT COUNT(*) FROM access_log)*100) AS percentage
167             FROM access_log
168             GROUP BY squid_request_status
169             ORDER BY 2 DESC;
170
171           To distinguish only between HITs and MISSes:
172
173             SELECT
174               'hits',
175               (SELECT COUNT(*)
176               FROM access_log
177               WHERE squid_request_status LIKE '%HIT%')
178               /
179               (SELECT COUNT(*) FROM access_log)*100
180               AS percentage
181             UNION
182             SELECT
183               'misses',
184               (SELECT COUNT(*)
185               FROM access_log
186               WHERE squid_request_status LIKE '%MISS%')
187               /
188               (SELECT COUNT(*) FROM access_log)*100
189               AS percentage;
190
191       Response time ranges
192             SELECT
193               '0..500',
194               COUNT(*)/(SELECT COUNT(*) FROM access_log)*100 AS percentage
195             FROM access_log
196             WHERE time_response >= 0 AND time_response < 500
197             UNION
198             SELECT
199               '500..1000',
200               COUNT(*)/(SELECT COUNT(*) FROM access_log)*100 AS percentage
201             FROM access_log
202             WHERE time_response >= 500 AND time_response < 1000
203             UNION
204             SELECT
205               '1000..2000',
206               COUNT(*)/(SELECT COUNT(*) FROM access_log)*100 AS percentage
207             FROM access_log
208             WHERE time_response >= 1000 AND time_response < 2000
209             UNION
210             SELECT
211               '>= 2000',
212               COUNT(*)/(SELECT COUNT(*) FROM access_log)*100 AS percentage
213             FROM access_log
214             WHERE time_response >= 2000;
215
216       Traffic by mime type
217             SELECT
218               http_mime_type,
219               SUM(http_reply_size) as total_bytes
220             FROM access_log
221             GROUP BY http_mime_type
222             ORDER BY 2 DESC;
223
224       Traffic by client
225             SELECT
226               ip_client,
227               SUM(http_reply_size) AS total_bytes
228             FROM access_log
229             GROUP BY 1
230             ORDER BY 2 DESC;
231
232   Speed issues
233       The MyISAM storage engine is known to be faster than the InnoDB one, so
234       although it doesn't support transactions and referential integrity, it
235       might be more appropriate in this scenario. You might want to append
236       "ENGINE=MYISAM" at the end of the table creation code in the above SQL
237       script.
238
239       Indexes should be created according to the queries that are more
240       frequently run. The DDL script only creates an implicit index for the
241       primary key column.
242

TODO

244   Table cleanup
245       This script currently implements only the "L" (i.e. "append a line to
246       the log") command, therefore the log lines are never purged from the
247       table. This approach has an obvious scalability problem.
248
249       One solution would be to implement e.g. the "rotate log" command in a
250       way that would calculate some summary values, put them in a "summary
251       table" and then delete the lines used to calculate those values.
252
253       Similar cleanup code could be implemented in an external script and run
254       periodically independently from squid log commands.
255
256   Testing
257       This script has only been tested in low-volume scenarios (single
258       client, less than 10 req/s). Tests in high volume environments could
259       reveal performance bottlenecks and bugs.
260

AUTHOR

262       Marcello Romani, marcello.romani@libero.it Amos Jeffries,
263       amosjeffries@squid-cache.org
264
266        * Copyright (C) 1996-2016 The Squid Software Foundation and contributors
267        *
268        * Squid software is distributed under GPLv2+ license and includes
269        * contributions from numerous individuals and organizations.
270        * Please see the COPYING and CONTRIBUTORS files for details.
271
272       Copyright (C) 2008 by Marcello Romani
273
274       This library is free software; you can redistribute it and/or modify it
275       under the same terms as Perl itself, either Perl version 5.8.8 or, at
276       your option, any later version of Perl 5 you may have available.
277
278
279
280perl v5.16.3                      2019-01-29                  LOG_DB_DAEMON(8)
Impressum