#DBHangOps 05/28/15 -- Deep Engine and more!

Hello everybody!

Join in #DBHangOps this Thursday, May, 28, 2015 at 11:00am pacific (19:00 GMT), to participate in the discussion about:

• Deep Engine presentation by Mike Skubisz! (Come with questions!)

• What does being a DBA mean to you?

  • What are your expectations of a DBA?
• What's the last thing you automated and why?

You can check out the event page at https://plus.google.com/events/cgomeasl8m4fr47tl25e74rvh9g on Thursday to participate.

As always, you can still watch the #DBHangOps twitter search, the @DBHangOps …

Many database vendors would like me to take a look at their products and consider adopting them for all sorts of purposes. Often they’re pitching something quite new and unproven as a replacement for mature, boring technology I’m using happily.

I would consider a new and unproven technology, and I often have. As I’ve written previously, though, a real evaluation takes a lot of effort, and that makes most evaluations non-starters.

Perhaps the most important thing I’m considering is whether the product is mature. There are different levels of maturity, naturally, but I want to understand whether it’s mature enough for me to take a look at it. And in that spirit, it’s worth understanding what makes a database mature.

For my purposes, maturity really means demonstrated capability and quality with a lot of …

I accidentally stumbled upon this Stack Overflow question this morning:

I am wondering if there is any difference in regards to performance between the following:

SELECT ... FROM ... WHERE someFIELD IN(1,2,3,4);
SELECT ... FROM ... WHERE someFIELD between  0 AND 5;
SELECT ... FROM ... WHERE someFIELD = 1 OR someFIELD = 2 OR someFIELD = 3 ...;

It is an interesting question because there was no good way to answer it when it was asked in 2009. All of the queries resolve to the same output in EXPLAIN. Here is an example using the sakila schema:

mysql> EXPLAIN SELECT * FROM film WHERE film_id BETWEEN 1 AND 5\G
mysql> EXPLAIN SELECT * FROM film WHERE film_id IN (1,2,3,4,5)\G
mysql> EXPLAIN SELECT * FROM film WHERE film_id =1 or film_id=2 or film_id=3 or film_id=4 or film_id=5\G
********* 1. row *********
           id: 1 …

History is repeating again. MongoDB is breaking out of the niche into the mainstream, performance and instrumentation are terrible in specific cases, MongoDB isn’t able to fix all the problems alone, and an ecosystem is growing.

This should really be a series of blog posts, because there’s a book’s worth of things happening, but I’ll summarize instead. Randomly ordered:

• MongoDB is in many respects closely following MySQL’s development, 10 years offset. Single index per query, MyISAM-like storage engine, etc. Background.
• Tokutek built an excellent transactional storage engine and replaced MongoDB’s, calling it TokuMX. Results were dramatically better performance (plus ACID). MongoDB’s response was to buy WiredTiger and make it the default storage engine in MongoDB 3.0.
• Percona acquired …

Fri, 2015-05-22 07:19geoff_montee_g

Out-of-order parallel replication is a great feature in MariaDB 10.0 that improves replication performance by committing independent transactions in parallel on a slave. If slave_parallel_threads is greater than 0, then the SQL thread will instruct multiple worker threads to concurrently apply transactions with different domain IDs.

If an application is setting the domain ID, and if parallel replication is enabled in MariaDB, then out-of-order parallel replication should mostly work automatically. However, depending on an …

At Yahoo, we manage a massive number of MySQL databases spread across multiple data centers. 

In order to identify and respond to performance issues, we rely on an extremely lightweight and robust web based tool to proactively investigate the issues in them.

The tool has real time tracking features and continually gathers the most important performance metrics, provides visualization and statistical analysis for quickly identifying performance patterns, bottlenecks and possible tuning opportunities.

Features

Lightweight Agentless Java Web Application
Rich User Interface
Gather and Store performance metrics 
Detect anomalies and send alerts
Access to Real time Performance data

Open Source

Today, we’re releasing MySQL Performance Analyzer. You can check out the code on …

General-purpose MySQL applications should read MySQL option files like /etc/my.cnf, ~/.my.cnf, ... and ~/.mylogin.cnf. But ~/.mylogin.cnf is encrypted. That's a problem for our ocelotgui GUI application, and I suppose other writers of Linux applications could face the same problem, so I'll share the code we'll use to solve it.

First some words of defence. I think that encryption (or more correctly obfuscation) is okay as an option: a customer asked for it, and it prevents the most casual snoopers -- rather like a low fence: anyone can get over it, but making it a bit troublesome will make most passersby pass by. I favoured the idea, though other MySQL employees were against it on the old "false sense of security" argument. After all, by …

In part one of this post, I gave you a couple examples of how to backup your MySQL databases using mysqldump. In part two, I will show you how to use the MySQL Enterprise Backup (MEB) to create a full and partial backup.

MySQL Enterprise Backup provides enterprise-grade backup and recovery for MySQL. It delivers hot, online, non-blocking backups on multiple platforms including Linux, Windows, Mac & Solaris. To learn more, you may download a …

[…] of the cool new features in 5.7 Release Candidate is Multi Source […]

Last week, I wrote about how to use bash arrays and the MySQL database to create unit and integration test scripts. While the MySQL example was nice for some users, there were some others who wanted me to show how to write bash shell scripts for Oracle unit and integration testing. That’s what this blog post does.

If you don’t know much about bash shell, you should start with the prior post to learn about bash arrays, if-statements, and for-loops. In this blog post I only cover how to implement a bash shell script that runs SQL scripts in silent mode and then queries the database in silent mode and writes the output to an external file.

I’ve copied the basic ERD for the example because of a request from a reader. In their opinion it makes cross referencing the …