OurSQL Episode 154: Tooling Around, Part 4

This week we continue our series exploring the MySQL Utilities. Ear Candy is using the performance schema to find out which accounts fail to properly close connections, and At the Movies has a video about databases and determinism.

Part 1 of MySQL Utilities
Part 2 of MySQL Utilities
Part 3 of MySQL Utilities
Part 5 of MySQL Utilities

Events
DB Hangops - every other Wednesay at noon Pacific time

Upcoming MySQL events

MySQL Connect is happening in San Francisco from Saturday September 21st - Monday September 23rd.

MySQL Tech Day in Paris Thursday Oct 10th

Percona Live London 2013 is happening Monday November 11th and Tuesday November 12th, 2013 at the Millenium Gloucester Conference Center

Training
SkySQL Trainings
Tungsten University trainings
Oracle's MySQL Trainings
Percona Trainings

MySQL Utilities part 4
mysqlindexcheck
Episode 80, where we talked about pt-duplicate-key-checker

Comparison of pt-duplicate-key-checker and mysqlindexcheck for a table with no redundant indexes

# pt-duplicate-key-checker --tables bugs.whine_events
# ########################################################################
# Summary of indexes                                                     
# ########################################################################
# Total Indexes  3

# mysqlindexcheck  --server=root:PASSWORD@localhost:/var/lib/mysql/mysql.sock bugs.whine_events
# Source on localhost: ... connected.


Comparison of the tools for a table with redundant indexes
SHOW CREATE TABLE output for a table with duplicate keys on the visibility_value_id field:

*************************** 1. row ***************************
       Table: cf_status_20
