diff options
Diffstat (limited to 'Documentation')
-rw-r--r-- | Documentation/documentation.lyx | 395 |
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 | ||
425 | Edit Makefile for your system: | 425 | You 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 | ||
439 | The location of your MySQL libraries, find using 'locate libmysqlclient' | 439 | The location of your MySQL libraries, find using 'locate libmysqlclient.so' |
440 | \layout Itemize | 440 | \layout Itemize |
441 | 441 | ||
442 | The location of your MySQL header files, find using 'locate mysql.h' | 442 | The 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 | ||
446 | This is | 446 | |
447 | \series bold | 447 | \series bold |
448 | optional | 448 | Optional |
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 | ||
455 | The location of your SSL header files, find using 'locate mod_ssl.h' | 455 | The location of your SSL header files, find using 'locate mod_ssl.h' |
456 | \layout Itemize | ||
457 | |||
458 | The 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 | ||
459 | Note: you do | 462 | You do |
460 | \series bold | 463 | \series bold |
461 | not | 464 | not |
462 | \series default | 465 | \series default |
463 | need to compile SSL support into mod_log_sql in order to simply use it with | 466 | need 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 | 470 | if you want to log SSL-specific data |
471 | \series default | ||
472 | such as the cipher type. | ||
473 | \end_deeper | ||
474 | \layout Enumerate | ||
468 | 475 | ||
469 | Now that you know these things, edit Makefile and replace the stock values | 476 | Now 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 | ||
473 | IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS by putting | 481 | IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS by putting |
@@ -514,13 +522,52 @@ Instruct apxs to install the DSO. | |||
514 | You should see output similar to the following: | 522 | You 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 | |||
528 | cp mod_log_sql.so /usr/local/Apache/libexec/mod_log_sql.so | ||
529 | \layout LyX-Code | ||
530 | |||
531 | chmod 755 /usr/local/Apache/libexec/mod_log_sql.so | ||
532 | \end_deeper | ||
533 | \layout Enumerate | ||
534 | |||
535 | Load and activate the module in httpd.conf: | ||
536 | \begin_deeper | ||
537 | \layout Enumerate | ||
538 | |||
539 | Insert this line in the same area as other logging modules, e.g. | ||
540 | near | ||
541 | \begin_inset Quotes eld | ||
542 | \end_inset | ||
543 | |||
544 | LoadModule 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 | ||
520 | cp mod_log_sql.so /usr/local/Apache/libexec/mod_log_sql.so | 552 | LoadModule sql_log_module libexec/mod_log_sql.so |
553 | \end_deeper | ||
554 | \layout Enumerate | ||
555 | |||
556 | Insert this line in the same area as other logging modules, e.g. | ||
557 | near | ||
558 | \begin_inset Quotes eld | ||
559 | \end_inset | ||
560 | |||
561 | AddModule 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 | ||
523 | chmod 755 /usr/local/Apache/libexec/mod_log_sql.so | 569 | AddModule 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 | ||
541 | in your httpd.conf file. | 588 | in 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 | |||
593 | ssl_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 | ||
555 | Now skip below to section | 612 | Now 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 | ||
596 | Edit Makefile for your system. | 653 | You 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' | |||
615 | The location of your MySQL header files, find using 'locate mysql.h' | 672 | The location of your MySQL header files, find using 'locate mysql.h' |
616 | \layout Itemize | 673 | \layout Itemize |
617 | 674 | ||
618 | The location of your MySQL libraries, find using 'locate libmysqlclient' | 675 | The location of your MySQL libraries, find using 'locate libmysqlclient.so' |
619 | \end_deeper | 676 | \end_deeper |
620 | \layout Enumerate | 677 | \layout Enumerate |
621 | 678 | ||
622 | This is | 679 | |
623 | \series bold | 680 | \series bold |
624 | optional | 681 | Optional |
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 | ||
631 | The location of your mod_ssl header files, find using 'locate mod_ssl.h' | 688 | The location of your mod_ssl header files, find using 'locate mod_ssl.h' |
632 | \layout Itemize | 689 | \layout Itemize |
633 | 690 | ||
634 | The location of your OpenSSL header files, find using 'locate x509.h' | 691 | The location of your db1 header files, find using 'locate ndbm.h' |
635 | \layout Itemize | ||
636 | |||
637 | The 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 | ||
641 | Note: you do | 695 | You do |
642 | \series bold | 696 | \series bold |
643 | not | 697 | not |
644 | \series default | 698 | \series default |
645 | need to compile SSL support into mod_log_sql in order to simply use it with | 699 | need 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 | 703 | if you want to log SSL-specific data |
704 | \series default | ||
705 | such as the cipher type. | ||
706 | \end_deeper | ||
707 | \layout Enumerate | ||
650 | 708 | ||
651 | Now that you know these things, edit Makefile and replace the stock values | 709 | Now 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 | ||
655 | IMPORTANT: If you are not logging SSL info, comment out MODSSLHDRS, OPNSSLHDRS | 714 | IMPORTANT: 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. | |||
668 | You should see output similar to the following: | 729 | You should see output similar to the following: |
669 | \layout LyX-Code | 730 | \layout LyX-Code |
670 | 731 | ||
671 | gcc -fPIC -O2 -Wall -I/usr/local/Apache/include -I/usr/include/mysql -I/usr/lo... | 732 | gcc -fpic -O2 -Wall -I/usr/local/Apache/include -I/usr/include/mysql -I/usr/lo... |
672 | \layout Standard | 733 | \layout Standard |
673 | 734 | ||
674 | You should see no errors and have a new file called "mod_log_sql.o" in your | 735 | You 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 | |||
2402 | Optimizing for a busy database | ||
2403 | \layout Standard | ||
2404 | |||
2405 | A 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 | |||
2412 | delayed inserts, | ||
2413 | \begin_inset Quotes erd | ||
2414 | \end_inset | ||
2415 | |||
2416 | which are described as follows in the MySQL documentation: | ||
2417 | \layout Quote | ||
2418 | |||
2419 | The 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 | ||
2422 | ly 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 | |||
2427 | INSERT 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 | |||
2434 | When 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 | |||
2438 | Another 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 | |||
2444 | The general disadvantages of delayed inserts are: | ||
2445 | \layout Enumerate | ||
2446 | |||
2447 | The 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 | |||
2453 | There 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 | |||
2457 | The MySQL documentation concludes, | ||
2458 | \begin_inset Quotes eld | ||
2459 | \end_inset | ||
2460 | |||
2461 | This 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 | |||
2469 | If 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%'; | |||
5391 | mysql> optimize table access_log; | 5527 | mysql> optimize table access_log; |
5392 | \layout Subsection | 5528 | \layout Subsection |
5393 | 5529 | ||
5530 | Do I need to be worried about all the running MySQL children? Will holding | ||
5531 | open | ||
5532 | \emph on | ||
5533 | n | ||
5534 | \emph default | ||
5535 | Apache -> MySQL connections consume a lot of memory? | ||
5536 | \layout Standard | ||
5537 | |||
5538 | Short answer: you shouldn't be worried. | ||
5539 | \layout Standard | ||
5540 | |||
5541 | Long answer: you might be evaluating at the output of | ||
5542 | \begin_inset Quotes eld | ||
5543 | \end_inset | ||
5544 | |||
5545 | ps -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 | |||
5567 | Fortunately 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 | |||
5572 | copy-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 | |||
5582 | ps | ||
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 | |||
5593 | read-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 | |||
5603 | A memory page is only duplicated when it needs to be written to, hence | ||
5604 | \begin_inset Quotes eld | ||
5605 | \end_inset | ||
5606 | |||
5607 | copy-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 | |||
5616 | ps | ||
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 | |||
5624 | cost | ||
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 | ||
5631 | not | ||
5632 | \emph on | ||
5633 | |||
5634 | \emph default | ||
5635 | correct | ||
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 | |||
5646 | copy-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 | ||
5653 | do not | ||
5654 | \series default | ||
5655 | occupy 880MB of RAM. | ||
5656 | \layout Standard | ||
5657 | |||
5658 | The bottom line: although tangible, there is not much memory cost associated | ||
5659 | with spawning off extra httpd or mysqld children. | ||
5660 | \layout Subsection | ||
5661 | |||
5662 | My database cannot handle all the open connections from mod_log_sql, is | ||
5663 | there anything I can do? | ||
5664 | \layout Standard | ||
5665 | |||
5666 | The rule of thumb: if you have | ||
5667 | \emph on | ||
5668 | n | ||
5669 | \emph default | ||
5670 | webservers each configured to support | ||
5671 | \emph on | ||
5672 | y | ||
5673 | \emph default | ||
5674 | |||
5675 | \noun on | ||
5676 | MaxClients | ||
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 | ||
5684 | in the worst case. | ||
5685 | |||
5686 | \emph default | ||
5687 | Certainly you must use common sense, consider reasonable traffic expectations | ||
5688 | and structure things accordingly. | ||
5689 | \layout Standard | ||
5690 | |||
5691 | Tweaking 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 | |||
5698 | Please remember that mod_log_sql's overriding principle is | ||
5699 | \series bold | ||
5700 | performance | ||
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 | |||
5713 | My webservers cannot handle all the traffic that my site receives, is there | ||
5714 | anything I can do? | ||
5715 | \layout Standard | ||
5716 | |||
5717 | If 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 | |||
5394 | Who's using mod_log_sql? | 5723 | Who's using mod_log_sql? |
5395 | \layout Standard | 5724 | \layout Standard |
5396 | 5725 | ||