Wednesday, November 9, 2016

PyMyTools: Simple diagnostic tools for Amazon Aurora and MySQL

This post introduces PyMyTools v0.1: my first version of a simple diagnostic toolkit for automating boring DBA tasks. Read on for tool descriptions and demonstrations!

Introduction

No lengthy introductions this time:
  • I wrote these tools to make my own everyday tasks less time consuming. I hope you too will find them useful.
  • Tools can be downloaded from GitHub and are provided under the MIT License.
  • Your feedback is very welcome (use GitHub Issues).

Tools: general description

The tools are designed to automate some of the simple DBA tasks:
  • Monitor the values of server status variables.
  • Report on basic status variables (buffer/cache usage, uptime, query traffic).
  • Dump textual outputs from diagnostic commands.
  • Describe the topology and replication status of Amazon Aurora clusters.
  • Explain and profile queries.
  • Report and advise on table and index design.
All tools can be used against local (Unix socket) and remote (TCP) servers. They don't require elevated privileges and therefore can be used against managed MySQL servers such as Amazon RDS.

I won't be spending too much of your time describing each tool, let's just paste some outputs so you can see for yourself if that's is something you can use!

Note that PyMyTools enjoy additional features when used against Amazon Aurora. See GitHub site for more details.

pmt_instance_report

The tool reports on basic server status variables and can provide the general idea of server's past and current utilization.

Tool syntax & help output:
$ ./pmt_instance_report -h
usage: pmt_instance_report [-h] (--host HOST | --socket SOCKET) [--port PORT]
                           --user USER [--password PASSWORD] [--run-at RUN_AT]
                           [--sample-length SAMPLE_LENGTH] [--version]

Summarize server status variables and sample engine metrics

optional arguments:
  -h, --help            show this help message and exit
  --host HOST           Host address
  --socket SOCKET       Socket
  --port PORT           Port (default: 3306)
  --user USER           User
  --password PASSWORD   Password (default: prompt)
  --run-at RUN_AT       Run at the specified time (UTC), format: YYYY-MM-
                        DDTHH:MM:SS (example: 2016-10-31T12:25:00)
  --sample-length SAMPLE_LENGTH
                        Sample length (seconds); default: 5
  --version             Show version number and exit
Example output:
$ ./pmt_instance_report --host HOST --port PORT --password PASSWORD --user USER

+----------------------------------------------------------------------------------------------------------------------+
| pmt_instance_report invoked at 2016-11-08 06:39:14 UTC                                                               |
+----------------------------------------------------------------------------------------------------------------------+

       Instance name: pytest
      Engine version: 1.8.1
              Uptime: 314979 seconds (3.0 days 15.0 hours 29.0 minutes 39 seconds)

#
##
### Server statistics
----------------------

 Lock manager memory: 608.469 MB
         Query cache: Enabled, 424.0 MB free in 1 block(s); hit ratio 0%; 1 queries in cache, 693221 not cached, 0 evicted
         Buffer pool: 6.0 / 7.0 GB free; hit ratio 100.0 %; 0 disk reads/s; 456300 pages free, 32268 pages used

 Connection activity: 0.0 connections/s (283 total, 207 aborted) , 6.0 user queries/s, 6.0 total statements/s
      Query activity: 2.0 selects/s (0.3 ms avg), 0.0 inserts/s (0.2 ms avg), 0.0 updates/s (0 ms avg), 0.0 deletes/s (0 ms avg)

#
##
### Current sessions summary by time @ state (excluding internal RDS accounts)
-------------------------------------------------------------------------------

+--------------+------+-------------+---------------+---------------+---------------+-----------------+--------------+
| Session state| Total| 0-10 seconds| 10-100 seconds| 100-1K seconds| 1K-10K seconds| 10K-100K seconds| 100K+ seconds|
+--------------+------+-------------+---------------+---------------+---------------+-----------------+--------------+
|         Query|     1|            1|              0|              0|              0|                0|             0|
+--------------+------+-------------+---------------+---------------+---------------+-----------------+--------------+

#
##
### Purge & active transactions
--------------------------------

Trx id counter 5924364
Purge done for trx's n:o < 5781851 undo n:o < 0 state: running but idle
History list length 195

(no active transactions)

#
##
### Current workload (per second averages, 5 second sample)
------------------------------------------------------------

                    Questions: 6.2
                   Com_select: 2.0
                   Com_insert: 0.4
                   Com_delete: 0.0
                   Com_update: 0.0
           Created_tmp_tables: 1.2
      Created_tmp_disk_tables: 0.0
         Innodb_row_lock_time: 0.0 ms
   AuroraDb_ddl_stmt_duration: 0.0 ms
