1LOG_DB_DAEMON(8) User Contributed Perl Documentation LOG_DB_DAEMON(8)
2
6 log_db_daemon - Database logging daemon for Squid
7 Version 0.5.
9
11 log_db_daemon DSN [options]
12
14 This program writes Squid access.log entries to a database. Presently
15 only accepts the squid native log format.
16 The script has been developed and tested in the following environment:
18 squid-2.7 Squid-3.2
20 mysql 5.0.26 and 5.1
21 perl 5.8.8
22 OpenSUSE 10.2
23
25 DSN Database DSN encoded as a path. This is sent as the
26 access_log file path.
27 Sample configuration:
29 access_log daemon:/host/database/table/username/password
30 squid
31 to leave a parameter unspecified use a double slash:
33 access_log daemon://database/table/username/password squid
34 Default "DBI:mysql:database=squid"
36 --debug Write debug info to stderr.
38
40 Squid configuration
41 access_log directive
42 The path to the access log file is used to provide the database
44 connection parameters.
45 access_log daemon:/mysql_host:port/database/table/username/password squid
47 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 The last parameter tells squid which log format to use when writing
52 lines to the log daemon. Presently squid format is supported.
53 mysql_host:port
55 Host where the mysql server is running. If left empty, 'localhost'
56 is assumed.
57 database
59 Name of the database to connect to. If left empty, 'squid_log' is
60 assumed.
61 table
63 Name of the database table where log lines are stored. If left
64 empty, 'access_log' is assumed.
65 username
67 Username to use when connecting to the database. If left empty,
68 'squid' is assumed.
69 password
71 Password to use when connecting to the database. If left empty, no
72 password is used.
73 To leave all fields to their default values, you can use a single
75 slash:
76 access_log daemon:/ squid
78 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 access_log daemon://///password squid
83 logfile_daemon directive
85 This is the current way of telling squid where the logfile daemon
87 resides.
88 logfile_daemon /path/to/squid/libexec/logfile-daemon_mysql.pl
90 The script must be copied to the location specified in the directive.
92 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 Database
98 Create the database:
100 CREATE DATABASE squid_log;
102 User
104 Create the user:
106 GRANT INSERT,SELECT,CREATE ON squid_log.* TO 'squid'@'localhost' IDENTIFIED BY 'squid';
108 FLUSH PRIVILEGES;
109 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 Table
115 The Daemon will attempt to initialize this table if none exists when it
117 starts.
118 The table created should look like:
120 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
138 Sample queries.
139 Clients accessing the cache
140 SELECT DISTINCT ip_client FROM access_log;
141 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 Request status count
151 To obtain the raw count of each request status:
152 SELECT squid_request_status, COUNT(*) AS n
154 FROM access_log
155 GROUP BY squid_request_status
156 ORDER BY 2 DESC;
157 To calculate the percentage of each request status:
159 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 To distinguish only between HITs and MISSes:
168 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 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 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 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
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 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 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 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 Similar cleanup code could be implemented in an external script and run
250 periodically independently from squid log commands.
251 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
258 This program was written by Marcello Romani <marcello.romani@libero.it>
259 , Amos Jeffries <amosjeffries@squid-cache.org>
260
262 * Copyright (C) 1996-2021 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 Copyright (C) 2008 by Marcello Romani
269 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
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
279 Bug reports need to be made in English. See
280 http://wiki.squid-cache.org/SquidFaq/BugReporting for details of what
281 you need to include with your bug report.
282 Report bugs or bug fixes using http://bugs.squid-cache.org/
284 Report serious security bugs to Squid Bugs
286 <squid-bugs@lists.squid-cache.org>
287 Report ideas for new improvements to the Squid Developers mailing list
289 <squid-dev@lists.squid-cache.org>
290
292 squid (8), GPL (7),
293 The Squid FAQ wiki http://wiki.squid-cache.org/SquidFaq
295 The Squid Configuration Manual http://www.squid-cache.org/Doc/config/
297perl v5.34.0 2021-10-05 LOG_DB_DAEMON(8)