DFW Unix User Group – MySQL Workbench January 8th

MySQL Workbench will be the subject of the January 8th presentation of the DFW Unix User Group. Pizza before the meeting at the IBM Innovation Center at 1177 South Beltline Road, in Coppell, just south of the Airline Drive traffic light, and just west of North Lake.

MySQL Workbench Performance Dashboard

Leave a comment

Filed under community team, DBA Tools, MySQL Workbench, User Group

SYS Schema Second Steps

The SYS Schema is a valuable set of tools for any MySQL DBA and last entry covered in the installation and first, tentative steps. You also should peruse Mark Leith’s presentation from the recent MySQL Central @ Oracle Open World. There is a lot in the SYS Schema and this blog entry is a look what has been so nicely gift wrapped at a cursory level. I hope to explore further and in greater detail but right now is still exploration at a simple level.

Simple Query Information
So what can bee seen from running a simple query like SELECT Name FROM City LIMIT 6?

*************************** 1. row ***************************
db: world
full_scan: *
exec_count: 1
err_count: 0
warn_count: 0
total_latency: 394.55 us
max_latency: 394.55 us
avg_latency: 394.55 us
lock_latency: 132.00 us
rows_sent: 6
rows_sent_avg: 6
rows_examined: 6
rows_examined_avg: 6
rows_affected: 0
rows_affected_avg: 0
tmp_tables: 0
tmp_disk_tables: 0
rows_sorted: 0
sort_merge_passes: 0
digest: 12f76baa0640720ddc461eb65682adc4
first_seen: 2014-12-11 13:43:21
last_seen: 2014-12-11 13:43:21
1 row in set (0.00 sec)

Checking on the table statistics

mysql> select * from schema_table_statistics where table_name='City'\G
*************************** 1. row ***************************
table_schema: world
table_name: City
total_latency: 373.83 us
rows_fetched: 36
fetch_latency: 373.83 us
rows_inserted: 0
insert_latency: 0 ps
rows_updated: 0
update_latency: 0 ps
rows_deleted: 0
delete_latency: 0 ps
io_read_requests: 12
io_read: 80.73 KiB
io_read_latency: 87.32 ms
io_write_requests: 0
io_write: 0 bytes
io_write_latency: 0 ps
io_misc_requests: 11
io_misc_latency: 251.77 us
1 row in set (0.06 sec)

There are ways to dog into what running statements are doing and hopefully we can circle back to that in the future. This is more like exploring what is in the toy box that can be glanced over quickly. And SYS Schema is a big toy box.

Schema Synonyms?
Ever need to call a database by another name? SYS Schema comes with several handy procedues such as this one.
mysql> CALL sys.create_synonym_db('world','world_innodb');
| summary |
| Created 3 views in the world_innodb database |
1 row in set (0.08 sec)

Query OK, 0 rows affected (0.08 sec)

mysql> show databases;
| Database |
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
| world |
| world_innodb |
7 rows in set (0.00 sec)

Very neatly the world and world_nnodb tables point to the same data!

