MariaDB can now print query’s EXPLAIN into the slow query log! The feature is based on SHOW EXPLAIN technology and its output looks like this:

# Time: 131017 21:26:02
# User@Host: root[root] @ localhost []
# Thread_id: 2  Schema: dbt3sf1  QC_hit: No
# Query_time: 5.535819  Lock_time: 0.000350  Rows_sent: 1  Rows_examined: 65633
## <explain>
##   id select_type     table   type    possible_keys   key     key_len ref     rows    Extra
##   1  SIMPLE  nation  ref     PRIMARY,n_name  n_name  26      const   1       Using where; Using index
##   1  SIMPLE  customer        ref     PRIMARY,i_c_nationkey   i_c_nationkey   5       dbt3sf1.nation.n_nationkey      25124   Using index
##   1  SIMPLE  orders  ref     i_o_custkey     i_o_custkey     5       dbt3sf1.customer.c_custkey      7       Using index
## </explain>
SET timestamp=1382030762;
select count(*) from customer, orders, nation …

Do you have the background necessary to take full advantage of Tungsten Replicator in your environments? Tungsten offers enterprise-quality replication features in an open source package hosted on Google Code. This virtual course will teach you how to set up innovative topologies that solve complex replication problems. We start with a list of the main problems that Tungsten can solve, then show

MySQL 5.6 added support for EXPLAIN INSERT/UPDATE/DELETE. MySQL 5.7 made some improvements to EXPLAIN code. Also, 5.7 added support for EXPLAIN FOR CONNECTION, which looks very similar to MariaDB’s SHOW EXPLAIN. I was working on putting EXPLAIN INSERT/UPDATE/DELETE into MariaDB 10.0, so I’ve dug up some details and thought I’d share them:

EXPLAIN UPDATE/DELETE

EXPLAIN UPDATE looks like regular EXPLAIN SELECT:

MySQL [dbt3sf1]> explain update orders set o_orderpriority='1-URGENT' where o_orderDATE='1998-07-21';
+----+-------------+--------+-------+---------------+---------------+---------+-------+------+-------------+
| id | select_type | table  | type  | possible_keys | key           | key_len | ref   | rows | Extra       |
+----+-------------+--------+-------+---------------+---------------+---------+-------+------+-------------+
|  1 | SIMPLE      | orders | range | i_o_orderdate | i_o_orderdate | 4       | const |  628 | Using …

Here are some observations from writing web-crawling robots.

Intro

At some point, many of us (in the IT security industry) will need to write a robot which scrapes lots of web sites. By "lots", I mean a very large number, run by arbitrary parties. Not just a few run by well-behaved, cooperative entities.

Most owners of web servers try to make them compatible - but this is not guaranteed. Even with the best of intentions, we'll probably find things which go wrong.

Behaviour observed
Faulty DNS
* Returns too large responses
* Returns private addresses in "A" responses

Server hangs / timeout
* Connection timeout
* Timeout waiting for response
* Connection hang during headers or response

Bad responses
* Connection closed after request
* Connection closed while transmitting headers
* Connection closed …

The first section in the exam topics for both the MySQL 5.6 Developer and DBA exam deals is titled MySQL Architecture, but each has a different emphasis.  This blog will focus on those objectives listed for the Developer exam:

• Use MySQL client programs to interface with the MySQL Server interactively and in batch
• Describe SQL Modes and their impact on behavior of MySQL
• Identify characteristics which have session scope

For those of us who have spent time pulling both DBA and developer duties, this section is likely to be relatively straight-forward – perhaps even easy.  I’ll walk through the exercises and documents I’ve used to review below to kick off my Exam Cram blog series.

Using MySQL Client Programs

My first step …

this is a short script for daily backup of the OpenStack MySQL and the /etc direcotry of the control node

[root@dev-epg-rhos-01 BACKUP]# cat /root/bin/epg.innobackupex.openstack.sh
#!/bin/sh
# done for epgmad4@tid.es
# Barcelona Thu Oct 17 16:30:36 CEST 2013

days_to_keep=7
NFS=/BACKUP
exportDate=`date +%Y-%m-%d.%H.%M.%S`
export_DIR=${NFS}/${HOSTNAME}.${exportDate}
test ! -d "${export_DIR}" && echo "$(date) : creating ${export_DIR}" && mkdir -p "${export_DIR}"
export_MySQL_DIR=${export_DIR}/mysql.bckp
export_ETC_DIR=${export_DIR}/etc.bckp
rsync -avh /etc ${export_ETC_DIR}
echo "=========================================================================================================" >> ${export_DIR}/README.restore.with.innobackupex
echo "HOW to restore this FULL mysql backup" >> ${export_DIR}/README.restore.with.innobackupex
echo "=========================================================================================================" >> …

We are looking for community feedback on the use-cases for the Federated Storage Engine in MySQL.

Specifically, I would be interesting to hear which users find that the introduction of multi-source replication meets (or does not meet) their requirements.

For a bit of background:

The Federated Storage Engine was introduced in 5.0, but has long been disabled by default. It is useful for ad-hoc queries across MySQL servers, but it misses some of MySQL’s newer optimizations, and does not perform as well as we would like. Among it’s limitations it also does not support transactions.

By using multi-source replication, many of the downsides mentioned above are negated, since …

When I came from Linux world to Mac OS, I’ve got frustrated in not having a package management system as there are in Ubuntu (with Apt-get) or in CentOS ( YUM ). The world was so fantastic, if I need to install MySQL Database on Ubuntu, I’d just type: $ sudo apt-get install mysql-server On Mac, the […]

The SQL language is a bit like a toolkit for data. It consists of lots of little fiddly bits of syntax that, taken together, allow you to build complex edifices and return powerful results. For the uninitiated, the many tools can be quite confusing, and it's sometimes difficult to decide how to go about the process of building non-trivial queries, that is, queries that are more than a simple SELECT a, b FROM c;

A System for Building Queries

When you're building queries, you could use a system like the following: 

1. Decide which fields contain the values you want to use in our output, and how you wish to alias those fields
   1. Values you want to see in your output
   2. Values you want to use in calculations . For example, to calculate margin on a product, you could calculate price - cost and give it the alias margin.
   3. Values you want …

Checking throughput with async MySQL replication

Replication throughput is the measure of just how fast the slaves can apply replication (at least by my definition).  In MySQL async replication this is important to know because the single-threaded apply nature of async replication can be a write performance bottleneck.  In a production system, we can tell how fast the slave is currently running (applying writes), and we might have historical data to check for the most throughput ever seen, but that doesn’t give us a solid way of determining where we stand right NOW().

An old consulting trick to answer this question is to simply stop replicating on your slave for a minute, (usually just the SQL_THREAD), restart it and watch how long it takes to catch up.  We can also watch the slave thread apply rate during this interval to get a sense of just how many writes per second we can do and compare that with the normal rate …