May 18, 2013

Innodb Table Locks

July 31, 2012 By 4 Comments

Innodb uses row level locks right ? So if you see locked tables reported in SHOW ENGINE INNODB STATUS you might be confused and rightfully so as Innodb table locking is a bit more complicated than traditional MyISAM table locks. Let me start with some examples. First lets run SELECT Query:

As you can [...]

Filed Under: Insight for DBAs, MySQL

Percona XtraDB Cluster: Failure Scenarios with only 2 nodes

July 25, 2012 By 6 Comments

During the design period of a new cluster, it is always advised to have at least 3 nodes (this is the case with PXC but it’s also the same with PRM). But why and what are the risks ? The goal of having more than 2 nodes, in fact an odd number is recommended in [...]

Filed Under: MySQL, XtraDB Cluster

read_buffer_size can break your replication

June 6, 2012 By 12 Comments

There are some variables that can affect the replication behavior and sometimes cause some big troubles. In this post I’m going to talk about read_buffer_size and how this variable together with max_allowed_packet can break your replication. The setup is a master-master replication with the following values: max_allowed_packet = 32M read_buffer_size = 100M To break the [...]

Filed Under: MySQL

A case for MariaDB’s Hash Joins

May 31, 2012 By Leave a Comment

MariaDB 5.3/5.5 has introduced a new join type “Hash Joins” which is an implementation of a Classic Block-based Hash Join Algorithm. In this post we will see what the Hash Join is, how it works and for what types of queries would it be the right choice. I will show the results of executing benchmarks [...]

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , ,

Join Optimizations in MySQL 5.6 and MariaDB 5.5

April 4, 2012 By 4 Comments

This is the third blog post in the series of blog posts leading up to the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. This blog post is targeted at the join related optimizations introduced in the optimizer. These optimizations are available in both MySQL 5.6 and MariaDB 5.5, and MariaDB 5.5 [...]

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , , , , , , , , , , ,

InnoDB’s gap locks

March 27, 2012 By 15 Comments

One of the most important features of InnoDB is the row level locking. This feature provides better concurrency under heavy write load but needs additional precautions to avoid phantom reads and to get a consistent Statement based replication. To accomplish that, row level locking databases also acquire gap locks. What is a Phantom Read A [...]

Filed Under: MySQL

Troubleshooting MySQL Memory Usage

March 21, 2012 By 5 Comments

One of the most painful troubleshooting tasks with MySQL is troubleshooting memory usage. The problem usually starts like this – you have configured MySQL to use reasonable global buffers, such as innodb_buffer_size, key_buffer_size etc, you have reasonable amount of connections but yet MySQL takes much more memory than you would expect, causing swapping or other [...]

Filed Under: Insight for DBAs, MySQL

Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact

March 12, 2012 By 12 Comments

I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available [...]

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , , , ,

How to Monitor MySQL with Percona’s Nagios Plugins

February 22, 2012 By 9 Comments

In this post, I’ll cover the new MySQL monitoring plugins we created for Nagios, and explain their features and intended purpose. I want to add a little context. What problem were we trying to solve with these plugins? Why yet another set of MySQL monitoring plugins? The typical problem with Nagios monitoring (and indeed with [...]

Filed Under: Insight for DBAs, MySQL

How to convert MySQL’s SHOW PROFILES into a real profile

February 20, 2012 By 12 Comments

SHOW PROFILES shows how much time MySQL spends in various phases of query execution, but it isn’t a full-featured profile. By that, I mean that it doesn’t show similar phases aggregated together, doesn’t sort them by worst-first, and doesn’t show the relative amount of time consumed. I’ll profile the “nicer_but_slower_film_list” included with the Sakila sample [...]

Filed Under: Insight for Developers, MySQL
« Older Posts
Newer Posts »