Taxonomy upgrade extras: HAProxyload balancerGalera ClusterVIPvirtual IPHigh Availabilityha

After properly installing and testing a Galera Cluster we see that the set-up is not finished yet. It needs something in front of the Galera Cluster that balances the load over all nodes.
So we install a load balancer in front of the Galera Cluster. Typically nowadays …

Here is a list of my most useful tools that I use when doing performance audits.
Please note, I am writing this mainly for myself, because I sometimes end up trying to find them in my other blog post about mastering indexing and this may save me time as well as a few changes that have happened over the years.
Regular Slow Log Report pt-query-digest slow_query.log  >slow.txt
All Queries (that use indexes) for a certain table pt-query-digest slow_query.log  --filter '($event->{fingerprint} =~ m/^(!?select|update|delete)/) &&  ($event->{arg} =~ m/mytable /) ' --limit=100% >mytable.txt
Longest Running Select Queries - most painful queries with response time % right next to them. pt-query-digest slow_query.log  --filter '($event->{fingerprint} =~ …

Disclaimer: Do this at your own risk! It doesn’t apply if you’re using Pluggable authentication and certainly won’t be usable if/when MySQL system tables are stored on InnoDB

Recover your root password with care!

What is the situation?

The situation is the classic “need to recover MySQL root password” but you cannot restart MySQL (because it is the master production server, or any other reason), which makes the –skip-grant-tables solution as a no-no possibility.

 What can I do?

There is a workaround, which is the following:

•  Launch another instance of mysqld, a small one (without innodb).
•  Copy your user.[frm|MYD|MYI] files from the original datadir to the datadir of the new instance.
• Modify them and then copy …

In MySQL 5.6 Oracle introduced a Transportable Tablespace feature (copying tablespaces to another server) and Percona Server adopted it for partial backups which means you can now take individual database or table backups and your destination server can be a vanilla MySQL server. Moreover, since Percona Server 5.6, innodb_import_table_from_xtrabackup is obsolete as Percona Server also implemented Oracle MySQL’s transportable tablespaces feature which as I mentioned gives you the ability to copy tablespace (table.ibd) between servers. Let me demonstrate this through one example where I am going to take partial backup of selective tables instead of an entire MySQL server and restore it on a running MySQL …

This should sound familiar:

One of the great lies about NoSQL databases is that they’re simple. Simplicity done wrong makes things a lot harder and more complicated to develop and operate. Programmers and operations staff end up reimplementing (badly) things the database should do.

Nobody argued this line of reasoning more vigorously than when trying to defend relational databases, especially during the darkest years (ca. 2009-2010), when NoSQL still meant NO SQL DAMMIT, all sorts of NoSQL databases were sprouting, and most of them were massively overhyped. But as valid as those arguments against NoSQL’s “false economy” simplicity were and are, the arguments against relational databases’ complexity hold true, too.

The truth is that no database is really simple. Databases have a lot of functionality and behaviors—even the “simple” databases do—and require deep knowledge to use well when …

This should sound familiar:

One of the great lies about NoSQL databases is that they’re simple. Simplicity done wrong makes things a lot harder and more complicated to develop and operate. Programmers and operations staff end up reimplementing (badly) things the database should do.

Nobody argued this line of reasoning more vigorously than when trying to defend relational databases, especially during the darkest years (ca. 2009-2010), when NoSQL still meant NO SQL DAMMIT, all sorts of NoSQL databases were sprouting, and most of them were massively overhyped. But as valid as those arguments against NoSQL’s “false economy” simplicity were and are, the arguments against relational databases’ complexity hold true, too.

The truth is that no database is really simple. Databases have a lot of functionality and behaviors—even the “simple” databases do—and require deep knowledge to use well when …

MariaDB tries to encourage use of many storage engines, which Oracle doesn't bother with. The ones that could be considered -- occasionally! -- as InnoDB replacements are: Mroonga, TokuDB, and Aria.

Mroonga

I predicted that Mroonga would be in MariaDB 10.0.8, and behold, here it is in MariaDB 10.0.15. Truly I am the Nostradamus of the database world.

It's a general-purpose storage engine, in that it doesn't have serious limitations regarding what can be stored or indexed. I think its "column-store" feature has limited appeal, but as I saw in January, it's dandy for full-text searching. This time I look at the list of "Full-Text Restrictions" in the MySQL manual, and quote from it:

"Full-text searches are supported for …

Intro

I had the opportunity to work with a variety of databases: Firebird (yeap!), MySQL, Postgres, Oracle and SQL Server. And to count the NoSQL ones I was able to work with MongoDB and DynamoDB.

I have different opinions about each one of the above (note that I didn't include Access on the list, as far as I know, that is not a database). Also, it is not secret that I like Amazon AWS products, and using some of them daily I can see why each service can be magically integrated to achieve a goal. …

With MySQL replication, when using the UUID() function to insert or update values in your database you need to be careful to assure the UUIDs are replicated properly. This is primarily a problem in statement-based replication.

Here's a simple example with row-based replication to illustrate UUIDs replicating successfully:

Master

``` master > set binlog_format = 'ROW'; Query OK, 0 rows affected (0.00 sec)

master > insert into uuid_test (binlog_format,uuid_string) values(@@binlog_format,uuid()); Query OK, 1 row affected (0.00 sec)

master > select id,binlog_format,uuid_string,md5(uuid_string) from uuid_test; +----+---------------+--------------------------------------+----------------------------------+ | id | binlog_format | uuid_string | md5(uuid_string) | …

MySQL Cluster NDB is a transaction engine and allows multi-master for write operations.  One may consider the architecture using other storage engine such as INNODB as slave server to provide the historical database.  This serves the analytic applications with historical data.  The master - slave topology allows scale out for both Master and Slave Servers.

In order to achieve the MySQL Cluster Replication between NDB and other storage engine together with providing historical data,
1. Setup the MySQL Cluster Replication
2. Disable binary logging for data deletion process - Ensure data removal / house keeping process on transaction tables in the MySQL Cluster not to propagate to the slaves.  Otherwise, data from slave servers will also be cleaned up.

Setup the MySQL Cluster Replication
a. With a running MySQL Cluster, management node should be running.   Attaching …