Problematic Queries
It is also easy to spot queries having problems.
mysql> select * from statements_with_errors_or_warnings\G
*************************** 1. row ***************************
query: SELECT `sys` . `format_stateme ... , `sys` . `format_time` ( ...
db: sys
exec_count: 7
errors: 1
error_pct: 14.2857
warnings: 0
warning_pct: 0.0000
first_seen: 2014-12-11 13:42:13
last_seen: 2014-12-11 13:47:36
digest: 220e9caf13a5e5bfe8f73987bc26e443
1 row in set (0.00 sec)


And How Is Your MySQL Memory?
And to find the total memory being used globally by the server.
mysql> select * from memory_global_total;
| total_allocated |
| 500.68 MiB |
1 row in set (0.01 sec)

Leave a comment

Filed under SYS Schema

SYS Schema First Steps

Oracle DBAs have has the luxury of their V$ variables for a long time while we MySQL DBAs pretended we were not envious. With MySQL 5.6 and 5.7 we were gifted with the PERFORMANCE_SCHEMA tables. But there is such a wealth of information in those tables that it is intimidating to plunge in to them.  Thankfully Mark Leith has given us the SYS Schema. The SYS Schema is a collection of views, functions and procedures to help MySQL administrators get insight in to MySQL Database usage.

The first step is to get a copy of the SYS SCHEMA files.
git clone https://github.com/MarkLeith/mysql-sys

Next install the SYS Schema (here for MySQL 5.7)
mysql -u root -p < ./sys_57.sql

Now run MySQL and look at this new schema.
mysql> use sys;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
| Tables_in_sys |
| host_summary |
| host_summary_by_file_io |
| host_summary_by_file_io_type |
| host_summary_by_stages |
| host_summary_by_statement_latency |
| host_summary_by_statement_type |
| innodb_buffer_stats_by_schema |
| innodb_buffer_stats_by_table |
| innodb_lock_waits |
| io_by_thread_by_latency |
| io_global_by_file_by_bytes |
| io_global_by_file_by_latency |
| io_global_by_wait_by_bytes |
| io_global_by_wait_by_latency |
| latest_file_io |
| memory_by_host_by_current_bytes |
| memory_by_thread_by_current_bytes |
| memory_by_user_by_current_bytes |
| memory_global_by_current_allocated |
| memory_global_total |
| processlist |
| ps_check_lost_instrumentation |
| schema_index_statistics |
| schema_object_overview |
| schema_table_statistics |
| schema_table_statistics_with_buffer |
| schema_tables_with_full_table_scans |
| schema_unused_indexes |
| statement_analysis |
| statements_with_errors_or_warnings |
| statements_with_full_table_scans |
| statements_with_runtimes_in_95th_percentile |
| statements_with_sorting |
| statements_with_temp_tables |
| sys_config |
| user_summary |
| user_summary_by_file_io |
| user_summary_by_file_io_type |
| user_summary_by_stages |
| user_summary_by_statement_latency |
| user_summary_by_statement_type |
| version |
| wait_classes_global_by_avg_latency |
| wait_classes_global_by_latency |
| waits_by_host_by_latency |
| waits_by_user_by_latency |
| waits_global_by_latency |
| x$host_summary |
| x$host_summary_by_file_io |
| x$host_summary_by_file_io_type |
| x$host_summary_by_stages |
| x$host_summary_by_statement_latency |
| x$host_summary_by_statement_type |
| x$innodb_buffer_stats_by_schema |
| x$innodb_buffer_stats_by_table |
| x$innodb_lock_waits |
| x$io_by_thread_by_latency |
| x$io_global_by_file_by_bytes |
| x$io_global_by_file_by_latency |
| x$io_global_by_wait_by_bytes |
| x$io_global_by_wait_by_latency |
| x$latest_file_io |
| x$memory_by_host_by_current_bytes |
| x$memory_by_thread_by_current_bytes |
| x$memory_by_user_by_current_bytes |
| x$memory_global_by_current_allocated |
| x$memory_global_total |
| x$processlist |
| x$ps_digest_95th_percentile_by_avg_us |
| x$ps_digest_avg_latency_distribution |
| x$ps_schema_table_statistics_io |
| x$schema_index_statistics |
| x$schema_table_statistics |
| x$schema_table_statistics_with_buffer |
| x$schema_tables_with_full_table_scans |
| x$statement_analysis |
| x$statements_with_errors_or_warnings |
| x$statements_with_full_table_scans |
| x$statements_with_runtimes_in_95th_percentile |
| x$statements_with_sorting |
| x$statements_with_temp_tables |
| x$user_summary |
| x$user_summary_by_file_io |
| x$user_summary_by_file_io_type |
| x$user_summary_by_stages |
| x$user_summary_by_statement_latency |
| x$user_summary_by_statement_type |
| x$wait_classes_global_by_avg_latency |
| x$wait_classes_global_by_latency |
| x$waits_by_host_by_latency |
| x$waits_by_user_by_latency |
| x$waits_global_by_latency |
92 rows in set (0.00 sec)

Note that the views preceded by x$ are designed to be used within tools.

So the first table in the list above is is host_summaryand a quick query reveals the following.
mysql> select * from host_summary\G
*************************** 1. row ***************************
host: localhost
statements: 1328
statement_latency: 10.73 s
statement_avg_latency: 8.08 ms
table_scans: 88
file_ios: 17268
file_io_latency: 6.34 h
current_connections: 3
total_connections: 10
unique_hosts: 2
current_memory: 0 bytes
total_memory_allocated: 0 bytes
1 row in set (0.03 sec)

Now that I have pried open the lid, the next post will show how to dig deeper into the SYS Schema.

1 Comment

Filed under MySQL, Uncategorized

2014 MySQL DBA/Developers Holiday Gifts

MySQL DBAs and Developers are hard to shop for but here are some ideas that will appeal to them.

1. Hamster Wheel Standing Desk http://www.dudeiwantthat.com/gear/office/hamster-wheel-standing-desk.asp

Hamster Wheel Standing Desk
Get your favorite MySQL-er a new desk, some exercise, and you will be giving a gift that provides more than just a place to stash stuff.

For the DBA/Dev on the go, there is also the Refold Portable Cardboard Desk http://www.dudeiwantthat.com/gear/office/refold-portable-cardboard-desk.asp that does not provide as much exercise but could be just the thing for the trip to that oh so crowded Starbucks.

2. “DB Evil Genius” Mug http://www.cafepress.com/mf/10655743/db-evil-genius_mugs?productId=46810134
What goes better on a new desk than a new mug?

3. Sometimes I Self Join postcards http://www.cafepress.com/+query_self_joins_postcards_package_of_8,295044156

4. T-shirts are always popular and the cornerstone of most developers ensembles.

5. The Degree or normality in a database in inversely proportional to that of its DBA t-Shirt

6. Dolphin Casting Kit (MySQL mascot)

Found at a local TJ Max but popular in toy sections of most retailers.

Found at a local TJ Max but popular in toy sections of most retailers.

7. Another fun shirt for those who enjoy facial hair humor

8. A shirt proclaiming the truth from http://www.dictionarytshirts.com!

9. A Keep Clam shirt

10. And finally for the MySQL-ers in your life that is a little edgy go to http://ep.yimg.com/ay/yhst-54334793715728/golden-scorpion-clear-computer-mouse-6.png

Any other ideas? Please add your comments below.

Leave a comment

Filed under Uncategorized

Joomla! World, PHP[World], and Live360

Joomla! and MySQL — Slightly Different Paths to the Same Target will be the subject of my presentation at Joomla! World and it will be the Sunday morning keynote. Then I fly to Washington, D.C. for the first ever PHP[World] where I hold a SQL for PHP Developers workshop and have a session on MySQL 5.7! And then I am off to Oralndo for Live 360 megaconference to show off MySQL to a very large gathering of Windows developers.

Leave a comment

Filed under community team

MySQL Marinate for the Holiday Season

Just a friendly reminder that you can pick up MySQL Marinate whenever you want! You can use the master list at http://www.meetup.com/Virtual-Tech-Self-Study/messages/boards/thread/38423162 for reference – just ignore the dates and work at your own pace!

We found that very few people were taking the dates to heart, so we stopped trying to organize around them. The message boards are still valid, so feel free to ask if you have any questions – we are here to help!

The above was a quick note from Sheeri Cabral about the wonderful MySQL Marinate program that arrived in my email. This is a great way for novices to learn MySQL and for old war horses to find some new insights.

Leave a comment

Filed under Basics, MySQL

MySQL Utilities — The General Operations

The MySQL Utilities are divided into five groups and this blog post covers the second group — General Operations. These include mysqldiskuseage, mysqlfrm, mysqlindexcehck, mysqlmetagrep, mysqlprocgrep, mysqluc, and mysqluserclone.

For checking the disk usage of your data, there is mysqldiskusage. The following example is a on a fresh install of 5.7.5 on Ubuntu.
mysqldiskusage --server=root:hidave@localhost -b -m -i
[sudo] password for dstokes:
# Source on localhost: ... connected.
# Database totals:
| db_name | total |
| mysql | 13,851,103 |
| performance_schema | 721,458 |
| sakila | 31,865,975 |
| sys | 0 |
| world | 1,783,833 |

Total database disk usage = 48,307,605 bytes or 46.07 MB

# Binary logging is turned off on the server.
# InnoDB tablespace information:
| innodb_file | size |
| ib_logfile0 | 50,331,648 |
| ib_logfile1 | 50,331,648 |
| ibdata1 | 79,691,776 |

Total size of InnoDB files = 180,355,072 bytes or 172.00 MB


Checking indexes is simple with mysqlindexcheck.
mysqlindexcheck --server=root:hidave@localhost world
# Source on localhost: ... connected.
# The following index is a duplicate or redundant for table world.CountryLanguage:
CREATE INDEX `CountryCode` ON `world`.`CountryLanguage` (`CountryCode`) USING BTREE
# may be redundant or duplicate of:
ALTER TABLE `world`.`CountryLanguage` ADD PRIMARY KEY (`CountryCode`, `Language`)

Add -i to get a list of all indexes on the tables in the database or –stats to see the 5 worst performing indexes.

Ever need to look for a column name or trigger quickly then you need to check out mysqlmetagrep. For example lets search for a column named CountryCode.

mysqlmetagrep --pattern="CountryCode" --server=root:hidave@localhost
| Connection | Object Type | Object Name | Database | Field Type | Matches |
| root:*@localhost:3306 | TABLE | City | world | COLUMN | CountryCode |
| root:*@localhost:3306 | TABLE | CountryLanguage | world | COLUMN | CountryCode |

And yes it accepts regex!

The ability to search he server process list comes with mysqlpocgrep.
mysqlprocgrep --server=root:hidave@localhost
| Connection | Id | User | Host | Db | Command | Time | State | Info |
| root:*@localhost:3306 | 25 | root | localhost:52475 | None | Query | 0 | executing | SELECT
Id, User, Host, Db, Command, Time, State, Info
| root:*@localhost:3306 | 15 | root | localhost:52307 | None | Sleep | 3 | | None |
| root:*@localhost:3306 | 6 | root | localhost:52031 | world | Sleep | 132 | | None |
| root:*@localhost:3306 | 14 | root | localhost:52306 | None | Sleep | 911 | | None |
| root:*@localhost:3306 | 5 | root | localhost:52030 | world | Sleep | 132 | | None |

Where this utility shines is the ability look for age of a process in the current state (long running or hung process), process running a certain command, process running against a selected database or from a certain user, and possibly kill that process. Heck it will even output the SQL code for a stored procedure that could fed into the Event Manager.

To clone a user from the local or remote server or to get a list off accounts, there is mysqluserclone.

mysqluserclone --source=root:hidave@localhost --list
# Source on localhost: ... connected.
# All Users:
| user | host |
| dstokes | % |
| root | localhost |

And finally there is mysqluc which us the command client for running the MySQL Utilities, usually from MySQL Workbench.

Leave a comment

Filed under Administration, MySQL Utilities, MySQL Workbench

MySQL Utilities — The Database Operations

Version 1.5 of the MySQL Utilities are divided into five groups — Database Operations, General Operations, High Availability Operations, Server Operations, and Specialized Operations.

The Database Operations are mysqldbcompare, mysqldbcopy, mysqldbexport, mysqldbimport, and mysqldiff.

mysqldbcopy is used to copy databases between servers (or clone them on the same server. The default is to copy (or clone) everything or the –exclude option to exclude an object, change the storage engine with –new-storage-engine, and locking of the source databse can be turned off with –locking. There is even a –rpl option to pass replication statements so the the new copy a clone of the source server or sync the new copy to the same master as that the slave used for the source database. There is a –drop-first to make a fresh copy, -all to copy all databases

mysqldbcopy --source=root:hidave@localhost --destination=root:hidave@localhost world:world_copy
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Copying database world renamed as world_copy
# Copying TABLE world.City
# Copying TABLE world.Country
# Copying TABLE world.CountryLanguage
# Copying data for TABLE world.City
# Copying data for TABLE world.Country
# Copying data for TABLE world.CountryLanguage

More details on the man page.

After copying (or cloning) a database it is easy to double check
your work with mysqldbcompare.
mysqldbcompare --server1=root:hidave@localhost world:world_copy --run-all-tests
# server1 on localhost: ... connected.
# Checking databases world and world_copy on server1
# Object definitions differ. (--changes-for=server1)

--- `world`
+++ `world_copy`
@@ -1 +1 @@
+CREATE DATABASE `world_copy` /*!40100 DEFAULT CHARACTER SET latin1 */

# Defn Row Data
# Type Object Name Diff Count Check
# -------------------------------------------------------------------------
# TABLE City pass pass pass
# TABLE Country pass pass pass
# TABLE CountryLanguage pass pass pass

# Databases are consistent.
# ...done

More at the man page.

It is easy to use mysqldiff to check differences between two instances. For demonstration purposes, a dummy column was added to wold_copy.City.
mysqldiff --server1=root:hidave@localhost world:world_copy
# server1 on localhost: ... connected.
# Comparing `world` to `world_copy` [FAIL]
# Object definitions differ. (--changes-for=server1)

--- `world`
+++ `world_copy`
@@ -1 +1 @@
+CREATE DATABASE `world_copy` /*!40100 DEFAULT CHARACTER SET latin1 */
Compare failed. One or more differences found.

With version 1.5 id comes the –diff to display the differences.

The mysqldbexport command exports metadata, data, or both. There is a –bulk-insert can be used with –export=DATA to export data from a database for bulk import.
mysqldbexport --server=root:hidave@localhost world
# Source on localhost: ... connected.
# Exporting metadata from world
USE `world`;
# TABLE: world.City
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
# TABLE: world.Country
CREATE TABLE `Country` (
`Code` char(3) NOT NULL DEFAULT '',
`Name` char(52) NOT NULL DEFAULT '',
`Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
`Region` char(26) NOT NULL DEFAULT '',
`SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00',
`IndepYear` smallint(6) DEFAULT NULL,
`Population` int(11) NOT NULL DEFAULT '0',
`LifeExpectancy` float(3,1) DEFAULT NULL,
`GNP` float(10,2) DEFAULT NULL,
`GNPOld` float(10,2) DEFAULT NULL,
`LocalName` char(45) NOT NULL DEFAULT '',
`GovernmentForm` char(45) NOT NULL DEFAULT '',
`HeadOfState` char(60) DEFAULT NULL,
`Capital` int(11) DEFAULT NULL,
`Code2` char(2) NOT NULL DEFAULT '',
# TABLE: world.CountryLanguage
CREATE TABLE `CountryLanguage` (
`CountryCode` char(3) NOT NULL DEFAULT '',
`Language` char(30) NOT NULL DEFAULT '',
`IsOfficial` enum('T','F') NOT NULL DEFAULT 'F',
`Percentage` float(4,1) NOT NULL DEFAULT '0.0',
PRIMARY KEY (`CountryCode`,`Language`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)

The man page.

And fianlly mysqldbimport is used to read metadata, data or both. Note that if an object exists on the destination server it will be dropped. If you need to copy only the data after a truncate table, use the –import=data option. See the man page for details.

Next time: The MySQL Utilities General Operations

Leave a comment

Filed under Uncategorized

Raleigh MySQL Tech Tour

MySQL Raleigh Tech Tour

Please join MySQL Sr. Sales Consultant Tony Darnell who will review latest and greatest updates in MySQL 5.6 and share his experience on how they improve performance, scalability and high availability. Topics include:

  • MySQL 5.6 — New Features Update
  • Security Improvements — Encryption
  • Fabric
  • MySQL Enterprise Tools Update
  • MySQL Utilities Update
  • New MySQL Plug-In for Oracle OEM
  • The Road Ahead — MySQL 5.7
  • Oracle Support for MySQL

To register you must have an Oracle account, if you don’t have an Oracle account, sign up is quick and easy.

We look forward to your participation at this exclusive seminar.

Oct 21, 2014
08:30 AM – 12:30 PM
Oracle Raleigh
8081 Arco Corporate Drive,
Suite 270
Raleigh, NC 27617

Featured Speaker:

Tony Darnell,
Sr Sales Consultant

David Stokes,
MySQL Community Manager

Leave a comment

Filed under Tech Tour, Uncategorized

Lightning Talks at MySQL Community Reception

Come dazzle us! Lightening talks will be part of the entertainment at the MySQL Community Reception Tuesday September 30th at 7:00 pm. (RSVP link for reception here). You do not need to be an Oracle Openworld or MySQL Central @ OpenWorld attendee to come to the reception but you do need to RSVP.

You will have five minutes to impress the audience and a chance to win a stuffed dolphin plush toy. There will be no slides, but props will not be discouraged. We are looking for you best material on your favourite subject and, after a full day of tech talk, we are looking to keep the content on the lighter side.

Send you name and the subject of your talk to david.stokes AT oracle.com with the email head of ‘Lightning Talk’.

Leave a comment

Filed under MySQL Central @ Oracle OpenWorld, MySQL Community Team Event