1LOG_DB_DAEMON(1) User Contributed Perl Documentation LOG_DB_DAEMON(1)
2
6 log_db_daemon - Database logging daemon for Squid
7
9 log_db_daemon DSN [options]
10
12 This program writes Squid access.log entries to a database. Presently
13 only accepts the squid native format
14 DSN Database DSN encoded as a path. This is sent as the access_log
16 file path.
17 Sample configuration:
19 access_log daemon:/host/database/table/username/password
20 squid
21 to leave a parameter unspecified use a double slash:
23 access_log daemon://database/table/username/password squid
24 Default "DBI:mysql:database=squid"
26 --debug Write debug messages to Squid stderr or cache.log
28
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
34 Squid configuration
35 access_log directive
36 The path to the access log file is used to provide the database
38 connection parameters.
39 access_log daemon:/mysql_host:port/database/table/username/password squid
41 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 The last parameter tells squid which log format to use when writing
46 lines to the log daemon. Presently squid format is supported.
47 mysql_host:port
49 Host where the mysql server is running. If left empty, 'localhost'
50 is assumed.
51 database
53 Name of the database to connect to. If left empty, 'squid_log' is
54 assumed.
55 table
57 Name of the database table where log lines are stored. If left
58 empty, 'access_log' is assumed.
59 username
61 Username to use when connecting to the database. If left empty,
62 'squid' is assumed.
63 password
65 Password to use when connecting to the database. If left empty, no
66 password is used.
67 To leave all fields to their default values, you can use a single
69 slash:
70 access_log daemon:/ squid
72 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 access_log daemon://///password squid
77 logfile_daemon directive
79 This is the current way of telling squid where the logfile daemon
81 resides.
82 logfile_daemon /path/to/squid/libexec/logfile-daemon_mysql.pl
84 The script must be copied to the location specified in the directive.
86 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 Database
92 Create the database:
94 CREATE DATABASE squid_log;
96 User
98 Create the user:
100 GRANT INSERT,SELECT,CREATE ON squid_log.* TO 'squid'@'localhost' IDENTIFIED BY 'squid';
102 FLUSH PRIVILEGES;
103 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 Table
109 The Daemon will attempt to initialize this table if none exists when it
111 starts.
112 The table created should look like:
114 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
132 This document refers to "log_db_daemon" script version 0.5.
133 The script has been developed and tested in the following environment:
135 squid-2.7 Squid-3.2
137 mysql 5.0.26 and 5.1
138 perl 5.8.8
139 OpenSUSE 10.2
140
142 Sample queries.
143 Clients accessing the cache
144 SELECT DISTINCT ip_client FROM access_log;
145 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 Request status count
155 To obtain the raw count of each request status:
156 SELECT squid_request_status, COUNT(*) AS n
158 FROM access_log
159 GROUP BY squid_request_status
160 ORDER BY 2 DESC;
161 To calculate the percentage of each request status:
163 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 To distinguish only between HITs and MISSes:
172 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 pecentage;
190 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 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 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 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 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
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 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 caluclate those values.
252 Similar cleanup code could be implemented in an external script and run
254 periodically independently from squid log commands.
255 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
262 Marcello Romani, marcello.romani@libero.it Amos Jeffries,
263 amosjeffries@squid-cache.org
264
266 Copyright (C) 2008 by Marcello Romani
267 This library is free software; you can redistribute it and/or modify it
269 under the same terms as Perl itself, either Perl version 5.8.8 or, at
270 your option, any later version of Perl 5 you may have available.
271perl v5.10.1 2017-03-22 LOG_DB_DAEMON(1)