summaryrefslogtreecommitdiffstats
path: root/Documentation/documentation.lyx
diff options
context:
space:
mode:
authorGravatar Christopher Powell 2002-11-27 07:13:59 +0000
committerGravatar Christopher Powell 2002-11-27 07:13:59 +0000
commitef6cec547cbb21caa7204095ee914cba89f6247b (patch)
tree75b78ac843e03ff0632aa2b82b8e5d0cc2fc9d9f /Documentation/documentation.lyx
parentc00607cbde2fc719bfc1af4e9c58a9eb4eccf691 (diff)
Further Makefile reorg/cleanup, a new option to support delayed inserts
with accompanying documentation.
Diffstat (limited to 'Documentation/documentation.lyx')
-rw-r--r--Documentation/documentation.lyx395
1 files changed, 362 insertions, 33 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,
422\end_deeper 422\end_deeper
423\layout Enumerate 423\layout Enumerate
424 424
425Edit Makefile for your system: 425You must know the paths to some installed software before continuing.
426\begin_deeper 426\begin_deeper
427\layout Enumerate 427\layout Enumerate
428 428
@@ -436,38 +436,46 @@ The location where you installed Apache -- usually /usr/local/apache, 'locate
436 apxs' can help you find it. 436 apxs' can help you find it.
437\layout Itemize 437\layout Itemize
438 438
439The location of your MySQL libraries, find using 'locate libmysqlclient' 439The location of your MySQL libraries, find using 'locate libmysqlclient.so'
440\layout Itemize 440\layout Itemize
441 441
442The location of your MySQL header files, find using 'locate mysql.h' 442The location of your MySQL header files, find using 'locate mysql.h'
443\end_deeper 443\end_deeper
444\layout Enumerate 444\layout Enumerate
445 445
446This is 446
447\series bold 447\series bold
448optional 448Optional
449\series default 449\series default
450: if you have included mod_ssl in Apache and want to log SSL data such as 450: if you compiled mod_ssl for Apache and want to log SSL data such as 'keysize'
451 keysize and cipher type: 451 and 'cipher type':
452\begin_deeper 452\begin_deeper
453\layout Itemize 453\layout Itemize
454 454
455The location of your SSL header files, find using 'locate mod_ssl.h' 455The location of your SSL header files, find using 'locate mod_ssl.h'
456\layout Itemize
457
458The location of your db1 header files, find using 'locate ndbm.h'
456\end_deeper 459\end_deeper
457\layout Standard 460\layout Standard
458 461
459Note: you do 462You do
460\series bold 463\series bold
461not 464not
462\series default 465\series default
463need to compile SSL support into mod_log_sql in order to simply use it with 466need to compile SSL support into mod_log_sql in order to simply use it with
464 a secure site. 467 a secure site.
465 You only need to compile SSL support into mod_log_sql if you want to log 468 You only need to compile SSL support into mod_log_sql
466 SSL-specific data such as cipher type. 469\series bold
467\layout Standard 470if you want to log SSL-specific data
471\series default
472such as the cipher type.
473\end_deeper
474\layout Enumerate
468 475
469Now that you know these things, edit Makefile and replace the stock values 476Now that you know these things, edit Makefile and replace the stock values
470 with your own. 477 with your own.
478\begin_deeper
471\layout Standard 479\layout Standard
472 480
473IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS by putting 481IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS by putting
@@ -514,13 +522,52 @@ Instruct apxs to install the DSO.
514You should see output similar to the following: 522You should see output similar to the following:
515\layout LyX-Code 523\layout LyX-Code
516 524
517/usr/local/Apache/bin/apxs -i mod_log_sql.so 525/usr/local/Apache/bin/apxs -i mod_log_sql.so
526\layout LyX-Code
527
528cp mod_log_sql.so /usr/local/Apache/libexec/mod_log_sql.so
529\layout LyX-Code
530
531chmod 755 /usr/local/Apache/libexec/mod_log_sql.so
532\end_deeper
533\layout Enumerate
534
535Load and activate the module in httpd.conf:
536\begin_deeper
537\layout Enumerate
538
539Insert this line in the same area as other logging modules, e.g.
540 near
541\begin_inset Quotes eld
542\end_inset
543
544LoadModule config_log_module
545\begin_inset Quotes erd
546\end_inset
547
548:
549\begin_deeper
518\layout LyX-Code 550\layout LyX-Code
519 551
520cp mod_log_sql.so /usr/local/Apache/libexec/mod_log_sql.so 552LoadModule sql_log_module libexec/mod_log_sql.so
553\end_deeper
554\layout Enumerate
555
556Insert this line in the same area as other logging modules, e.g.
557 near
558\begin_inset Quotes eld
559\end_inset
560
561AddModule mod_log_config.c
562\begin_inset Quotes erd
563\end_inset
564
565:
566\begin_deeper
521\layout LyX-Code 567\layout LyX-Code
522 568
523chmod 755 /usr/local/Apache/libexec/mod_log_sql.so 569AddModule mod_log_sql.c
570\end_deeper
524\end_deeper 571\end_deeper
525\layout Enumerate 572\layout Enumerate
526 573
@@ -539,6 +586,15 @@ LoadModule sql_log_module libexec/mod_log_sql.so
539\layout Standard 586\layout Standard
540 587
541in your httpd.conf file. 588in your httpd.conf file.
589 If they are out of order, simply cut-and-paste the
590\begin_inset Quotes eld
591\end_inset
592
593ssl_module
594\begin_inset Quotes erd
595\end_inset
596
597 section so that it is at the top.
542 If you do not, you will get this error when you start Apache: 598 If you do not, you will get this error when you start Apache:
543\layout LyX-Code 599\layout LyX-Code
544 600
@@ -549,7 +605,8 @@ in your httpd.conf file.
549\layout Standard 605\layout Standard
550 606
551(mod_log_sql has a dependency on mod_ssl for SSL symbols. 607(mod_log_sql has a dependency on mod_ssl for SSL symbols.
552 If the statements are out of order, mod_log_sql can't recognize those symbols.) 608 If the statements are out of order, mod_log_sql cannot recognize those
609 symbols.)
553\layout Standard 610\layout Standard
554 611
555Now skip below to section 612Now skip below to section
@@ -593,7 +650,7 @@ Unpack the archive into a working directory.
593 650
594\end_inset 651\end_inset
595 652
596Edit Makefile for your system. 653You must know the paths to some installed software before continuing.
597\begin_deeper 654\begin_deeper
598\layout Enumerate 655\layout Enumerate
599 656
@@ -615,46 +672,50 @@ The location of your Apache *sources*, find using 'locate ABOUT_APACHE'
615The location of your MySQL header files, find using 'locate mysql.h' 672The location of your MySQL header files, find using 'locate mysql.h'
616\layout Itemize 673\layout Itemize
617 674
618The location of your MySQL libraries, find using 'locate libmysqlclient' 675The location of your MySQL libraries, find using 'locate libmysqlclient.so'
619\end_deeper 676\end_deeper
620\layout Enumerate 677\layout Enumerate
621 678
622This is 679
623\series bold 680\series bold
624optional 681Optional
625\series default 682\series default
626: if you have included mod_ssl in Apache and want to log SSL data such as 683: if you compiled mod_ssl for Apache and want to log SSL data such as 'keysize'
627 keysize and cipher type: 684 and 'cipher type':
628\begin_deeper 685\begin_deeper
629\layout Itemize 686\layout Itemize
630 687
631The location of your mod_ssl header files, find using 'locate mod_ssl.h' 688The location of your mod_ssl header files, find using 'locate mod_ssl.h'
632\layout Itemize 689\layout Itemize
633 690
634The location of your OpenSSL header files, find using 'locate x509.h' 691The location of your db1 header files, find using 'locate ndbm.h'
635\layout Itemize
636
637The location of your db1 header files, find using 'locate mpool.h'
638\end_deeper 692\end_deeper
639\layout Standard 693\layout Standard
640 694
641Note: you do 695You do
642\series bold 696\series bold
643not 697not
644\series default 698\series default
645need to compile SSL support into mod_log_sql in order to simply use it with 699need to compile SSL support into mod_log_sql in order to simply use it with
646 a secure site. 700 a secure site.
647 You only need to compile SSL support into mod_log_sql if you want to log 701 You only need to compile SSL support into mod_log_sql
648 SSL-specific data such as cipher type. 702\series bold
649\layout Standard 703if you want to log SSL-specific data
704\series default
705 such as the cipher type.
706\end_deeper
707\layout Enumerate
650 708
651Now that you know these things, edit Makefile and replace the stock values 709Now that you know these things, edit Makefile and replace the stock values
652 with your own. 710 with your own.
711\begin_deeper
653\layout Standard 712\layout Standard
654 713
655IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS, OPNSSLHDRS 714IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS by putting
656 and DB1HDRS by putting a # character in front of each one, e.g. 715 a # character in front of it:
657 #OPNSSLHDRS=/usr/include/... 716\layout LyX-Code
717
718#MODSSLHDRS=/usr/include/...
658\end_deeper 719\end_deeper
659\layout Enumerate 720\layout Enumerate
660 721
@@ -668,7 +729,7 @@ Compile the module.
668You should see output similar to the following: 729You should see output similar to the following:
669\layout LyX-Code 730\layout LyX-Code
670 731
671gcc -fPIC -O2 -Wall -I/usr/local/Apache/include -I/usr/include/mysql -I/usr/lo... 732gcc -fpic -O2 -Wall -I/usr/local/Apache/include -I/usr/include/mysql -I/usr/lo...
672\layout Standard 733\layout Standard
673 734
674You should see no errors and have a new file called "mod_log_sql.o" in your 735You should see no errors and have a new file called "mod_log_sql.o" in your
@@ -1772,7 +1833,7 @@ zerberus.aiacs.net
1772 1833
1773\layout Standard 1834\layout Standard
1774 1835
1775/index.html 1836/mod_log_sql/index.html
1776\end_inset 1837\end_inset
1777</cell> 1838</cell>
1778<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none"> 1839<cell alignment="center" valignment="top" topline="true" leftline="true" rightline="true" usebox="none">
@@ -2336,6 +2397,81 @@ LogSQLTransferLogTable.)
2336\layout LyX-Code 2397\layout LyX-Code
2337 2398
2338</VirtualHost> 2399</VirtualHost>
2400\layout Subsubsection
2401
2402Optimizing for a busy database
2403\layout Standard
2404
2405A busy MySQL database will have SELECT statements running concurrently with
2406 INSERT and UPDATE statements.
2407 A long-running SELECT can block INSERTs, therefore will block mod_log_sql.
2408 This can be solved by compiling mod_log_sql for
2409\begin_inset Quotes eld
2410\end_inset
2411
2412delayed inserts,
2413\begin_inset Quotes erd
2414\end_inset
2415
2416 which are described as follows in the MySQL documentation:
2417\layout Quote
2418
2419The DELAYED option for the INSERT statement is a MySQL-specific option that
2420 is very useful if you have clients that can't wait for the INSERT to complete.
2421 This is a common problem when you use MySQL for logging and you also periodical
2422ly run SELECT and UPDATE statements that take a long time to complete.
2423 DELAYED was introduced in MySQL Version 3.22.15.
2424 It is a MySQL extension to ANSI SQL92.
2425\layout Quote
2426
2427INSERT DELAYED only works with ISAM and MyISAM tables.
2428 Note that as MyISAM tables supports concurrent SELECT and INSERT, if there
2429 is no free blocks in the middle of the data file, you very seldom need
2430 to use INSERT DELAYED with MyISAM.
2431
2432\layout Quote
2433
2434When you use INSERT DELAYED, the client will get an OK at once and the row
2435 will be inserted when the table is not in use by any other thread.
2436\layout Quote
2437
2438Another major benefit of using INSERT DELAYED is that inserts from many
2439 clients are bundled together and written in one block.
2440 This is much faster than doing many separate inserts.
2441
2442\layout Standard
2443
2444The general disadvantages of delayed inserts are:
2445\layout Enumerate
2446
2447The queued rows are only stored in memory until they are inserted into the
2448 table.
2449 If mysqld dies unexpectedly, any queued rows that weren't written to disk
2450 are lost.
2451\layout Enumerate
2452
2453There is additional overhead for the server to handle a separate thread
2454 for each table on which you use INSERT DELAYED.
2455\layout Standard
2456
2457The MySQL documentation concludes,
2458\begin_inset Quotes eld
2459\end_inset
2460
2461This means that you should only use INSERT DELAYED when you are really sure
2462 you need it!
2463\begin_inset Quotes erd
2464\end_inset
2465
2466
2467\layout Standard
2468
2469If you are experiencing issues which could be solved by delayed inserts,
2470 uncomment the #MYSQLDELAYED line in the Makefile by removing the # that
2471 is in front of it.
2472 Recompile and reinstall your module.
2473 All regular INSERT statements are now INSERT DELAYED, and you should see
2474 no more blocking of the module.
2339\layout Subsection 2475\layout Subsection
2340 2476
2341 2477
@@ -5391,6 +5527,199 @@ mysql> delete from access_log where agent like 'ApacheBench%';
5391mysql> optimize table access_log; 5527mysql> optimize table access_log;
5392\layout Subsection 5528\layout Subsection
5393 5529
5530Do I need to be worried about all the running MySQL children? Will holding
5531 open
5532\emph on
5533n
5534\emph default
5535 Apache -> MySQL connections consume a lot of memory?
5536\layout Standard
5537
5538Short answer: you shouldn't be worried.
5539\layout Standard
5540
5541Long answer: you might be evaluating at the output of
5542\begin_inset Quotes eld
5543\end_inset
5544
5545ps -aufxw
5546\begin_inset Quotes erd
5547\end_inset
5548
5549 and becoming alarmed at all the 7MB httpd processes or 22MB mysqld children
5550 that you see.
5551 Don't be alarmed
5552\emph on
5553.
5554
5555\emph default
5556 It's true that mod_log_sql opens and holds open many MySQL connections:
5557 each httpd child maintains one open database connection (and holds it open
5558 for performance reasons).
5559 Four webservers, each running 20 Apache children, will hold open 80 MySQL
5560 connections, which means that your MySQL server needs to handle 80 simultaneous
5561 connections.
5562 In truth, your MySQL server needs to handle far more than that if traffic
5563 to your website spikes and the Apache webservers spawn off an additional
5564 30 children each...
5565\layout Standard
5566
5567Fortunately the cost reported by 'ps -aufxw' is indeed deceptive due to
5568 an OS memory-management feature called
5569\begin_inset Quotes eld
5570\end_inset
5571
5572copy-on-write.
5573\begin_inset Quotes erd
5574\end_inset
5575
5576 This is a memory-management technique used by Unix-based systems.
5577 When you have a number of identical child processes (e.g.
5578 Apache, MySQL), it would appear in
5579\begin_inset Quotes eld
5580\end_inset
5581
5582ps
5583\begin_inset Quotes erd
5584\end_inset
5585
5586 as though each one occupies a great deal of RAM -- as much as 7MB per httpd
5587 child! In actuality each additional child only occupies a small bit of
5588 extra memory -- most of the memory pages are common to each child and therefore
5589 shared in a
5590\begin_inset Quotes eld
5591\end_inset
5592
5593read-only
5594\begin_inset Quotes erd
5595\end_inset
5596
5597 fashion.
5598 The OS can get away with this because the majority of memory pages for
5599 one child are identical across all children.
5600
5601\layout Standard
5602
5603A memory page is only duplicated when it needs to be written to, hence
5604\begin_inset Quotes eld
5605\end_inset
5606
5607copy-on-write.
5608\begin_inset Quotes erd
5609\end_inset
5610
5611 The result is efficiency and decreased memory consumption.
5612
5613\begin_inset Quotes eld
5614\end_inset
5615
5616ps
5617\begin_inset Quotes erd
5618\end_inset
5619
5620 may report 7MB per child, but it might really only
5621\begin_inset Quotes eld
5622\end_inset
5623
5624cost
5625\begin_inset Quotes erd
5626\end_inset
5627
5628 900K of extra memory to add one more child.
5629 It is
5630\series bold
5631not
5632\emph on
5633
5634\emph default
5635correct
5636\series default
5637 to assume that 20 Apache children with a VSZ of 7MB each equals
5638\begin_inset Formula $(20\times 7MB)$
5639\end_inset
5640
5641 of memory consumption -- the real answer is much, much lower.
5642 The same
5643\begin_inset Quotes eld
5644\end_inset
5645
5646copy-on-write
5647\begin_inset Quotes erd
5648\end_inset
5649
5650 rules apply to all your MySQL children: 40 mysqld children @ 22MB each
5651
5652\series bold
5653do not
5654\series default
5655 occupy 880MB of RAM.
5656\layout Standard
5657
5658The bottom line: although tangible, there is not much memory cost associated
5659 with spawning off extra httpd or mysqld children.
5660\layout Subsection
5661
5662My database cannot handle all the open connections from mod_log_sql, is
5663 there anything I can do?
5664\layout Standard
5665
5666The rule of thumb: if you have
5667\emph on
5668n
5669\emph default
5670webservers each configured to support
5671\emph on
5672y
5673\emph default
5674
5675\noun on
5676MaxClients
5677\noun default
5678, then your database must be able to handle
5679\begin_inset Formula $n\times y$
5680\end_inset
5681
5682 simultenous connections
5683\emph on
5684in the worst case.
5685
5686\emph default
5687Certainly you must use common sense, consider reasonable traffic expectations
5688 and structure things accordingly.
5689\layout Standard
5690
5691Tweaking my.cnf to scale to high connection loads is imperative.
5692 But if hardware limitations prevent your MySQL server from gracefully handling
5693 the number of incoming connections, it would be beneficial to upgrade the
5694 memory or CPU on that server in order to handle the load.
5695
5696\layout Standard
5697
5698Please remember that mod_log_sql's overriding principle is
5699\series bold
5700performance
5701\series default
5702 -- that is what the target audience demands and expects.
5703 Other database logging solutions do not open and maintain many database
5704 connections, but their performance suffers drastically.
5705 For example, pgLOGd funnels all log connections through a separate daemon
5706 that connects to the database, but that bottlenecks the entire process.
5707 mod_log_sql achieves performance numbers an order of magnitude greater
5708 than the alternatives because it dispenses with the overhead associated
5709 with rapid connection cycling, and it doesn't attempt to shoehorn all the
5710 database traffic through a single extra daemon or proxy process.
5711\layout Subsection
5712
5713My webservers cannot handle all the traffic that my site receives, is there
5714 anything I can do?
5715\layout Standard
5716
5717If you have exhausted all the tuning possibilities on your existing server,
5718 it is probably time you evaluated the benefits of clustering two or more
5719 webservers together in a load-balanced fashion.
5720 In fact, users of such a setup are mod_log_sql's target audience!
5721\layout Subsection
5722
5394Who's using mod_log_sql? 5723Who's using mod_log_sql?
5395\layout Standard 5724\layout Standard
5396 5725