June 26, 2009

check-unused-keys: A tool to interact with INDEX_STATISTICS

Posted by Ryan Lowe |

With the growing adoption of Google’s User Statistics Patch**, the need for supporting scripts has become clear. To that end, we’ve created check-unused-keys, a Perl script to provide a nicer interface than directly querying the INFORMATION_SCHEMA database.

check-unused-keys can be invoked and used as follows:

%> check-unused-keys --help
Usage:
     check-unused-keys [OPTIONS]

     Options:
       -d, --databases=<dbname>  Comma-separated list of databases to check
       -h, --help                Display this message and exit
       -H, --hostname=<hostname> The target MySQL server host
       --ignore-databases        Comma-separated list of databases to ignore
       --ignore-indexes          Comma-separated list of indexes to ignore
                                     db_name.tbl_name.index_name
       --ignore-tables           Comma-separated list of tables to ignore
                                     db_name.tbl_name
       --options-file            The options file to use
       -p, --password=<password> The password of the MySQL user
       -i, --port=<portnum>      The port MySQL is listening on
       -s, --socket=<sockfile>   Use the specified mysql unix socket to connect
       -t, --tables=<tables>     Comma-separated list of tables to evaluate
                                     db_name.tbl_name
       --[no]summary             Display summary information
       -u, --username=<username> The MySQL user used to connect
       -v, --verbose             Increase verbosity level
       -V, --version             Display version information and exit

     Defaults are:

     ATTRIBUTE                  VALUE
     -------------------------- ------------------
     databases                  ALL databases
     help                       FALSE
     hostname                   localhost
     ignore-databases           No default value
     ignore-indexes             No default value
     ignore-tables              No default value
     options-file               ~/.my.cnf
     password                   No default value
     port                       3306
     socket                     No default value
     summary                    TRUE
     tables                     No Default Value
     username                   No default value
     verbose                    0 (out of 2)
     version                    FALSE

** FULLTEXT indexes are not taken into account by this patch, so be wary. And, as always, sanity check the suggestions and test thoroughly before making changes to production.

Related posts: :Unused indexes by single query::Dropping unused indexes::Innodb locking and Foreign Keys:
 

7 Comments »

  1. into maatkit?

    Comment :: June 26, 2009 @ 3:25 pm

  2. 2. Ryan Lowe

    @Arjen someday perhaps:)

    Comment :: June 26, 2009 @ 3:40 pm

  3. Does this script work on a standard Mysql installation (without Google’s user_statistics) ?

    Comment :: June 27, 2009 @ 1:21 am

  4. 4. Ryan Lowe

    @petitchevalroux No, it does not. It queries the INFORMATION_SCHEMA.INDEX_STATISTICS table, which is only available with the Google patch set.

    Comment :: June 27, 2009 @ 2:29 pm

  5. I’m pretty sure that is only available with the Percona patches. The Google patches added SHOW commands, we turned them into INFORMATION_SCHEMA tables (I might be wrong about that).

    Comment :: June 28, 2009 @ 6:56 am

  6. 6. Lance Li

    it looks there is a typo

    # diff check-unused-keys-0.0.2 check-unused-keys-0.0.2.1
    153c153
    my @tbls = split(/,/, $OPTIONS{’tables’});

    Comment :: June 28, 2009 @ 8:58 pm

  7. 7. Lance Li

    sorry for the duplicate comment… it looks i cannot paste a diff result here.

    but i really think you know what i mean… :)

    Comment :: June 28, 2009 @ 9:01 pm

 

Subscribe without commenting

Trackbacks/Pingbacks