In many write-intensive workloads Innodb/XtraDB storage engines you may see hidden and dangerous “debt” being accumulated – unpurged transaction “history” which if not kept in check over time will cause serve performance regression or will take all free space and cause an outage. Let’s talk about where it comes from and what can you do to avoid running into the trouble.

Technical Background: InnoDB is an MVCC engine which means it keeps multiple versions of the rows in the database, and when rows are deleted or updated they are not immediately removed from the database but kept for some time – until they can be removed. For a majority of OLTP workloads they can be removed seconds after the change actually took place. In some cases though they might need to be kept for a long period of time – if there are some old transactions running in the system that might still need to look at an old database state. As of …

Bloggers get connected to both the databases and their readers through their blogs. Bloggers act like a bridge here. Log Buffer extends this nexus through the Log Buffer Edition.

Oracle:

MS Sharepoint and Oracle APEX integration.

Just a couple of screenshots of sqlplus+rlwrap+cygwin+console.

Say “Big Data” One More Time (I dare you!)

Update OEM Harvester after 12.1.0.4 Upgrade

Insight in the …

Multi-source replication for MySQL has been released as a part of 5.7.5-labs-preview
downloadable from labs.mysql.com. It is one among the several features that are
cooking in the replication technologies at MySQL.  (For a birds eye view of all
replication features introduced in 5.7 and labs, look  at the blog posts here and here.

Previously, we have introduced a preliminary multi-source feature labs preview. Based on the feed back from that labs release, we …

We have been asked several times by MySQL users about how to install multiple MySQL instances on a single host.
Typically, this is required when testing different MySQL versions or MySQL servers (MySQL server, Percona server and MariaDB server) while no available resources are available.
Sometimes, it is even required to install multiple MySQL instances on a single production server.

In this article, I'll go through the steps needed to install multiple MySQL instances on a single host (using the tar balls binaries) and how our popular tool MyEnv can make such process so easy.

Prepare MySQL environment

[root@centos-temp ~]# groupadd mysql
[root@centos-temp ~]# useradd -g mysql mysql
[root@centos-temp ~]# su - mysql
[mysql@centos-temp:~]$ mkdir ~/product
[mysql@centos-temp:~]$ mkdir ~/data
[mysql@centos-temp ~]$ cd /downloads
[mysql@centos-temp …

I still get asked about the lost+found directory enough, and so I wanted to provide a current update.

The lost+found directory is a filesystem directory created at the root level of a mapped drive. Thus this is common to see if you create your mysql datadir at the root level of a mapped drive.

In the past, you could ignore it, if it wasn’t too problematic for you, or you could move your datadir down a level, and then it wouldn’t be created in the datadir anymore.

However, there is now the –ignore-db-dir option. It is actually not too new (it’s been in MariaDB since 5.3.9 and 5.5.28, and in MySQL as of 5.6.3), but I don’t think many are too familiar with it.

But when you do run into this problem, some/many would prefer to add a single line to the config file rather than move the datadir.

To do this, just add the following option to your my.cnf file, under the [mysqld] section (it cannot be set …

Padding Oracle On Downgraded Legacy Encryption

First off, the naming “convention” as of late for security issues has been terrible. The newest vulnerability (CVE­-2014-3566) is nicknamed POODLE, which at least is an acronym and as per the header above has some meaning.

The summary of this issue is that it is much the same as the earlier B.E.A.S.T (Browser Exploit Against SSL TLS), however there’s no known mitigation method in this case – other than entirely disabling SSLv3 support, in short, an attacker has a vector by which they can retrieve the plaintext form your encrypted streams.

So let’s talk mitigation, the …

Percona Toolkit’s  pt-table-checksum performs an online replication consistency check by executing checksum queries on the master, which produces different results on replicas that are inconsistent with the master – and the tool pt-table-sync synchronizes data efficiently between MySQL tables.

The tools by default use the CRC32. Other good choices include MD5 and SHA1. If you have installed the FNV_64 user-defined function, pt-table-sync will detect it and prefer to use it, because it is much faster than the built-ins. You can also use …

Fri, 2014-10-10 10:08jensbollmann

Installing MariaDB became very easy on the latest CentOS /RHEL Linux version 7. During initial setup MariaDB has a tick in the graphical setup environment which enables MariaDB and replaces MySQL.

During installation the section

allows enabling a MariaDB server.

If you prefer the command line the installation can be performed with:

shell> yum install mariadb-server

MariaDB is shipped untuned and not protected with MariaDB version 5.5.35. No basic optimizations in the configuration file have been set and the root user has no password. An update will install the version 5.5.37. (The latest version of MariaDB October 2014 is Version 5.5.39).

shell> yum update

It is important to secure the server. MariaDB is shipped with a script to harden the system accordingly using a user dialog. It is highly recommended to use this script before …

Fri, 2014-10-10 09:36benstillman

For several years, I have hosted a medium sized Invision Power Board (IPB) site. The site has roughly 9,000+ users and 1,000,000+ posts. I work with MariaDB on a daily basis, and often find myself recommending it to customers. It's only appropriate I put my money where my mouth is, so to speak. This blog post has no benchmarks. It is just a very simple to follow set of steps I took to migrate my site from MySQL 5.1 to MariaDB 10 on CentOS 6.5 with very little downtime for the end users.

To do this, I setup a new virtual server for MariaDB, use mysqldump to export the data with master info, restored this backup to MariaDB, setup replication, set the site to offline, switched the conf_global.php database values, then set the site to online. Sounds easy, right? It was. Very easy. Here are the steps I took with more detail.

For these steps, we will call the original MySQL 5.1 server DB1 and …

The MySQL Server 5.7.5 Development Milestone Release, which was published recently, contains some significant changes to the metadata locking (MDL) subsystem and to the usage of the THR_LOCK manager for InnoDB tables. This post provides more information about these changes, which resulted in nice improvements in both scalability and performance.

Sometime during the development cycle of MySQL 5.6 we realized that locks used in the metadata locking subsystem (MDL) implementation can become a scalability bottleneck when many short statements were executed in @autocommit=1 mode against InnoDB tables.

Bug #66473 is a good example (8-table Sysbench POINT_SELECT/InnoDB test). In this particular case the bottleneck was the mutex protecting the MDL_map hash which …