A few days back we were given a MySQL database hosted in EC2 with around 5TB data to move some of the tables to a new server.  The intention was to share the tables between 2 EC2 instances. Since AWS had the option to take online snapshots, the plan was to take a snapshot, create a new machine with that snapshot and drop the unwanted tables.So everything went as planned until creating a new machine with the snapshot.  The real challenge was dropping the unwanted tables.  It took around 4 minutes to Drop a table whose size is 20GB.  It took 20 minutes to drop a 100GB table. The time kept on increasing for larger tables. MySQL even went to “defunct” when we killed the drop query and at times crashed. To track down this issue we executed drop table in one session and checked the processlist from another session that gave the below output.
mysql> show processlist \G

*************************** 1. row …

If you’re monitoring MySQL performance on dozens or hundreds of servers, chances are you have a “rainbow chart” – a time-series chart with hundreds of tiny area graphs stacked on top of each other, crawling imperceptibly one pixel at a time across the big-screen monitor in your office. The trouble with these charts is they’re hard to see. It takes many minutes for enough new pixels to display after a change. In the meantime you can’t see the change clearly.

At VividCortex, we think we’ve found a better way to keep tabs on what’s going on in your infrastructure: a bubble visualization. It is compact, and immediately communicates current status and trend, with visualizations that your eye interprets in a glance.

This is the Activity layout on the Hosts dashboard. It’s designed to scale to hundreds, even thousands of hosts. Tabular layouts and strip-charts won’t do the trick, but live, interactive bubbles will. …

TokuDB offers high throughput for write intensive applications, and the throughput scales with the number of concurrent clients.  However, when the binary log is turned on, TokuDB 7.5.2 throughput suffers.  The throughput scaling problem is caused by a poor interaction between the binary log group commit algorithm in MySQL 5.6 and the way TokuDB commits transactions.   TokuDB 7.5.4 for Percona Server 5.6 fixes this problem, and the result is roughly an order of magnitude increase in SysBench throughput for in memory workloads.

MySQL uses two phase commit protocol to synchronize the MySQL binary log with the recovery logs of the storage engines when a transaction commits.  Since fsync’s are used to ensure the durability of the data in the various logs, and fsync’s can be very slow, the fsync can easily become a bottleneck.  A …

I believe InnoDB storage engine architecture is great for a lot of online workloads, however, there are no silver bullets in technology and all design choices have their trade offs. In this blog post I’m going to talk about one important InnoDB limitation that you should consider.

InnoDB is a multiversion concurrency control (MVCC) storage engine which means many versions of the single row can exist at the same time. In fact there can be a huge amount of such row versions. Depending on the isolation mode you have chosen, InnoDB might have to keep all row versions going back to the earliest active read view, but at the very least it will have to keep all versions going back to the start of SELECT query which is currently running.

In most cases this is not a big deal – if you have many short transactions happening you will have only a few row versions to deal with. If you just use the system for reporting queries but do not …

One of great innovation in MySQL 5.7 is memory summary tables в Performance Schema and corresponding views in sys schema

And as troubleshooting freak I have huge reason to greet this feature.

Before version 5.7 we had very limited abilities to diagnose memory issues in MySQL. We could use operating system tools, such as vmstat, top, free, but they only showed what MySQL server uses memory, but do not show how. In version 5.7 things changed.

Lets examine what can we study about memory usage by MySQL Server.

At first, this is total amount of memory, used by all internal MySQL structures:

mysql> select * from …

How to install osCommerce on Ubuntu 14.04 (Trusty Tahr)

This document describes how to install osCommerce in Ubuntu 14.04. Open Source Commerce (osCommerce) is a popular e-Commerce and online store-management software program that may be easily used on any web server with PHP and MySQL installed. osCommerce is available to users as a free software under the General Public License (GNU) The versatile and fuss-free software enables easy setting up and maintenance of e-stores using minimal effort. This tutorial describes the process of installing osCommerce on Ubuntu 14.04.

How to install osCommerce on Ubuntu 14.04 (Trusty Tahr) This document describes how to install osCommerce in Ubuntu 14.04. Open Source Commerce (osCommerce) is a popular e-Commerce and online store-management software program that may be easily used on any web server with PHP and MySQL installed. osCommerce is available to users as a free software under the General Public License (GNU) The versatile and fuss-free software enables easy setting up and maintenance of e-stores using minimal effort. This tutorial describes the process of installing osCommerce on Ubuntu 14.04.

How to install osCommerce on Ubuntu 14.04 (Trusty Tahr) This document describes how to install osCommerce in Ubuntu 14.04. Open Source Commerce (osCommerce) is a popular e-Commerce and online store-management software program that may be easily used on any web server with PHP and MySQL installed. osCommerce is available to users as a free software under the General Public License (GNU) The versatile and fuss-free software enables easy setting up and maintenance of e-stores using minimal effort. This tutorial describes the process of installing osCommerce on Ubuntu 14.04.

For an online shop, the website is the cash register. It has to be open for business 24 hours a day.

As the ops person on duty, when you get a call at 3am after your website went down, your priority number one is to restore the service asap. But why can we not have our application stack automatically recover, and not have the pager wake us at all? Why do we still stick to tedious manual processes, which take up time and resources, and hinder future growth?

Infrastructure automation isn’t easy, but it’s not rocket science either, says Riaan Nolan. Riaan has been in operations for the past decade, and has built over a dozen eCommerce properties. Automation is a worthwhile investment for retailers serious about eCommerce, but deciding on which tools to invest in can be a confusing and overwhelming process.

Join us for this webinar to understand the key pain points that online retailers experience which indicate it’s time …

It’s an ancient tradition amongst MySQL DBAs to analyze slow query log files and sort the results by the so-called “index ratio” to find badly indexed queries. The “index ratio,” in this case, is a heuristic derived from the ratio of rows read to rows returned. The thinking goes that if a query is reading lots of rows and returning only a few, it might be missing an index.

The trouble with this is that it’s very prone to false positives: GROUP BY queries, for example, will often read lots of rows and aggregate them into few.

Instead of relying on a surrogate measure such as this sometimes-accidentally-right ratio, why not get the truth straight from the horse’s mouth? MySQL sets a flag in a query when it doesn’t use an index. And VividCortex sniffs this flag right off the wire and generates metrics from it.

To rank queries by their frequency of no-index-used flag, just go to Top Queries and change the Rank …