A database can never be too optimized, and DBAs will never be completely satisfied with the performance of their creations. As your MySQL databases grow in size and complexity, taking full advantage of the optimizing tools built into the MySQL Workbench becomes increasingly important.
DBAs have something in common with NASCAR pit crew chiefs: No matter how well your MySQL database is performing, there’s always a little voice in your head telling you, “I can make it go faster.”
Of course, you can go overboard trying to fine-tune your database’s performance. In reality, most database tweaking is done to address a particular performance glitch or to prevent the system from bogging down as the database grows in size and complexity.
One of the tools in the MySQL Workbench for optimizing your database is the Performance Dashboard. When you mouse over a graph or other element in the dashboard, you get a snapshot of server, network, and InnoDB metrics.
The Performance Dashboard in the MySQL Workbench provides at-a-glance views of key metrics of network traffic, server activity, and InnoDB storage. Source: MySQL.com
Other built-in optimization tools are Performance Reports for analyzing IO hotspots, high-cost SQL statements, Wait statistics, and InnoDB engine metrics; Visual Explain Plans that offer graphical views of SQL statement execution; and Query Statistics that report on client timing, network latency, server execution timing, index use, rows scanned, joins, temporary storage use, and other operations.
A maintenance release of the MySQL Workbench, version 6.2.4, was announced on November 20, 2014, and is described on the MySQL Workbench Team Blog. Among the new features in MySQL Workbench 6.2 are a spatial data viewer for graphing data sets with GEOMETRY data; enhanced Fabric Cluster connectivity; and a Metadata Locks View for finding and troubleshooting threads that are blocked or stuck waiting on a lock.
Peering deeper into your database’s operation
One of the performance enhancements in MySQL 5.7 is the new Cost Model, as Marcin Szalowicz explains in a September 25, 2014, post on the MySQL Workbench blog. For example, Visual Explain’s interface has been improved to facilitate optimizing query performance.
MySQL 5.7’s Visual Explain interface now provides more insight for improving the query processing of your database. Source: MySQL.com
The new query results panel centralizes information about result sets, including Result Grid, Form Editor, Field Types, Query Stats, Spatial Viewer, and both traditional and Visual Execution Plans. Also new is the File > Run SQL Script option that makes it easy to execute huge SQL script files.
Attempts to optimize SQL tables automatically via the OPTIMIZE TABLE command often go nowhere. A post from March 2011 on Stack Overflow demonstrates that you may end up with slower performance and more storage space used rather than less. The best approach is to use “mysqlcheck” at the command line:
Run “mysqlcheck” at the command line to optimize a single database or all databases at once. Source: Stack Overflow
Alternatively, you could run a php script to optimize all the tables in a database:
A php script can be used to optimize all the tables in a database at one time. Source: Stack Overflow
A follow-up to the above post on DBA StackExchange points out that MySQL Workbench has a “hidden” maintenance tool called the Schema Inspector that opens an editor area in which you can inspect and tweak several pages at once.
What is evident from these exchanges is that database optimization remains a continuous process, even with the arrival of new tools and techniques. A principal advantage of the Morpheus database-as-a-service (DBaaS) is the use of a single dashboard to access statistics about all your MySQL, MongoDB, Redis, and ElasticSearch databases.
With Morpheus you can provision, deploy, and host SQL, NoSQL, and in-memory databases with a single click. The service supports a range of tools for connecting, configuring, and managing your databases, and automated backups for MySQL and Redis.
Visit the Morpheus site to create a free account. Database optimization has never been simpler!