AuroraDb_select_stmt_duration: 524.4 ms
AuroraDb_insert_stmt_duration: 91.2 ms
AuroraDb_update_stmt_duration: 0.0 ms
AuroraDb_delete_stmt_duration: 0.0 ms

>>> Script execution finished at 2016-11-08 06:39:20 UTC

pmt_cluster_report

This tool describes the topology of an Amazon Aurora cluster and prints the status summary for each cluster node (similar to pmt_instance_report).

Tool syntax & help output:
$ ./pmt_cluster_report -h
usage: pmt_cluster_report [-h] (--host HOST | --socket SOCKET) [--port PORT]
                          --user USER [--password PASSWORD] [--run-at RUN_AT]
                          [--sample-length SAMPLE_LENGTH] [--version]

Explain Aurora cluster topology, summarize status variables and sample engine
metrics for each cluster node

optional arguments:
  -h, --help            show this help message and exit
  --host HOST           Cluster DNS endpoint
  --socket SOCKET       Socket
  --port PORT           Port (default: 3306)
  --user USER           User
  --password PASSWORD   Password (default: prompt)
  --run-at RUN_AT       Run at the specified time (UTC), format: YYYY-MM-
                        DDTHH:MM:SS (example: 2016-10-31T12:25:00)
  --sample-length SAMPLE_LENGTH
                        Workload sample length (seconds); default: do not
                        sample
  --version             Show version number and exit
Example output for an Aurora cluster with 3 nodes:
$ ./pmt_cluster_report --host CLUSTER_ENDPOINT --port PORT --password PASSWORD --user USER

+----------------------------------------------------------------------------------------------------------------------+
| pmt_cluster_report invoked at 2016-11-06 04:45:48 UTC                                                                |
+----------------------------------------------------------------------------------------------------------------------+


#
##
### Cluster topology
---------------------

Detected 3 nodes:

+- "pytest" (Writer)
|
+---- "pytest-node2": 20.4 ms replication lag, oldest read view 13 trx behind master
+---- "pytest-node3": 19.5 ms replication lag, oldest read view 29465 trx behind master

+----------------------------------------------------------------------------------------------------------------------+
| Details for node "pytest"                                                                                            |
+----------------------------------------------------------------------------------------------------------------------+

       Instance name: pytest
      Engine version: 1.8.1
              Uptime: 135373 seconds (1 days 13 hours 36 minutes 13 seconds)

#
##
### Server statistics
----------------------

 Lock manager memory: 608.469 MB
         Query cache: Enabled, 424.0 MB free in 1 block(s); hit ratio 0%; 0 queries in cache, 298295 not cached, 0 evicted
         Buffer pool: 7.0 / 7.0 GB free; hit ratio 100.0 %; 0 disk reads/s; 463476 pages free, 25092 pages used

 Connection activity: 0.0 connections/s (174 total, 151 aborted) , 6.0 user queries/s, 6.0 total statements/s
      Query activity: 2.0 selects/s (0.3 ms avg), 0.0 inserts/s (0.2 ms avg), 0.0 updates/s (0 ms avg), 0.0 deletes/s (0 ms avg)

#
##
### Current sessions summary by time @ state (excluding rdsadmin, rdsrepladmin)
--------------------------------------------------------------------------------

+--------------+------+-------------+---------------+---------------+---------------+-----------------+--------------+
| Session state| Total| 0-10 seconds| 10-100 seconds| 100-1K seconds| 1K-10K seconds| 10K-100K seconds| 100K+ seconds|
+--------------+------+-------------+---------------+---------------+---------------+-----------------+--------------+
|         Query|     1|            1|              0|              0|              0|                0|             0|
|         Sleep|     2|            1|              0|              1|              0|                0|             0|
+--------------+------+-------------+---------------+---------------+---------------+-----------------+--------------+

#
##
### Purge & active transactions
--------------------------------

Trx id counter 5383154
Purge done for trx's n:o < 5204725 undo n:o < 0 state: running but idle
History list length 9858

(no active transaction)


+----------------------------------------------------------------------------------------------------------------------+
| Details for node "pytest-node2"                                                                                      |
+----------------------------------------------------------------------------------------------------------------------+

       Instance name: pytest-node2
      Engine version: 1.8.1
              Uptime: 17742 seconds (4 hours 55 minutes 42 seconds)

