DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations

Trending

  • Security Challenges for Microservice Applications in Multi-Cloud Environments
  • Azure Virtual Machines
  • What Is JHipster?
  • Tech Hiring: Trends, Predictions, and Strategies for Success

Trending

  • Security Challenges for Microservice Applications in Multi-Cloud Environments
  • Azure Virtual Machines
  • What Is JHipster?
  • Tech Hiring: Trends, Predictions, and Strategies for Success
  1. DZone
  2. Data Engineering
  3. Databases
  4. Your Options for Optimizing the Performance of MySQL Databases

Your Options for Optimizing the Performance of MySQL Databases

Gen Furukawa user avatar by
Gen Furukawa
·
Mar. 04, 15 · Interview
Like (0)
Save
Tweet
Share
4.20K Views

Join the DZone community and get the full member experience.

Join For Free
feature photo

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 running backups for mysql and redis.

visit the morpheus site to create a free account . database optimization has never been simpler!

Database MySQL Workbench

Published at DZone with permission of Gen Furukawa, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Trending

  • Security Challenges for Microservice Applications in Multi-Cloud Environments
  • Azure Virtual Machines
  • What Is JHipster?
  • Tech Hiring: Trends, Predictions, and Strategies for Success

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: