Exoprting Maven dependency jars to a folder

The following command can be used to export all the dependency jars declared in the pom file.

mvn dependency:copy-dependencies -DoutputDirectory=OUTPUT_DIR

the output_DIR  is relative to the project folder. 

More About Apache Tomcat 7

Apache Tomcat 7

More about the Cat

This article is meant for advanced programmers who is interested to know more about Tomcat; or using Tomcat for production. For novices, read "How to Install and Get Started with Tomcat".
The authoritative source of information on Tomcat is the Tomcat's documentation, available under Tomcat's "webapps\docs" directory. You may also refer to the Java Servlet, JSP and JSF specifications, as Tomcat is the Reference Implementation for these technologies.
I shall assume that Tomcat is installed in d:\myproject\tomcat, and shall denote this directory as <TOMCAT_HOME> or <CATALINA_HOME> - "Catalina" is the codename for Tomcat 5 and above.

1.  Tomcat Architecture and Configuration

1.1  Tomcat's Installed Directory Structure

Tomcat installation provides these directories:

• bin: for Tomcat's binaries and startup scripts.
• conf: global configuration applicable to all the webapps. The default installation provides:
  • One Policy File: catalina.policy for specifying security policy.
  • Two Properties Files: catalina.properties and logging.properties,
  • Four Configuration XML Files: server.xml (Tomcat main configuration file), web.xml (global web application deployment descriptors), context.xml (global Tomcat-specific configuration options) and tomcat-users.xml (a database of user, password and role for authentication and access control).
  The conf also contain a sub-directory for each engine, e.g., Catalina, which in turn contains a sub-sub-directory for each of its hosts, e.g., localhost. You can place the host-specific context information (similar to context.xml, but named as webapp.xml for each webapp under the host).
• lib: Keeps the JAR-file that are available to all webapps. The default installation include servlet-api.jar (Servlet), jasper.jar (JSP) and jasper-el.jar (EL). You may also keep the JAR files of external package here, such as MySQL JDBC driver (mysql-connector-java-5.1.{xx}-bin.jar) and JSTL (jstl.jar and standard.jar).
• logs: contains the engine logfile Catalina.{yyyy-mm-dd}.log, host logfile localhost.{yyyy-mm-dd}.log, and other application logfiles such as manger and host-manager. The access log (created by the AccessLogValve) is also kept here.
• webapps: the default appBase - web applications base directory of the host localhost.
• work: contains the translated servlet source files and classes of JSP/JSF. Organized in hierarchy of engine name (Catalina), host name (localhost), webapp name, followed by the Java classes package structure.
• temp: temporary files.

1.2  Tomcat Architecture

Tomcat is an HTTP server. Tomcat is also a servlet container that can execute Java Servlet, and converting JavaServer Pages (JSP) and JavaServerFaces (JSF) to Java Servlet. Tomcat employs a hierarchical and modular architecture as illustrated:

1.3  Main Configuration File "server.xml"

Tomcat's main configuration file is the "server.xml", kept under the <CATALINA_HOME>\conf directory. The default "server.xml" is reproduced as follows (after removing the comments and minor touch-ups):

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

<?xml version='1.0' encoding='utf-8'?> <Server port="8005" shutdown="SHUTDOWN"> <Listener className="org.apache.catalina.core.JasperListener" /> <Listener className="org.apache.catalina.core.AprLifecycleListener" SSLEngine="on" /> <Listener className="org.apache.catalina.core.JreMemoryLeakPreventionListener" /> <Listener className="org.apache.catalina.mbeans.GlobalResourcesLifecycleListener" /> <Listener className="org.apache.catalina.core.ThreadLocalLeakPreventionListener" /> <GlobalNamingResources> <Resource name="UserDatabase" auth="Container" type="org.apache.catalina.UserDatabase" description="User database that can be updated and saved" factory="org.apache.catalina.users.MemoryUserDatabaseFactory" pathname="conf/tomcat-users.xml" /> </GlobalNamingResources> <Service name="Catalina"> <Connector port="8080" protocol="HTTP/1.1" connectionTimeout="20000" redirectPort="8443" /> <Connector port="8009" protocol="AJP/1.3" redirectPort="8443" /> <Engine name="Catalina" defaultHost="localhost"> <Realm className="org.apache.catalina.realm.LockOutRealm"> <Realm className="org.apache.catalina.realm.UserDatabaseRealm" resourceName="UserDatabase"/> </Realm> <Host name="localhost" appBase="webapps" unpackWARs="true" autoDeploy="true"> <Valve className="org.apache.catalina.valves.AccessLogValve" directory="logs" prefix="localhost_access_log." suffix=".txt" pattern="%h %l %u %t &quot;%r&quot; %s %b" /> </Host> </Engine> </Service> </Server>

Server

Server (Line 2) is top component, representing an instance of Tomcat.It can contains one or more Services, each with its own Engines and Connectors.

<Server port="8005" shutdown="SHUTDOWN"> ...... </Server>

Listeners

The Server contains several Listeners (Lines 3-7). A Listener listens and responses to specific events.

• The JasperListener enables the Jasper JSP engine, and is responsible for re-compiling the JSP pages that have been updated.

  <Listener className="org.apache.catalina.core.JasperListener" />

• The GlobalResourcesLifecycleListener enables the global resources, and makes possible the use of JNDI for accessing resources such as databases.

  <Listener className="org.apache.catalina.mbeans.GlobalResourcesLifecycleListener" />

Global Naming Resources

The <GlobalNamingResources> element (Line 9-15) defines the JNDI (Java Naming and Directory Interface) resources, that allows Java software clients to discover and look up data and objects via a name.
The default configuration defines a JNDI name called UserDatabase via the <Resource> element (Line 10-14), which is a memory-based database for user authentication loaded from "conf/tomcat-users.xml".

<GlobalNamingResources>
  <Resource name="UserDatabase" auth="Container"
            type="org.apache.catalina.UserDatabase"
            description="User database that can be updated and saved"
            factory="org.apache.catalina.users.MemoryUserDatabaseFactory"
            pathname="conf/tomcat-users.xml" />
</GlobalNamingResources>

You can define other global resource JNDI such as MySQL database to implement connection pooling.

Services

A Service associates one or more Connectors to a Engine. The default configuration defines a Service called "Catalina", and associates two Connectors: HTTP and AJP to the Engine.

<Service name="Catalina"> ...... </Service>

Connectors

A Connector is associated with a TCP port to handle communications between the Service and the clients. The default configuration defines two Connectors:

• HTTP/1.1: Handle HTTP communication and enable Tomcat to be an HTTP server. Clients can issue HTTP requests to the server via this Connector, and receive the HTTP response messages.

  <Connector port="8080" protocol="HTTP/1.1" connectionTimeout="20000" redirectPort="8443" />

  The default chooses TCP port 8080 to run the Tomcat HTTP server, which is different from the default port number of 80 for HTTP production server. You can choose any number between 1024 to 65535, which is not used by any application, to run your Tomcat server.
  The connectionTimeout attribute define the number of milliseconds this connector will wait, after accepting a connection, for the request URI line (request message) to be presented. The default is 20 seconds.
  The redirect attribute re-directs the SSL requests to TCP port 8443.
• AJP/1.3: Apache JServ Protocol connector to handle communication between Tomcat server and Apache HTTP server.

  <Connector port="8009" protocol="AJP/1.3" redirectPort="8443" />

  You could run Tomcat and Apache HTTP servers together, and let the Apache HTTP server handles static requests and PHP; while Tomcat server handles the Java Servlet/JSP. Read "How To Configure Tomcat to work with Apache".

Containers

Tomcat refers to Engine, Host, Context, and Cluster, as container. The highest-level is Engine; while the lowest-level is Context. Certain components, such as Realm and Valve, can be placed in a container.

Engine

A Engine is the highest-level of a container. It can contains one or more Hosts. You could configure a Tomcat server to run on several hostnames, known as virtual host.

<Engine name="Catalina" defaultHost="localhost">

The Catalina Engine receives HTTP requests from the HTTP connector, and direct them to the correct host based on the hostname/IP address in the request header.

Realm

A Realm is a database of user, password, and role for authentication (i.e., access control). You can define Realm for any container, such as Engine, Host, and Context, and Cluster.

<Realm className="org.apache.catalina.realm.LockOutRealm">
  <Realm className="org.apache.catalina.realm.UserDatabaseRealm" resourceName="UserDatabase"/>
</Realm>