#
##
### Server statistics
----------------------

 Lock manager memory: 608.469 MB
         Query cache: Enabled, 424.0 MB free in 1 block(s); hit ratio 0%; 0 queries in cache, 29442 not cached, 0 evicted
         Buffer pool: 7.0 / 7.0 GB free; hit ratio 100.0 %; 0 disk reads/s; 488531 pages free, 37 pages used

 Connection activity: 0.0 connections/s (22 total, 1 aborted) , 3.0 user queries/s, 3.0 total statements/s
      Query activity: 1.0 selects/s (0.4 ms avg), 0.0 inserts/s (0 ms avg), 0.0 updates/s (0 ms avg), 0.0 deletes/s (0 ms avg)

#
##
### Current sessions summary by time @ state (excluding rdsadmin, rdsrepladmin)
--------------------------------------------------------------------------------

+--------------+------+-------------+---------------+---------------+---------------+-----------------+--------------+
| Session state| Total| 0-10 seconds| 10-100 seconds| 100-1K seconds| 1K-10K seconds| 10K-100K seconds| 100K+ seconds|
+--------------+------+-------------+---------------+---------------+---------------+-----------------+--------------+
|         Query|     1|            1|              0|              0|              0|                0|             0|
+--------------+------+-------------+---------------+---------------+---------------+-----------------+--------------+

#
##
### Purge & active transactions
--------------------------------

Oldest read view 13 trx behind master

(no active transaction)


+----------------------------------------------------------------------------------------------------------------------+
| Details for node "pytest-node3"                                                                                      |
+----------------------------------------------------------------------------------------------------------------------+

       Instance name: pytest-node3
      Engine version: 1.8.1
              Uptime: 17738 seconds (4 hours 55 minutes 38 seconds)

#
##
### Server statistics
----------------------

 Lock manager memory: 608.469 MB
         Query cache: Enabled, 424.0 MB free in 1 block(s); hit ratio 2944001.0%; 1 queries in cache, 29439 not cached, 0 evicted
         Buffer pool: 7.0 / 7.0 GB free; hit ratio 100.0 %; 0 disk reads/s; 488449 pages free, 119 pages used

 Connection activity: 0.0 connections/s (22 total, 1 aborted) , 3.0 user queries/s, 3.0 total statements/s
      Query activity: 1.0 selects/s (0.4 ms avg), 0.0 inserts/s (0 ms avg), 0.0 updates/s (0 ms avg), 0.0 deletes/s (0 ms avg)

#
##
### Current sessions summary by time @ state (excluding rdsadmin, rdsrepladmin)
--------------------------------------------------------------------------------

+--------------+------+-------------+---------------+---------------+---------------+-----------------+--------------+
| Session state| Total| 0-10 seconds| 10-100 seconds| 100-1K seconds| 1K-10K seconds| 10K-100K seconds| 100K+ seconds|
+--------------+------+-------------+---------------+---------------+---------------+-----------------+--------------+
|         Query|     1|            1|              0|              0|              0|                0|             0|
|         Sleep|     1|            0|              0|              0|              1|                0|             0|
+--------------+------+-------------+---------------+---------------+---------------+-----------------+--------------+

#
##
### Purge & active transactions
--------------------------------

Oldest read view 29465 trx behind master

+-------+-----------+--------+--------------------+--------+------------+--------------+------------+--------------+
| TRX ID| Session ID|   State|             Started|     Age| Tables used| Tables locked| Rows locked| Rows modified|
+-------+-----------+--------+--------------------+--------+------------+--------------+------------+--------------+
|5379776|         12| RUNNING| 2016-11-06 02:56:45| 1:49:04|           0|             0|           0|             0|
+-------+-----------+--------+--------------------+--------+------------+--------------+------------+--------------+

pmt_diag_monitor

This tool dumps outputs from various diagnostic queries in a regular interval. It writes outputs in a "one file per command, per sample" fashion for easy browsing and analysis.

Tool syntax & help output:
$ ./pmt_diag_monitor -h
usage: pmt_diag_monitor [-h] (--host HOST | --socket SOCKET) [--port PORT]
                        --user USER [--password PASSWORD] [--run-at RUN_AT]
                        [--interval INTERVAL] [--count COUNT]
                        [--output-dir OUTPUT_DIR] [--skip-processlist]
                        [--skip-global-status] [--skip-innodb-status]
                        [--skip-innodb-trx] [--skip-innodb-locks]
                        [--skip-innodb-lock-waits] [--version]

Regularly dump output from diagnostic statements

