summaryrefslogtreecommitdiffstats
path: root/docs/node5.html
diff options
context:
space:
mode:
authorGravatar Edward Rudd 2011-07-21 19:10:20 -0400
committerGravatar Edward Rudd 2011-07-21 19:10:38 -0400
commitf1fd1c9714256bb9b212462dd31ca6dc56ea31ef (patch)
tree6d3aaaf843f758f5cd7dd2dc7641dca6ed4badca /docs/node5.html
add in project web page
Diffstat (limited to 'docs/node5.html')
-rw-r--r--docs/node5.html1270
1 files changed, 1270 insertions, 0 deletions
diff --git a/docs/node5.html b/docs/node5.html
new file mode 100644
index 0000000..e9a593c
--- /dev/null
+++ b/docs/node5.html
@@ -0,0 +1,1270 @@
1<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">
2
3<!--Converted with LaTeX2HTML 2002-1 (1.68)
4original version by: Nikos Drakos, CBLU, University of Leeds
5* revised and updated by: Marcus Hennecke, Ross Moore, Herb Swan
6* with significant contributions from:
7 Jens Lippmann, Marek Rouchal, Martin Wilck and others -->
8<HTML>
9<HEAD>
10<TITLE>4 FAQ</TITLE>
11<META NAME="description" CONTENT="4 FAQ">
12<META NAME="keywords" CONTENT="documentation">
13<META NAME="resource-type" CONTENT="document">
14<META NAME="distribution" CONTENT="global">
15
16<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
17<META NAME="Generator" CONTENT="LaTeX2HTML v2002-1">
18<META HTTP-EQUIV="Content-Style-Type" CONTENT="text/css">
19
20<LINK REL="STYLESHEET" HREF="documentation.css">
21
22<LINK REL="next" HREF="node6.html">
23<LINK REL="previous" HREF="node4.html">
24<LINK REL="up" HREF="documentation.html">
25<LINK REL="next" HREF="node6.html">
26</HEAD>
27
28<BODY >
29<!--Navigation Panel-->
30<A NAME="tex2html219"
31 HREF="node6.html">
32<IMG WIDTH="37" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="next" SRC="next.png"></A>
33<A NAME="tex2html215"
34 HREF="documentation.html">
35<IMG WIDTH="26" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="up" SRC="up.png"></A>
36<A NAME="tex2html209"
37 HREF="node4.html">
38<IMG WIDTH="63" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="previous" SRC="prev.png"></A>
39<A NAME="tex2html217"
40 HREF="node1.html">
41<IMG WIDTH="65" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="contents" SRC="contents.png"></A>
42<BR>
43<B> Next:</B> <A NAME="tex2html220"
44 HREF="node6.html">About this document ...</A>
45<B> Up:</B> <A NAME="tex2html216"
46 HREF="documentation.html">Installing and Running mod_log_sql</A>
47<B> Previous:</B> <A NAME="tex2html210"
48 HREF="node4.html">3 Configuration</A>
49 &nbsp; <B> <A NAME="tex2html218"
50 HREF="node1.html">Contents</A></B>
51<BR>
52<BR>
53<!--End of Navigation Panel-->
54<!--Table of Child-Links-->
55<A NAME="CHILD_LINKS"><STRONG>Subsections</STRONG></A>
56
57<UL>
58<LI><A NAME="tex2html221"
59 HREF="node5.html#SECTION00051000000000000000">4.1 General module questions</A>
60<UL>
61<LI><A NAME="tex2html222"
62 HREF="node5.html#SECTION00051100000000000000">4.1.1 Why log to an SQL database?</A>
63<LI><A NAME="tex2html223"
64 HREF="node5.html#SECTION00051200000000000000">4.1.2 Why use MySQL? Are there alternatives?</A>
65<LI><A NAME="tex2html224"
66 HREF="node5.html#SECTION00051300000000000000">4.1.3 Is this code production-ready?</A>
67<LI><A NAME="tex2html225"
68 HREF="node5.html#SECTION00051400000000000000">4.1.4 Who's using mod_log_sql?</A>
69<LI><A NAME="tex2html226"
70 HREF="node5.html#SECTION00051500000000000000">4.1.5 Why doesn't the module also replace the Apache ErrorLog?</A>
71<LI><A NAME="tex2html227"
72 HREF="node5.html#SECTION00051600000000000000">4.1.6 Does mod_log_sql work with Apache 2.x?</A>
73<LI><A NAME="tex2html228"
74 HREF="node5.html#SECTION00051700000000000000">4.1.7 Does mod_log_sql connect to MySQL via TCP/IP or a socket?</A>
75<LI><A NAME="tex2html229"
76 HREF="node5.html#SECTION00051800000000000000">4.1.8 I have discovered a bug. Who can I contact?</A>
77</UL>
78<BR>
79<LI><A NAME="tex2html230"
80 HREF="node5.html#SECTION00052000000000000000">4.2 Problems</A>
81<UL>
82<LI><A NAME="tex2html231"
83 HREF="node5.html#SECTION00052100000000000000">4.2.1 Apache segfaults when using PHP and mod_log_sql</A>
84<LI><A NAME="tex2html232"
85 HREF="node5.html#SECTION00052200000000000000">4.2.2 Apache appears to start up fine, but nothing
86is getting logged in the database</A>
87<LI><A NAME="tex2html233"
88 HREF="node5.html#SECTION00052300000000000000">4.2.3 Why do I get the message ``insufficient configuration info to
89establish database link'' in my Apache error log?</A>
90<LI><A NAME="tex2html234"
91 HREF="node5.html#SECTION00052400000000000000">4.2.4 My database cannot handle all the open connections from mod_log_sql,
92is there anything I can do?</A>
93<LI><A NAME="tex2html235"
94 HREF="node5.html#SECTION00052500000000000000">4.2.5 Why do I occasionally see a ``lost connection to MySQL server''
95message in my Apache error log?</A>
96</UL>
97<BR>
98<LI><A NAME="tex2html236"
99 HREF="node5.html#SECTION00053000000000000000">4.3 Performance and Tuning</A>
100<UL>
101<LI><A NAME="tex2html237"
102 HREF="node5.html#SECTION00053100000000000000">4.3.1 How well does it perform?</A>
103<LI><A NAME="tex2html238"
104 HREF="node5.html#SECTION00053200000000000000">4.3.2 Do I need to be worried about all the running MySQL children? Will
105holding open <I>n</I> Apache-to-MySQL connections consume a lot of
106memory? </A>
107<LI><A NAME="tex2html239"
108 HREF="node5.html#SECTION00053300000000000000">4.3.3 My webserver cannot handle all the traffic that my site receives,
109is there anything I can do?</A>
110<LI><A NAME="tex2html240"
111 HREF="node5.html#SECTION00053400000000000000">4.3.4 What is the issue with activating delayed
112inserts?</A>
113</UL>
114<BR>
115<LI><A NAME="tex2html241"
116 HREF="node5.html#SECTION00054000000000000000">4.4 ``How do I...?'' - accomplishing certain tasks</A>
117<UL>
118<LI><A NAME="tex2html242"
119 HREF="node5.html#SECTION00054100000000000000">4.4.1 I am using LogSQLMassVirtualHosting, and sometimes a single VirtualHost
120gets logged to two different tables. How do I prevent that?</A>
121<LI><A NAME="tex2html243"
122 HREF="node5.html#SECTION00054200000000000000">4.4.2 How do I extract the data in a format that my analysis tool can understand?</A>
123<LI><A NAME="tex2html244"
124 HREF="node5.html#SECTION00054300000000000000">4.4.3 How can I log mod_usertrack cookies?</A>
125<LI><A NAME="tex2html245"
126 HREF="node5.html#SECTION00054400000000000000">4.4.4 What if I want to log more than one cookie? What is the difference
127between LogSQLWhichCookie and LogSQLWhichCookies?</A>
128<LI><A NAME="tex2html246"
129 HREF="node5.html#SECTION00054500000000000000">4.4.5 What are the SSL logging features, and how do I activate them?</A>
130</UL></UL>
131<!--End of Table of Child-Links-->
132<HR>
133
134<H1><A NAME="SECTION00050000000000000000">
1354 FAQ</A>
136</H1>
137
138<P>
139
140<H2><A NAME="SECTION00051000000000000000">
1414.1 General module questions</A>
142</H2>
143
144<P>
145
146<H3><A NAME="SECTION00051100000000000000"></A><A NAME="sub:why"></A>
147<BR>
1484.1.1 Why log to an SQL database?
149</H3>
150
151<P>
152To begin with, let's get it out of the way: logging to a database
153is not a panacea. But while there are complexities with this solution,
154the benefit can be substantial for certain classes of administrator
155or people with advanced requirements:
156
157<P>
158
159<UL>
160<LI>Chores like log rotation go away, as you can DELETE records from the
161SQL database once they are no longer useful. For example, the excellent
162and popular log-analysis tool Webalizer (http://www.webalizer.com)
163does not need historic logs after it has processed them, enabling
164you to delete older logs.
165</LI>
166<LI>People with clusters of web servers (for high availability) will benefit
167the most - all their webservers can log to a single SQL database.
168This obviates the need to collate/interleave the many separate logfiles,
169which can be / highly/ problematic.
170</LI>
171<LI>People acquainted with the power of SQL SELECT statements will know
172the flexibility of the extraction possibilities at their fingertips.
173</LI>
174</UL>
175For example, do you want to see all your 404's? Do this:
176
177<P>
178
179<DL COMPACT>
180<DT>
181<DD>select&nbsp;remote_host,status,request_uri,bytes_sent,from_unixtime(time_stamp)
182
183<P>
184from&nbsp;acc_log_tbl&nbsp;where&nbsp;status=404&nbsp;order&nbsp;by&nbsp;time_stamp;
185
186<P>
187</DD>
188</DL>
189<DIV ALIGN="CENTER">
190<TABLE CELLPADDING=3 BORDER="1">
191<TR><TD ALIGN="LEFT">remote_host</TD>
192<TD ALIGN="LEFT">status</TD>
193<TD ALIGN="LEFT">request_uri</TD>
194<TD ALIGN="LEFT">bytes_sent</TD>
195<TD ALIGN="LEFT">from_unixtime(time_stamp)</TD>
196</TR>
197<TR><TD ALIGN="LEFT">marge.mmm.co.uk</TD>
198<TD ALIGN="LEFT">404</TD>
199<TD ALIGN="LEFT">/favicon.ico</TD>
200<TD ALIGN="LEFT">321</TD>
201<TD ALIGN="LEFT">2001-11-20 02:30:56</TD>
202</TR>
203<TR><TD ALIGN="LEFT">62.180.239.251</TD>
204<TD ALIGN="LEFT">404</TD>
205<TD ALIGN="LEFT">/favicon.ico</TD>
206<TD ALIGN="LEFT">333</TD>
207<TD ALIGN="LEFT">2001-11-20 02:45:25</TD>
208</TR>
209<TR><TD ALIGN="LEFT">212.234.12.66</TD>
210<TD ALIGN="LEFT">404</TD>
211<TD ALIGN="LEFT">/favicon.ico</TD>
212<TD ALIGN="LEFT">321</TD>
213<TD ALIGN="LEFT">2001-11-20 03:01:00</TD>
214</TR>
215<TR><TD ALIGN="LEFT">212.210.78.254</TD>
216<TD ALIGN="LEFT">404</TD>
217<TD ALIGN="LEFT">/favicon.ico</TD>
218<TD ALIGN="LEFT">333</TD>
219<TD ALIGN="LEFT">2001-11-20 03:26:05</TD>
220</TR>
221</TABLE>
222</DIV>
223
224<P>
225
226<DL COMPACT>
227<DT>
228<DD><P>
229</DD>
230</DL>Or do you want to see how many bytes you've sent within a certain
231directory or site? Do this:
232
233<P>
234
235<DL COMPACT>
236<DT>
237<DD>select&nbsp;request_uri,sum(bytes_sent)&nbsp;as&nbsp;bytes,count(request_uri)&nbsp;as&nbsp;howmany&nbsp;from
238
239<P>
240acc_log_tbl&nbsp;where&nbsp;request_uri&nbsp;like&nbsp;'%mod_log_sql%'&nbsp;group&nbsp;by&nbsp;request_uri&nbsp;order
241
242<P>
243by&nbsp;howmany&nbsp;desc;&nbsp;
244
245<P>
246</DD>
247</DL>
248<DIV ALIGN="CENTER">
249<TABLE CELLPADDING=3 BORDER="1">
250<TR><TD ALIGN="LEFT">request_uri</TD>
251<TD ALIGN="LEFT">bytes</TD>
252<TD ALIGN="LEFT">howmany</TD>
253</TR>
254<TR><TD ALIGN="LEFT">/mod_log_sql/style_1.css</TD>
255<TD ALIGN="LEFT">157396</TD>
256<TD ALIGN="LEFT">1288</TD>
257</TR>
258<TR><TD ALIGN="LEFT">/mod_log_sql/</TD>
259<TD ALIGN="LEFT">2514337</TD>
260<TD ALIGN="LEFT">801</TD>
261</TR>
262<TR><TD ALIGN="LEFT">/mod_log_sql/mod_log_sql.tar.gz</TD>
263<TD ALIGN="LEFT">9769312</TD>
264<TD ALIGN="LEFT">456</TD>
265</TR>
266<TR><TD ALIGN="LEFT">/mod_log_sql/faq.html</TD>
267<TD ALIGN="LEFT">5038728</TD>
268<TD ALIGN="LEFT">436</TD>
269</TR>
270</TABLE>
271</DIV>
272
273<P>
274
275<DL COMPACT>
276<DT>
277<DD><P>
278</DD>
279</DL>Or maybe you want to see who's linking to you? Do this:
280
281<P>
282
283<DL COMPACT>
284<DT>
285<DD>select&nbsp;count(referer)&nbsp;as&nbsp;num,referer&nbsp;from&nbsp;acc_log_tbl&nbsp;where
286
287<P>
288request_uri='/mod_log_sql/'&nbsp;group&nbsp;by&nbsp;referer&nbsp;order&nbsp;by&nbsp;num&nbsp;desc;
289
290<P>
291</DD>
292</DL>
293<DIV ALIGN="CENTER">
294<TABLE CELLPADDING=3 BORDER="1">
295<TR><TD ALIGN="LEFT">num</TD>
296<TD ALIGN="LEFT">referer</TD>
297</TR>
298<TR><TD ALIGN="LEFT">271</TD>
299<TD ALIGN="LEFT">http://freshmeat.net/projects/mod_log_sql/</TD>
300</TR>
301<TR><TD ALIGN="LEFT">96</TD>
302<TD ALIGN="LEFT">http://modules.apache.org/search?id=339</TD>
303</TR>
304<TR><TD ALIGN="LEFT">48</TD>
305<TD ALIGN="LEFT">http://freshmeat.net/</TD>
306</TR>
307<TR><TD ALIGN="LEFT">8</TD>
308<TD ALIGN="LEFT">http://freshmeat.net</TD>
309</TR>
310</TABLE>
311</DIV>
312
313<P>
314
315<DL COMPACT>
316<DT>
317<DD><P>
318</DD>
319</DL>As you can see, there are myriad possibilities that can be constructed
320with the wonderful SQL SELECT statement. Logging to an SQL database
321can be really quite useful!
322
323<P>
324
325<H3><A NAME="SECTION00051200000000000000">
3264.1.2 Why use MySQL? Are there alternatives?</A>
327</H3>
328
329<P>
330MySQL is a robust, free, and very powerful production-quality database
331engine. It is well supported and comes with detailed documentation.
332Many 3rd-party software pacakges (e.g. Slashcode, the engine that
333powers Slashdot) run exclusively with MySQL. In other words, you will
334belong to a very robust and well-supported community by choosing MySQL.
335
336<P>
337That being said, there are alternatives. PostgreSQL is probably MySQL's
338leading &#34;competitor&#34; in the free database world.
339There is also an excellent module available for Apache to permit logging
340to a PostgreSQL database, called pgLOGd (http://www.digitalstratum.com/pglogd/).
341
342<P>
343
344<H3><A NAME="SECTION00051300000000000000">
3454.1.3 Is this code production-ready?</A>
346</H3>
347
348<P>
349By all accounts it is. It is known to work without a problem on many-thousands-of-hits-per-day
350webservers. Does that mean it is 100% bug free? Well, no software
351is. But it is well-tested and believed to be fully compatible with
352production environments. (The usual disclaimers apply. This software
353is provided without warranty of any kind.)
354
355<P>
356
357<H3><A NAME="SECTION00051400000000000000">
3584.1.4 Who's using mod_log_sql?</A>
359</H3>
360
361<P>
362Good question! It would be great to find out! If you are a production-level
363mod_log_sql user, please contact the maintainer, Chris Powell (chris@grubbybaby.com)
364so that you can be mentioned here.
365
366<P>
367
368<H3><A NAME="SECTION00051500000000000000">
3694.1.5 Why doesn't the module also replace the Apache ErrorLog?</A>
370</H3>
371
372<P>
373There are circumstances when that would be quite unwise - for example,
374if Apache could not reach the MySQL server for some reason and needed
375to log that fact. Without a text-based error log you'd never know
376anything was wrong, because Apache would be trying to log a database
377connection error to the database... you get the point.
378
379<P>
380Error logs are usually not very high-traffic and are really best left
381as text files on a web server machine.
382
383<P>
384
385<H3><A NAME="SECTION00051600000000000000">
3864.1.6 Does mod_log_sql work with Apache 2.x?</A>
387</H3>
388
389<P>
390As of this writing, no. The Apache Group significantly altered the
391module API with the release of Apache 2.0. All modules written for
3921.3, including mod_log_sql, will not work with 2.0.
393
394<P>
395mod_log_sql will eventually be ported to Apache 2.x, but not immediately.
396It is going to take some time, and there are other features that have
397higher priority. Please sign up for the announcements list (on the
398main website) or monitor the website for updates to learn when the
399port (and other releases) are available.
400
401<P>
402&lt;OPINION&gt;If you're a *NIX user, stick with Apache 1.3.x for now.
403Major modules like mod_ssl and PHP are not even ready for 2.0 yet,
404and the main benefits in 2.0 are for Win32 users anyway. Apache 1.3.x
405is rock-stable and performs equally well on *NIX as 2.0.&lt;/OPINION&gt;
406
407<P>
408
409<H3><A NAME="SECTION00051700000000000000">
4104.1.7 Does mod_log_sql connect to MySQL via TCP/IP or a socket?</A>
411</H3>
412
413<P>
414It depends! This is not determined by mod_log_sql. mod_log_sql
415relies on a connection command that is supplied in the MySQL API,
416and that command is somewhat intelligent. How it works:
417
418<P>
419
420<UL>
421<LI>if the specified MySQL database is on the same machine, the connection
422command uses a socket to communicate with MySQL
423</LI>
424<LI>if the specified MySQL database is on a different machine, mod_log_sql
425connects using TCP/IP.
426</LI>
427</UL>
428You don't have any control of which methodology is used. You can fine-tune
429some of the configuration, however. The L<SMALL>OG</SMALL>SQLS<SMALL>OCKET</SMALL>F<SMALL>ILE</SMALL>
430runtime configuration directive overrides the default of ``/var/lib/mysql/mysql.sock''
431for socket-based connections, whereas the L<SMALL>OG</SMALL>SQLTCPP<SMALL>ORT</SMALL> command
432allows to you override the default TCP port of 3306 for TCP/IP connections.
433
434<P>
435
436<H3><A NAME="SECTION00051800000000000000">
4374.1.8 I have discovered a bug. Who can I contact?</A>
438</H3>
439
440<P>
441Please contact the maintainer (chris@grubbybaby.com)! Your
442comments, suggestions, bugfixes, bug catches, and usage testimonials
443are always welcome. As free software, mod_log_sql is intended to
444be a community effort - any code contributions or other ideas will
445be fully and openly credited, of course.
446
447<P>
448
449<H2><A NAME="SECTION00052000000000000000">
4504.2 Problems</A>
451</H2>
452
453<P>
454
455<H3><A NAME="SECTION00052100000000000000">
4564.2.1 Apache segfaults when using PHP and mod_log_sql</A>
457</H3>
458
459<P>
460This occurs if you compiled PHP with MySQL database support. PHP utilizes
461its internal, bundled MySQL libraries by default. These conflict with
462the ``real'' MySQL libraries linked by mod_log_sql, causing
463the segmentation fault.
464
465<P>
466The solution is to configure PHP to link against the real MySQL libraries
467and recompile mod_php. Apache will run properly once the modules
468are all using the same version of the MySQL libraries.
469
470<P>
471
472<H3><A NAME="SECTION00052200000000000000"></A><A NAME="faq:NothingLogged"></A>
473<BR>
4744.2.2 Apache appears to start up fine, but nothing
475is getting logged in the database
476</H3>
477
478<P>
479If you do not see any entries in the access_log, then something is
480preventing the inserts from happening. This could be caused by several
481things:
482
483<P>
484
485<UL>
486<LI>Improper privileges set up in the MySQL database
487</LI>
488<LI>You aren't hitting a VirtualHost that has a LogSQLTransferLogTable
489entry
490</LI>
491<LI>You didn't specify the right database host or login information
492</LI>
493<LI>Another factor is preventing a connection to the database
494</LI>
495</UL>
496Important: it is improper to ask for help before you have followed
497these steps.
498
499<P>
500First examine the MySQL log that you established in step <A HREF="node4.html#step:EnaLog">6</A>
501of section <A HREF="node4.html#sub:PrepDb">3.1</A>. Ensure that the INSERT statements are
502not being rejected because of a malformed table name or other typographical
503error. By enabling that log, you instructed MySQL to log every connection
504and command it receives - if you see no INSERT attempts in the log,
505the module isn't successfully connecting to the database. If you see
506nothing at all in the log - not even a record of your administrative
507connection attempts, then you did not enable the log correctly. If
508you do see INSERT attempts but they are failing, the log should tell
509you why.
510
511<P>
512Second, confirm that your L<SMALL>OG</SMALL>SQL* directives are all correct.
513
514<P>
515Third, examine the Apache error logs for messages from mod_log_sql;
516the module will offer hints as to why it cannot connect, etc.
517
518<P>
519The next thing to do is recompile the module with debugging output
520activated. change the &#34;#undef DEBUG&#34; on line 8
521of mod_log_sql.c to &#34;#define DEBUG&#34; and recompile/reinstall.
522The module will now output copious notes about what it is doing, and
523this will help you (and the maintainer) solve the problem. In order
524to see the debugging messages, ensure that you make them visible using
525the L<SMALL>OG</SMALL>L<SMALL>EVEL</SMALL> directive <B>in the main server config
526as well as in each</B> <B>V<SMALL>IRTUAL</SMALL>H<SMALL>OST</SMALL></B> <B>config:</B>
527
528<P>
529
530<DL COMPACT>
531<DT>
532<DD>LogLevel&nbsp;debug
533
534<P>
535ErrorLog&nbsp;/var/log/httpd/server-messages&nbsp;
536</DD>
537</DL>
538<P>
539
540<H3><A NAME="SECTION00052300000000000000">
5414.2.3 Why do I get the message ``insufficient configuration info to
542establish database link'' in my Apache error log?</A>
543</H3>
544
545<P>
546At a minimum, L<SMALL>OG</SMALL>SQLD<SMALL>ATABASE</SMALL> and L<SMALL>OG</SMALL>SQLL<SMALL>OGIN</SMALL>I<SMALL>NFO</SMALL>
547must be defined in order for the module to be able to establish a
548database link. If these are not defined or are incomplete you will
549receive this error message.
550
551<P>
552
553<H3><A NAME="SECTION00052400000000000000">
5544.2.4 My database cannot handle all the open connections from mod_log_sql,
555is there anything I can do?</A>
556</H3>
557
558<P>
559The rule of thumb: if you have <I>n</I> webservers each configured
560to support <I>y</I> M<SMALL>AX</SMALL>C<SMALL>LIENTS</SMALL>, then your database must be
561able to handle <IMG
562 WIDTH="41" HEIGHT="29" ALIGN="MIDDLE" BORDER="0"
563 SRC="img3.png"
564 ALT="$n\times y$"> simultenous connections <I>in the worst
565case.</I> Certainly you must use common sense, consider reasonable traffic
566expectations and structure things accordingly.
567
568<P>
569Tweaking my.cnf to scale to high connection loads is imperative. But
570if hardware limitations prevent your MySQL server from gracefully
571handling the number of incoming connections, it would be beneficial
572to upgrade the memory or CPU on that server in order to handle the
573load.
574
575<P>
576Jeremy Zawodny, a highly respected MySQL user and contributor to Linux
577Magazine, has this very helpful and highly appropriate article on
578tuning MySQL: http://jeremy.zawodny.com/blog/archives/000173.html
579
580<P>
581Please remember that mod_log_sql's overriding principle is <B>performance</B>
582- that is what the target audience demands and expects. Other database
583logging solutions do not open and maintain many database connections,
584but their performance suffers drastically. For example, pgLOGd funnels
585all log connections through a separate daemon that connects to the
586database, but that bottlenecks the entire process. mod_log_sql achieves
587performance numbers an order of magnitude greater than the alternatives
588because it dispenses with the overhead associated with rapid connection
589cycling, and it doesn't attempt to shoehorn all the database traffic
590through a single extra daemon or proxy process.
591
592<P>
593
594<H3><A NAME="SECTION00052500000000000000">
5954.2.5 Why do I occasionally see a ``lost connection to MySQL server''
596message in my Apache error log?</A>
597</H3>
598
599<P>
600This message may appear every now and then in your Apache error log,
601especially on very lightly loaded servers. This doesn't mean that
602anything is necessarily wrong. Within each httpd child process, mod_log_sql
603will open (and keep open) a connection to the MySQL server. MySQL,
604however, will close connections that haven't been used in a while;
605the default timeout is 8 hours. When this occurs, mod_log_sql will
606notice and re-open the connection. That event is what is being logged,
607and looks like this:
608
609<P>
610
611<DL COMPACT>
612<DT>
613<DD>[Tue&nbsp;Nov&nbsp;12&nbsp;19:04:10&nbsp;2002]&nbsp;[error]&nbsp;mod_log_sql:&nbsp;first&nbsp;attempt&nbsp;failed,&nbsp;
614
615<P>
616&nbsp;&nbsp;API&nbsp;said:&nbsp;error&nbsp;2013,&nbsp;Lost&nbsp;connection&nbsp;to&nbsp;MySQL&nbsp;server&nbsp;during&nbsp;query&nbsp;
617
618<P>
619[Tue&nbsp;Nov&nbsp;12&nbsp;19:04:10&nbsp;2002]&nbsp;[error]&nbsp;mod_log_sql:&nbsp;reconnect&nbsp;successful
620
621<P>
622[Tue&nbsp;Nov&nbsp;12&nbsp;19:04:10&nbsp;2002]&nbsp;[error]&nbsp;mod_log_sql:&nbsp;second&nbsp;attempt&nbsp;successful
623</DD>
624</DL>Reference: MySQL documentation (http://www.mysql.com/documentation/mysql/bychapter/manual_Problems.html#Gone_away)
625
626<P>
627
628<H2><A NAME="SECTION00053000000000000000">
6294.3 Performance and Tuning</A>
630</H2>
631
632<P>
633
634<H3><A NAME="SECTION00053100000000000000">
6354.3.1 How well does it perform?</A>
636</H3>
637
638<P>
639mod_log_sql scales to very high loads. Apache 1.3.22 + mod_log_sql
640was benchmarked using the &#34;ab&#34; (Apache Bench) program
641that comes with the Apache distribution; here are the results.
642
643<P>
644Overall configuration:
645
646<P>
647
648<UL>
649<LI>Machine A: Apache webserver
650</LI>
651<LI>Machine B: MySQL server
652</LI>
653<LI>Machines A and B connected with 100Mbps Ethernet
654</LI>
655<LI>Webserver: Celeron 400, 128 MB RAM, IDE storage
656</LI>
657</UL>
658Apache configuration:
659
660<P>
661
662<DL COMPACT>
663<DT>
664<DD>Timeout&nbsp;300&nbsp;
665
666<P>
667KeepAlive&nbsp;On&nbsp;
668
669<P>
670MaxKeepAliveRequests&nbsp;100&nbsp;
671
672<P>
673KeepAliveTimeout&nbsp;15&nbsp;
674
675<P>
676MinSpareServers&nbsp;5&nbsp;
677
678<P>
679StartServers&nbsp;10&nbsp;
680
681<P>
682MaxSpareServers&nbsp;15&nbsp;
683
684<P>
685MaxClients&nbsp;256&nbsp;
686
687<P>
688MaxRequestsPerChild&nbsp;5000&nbsp;
689
690<P>
691LogSQLTransferLogFormat&nbsp;AbHhmRSsTUuvc&nbsp;
692
693<P>
694LogSQLWhichCookie&nbsp;Clicks&nbsp;
695
696<P>
697CookieTracking&nbsp;on&nbsp;
698
699<P>
700CookieName&nbsp;Clicks
701</DD>
702</DL>&#34;ab&#34; commandline:
703
704<P>
705
706<DL COMPACT>
707<DT>
708<DD>./ab&nbsp;-c&nbsp;10&nbsp;-t&nbsp;20&nbsp;-v&nbsp;2&nbsp;-C&nbsp;Clicks=ab_run&nbsp;http://www.hostname.com/target&nbsp;
709</DD>
710</DL>( 10 concurrent requests; 20 second test; setting a cookie &#34;Clicks=ab_run&#34;;
711target = the mod_log_sql homepage. )
712
713<P>
714Ten total ab runs were conducted: five with MySQL logging enabled,
715and five with all MySQL directives commented out of httpd.conf. Then
716each five were averaged. The results:
717
718<P>
719
720<UL>
721<LI>Average of five runs employing MySQL <I>and</I> standard text logging:
722<B>139.01 requests per second, zero errors</B>.
723</LI>
724<LI>Average of five runs employing <I>only</I> standard text logging:
725<B>139.96 requests per second, zero errors</B>.
726</LI>
727</UL>
728In other words, any rate-limiting effects on this particular hardware
729setup are not caused by MySQL. Note that although this very simple
730webserver setup is hardly cutting-edge - it is, after all, a fairly
731small machine - 139 requests per second equal over <I>twelve million
732hits per day.</I>
733
734<P>
735If you run this benchmark yourself, take note of three things:
736
737<P>
738
739<OL>
740<LI>Use a target URL that is on your own webserver :-).
741</LI>
742<LI>Wait until all your connections are closed out between runs; after
743several thousand requests your TCP/IP stack will be filled with hundreds
744of connections in TIME_WAIT that need to close. Do a &#34;netstat
745-t|wc -l&#34; on the webserver to see. If you don't wait, you
746can expect to see a lot of messages like &#34;ip_conntrack:
747table full, dropping packet&#34; in your logs. (This has nothing
748to do with mod_log_sql, this is simply the nature of the TCP/IP
749stack in the Linux kernel.)
750</LI>
751<LI>When done with your runs, clean these many thousands of requests out
752of your database:
753</LI>
754</OL>
755
756<DL COMPACT>
757<DT>
758<DD>mysql&gt;&nbsp;delete&nbsp;from&nbsp;access_log&nbsp;where&nbsp;agent&nbsp;like&nbsp;'ApacheBench%';&nbsp;
759
760<P>
761mysql&gt;&nbsp;optimize&nbsp;table&nbsp;access_log;&nbsp;
762</DD>
763</DL>
764<P>
765
766<H3><A NAME="SECTION00053200000000000000">
7674.3.2 Do I need to be worried about all the running MySQL children? Will
768holding open <I>n</I> Apache-to-MySQL connections consume a lot of
769memory? </A>
770</H3>
771
772<P>
773Short answer: you shouldn't be worried.
774
775<P>
776Long answer: you might be evaluating at the output of ``ps -aufxw''
777and becoming alarmed at all the 7MB httpd processes or 22MB mysqld
778children that you see. Don't be alarmed<I>.</I> It's true that mod_log_sql
779opens and holds open many MySQL connections: each httpd child maintains
780one open database connection (and holds it open for performance reasons).
781Four webservers, each running 20 Apache children, will hold open 80
782MySQL connections, which means that your MySQL server needs to handle
78380 simultaneous connections. In truth, your MySQL server needs to
784handle far more than that if traffic to your website spikes and the
785Apache webservers spawn off an additional 30 children each...
786
787<P>
788Fortunately the cost reported by 'ps -aufxw' is deceptive. This is
789due to an OS memory-management feature called ``copy-on-write.''
790When you have a number of identical child processes (e.g. Apache,
791MySQL), it would appear in ``ps'' as though each one occupies
792a great deal of RAM - as much as 7MB per httpd child! In actuality
793each additional child only occupies a small bit of extra memory -
794most of the memory pages are common to each child and therefore shared
795in a ``read-only'' fashion. The OS can get away with this because
796the majority of memory pages for one child are identical across all
797children. Instead of thinking of each child as a rubber stamp of the
798others, think of each child as a basket of links to a common memory
799area.
800
801<P>
802A memory page is only duplicated when it needs to be written to, hence
803``copy-on-write.'' The result is efficiency and decreased memory
804consumption. ``ps'' may report 7MB per child, but it might really
805only ``cost'' 900K of extra memory to add one more child. It is
806<B>not</B> <B>correct</B> to assume that 20 Apache
807children with a VSZ of 7MB each equals <!-- MATH
808 $(20\times 7MB)$
809 -->
810<IMG
811 WIDTH="90" HEIGHT="32" ALIGN="MIDDLE" BORDER="0"
812 SRC="img4.png"
813 ALT="$(20\times 7MB)$"> of memory
814consumption - the real answer is much, much lower. The same ``copy-on-write''
815rules apply to all your MySQL children: 40 mysqld children @ 22MB
816each <B>do not</B> occupy 880MB of RAM.
817
818<P>
819The bottom line: although there is a cost to spawn extra httpd or
820mysqld children, that cost is not as great as ``ps'' would lead
821you to believe.
822
823<P>
824
825<H3><A NAME="SECTION00053300000000000000">
8264.3.3 My webserver cannot handle all the traffic that my site receives,
827is there anything I can do?</A>
828</H3>
829
830<P>
831If you have exhausted all the tuning possibilities on your existing
832server, it is probably time you evaluated the benefits of clustering
833two or more webservers together in a load-balanced fashion. In fact,
834users of such a setup are mod_log_sql's target audience!
835
836<P>
837
838<H3><A NAME="SECTION00053400000000000000"></A><A NAME="sub:DelayedInsFAQ"></A>
839<BR>
8404.3.4 What is the issue with activating delayed
841inserts?
842</H3>
843
844<P>
845There are several.
846
847<P>
848
849<OL>
850<LI>INSERT DELAYED is a specific syntax to MySQL and is not supported
851by any other database. Ergo, why is it needed, and what MySQL deficiency
852is it working around? INSERT DELAYED is a kluge.
853</LI>
854<LI>The MySQL documentation is unclear whether INSERT DELAYED is even
855necessary for an optimized database. It says, ``The DELAYED option
856for the INSERT statement is a MySQL-specific option that is very useful
857if you have clients that can't wait for the INSERT to complete.''
858But then it goes on to say, ``Note that as MyISAM tables supports
859concurrent SELECT and INSERT, if there is no free blocks in the middle
860of the data file, you very seldom need to use INSERT DELAYED with
861MyISAM.''
862</LI>
863<LI>Because INSERT DELAYED returns without waiting for the data to be
864written, a hard kill of your MySQL database at the right (wrong?)
865moment could lose those logfile entries.
866</LI>
867<LI>As of MySQL version 3.23.52, the error return functions disagree after
868a failed INSERT DELAYED: mysql_errno() always returns 0, even if
869mysql_error() returns a textual error. I have reported this bug to
870the MySQL folks. However, we have no way of knowing what solution
871they will adopt to fix this, and with the worst case solution mod_log_sql
872would not be able to tell if anything went wrong with a delayed insert.
873</LI>
874</OL>
875Instead of delayed inserts, you may wish to utilize InnoDB tables
876(instead of the standard MyISAM tables). InnoDB tables suppot row-level
877locking and are recommended for high-volume databases.
878
879<P>
880If after understanding these problems you still wish to enable delayed
881inserts, section <A HREF="node4.html#sub:DelayedIns">3.5.4</A> discusses how.
882
883<P>
884
885<H2><A NAME="SECTION00054000000000000000">
8864.4 ``How do I...?'' - accomplishing certain tasks</A>
887</H2>
888
889<P>
890
891<H3><A NAME="SECTION00054100000000000000">
8924.4.1 I am using LogSQLMassVirtualHosting, and sometimes a single VirtualHost
893gets logged to two different tables. How do I prevent that?</A>
894</H3>
895
896<P>
897Proper usage of the Apache runtime S<SMALL>ERVER</SMALL>N<SMALL>AME</SMALL> directive and
898the directive U<SMALL>SE</SMALL>C<SMALL>ANONICAL</SMALL>N<SMALL>AME </SMALL>O<SMALL>N</SMALL> (or DNS) are necessary
899to prevent this problem. ``On'' is the default for U<SMALL>SE</SMALL>C<SMALL>ANONICAL</SMALL>N<SMALL>AME</SMALL>,
900and specifies that self-referential URLs are generated from the S<SMALL>ERVER</SMALL>N<SMALL>AME</SMALL>
901part of your VirtualHost:
902
903<P>
904<BLOCKQUOTE>
905With UseCanonicalName on (and in all versions prior to 1.3) Apache
906will use the ServerName and Port directives to construct the canonical
907name for the server. With UseCanonicalName off Apache will form self-referential
908URLs using the hostname and port supplied by the client if any are
909supplied (otherwise it will use the canonical name, as defined above).
910[From the Apache documentation http://httpd.apache.org/docs/mod/core.html#usecanonicalname]
911
912</BLOCKQUOTE>
913The module inherits Apache's ``knowledge'' about the server name
914being accessed. As long as those two directives are properly configured,
915mod_log_sql will log to only one table per virtual host while using
916L<SMALL>OG</SMALL>SQLM<SMALL>ASS</SMALL>V<SMALL>IRTUAL</SMALL>H<SMALL>OSTING</SMALL>.
917
918<P>
919
920<H3><A NAME="SECTION00054200000000000000">
9214.4.2 How do I extract the data in a format that my analysis tool can understand?</A>
922</H3>
923
924<P>
925mod_log_sql would be virtually useless if there weren't a way for
926you to extract the data from your database in a somewhat meaningful
927fashion. To that end there's a Perl script enclosed with the distribution.
928That script (make_combined_log.pl) is designed to extract N-many
929days worth of access logs and provide them in a Combined Log Format
930output. You can use this very tool right in /etc/crontab to extract
931logs on a regular basis so that your favorite web analysis tool can
932read them. Or you can examine the Perl code to construct your own
933custom tool.
934
935<P>
936For example, let's say that you want your web statistics updated once
937per day in the wee hours of the morning. A good way to accomplish
938that could be the following entries in /etc/crontab:
939
940<P>
941
942<DL COMPACT>
943<DT>
944<DD>#&nbsp;Generate&nbsp;the&nbsp;temporary&nbsp;apache&nbsp;logs&nbsp;from&nbsp;the&nbsp;MySQL&nbsp;database&nbsp;(for&nbsp;webalizer)&nbsp;
945
946<P>
94705&nbsp;04&nbsp;*&nbsp;*&nbsp;*&nbsp;root&nbsp;make_combined_log.pl&nbsp;1&nbsp;www.grubbybaby.com&nbsp;&gt;&nbsp;/var/log/temp01
948
949<P>
950#&nbsp;Run&nbsp;webalizer&nbsp;on&nbsp;httpd&nbsp;log&nbsp;
951
952<P>
95330&nbsp;04&nbsp;*&nbsp;*&nbsp;*&nbsp;root&nbsp;webalizer&nbsp;-c&nbsp;/etc/webalizer.conf;&nbsp;rm&nbsp;-f&nbsp;/var/log/temp01
954</DD>
955</DL>Or if you have a newer system that puts files in /etc/cron.daily etc.,
956create a file called ``webalizer'' in the cron.daily subdirectory.
957Use the following as the contents of your file, and make sure to chmod
958755 it when done.
959
960<P>
961
962<DL COMPACT>
963<DT>
964<DD>#!/bin/sh
965
966<P>
967/usr/local/sbin/make_combined_log.pl&nbsp;1&nbsp;www.yourdomain.com&nbsp;&gt;&nbsp;/var/log/httpd/templog
968
969<P>
970/usr/local/bin/webalizer&nbsp;-q&nbsp;-c&nbsp;/etc/webalizer.conf&nbsp;
971
972<P>
973rm&nbsp;-f&nbsp;/var/log/httpd/templog
974</DD>
975</DL>See? Easy.
976
977<P>
978
979<H3><A NAME="SECTION00054300000000000000"></A><A NAME="sec:cookie"></A>
980<BR>
9814.4.3 How can I log mod_usertrack cookies?
982</H3>
983
984<P>
985A number of people like to log mod_usertrack cookies in their Apache
986TransferLog to aid in understanding their visitors' clickstreams.
987This is accomplished, for example, with a statement as follows:
988
989<P>
990
991<DL COMPACT>
992<DT>
993<DD>LogFormat&nbsp;&#34;%h&nbsp;%l&nbsp;%u&nbsp;%t&nbsp;&#92;&#34;%r&#92;&#34;&nbsp;%s&nbsp;%b&nbsp;&#92;&#34;%{Referer}i&#92;&#34;&nbsp;&#92;&#34;%{User-Agent}i&#92;&#34;&#34;&nbsp;&#92;&#34;%{cookie}n&#92;&#34;&#34;
994</DD>
995</DL>Naturally it would be nice for mod_log_sql to permit the admin to
996log the cookie data as well, so as of version 1.10 you can do this.
997You need to have already compiled mod_usertrack into httpd - it's
998one of the standard Apache modules.
999
1000<P>
1001First make sure you have a column called &#34;cookie&#34;
1002in the MySQL database to hold the cookies, which can be done as follows
1003if you already have a working database:
1004
1005<P>
1006
1007<DL COMPACT>
1008<DT>
1009<DD>alter&nbsp;table&nbsp;acc_log_tbl&nbsp;add&nbsp;column&nbsp;cookie&nbsp;varchar(255);
1010</DD>
1011</DL>Next configure your server to set usertracking cookies as follows,
1012and make sure you include the new 'c' directive in your L<SMALL>OG</SMALL>SQLT<SMALL>RANSFER</SMALL>L<SMALL>OG</SMALL>F<SMALL>ORMAT</SMALL>,
1013which activates cookie logging. Here's an example:
1014
1015<P>
1016
1017<DL COMPACT>
1018<DT>
1019<DD>&lt;VirtualHost&nbsp;1.2.3.4&gt;&nbsp;
1020
1021<P>
1022&nbsp;CookieTracking&nbsp;on&nbsp;
1023
1024<P>
1025&nbsp;CookieStyle&nbsp;Cookie&nbsp;
1026
1027<P>
1028&nbsp;CookieName&nbsp;Foobar&nbsp;
1029
1030<P>
1031&nbsp;LogSQLTransferLogFormat&nbsp;huSUsbTvRAc&nbsp;
1032
1033<P>
1034&nbsp;LogSQLWhichCookie&nbsp;Foobar&nbsp;
1035
1036<P>
1037&lt;/VirtualHost&gt;
1038</DD>
1039</DL>The first three lines configure mod_usertrack to create a COOKIE
1040(RFC 2109) format cookie called Foobar. The last two lines tell mod_log_sql
1041to log cookies named Foobar. You have to choose which cookie to log
1042because more than one cookie can/will be sent to the server by the
1043client.
1044
1045<P>
1046Recap: the 'c' character <I>activates</I> cookie logging, and the
1047L<SMALL>OG</SMALL>SQLW<SMALL>HICH</SMALL>C<SMALL>OOKIE</SMALL> directive <I>chooses</I> which cookie to
1048log.
1049
1050<P>
1051FYI, you are advised NOT to use C<SMALL>OOKIE</SMALL>S<SMALL>TYLE </SMALL>C<SMALL>OOKIE2</SMALL> - it
1052seems that even newer browsers (IE 5.5, etc.) have trouble with the
1053new COOKIE2 (RFC 2965) format. Just stick with the standard COOKIE
1054format and you'll be fine.
1055
1056<P>
1057Perform some hits on your server and run a select:
1058
1059<P>
1060
1061<DL COMPACT>
1062<DT>
1063<DD>mysql&gt;&nbsp;select&nbsp;request_uri,cookie&nbsp;from&nbsp;access_log&nbsp;where&nbsp;cookie&nbsp;is&nbsp;not&nbsp;null;
1064
1065<P>
1066</DD>
1067</DL>
1068<DIV ALIGN="CENTER">
1069<TABLE CELLPADDING=3 BORDER="1">
1070<TR><TD ALIGN="LEFT">request_uri</TD>
1071<TD ALIGN="LEFT">cookie</TD>
1072</TR>
1073<TR><TD ALIGN="LEFT">/mod_log_sql/</TD>
1074<TD ALIGN="LEFT">ool-18e4.dyn.optonline.net.130051007102700823</TD>
1075</TR>
1076<TR><TD ALIGN="LEFT">/mod_log_sql/usa.gif</TD>
1077<TD ALIGN="LEFT">ool-18e4.dyn.optonline.net.130051007102700823</TD>
1078</TR>
1079<TR><TD ALIGN="LEFT">/mod_log_sql/style_1.css</TD>
1080<TD ALIGN="LEFT">ool-18e4.dyn.optonline.net.130051007102700823</TD>
1081</TR>
1082</TABLE>
1083</DIV>
1084
1085<P>
1086
1087<DL COMPACT>
1088<DT>
1089<DD><P>
1090</DD>
1091</DL>
1092<P>
1093
1094<H3><A NAME="SECTION00054400000000000000">
10954.4.4 What if I want to log more than one cookie? What is the difference
1096between LogSQLWhichCookie and LogSQLWhichCookies?</A>
1097</H3>
1098
1099<P>
1100As of version 1.17, you have a choice in how you want cookie logging
1101handled.
1102
1103<P>
1104If you are interested in logging only one cookie per request, follow
1105the instructions in section <A HREF="node5.html#sec:cookie">4.4.3</A> above. That cookie will
1106be logged to a column in the regular access_log table, and the actual
1107cookie you want to log is specified with L<SMALL>OG</SMALL>SQLW<SMALL>HICH</SMALL>C<SMALL>OOKIE</SMALL>.
1108Don't forget to specify the 'c' character in L<SMALL>OG</SMALL>SQLT<SMALL>RANSFER</SMALL>L<SMALL>OG</SMALL>F<SMALL>ORMAT</SMALL>.
1109
1110<P>
1111If, however, you need to log multiple cookies per request, you must
1112employ the L<SMALL>OG</SMALL>SQLW<SMALL>HICH</SMALL>C<SMALL>OOKIES</SMALL> (note the plural) directive.
1113The cookies you specify will be logged to a separate table (as discussed
1114in section <A HREF="node4.html#secMulTable">3.5.2</A>), and entries in that table will be
1115linked to the regular access_log entries via the unique ID that is
1116supplied by mod_unique_id. Without mod_unique_id the information
1117will still be logged but you will be unable to correlate which cookies
1118go with which access-requests. Furthermore, with L<SMALL>OG</SMALL>SQLW<SMALL>HICH</SMALL>C<SMALL>OOKIES</SMALL>,
1119you do <B>not</B> need to include the 'c' character in L<SMALL>OG</SMALL>SQLT<SMALL>RANSFER</SMALL>L<SMALL>OG</SMALL>F<SMALL>ORMAT</SMALL>.
1120
1121<P>
1122L<SMALL>OG</SMALL>SQLW<SMALL>HICH</SMALL>C<SMALL>OOKIE</SMALL> and L<SMALL>OG</SMALL>SQLW<SMALL>HICH</SMALL>C<SMALL>OOKIES</SMALL> can coexist
1123without conflict because they operate on entireley different tables,
1124but you're better off choosing the one you need.
1125
1126<P>
1127
1128<H3><A NAME="SECTION00054500000000000000">
11294.4.5 What are the SSL logging features, and how do I activate them?</A>
1130</H3>
1131
1132<P>
1133Note: you do <B>not</B> need to compile SSL support into mod_log_sql
1134in order to simply use it with a secure site. You only need to compile
1135SSL support into mod_log_sql if you want to log SSL-specific data
1136such as the cipher type used, or the keysize that was negotiated.
1137If that information is unimportant to you, you can ignore this FAQ.
1138
1139<P>
1140By adding certain characters to your L<SMALL>OG</SMALL>SQLT<SMALL>RANSFER</SMALL>L<SMALL>OG</SMALL>F<SMALL>ORMAT</SMALL>
1141string you can tell mod_log_sql to log the SSL cipher, the SSL keysize
1142of the connection, and the maximum keysize that was available. This
1143would let you tell, for example, which clients were using only export-grade
1144security to access your secure software area.
1145
1146<P>
1147You can compile mod_log_sql with SSL logging support if you have
1148the right packages installed. If you already have an SSL-enabled Apache
1149then you by definition have the correct packages already installed:
1150OpenSSL and mod_ssl.
1151
1152<P>
1153You need to ensure that your database is set up to log the SSL data.
1154Issue the following commands to MySQL if your access table does not
1155already have them:
1156
1157<P>
1158
1159<DL COMPACT>
1160<DT>
1161<DD>alter&nbsp;table&nbsp;access_log&nbsp;add&nbsp;column&nbsp;ssl_cipher&nbsp;varchar(25);
1162
1163<P>
1164alter&nbsp;table&nbsp;access_log&nbsp;add&nbsp;column&nbsp;ssl_keysize&nbsp;smallint&nbsp;unsigned;
1165
1166<P>
1167alter&nbsp;table&nbsp;access_log&nbsp;add&nbsp;column&nbsp;ssl_maxkeysize&nbsp;smallint&nbsp;unsigned;
1168</DD>
1169</DL>Finally configure httpd.conf to activate the SSL fields. Note that
1170this is only meaningful in a VirtualHost that is set up for SSL.
1171
1172<P>
1173
1174<DL COMPACT>
1175<DT>
1176<DD>&lt;VirtualHost&nbsp;1.2.3.4:443&gt;&nbsp;
1177
1178<P>
1179&nbsp;LogSQLTransferLogFormat&nbsp;AbHhmRSsTUuvc<B>Qqz</B>&nbsp;
1180
1181<P>
1182&lt;/VirtualHost&gt;
1183</DD>
1184</DL>The last three characters (Qqz) in the directive are the SSL ones;
1185see section <A HREF="node4.html#sub:Frmat">3.6.17</A> in the directives documentation for details
1186of the L<SMALL>OG</SMALL>SQLT<SMALL>RANSFER</SMALL>L<SMALL>OG</SMALL>F<SMALL>ORMAT</SMALL> directive.
1187
1188<P>
1189Restart Apache, then perform some hits on your server. Then run the
1190following select statement:
1191
1192<P>
1193
1194<DL COMPACT>
1195<DT>
1196<DD>mysql&gt;&nbsp;select&nbsp;remote_host,request_uri,ssl_cipher,ssl_keysize,ssl_maxkeysize
1197
1198<P>
1199from&nbsp;access_log&nbsp;where&nbsp;ssl_cipher&nbsp;is&nbsp;not&nbsp;null;
1200
1201<P>
1202</DD>
1203</DL>
1204<DIV ALIGN="CENTER">
1205<TABLE CELLPADDING=3 BORDER="1">
1206<TR><TD ALIGN="LEFT">remote_host</TD>
1207<TD ALIGN="LEFT">request_uri</TD>
1208<TD ALIGN="LEFT">ssl_cipher</TD>
1209<TD ALIGN="LEFT">ssl_keysize</TD>
1210<TD ALIGN="LEFT">ssl_maxkeysize</TD>
1211</TR>
1212<TR><TD ALIGN="LEFT">216.190.52.4</TD>
1213<TD ALIGN="LEFT">/dir/somefile.html</TD>
1214<TD ALIGN="LEFT">RC4-MD5</TD>
1215<TD ALIGN="LEFT">128</TD>
1216<TD ALIGN="LEFT">128</TD>
1217</TR>
1218<TR><TD ALIGN="LEFT">216.190.52.4</TD>
1219<TD ALIGN="LEFT">/dir/somefile.gif</TD>
1220<TD ALIGN="LEFT">RC4-MD5</TD>
1221<TD ALIGN="LEFT">128</TD>
1222<TD ALIGN="LEFT">128</TD>
1223</TR>
1224<TR><TD ALIGN="LEFT">216.190.52.4</TD>
1225<TD ALIGN="LEFT">/dir/somefile.jpg</TD>
1226<TD ALIGN="LEFT">RC4-MD5</TD>
1227<TD ALIGN="LEFT">128</TD>
1228<TD ALIGN="LEFT">128</TD>
1229</TR>
1230</TABLE>
1231</DIV>
1232
1233<P>
1234
1235<DL COMPACT>
1236<DT>
1237<DD>
1238</DD>
1239</DL>
1240<P>
1241<HR>
1242<!--Navigation Panel-->
1243<A NAME="tex2html219"
1244 HREF="node6.html">
1245<IMG WIDTH="37" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="next" SRC="next.png"></A>
1246<A NAME="tex2html215"
1247 HREF="documentation.html">
1248<IMG WIDTH="26" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="up" SRC="up.png"></A>
1249<A NAME="tex2html209"
1250 HREF="node4.html">
1251<IMG WIDTH="63" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="previous" SRC="prev.png"></A>
1252<A NAME="tex2html217"
1253 HREF="node1.html">
1254<IMG WIDTH="65" HEIGHT="24" ALIGN="BOTTOM" BORDER="0" ALT="contents" SRC="contents.png"></A>
1255<BR>
1256<B> Next:</B> <A NAME="tex2html220"
1257 HREF="node6.html">About this document ...</A>
1258<B> Up:</B> <A NAME="tex2html216"
1259 HREF="documentation.html">Installing and Running mod_log_sql</A>
1260<B> Previous:</B> <A NAME="tex2html210"
1261 HREF="node4.html">3 Configuration</A>
1262 &nbsp; <B> <A NAME="tex2html218"
1263 HREF="node1.html">Contents</A></B>
1264<!--End of Navigation Panel-->
1265<ADDRESS>
1266Chris Powell
12672002-12-18
1268</ADDRESS>
1269</BODY>
1270</HTML>
generated by cgit (git 2.34.1) at 2025-12-08 21:21:13 +0000