The default configuration defines a Realm (UserDatabaseRealm) for the Catalina Engine, to perform user authentication for accessing this engine. It uses the JNDI name UserDatabase defined in the GlobalNamingResources.
Besides the UserDatabaseRealm, there are: JDBCRealm (for authenticating users to connect to a relational database via the JDBC driver); DataSourceRealm (to connect to a DataSource via JNDI; JNDIRealm (to connect to an LDAP directory); and MemoryRealm (to load an XML file in memory).

Hosts

A Host defines a virtual host under the Engine, which can in turn support many Contexts (webapps).

<Host name="localhost" appBase="webapps" unpackWARs="true" autoDeploy="true">

The default configuration define one host called localhost. The appBase attribute defines the base directory of all the webapps, in this case, <CATALINA_HOME>\webapps. By default, each webapp's URL is the same as its directory name. For example, the default Tomcat installation provides four webapps: docs, examples, host-manager and manager under the webapps directory. The only exception is ROOT, which is identified by an empty string. That is, its URL is http://localhost:8080/.
The unpackWARs specifies whether WAR-file dropped into the webapps directory shall be unzipped. For unpackWARs="false", Tomcat will run the application from the WAR-file directly, without unpacking, which could mean slower execution.
The autoDeploy attribute specifies whether to deploy application dropped into the webapps directory automatically.

Cluster

Tomcat supports server clustering. It can replicate sessions and context attributes across the clustered server. It can also deploy a WAR-file on all the cluster.

Valve

A Valve can intercept HTTP requests before forwarding them to the applications, for pre-processing the requests. A Valve can be defined for any container, such as Engine, Host, and Context, and Cluster.
In the default configuration, the AccessLogValve intercepts an HTTP request and creates a log entry in the log file, as follows:

<Valve className="org.apache.catalina.valves.AccessLogValve" directory="logs"
       prefix="localhost_access_log." suffix=".txt"
       pattern="%h %l %u %t &quot;%r&quot; %s %b" />

Other valves include:

• RemoteAddrValve: which blocks requests from certain IP addresses,
• RemoteHostValve: which blocks request based on hostnames,
• RequestDumperValve: which logs details of the requests,
• SingleSignOn Valve: when placed under a <host>, allows single sign-on to access all the webapp under the host.

1.4  Other Configuration Files: web.xml, context.xml, tomcat-users.xml

[TODO]
They are so many things that you can configured in Tomcat. I describe some of the configurations that I found useful in this section.

Enabling Directory Listing

When the request URL refers to a directory instead of a file, e.g., http://host:port/hello/, you can configure Tomcat to serve the directory listing, or a welcome file, or issue error "404 Page Not Found". Enabling directory listing is handy for test server but NOT desire for production server (as it reveal the directory structure and expose the entire directory hierarchy).

Enabling Directory Listing for ALL Webapps

To enable directory listing for all the web applications, you could modify the <CATALINA_HOME>\conf\web.xml, by changing "listings" from "false" to "true" for the "default" servlet, as follows:

<!-- The default servlet for all web applications, that serves static     -->
<!-- resources.  It processes all requests that are not mapped to other   -->
<!-- servlets with servlet mappings.                                      -->
<servlet>
  <servlet-name>default</servlet-name>
  <servlet-class>org.apache.catalina.servlets.DefaultServlet</servlet-class>
  <init-param>
    <param-name>debug</param-name>
    <param-value>0</param-value>
  </init-param>
  <init-param>
    <param-name>listings</param-name>
    <param-value>true</param-value>
  </init-param>
  <load-on-startup>1</load-on-startup>
</servlet>
    
<!-- The mapping for the default servlet -->
<servlet-mapping>
  <servlet-name>default</servlet-name>
  <url-pattern>/</url-pattern>
</servlet-mapping>
   
<!-- ==================== Default Welcome File List ===================== -->
<!-- When a request URI refers to a directory, the default servlet looks  -->
<!-- for a "welcome file" within that directory and, if present,          -->
<!-- to the corresponding resource URI for display.  If no welcome file   -->
<!-- is present, the default servlet either serves a directory listing,   -->
<!-- or returns a 404 status, depending on how it is configured.          -->
<welcome-file-list>
  <welcome-file>index.html</welcome-file>
  <welcome-file>index.htm</welcome-file>
  <welcome-file>index.jsp</welcome-file>
</welcome-file-list>

The above configuration maps URL "\" (root directory of the web context) (in <url-pattern>) to Java class DefaultServlet (in <servlet-class>) via the common servlet name of default (in <servlet-name>). We enable directory listing by changing the servlet's initialization parameter listings to true.
If a user requests for a directory, and the directory listing is enabled and it contains one of the files in the <welcome-file> list, the welcome file will be served; otherwise, the directory listing will be served. On the other hand, if a directory request is received and the directory listing is not enabled, the server returns an error "404 Page Not Found".

Enabling Directory Listing for a particular Webapp

If you wish to allow directory listing of a particular web application only, you could disable the directory listing in "<CATALINA_HOME>\conf\web.xml" globally, and define the following <servlet> and <servlet-mapping> in your application-specific WEB-INF\web.xml, as follows. You need to use another <servlet-name> in place of DefaultServlet.

<servlet>
  <servlet-name>DirectoryListing</servlet-name>
  <servlet-class>org.apache.catalina.servlets.DefaultServlet</servlet-class>
  <init-param>
    <param-name>debug</param-name>
    <param-value>0</param-value>
  </init-param>
  <init-param>
    <param-name>listings</param-name>
    <param-value>true</param-value>
  </init-param>
</servlet>
   
<servlet-mapping>
  <servlet-name>DirectoryListing</servlet-name>
  <url-pattern>/</url-pattern>
</servlet-mapping>

Automatic Servlet Reload

To enable automatic servlet reload (whenever a servlet is re-compiled), you need to specify <Context reloadable="true">...</Context>, in "<CATALINA_HOME>\conf\context.xml" for all web applications, or the <Context> element in "<CATALINA_HOME>\conf\server.xml" for a particular web application.
The following messages appear on the Tomcat's console if you re-compile a servlet:

XXX X, XXXX XX:XX:XX XX org.apache.catalina.core.StandardContext reload
INFO: Reloading Context with path [/hello] has started
XXX X, XXXX XX:XX:XX XX org.apache.catalina.core.StandardContext reload
INFO: Reloading Context with path [/hello] is completed

Enabling automatic servlet reload is handy during application development, but it requires significant runtime overhead to listen to the changes, and is not recommended for production systems. Instead, you could use the "manager" to trigger reloads on demand.

Setting the Context Root Directory and Request URL of a Webapp

A server could run many web applications. A webapp is also called a web context. The context root (or document base directory) refers to the base directory of a webapp. They are a few ways to configure a context root and its request URL of a webapp:

1. (RECOMMENDED) Create a directory under <CATALINA_HOME>\webapps for your webapp. A context will be created with request URL set to the name of the directory. For example, if you create a directory called "hello" under Tomcat's "webapps". This application can be accessed by web users via URL http://host:port/hello.
   To change the request URL of the webapp, create a "context.xml" configuration file, as follows, and place it under "ContextRoot\META-INF":

   <Context path="/yourURLPath" />

2. Alternatively, you can write a <Context> element in <CATALINA_HOME>\conf\server.xml, under the <Host> element. You can specify both the URL and the base directory. For example,

           ......
           ......
           <Context path="/ws" docBase="d:/workshop" reloadable="true">
           </Context>
         </Host>
       </Engine>
     </Service>
   </Server>

   In the above example, we define a web context with URL "/ws", with context root (docBase or document base directory) at "d:\workshop". This application can be accessed via URL http://host:port/ws.
   Take note that:
   • The configuration creates a mapping from the "URL Path" issued by the web users to the "document base directory" in the server's file system, where you store your webapp resources.
   • Place the <Context> element before the ending tag of the <Host> element.
   • Use Unix-style forward slash '/' as the directory separator in the configuration file, instead of Window-style back slash '\'.
   • The attribute reloadable="true" asks Tomcat to monitor your servlets for changes, and automatically reload the servlets if changes is detected. This is handy for a development system, but inefficient in a production system.
3. Write a configuration file with a <Context> element and place it under Tomcat's "conf\Catalina\localhost". For example, suppose we wish to create a webapp with URL "hello" in base directory "d:\myproject\myHello", create the following file "hello.xml":

   <?xml version="1.0" encoding="UTF-8"?>
   <Context docBase="D:\myproject\myHello" path="/hello" />

Changing the Default "webapps" Directory

The default directory for deploying web applications is <CATALINA_HOME>\webapps. You could change the default by modifying the configuration file "conf\server.xml" <Host> element's "appBase" attribute as follows:

<Host name="localhost" appBase="webapps"
      unpackWARs="true" autoDeploy="true"
      xmlValidation="false" xmlNamespaceAware="false">
   ......
</host>

2.  Deploying Webapps

A web context is a single web application (webapp). It is the lowest-level container, that you can define components such as Realm and Valve. By default, all webapps are kept under the <CATALINA_HOME>\webapps directory (as configured in the <host> element appBase attribute.
A Java webapp may contain many types of files, such as HTML, CSS, Scripts, images, JSP, servlet, utility classes, external library jar-files. A Java webapp must follow a strict directory structure as depicted in the Servlet/JSP specifications. This enables deployment in a Java-capable web server (such as Apache Tomcat and Glassfish). The resources must be kept in the correct directories and sub-directories.
The URL of a webapp, by default, is the same as the base directory name (or context root) of the webapp.

2.1  Webapp's Directory Structure

The directory structure of a webapp is as follows:

• "ContextRoot": contains the resources that are visible and accessible by the web clients, such as HTML, CSS, Scripts and images. These resources will be delivered to the clients as it is. You could create sub-directories such as images, css and scripts, to further categories the various resources.
• "ContextRoot\WEB-INF": This directory, although under the context root, is NOT visible to the web users. In other words, it is NOT accessible by the clients directly (for security reason). This is where you keep your application-specific configuration files such as "web.xml". It's sub-directories contain program classes, source files, and libraries.
• "ContextRoot\WEB-INF\src": Keeps the Java program source files. It is optional but a good practice to separate the source files and classes to facilitate deployment.
• "ContextRoot\WEB-INF\classes": Keeps the Java classes (compiled from the source codes). Classes defined in packages must be kept according to the Java package directory structure.
• "ContextRoot\WEB-INF\lib": Keeps the libraries (jar-files), which are provided by other packages, specific and available to this webapp only.
• "ContextRoot\META-INF\": This is also a hidden directory, to keep resources and configurations (e.g., "context.xml") related to the server. In contrast, "WEB-INF" is for resources related to this web application, independent of the server.

2.2  Webapp-Specific Configuration Files

These are the configuration files specific to a webapp: (a) WEB-INF\web.xml; (b) META-INF\context.xml.
You can configure a webapp in many ways: (a) Write a <context> element in server.xml under <Host> element, (b) contextRoot\META-INF\context.xml, and (c) conf\Catalina\localhost\webapp.xml, and (d) conf\context.xml. See "Setting the Context Root Directory and Request URL of a Webapp".

2.3  Web Application Deployment Descriptors - "web.xml"

The "web.xml" contains the deployment descriptors. There are two sets of web.xml:

1. <CATALINA_HOME>\conf\web.xml: applicable to ALL webapps.
2. ContextRoot\WEB-INF\web.xml: applicable to the specific web context. It overrides the global setting, if any.

The complete specification for "web.xml" can be found in the "Java Servlet Specification" under "Deployment Descriptor".
A sample configuration of a servlet with most of its sub-elements is as follows:

<web-app ......>
  ......
  <servlet>
    <icon>
      <small-icon>/images/icon.jpg</small-icon>
    </icon>
    <servlet-name>MyServlat</servlet-name>
    <display-name>My Servlet Display Name</display-name>
    <description>My Testing Servlet long description</description>
    <servlet-class>MyServletClassname</servlet-class>
    <init-param>
      <param-name>myParmName</param-name>
      <param-value>myParmValue</param-value>
    </init-param>
    <load-on-startup>25</load-on-startup>
  </servlet>
  ......
 
  <servlet-mapping>
    <servlet-name>MyServlat</servlet-name>
    <url-pattern>/sayhello</url-pattern>
  </servlet-mapping>
  ......
</web-app>

2.4  Deploying a Web Application in a WAR file

You could use the JDK's jar utility to "zip" up all the files of a web application to produce a so-called WAR (Web Application Archive) file for deployment, or distribution.

.... Change current directory to the web application's context root
contextRoot> jar cvf test.war .

Drop the test.war into <CATALINA_HOME>\webapps. A context called test will be created automatically. You can access the web application via URL http://host:port/test.
Tomcat actually unpacks the test.war into a "test" directory in <CATALINA_HOME>\webapps. You need to remove this directory, if you reload a new version.

3.  Running Tomcat

3.1  Tomcat's Manager

References:

1. "Tomcat Web Application Manager How To" @ "webapps/docs/html-manager-howto.html".
2. "Tomcat Manager App How-To" @ "webapps/docs/manager-howto.html".

Tomcat "manager" webapp allows you to deploy a new web application; start, stop, reload or un-deploy an existing one, without having to shut down and restart the server, in a production environment.
To enable Tomcat's manager, edit "<CATALINA_HOME>\conf\tomcat-users.xml" to include a role called "manager-gui" and a user with this role. You may also assign other roles, such as admin-gui for accessing the Tomcat "Host Manager".

<role rolename="manager-gui" />
<role rolename="manager-status" />
<role rolename="manager-script" />
<role rolename="manager-jmx" />

<role rolename="admin-gui" />
<role rolename="admin-script" />
 
<user username="mymanager" password="xxxx" roles="manager-gui, admin-gui"/>

To invoke manager web application, use http://localhost:8080/manager/html. You can use Tomcat's manager to:

1. List all webapps.
2. Start/Stop/Reload a particular webapp.
3. Deploy a new webapp remotely, and undeploy a webapp without restarting the container.
4. Terminate (or Invalidate) sessions - a session has a pre-set expiry time (e.g., 30 sec).
5. Analyze memory leaks.
6. View JVM status and Server status.

Tomcat 7 provides separate manager roles for the GUI (manager-gui), status (manager-status), scripting (manager-script) and JMX proxy (manager-jmx), defined in "webapps/manager/WEB-INF/web.xml". This allows for fine-grained access control to management tasks.

• manager-gui - Access to the HTML "web" interface, via:

  http://{host}:{port}/manager/html

• manager-status - Access to the "Server Status" page only, via:

  http://{host}:{port}/manager/status

• manager-script - Access to the "plain-text" interface, and to the "Server Status" page, via command in the form of:

  http://{host}:{port}/manager/text/{command}?{parameters}
   
  // Examples
  http://{host}:{port}/manager/text/list                  // List all webapps
  http://{host}:{port}/manager/text/deploy?path=/testapp  // Deploy webapp

• manager-jmx - Access to JMX proxy interface and to the "Server Status" page, via:

  http://{host}:{port}/manager/jmxproxy/?{command}={parameter}

For security reason, a user should NOT be given more than one of the following roles: manager-gui, manager-script, and manager-jmx.

Tomcat's "Administration Tool"

The Tomcat "Adminstrative Tool" (or "Administrator Console") has been removed since Tomcat 6. Use JMX manager instead.

3.2  Automatic Startup at Boot-time

(Windows) Running Tomcat as a Windows Service

You need to download the Windows-specific version of Tomcat (from Tomcat's download, choose 32-bit or 64-bit Windows' zip version, e.g., apache-tomcat-7.0.{xx}-windows-x86.zip).
Read "Windows service How-To" in the Tomcat documentation (<CATALINA_HOME>\webapps\docs\windows-service-howto.html).
In a production environment, it is more convenient to run Tomcat as a service, so that it can start automatically whenever the system is started (or re-start automatically after an unexpected interruption).
To install Tomcat as a service, start a CMD shell (with administrator right) and run the <CATALINA_HOME>\bin\service.bat with install option:

... Change directory to <CATALINA_HOME>\bin ...
<CATALINA_HOME>\bin> service install
Installing the service 'Tomcat7' ...
......
The service 'Tomcat7' has been installed.

The Tomcat service called "Apache Tomcat 7" is installed and will start automatically whenever the system is started. Check the "Services" under "Control Panel" ⇒ "Administrative Tools".
A GUI application called Tomcat7w is available for monitoring and configuring Tomcat services. Launch Tomcat7w:

<CATALINA_HOME>\bin> Tomcat7w

You could put the Tomcat icon in the system tray via the MS (Monitor Service) option:

<CATALINA_HOME>\bin> Tomcat7w //MS//

You can start/stop the Tomcat service now via:

1. Tomcat7w;
2. "Control Panel" ⇒ "Administrator Tools" ⇒ "Services" ⇒ "Apache Tomcat 7" ⇒ "Start";
3. From CMD shell, Issue "net" command:

   prompt> net start tomcat7
   The Apache Tomcat 7 service is starting..
   The Apache Tomcat 7 service was started successfully.
   ......
   ......
   prompt> net stop tomcat7
   The Apache Tomcat 7 service is stopping..
   The Apache Tomcat 7 service was stopped successfully.

To uninstall Tomcat Service, run the <CATALINA_HOME>\bin\service.bat with remove option:

<CATALINA_HOME>\bin> service remove
The service 'Tomcat7' has been removed

You can also use Microsoft Management Console (MMC) to manage the services: Go to "Start" ⇒ ⇒ Run ⇒ enter "services.msc".
A flip side of running Tomcat as a service is you need to read the error messages from <CATALINA_HOME>\logs instead of the Tomcat console.

(Linux and Mac OS) Automatic Startup on Reboots

To start Tomcat automatically when the machine boots up, you need to create a init script in /etc/init.d, e.g., /etc/init.d/tomcat, as follows:

#!/bin/sh
# Tomcat init script for Linux.
#
JAVA_HOME={path-to-java-installed-directory}
CATALINA_HOME={path-to-Tomat-installed-directgory}
export JAVA_HOME CATALINA_HOME
exec $CATALINA_HOME/bin/catalina.sh $*

Save the script as "tomcat" in /etc/init.d.

$ cd /etc/init.d
$ sudo chown root.root tomcat   // change owner to user root, group root
$ sudo chmod 755 tomcat         // change mode u+rwx, g+rx, a+rx

You can start|stop|restart Tomcat via:

$ service tomcat start|stop|restart

3.3  Tomcat's Startup Script

To start tomcat server, you could invoke the batch file "startup.bat" (for Windows) or "startup.sh" (for Linux/Mac) (in directory "<CATALINA_HOME>\bin", where <CATALINA_HOME> refers to the Tomcat installed directory). The "startup.bat|startup.sh" invokes "catalina.bat start|catalina.sh start".
Alternatively, you could call the "catalina.bat|catalina.sh" directly, which provides more options of starting Tomcat. Enter "catalina" to view the options:

<CATALINA_HOME>/bin> catalina
Using CATALINA_BASE:   D:\xxx\tomcat7.0.{xx}
Using CATALINA_HOME:   D:\xxx\tomcat7.0.{xx}
Using CATALINA_TMPDIR: D:\xxx\tomcat7.0.{xx}\temp
Using JRE_HOME:        d:\xxx\jdk1.6
Usage:  catalina ( commands ... )
commands:
  debug             Start Catalina in a debugger
  debug -security   Debug Catalina with a security manager
  jpda start        Start Catalina under JPDA debugger
  run               Start Catalina in the current window
  run -security     Start in the current window with security manager
  start             Start Catalina in a separate window
  start -security   Start in a separate window with security manager
  stop              Stop Catalina
  configtest        Run a basic syntax check on server.xml
  version           What version of tomcat are you running?

Study the source codes of "catalina.bat|catalina.sh". Take note that the environment variable JAVA_HOME is needed in this script.
The other scripts provided are:

• shutdown.bat|shutdown.sh: for shuting down the Tomcat server.
• configtest.bat|configtest.sh: for checking the configuration file server.xml, same as "catalina configtest".
• version.bat|version.sh: for displaying the versions, same as "catalina version".
• digest.bat|digest.sh: making password hash and encrypting password.
• Internal used: setclasspath.bat|setclasspath.sh, cpappend.bat (classpath append), tool-wrapper.bat|tool-wrapper.sh, daemon.sh.

4.  Security

4.1  Realm and User Authentication in Tomcat

References:

1. "Realm Configuration HOW-TO" (@ "<CATALINA_HOME>\webapps\docs\realm-howto.html").
2. "Java EE 5 Tutorial", Part IV "Services", Chapters 28-30 on Security.

In Information Security:

• Access control deals with identifying which resources require protection, and which users (roles) are authorized to access the protected resources.
• Authentication deals with verifying users' credential, i.e., ensuring the user is "who he said he is". User's credential is typically provided in the form of username/password and possibly IP address (hostname). Other means include biometrics (finger-print, face recognition, retina) and digital certificates.
• Confidentiality deals with the encryption of the transmitted data over the network. This is often carried out via employing HTTP over SSL (Secure Socket Layer), known as HTTPS.
• Message Integrity ensures that messages are not tempered during transmission. This is done via message digest or hash.
• Non-repudiation: If he/she has sent a message, he/she cannot deny. This is done via digital certificate and public/private keys.

Security can be managed by the webapps themselves (called application-managed security) or via the Tomcat container (called container-managed security). In container-managed security, security is handled by the server. The server-side programs (servlets, JSPs) do not need any security-aware code. That is, the security control is totally transparent to the server-side programs. This section shall deal with container-managed security for access control and authentication.
In Tomcat, a user is identified via username/password. A user is assigned role(s) (e.g., manager, admin, user, etc). Tomcat grants access for webapps to role(s), instead of individual users.
A realm is a collection of usernames/passwords and roles. Tomcat supports the following types of realms:

• UserDatabaseRealm: kept in a XML file "conf\tomcat-users.xml", accessed via JDNI (Java Naming and Directory Interface).
• JDBCRealm: kept in a relational database such as MySQL, accessed via JDBC.
• others: JNDIRealm (uses LDAP directory), JAASRealm (Java Authentication and Authorization Service).

You can used the <realm> element to configure a realm in "conf\server.xml". <realm> element can be placed in <engine>, <host>, or <context>, which determines the scope of the realm: all virtual hosts under the engine, a particular host, or a particular web application.

4.2  UserDatabaseRealm

UserDatabaseRealm stores user information in a XML file and accessed via JNDI (Java Naming and Directory Interface). By default, the XML file is "<CATALINA_HOME>\conf\tomcat-users.xml". UserDatabaseRealm is loaded into memory from the specified file, and kept in memory until Tomcat is shut down.
Tomcat provide a JSP example called "FORM Authentication" (@ http://localhost:8080/examples/jsp/security/protected/index.jsp"), which uses UserDatabaseRealm. Let us study this example. But before we get into the codes, let's look at the Tomcat's configurations.

"conf\server.xml"

You can specify the type of realm to be used via <Realm> element in server.xml. In this case, UserDatabaseRealm. The <Realm> is defined within the <Engine> elements, and thus applicable to all the virtual hosts and webapps, under this server.
To specify the file used in UserDatabaseRealm, a JDNI resource named "UserDatabase" is defined, which maps to the file "conf\tomcat-users.xml".

<Server ...... >
  <!-- Global JNDI resources -->
  <GlobalNamingResources>
    <!-- Editable user database that can also be used by
         UserDatabaseRealm to authenticate users -->
    <Resource name="UserDatabase" auth="Container"
              type="org.apache.catalina.UserDatabase"
              description="User database that can be updated and saved"
              factory="org.apache.catalina.users.MemoryUserDatabaseFactory"
              pathname="conf/tomcat-users.xml" />
  </GlobalNamingResources>
  
  <Service name="Catalina">
    <Engine name="Catalina" defaultHost="localhost">
      <!-- Use the LockOutRealm to prevent attempts to guess user passwords
           via a brute-force attack -->
      <Realm className="org.apache.catalina.realm.LockOutRealm">
        <Realm className="org.apache.catalina.realm.UserDatabaseRealm"
             resourceName="UserDatabase" />
      </Realm>
      <Host name="localhost" ......
        ......
      </Host>
    </Engine>
  </Service>   
</Server>

"conf\tomcat-users.xml"

Recall that a user is identified via username/password. A user is assigned role(s). Accesses for web applications are granted to role(s) instead of individual users. "Tomcat-users.xml" contains the following roles and username/password, but commented-out. Uncomment them for testing the example. Two roles, tomcat and role1, and three users, tomcat, role1 and both are defined.

<?xml version="1.0" encoding="ISO-8859-1" ?>
<tomcat-users>
  <role rolename="tomcat" />
  <role rolename="role1" />
 
  <user username="tomcat" password="tomcat" roles="tomcat" />
  <user username="both" password="tomcat" roles="tomcat,role1" />
  <user username="role1" password="tomcat" roles="role1" />
</tomcat-users>

Take note that the passwords are stored in clear text, which is not really desirable.

"ContextRoot\WEB-INF\web.xml"

For Tomcat's webapp called "examples", the security roles are defined using <security-constraint> element in "webapps\examples\WEB-INF\web.xml" as follows. The URL patterns /jsp/security/protected/* are accessible by users having roles of tomcat and role1 only.

<web-app ......>
  ......
  <security-constraint>
    <display-name>Example Security Constraint</display-name>
    <web-resource-collection>
      <web-resource-name>Protected Area</web-resource-name>
      <!-- Define the context-relative URL(s) to be protected -->
      <url-pattern>/jsp/security/protected/*</url-pattern>
      <!-- If you list http methods, only those methods are protected -->
      <http-method>DELETE</http-method>
      <http-method>GET</http-method>
      <http-method>POST</http-method>
      <http-method>PUT</http-method>
    </web-resource-collection>
    <auth-constraint>
      <!-- Anyone with one of the listed roles may access this area -->
      <role-name>tomcat</role-name>
      <role-name>role1</role-name>
    </auth-constraint>
  </security-constraint>
  
  <!-- Default login configuration uses form-based authentication -->
  <login-config>
    <auth-method>FORM</auth-method>
    <realm-name>Example Form-Based Authentication Area</realm-name>
    <form-login-config>
      <form-login-page>/jsp/security/protected/login.jsp</form-login-page>
      <form-error-page>/jsp/security/protected/error.jsp</form-error-page>
    </form-login-config>
  </login-config>
        
  <!-- Security roles referenced by this web application -->
  <security-role>
    <role-name>role1</role-name>
  </security-role>
  <security-role>
    <role-name>tomcat</role-name>
  </security-role>    

4.3  HTML FORM-based Authentication Method

The example uses HTML FORM-based authentication method, defined in element <login-config>. All accesses to the protected URLs (http://localhost:8080/examples/jsp/security/protected/*) will be redirected to the login.jsp page (defined in <form-login-page>), which prompts user for the credential. For example, if a user requests for http://localhost:8080/examples/jsp/security/protected/index.jsp, the login.jsp will be displayed.
The login.jsp page contain a html <form> (thus called FORM-based authentication):

<html>
<head><title>Login Page for Examples</title></head>
<body>
<form method="POST" action='<%= response.encodeURL("j_security_check") %>' >
  Username:<input type="text" name="j_username">
  Password:<input type="password" name="j_password">
  <input type="submit" value="Log In">
</form>
</body>
</html>

The login page submits the username and password in parameters j_username and j_password to j_security_check. <input type="password" ...> is used for password text field, which displays the password as *'s. The response.encodeURL(URL) encodes the URL j_security_check by including the session ID if URL-rewriting is used for session tracking; it returns the URL unchanged if cookie is used. For robust session tracking, all URLs emitted by server-side programs (servlet/JSP) should be run through this method.
If login fails, user will be redirected to error.jsp page, as follows,

<html>
<head><title>Error Page For Examples</title></head>
<body>
Invalid username and/or password, please try again
<a href='<%= response.encodeURL("index.jsp") %>'>again</a>.
</body>
</html>

If login succeeds, the user will get the page he requested for. Study the "examples\jsp\security\protected\index.jsp" source.

• To logoff, terminate the current session via session.invalidate().
• You can use request.getRemoteUser() to get the authenticated login username; request.getUserPrincipal() to get a java.security.Principal object containing the name of the current authenticated user; request.isUserInRole(role) to check if the authenticated user is included in the specified role.

4.4  HTTPS

In FORM-based authentication, the username/password are sent in clear text, and susceptible to eavesdropping. Hence, it is important to encrypt the transport by turning on SSL (HTTPS). Read "Tomcat with SSL" on how to setup Tomcat with SSL.
To enforce user to use secure transport (HTTPS), add a <transport-guarantee>CONFIDENTIAL</transport-guarantee>, inside the <security-constraint>, as follows:

<security-constraint>
  <display-name>Example Security Constraint</display-name>
  <web-resource-collection>
    <web-resource-name>Protected Area</web-resource-name>
    <url-pattern>/jsp/security/protected/*</url-pattern>
    ......
  </web-resource-collection>
  <auth-constraint>
    <role-name>tomcat</role-name>
    ......
  </auth-constraint>
  <!-- must use SSL for secure transport -->
  <user-data-constraint>
    <transport-guarantee>CONFIDENTIAL</transport-guarantee>
  </user-data-constraint>
</security-constraint>

All accesses to HTTP at port 8080 (e.g., http://localhost:8080/examples/jsp/security/protected/index.jsp) will be redirected to HTTPS at port 8443 (e.g., https://localhost:8443/examples/jsp/security/protected/index.jsp).

4.5  HTTP BASIC Authentication

HTTP defines two access authentication schemes to request for username/password: Basic and Digest. Read "HTTP Authentication".
To use BASIC authentication, change the <login-config>'s <auth-method> to BASIC.

<login-config>
   <auth-method>BASIC</auth-method>
   <realm-name>Basic Authentication Area</realm-name>
   <!-- Removed, no applicable for BASIC authentication
   <form-login-config>
      <form-login-page>/jsp/security/protected/login.jsp</form-login-page>
      <form-error-page>/jsp/security/protected/error.jsp</form-error-page>
    </form-login-config>
    -->
</login-config>

In BASIC authentication, Tomcat uses the HTTP Basic Authentication to ask for username and password. Try http://localhost:8080/examples/jsp/security/protected/index.jsp, you will be prompted for username/password automatically. There is no redirect to login.jsp and no need to write the login.jsp.
Again, the HTTP Basic Authentication sends the username and password in clear text (password is encoded in Base64, but not encrypted). It is totally insecure, unless you use a secure transport (HTTPS) or VPN (Virtual Private Network).
The Tomcat's webapp manager (under webapps/manager) uses BASIC authentication.

4.6  HTTP DIGEST Authentication

To use DIGEST authentation, change the <login-config>'s <auth-method> to DIGEST. In DIGEST authentication, Tomcat uses HTTP Digest Authentication Scheme to ask for username/password. Instead of sending password in clear text, the digest of password is send to the server. Although DIGEST authentication is more secure than BASIC authentication, HTTPS is much more secure.

<login-config>
   <auth-method>DIGEST</auth-method>
   <realm-name>Digest Authentication Area</realm-name>
</login-config>

4.7  JDBCRealm

UserDatabaseRealm is not meant for serious production environment, as it is hard to maintain. JDBCRealm is more appropriate.
In JDBCRealm, user information is kept in a relational database, such as MySQL, accessed via JDBC, instead of an XML file. The information can be secured thruough proper database security.

Setting up Database

We shall set up our user database in MySQL. Read "How to Install MySQL and Get Started" if you are new to MySQL.
The following script can be used to set up the user database. Two tables are required: a users table containing username and password, and a user_roles containing username and the role assigned.

create database tomcat_users;
  
use tomcat_users;
  
create table users (
  username varchar(15) not null,
  password varchar(15) not null,
  primary key (username)
);
  
create table user_roles (
  username varchar(15) not null,
  role     varchar(15) not null,
  primary key (username, role)
);
  
insert into users values 
  ('tomcat', 'tomcat'), 
  ('both', 'tomcat'), 
  ('role1', 'tomcat');
  
insert into user_roles values 
  ('tomcat', 'tomcat'), 
  ('role1', 'role1'), 
  ('both', 'tomcat'), 
  ('both', 'role1');

JDBC Driver

Next, copy the MySQL's JDBC driver ("mysql-connector-java-5.1.{xx}-bin.jar") into Tomcat's lib ("<CATALINA_HOME>\lib"). Read "How to Install MySQL and Get Started"

"conf\server.xml"

Again, the realm is defined in server.xml via a <Realm> element. In this case, a JDBCRealm, with a connectionURL providing a MySQL database connection.

<Realm className="org.apache.catalina.realm.JDBCRealm"
   driverName="com.mysql.jdbc.Driver"
   connectionURL="jdbc:mysql://localhost:{port}/tomcat_users"
   connectionName="{dbuser}"
   connectionPassword="{dbpassword}"
   userTable="users" userNameCol="username" userCredCol="password"
   userRoleTable="user_roles" roleNameCol="role" />

Replace the {port} with your MySQL server port number, and {dbuser} and {dbpass} with an authorized MySQL username/password.

"ContextRoot\WEB-INF\web.xml"

Same as UserDatabaseRealm.

Authentication Methods

Same as UserDatabaseRealm, you can use FORM, BASIC or DIGEST authentication method.

Testing

You need to start MySQL server before starting the Tomcat Server.

4.8  Single Login

By default, each protected webapp would request for login during the first access. You can enable single login to all webapps under the host by uncommenting the single-login valve, as follows:

<Host ......>
  <!-- SingleSignOn valve, share authentication between web applications
       Documentation at: /docs/config/valve.html -->
  <Valve className="org.apache.catalina.authenticator.SingleSignOn" />
</Host>

4.9  Tomcat with SSL

SSL (Secure Socket Layer), allows web browsers and web servers to communicate over a secured (encrypted) connection. Tomcat provides built-in support for SSL.
Read:

• "SSL Configuration How-to" of Tomcat Documentation @ "<CATALINA_HOME>\webapps\docs\ssl-howto.html".
• "keytool - Key and Certificate Management Tool" @ JDK documentation.

The steps to turn on SSL support are:
Step 1: Check your JDK version. Tomcat's SSL uses Java Secure Socket Extension (JSSE), which has been integrated into JDK since 1.4.
Step 2: Prepare the Tomcat's server certificate, using the JDK's Key and Certificate Management Tool called "keytool" (in "<JAVA_HOME>\bin" ), as follows:

> keytool
... display the help menu ...
 
// Generate a self-signed certificate for Tomcat
> keytool -genkey -alias tomcat -keyalg RSA -keystore {TOMCAT_HOME}\conf\.keystore
Enter keystore password: xxxxxxxx
Re-enter new password: xxxxxxxx
What is your first and last name?
  [Unknown]:
What is the name of your organizational unit?
  [Unknown]:
What is the name of your organization?
  [Unknown]:
What is the name of your City or Locality?
  [Unknown]:
What is the name of your State or Province?
  [Unknown]:
What is the two-letter country code for this unit?
  [Unknown]:
Is CN=Unknown, OU=Unknown, O=Unknown, L=Unknown, ST=Unknown, C=Unknown correct?
  [no]:  y
Enter key password for <tomcat>
        (RETURN if same as keystore password):

• The "-genkey" option is used to generate a public-private key pair. The public key is wrapped into an X.509 v1 self-signed certificate. The certificate and the private key are stored in a new keystore entry identified by the alias. In our case, the alias name must be "tomcat".
• The "-keyalg" option specifies the key generation algorithm. RSA public key algorithm is used in this case.
• The "-keystore" option specifies the name and location of the key store file.
• The password for alias tomcat must be the same as the keystore (i.e., hit enter for the last question).

Step 3: Enable SSL support for Tomcat. SSL is built into Tomcat. The Tomcat's configuration file commented out the SSL configuration directive. Uncomment them by removing the <!-- and --> around the SSL Coyote HTTP/1.1 Connector as follows:

<!-- Define a SSL HTTP/1.1 Connector on port 8443
     This connector uses the JSSE configuration, when using APR, the 
     connector should be using the OpenSSL style configuration
     described in the APR documentation -->
<Connector port="8443" protocol="org.apache.coyote.http11.Http11Protocol"
     SSLEnabled="true" maxThreads="150" scheme="https" secure="true"
     clientAuth="false" sslProtocol="TLS" 
     keystoreFile="{TOMCAT_HOME}\conf\.keystore"
     keystorePass="passwordOfKeyStore" />

Note that the SSL (or HTTPS) is running on port 8443 instead of its default port number 443.
Add in the keystoreFile and keyStorePass attributes. The keystoreFile attribute specified the location of the keystore file. The keyStorePass provides the password for accessing the keystore file.
Step 4: Start your tomcat (run "<CATALINA_HOME>\bin\startup.bat"). After that, start a web browser and issue an HTTPS request as follows:

https://localhost:8443

5.  Clustering

[TODO]

5.1  Configuring Virtual Hosts

To set up a virtual host called "www.mytest.com" (suppose that you have registered this hostname with at static IP address). Include the following <Host> element in server.xml under the Engine Catalina:

<Engine name="Catalina" >
  <Host name="localhost .....>
    ......
  </Host>
  <Host name="www.mytest.com" appBase="webapps_mytest.com"
        unpackWARs="true" autoDeploy="true" >
    <Alias>mytest.com</Alias>
    <Valve className="org.apache.catalina.valves.AccessLogValve"
           directory="logs"
           prefix="mytest.com_access_log." suffix=".log"
           pattern="%h %l %u %t &quot;%r&quot; %s %b"
           resolveHosts="false" />
  </Host>
</Engine>

The above lines configure a virtual host with hostname "www.mytest.com", with webapps base directory at "<CATALINA_HOME>\webapps_mytest.com". We also define a alias called "mytest.com". That is, this host can be accessed via http://www.mytest.com:port or http://mytest.com:port. We also define a Valve, which intercepts the request message to write a log entries (similar to localhost).
Next:

1. Create a directory "webapps_mytest.com" under <CATALINA_HOME>, according to the appBase.
2. Create a web application called ROOT, by creating a directory ROOT under the "webapps_mytest.com". Recall that ROOT was configured with an empty string URL. In other words, http://www.mytest.com:port/ accesses the ROOT application.
3. Create a directory "www.mytest.com" under "conf\Catalina".
4. Write a welcome page called "index.html" and save it in "webapps_mytest.com\ROOT".

   <html>
   <head><title>Testing Virtual Host</title></head>
   <body>
     <h1>It's work on virtual host</h1>
   </body>
   </html>

To test the virtual host, without registering the hostname with an ISP, edit "C:\Windows\System32\drivers\etc\hosts" to include the following lines (required administrative authority):

127.0.0.1   www.mytest.com
127.0.0.1   mytest.com

These lines maps host names www.mytest.com and mytest.com to IP address 127.0.0.1, which is the localhost. As the IP software checks the host file before asking Domain Name Service (DNS) to resolve a host name, you willl be able to test your virtual host.
Now, you are ready to test the virtual hosts. Start the Tomcat server and issue these URL:

http://www.mytest.com:8080
http://mytest.com:8080
http://www.mytest.com:8080/
http://mytest.com:8080/
http://www.mytest.com:8080/index.html
http://mytest.com:8080/index.html

5.2  Tomcat Host Manager for Clustering and Admin Roles

Reference: "Clustering/Session Replication HOW-TO" @ "webapps/docs/cluster-howto.html".
The Tomcat "host-manager" webapp allows you to manage Tomcat clusters. To access host-manager, you need to define a user with admin role.
The admin role has been separated out as admin-gui and admin-script, in Tomcat 7, as defined in webapps/host-manager/WEB-INF/web.xml.
The "Host Manager" web application can be accessed via http://{host}:{port}/host-manager/html.

6.  Performance Tuning

[TODO]

REFERENCES & RESOURCES

1. Tomcat mother site @ http://tomcat.apache.org
2. Tomcat's Documentation @ "<CATALINA_HOME>\webapps\docs".
3. Java Servlet, JavaServer Pages (JSP), and JavaServer Faces (JSF) specifications.

SOURCE

Cron Format

Cron format is a simple, yet powerful and flexible way to define time and frequency of various actions. nnCron make active use of cron format in both classic and extended modes.
Traditional (inherited from Unix) cron format consists of five fields separated by white spaces:

<Minute> <Hour> <Day_of_the_Month> <Month_of_the_Year> <Day_of_the_Week>

nnCron can use both traditional and "enhanced" version of cron format, which has an additional (6th) field: <Year>:

<Minute> <Hour> <Day_of_the_Month> <Month_of_the_Year> <Day_of_the_Week> <Year>

A user can select the format he would like to use by selecting or unselecting the Year field checkbox on General tab in Options dialog (which can be opened by doublecliking the nnCron icon in system tray). By default, nnCron uses the enhanced format.
The following graph shows what it consists of:

* * * * * *
| | | | | | 
| | | | | +-- Year              (range: 1900-3000)
| | | | +---- Day of the Week   (range: 1-7, 1 standing for Monday)
| | | +------ Month of the Year (range: 1-12)
| | +-------- Day of the Month  (range: 1-31)
| +---------- Hour              (range: 0-23)
+------------ Minute            (range: 0-59)

Any of these 6 fields may be an asterisk (*). This would mean the entire range of possible values, i.e. each minute, each hour, etc. In the first four fields, nnCron users can also use "nonstandard" character ? (question mark), described here.
Any field may contain a list of values separated by commas, (e.g. 1,3,7) or a range of values (two integers separated by a hyphen, e.g. 1-5).
After an asterisk (*) or a range of values, you can use character / to specify that values are repeated over and over with a certain interval between them. For example, you can write "0-23/2" in Hour field to specify that some action should be performed every two hours (it will have the same effect as "0,2,4,6,8,10,12,14,16,18,20,22"); value "*/4" in Minute field means that the action should be performed every 4 minutes, "1-30/3" means the same as "1,4,7,10,13,16,19,22,25,28".
In Month and Day of Week fields, you can use names of months or days of weeks abbreviated to first three letters ("Jan,Feb,...,Dec" or "Mon,Tue,...,Sun") instead of their numeric values.
Examples:

* * * * * *                         Each minute


59 23 31 12 5 *                     One minute  before the end of year if the last day of the year is Friday
         
59 23 31 DEC Fri *                  Same as above (different notation)


45 17 7 6 * *                       Every  year, on June 7th at 17:45


45 17 7 6 * 2001,2002               Once a   year, on June 7th at 17:45, if the year is 2001 or  2002


0,15,30,45 0,6,12,18 1,15,31 * 1-5 *  At 00:00, 00:15, 00:30, 00:45, 06:00, 06:15, 06:30,
                                    06:45, 12:00, 12:15, 12:30, 12:45, 18:00, 18:15,
                                    18:30, 18:45, on 1st, 15th or  31st of each  month, but not on weekends


*/15 */6 1,15,31 * 1-5 *            Same as above (different notation)


0 12 * * 1-5 * (0 12 * * Mon-Fri *) At midday on weekdays


* * * 1,3,5,7,9,11 * *              Each minute in January,  March,  May, July, September, and November


1,2,3,5,20-25,30-35,59 23 31 12 * * On the  last day of year, at 23:01, 23:02, 23:03, 23:05,
                                    23:20, 23:21, 23:22, 23:23, 23:24, 23:25, 23:30,
                                    23:31, 23:32, 23:33, 23:34, 23:35, 23:59


0 9 1-7 * 1 *                       First Monday of each month, at 9 a.m.


0 0 1 * * *                         At midnight, on the first day of each month


* 0-11 * * *                        Each minute before midday


* * * 1,2,3 * *                     Each minute in January, February or March


* * * Jan,Feb,Mar * *               Same as above (different notation)


0 0 * * * *                         Daily at midnight


0 0 * * 3 *                         Each Wednesday at midnight

In extended mode, crontab notation may be abridged by omitting the rightmost asterisks.
Examples: (only for extended mode)

Full notation                         Abridged notation

* * * * * * 

59 23 31 12 5 2003                    59 23 31 12 5 2003

59 23 31 12 5 *                       59 23 31 12 5

45 17 7 6 * *                         45 17 7 6

0,15,30,45 0,6,12,18 1,15,31 * * *    0,15,30,45 0,6,12,18 1,15,31

0 12 * * 1-5 *                        0 12 * * 1-5

* * * 1,3,5,7,9,11 * *                * * * 1,3,5,7,9,11

1,2,3,5,20-25,30-35,59 23 31 12 * *   1,2,3,5,20-25,30-35,59 23 31 12

0 9 1-7 * 1 *                         0 9 1-7 * 1

0 0 1 * * *                           0 0 1

* 0-11 * * * *                        * 0-11

* * * 1,2,3 * *                       * * * 1,2,3

0 0 * * * *                           0 0

0 0 * * 3 *                           0 0 * * 3

Both in classic and extended modes, nnCron users can use a "nonstandard" character "?" in the first four fields of cron format. It stands for time of nnCron startup, i. e. when a field is processed, startup time will be substituted for the question mark: minutes for Minute field, hour for Hour field, day of the month for Day of month field and month for Month field.
For example, if you write the following:

Time: ? ? * * * *

the task will be executed on nnCron startup and then will be executed daily at the same time (unless the user doesn't restart nnCron, of course): question marks will be "replaced" by nnCron startup time. For example, if nnCron was started at 8:25, question marks will be substituted this way:

Time: 25 8 * * * *

Some more examples:

\ task will be executed only on nnCron startup 
\ (this has the sane effect  as "Time: START-TIME")
Time: ? ? ? ? * *

\ task will be executed on nnCron startup (e.g. at 10:15) 
\ and then will be executed again each hour: 
\ at 11:15, 12:15, 13:15 etc.
Time: ? * * * * *

\ task will be started each minute after nnCron startup until the next hour starts. 
\ The next day, if nnCron has not been restarted in the meanwhile,  
\it will be executed again each minute during the same hour.
Time: * ? * * * * 

\ task will be executed daily,  every five minutes,
\ during the hour when nnCron was started.
Time: */5 ? * * * *

Cron and Crontab usage and examples

Cron

Cron is a daemon that executes scheduled commands. Cron is started automatically from /etc/init.d on entering multi-user runlevels. Cron searches its spool area (/var/spool/cron/crontabs) for crontab files (which are named after accounts in /etc/passwd); crontabs found are loaded into memory. Note that crontabs in this directory should not be accessed directly - the crontab command should be used to access and update them.
Cron also reads /etc/crontab, which is in a slightly different format. Additionally, cron reads the files in /etc/cron.d.
Cron then wakes up every minute, examining all stored crontabs, checking each command to see if it should be run in the current minute. When executing commands, any output is mailed to the owner of the crontab (or to the user named in the MAILTO environment variable in the crontab, if such exists). The children copies of cron running these processes have their name coerced to uppercase, as will be seen in the syslog and ps output.
Additionally, cron checks each minute to see if its spool directory's modtime (or the modtime on /etc/crontab) has changed, and if it has, cron will then examine the modtime on all crontabs and reload those which have changed. Thus cron need not be restarted whenever a crontab file is modified. Note that the crontab(1) command updates the modtime of the spool directory whenever it changes a crontab.
Special considerations exist when the clock is changed by less than 3 hours, for example at the beginning and end of daylight savings time. If the time has moved forwards, those jobs which would have run in the time that was skipped will be run soon after the change. Conversely, if the time has moved backwards by less than 3 hours, those jobs that fall into the repeated time will not be re-run.
Only jobs that run at a particular time (not specified as @hourly, nor with '*' in the hour or minute specifier) are affected. Jobs which are specified with wild cards are run based on the new time immediately.
Clock changes of more than 3 hours are considered to be corrections to the clock, and the new time is used immediately.
In Debian and Redhat cron treats the files in /etc/cron.d as extensions to the /etc/crontab file (they follow the special format of that file, i.e. they include the user field). The intended purpose of this feature is to allow packages that require finer control of their scheduling than the /etc/cron.{daily,weekly,monthly} directories allow to add a crontab file to /etc/cron.d. Such files should be named after the package that supplies them. Files must conform to the same naming convention as used by run-parts: they must consist solely of upper- and lower-case letters, digits, underscores, and hyphens. Like /etc/crontab, the files in the /etc/cron.d directory are monitored for changes.
You should use absolute path names for commands like /bin/ls. This is to insure you call the correct command.

Crontab

Crontab is the program used to install, deinstall or list the tables used to drive the cron daemon in Vixie Cron. Each user can have their own crontab, and though these are files in /var/spool/cron/crontabs, they are not intended to be edited directly.
Each user has their own crontab, and commands in any given crontab will be executed as the user who owns the crontab. Uucp and News will usually have their own crontabs, eliminating the need for explicitly running su as part of a cron command.
Blank lines and leading spaces and tabs are ignored. Lines whose first non-space character is a hash-sign (#) are comments, and are ignored. Note that comments are not allowed on the same line as cron commands, since they will be taken to be part of the command. Similarly, comments are not allowed on the same line as environment variable settings.
An active line in a crontab will be either an environment setting or a cron command. An environ‐ ment setting is of the form: name = value where the spaces around the equal-sign (=) are optional, and any subsequent non-leading spaces in value will be part of the value assigned to name. The value string may be placed in quotes (single or double, but matching) to preserve leading or trailing blanks. The value string is not parsed for environmental substitutions, thus lines like: PATH = $HOME/bin:$PATH will not work as you might expect.
Several environment variables are set up automatically by the cron daemon. SHELL is set to /bin/sh, and LOGNAME and HOME are set from the /etc/passwd line of the crontab's owner. PATH is set to "/usr/bin:/bin". HOME, SHELL, and PATH may be overridden by settings in the crontab; LOGNAME is the user that the job is running from, and may not be changed. Another note: the LOGNAME variable is sometimes called USER on BSD systems... on these systems, USER will be set also.
In addition to LOGNAME, HOME, and SHELL, cron will look at MAILTO if it has any reason to send mail as a result of running commands in "this" crontab. If MAILTO is defined (and non-empty), mail is sent to the user so named. If MAILTO is defined but empty (MAILTO=""), no mail will be sent. Otherwise mail is sent to the owner of the crontab.
If the /etc/cron.allow file exists, then you must be listed therein in order to be allowed to use this command. If the /etc/cron.allow file does not exist but the /etc/cron.deny file does exist, then you must not be listed in the /etc/cron.deny file in order to use this command. If neither of these files exists, then depending on site-dependent configuration parameters, only the super user will be allowed to use this command, or all users will be able to use this command. For standard Debian systems, all users may use this command.
If the -u option is given, it specifies the name of the user whose crontab is to be tweaked. If this option is not given, crontab examines "your" crontab, i.e., the crontab of the person executing the command. Note that su can confuse crontab and that if you are running inside of su you should always use the -u option for safety's sake.
The first form of this command is used to install a new crontab from some named file or standard input if the pseudo-filename ``-'' is given.
The -l option causes the current crontab to be displayed on standard output.
The -r option causes the current crontab to be removed.
The -e option is used to edit the current crontab using the editor specified by the VISUAL or EDITOR environment variables. The specified editor must edit the file in place; any editor that unlinks the file and recreates it cannot be used. After you exit from the editor, the modified crontab will be installed automatically.
On the Debian GNU/Linux system, cron supports the pam_env module, and loads the environment specified by /etc/security/pam_env.conf. However, the PAM setting do NOT override the settings described above nor any settings in the crontab file itself. Note in particular that if you want a PATH other than "/usr/bin:/bin", you will need to set it in the crontab file.
By default, cron will send mail using the mail "Content-Type:" header of "text/plain" with the "charset=" parameter set to the charmap / codeset of the locale in which crond is started up - ie. either the default system locale, if no LC_* environment variables are set, or the locale specified by the LC_* environment variables ( see locale(7)). You can use different character encodings for mailed cron job output by setting the CONTENT_TYPE and CONTENT_TRANSFER_ENCODING variables in crontabs, to the correct values of the mail headers of those names.

Crontab Format

Commands are executed by cron when the minute, hour, and month of year fields match the current time, and when at least one of the two day fields (day of month, or day of week) match the current time.
A field may be an asterisk (*), which always stands for "first-last".
Ranges of numbers are allowed. Ranges are two numbers separated with a hyphen. The specified range is inclusive. For example, 8-11 for an "hours" entry specifies execution at hours 8, 9, 10 and 11.
Lists are allowed. A list is a set of numbers (or ranges) separated by commas. Examples: "1,2,5,9", "0-4,8-12".
Step values can be used in conjunction with ranges. Following a range with "/" specifies skips of the number's value through the range. For example, "0-23/2" can be used in the hours field to specify command execution every other hour (the alternative in the V7 standard is "0,2,4,6,8,10,12,14,16,18,20,22"). Steps are also permitted after an asterisk, so if you want to say "every two hours", just use "*/2".
Names can also be used for the "month" and "day of week" fields. Use the first three letters of the particular day or month (case doesn't matter). Ranges or lists of names are not allowed.
The "sixth" field (the rest of the line) specifies the command to be run. The entire command portion of the line, up to a newline or % character, will be executed by /bin/sh or by the shell specified in the SHELL variable of the crontab file. Percent-signs (%) in the command, unless escaped with backslash (\), will be changed into newline characters, and all data after the first % will be sent to the command as standard input. There is no way to split a single command line onto multiple lines, like the shell's trailing "\".
Note: The day of a command's execution can be specified by two fields - day of month, and day of week. If both fields are restricted (i.e., aren't *), the command will be run when either field matches the current time. For example, "30 4 1,15 * 5" would cause a command to be run at 4:30 am on the 1st and 15th of each month, plus every Friday.
Instead of the first five fields, one of eight special strings may appear:

string         meaning
------         -------
@reboot        Run once, at startup.
@yearly        Run once a year, "0 0 1 1 *".
@annually      (same as @yearly)
@monthly       Run once a month, "0 0 1 * *".
@weekly        Run once a week, "0 0 * * 0".
@daily         Run once a day, "0 0 * * *".
@midnight      (same as @daily)
@hourly        Run once an hour, "0 * * * *".

An example of crontab format with commented fields is as follows:

# Minute   Hour   Day of Month       Month          Day of Week        Command    
# (0-59)  (0-23)     (1-31)    (1-12 or Jan-Dec)  (0-6 or Sun-Sat)                
    0        2          12             *                *            /usr/bin/find

This line executes the "find" command at 2AM on the 12th of every month.

Examples

Here are some more examples of crontab lines. Use the command "crontab -e" to edit your crontab file.
This line executes the "ping" command every minute of every hour of every day of every month. The standard output is redirected to dev null so we will get no e-mail but will allow the standard error to be sent as a e-mail. If you want no e-mail ever change the command line to "/sbin/ping -c 1 192.168.0.1 > /dev/null 2>&1".

*       *       *       *       *       /sbin/ping -c 1 192.168.0.1 > /dev/null

This line executes the "ping" and the "ls" command every 12am and 12pm on the 1st day of every 2nd month. It also puts the output of the commands into the log file /var/log/cronrun.

0 0,12 1 */2 * /sbin/ping -c 192.168.0.1; ls -la >>/var/log/cronrun

This line executes the disk usage command to get the directory sizes every 2am on the 1st through the 10th of each month. E-mail is sent to the email addresses specified with the MAILTO line. The PATH is also set to something different.

PATH=/usr/local/sbin:/usr/local/bin:/home/user1/bin
MAILTO=user1@nowhere.org,user2@somewhere.org
0 2 1-10 * * du -h --max-depth=1 /

This line is and example of running a cron job every month at 4am on Mondays, and on the days between 15-21. This is because using the day of month and day of week fields with restrictions (no *) makes this an "or" condition not an "and" condition. Both will be executed.

0 4 15-21 * 1 /command

Run on every second Sunday of every month. The test has to be run first because of the issue mentioned in the example above.

0 4 8-14 * *  test $(date +\%u) -eq 7 && echo "2nd Sunday"

Things to look out for! Gotchas!

1. When cron job is run from the users crontab it is executed as that user. It does not however source any files in the users home directory like their .cshrc or .bashrc or any other file. If you need cron to source (read) any file that your script will need you should do it from the script cron is calling. Setting paths, sourcing files, setting environment variables, etc.
2. If the users account has a crontab but no usable shell in /etc/passwd then the cronjob will not run. You will have to give the account a shell for the crontab to run.
3. If your cronjobs are not running check if the cron daemon is running. Then remember to check /etc/cron.allow and /etc/cron.deny files. If they exist then the user you want to be able to run jobs must be in /etc/cron.allow. You also might want to check if the /etc/security/access.conf file exists. You might need to add your user in there.
4. Crontab is not parsed for environmental substitutions. You can not use things like $PATH, $HOME, or ~/sbin. You can set things like MAILTO= or PATH= and other environment variables the /bin/sh shell uses.
5. Cron does not deal with seconds so you can't have cron job's going off in any time period dealing with seconds. Like a cronjob going off every 30 seconds.
6. You can not use % in the command area. They will need to be escaped and if used with command substitution like the date command you can put it in backticks. Ex. `date +\%Y-\%m-\%d`. Or use bash's command substituion $().
7. Watch out using day of month and day of week together. Day of month and day of week fields with restrictions (no *) makes this an "or" condition not an "and" condition. When either field is true it will be executed.

SOURCE

install broadcom drivers in ubuntu

sudo apt-get install b43-fwcutter firmware-b43-installer

Installing Open JDK 7 on ubuntu 16.04

sudo add-apt-repository ppa:openjdk-r/ppa  
sudo apt-get update   
sudo apt-get install openjdk-7-jdk  

Velocity Template in java web application

Following is the code to create the text from the velocity template in a java web application.

Properties props = new Properties();
            props.setProperty("resource.loader", "class");
            props.setProperty("class.resource.loader.class", "org.apache.velocity.runtime.resource.loader.ClasspathResourceLoader");
            props.setProperty("webapp.resource.loader.path", "/WEB-INF/classes");
            VelocityEngine velocityEngine = new VelocityEngine();
            velocityEngine.init(props);
            Template template = velocityEngine.getTemplate("helloworld.vm");
             /* create our list of maps  */
            VelocityContext context = new VelocityContext();
            context.put("name", "jakki");
            /* now render the template into a StringWriter */
            StringWriter writer = new StringWriter();
            template.merge(context, writer);
            /* show the World */
            String content = writer.toString();

Template is as follows.

Hello $name!  Welcome to Dev Code Helper!

Dependency JARs

velocity-1.4.jar

velocity-dep-1.4.jar 

Getting maximum performance from MySQL

1 Changing the size of MySQL buffers

You can get the default buffer sizes used by the mysqld server with this command:

shell> mysqld --help

This command produces a list of all mysqld options and configurable variables. The output includes the default values and looks something like this:

Possible variables for option --set-variable (-O) are:
back_log              current value: 5
connect_timeout       current value: 5
join_buffer           current value: 131072
key_buffer            current value: 1048540
long_query_time       current value: 10
max_allowed_packet    current value: 1048576
max_connections       current value: 90
max_connect_errors    current value: 10
max_join_size         current value: 4294967295
max_sort_length       current value: 1024
net_buffer_length     current value: 16384
record_buffer         current value: 131072
sort_buffer           current value: 2097116
table_cache           current value: 64
tmp_table_size        current value: 1048576
thread_stack          current value: 131072
wait_timeout          current value: 28800

If there is a mysqld server currently running, you can see what values it actually is using for the variables by executing this command:

shell> mysqladmin variables

Each option is described below. Values for buffer sizes, lengths and stack sizes are given in bytes. You can specify values with a suffix of `K' or `M' to indicate kilobytes or megabytes. For example, 16M indicates 16 megabytes. Case of suffix letters does not matter; 16M and 16m are equivalent.

back_log

The number of outstanding connection requests MySQL can have. This comes into play when the main MySQL thread gets VERY many connection requests in a very short time. It then takes some time (but very short) for the main thread to check the connection and start a new thread. The back_log value indicates how many requests can be stacked during this short time before MySQL momentarily stops answering new requests. You need to increase this only if you expect a large number of connections in a short period of time. In other words, this value is the size of the listen queue for incoming TCP/IP connections. Your operating system has its own limit on the size of this queue. The manual page for the Unix system call listen(2) should have more details. Check your OS documentation for the maximum value for this variable. Attempting to set back_log higher than this maximum will be ineffective.

connect_timeout

The number of seconds the mysqld server is waiting for a connect packet before responding with Bad handshake.

join_buffer

The size of the buffer that is used for full joins (joins that do not use indexes). The buffer is allocated one time for each full join between two tables. Increase this value to get a faster full join when adding indexes is not possible. (Normally the best way to get fast joins is to add indexes.)

key_buffer

Index blocks are buffered and are shared by all threads. key_buffer is the size of the buffer used for index blocks. You might want to increase this value when doing many DELETE or INSERT operations on a table with lots of indexes. To get even more speed, use LOCK TABLES. See section 7.23 LOCK TABLES/UNLOCK TABLES syntax.

max_allowed_packet

The maximum size of one packet. The message buffer is initialized to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. This value by default is small to catch big (possibly wrong) packets. You must increase this value if you are using big BLOB columns. It should be as big as the biggest BLOB you want to use.

max_connections

The number of simultaneous clients allowed. Increasing this value increases the number of file descriptors that mysqld requires. See below for comments on file descriptor limits.

max_connect_errors

If there is more than this number of interrupted connections from a host this host will be blocked for further connections. You can unblock a host with the command FLUSH HOSTS.

max_join_size

Joins that are probably going to read more than max_join_size records return an error. Set this value if your users tend to perform joins without a WHERE clause that take a long time and return millions of rows.

max_sort_length

The number of bytes to use when sorting BLOB or TEXT values (only the first max_sort_length bytes of each value are used; the rest are ignored).

net_buffer_length

The communication buffer is reset to this size between queries. This should not normally be changed, but if you have very little memory, you can set it to the expected size of a query. (That is, the expected length of SQL statements sent by clients. If statements exceed this length, the buffer is automatically enlarged, up to max_allowed_packet bytes.)

record_buffer

Each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans, you may want to increase this value.

sort_buffer

Each thread that needs to do a sort allocates a buffer of this size. Increase this value for faster ORDER BY or GROUP BY operations. See section 16.4 Where MySQL stores temporary files.

table_cache

The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. MySQL needs two file descriptors for each unique open table. See below for comments on file descriptor limits. For information about how the table cache works, see section 10.6 How MySQL opens and closes tables.

tmp_table_size

If a temporary table exceeds this size, MySQL generates an error of the form The table tbl_name is full. Increase the value of tmp_table_size if you do many advanced GROUP BY queries.

thread_stack

The stack size for each thread. Many of the limits detected by the crash-me test are dependent on this value. The default is normally large enough. See section 11 The MySQL benchmark suite.

wait_timeout

The number of seconds the server waits for activity on a connection before closing it.

table_cache and max_connections affect the maximum number of files the server keeps open. If you increase one or both of these values, you may run up against a limit imposed by your operating system on the per-process number of open file descriptors. However, you can increase the limit on many systems. Consult your OS documentation to find out how to do this, because the method for changing the limit varies widely from system to system.
table_cache is related to max_connections. For example, for 200 open connections, you should have a table cache of at least 200 * n, where n is the maximum number of tables in a join.
MySQL uses algorithms that are very scalable, so you can usually run with very little memory or give MySQL more memory to get better performance.
If you have much memory and many tables and want maximum performance with a moderate number of clients, you should use something like this:

shell> safe_mysqld -O key_buffer=16M -O table_cache=128 \
           -O sort_buffer=4M -O record_buffer=1M &

If you have little memory and lots of connections, use something like this:

shell> safe_mysqld -O key_buffer=512k -O sort_buffer=100k \
           -O record_buffer=100k &

or even:

shell> safe_mysqld -O key_buffer=512k -O sort_buffer=16k \
           -O table_cache=32 -O record_buffer=8k -O net_buffer=1K &

If there are very many connections, "swapping problems" may occur unless mysqld has been configured to use very little memory for each connection. mysqld performs better if you have enough memory for all connections, of course.
Note that if you change an option to mysqld, it remains in effect only for that instance of the server.
To see the effects of a parameter change, do something like this:

shell> mysqld -O key_buffer=32m --help

Make sure that the --help option is last; otherwise, the effect of any options listed after it on the command line will not be reflected in the output.

2 How MySQL uses memory

The list below indicates some of the ways that the mysqld server uses memory. Where applicable, the name of the server variable relevant to the memory use is given.

• The key buffer (variable key_buffer) is shared by all threads; Other buffers used by the server are allocated as needed.
• Each connection uses some thread specific space; A stack (64K, variable thread_stack) a connection buffer (variable net_buffer_length), and a result buffer (variable net_buffer_length). The connection buffer and result buffer are dynamicly enlarged up to max_allowed_packet when needed. When a query is running a copy of the current query string is also alloced.
• All threads share the same base memory.
• Nothing is memory-mapped yet (except compressed tables, but that's another story). This is because the 32-bit memory space of 4GB is not large enough for most large tables. When we get a system with a 64-bit address space, we may add general support for memory-mapping.
• Each request doing a sequential scan over a table allocates a read buffer (variable record_buffer).
• All joins are done in one pass and most joins can be done without even using a temporary table. Most temporary tables are memory-based (HEAP) tables. Temporary tables with a big record length (calculated as the sum of all column lengths) or that contain BLOB columns are stored on disk. One current problem is that if a HEAP table exceeds the size of tmp_table_size, you get the error The table tbl_name is full. In the future, we will fix this by automatically changing the in-memory (HEAP) table to a disk-based (NISAM) table as necessary. To work around this problem, you can increase the temporary table size by setting the tmp_table_size option to mysqld, or by setting the SQL option SQL_BIG_TABLES in the client program. See section 7.24 SET OPTION syntax. In MySQL 3.20, the maximum size of the temporary table was record_buffer*16, so if you are using this version, you have to increase the value of record_buffer. You can also start mysqld with the --big-tables option to always store temporary tables on disk, however, this will affect the speed of all complicated queries.
• Most requests doing a sort allocate a sort buffer and one or two temporary files. See section 16.4 Where MySQL stores temporary files.
• Almost all parsing and calculating is done in a local memory store. No memory overhead is needed for small items and the normal slow memory allocation and freeing is avoided. Memory is allocated only for unexpectedly large strings (this is done with malloc() and free()).
• Each index file is opened once and the data file is opened once for each concurrently-running thread. For each concurrent thread, a table structure, column structures for each column, and a buffer of size 3 * n is allocated (where n is the maximum row length, not counting BLOB columns). A BLOB uses 5 to 8 bytes plus the length of the BLOB data.
• For each table having BLOB columns, a buffer is enlarged dynamically to read in larger BLOB values. If you scan a table, a buffer as large as the largest BLOB value is allocated.
• Table handlers for all in-use tables are saved in a cache and managed as a FIFO. Normally the cache has 64 entries. If a table has been used by two running threads at the same time, the cache contains two entries for the table. See section 10.6 How MySQL opens and closes tables.
• A mysqladmin flush-tables command closes all tables that are not in use and marks all in-use tables to be closed when the currently executing thread finishes. This will effectively free most in-use memory.

ps and other system status programs may report that mysqld uses a lot of memory. This may be caused by thread-stacks on different memory addresses. For example, the Solaris version of ps counts the unused memory between stacks as used memory. You can verify this by checking available swap with swap -s. We have tested mysqld with commercial memory-leakage detectors, so there should be no memory leaks.

3 How compiling and linking affects the speed of MySQL

Most of the following tests are done on Linux and with the MySQL benchmarks, but they should give some indication for other operating systems.
You get the fastest executable when you link with -static. Using Unix sockets rather than TCP/IP to connect to a database also gives better performance.
On Linux, you will get the fastest code when compiling with pgcc and -O6. To compile `sql_yacc.cc' with these options, you need 180M memory because gcc/pgcc needs a lot of memory to make all functions inline. You should also set CXX=gcc when configuring MySQL to avoid inclusion of the libstdc++ library.

• If you use pgcc and compile everything with -O6, the mysqld server is 11% faster than with gcc.
• If you link dynamically (without -static), the result is 13% slower.
• If you connect using TCP/IP rather than Unix sockets, the result is 7.5% slower.
• On a Sun sparcstation 10, gcc 2.7.3 is 13% faster than Sun Pro C++ 4.2.
• On Solaris 2.5.1, MIT-pthreads is 8-12% slower than Solaris native threads.

The MySQL-Linux distribution provided by TcX is compiled with pgcc and linked statically.

4 How MySQL uses indexes

All indexes (PRIMARY, UNIQUE and INDEX()) are stored in B-trees. Strings are automatically prefix- and end-space compressed. See section 7.26 CREATE INDEX syntax (Compatibility function).
Indexes are used to:

• Quickly find the rows that match a WHERE clause.
• Retrieve rows from other tables when performing joins.
• Find the MAX() or MIN() value for a specific key.
• Sort or group a table if the sorting or grouping is done on a leftmost prefix of a usable key (e.g., ORDER BY key_part_1,key_part_2 ). The key is read in reverse order if all key parts are followed by DESC.
• Retrieve values without consulting the data file, in some cases. If all used columns for some table are numeric and form a leftmost prefix for some key, the values may be retrieved from the index tree for greater speed.

Suppose you issue the following SELECT statement:

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimizer decides which index will find fewer rows and uses that index to fetch the rows.
If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows. For example, if you have a three-column index on (col1,col2,col3), you have indexed search capabilities on (col1), (col1,col2) and (col1,col2,col3).
MySQL can't use a partial index if the columns don't form a leftmost prefix of the index. Suppose you have the SELECT statements shown below:

mysql> SELECT * FROM tbl_name WHERE col1=val1;
mysql> SELECT * FROM tbl_name WHERE col2=val2;
mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

If an index exists on (col1,col2,col3), only the first query shown above uses the index. The second and third queries do involve indexed columns, but (col2) and (col2,col3) are not leftmost prefixes of (col1,col2,col3).
MySQL also uses indexes for LIKE comparisons if the argument to LIKE is a constant string that doesn't start with a wildcard character. For example, the following SELECT statements use indexes:

mysql> select * from tbl_name where key_col LIKE "Patrick%";
mysql> select * from tbl_name where key_col LIKE "Pat%_ck%";

In the first statement, only rows with "Patrick" <= key_col < "Patricl" are considered. In the second statement, only rows with "Pat" <= key_col < "Pau" are considered.
The following SELECT statements will not use indexes:

mysql> select * from tbl_name where key_col LIKE "%Patrick%";
mysql> select * from tbl_name where key_col LIKE other_col;

In the first statement, the LIKE value begins with a wildcard character. In the second statement, the LIKE value is not a constant.
MySQL normally uses the index that finds least number of rows. An index is used for columns that you compare with the following operators: =, >, >=, <, <=, BETWEEN and a LIKE with a non-wildcard prefix like 'something%'.
Any index that doesn't span all AND levels in the WHERE clause is not used to optimize the query.
The following WHERE clauses use indexes:

... WHERE index_part1=1 AND index_part2=2
... WHERE index=1 OR A=10 AND index=2      /* index = 1 OR index = 2 */
... WHERE index_part1='hello' AND index_part_3=5
          /* optimized like "index_part1='hello'" */

These WHERE clauses do NOT use indexes:

... WHERE index_part2=1 AND index_part3=2  /* index_part_1 is not used */
... WHERE index=1 OR A=10                  /* No index */
... WHERE index_part1=1 OR index_part2=10  /* No index spans all rows */

5 How MySQL optimizes WHERE clauses

(This section is incomplete; MySQL does many optimizations.)
In general, when you want to make a slow SELECT ... WHERE faster, the first thing to check is whether or not you can add an index. All references between different tables should usually be done with indexes. You can use the EXPLAIN command to determine which indexes are used for a SELECT. See section 7.21 EXPLAIN syntax (Get information about a SELECT).
Some of the optimizations performed by MySQL are listed below:

• Removal of unnecessary parentheses:

     ((a AND b) AND c OR (((a AND b) AND (c AND d))))
  -> (a AND b AND c) OR (a AND b AND c AND d)
  

• Constant folding:

     (a<b AND b=c) AND a=5
  -> b>5 AND b=c AND a=5
  

• Constant condition removal (needed because of constant folding):

     (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
  -> B=5 OR B=6
  

• Constant expressions used by indexes are evaluated only once.
• COUNT(*) on a single table without a WHERE is retrieved directly from the table information. This is also done for any NOT NULL expression when used with only one table.
• Early detection of invalid constant expressions. MySQL quickly detects that some SELECT statements are impossible and returns no rows.
• HAVING is merged with WHERE if you don't use GROUP BY or group functions (COUNT(), MIN()...)
• For each sub join, a simpler WHERE is constructed to get a fast WHERE evaluation for each sub join and also to skip records as soon as possible.
• All constant tables are read first, before any other tables in the query. A constant table is:
  • An empty table or a table with 1 row.
  • A table that is used with a WHERE clause on a UNIQUE index or a PRIMARY KEY, where all index parts are used with constant expressions.
  All the following tables are used as constant tables:

  mysql> SELECT * FROM t WHERE primary_key=1;
  mysql> SELECT * FROM t1,t2
             WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
  

• The best join combination to join the tables is found by trying all possibilities :(. If all columns in ORDER BY and in GROUP BY come from the same table, then this table is preferred first when joining.
• If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.
• Each table index is queried and the best index that spans less than 30% of the rows is used. If no such index can be found, a quick table scan is used.
• In some cases, MySQL can read rows from the index without even consulting the data file. If all columns used from the index are numeric, then only the index tree is used to resolve the query.
• Before each record is output, those that do not match the HAVING clause are skipped.

Some examples of queries that are very fast:

mysql> SELECT COUNT(*) FROM tbl_name;
mysql> SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;
mysql> SELECT MAX(key_part2) FROM tbl_name
           WHERE key_part_1=constant;
mysql> SELECT ... FROM tbl_name
           ORDER BY key_part1,key_part2,... LIMIT 10;
mysql> SELECT ... FROM tbl_name
           ORDER BY key_part1 DESC,key_part2 DESC,... LIMIT 10;

The following queries are resolved using only the index tree (assuming the indexed columns are numeric):

mysql> SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;
mysql> SELECT COUNT(*) FROM tbl_name
           WHERE key_part1=val1 and key_part2=val2;
mysql> SELECT key_part2 FROM tbl_name GROUP BY key_part1;

The following queries use indexing to retrieve the rows in sorted order without a separate sorting pass:

mysql> SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,...
mysql> SELECT ... FROM tbl_name ORDER BY key_part1 DESC,key_part2 DESC,...

6 How MySQL opens and closes tables

The cache of open tables can grow to a maximum of table_cache (default 64; this can be changed with with the -O table_cache=# option to mysqld). A table is never closed, except when the cache is full and another thread tries to open a table or if you use mysqladmin refresh or mysqladmin flush-tables.
When the table cache fills up, the server uses the following procedure to locate a cache entry to use:

• Tables that are not currently in use are released, in least-recently-used order.
• If the cache is full and no tables can be released, but a new table needs to be opened, the cache is temporarily extended as necessary.
• If the cache is in a temporarily-extended state and a table goes from in-use to not-in-use state, it is closed and released from the cache.

A table is opened for each concurrent access. This means that if you have two threads accessing the same table or access the table twice in the same query (with AS) the table needs to be opened twice. The first open of any table takes two file descriptors; each additional use of the table takes only one file descriptor. The extra descriptor for the first open is used for the index file; this descriptor is shared among all threads.

6.1 Drawbacks of creating large numbers of tables in a database

If you have many files in a directory, open, close and create operations will be slow. If you execute SELECT statements on many different tables, there will be a little overhead when the table cache is full, because for every table that has to be opened, another must be closed. You can reduce this overhead by making the table cache larger.

7 Why so many open tables?

When you run mysqladmin status, you'll see something like this:

Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12

This can be somewhat perplexing if you only have 6 tables.
MySQL is multithreaded, so it may have many queries on the same table at once. To minimize the problem with two threads having different states on the same file, the table is opened independently by each concurrent thread. This takes some memory and one extra file descriptor for the data file. The index file descriptor is shared between all threads.

8 Using symbolic links for databases and tables

You can move tables and databases from the database directory to other locations and replace them with symbolic links to the new locations. You might want to do this, for example, to move a database to a file system with more free space.
If MySQL notices that a table is a symbolically-linked, it will resolve the symlink and use the table it points to instead. This works on all systems that support the realpath() call (at least Linux and Solaris support realpath())! On systems that don't support realpath(), you should not access the table through the real path and through the symlink at the same time! If you do, the table will be inconsistent after any update.
MySQL doesn't support linking of databases by default. Things will work fine as long as you don't make a symbolic link between databases. Suppose you have a database db1 under the MySQL data directory, and then make a symlink db2 that points to db1:

shell> cd /path/to/datadir
shell> ln -s db1 db2

Now, for any table tbl_a in db1, there also appears to be a table tbl_a in db2. If one thread updates db1.tbl_a and another thread updates db2.tbl_a, there will be problems.
If you really need this, you must change the following code in `mysys/mf_format.c':

if (!lstat(to,&stat_buff))  /* Check if it's a symbolic link */
    if (S_ISLNK(stat_buff.st_mode) && realpath(to,buff))

Change the code to this:

if (realpath(to,buff))

9 How MySQL locks tables

All locking in MySQL is deadlock-free. This is managed by always requesting all needed locks at once at the beginning of a query and always locking the tables in the same order.
The locking method MySQL uses for WRITE locks works as follows:

• If there are no locks on the table, put a write lock on it.
• Otherwise, put the lock request in the write lock queue.

The locking method MySQL uses for READ locks works as follows:

• If there are no write locks on the table, put a read lock on it.
• Otherwise, put the lock request in the read lock queue.

When a lock is released, the lock is made available to the threads in the write lock queue, then to the threads in the read lock queue.
This means that if you have many updates on a table, SELECT statements will wait until there are no more updates.
To work around this for the case where you want to do many INSERT and SELECT operations on a table, you can insert rows in a temporary table and update the real table with the records from the temporary table once in a while.
This can be done with the following code:

mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
mysql> insert into real_table select * from insert_table;
mysql> delete from insert_table;
mysql> UNLOCK TABLES;

You can use the LOW_PRIORITY or HIGH_PRIORITY options with INSERT if you want to prioritize retrieval in some specific cases. See section 7.13 INSERT syntax
You could also change the locking code in `mysys/thr_lock.c' to use a single queue. In this case, write locks and read locks would have the same priority, which might help some applications.

10 How to arrange a table to be as fast/small as possible

You can get better performance on a table and minimize storage space using the techniques listed below:

• Declare columns to be NOT NULL if possible. It makes everything faster and you save one bit per column.
• Take advantage of the fact that all columns have default values. Insert values explicitly only when the value to be inserted differs from the default. You don't have to insert a value into the first TIMESTAMP column or into an AUTO_INCREMENT column in an INSERT statement. See section 18.4.49 How can I get the unique ID for the last inserted row?.
• Use the smaller integer types if possible to get smaller tables. For example, MEDIUMINT is often better than INT.
• If you don't have any variable-length columns (VARCHAR, TEXT or BLOB columns), a fixed-size record format is used. This is much faster but unfortunately may waste some space. See section 10.15 What are the different row formats? Or, when should VARCHAR/CHAR be used?.
• To help MySQL optimize queries better, run isamchk --analyze on a table after it has been loaded with relevant data. This updates a value for each index that indicates the average number of rows that have the same value. (For unique indexes, this is always 1, of course.)
• To sort an index and data according to an index, use isamchk --sort-index --sort-records=1 (if you want to sort on index 1). If you have a unique index from which you want to read all records in order according to that index, this is a good way to make that faster.
• For INSERT statements, use multiple value lists if possible. This is much faster than using separate INSERT statements.
• When loading a table with data, use LOAD DATA INFILE. This is usually 20 times faster than using a lot of INSERT statements. See section 7.15 LOAD DATA INFILE syntax. You can even get more speed when loading data into a table with many indexes using the following procedure:
  1. Create the table in mysql or Perl with CREATE TABLE.
  2. Execute mysqladmin flush-tables.
  3. Use isamchk --keys-used=0 /path/to/db/tbl_name. This will remove all usage of all indexes from the table.
  4. Insert data into the table with LOAD DATA INFILE.
  5. If you have pack_isam and want to compress the table, run pack_isam on it.
  6. Recreate the indexes with isamchk -r -q /path/to/db/tbl_name.
  7. Execute mysqladmin flush-tables.
• To get some more speed for both LOAD DATA INFILE and INSERT, enlarge the key buffer. This can be done with the -O key_buffer=# option to mysqld or safe_mysqld. For example, 16M should be a good value if you have much RAM. :)
• When dumping data as text files for use by other programs, use SELECT ... INTO OUTFILE. See section 7.15 LOAD DATA INFILE syntax.
• When doing many successive inserts or updates, you can get more speed by locking your tables using LOCK TABLES. LOAD DATA INFILE and SELECT ...INTO OUTFILE are atomic, so you don't have to use LOCK TABLES when using them. See section 7.23 LOCK TABLES/UNLOCK TABLES syntax.

To check how fragmented your tables are, run isamchk -evi on the `.ISM' file. See section 13 Using isamchk for table maintenance and crash recovery.

11 Table locking issues

The table locking code in MySQL is deadlock free.
MySQL uses table locking (instead of row locking or column locking) to achieve a very high lock speed. For large tables, table locking is MUCH better than row locking, but there are of course some pitfalls.
Table locking enables many threads to read from a table at the same time, but if a thread wants to write to a table, it must first get exclusive access. During the update all others threads that want to access this particular table will wait until the update is ready.
As updates of databases normally are considered to be more important than SELECT, all statements that update a table have higher priority than statements that retrieve information from a table. This should ensure that updates are not 'starved' because one issues a lot of heavy queries against a specific table.
One main problem with this is the following:

• A client issues a SELECT that takes a long time to run.
• Another client then issues an INSERT on a used table; This client will wait until the SELECT is finished..
• Another client issues another SELECT statement on the same table; As INSERT has higher priority than SELECT, this SELECT will wait for the INSERT to finish. It will also wait for the first SELECT to finish!

Some possible solutions to this problem are:

• Try to get the SELECT statements to run faster; You may have to create some summary tables to do this.
• Start mysqld with --low-priority-inserts. This will give all statements that update a table lower priority than a SELECT statement. In this case the last SELECT statement in the previous scenario would execute before the INSERT statement.
• You can give a specific INSERT,UPDATE or DELETE statement lower priority with the LOW_PRIORITY attribute.
• You can specify that all updates from a specific thread should be done with low priority by using the SQL command: SET SQL_LOW_PRIORITY_UPDATES=1. See section 7.24 SET OPTION syntax.
• You can specify that a specific SELECT is very important with the HIGH_PRIORITY attribute. See section 7.11 SELECT syntax.
• If you mainly mix INSERT and SELECT statements, the DELAYED attribute to INSERT will probably solve your problems. See section 7.13 INSERT syntax.
• If you have problems with SELECT and DELETE, the LIMIT option to DELETE may help. See section 7.10 DELETE syntax.

12 Factors affecting the speed of INSERT statements

The time to insert a record consists of:

• Connect: (3)
• Sending query to server: (2)
• Parsing query: (2)
• Inserting record: (1 x size of record)
• Inserting indexes: (1 x indexes)
• Close: (1)

Where (number) is proportional time. This does not take into consideration the initial overhead to open tables (which is done once for each concurrently-running query).
The size of the table slows down the insertion of indexes by N log N (B-trees).
You can speed up insertions by locking your table and/or using multiple value lists with INSERT statements. Using multiple value lists can be up to 5 times faster than using separate inserts.

mysql> LOCK TABLES a WRITE;
mysql> INSERT INTO a VALUES (1,23),(2,34),(4,33);
mysql> INSERT INTO a VALUES (8,26),(6,29);
mysql> UNLOCK TABLES;

The main speed difference is that the index buffer is flushed to disk only once, after all INSERT statements have completed. Normally there would be as many index buffer flushes as there are different INSERT statements. Locking is not needed if you can insert all rows with a single statement.
Locking will also lower the total time of multi-connection tests, but the maximum wait time for some threads will go up (because they wait for locks). For example:

thread 1 does 1000 inserts
thread 2, 3, and 4 does 1 insert
thread 5 does 1000 inserts

If you don't use locking, 2, 3 and 4 will finish before 1 and 5. If you use locking, 2, 3 and 4 probably will not finish before 1 or 5, but the total time should be about 40% faster.
As INSERT, UPDATE and DELETE operations are very fast in MySQL, you will obtain better overall performance by adding locks around everything that does more than about 5 inserts or updates in a row. If you do very many inserts in a row, you could do a LOCK TABLES followed by a UNLOCK TABLES once in a while (about each 1000 rows) to allow other threads access to the table. This would still result in a nice performance gain.
Of course, LOAD DATA INFILE is much faster still.
If you are inserting a lot of rows from different clients, you can get higher speed by using the INSERT DELAYED statement. See section 7.13 INSERT syntax.

13 Factors affecting the speed of DELETE statements

The time to delete a record is exactly proportional to the number of indexes. To delete records more quickly, you can increase the size of the index cache. The default index cache is 1M; to get faster deletes, it should be increased by several factors (try 16M if you have enough memory).

14 How do I get MySQL to run at full speed?

Start by benchmarking your problem! You can take any program from the MySQL benchmark suite (normally found in the `sql-bench' directory) and modify it for your needs. By doing this, you can try different solutions to your problem and test which is really the fastest solution for you.

• Start mysqld with the correct options. More memory gives more speed if you have it. See section 10.1 Changing the size of MySQL buffers.
• Create indexes to make your SELECT statements faster. See section 10.4 How MySQL uses indexes.
• Optimize your column types to be as efficient as possible. For example, declere columns to be NOT NULL if possible. See section 10.10 How to arrange a table to be as fast/small as possible.
• The --skip-locking option disables file locking between SQL requests. This gives greater speed but has the following consequences:
  • You MUST flush all tables with mysqladmin flush-tables before you try to check or repair tables with isamchk. (isamchk -d tbl_name is always allowed, since that simply displays table information.)
  • You can't run two MySQL servers on the same data files, if both are going to update the same tables.
  The --skip-locking option is on by default when compiling with MIT-pthreads, because flock() isn't fully supported by MIT-pthreads on all platforms.
• If updates are a problem, you can delay updates and then do many updates in a row later. Doing many updates in a row is much quicker than doing one at a time.
• On FreeBSD systems, if the problem is with MIT-pthreads, upgrading to FreeBSD 3.0 (or higher) should help. This makes it possible to use Unix sockets (with FreeBSD, this is quicker than connecting using TCP/IP with MIT-pthreads) and the threads package is much more integrated.
• GRANT checking on the table or column level will decrease performance.

If your problem is with some explicit MySQL function, you can always time this in the MySQL client:

mysql> select benchmark(1000000,1+1);
+------------------------+
| benchmark(1000000,1+1) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.32 sec)

The above shows that MySQL can execute 1,000,000 + expressions in 0.32 seconds on a simple PentiumII 400MHz.
All MySQL functions should be very optimized, but there may be some exceptions and the benchmark(loop_count,expression) is a great tool to find if this is a problem with your query.

15 What are the different row formats? Or, when should VARCHAR/CHAR be used?

MySQL dosen't have true SQL VARCHAR types.
Instead, MySQL has three different ways to store records and uses these to emulate VARCHAR.
If a table doesn't have any VARCHAR, BLOB or TEXT columns, a fixed row size is used. Otherwise a dynamic row size is used. CHAR and VARCHAR columns are treated identically from the application's point of view; both have trailing spaces removed when the columns are retrieved.
You can check the format used in a table with isamchk -d (-d means "describe the table").
MySQL has three different table formats: fixed-length, dynamic and compressed. These are compared below.
Fixed-length tables

• This is the default format. It's used when the table contains no VARCHAR, BLOB or TEXT columns.
• All CHAR, NUMERIC and DECIMAL columns are space-padded to the column width.
• Very quick.
• Easy to cache.
• Easy to reconstruct after a crash, because records are located in fixed positions.
• Doesn't have to be reorganized (with isamchk) unless a huge number of records are deleted and you want to return free disk space to the operating system.
• Usually requires more disk space than dynamic tables.

Dynamic tables

• This format is used if the table contains any VARCHAR, BLOB or TEXT columns.
• All string columns are dynamic (except those with a length less than 4).
• Each record is preceded by a bitmap indicating which columns are empty (") for string columns, or zero for numeric columns (this isn't the same as columns containing NULL values). If a string column has a length of zero after removal of trailing spaces, or a numeric column has a value of zero, it is marked in the bit map and not saved to disk. Non-empty strings are saved as a length byte plus the string contents.
• Usually takes much less disk space than fixed-length tables.
• Each record uses only as much space as is required. If a record becomes larger, it is split into as many pieces as required. This results in record fragmentation.
• If you update a row with information that extends the row length, the row will be fragmented. In this case, you may have to run isamchk -r from time to time to get better performance. Use isamchk -ei tbl_name for some statistics.
• Not as easy to reconstruct after a crash, because a record may be fragmented into many pieces and a link (fragment) may be missing.
• The expected row length for dynamic sized records is:

  3
  + (number of columns + 7) / 8
  + (number of char columns)
  + packed size of numeric columns
  + length of strings
  + (number of NULL columns + 7) / 8
  

  There is a penalty of 6 bytes for each link. A dynamic record is linked whenever an update causes an enlargement of the record. Each new link will be at least 20 bytes, so the next enlargement will probably go in the same link. If not, there will be another link. You may check how many links there are with isamchk -ed. All links may be removed with isamchk -r.

Compressed tables

• A read-only table made with the pack_isam utility. All customers with extended MySQL email support are entitled to a copy of pack_isam for their internal usage.
• The uncompress code exists in all MySQL distributions so that even customers who don't have pack_isam can read tables that were compressed with pack_isam (as long as the table was compressed on the same platform).
• Takes very little disk space. Minimises disk usage.
• Each record is compressed separately (very little access overhead). The header for a record is fixed (1-3 bytes) depending on the biggest record in the table. Each column is compressed differently. Some of the compression types are:
  • There is usually a different Huffman table for each column.
  • Suffix space compression.
  • Prefix space compression.
  • Numbers with value 0 are stored using 1 bit.
  • If values in an integer column have a small range, the column is stored using the smallest possible type. For example, a BIGINT column (8 bytes) may be stored as a TINYINT column (1 byte) if all values are in the range 0 to 255.
  • If a column has only a small set of possible values, the column type is converted to ENUM.
  • A column may use a combination of the above compressions.
• Can handle fixed or dynamic length records, but not BLOB or TEXT columns.
• Can be uncompressed with isamchk.

MySQL can support different index types, but the normal type is NISAM. This is a B-tree index and you can roughly calculate the size for the index file as (key_length+4)*0.67, summed over all keys. (This is for the worst case when all keys are inserted in sorted order.)
String indexes are space compressed. If the first index part is a string, it will also be prefix compressed. Space compression makes the index file smaller if the string column has a lot of trailing space or is a VARCHAR column that is not always used to the full length. Prefix compression helps if there are many strings with an identical prefix.

SOURCE