optional arguments:
  -h, --help            show this help message and exit
  --host HOST           Host address
  --socket SOCKET       Socket
  --port PORT           Port (default: 3306)
  --user USER           User
  --password PASSWORD   Password (default: prompt)
  --run-at RUN_AT       Run at the specified time (UTC), format: YYYY-MM-
                        DDTHH:MM:SS (example: 2016-10-31T12:25:00)
  --interval INTERVAL   Dump interval in seconds; default: 60
  --count COUNT         Dump count; default: 15
  --output-dir OUTPUT_DIR
                        Output directory; default ./pmt_diag_monitor_YYYYMMDD-
                        HHMMSS
  --skip-processlist    Skip processlit output; default: False
  --skip-global-status  Skip global status output; default: False
  --skip-innodb-status  Skip innodb engine status output; default: False
  --skip-innodb-trx     Skip information_schema.innodb_trx output; default:
                        False
  --skip-innodb-locks   Skip information_schema.innodb_locks output; default:
                        False
  --skip-innodb-lock-waits
                        Skip a join query on
                        information_schema.innodb_lock_waits and
                        information_schema.innodb_trx; default: False
  --version             Show version number and exit
Example output:
$ ./pmt_diag_monitor --host HOST --port PORT --password PASSWORD --user USER --interval 1 --count 5

+----------------------------------------------------------------------------------------------------------------------+
| pmt_diag_monitor invoked at 2016-11-08 23:02:12 UTC; dump count = 5, interval = 1 seconds                            |
+----------------------------------------------------------------------------------------------------------------------+

--- This sample: 1 of 5 every 1 s
>>> Writing "processlist" to "./pmt_diag_monitor_20161108-230212/processlist_1478646132.txt"
>>> Writing "global_status" to "./pmt_diag_monitor_20161108-230212/global_status_1478646132.txt"
>>> Writing "innodb_status" to "./pmt_diag_monitor_20161108-230212/innodb_status_1478646132.txt"
>>> Writing "innodb_trx" to "./pmt_diag_monitor_20161108-230212/innodb_trx_1478646132.txt"
>>> Writing "innodb_locks" to "./pmt_diag_monitor_20161108-230212/innodb_locks_1478646132.txt"
>>> Writing "innodb_lock_waits" to "./pmt_diag_monitor_20161108-230212/innodb_lock_waits_1478646132.txt"
--- Iteration took 12 ms, now sleeping for 0.988 s
--- This sample: 2 of 5 every 1 s
>>> Writing "processlist" to "./pmt_diag_monitor_20161108-230212/processlist_1478646133.txt"
>>> Writing "global_status" to "./pmt_diag_monitor_20161108-230212/global_status_1478646133.txt"
>>> Writing "innodb_status" to "./pmt_diag_monitor_20161108-230212/innodb_status_1478646133.txt"
>>> Writing "innodb_trx" to "./pmt_diag_monitor_20161108-230212/innodb_trx_1478646133.txt"
>>> Writing "innodb_locks" to "./pmt_diag_monitor_20161108-230212/innodb_locks_1478646133.txt"
>>> Writing "innodb_lock_waits" to "./pmt_diag_monitor_20161108-230212/innodb_lock_waits_1478646133.txt"
--- Iteration took 11 ms, now sleeping for 0.989 s
--- This sample: 3 of 5 every 1 s
>>> Writing "processlist" to "./pmt_diag_monitor_20161108-230212/processlist_1478646134.txt"
>>> Writing "global_status" to "./pmt_diag_monitor_20161108-230212/global_status_1478646134.txt"
>>> Writing "innodb_status" to "./pmt_diag_monitor_20161108-230212/innodb_status_1478646134.txt"
>>> Writing "innodb_trx" to "./pmt_diag_monitor_20161108-230212/innodb_trx_1478646134.txt"
>>> Writing "innodb_locks" to "./pmt_diag_monitor_20161108-230212/innodb_locks_1478646134.txt"
>>> Writing "innodb_lock_waits" to "./pmt_diag_monitor_20161108-230212/innodb_lock_waits_1478646134.txt"
--- Iteration took 18 ms, now sleeping for 0.982 s
--- This sample: 4 of 5 every 1 s
>>> Writing "processlist" to "./pmt_diag_monitor_20161108-230212/processlist_1478646135.txt"
>>> Writing "global_status" to "./pmt_diag_monitor_20161108-230212/global_status_1478646135.txt"
>>> Writing "innodb_status" to "./pmt_diag_monitor_20161108-230212/innodb_status_1478646135.txt"
>>> Writing "innodb_trx" to "./pmt_diag_monitor_20161108-230212/innodb_trx_1478646135.txt"
>>> Writing "innodb_locks" to "./pmt_diag_monitor_20161108-230212/innodb_locks_1478646135.txt"
>>> Writing "innodb_lock_waits" to "./pmt_diag_monitor_20161108-230212/innodb_lock_waits_1478646135.txt"
--- Iteration took 30 ms, now sleeping for 0.97 s
--- This sample: 5 of 5 every 1 s
>>> Writing "processlist" to "./pmt_diag_monitor_20161108-230212/processlist_1478646136.txt"
>>> Writing "global_status" to "./pmt_diag_monitor_20161108-230212/global_status_1478646136.txt"
>>> Writing "innodb_status" to "./pmt_diag_monitor_20161108-230212/innodb_status_1478646136.txt"
>>> Writing "innodb_trx" to "./pmt_diag_monitor_20161108-230212/innodb_trx_1478646136.txt"
>>> Writing "innodb_locks" to "./pmt_diag_monitor_20161108-230212/innodb_locks_1478646136.txt"
>>> Writing "innodb_lock_waits" to "./pmt_diag_monitor_20161108-230212/innodb_lock_waits_1478646136.txt"
---
>>> Script execution finished at 2016-11-08 23:02:16 UTC

