Package org.apache.commons.jcs3.auxiliary.disk.jdbc.mysql

Class MySQLTableOptimizer

java.lang.Object
org.apache.commons.jcs3.auxiliary.disk.jdbc.mysql.MySQLTableOptimizer
public class MySQLTableOptimizer extends Object
The MySQL Table Optimizer can optimize MySQL tables. It knows how to optimize for MySQL databases in particular and how to repair the table if it is corrupted in the process.

We will probably be able to abstract out a generic optimizer interface from this class in the future.

  • Constructor Details

    • MySQLTableOptimizer

      public MySQLTableOptimizer(MySQLDiskCacheAttributes attributes, TableState tableState, DataSource dataSource)
      This constructs an optimizer with the disk can properties.

      Parameters:
      attributes -
      tableState - We mark the table status as optimizing when this is happening.
      dataSource - access to the database

  • Method Details

    • optimizeTable

      public boolean optimizeTable()
      A scheduler will call this method. When it is called the table state is marked as optimizing. TODO we need to verify that no deletions are running before we call optimize. We should wait if a deletion is in progress.

      This restores when there is an optimization error. The error output looks like this: 

                 mysql> optimize table JCS_STORE_FLIGHT_OPTION_ITINERARY;
               +---------------------------------------------+----------+----------+---------------------+
               | Table                                       | Op       | Msg_type | Msg_text            |
               +---------------------------------------------+----------+----------+---------------------+
               | jcs_cache.JCS_STORE_FLIGHT_OPTION_ITINERARY | optimize | error    | 2 when fixing table |
               | jcs_cache.JCS_STORE_FLIGHT_OPTION_ITINERARY | optimize | status   | Operation failed    |
               +---------------------------------------------+----------+----------+---------------------+
               2 rows in set (51.78 sec)
      A successful repair response looks like this: 
              mysql> REPAIR TABLE JCS_STORE_FLIGHT_OPTION_ITINERARY;
            +---------------------------------------------+--------+----------+----------------------------------------------+
            | Table                                       | Op     | Msg_type | Msg_text                                     |
            +---------------------------------------------+--------+----------+----------------------------------------------+
            | jcs_cache.JCS_STORE_FLIGHT_OPTION_ITINERARY | repair | error    | 2 when fixing table                          |
            | jcs_cache.JCS_STORE_FLIGHT_OPTION_ITINERARY | repair | warning  | Number of rows changed from 131276 to 260461 |
            | jcs_cache.JCS_STORE_FLIGHT_OPTION_ITINERARY | repair | status   | OK                                           |
            +---------------------------------------------+--------+----------+----------------------------------------------+
            3 rows in set (3 min 5.94 sec)
      A successful optimization looks like this: 
             mysql> optimize table JCS_STORE_DEFAULT;
           +-----------------------------+----------+----------+----------+
           | Table                       | Op       | Msg_type | Msg_text |
           +-----------------------------+----------+----------+----------+
           | jcs_cache.JCS_STORE_DEFAULT | optimize | status   | OK       |
           +-----------------------------+----------+----------+----------+
           1 row in set (1.10 sec)
      Returns:
      true if it worked

    • getTableStatus

      protected String getTableStatus(Statement sStatement) throws SQLException
      This calls show table status and returns the result as a String.

      Parameters:
      sStatement -
      Returns:
      String
      Throws:
      SQLException

    • repairTable

      protected boolean repairTable(Statement sStatement) throws SQLException
      This is called if the optimization is in error.

      It looks for "OK" in response. If it find "OK" as a message in any result set row, it returns true. Otherwise we assume that the repair failed.

      Parameters:
      sStatement -
      Returns:
      true if successful
      Throws:
      SQLException

    • setTableName

      public void setTableName(String tableName)
      Parameters:
      tableName - The tableName to set.

    • getTableName

      public String getTableName()
      Returns:
      Returns the tableName.