One of my favorite MySQL tools ever is pt-query-digest. It's the tool you use to generate a report of your slow query log (or some other supported sources), and is similar to for example the Query Analyzer in MySQL Enterprise Monitor. Especially in the kind of job I am, where I often just land out of nowhere on a server at the customer site, and need to quickly get an overview of what is going on, this tool is always the first one I run. I will show some examples below.

read more

How InnoDB work with transactions:

When any transaction will be completed with COMMIT,  InnoDB will write those changes in InnoDB Buffer Pool. After that InnoDB will run some background operations like checkpoint.  Checkpoint is the most important operation which will writes the changes on disk.   Lets see how it will work.

During the checkpoint phase, InnoDB writes dirty pages to the double write buffer, and then writes pages from the doublewrite buffer to the actual tablespace. During checkpointing, as pages are flushed to the actual tablespace making the data changes persistent on disk, log_sequence_numbers (LSN) are also updated on the pages. The LSN info written to the page is what identifies whether a data page has current data or not, during the crash recovery phase.

How InnoDB does crash / auto …

Valeriy has mentioned that MySQL manual has no documentation about EXPLAIN FORMAT=JSON feature. There was a bug filed for this since September, 2012. I had spent some time looking at EXPLAIN FORMAT=JSON, so I thought I would share my findings.

The first blog post about EXPLAIN FORMAT=JSON was made by Sheeri. She listed these features of FORMAT=JSON:

• attached_condition and implicit casts
• used_key_parts field

In addition to those, I was able to find

• Pushed index condition information
• More information about sorting/grouping
• Subquery …

"mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK TABLES if the --single-transaction option is not used. Certain options might require other privileges as noted in the option descriptions."
- http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html

It is everywhere in the world of MySQL that if your replication is broken because an event caused a duplicate key or a row was not found and it cannot be updated or deleted, then you can use ‘

STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

 ’ and be done with it. In some cases this is fine and you can repair the offending row or statements later on. But what if the statement is part of a multi-statement transaction? Well, then it becomes more interesting, because skipping the offending statement will cause the whole transaction to be skipped. This is well documented in the manual by the way. So here’s a quick example.

3 rows on the master:

master> select * from t;
+----+-----+
| id | pid |
+----+-----+
|  1 |   1 |
|  2 |   2 |
|  3 |   3 |
+----+-----+
3 rows in set (0.00 …

The Early Bird rate for MySQL Connect has been extended for two weeks. Register by August 2 to save US$500 over the onsite price!

MySQL Connect will be held on September 21 - 23 in San Francisco, California. The conference will start on Saturday with "The State of The Dolphin" keynote presented by Oracle's Chief Corporate Architect, Edward Screven, and Vice President of MySQL Engineering, Tomas Ulin, followed by a panel to discuss "Current MySQL Usage Models and Future Developments", with panelists from renowned web properties including Facebook, Twitter, LinkedIn and Paypal. …

Good afternoon Oscon attendees - in particular attendees of my High Availability in MySQL tutorial!

Attached you can find a spreadsheet (both LibreOffice, Excel) that we will use at the end of the tutorial. You can also download the slides, in case you wish to browse them at your own pace.

In this Log Buffer, you have naan bread, two types of roti, pasta, Karachi biryani, Chiken Tandoori, Beef tikka, eggplant masala, mixed pickles, French pastry with some elixir to wash all of it down. Yes, once again, this edition of Log Buffer contains numerous flavors from Oracle, SQL Server and MySQL.

Oracle:

Sometimes it’s the little differences that make something really cool, and Martin was wondering why this hasn’t made it into the Oracle dictionary before.

Oracle introduced a new feature for DataGuard called Far Sync. This configuration is designed to support synchronous redo transport between primary and standby database, which are far apart.

There are a few …

A lot is said about the differences in the data between MySQL and MongoDB. Things such as “MongoDB is document based”, “MySQL is relational”, “InnoDB has a clustering key”, etc.. Some may wonder how TokuDB, our MySQL storage engine, and TokuMX, our MongoDB product, fit in with these data layouts. I could not find anything describing the differences with a simple google search, so I figured I’d write a post explaining how things compare.

So who are the players here? With MySQL, users are likely familiar with two storage engines: MyISAM, the original default up until …

At OSCON, if you’re into the MySQL world, the day to be paying attention to BoFs is clearly Wednesday, 24 July 2013. We have a MariaDB BoF at D136 at 8pm-9pm, but before that you might be at the MySQL BoF in the same room, or you might be in Peter Zaitsev’s BoF on MySQL, NoSQL, Big Data – Better Together in E141. So figure out how to split yourself at 7-8pm, and once you’re done, come from 8-9pm at D136 on Wednesday and join the MariaDB crew at OSCON!

We’ll also be at the SkySQL booth, so come by Booth 815 and get some free t-shirts, stickers, or support the MariaDB community by being a foundation donor.