pmt_statvar_monitor

The tool print per-second averages for server status variables in tabular (human readable) format or CSV format for easy parsing.

Tool syntax & help output:
$ ./pmt_statvar_monitor -h
usage: pmt_statvar_monitor [-h] (--host HOST | --socket SOCKET) [--port PORT]
                           --user USER [--password PASSWORD] [--run-at RUN_AT]
                           [--interval INTERVAL] [--count COUNT]
                           [--header-repeat HEADER_REPEAT] [--csv] [--version]
                           [variable [variable ...]]

Print per-second averages of server status variables

positional arguments:
  variable              Names of status variables to monitor; default:
                        Questions Com_select Com_insert Com_delete Com_update
                        Created_tmp_tables Created_tmp_disk_tables
                        Innodb_row_lock_time

optional arguments:
  -h, --help            show this help message and exit
  --host HOST           Host address
  --socket SOCKET       Socket
  --port PORT           Port (default: 3306)
  --user USER           User
  --password PASSWORD   Password (default: prompt)
  --run-at RUN_AT       Run at the specified time (UTC), format: YYYY-MM-
                        DDTHH:MM:SS (example: 2016-10-31T12:25:00)
  --interval INTERVAL   Report interval in seconds; default: 1
  --count COUNT         Report count; default: 60
  --header-repeat HEADER_REPEAT
                        Header repeat interval; default: 20
  --csv                 CSV output
  --version             Show version number and exit
Example output with the default variable set:
$ ./pmt_statvar_monitor --host HOST --port PORT --password PASSWORD --user USER --interval 1 --count 10
*** Per-second deltas since restart or FLUSH STATUS
--------------------------------------------------------------------------------------------------------------------------------------------------
      Timestamp UTC| Questions| Com_select| Com_insert| Com_delete| Com_update| Created_tmp_tables| Created_tmp_disk_tables| Innodb_row_lock_time|
2016-11-08 23:08:06|     6.38 |       2.2 |       0.5 |       0.0 |       0.0 |              1.17 |                  0.005 |               0.0 ms|

*** Per-second deltas every 1 second(s)
--------------------------------------------------------------------------------------------------------------------------------------------------
      Timestamp UTC| Questions| Com_select| Com_insert| Com_delete| Com_update| Created_tmp_tables| Created_tmp_disk_tables| Innodb_row_lock_time|