Create Table: CREATE TABLE `cf_status_20` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `value` varchar(64) NOT NULL,
  `sortkey` smallint(6) NOT NULL DEFAULT '0',
  `isactive` tinyint(4) NOT NULL DEFAULT '1',
  `visibility_value_id` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `cf_status_193_value_idx` (`value`),
  KEY `cf_status_193_visibility_value_id_idx` (`visibility_value_id`),
  KEY `cf_status_193_sortkey_idx` (`sortkey`,`value`),
  KEY `cf_status_20_visibility_value_id_idx` (`visibility_value_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8

pt-duplicate-key-checker output:

# pt-duplicate-key-checker --tables bugs.cf_status_20
# ########################################################################
# bugs.cf_status_20                                                      
# ########################################################################

# cf_status_193_visibility_value_id_idx is a duplicate of cf_status_20_visibility_value_id_idx
# Key definitions:
#   KEY `cf_status_193_visibility_value_id_idx` (`visibility_value_id`),
#   KEY `cf_status_20_visibility_value_id_idx` (`visibility_value_id`)
# Column types:
#         `visibility_value_id` smallint(6) default null
# To remove this duplicate index, execute:
ALTER TABLE `bugs`.`cf_status_20` DROP INDEX `cf_status_193_visibility_value_id_idx`;

# ########################################################################
# Summary of indexes                                                     
# ########################################################################

# Size Duplicate Indexes   3
# Total Duplicate Indexes  1
# Total Indexes            5



Clustered index example:
mysql> SHOW CREATE TABLE duplicates\G
*************************** 1. row ***************************
       Table: duplicates
Create Table: CREATE TABLE `duplicates` (
  `dupe_of` mediumint(9) NOT NULL,
  `dupe` mediumint(9) NOT NULL,
  PRIMARY KEY (`dupe`),
  KEY `duplicates_dupe_of_idx` (`dupe_of`,`dupe`),
  CONSTRAINT `fk_duplicates_dupe_bugs_bug_id` FOREIGN KEY (`dupe`) REFERENCES `bugs` (`bug_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_duplicates_dupe_of_bugs_bug_id` FOREIGN KEY (`dupe_of`) REFERENCES `bugs` (`bug_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 PACK_KEYS=1
1 row in set (0.00 sec)



# mysqlindexcheck  --server=root:PASSWORD@localhost:/var/lib/mysql/mysql.sock bugs.duplicates -d
# Source on localhost: ... connected.
# The following index for table bugs.duplicates contains the clustered index and might be redundant:
#
CREATE INDEX `duplicates_dupe_of_idx` ON `bugs`.`duplicates` (`dupe_of`, `dupe`) USING BTREE
#
# DROP/ADD statement:
#
ALTER TABLE `bugs`.`duplicates` DROP INDEX `duplicates_dupe_of_idx`, ADD INDEX `duplicates_dupe_of_idx` (dupe_of);
#



No PRIMARY key, but the clustered index might be in the UNIQUE KEY
SHOW CREATE TABLE bugs.bug_cf_fixed_in\G"
*************************** 1. row ***************************
       Table: bug_cf_fixed_in
Create Table: CREATE TABLE `bug_cf_fixed_in` (
  `bug_id` mediumint(9) NOT NULL,
  `value` varchar(64) NOT NULL,
  UNIQUE KEY `bug_cf_fixed_in_bug_id_idx` (`bug_id`,`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

# mysqlindexcheck  --server=root:PASSWORD@localhost:/var/lib/mysql/mysql.sock bugs.bug_cf_fixed_in
# Source on localhost: ... connected.
# The following index for table bugs.bug_cf_fixed_in contains the clustered index and might be redundant:
#
CREATE INDEX `bug_cf_fixed_in_bug_id_idx` ON `bugs`.`bug_cf_fixed_in` (`bug_id`, `value`) USING BTREE
<HR>
<strong>Foreign key, but no PRIMARY key</strong>
<code>mysql> show create table bugs.login_failure\G
*************************** 1. row ***************************
       Table: login_failure
Create Table: CREATE TABLE `login_failure` (
  `user_id` mediumint(9) NOT NULL,
  `login_time` datetime NOT NULL,
  `ip_addr` varchar(40) NOT NULL,
  KEY `login_failure_user_id_idx` (`user_id`),
  CONSTRAINT `fk_login_failure_user_id_profiles_userid` FOREIGN KEY (`user_id`) REFERENCES `profiles` (`userid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

# mysqlindexcheck  --server=root:PASSWORD@localhost:/var/lib/mysql/mysql.sock bugs.login_failure
# Source on localhost: ... connected.
# The following index for table bugs.login_failure contains the clustered index and might be redundant:
#
CREATE INDEX `login_failure_user_id_idx` ON `bugs`.`login_failure` (`user_id`) USING BTREE



Worst index statistics
The higher the cardinality, the "better" the index. As always, this is subjective, and depends heavily on your situation, as we explain in the podcast itself.
# mysqlindexcheck  --server=root:PASSWORD@localhost:/var/lib/mysql/mysql.sock bugs.bugs -d --stats --worst 10 --format vertical
# Source on localhost: ... connected.
#
# Showing the top 10 worst performing indexes from bugs.bugs:
#
*************************       1. row *************************
    database: bugs
       table: bugs
        name: bugs_priority_idx
      column: priority
    sequence: 1
num columns: 1
cardinality: 17
   est. rows: 918091
     percent: 0.00
*************************       2. row *************************
    database: bugs
       table: bugs
        name: bugs_op_sys_idx
      column: op_sys
    sequence: 1
num columns: 1
cardinality: 17
   est. rows: 918091
     percent: 0.00
*************************       3. row *************************
    database: bugs
       table: bugs
        name: bugs_votes_idx
      column: votes
    sequence: 1
num columns: 1
cardinality: 17
   est. rows: 918091
     percent: 0.00
*************************       4. row *************************
    database: bugs
       table: bugs
        name: bugs_bug_severity_idx
      column: bug_severity
    sequence: 1
num columns: 1
cardinality: 17
   est. rows: 918091
     percent: 0.00
*************************       5. row *************************
    database: bugs
       table: bugs
        name: bugs_resolution_idx
      column: resolution
    sequence: 1
num columns: 1
cardinality: 17
   est. rows: 918091
     percent: 0.00
*************************       6. row *************************
    database: bugs
       table: bugs
        name: bugs_bug_status_idx
      column: bug_status
    sequence: 1
num columns: 1
cardinality: 17
   est. rows: 918091
     percent: 0.00
*************************       7. row *************************
    database: bugs
       table: bugs
        name: bugs_version_idx
      column: version
    sequence: 1
num columns: 1
cardinality: 269
   est. rows: 918091
     percent: 0.03
*************************       8. row *************************
    database: bugs
       table: bugs
        name: bugs_product_id_idx
      column: product_id
    sequence: 1
num columns: 1
cardinality: 339
   est. rows: 918091
     percent: 0.04
*************************       9. row *************************
    database: bugs
       table: bugs
        name: bugs_target_milestone_idx
      column: target_milestone
    sequence: 1
num columns: 1
cardinality: 691
   est. rows: 918091
     percent: 0.08
*************************      10. row *************************
    database: bugs
       table: bugs
        name: bugs_qa_contact_idx
      column: qa_contact
    sequence: 1
num columns: 1
cardinality: 1611
   est. rows: 918091
     percent: 0.18
10 rows.



Bug 70339 for when mysqlindexcheck does not display best/worst reports, when there is not enough data in the table.



mysqlmetagrep
Find all the metadata objects that have "mail" in them:
[~]# mysqlmetagrep --server=root:PASSWORD@localhost:/var/lib/mysql/mysql.sock --object-types=table,column --pattern %mail% 
+--------------------------------------------------+--------------+-------------------+-----------+-------------+---------------------+
| Connection                                       | Object Type  | Object Name       | Database  | Field Type  | Matches             |
+--------------------------------------------------+--------------+-------------------+-----------+-------------+---------------------+
| root:*@localhost:3306:/var/lib/mysql/mysql.sock  | TABLE        | email_bug_ignore  | bugs      | TABLE       | email_bug_ignore    |
| root:*@localhost:3306:/var/lib/mysql/mysql.sock  | TABLE        | email_setting     | bugs      | TABLE       | email_setting       |
| root:*@localhost:3306:/var/lib/mysql/mysql.sock  | TABLE        | fielddefs         | bugs      | COLUMN      | mailhead            |
| root:*@localhost:3306:/var/lib/mysql/mysql.sock  | TABLE        | groups            | bugs      | COLUMN      | secure_mail         |
| root:*@localhost:3306:/var/lib/mysql/mysql.sock  | TABLE        | profiles          | bugs      | COLUMN      | disable_mail        |
| root:*@localhost:3306:/var/lib/mysql/mysql.sock  | TABLE        | tell_us_more      | bugs      | COLUMN      | mail                |
| root:*@localhost:3306:/var/lib/mysql/mysql.sock  | TABLE        | whine_events      | bugs      | COLUMN      | mailifnobugs        |
| root:*@localhost:3306:/var/lib/mysql/mysql.sock  | TABLE        | whine_queries     | bugs      | COLUMN      | onemailperbug       |
| root:*@localhost:3306:/var/lib/mysql/mysql.sock  | TABLE        | whine_schedules   | bugs      | COLUMN      | mailto,mailto_type  |
+--------------------------------------------------+--------------+-------------------+-----------+-------------+---------------------+

Get the INFORMATION_SCHEMA query, but do not execute it:

[~]# mysqlmetagrep --sql --object-types=table,column --pattern %mail% 
SELECT
  `Object Type`, `Object Name`, `Database`,
  `Field Type`, GROUP_CONCAT(`Field`) AS `Matches`
FROM (
  SELECT
    'TABLE' AS `Object Type`,
    table_name AS `Object Name`,
    table_schema AS `Database`,
    'TABLE' AS `Field Type`,
    table_name AS `Field`
  FROM
    information_schema.tables
  WHERE
    table_name LIKE '%mail%'
UNION
  SELECT
    'TABLE' AS `Object Type`,
    table_name AS `Object Name`,
    table_schema AS `Database`,
    'COLUMN' AS `Field Type`,
    column_name AS `Field`
  FROM
    information_schema.columns
  WHERE
    column_name LIKE '%mail%'
) AS all_results
  GROUP BY `Object Type`, `Database`, `Object Name`, `Field Type`



mysqlprocgrep


Ear Candy
Todd Farmer blogs about finding which accounts fail to properly close connections using the performance schema

SELECT
    ess.USER,
    ess.HOST,
    (a.TOTAL_CONNECTIONS - a.CURRENT_CONNECTIONS) - ess.COUNT_STAR not_closed,
    ((a.TOTAL_CONNECTIONS - a.CURRENT_CONNECTIONS) - ess.COUNT_STAR) * 100 /
       (a.TOTAL_CONNECTIONS - a.CURRENT_CONNECTIONS) pct_not_closed
FROM
    performance_schema.events_statements_summary_by_account_by_event_name ess
        JOIN
    performance_schema.accounts a ON (ess.USER = a.USER AND ess.HOST = a.HOST)
WHERE
    ess.EVENT_NAME = 'statement/com/Quit'
        AND (a.TOTAL_CONNECTIONS - a.CURRENT_CONNECTIONS) > ess.COUNT_STAR;

At the Movies
This week in At the Movies, we present John Hugg of VoltDB talking about determinism and databases, from the May 2013 Boston MySQL User Group meeting.

Feedback
Facebook group
Google+ page
e-mail: podcast at technocation.org
voicemail using phone/Skype: +1-617-674-2369
twitter: @oursqlcast
or Tweet about @oursqlcast