mod_log_sql 2.0 Documentation

Home » Projects » Apache » Mod_log_sql » Docs-2.0 » mod_log_sql 2.0 Documentation

Prev: How to tune logging with run-time directives --- Up: Configuration --- Next: Logging many-to-one data in separate tables

Advanced logging scenarios

Using the module in an ISP environment

mod_log_sql has three basic tiers of operation:

  1. The administrator creates all necessary tables by hand and configures each Apache VirtualHost by hand. (LogSQLCreateTables Off)

  2. The module is permitted to create necessary tables on-the-fly, but the administrator configures each Apache VirtualHost by hand. (LogSQLCreateTables On)

  3. The module is permitted to create all necessary tables and to make intelligent, on-the-fly configuration of each VirtualHost. (LogSQLMassVirtualHosting On)

Many users are happy to use the module in its most minimal form: they hand-create any necessary tables (using "create_tables.sql"), and they configure each VirtualHost by hand to suit their needs. However, some administrators need extra features due to a large and growing number of VirtualHosts. The LogSQLMassVirtualHosting directive activates module capabilities that make it far easier to manage an ISP environment, or any situation characterized by a large and varying number of virtual servers.

  • the on-the-fly table creation feature is activated automatically

  • the transfer log table name is dynamically set from the virtual host's name (example: a virtual host "www.grubbybaby.com" gets logged to table "access_www_grubbybaby_com")

There are numerous benefits. The admin will not need to create new tables for every new VirtualHost. (Although the admin will still need to drop the tables of virtual hosts that are removed.) The admin will not need to set LogSQLTransferLogTable for each virtual host -- it will be configured automatically based on the host's name. Because each virtual host will log to its own segregated table, data about one virtual server will segregate from others; an admin can grant users access to the tables they need, and they will be unable to view data about another user's virtual host.

In an ISP scenario the admin is likely to have a cluster of many front-end webservers logging to a back-end database. mod_log_sql has a feature that permits analysis of how well the web servers are loadbalancing: the LogSQLMachineID directive. The administrator uses this directive to assign a unique identifier to each machine in the web cluster, e.g. "LogSQLMachineID web01," "LogSQLMachineID web02," etc. Used in conjunction with the 'M' character in LogSQLTransferLogFormat, each entry in the SQL log will include the machine ID of the machine that created the entry. This permits the administrator to count the entries made by each particular machine and thereby analyze the front-end loadbalancing algorithm.

2. Logging many-to-one data in separate tables
3. Using the same database for production and test
4. Optimizing for a busy database