2016-11-08 23:08:07|     10.0 |       3.0 |       1.0 |       0.0 |       0.0 |               2.0 |                    0.0 |               0.0 ms|
2016-11-08 23:08:08|      4.0 |       1.0 |       0.0 |       0.0 |       0.0 |               2.0 |                    0.0 |               0.0 ms|
2016-11-08 23:08:09|     10.0 |       3.0 |       1.0 |       0.0 |       0.0 |               2.0 |                    0.0 |               0.0 ms|
2016-11-08 23:08:10|      4.0 |       1.0 |       0.0 |       0.0 |       0.0 |               2.0 |                    0.0 |               0.0 ms|
2016-11-08 23:08:11|     10.0 |       3.0 |       1.0 |       0.0 |       0.0 |               2.0 |                    0.0 |               0.0 ms|
2016-11-08 23:08:12|      4.0 |       1.0 |       0.0 |       0.0 |       0.0 |               2.0 |                    0.0 |               0.0 ms|
2016-11-08 23:08:13|     10.0 |       3.0 |       1.0 |       0.0 |       0.0 |               2.0 |                    0.0 |               0.0 ms|
2016-11-08 23:08:14|      4.0 |       1.0 |       0.0 |       0.0 |       0.0 |               2.0 |                    0.0 |               0.0 ms|
2016-11-08 23:08:15|     13.0 |       4.0 |       1.0 |       0.0 |       0.0 |               3.0 |                    0.0 |               0.0 ms|
>>> Script execution finished at 2016-11-08 23:08:16 UTC
Example output in CSV format:
$ ./pmt_statvar_monitor --host HOST --port PORT --password PASSWORD --user USER --interval 1 --count 10 --csv
Timestamp UTC,Questions,Com_select,Com_insert,Com_delete,Com_update,Created_tmp_tables,Created_tmp_disk_tables,Innodb_row_lock_time
2016-11-08 23:09:22,4.0,1.0,0.0,0.0,0.0,2.0,0.0,0.0
2016-11-08 23:09:23,10.0,3.0,1.0,0.0,0.0,2.0,0.0,0.0
2016-11-08 23:09:24,4.0,1.0,0.0,0.0,0.0,2.0,0.0,0.0
2016-11-08 23:09:25,17.0,8.0,1.0,0.0,0.0,7.0,0.0,0.0
2016-11-08 23:09:26,4.0,1.0,0.0,0.0,0.0,2.0,0.0,0.0
2016-11-08 23:09:27,10.0,3.0,1.0,0.0,0.0,2.0,0.0,0.0
2016-11-08 23:09:28,4.0,1.0,0.0,0.0,0.0,2.0,0.0,0.0
2016-11-08 23:09:29,10.0,3.0,1.0,0.0,0.0,2.0,0.0,0.0
2016-11-08 23:09:30,7.0,2.0,0.0,0.0,0.0,3.0,0.0,0.0
>>> Script execution finished at 2016-11-08 23:09:31 UTC

pmt_query_report

This tool explains and profiles a SELECT query.

Tool syntax & help output:
$ ./pmt_query_report -h
usage: pmt_query_report [-h] (--host HOST | --socket SOCKET) [--port PORT]
                        --user USER [--password PASSWORD] [--run-at RUN_AT]
                        --input-file INPUT_FILE [--trace] [--vertical]
                        [--profile] [--version]

Print diagnostic information for a SELECT query

optional arguments:
  -h, --help            show this help message and exit
  --host HOST           Host address
  --socket SOCKET       Socket
  --port PORT           Port (default: 3306)
  --user USER           User
  --password PASSWORD   Password (default: prompt)
  --run-at RUN_AT       Run at the specified time (UTC), format: YYYY-MM-
                        DDTHH:MM:SS (example: 2016-10-31T12:25:00)
  --input-file INPUT_FILE
                        Input file containing the query
  --trace               Show optimizer trace (EXPLAIN JSON)
  --vertical            Use vertical output instead of tabular for EXPLAIN
  --profile             Execute the query twice with profiling
  --version             Show version number and exit
Example output for a "select 1" query:
$ ./pmt_query_report --host HOST --port PORT --password PASSWORD --user MANAGER --input-file query.txt --trace --profile

+----------------------------------------------------------------------------------------------------------------------+
| pmt_query_report invoked at 2016-11-08 23:14:31 UTC; input = query.txt, trace = True, profile = True                 |
+----------------------------------------------------------------------------------------------------------------------+


#
##
### EXPLAIN output
-------------------

+---+------------+------+-----------+-----+--------------+----+--------+----+-----+---------------+
| id| select_type| table| partitions| type| possible_keys| key| key_len| ref| rows|          Extra|
+---+------------+------+-----------+-----+--------------+----+--------+----+-----+---------------+
|  1|      SIMPLE|      |           |     |              |    |        |    |     | No tables used|
+---+------------+------+-----------+-----+--------------+----+--------+----+-----+---------------+

#
##
### Optimizer trace
--------------------

**************************** 1. row ****************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "table": {
      "message": "No tables used"
    }
  }
}

#
##
### Query profile for 2 consecutive executions
-----------------------------------------------

+---------------------+---------+
|               Status| Duration|
+---------------------+---------+
|             starting| 0.000011|
| checking permissions| 0.000005|
|       Opening tables| 0.000004|
|                 init| 0.000006|
|           optimizing| 0.000005|
|            executing| 0.000006|
|                  end| 0.000003|
|            query end| 0.000003|
|       closing tables| 0.000004|
|        freeing items| 0.000010|
|          cleaning up| 0.000003|
+---------------------+---------+
+---------------------+---------+
|               Status| Duration|
+---------------------+---------+
|             starting| 0.000011|
| checking permissions| 0.000004|
|       Opening tables| 0.000004|
|                 init| 0.000005|
|           optimizing| 0.000004|
|            executing| 0.000005|
|                  end| 0.000003|
|            query end| 0.000003|
|       closing tables| 0.000003|
|        freeing items| 0.000010|
|          cleaning up| 0.000004|
+---------------------+---------+

