The major engine for MySQL is InnoDB, it complies with the ACID properties for a transactional database engine. Even if InnoDB is the most recommended engine for MySQL, it has also some caveats. The biggest criticism lies in the amount of disk space used. And even when we remove data, tablespaces don’t shrink.

This post is about those tables that are fragmented.

To find the top 10 of tables with free space (free space means gaps that may happen when for example large delete operations happened or if many pages had to be moved around), a simple query can be run:

SELECT CONCAT(table_schema, '.', table_name) as 'TABLE', 
       ENGINE, CONCAT(ROUND(table_rows / 1000000, 2), 'M')  ROWS, 
       CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA, 
       CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') IDX, 
       CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') 'TOTAL SIZE', …

Let’s look at some of the most popular Percona Database Performance Blog posts in 2017.

The closing of a year lends itself to looking back. And making lists. With the Percona Database Performance Blog, Percona staff and leadership work hard to provide the open source community with insights, technical support, predictions and metrics around multiple open source database software technologies. We’ve had over three and a half million visits to the blog in 2017: thank you! We look forward to providing you with even better articles, news and information in 2018.

As 2017 moves into 2018, let’s take a quick look back at some of the most popular posts on the blog this year.

Top 10 Most Read

These posts had the most number of views (working down from the highest):

…

Join Percona Chief Evangelist Colin Charles as he covers happenings, gives pointers and provides musings on the open source database community.

The end of the year is upon us, and this column is being penned a little earlier than usual as I enjoy a quick bout of wrapping up for the year.

We’ve had a lot of things happen in the database world. It’s clear that open source is everywhere, the cloud is large, and we’ve seen a lot of interesting movements in our MySQL world, from the standpoint of corporate contributions too. GitHub continues to enhance Orchestrator, and gh-ost keeps on getting better; Facebook has ensured MyRocks is now mainstream in both MariaDB Server and Percona Server for MySQL distributions; Alibaba Cloud and Tencent have been contributing to make MariaDB Server better. …

In this blog, we’ll look at how to monitor Amazon Aurora using Percona Monitoring and Management (PMM).

One impact of the growth of web services such as AWS on DBAs is the need to understand and report on database and application resource usage. DBAs have always had to monitor for performance-related usability issues, which may have led to investment in larger servers. However, services such as Aurora operate pricing models based on usage. Performance issues can be more closely attributed to rising costs, so badly performing systems more visibly affect the bottom line. DBA has become a cost center!

With that in mind, from Percona Monitoring and Management (PMM) 1.5 onwards, we delivered features that provide enhanced support for the performance monitoring of Amazon Aurora instances. “[Aurora] is a fully managed, …

In this blog, we’ll look at how the hidepid options for /proc and Percona XtraDB Cluster can fight with one another.

One of the things I like about consulting at Percona is the opportunity to be exposed to unusual problems. I recently worked with a customer having issues getting SST to work with Percona XtraDB Cluster. A simple problem you would think. After four hours of debugging, my general feeling was that nothing made sense.

I added a bash trace to the SST script and it claimed MySQL died prematurely:

[ -n '' ]]
+ ps -p 11244
+ wsrep_log_error 'Parent mysqld process (PID:11244) terminated unexpectedly.'
+ wsrep_log '[ERROR] Parent mysqld process (PID:11244) terminated unexpectedly.'
++ date '+%Y-%m-%d %H:%M:%S'
+ local readonly 'tst=2017-11-28 22:02:46'

At the same time, from the MySQL error log MySQL was …

Setting up databases in development environments can be challenging.

Normally, what I usually see is some automated process for setting up empty databases with up-to-date data structures. This is helpful for integration testing, but is tricky for actual development as well as performance testing. 
For example:

• It is difficult to conceptually get your head around writing a query when you cannot see any data in your tables
• You cannot possibly know if your query is slow before you deploying it to production without running it against 'some' data.

Relevant Post: How to Not be the One that Deploys that Slow Query to Production
In addition, there can be a strict requirement to not let sensitive customer data be available outside certain secure environments and …

tl;dr: Try it out here: https://preetam.github.io/explain-analyzer/#!/explain/

I introduced my explain analyzer for MySQL a little over a month ago. Here’s what I wrote at the end of that post:

It could look a lot better. The real thing isn’t as nice as the design, and the design is still pretty bad. I’ll work on that soon. In terms of the implementation, the explain analyzer is a single page app without any state, so if you refresh the page, you’ll lose everything. I think I’m going to add a “share” feature so that you can get a permalink with all of the values saved. That’ll take some more work but I think it would be a neat opportunity to use AWS Lambda for a user-facing site!

Both the design and the sharing feature have been addressed with …

tl;dr: Try it out here: https://preetam.github.io/explain-analyzer/#!/explain/

I introduced my explain analyzer for MySQL a little over a month ago. Here’s what I wrote at the end of that post:

It could look a lot better. The real thing isn’t as nice as the design, and the design is still pretty bad. I’ll work on that soon. In terms of the implementation, the explain analyzer is a single page app without any state, so if you refresh the page, you’ll lose everything. I think I’m going to add a “share” feature so that you can get a permalink with all of the values saved. That’ll take some more work but I think it would be a neat opportunity to use AWS Lambda for a user-facing site!

Both the design and the sharing feature have been addressed …

tl;dr: Simple, MIT-licensed, available on GitHub: https://github.com/Preetam/mysqllog

I couldn’t find a simple slow query log parser in Go so I decided to write one. The two I found are:

• Honeycomb’s in Honeytail which is Apache licensed. But it does query text normalization which requires SQL parsing which I don’t need.
• Percona’s in their go-mysql repo. It’s AGPL licensed.

I don’t like how those packages use channels. There’s just too much plumbing required when you have an input channel and an output channel, or a stop channel. With my package you just need to create a parser and feed it slow query log data …

tl;dr: Simple, MIT-licensed, available on GitHub: https://github.com/Preetam/mysqllog

I couldn’t find a simple slow query log parser in Go so I decided to write one. The two I found are:

• Honeycomb’s in Honeytail which is Apache licensed. But it does query text normalization which requires SQL parsing which I don’t need.
• Percona’s in their go-mysql repo. It’s AGPL licensed.

I don’t like how those packages use channels. There’s just too much plumbing required when you have an input channel and an output channel, or a stop channel. With my package you just need to create a parser and feed it slow query log data …