Introduction

Transaction locks are an important feature of any transactional storage engine. There are two types of transaction locks – table locks and row locks. Table locks are used to avoid a table being altered or dropped by one transaction when another transaction is using the table. It is also used to prohibit a transaction from accessing a table, when it is being altered. InnoDB supports multiple granularity locking (MGL). So to access rows in a table, intention locks must be taken on the tables.

Row locks are at finer granularity than table level locks, different threads can work on different parts of the table without interfering with each other. This is in contrast with MyISAM where the entire table has to be locked when updating even unrelated rows. Having row locks means that multiple transactions can read and write into a single …

In a large scale mysql deployment there could be multiple masters and multiple slaves. Masters are generally in circular replication. And are used for running all inserts, updates and deletes. Slaves are used to run selects.

When you are dealing with multiple mysql instances running in a large scale environment, it is important to take care of lags between masters and slaves. To handle such

In an earlier post, I wrote about when MySQL loads data in and out of cache. At the end of this post, I talked about the concept of page churn, and having enough memory for your working set:

“If you do not have enough memory to hold all of your working set in memory, what will happen is that the buffer pool will start juggling as it churns pages out of memory only to load them back in soon after. A small amount of this is probably okay, but it can start to degrade performance. The traditional rule of thumb is called ”the 5 minute rule”. This means that if you load a page into memory and are going to need it again in the next five minutes - it should not need to be churned out.”

To be able to calculate this in a precise way we would …

Any introduction to Prepared Statements has to start with SQL Statements.

Every SQL Statement arriving at a database is processed before executing and returning a resultset. The processing is a series of computations including: parsing, compiling, and query optimization.

The parsing, compiling, and query optimization can lead to a lot of wasted CPU if your queries are very similar, like this example from the widely available test database Sakila:

Example Queries:

  SELECT * FROM address WHERE address_id = 12;

  SELECT * FROM address WHERE address_id = 34;

  SELECT * FROM address WHERE address_id = 56;

Example Code, assuming PDO & PHP, to produce those queries:

  $ids = array(12,34,56);

  $sql = “SELECT * FROM address WHERE address_id = “;

…

Shard-Query inserts data into a “coordinator” table when answering queries.   When there is a GROUP BY on the original query, the coordinator table contains a UNIQUE KEY over the GROUP BY attributes.   Shard-Query uses INSERT .. ON DUPLICATE KEY UPDATE in combination with bulk insert (insert into … values (),(),() ) when inserting into the table.

For what would normally be efficiency sake, Shard-Query sends queries to the shards using ORDER BY NULL which disables the filesort operation. Of course, this often results in the rows being sent back from the shards in random order.

Because the results are in random order, the bulk insertion that the worker does into the coordinator table can deadlock with other worker threads when using InnoDB or TokuDB as the coordinator table. Right now I’ve just been using MyISAM for the coordinator table, which serializes queries at the bulk insert stage.  Having to insert the …

It's now two.. three.. four weeks since Percona MySQL Conference was finished.. - and there are still many things and new ideas running in my mind inspired by this great MySQL tech + community event (regardless many public holidays and spring vacations in May (which is also the reason why I'm late with this article ;-)). There was a lot of things to learn from others, tons of stuff to share, and as well enjoy long and deep discussions with many MySQL users/ hackers/ gurus/ mentors/ devs/ etc.. etc.. We were very proud to speak about MySQL 5.6 GA during this conference, and also present the first labs release of the future MySQL 5.7 version (and you may already download and play with it, or compile yourself from sources which are always here).. - don't miss presentation made by Tomas about all MySQL Innovation happening today @Oracle! And if MySQL 5.6 for today is the best MySQL …

Continuent Tungsten offers real-time replication from MySQL to a variety of DBMS types including Vertica. In this Tungsten University webcast we will show you the details of setting up MySQL-to-Vertica replication, including the following topics:

Introduction to Continuent Tungsten features for data warehouse loading Installation for MySQL to Vertica replication Best practices for

The May Boston MySQL User Group featured John Hugg of VoltDB talking about determinism and databases. I have uploaded the hour-long video to http://www.youtube.com/watch?v=mTDLyRauJtw. Seasoned MySQLers will nod their heads because the problems described are familiar, and those who are not exactly sure what “determinism and databases” means will learn a lot.

Enjoy!

(As always, videos are free on YouTube with no login or attempt to solicit your e-mail address or any other information)

A while ago (November 2012 to be exact), we upgraded the support.mozilla.org databases from Percona 5.1 to MariaDB 5.5 (the next step, happening soon, is upgrading them to Oracle’s MySQL 5.6). One of the engineers and I had a conversation where he mentioned that “one of our worst performing views on SUMO is doing waaaayyy better with the upgraded databases”, that it “seems more stable” and that “I stopped receiving ‘MySQL went away or disconnected emails’ which came in once in a while.”

It’s always nice to see upgrades actually making a difference. In our case we saw a lot less CPU wait, though that might also be partially due to tuning the memory settings on the machines and adding in another read slave to handle queries. As a result, network traffic throughput went from less than 1 Mb/sec to about 18 Mb/sec, because the machines were just handling more queries per second, period.

(I had this e-mail as a draft …

I got a question a while ago that I thought was quite simple, but turned into an interesting discussion: How much hard disk space does a database take up?

As it happens, there's a simple answer and a much, much more involved (yet ill-defined) answer, and which one you choose depends on what you think goes into a database and how very, very pedantic you are.

The Simple Answer

You can query the size of table and index data using Information Schema or SHOW TABLE STATUS. The columns DATA_LENGTH and INDEX_LENGTH contain the number of bytes for the table and index data respectively. You can construct simple queries based on that data, or do a quick search on the web for more interesting queries (For example, Peter Zaitsev of Percona wrote a post …