#
##
### Tested query
-----------------

select sql_no_cache 1

pmt_table_report

The tool can describe table structure and its basic statistics. It can also report index cardinality with default and custom sample size as well as identify basic indexing problems e.g. redundant indexes or unbound indexes on textual columns. If the table name is provided but schema name is not, the tool will attempt to locate the table in all schemas. For performance reasons, it is always recommended to provide both the table name and schema name.

Tool syntax & help output:
$ ./pmt_table_report -h
usage: pmt_table_report [-h] (--host HOST | --socket SOCKET) [--port PORT]
                        --user USER [--password PASSWORD] [--run-at RUN_AT]
                        [--analyze] [--find] [--analyze-extended]
                        [--extended-sample-size EXTENDED_SAMPLE_SIZE]
                        [--version]
                        table_name [schema_name]

Find tables and print details about table structure, indexes and statistics;
find index design issues

positional arguments:
  table_name            Table name
  schema_name           Table schema; default: try to find table automatically

optional arguments:
  -h, --help            show this help message and exit
  --host HOST           Host address
  --socket SOCKET       Socket
  --port PORT           Port (default: 3306)
  --user USER           User
  --password PASSWORD   Password (default: prompt)
  --run-at RUN_AT       Run at the specified time (UTC), format: YYYY-MM-
                        DDTHH:MM:SS (example: 2016-10-31T12:25:00)
  --analyze             Analyze the table and report index cardinality
                        changes; default: False
  --find                Only find the table, do not generate report; default:
                        False
  --analyze-extended    Analyze the table using 2 sample sizes (server/table
                        default and sample size specified in --extended-
                        sample-size); report index cardinality changes
                        (implies --analyze, default: False); requires ALTER
                        privilege on the table
  --extended-sample-size EXTENDED_SAMPLE_SIZE
                        Page count for stats sampling with --analyze-extended;
                        default: 256
  --version             Show version number and exit
Example output:
$ ./pmt_table_report --host HOST --port PORT --password PASSWORD --user USER --analyze-extended --extended-sample-size 2048 t s1

+----------------------------------------------------------------------------------------------------------------------+
| pmt_table_report invoked at 2016-11-08 23:19:27 UTC                                                                  |
+----------------------------------------------------------------------------------------------------------------------+

>>> Table `t` found in schema `s1`

