From ef6cec547cbb21caa7204095ee914cba89f6247b Mon Sep 17 00:00:00 2001 From: Christopher Powell Date: Wed, 27 Nov 2002 07:13:59 +0000 Subject: Further Makefile reorg/cleanup, a new option to support delayed inserts with accompanying documentation. --- Documentation/documentation.lyx | 395 ++++++++++++++++++++++++++++++++++++---- Makefile | 48 ++--- mod_log_sql.c | 46 ++--- 3 files changed, 407 insertions(+), 82 deletions(-) diff --git a/Documentation/documentation.lyx b/Documentation/documentation.lyx index 39daba8..50fceba 100644 --- a/Documentation/documentation.lyx +++ b/Documentation/documentation.lyx @@ -422,7 +422,7 @@ Perform all the following steps as root so that you have install privs, \end_deeper \layout Enumerate -Edit Makefile for your system: +You must know the paths to some installed software before continuing. \begin_deeper \layout Enumerate @@ -436,38 +436,46 @@ The location where you installed Apache -- usually /usr/local/apache, 'locate apxs' can help you find it. \layout Itemize -The location of your MySQL libraries, find using 'locate libmysqlclient' +The location of your MySQL libraries, find using 'locate libmysqlclient.so' \layout Itemize The location of your MySQL header files, find using 'locate mysql.h' \end_deeper \layout Enumerate -This is + \series bold -optional +Optional \series default -: if you have included mod_ssl in Apache and want to log SSL data such as - keysize and cipher type: +: if you compiled mod_ssl for Apache and want to log SSL data such as 'keysize' + and 'cipher type': \begin_deeper \layout Itemize The location of your SSL header files, find using 'locate mod_ssl.h' +\layout Itemize + +The location of your db1 header files, find using 'locate ndbm.h' \end_deeper \layout Standard -Note: you do +You do \series bold not \series default need to compile SSL support into mod_log_sql in order to simply use it with a secure site. - You only need to compile SSL support into mod_log_sql if you want to log - SSL-specific data such as cipher type. -\layout Standard + You only need to compile SSL support into mod_log_sql +\series bold +if you want to log SSL-specific data +\series default +such as the cipher type. +\end_deeper +\layout Enumerate Now that you know these things, edit Makefile and replace the stock values with your own. +\begin_deeper \layout Standard IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS by putting @@ -514,13 +522,52 @@ Instruct apxs to install the DSO. You should see output similar to the following: \layout LyX-Code -/usr/local/Apache/bin/apxs -i mod_log_sql.so +/usr/local/Apache/bin/apxs -i mod_log_sql.so +\layout LyX-Code + +cp mod_log_sql.so /usr/local/Apache/libexec/mod_log_sql.so +\layout LyX-Code + +chmod 755 /usr/local/Apache/libexec/mod_log_sql.so +\end_deeper +\layout Enumerate + +Load and activate the module in httpd.conf: +\begin_deeper +\layout Enumerate + +Insert this line in the same area as other logging modules, e.g. + near +\begin_inset Quotes eld +\end_inset + +LoadModule config_log_module +\begin_inset Quotes erd +\end_inset + +: +\begin_deeper \layout LyX-Code -cp mod_log_sql.so /usr/local/Apache/libexec/mod_log_sql.so +LoadModule sql_log_module libexec/mod_log_sql.so +\end_deeper +\layout Enumerate + +Insert this line in the same area as other logging modules, e.g. + near +\begin_inset Quotes eld +\end_inset + +AddModule mod_log_config.c +\begin_inset Quotes erd +\end_inset + +: +\begin_deeper \layout LyX-Code -chmod 755 /usr/local/Apache/libexec/mod_log_sql.so +AddModule mod_log_sql.c +\end_deeper \end_deeper \layout Enumerate @@ -539,6 +586,15 @@ LoadModule sql_log_module libexec/mod_log_sql.so \layout Standard in your httpd.conf file. + If they are out of order, simply cut-and-paste the +\begin_inset Quotes eld +\end_inset + +ssl_module +\begin_inset Quotes erd +\end_inset + + section so that it is at the top. If you do not, you will get this error when you start Apache: \layout LyX-Code @@ -549,7 +605,8 @@ in your httpd.conf file. \layout Standard (mod_log_sql has a dependency on mod_ssl for SSL symbols. - If the statements are out of order, mod_log_sql can't recognize those symbols.) + If the statements are out of order, mod_log_sql cannot recognize those + symbols.) \layout Standard Now skip below to section @@ -593,7 +650,7 @@ Unpack the archive into a working directory. \end_inset -Edit Makefile for your system. +You must know the paths to some installed software before continuing. \begin_deeper \layout Enumerate @@ -615,46 +672,50 @@ The location of your Apache *sources*, find using 'locate ABOUT_APACHE' The location of your MySQL header files, find using 'locate mysql.h' \layout Itemize -The location of your MySQL libraries, find using 'locate libmysqlclient' +The location of your MySQL libraries, find using 'locate libmysqlclient.so' \end_deeper \layout Enumerate -This is + \series bold -optional +Optional \series default -: if you have included mod_ssl in Apache and want to log SSL data such as - keysize and cipher type: +: if you compiled mod_ssl for Apache and want to log SSL data such as 'keysize' + and 'cipher type': \begin_deeper \layout Itemize The location of your mod_ssl header files, find using 'locate mod_ssl.h' \layout Itemize -The location of your OpenSSL header files, find using 'locate x509.h' -\layout Itemize - -The location of your db1 header files, find using 'locate mpool.h' +The location of your db1 header files, find using 'locate ndbm.h' \end_deeper \layout Standard -Note: you do +You do \series bold not \series default need to compile SSL support into mod_log_sql in order to simply use it with a secure site. - You only need to compile SSL support into mod_log_sql if you want to log - SSL-specific data such as cipher type. -\layout Standard + You only need to compile SSL support into mod_log_sql +\series bold +if you want to log SSL-specific data +\series default + such as the cipher type. +\end_deeper +\layout Enumerate Now that you know these things, edit Makefile and replace the stock values with your own. +\begin_deeper \layout Standard -IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS, OPNSSLHDRS - and DB1HDRS by putting a # character in front of each one, e.g. - #OPNSSLHDRS=/usr/include/... +IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS by putting + a # character in front of it: +\layout LyX-Code + +#MODSSLHDRS=/usr/include/... \end_deeper \layout Enumerate @@ -668,7 +729,7 @@ Compile the module. You should see output similar to the following: \layout LyX-Code -gcc -fPIC -O2 -Wall -I/usr/local/Apache/include -I/usr/include/mysql -I/usr/lo... +gcc -fpic -O2 -Wall -I/usr/local/Apache/include -I/usr/include/mysql -I/usr/lo... \layout Standard You should see no errors and have a new file called "mod_log_sql.o" in your @@ -1772,7 +1833,7 @@ zerberus.aiacs.net \layout Standard -/index.html +/mod_log_sql/index.html \end_inset @@ -2336,6 +2397,81 @@ LogSQLTransferLogTable.) \layout LyX-Code +\layout Subsubsection + +Optimizing for a busy database +\layout Standard + +A busy MySQL database will have SELECT statements running concurrently with + INSERT and UPDATE statements. + A long-running SELECT can block INSERTs, therefore will block mod_log_sql. + This can be solved by compiling mod_log_sql for +\begin_inset Quotes eld +\end_inset + +delayed inserts, +\begin_inset Quotes erd +\end_inset + + which are described as follows in the MySQL documentation: +\layout Quote + +The DELAYED option for the INSERT statement is a MySQL-specific option that + is very useful if you have clients that can't wait for the INSERT to complete. + This is a common problem when you use MySQL for logging and you also periodical +ly run SELECT and UPDATE statements that take a long time to complete. + DELAYED was introduced in MySQL Version 3.22.15. + It is a MySQL extension to ANSI SQL92. +\layout Quote + +INSERT DELAYED only works with ISAM and MyISAM tables. + Note that as MyISAM tables supports concurrent SELECT and INSERT, if there + is no free blocks in the middle of the data file, you very seldom need + to use INSERT DELAYED with MyISAM. + +\layout Quote + +When you use INSERT DELAYED, the client will get an OK at once and the row + will be inserted when the table is not in use by any other thread. +\layout Quote + +Another major benefit of using INSERT DELAYED is that inserts from many + clients are bundled together and written in one block. + This is much faster than doing many separate inserts. + +\layout Standard + +The general disadvantages of delayed inserts are: +\layout Enumerate + +The queued rows are only stored in memory until they are inserted into the + table. + If mysqld dies unexpectedly, any queued rows that weren't written to disk + are lost. +\layout Enumerate + +There is additional overhead for the server to handle a separate thread + for each table on which you use INSERT DELAYED. +\layout Standard + +The MySQL documentation concludes, +\begin_inset Quotes eld +\end_inset + +This means that you should only use INSERT DELAYED when you are really sure + you need it! +\begin_inset Quotes erd +\end_inset + + +\layout Standard + +If you are experiencing issues which could be solved by delayed inserts, + uncomment the #MYSQLDELAYED line in the Makefile by removing the # that + is in front of it. + Recompile and reinstall your module. + All regular INSERT statements are now INSERT DELAYED, and you should see + no more blocking of the module. \layout Subsection @@ -5391,6 +5527,199 @@ mysql> delete from access_log where agent like 'ApacheBench%'; mysql> optimize table access_log; \layout Subsection +Do I need to be worried about all the running MySQL children? Will holding + open +\emph on +n +\emph default + Apache -> MySQL connections consume a lot of memory? +\layout Standard + +Short answer: you shouldn't be worried. +\layout Standard + +Long answer: you might be evaluating at the output of +\begin_inset Quotes eld +\end_inset + +ps -aufxw +\begin_inset Quotes erd +\end_inset + + and becoming alarmed at all the 7MB httpd processes or 22MB mysqld children + that you see. + Don't be alarmed +\emph on +. + +\emph default + It's true that mod_log_sql opens and holds open many MySQL connections: + each httpd child maintains one open database connection (and holds it open + for performance reasons). + Four webservers, each running 20 Apache children, will hold open 80 MySQL + connections, which means that your MySQL server needs to handle 80 simultaneous + connections. + In truth, your MySQL server needs to handle far more than that if traffic + to your website spikes and the Apache webservers spawn off an additional + 30 children each... +\layout Standard + +Fortunately the cost reported by 'ps -aufxw' is indeed deceptive due to + an OS memory-management feature called +\begin_inset Quotes eld +\end_inset + +copy-on-write. +\begin_inset Quotes erd +\end_inset + + This is a memory-management technique used by Unix-based systems. + When you have a number of identical child processes (e.g. + Apache, MySQL), it would appear in +\begin_inset Quotes eld +\end_inset + +ps +\begin_inset Quotes erd +\end_inset + + as though each one occupies a great deal of RAM -- as much as 7MB per httpd + child! In actuality each additional child only occupies a small bit of + extra memory -- most of the memory pages are common to each child and therefore + shared in a +\begin_inset Quotes eld +\end_inset + +read-only +\begin_inset Quotes erd +\end_inset + + fashion. + The OS can get away with this because the majority of memory pages for + one child are identical across all children. + +\layout Standard + +A memory page is only duplicated when it needs to be written to, hence +\begin_inset Quotes eld +\end_inset + +copy-on-write. +\begin_inset Quotes erd +\end_inset + + The result is efficiency and decreased memory consumption. + +\begin_inset Quotes eld +\end_inset + +ps +\begin_inset Quotes erd +\end_inset + + may report 7MB per child, but it might really only +\begin_inset Quotes eld +\end_inset + +cost +\begin_inset Quotes erd +\end_inset + + 900K of extra memory to add one more child. + It is +\series bold +not +\emph on + +\emph default +correct +\series default + to assume that 20 Apache children with a VSZ of 7MB each equals +\begin_inset Formula $(20\times 7MB)$ +\end_inset + + of memory consumption -- the real answer is much, much lower. + The same +\begin_inset Quotes eld +\end_inset + +copy-on-write +\begin_inset Quotes erd +\end_inset + + rules apply to all your MySQL children: 40 mysqld children @ 22MB each + +\series bold +do not +\series default + occupy 880MB of RAM. +\layout Standard + +The bottom line: although tangible, there is not much memory cost associated + with spawning off extra httpd or mysqld children. +\layout Subsection + +My database cannot handle all the open connections from mod_log_sql, is + there anything I can do? +\layout Standard + +The rule of thumb: if you have +\emph on +n +\emph default +webservers each configured to support +\emph on +y +\emph default + +\noun on +MaxClients +\noun default +, then your database must be able to handle +\begin_inset Formula $n\times y$ +\end_inset + + simultenous connections +\emph on +in the worst case. + +\emph default +Certainly you must use common sense, consider reasonable traffic expectations + and structure things accordingly. +\layout Standard + +Tweaking my.cnf to scale to high connection loads is imperative. + But if hardware limitations prevent your MySQL server from gracefully handling + the number of incoming connections, it would be beneficial to upgrade the + memory or CPU on that server in order to handle the load. + +\layout Standard + +Please remember that mod_log_sql's overriding principle is +\series bold +performance +\series default + -- that is what the target audience demands and expects. + Other database logging solutions do not open and maintain many database + connections, but their performance suffers drastically. + For example, pgLOGd funnels all log connections through a separate daemon + that connects to the database, but that bottlenecks the entire process. + mod_log_sql achieves performance numbers an order of magnitude greater + than the alternatives because it dispenses with the overhead associated + with rapid connection cycling, and it doesn't attempt to shoehorn all the + database traffic through a single extra daemon or proxy process. +\layout Subsection + +My webservers cannot handle all the traffic that my site receives, is there + anything I can do? +\layout Standard + +If you have exhausted all the tuning possibilities on your existing server, + it is probably time you evaluated the benefits of clustering two or more + webservers together in a load-balanced fashion. + In fact, users of such a setup are mod_log_sql's target audience! +\layout Subsection + Who's using mod_log_sql? \layout Standard diff --git a/Makefile b/Makefile index c3257e3..d2effa0 100644 --- a/Makefile +++ b/Makefile @@ -1,27 +1,33 @@ -# $Id: Makefile,v 1.16 2002/11/19 02:59:43 helios Exp $ +# $Id: Makefile,v 1.17 2002/11/27 07:13:58 helios Exp $ -##################################### +########################################################################### # Important: -# Adjust these values as outlined in the INSTALL file. +# Adjust these values as outlined in section "Installation" in the docs. # Not all are needed at all times. -APACHEINST = /usr/local/Apache +APACHESOURCE = /usr/local/src/apache_1.3.27-dso +APACHEINST = /usr/local/Apache +APXS = $(APACHEINST)/bin/apxs + MYSQLLIBS = /usr/lib MYSQLHDRS = /usr/include/mysql -MODSSLHDRS = /usr/local/src/apache_1.3.27-dso/src/modules/ssl -APACHESOURCE = /usr/local/src/apache_1.3.27-dso -OPNSSLHDRS = /usr/include/openssl -DB1HDRS = /usr/include/db1 +#MODSSLHDRS = /usr/local/src/apache_1.3.27-dso/src/modules/ssl +DB1HDRS = /usr/include/db1 + +########################################################################### +# Don't uncomment this without reading the "Optimizing for a busy database" +# section in the documentation (under "Advanced logging scenarios"). +MYSQLDELAYED = -DWANT_DELAYED_MYSQL_INSERT -##################################### -# Shouldn't have to touch below here. +########################################################################### +# Rarely if ever have to touch below here. -MLMVERS = 1.17 -APXS = $(APACHEINST)/bin/apxs +MLMVERS = 1.18 #APXSGDB = -Wc,-g -APXSOPTS = -Wc,-O2 -Wc,-Wall -Wc,-DEAPI +APXSOPTS = -Wc,-O2 -Wc,-Wall +STATOPTS = -fpic -O2 -Wall CC = gcc INSTALL = /usr/bin/install -m 664 RM = /bin/rm @@ -32,11 +38,12 @@ LINKS = /usr/bin/links L2H = /usr/local/bin/latex2html WEBSERV = gw0.corp +STATFLAGS = -I$(APACHEINST)/include +SOFLAGS = -L$(MYSQLLIBS) -lmysqlclient -lz ifdef MODSSLHDRS - SSLDEF = -DWANT_SSL_LOGGING - CFLAGS = -fPIC -O2 -Wall -I$(APACHEINST)/include -I$(MYSQLHDRS) -I$(MODSSLHDRS) -I$(OPNSSLHDRS) $(SSLDEF) -I$(DB1HDRS) + FLAGS = -DEAPI -I$(MYSQLHDRS) $(MYSQLDELAYED) -I$(MODSSLHDRS) -I$(DB1HDRS) -DWANT_SSL_LOGGING else - CFLAGS = -fPIC -O2 -Wall -I$(APACHEINST)/include -I$(MYSQLHDRS) + FLAGS = -DEAPI -I$(MYSQLHDRS) $(MYSQLDELAYED) endif all: @@ -50,10 +57,10 @@ dso: mod_log_sql.so static: mod_log_sql.o mod_log_sql.so: mod_log_sql.c Makefile - $(APXS) $(APXSGDB) $(APXOPTS) -c -I$(MYSQLHDRS) -I$(MODSSLHDRS) $(SSLDEF) -L$(MYSQLLIBS) -lmysqlclient -lz mod_log_sql.c + $(APXS) -c $(APXSGDB) $(APXSOPTS) $(FLAGS) $(SOFLAGS) mod_log_sql.c mod_log_sql.o: mod_log_sql.c Makefile - $(CC) ${CFLAGS} -c mod_log_sql.c + $(CC) $(STATOPTS) $(FLAGS) $(STATFLAGS) -c mod_log_sql.c dsoinstall: dso $(APXS) -i mod_log_sql.so @@ -92,13 +99,10 @@ documentation: Documentation/documentation.lyx @echo "Creating PostScript docs..." @$(DVIPS) Documentation/documentation.dvi -o Documentation/documentation.ps 2>/dev/null @echo "Creating HTML docs..." - @$(L2H) -show_section_numbers -split 4 -navigation -noindex_in_navigation -contents_in_navigation -dir Documentation/HTML Documentation/documentation.tex >/dev/null 2>&1 + @$(L2H) -local_icons -show_section_numbers -split 4 -navigation -noindex_in_navigation -contents_in_navigation -dir Documentation/HTML Documentation/documentation.tex >/dev/null 2>&1 @echo "Creating plain text docs..." @$(L2H) -show_section_numbers -split 0 -dir Documentation/ Documentation/documentation.tex >/dev/null 2>&1 @$(LINKS) -dump Documentation/documentation.html > Documentation/documentation.txt 2>/dev/null @echo "Cleaning up..." @$(RM) -f Documentation/*.html Documentation/WARNINGS Documentation/*.pl Documentation/*.aux Documentation/*.css Documentation/*.toc Documentation/*.log Documentation/*.old Documentation/*.png Documentation/images.tex @$(RM) -f Documentation/HTML/WARNINGS Documentation/HTML/*.pl Documentation/HTML/*.log Documentation/HTML/*.aux Documentation/HTML/*.tex Documentation/HTML/*.old Documentation/HTML/index.html - - - diff --git a/mod_log_sql.c b/mod_log_sql.c index 6d3110c..3fc7ace 100644 --- a/mod_log_sql.c +++ b/mod_log_sql.c @@ -1,11 +1,11 @@ -/* $Id: mod_log_sql.c,v 1.18 2002/11/14 22:52:54 helios Exp $ */ +/* $Id: mod_log_sql.c,v 1.19 2002/11/27 07:13:58 helios Exp $ */ /* --------* * DEFINES * * --------*/ /* The enduser may wish to modify this */ -#undef DEBUG +#define DEBUG /* The enduser won't modify these */ #define MYSQL_ERROR(mysql) ((mysql)?(mysql_error(mysql)):"MySQL server has gone away") @@ -19,6 +19,7 @@ * ---------*/ #include #include +#include #include "httpd.h" #include "http_config.h" #include "http_log.h" @@ -57,6 +58,11 @@ char *db_pwd = NULL; char *mach_id = NULL; char *socket_file = "/tmp/mysql.sock"; unsigned int tcp_port = 3306; +#ifdef WANT_DELAYED_MYSQL_INSERT + char *insert_stmt = "insert delayed into "; +#else + char *insert_stmt = "insert into "; +#endif typedef const char *(*item_key_func) (request_rec *, char *); @@ -601,7 +607,7 @@ int open_logdb_link(server_rec* s) if (force_preserve) return 3; - if (mysql_log != NULL) + if (mysql_log) return 2; if (db_name) { @@ -609,6 +615,10 @@ int open_logdb_link(server_rec* s) mysql_log = mysql_real_connect(&sql_server, db_host, db_user, db_pwd, db_name, tcp_port, socket_file, 0); if (mysql_log) { + #ifdef DEBUG + ap_log_error(APLOG_MARK,DEBUGLEVEL,s,"HOST: '%s' PORT: '%d' DB: '%s' USER: '%s' SOCKET: '%s'", + db_host, tcp_port, db_name, db_user, socket_file); + #endif return 1; } else { #ifdef DEBUG @@ -623,28 +633,10 @@ int open_logdb_link(server_rec* s) return 0; } -#ifdef DEBUG -static int trace(void *data, const char *key, const char *val) -{ - FILE *fp; - request_rec *r = (request_rec *)data; - - fp = pfopen(r->pool, "/tmp/trace", "a"); - - if (fp) { - fprintf(fp, "Field '%s' == '%s'\n", key, val); - } - - pfclose(r->pool, fp); - - return TRUE; -} -#endif - const char *extract_table(void *data, const char *key, const char *val) { request_rec *r = (request_rec *)data; - + return ap_pstrcat(r->pool, key, " = ", val, " ", NULL); } @@ -1409,7 +1401,7 @@ int log_sql_transaction(request_rec *orig) } } if ( itemsets != "" ) { - note_query = ap_pstrcat(r->pool, "insert into `", cls->notes_table_name, "` (id, item, val) values ", itemsets, NULL); + note_query = ap_pstrcat(r->pool, insert_stmt, "`", cls->notes_table_name, "` (id, item, val) values ", itemsets, NULL); #ifdef DEBUG ap_log_error(APLOG_MARK,DEBUGLEVEL,orig->server,"mod_log_sql: note string: %s", note_query); #endif @@ -1437,7 +1429,7 @@ int log_sql_transaction(request_rec *orig) } } if ( itemsets != "" ) { - hout_query = ap_pstrcat(r->pool, "insert into `", cls->hout_table_name, "` (id, item, val) values ", itemsets, NULL); + hout_query = ap_pstrcat(r->pool, insert_stmt, "`", cls->hout_table_name, "` (id, item, val) values ", itemsets, NULL); #ifdef DEBUG ap_log_error(APLOG_MARK,DEBUGLEVEL,orig->server,"mod_log_sql: header_out string: %s", hout_query); #endif @@ -1466,7 +1458,7 @@ int log_sql_transaction(request_rec *orig) } } if ( itemsets != "" ) { - hin_query = ap_pstrcat(r->pool, "insert into `", cls->hin_table_name, "` (id, item, val) values ", itemsets, NULL); + hin_query = ap_pstrcat(r->pool, insert_stmt, "`", cls->hin_table_name, "` (id, item, val) values ", itemsets, NULL); #ifdef DEBUG ap_log_error(APLOG_MARK,DEBUGLEVEL,orig->server,"mod_log_sql: header_in string: %s", hin_query); #endif @@ -1496,7 +1488,7 @@ int log_sql_transaction(request_rec *orig) } if ( itemsets != "" ) { - cookie_query = ap_pstrcat(r->pool, "insert into `", cls->cookie_table_name, "` (id, item, val) values ", itemsets, NULL); + cookie_query = ap_pstrcat(r->pool, insert_stmt, "`", cls->cookie_table_name, "` (id, item, val) values ", itemsets, NULL); #ifdef DEBUG ap_log_error(APLOG_MARK,DEBUGLEVEL,orig->server,"mod_log_sql: cookie string: %s", cookie_query); #endif @@ -1504,7 +1496,7 @@ int log_sql_transaction(request_rec *orig) /* Set up the actual INSERT statement */ - access_query = ap_pstrcat(r->pool, "insert into `", cls->transfer_table_name, "` (", fields, ") values (", values, ")", NULL); + access_query = ap_pstrcat(r->pool, insert_stmt, "`", cls->transfer_table_name, "` (", fields, ") values (", values, ")", NULL); #ifdef DEBUG ap_log_error(APLOG_MARK,DEBUGLEVEL,r->server,"mod_log_sql: access string: %s", access_query); -- cgit