source: squid-ssl/trunk/fuentes/helpers/log_daemon/DB/log_db_daemon.8 @ 5496

Last change on this file since 5496 was 5496, checked in by Juanma, 23 months ago

Initial release

File size: 13.2 KB
Line 
1.\" Automatically generated by Pod::Man 2.28 (Pod::Simple 3.28)
2.\"
3.\" Standard preamble:
4.\" ========================================================================
5.de Sp \" Vertical space (when we can't use .PP)
6.if t .sp .5v
7.if n .sp
8..
9.de Vb \" Begin verbatim text
10.ft CW
11.nf
12.ne \\$1
13..
14.de Ve \" End verbatim text
15.ft R
16.fi
17..
18.\" Set up some character translations and predefined strings.  \*(-- will
19.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
20.\" double quote, and \*(R" will give a right double quote.  \*(C+ will
21.\" give a nicer C++.  Capital omega is used to do unbreakable dashes and
22.\" therefore won't be available.  \*(C` and \*(C' expand to `' in nroff,
23.\" nothing in troff, for use with C<>.
24.tr \(*W-
25.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
26.ie n \{\
27.    ds -- \(*W-
28.    ds PI pi
29.    if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
30.    if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\"  diablo 12 pitch
31.    ds L" ""
32.    ds R" ""
33.    ds C` ""
34.    ds C' ""
35'br\}
36.el\{\
37.    ds -- \|\(em\|
38.    ds PI \(*p
39.    ds L" ``
40.    ds R" ''
41.    ds C`
42.    ds C'
43'br\}
44.\"
45.\" Escape single quotes in literal strings from groff's Unicode transform.
46.ie \n(.g .ds Aq \(aq
47.el       .ds Aq '
48.\"
49.\" If the F register is turned on, we'll generate index entries on stderr for
50.\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index
51.\" entries marked with X<> in POD.  Of course, you'll have to process the
52.\" output yourself in some meaningful fashion.
53.\"
54.\" Avoid warning from groff about undefined register 'F'.
55.de IX
56..
57.nr rF 0
58.if \n(.g .if rF .nr rF 1
59.if (\n(rF:(\n(.g==0)) \{
60.    if \nF \{
61.        de IX
62.        tm Index:\\$1\t\\n%\t"\\$2"
63..
64.        if !\nF==2 \{
65.            nr % 0
66.            nr F 2
67.        \}
68.    \}
69.\}
70.rr rF
71.\"
72.\" Accent mark definitions (@(#)ms.acc 1.5 88/02/08 SMI; from UCB 4.2).
73.\" Fear.  Run.  Save yourself.  No user-serviceable parts.
74.    \" fudge factors for nroff and troff
75.if n \{\
76.    ds #H 0
77.    ds #V .8m
78.    ds #F .3m
79.    ds #[ \f1
80.    ds #] \fP
81.\}
82.if t \{\
83.    ds #H ((1u-(\\\\n(.fu%2u))*.13m)
84.    ds #V .6m
85.    ds #F 0
86.    ds #[ \&
87.    ds #] \&
88.\}
89.    \" simple accents for nroff and troff
90.if n \{\
91.    ds ' \&
92.    ds ` \&
93.    ds ^ \&
94.    ds , \&
95.    ds ~ ~
96.    ds /
97.\}
98.if t \{\
99.    ds ' \\k:\h'-(\\n(.wu*8/10-\*(#H)'\'\h"|\\n:u"
100.    ds ` \\k:\h'-(\\n(.wu*8/10-\*(#H)'\`\h'|\\n:u'
101.    ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'^\h'|\\n:u'
102.    ds , \\k:\h'-(\\n(.wu*8/10)',\h'|\\n:u'
103.    ds ~ \\k:\h'-(\\n(.wu-\*(#H-.1m)'~\h'|\\n:u'
104.    ds / \\k:\h'-(\\n(.wu*8/10-\*(#H)'\z\(sl\h'|\\n:u'
105.\}
106.    \" troff and (daisy-wheel) nroff accents
107.ds : \\k:\h'-(\\n(.wu*8/10-\*(#H+.1m+\*(#F)'\v'-\*(#V'\z.\h'.2m+\*(#F'.\h'|\\n:u'\v'\*(#V'
108.ds 8 \h'\*(#H'\(*b\h'-\*(#H'
109.ds o \\k:\h'-(\\n(.wu+\w'\(de'u-\*(#H)/2u'\v'-.3n'\*(#[\z\(de\v'.3n'\h'|\\n:u'\*(#]
110.ds d- \h'\*(#H'\(pd\h'-\w'~'u'\v'-.25m'\f2\(hy\fP\v'.25m'\h'-\*(#H'
111.ds D- D\\k:\h'-\w'D'u'\v'-.11m'\z\(hy\v'.11m'\h'|\\n:u'
112.ds th \*(#[\v'.3m'\s+1I\s-1\v'-.3m'\h'-(\w'I'u*2/3)'\s-1o\s+1\*(#]
113.ds Th \*(#[\s+2I\s-2\h'-\w'I'u*3/5'\v'-.3m'o\v'.3m'\*(#]
114.ds ae a\h'-(\w'a'u*4/10)'e
115.ds Ae A\h'-(\w'A'u*4/10)'E
116.    \" corrections for vroff
117.if v .ds ~ \\k:\h'-(\\n(.wu*9/10-\*(#H)'\s-2\u~\d\s+2\h'|\\n:u'
118.if v .ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'\v'-.4m'^\v'.4m'\h'|\\n:u'
119.    \" for low resolution devices (crt and lpr)
120.if \n(.H>23 .if \n(.V>19 \
121\{\
122.    ds : e
123.    ds 8 ss
124.    ds o a
125.    ds d- d\h'-1'\(ga
126.    ds D- D\h'-1'\(hy
127.    ds th \o'bp'
128.    ds Th \o'LP'
129.    ds ae ae
130.    ds Ae AE
131.\}
132.rm #[ #] #H #V #F C
133.\" ========================================================================
134.\"
135.IX Title "LOG_DB_DAEMON 8"
136.TH LOG_DB_DAEMON 8 "2015-11-28" "perl v5.20.2" "User Contributed Perl Documentation"
137.\" For nroff, turn off justification.  Always turn off hyphenation; it makes
138.\" way too many mistakes in technical documents.
139.if n .ad l
140.nh
141.SH "NAME"
142log_db_daemon \- Database logging daemon for Squid
143.SH "SYNOPSIS"
144.IX Header "SYNOPSIS"
145log_db_daemon \s-1DSN\s0 [options]
146.SH "DESCRIPTOIN"
147.IX Header "DESCRIPTOIN"
148This program writes Squid access.log entries to a database.
149Presently only accepts the \fBsquid\fR native format
150.IP "\fB\s-1DSN\s0\fR" 8
151.IX Item "DSN"
152Database \s-1DSN\s0 encoded as a path. This is sent as the access_log file path.
153.Sp
154Sample configuration:
155  access_log daemon:/host/database/table/username/password squid
156.Sp
157.Vb 2
158\&  to leave a parameter unspecified use a double slash:
159\&  access_log daemon://database/table/username/password squid
160.Ve
161.Sp
162Default \*(L"DBI:mysql:database=squid\*(R"
163.IP "\fB\-\-debug\fR" 8
164.IX Item "--debug"
165Write debug messages to Squid stderr or cache.log
166.SH "DESCRIPTION"
167.IX Header "DESCRIPTION"
168This module exploits the new logfile daemon support available in squid 2.7 and 3.2 to store access log entries in a MySQL database.
169.SH "CONFIGURATION"
170.IX Header "CONFIGURATION"
171.SS "Squid configuration"
172.IX Subsection "Squid configuration"
173\fIaccess_log directive\fR
174.IX Subsection "access_log directive"
175.PP
176The path to the access log file is used to provide the database connection parameters.
177.PP
178.Vb 1
179\&  access_log daemon:/mysql_host:port/database/table/username/password squid
180.Ve
181.PP
182The 'daemon' prefix is mandatory and tells squid that the \fBlogfile_daemon\fR helper is to be used instead of the normal file logging.
183.PP
184The last parameter tells squid which log format to use when writing lines to the log daemon.
185Presently \fBsquid\fR format is supported.
186.IP "mysql_host:port" 4
187.IX Item "mysql_host:port"
188Host where the mysql server is running. If left empty, 'localhost' is assumed.
189.IP "database" 4
190.IX Item "database"
191Name of the database to connect to. If left empty, 'squid_log' is assumed.
192.IP "table" 4
193.IX Item "table"
194Name of the database table where log lines are stored. If left empty, 'access_log' is assumed.
195.IP "username" 4
196.IX Item "username"
197Username to use when connecting to the database. If left empty, 'squid' is assumed.
198.IP "password" 4
199.IX Item "password"
200Password to use when connecting to the database. If left empty, no password is used.
201.PP
202To leave all fields to their default values, you can use a single slash:
203.PP
204.Vb 1
205\&  access_log daemon:/ squid
206.Ve
207.PP
208To specify only the database password, which by default is empty, you must leave unspecified all the other parameters by using null strings:
209.PP
210.Vb 1
211\&  access_log daemon://///password squid
212.Ve
213.PP
214\fIlogfile_daemon directive\fR
215.IX Subsection "logfile_daemon directive"
216.PP
217This is the current way of telling squid where the logfile daemon resides.
218.PP
219.Vb 1
220\&  logfile_daemon /path/to/squid/libexec/logfile\-daemon_mysql.pl
221.Ve
222.PP
223The script must be copied to the location specified in the directive.
224.SS "Database configuration"
225.IX Subsection "Database configuration"
226Let's call the database 'squid_log' and the log table 'access_log'. The username and password for the db connection will be both 'squid'.
227.PP
228\fIDatabase\fR
229.IX Subsection "Database"
230.PP
231Create the database:
232.PP
233.Vb 1
234\&  CREATE DATABASE squid_log;
235.Ve
236.PP
237\fIUser\fR
238.IX Subsection "User"
239.PP
240Create the user:
241.PP
242.Vb 2
243\&  GRANT INSERT,SELECT,CREATE ON squid_log.* TO \*(Aqsquid\*(Aq@\*(Aqlocalhost\*(Aq IDENTIFIED BY \*(Aqsquid\*(Aq;
244\&  FLUSH PRIVILEGES;
245.Ve
246.PP
247Note that only \s-1CREATE, INSERT\s0 and \s-1SELECT\s0 privileges are granted to the 'squid' user. This ensures that the logfile daemon script cannot change or modify the log entries.
248.PP
249\fITable\fR
250.IX Subsection "Table"
251.PP
252The Daemon will attempt to initialize this table if none exists when it starts.
253.PP
254The table created should look like:
255.PP
256.Vb 10
257\&  CREATE TABLE access_log (
258\&    id                   INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
259\&    time_since_epoch     DECIMAL(15,3),
260\&    time_response        INTEGER,
261\&    ip_client            CHAR(15),
262\&    ip_server            CHAR(15),
263\&    http_status_code     VARCHAR(10),
264\&    http_reply_size      INTEGER,
265\&    http_method          VARCHAR(20),
266\&    http_url             TEXT,
267\&    http_username        VARCHAR(20),
268\&    http_mime_type       VARCHAR(50),
269\&    squid_hier_status    VARCHAR(20),
270\&    squid_request_status VARCHAR(20)
271\&  );
272.Ve
273.SH "VERSION INFORMATION"
274.IX Header "VERSION INFORMATION"
275This document refers to \f(CW\*(C`log_db_daemon\*(C'\fR script version 0.5.
276.PP
277The script has been developed and tested in the following environment:
278.IP "squid\-2.7 Squid\-3.2" 4
279.IX Item "squid-2.7 Squid-3.2"
280.PD 0
281.IP "mysql 5.0.26 and 5.1" 4
282.IX Item "mysql 5.0.26 and 5.1"
283.IP "perl 5.8.8" 4
284.IX Item "perl 5.8.8"
285.IP "OpenSUSE 10.2" 4
286.IX Item "OpenSUSE 10.2"
287.PD
288.SH "DATA EXTRACTION"
289.IX Header "DATA EXTRACTION"
290.SS "Sample queries."
291.IX Subsection "Sample queries."
292.IP "Clients accessing the cache" 4
293.IX Item "Clients accessing the cache"
294.Vb 1
295\&  SELECT DISTINCT ip_client FROM access_log;
296.Ve
297.IP "Number of request per day" 4
298.IX Item "Number of request per day"
299.Vb 6
300\&  SELECT
301\&    DATE(FROM_UNIXTIME(time_since_epoch)) AS date_day,
302\&    COUNT(*) AS num_of_requests
303\&  FROM access_log
304\&  GROUP BY 1
305\&  ORDER BY 1;
306.Ve
307.IP "Request status count" 4
308.IX Item "Request status count"
309To obtain the raw count of each request status:
310.Sp
311.Vb 4
312\&  SELECT squid_request_status, COUNT(*) AS n
313\&  FROM access_log
314\&  GROUP BY squid_request_status
315\&  ORDER BY 2 DESC;
316.Ve
317.Sp
318To calculate the percentage of each request status:
319.Sp
320.Vb 6
321\&  SELECT
322\&    squid_request_status,
323\&    (COUNT(*)/(SELECT COUNT(*) FROM access_log)*100) AS percentage
324\&  FROM access_log
325\&  GROUP BY squid_request_status
326\&  ORDER BY 2 DESC;
327.Ve
328.Sp
329To distinguish only between HITs and MISSes:
330.Sp
331.Vb 10
332\&  SELECT
333\&    \*(Aqhits\*(Aq,
334\&    (SELECT COUNT(*)
335\&    FROM access_log
336\&    WHERE squid_request_status LIKE \*(Aq%HIT%\*(Aq)
337\&    /
338\&    (SELECT COUNT(*) FROM access_log)*100
339\&    AS percentage
340\&  UNION
341\&  SELECT
342\&    \*(Aqmisses\*(Aq,
343\&    (SELECT COUNT(*)
344\&    FROM access_log
345\&    WHERE squid_request_status LIKE \*(Aq%MISS%\*(Aq)
346\&    /
347\&    (SELECT COUNT(*) FROM access_log)*100
348\&    AS pecentage;
349.Ve
350.IP "Response time ranges" 4
351.IX Item "Response time ranges"
352.Vb 10
353\&  SELECT
354\&    \*(Aq0..500\*(Aq,
355\&    COUNT(*)/(SELECT COUNT(*) FROM access_log)*100 AS percentage
356\&  FROM access_log
357\&  WHERE time_response >= 0 AND time_response < 500
358\&  UNION
359\&  SELECT
360\&    \*(Aq500..1000\*(Aq,
361\&    COUNT(*)/(SELECT COUNT(*) FROM access_log)*100 AS percentage
362\&  FROM access_log
363\&  WHERE time_response >= 500 AND time_response < 1000
364\&  UNION
365\&  SELECT
366\&    \*(Aq1000..2000\*(Aq,
367\&    COUNT(*)/(SELECT COUNT(*) FROM access_log)*100 AS percentage
368\&  FROM access_log
369\&  WHERE time_response >= 1000 AND time_response < 2000
370\&  UNION
371\&  SELECT
372\&    \*(Aq>= 2000\*(Aq,
373\&    COUNT(*)/(SELECT COUNT(*) FROM access_log)*100 AS percentage
374\&  FROM access_log
375\&  WHERE time_response >= 2000;
376.Ve
377.IP "Traffic by mime type" 4
378.IX Item "Traffic by mime type"
379.Vb 6
380\&  SELECT
381\&    http_mime_type,
382\&    SUM(http_reply_size) as total_bytes
383\&  FROM access_log
384\&  GROUP BY http_mime_type
385\&  ORDER BY 2 DESC;
386.Ve
387.IP "Traffic by client" 4
388.IX Item "Traffic by client"
389.Vb 6
390\&  SELECT
391\&    ip_client,
392\&    SUM(http_reply_size) AS total_bytes
393\&  FROM access_log
394\&  GROUP BY 1
395\&  ORDER BY 2 DESC;
396.Ve
397.SS "Speed issues"
398.IX Subsection "Speed issues"
399The 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 \*(L"ENGINE=MYISAM\*(R" at the end of the table creation code in the above \s-1SQL\s0 script.
400.PP
401Indexes should be created according to the queries that are more frequently run. The \s-1DDL\s0 script only creates an implicit index for the primary key column.
402.SH "TODO"
403.IX Header "TODO"
404.SS "Table cleanup"
405.IX Subsection "Table cleanup"
406This script currently implements only the \f(CW\*(C`L\*(C'\fR (i.e. \*(L"append a line to the log\*(R") command, therefore the log lines are never purged from the table. This approach has an obvious scalability problem.
407.PP
408One solution would be to implement e.g. the \*(L"rotate log\*(R" command in a way that would calculate some summary values, put them in a \*(L"summary table\*(R" and then delete the lines used to caluclate those values.
409.PP
410Similar cleanup code could be implemented in an external script and run periodically independently from squid log commands.
411.SS "Testing"
412.IX Subsection "Testing"
413This 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.
414.SH "AUTHOR"
415.IX Header "AUTHOR"
416Marcello Romani, marcello.romani@libero.it
417Amos Jeffries, amosjeffries@squid\-cache.org
418.SH "COPYRIGHT AND LICENSE"
419.IX Header "COPYRIGHT AND LICENSE"
420.Vb 5
421\& * Copyright (C) 1996\-2015 The Squid Software Foundation and contributors
422\& *
423\& * Squid software is distributed under GPLv2+ license and includes
424\& * contributions from numerous individuals and organizations.
425\& * Please see the COPYING and CONTRIBUTORS files for details.
426.Ve
427.PP
428Copyright (C) 2008 by Marcello Romani
429.PP
430This library is free software; you can redistribute it and/or modify
431it under the same terms as Perl itself, either Perl version 5.8.8 or,
432at your option, any later version of Perl 5 you may have available.
Note: See TracBrowser for help on using the repository browser.