#
##
### Table structure
--------------------

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `s1` varchar(32) DEFAULT NULL,
  `s2` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `s1` (`s1`),
  KEY `s2` (`s2`(10)),
  KEY `s1_s2` (`s1`,`s2`),
  KEY `s1_id` (`s1`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1310694 DEFAULT CHARSET=latin1

#
##
### Index analysis
-------------------

! Index `s1` may be redundant with `s1_s2`, `s1_id`
! Index `s1` contains textual columns without prefix
! Index `s1_s2` contains textual columns without prefix
! Index `s1_id` contains textual columns without prefix
! Index `s1_s2` contains textual columns without prefix

#
##
### Table size (before ANALYZE)
--------------------------------

Rows: 1042304, 97 bytes on average
Size: 0.094 GiB (data) + 0.214 GiB (index) = 0.308 GiB total (69% index)

#
##
### Index cardinality (before ANALYZE)
---------------------------------------

+-----------+-----------+-------------+------------+------------+
| non_unique| index_name| seq_in_index| column_name| cardinality|
+-----------+-----------+-------------+------------+------------+
|          0|    PRIMARY|            1|          id|     1042304|
|          1|         s1|            1|          s1|     1042304|
|          1|         s2|            1|          s2|     1042304|
|          1|      s1_s2|            1|          s1|     1042304|
|          1|      s1_s2|            2|          s2|     1042304|
|          1|      s1_id|            1|          s1|     1042304|
|          1|      s1_id|            2|          id|     1042304|
+-----------+-----------+-------------+------------+------------+

#
##
### Table size (after ANALYZE)
-------------------------------

Rows: 1042561, 97 bytes on average
Size: 0.094 GiB (data) + 0.214 GiB (index) = 0.308 GiB total (69% index)

#
##
### Index cardinality (after ANALYZE)
--------------------------------------

+-----------+-----------+-------------+------------+------------+
| non_unique| index_name| seq_in_index| column_name| cardinality|
+-----------+-----------+-------------+------------+------------+
|          0|    PRIMARY|            1|          id|     1042561|
|          1|         s1|            1|          s1|     1042561|
|          1|         s2|            1|          s2|     1042561|
|          1|      s1_s2|            1|          s1|     1042561|
|          1|      s1_s2|            2|          s2|     1042561|
|          1|      s1_id|            1|          s1|     1042561|
|          1|      s1_id|            2|          id|     1042561|
+-----------+-----------+-------------+------------+------------+

#
##
### Index statistics (after ANALYZE with 2048-page sample)
-----------------------------------------------------------

>>> Table does not have custom sampling configuration, global setting is 20 pages
+-----------+-----------+-------------+------------+------------+
| non_unique| index_name| seq_in_index| column_name| cardinality|
+-----------+-----------+-------------+------------+------------+
|          0|    PRIMARY|            1|          id|     1042304|
|          1|         s1|            1|          s1|     1042304|
|          1|         s2|            1|          s2|     1042304|
|          1|      s1_s2|            1|          s1|     1042304|
|          1|      s1_s2|            2|          s2|     1042304|
|          1|      s1_id|            1|          s1|     1042304|
|          1|      s1_id|            2|          id|     1042304|
+-----------+-----------+-------------+------------+------------+
>>> Reverting to default sampling configuration
>>> Script execution finished at 2016-11-08 23:19:27 UTC

pmt_size_report

The tool reports the size of schemas and tables. It can report on the entire instance or just the selected schemas. It can automatically analyze tables (i.e. update statistics) prior to generating the report.

Tool syntax & help output:
$ ./pmt_size_report -h
usage: pmt_size_report [-h] (--host HOST | --socket SOCKET) [--port PORT]
                       --user USER [--password PASSWORD] [--run-at RUN_AT]
                       [--sort {alpha,size}] [--limit LIMIT] [--analyze]
                       [--version]
                       [schema [schema ...]]

Print storage consumption in an instance or selected schema(s), except system
schemas

positional arguments:
  schema               Names of schemas for which size should be calculated;
                       default: all schemas

optional arguments:
  -h, --help           show this help message and exit
  --host HOST          Host address
  --socket SOCKET      Socket
  --port PORT          Port (default: 3306)
  --user USER          User
  --password PASSWORD  Password (default: prompt)
  --run-at RUN_AT      Run at the specified time (UTC), format: YYYY-MM-
                       DDTHH:MM:SS (example: 2016-10-31T12:25:00)
  --sort {alpha,size}  Sort order for table lists; default: size
  --limit LIMIT        List at most that many tables per schema; default: 10,
                       implies --sort size
  --analyze            Analyze all tables in scope before calculating their
                       size
  --version            Show version number and exit

WARNING: Statements executed by this program may execute slowly and/or affect
performance of large or busy databases. Use at your own risk.
Example output:
$ ./pmt_size_report --host HOST --port PORT --password PASSWORD --user USER --analyze repro s1

+----------------------------------------------------------------------------------------------------------------------+
| pmt_size_report invoked at 2016-11-15 23:57:36 UTC                                                                   |
+----------------------------------------------------------------------------------------------------------------------+

>>> Selected schemas: `repro`, `s1`

>>> Analyzing table `repro`.`a1`
>>> Analyzing table `repro`.`largetable`
>>> Analyzing table `repro`.`t`
>>> Analyzing table `s1`.`t`
>>> Analyzing table `s1`.`t2`
>>> Analyzing table `s1`.`t3`

#
##
### Schema summary
-------------------

Total size of selected schemas: 2.333 M rows, 0.45 GiB total, 0.18 GiB data, 0.27 GiB index

= denotes Data, # denotes Index

s1    [=======################# ]     1.043 M rows,    0.31 GiB,    0.09 GiB data,    0.21 GiB index
repro [======####               ]      1.29 M rows,    0.14 GiB,    0.08 GiB data,    0.05 GiB index


#
##
### 10 largest tables in schema `repro`
----------------------------------------

largetable [===============######### ]     1.282 M rows,    0.14 GiB,    0.08 GiB data,    0.05 GiB index
a1         [                         ]     0.008 M rows,     0.0 GiB,     0.0 GiB data,     0.0 GiB index
t          [                         ]       0.0 M rows,     0.0 GiB,     0.0 GiB data,     0.0 GiB index

#
##
### 10 largest tables in schema `s1`
-------------------------------------

t          [=======################# ]     1.043 M rows,    0.31 GiB,    0.09 GiB data,    0.21 GiB index
t2         [                         ]       0.0 M rows,     0.0 GiB,     0.0 GiB data,     0.0 GiB index
t3         [                         ]       0.0 M rows,     0.0 GiB,     0.0 GiB data,     0.0 GiB index

>>> Script execution finished at 2016-11-15 23:57:36 UTC

No comments:

Post a Comment