As I promised in my previous blog posting, I got the opportunity to ask Ronald Bradford some questions.  I hope this serves as an introduction to Ronald, and that you’ll attend Kaleidoscope to learn more from Ronald in person.

Ronald Bradford

MR: Would you like to share some background information about yourself for our ODTUG readers?

RB: “I have worked with various RDBMS technologies for 20+ years.   I was first introduced to Ingres at university and after my Bachelors Degree in 1989. I started my professional IT career performing Ingres database architecture, system design as well as software development.  I worked solidly with …

Tekes, a Finnish government agency funding R&D in Technology and Innovation (including MariaDB) has recently published a book on Finnish startups, (PDF), which contains a whole chapter on MySQL AB.

It seems to be a well researched chapter and references many past interviews over the years, as well as being based on interviews of at least Mårten, Monty and Kevin Harvey of Benchmark. This is the most comprehensive narrative I've ever seen of items like "InnoDB Friday", a phrase I thought until now was company confidential, since talking about it would have revealed there was something negative about the day Oracle …

We just released MySQL Connector/Python 0.1.5 which includes a critical bug fix. It was impossible to read big result sets. The files for 0.1.4-release have been removed.

You can download MySQL Connector/Python from Launchpad.

Highlights:

• It was impossible to retrieve big result sets. (bug lp:551533 and lp:586003)
• Changing copyright from Sun to Oracle (also fixing silly typo)

A very Big Thanks goes to the reporters of bug lp:551533 and lp:586003. Apologies for not being able to reproduce the bug earlier, before releasing 0.1.4.

About MySQL Connector/Python: MySQL …

So this is about a SELECT COUNT(*) FROM tblname without a WHERE clause. MyISAM has an optimisation for that since it maintains a rowcount for each table. InnoDB and PBXT can’t do that (at least not easily) because of their multi-versioned nature… different transactions may see a different number of rows for the table table!

So, it’s kinda known but nevertheless often ignored that this operation on InnoDB is costly in terms of time; what InnoDB has to do to figure out the exact number of rows is scan the primary key and just tally. Of course it’s faster if it doesn’t have to read a lot of the blocks from disk (i.e. smaller dataset or a large enough buffer pool).

I was curious about PBXT’s performance on this, and behold it appears to be quite a bit faster! For a table with 50 million rows, PBXT took about 20 minutes whereas the same table in InnoDB took 30 minutes. Interesting! …

With Paul McCullagh’s PBXT storage engine getting integrated into MariaDB 5.1, it’s never been easier to it out. So we have, on a slave off one of our own production systems which gets lots of inserts from our Zabbix monitoring system.

That’s possibly an ideal usage profile, since PBXT is a log based engine (simplistically stated, it indexes its transaction logs, rather than rewriting data from log into index and indexing that) so it should require less disk I/O than say InnoDB. And that means it should be particularly suited to for instance logging, which have lots of inserts on a sustained basis. Note that for short insert burst you may not see a difference with InnoDB because of caching, but sustain it and then you can notice.

Because PBXT has such different/distinct architecture there’s a lot of learning involved. Together …

I wrote recently about early detection for MySQL performance problems. If your server is having micro-fluctuations in performance, it’s important to know, because very soon they will turn much worse. What can you do about this?

The most important thing is not to guess at what’s happening, but to measure instead. I have seen these problems from DNS, the binary log, failing hardware, the query cache, the table cache, the thread cache, and a variety of InnoDB edge cases. Guessing at the problem is very dangerous; you need diagnostic data. But it is often quite hard to catch a problem in action when you can only observe it in hindsight, and it happens only for a few seconds once or twice a week. This blog post is about how to detect small variations in performance, especially when it is most difficult to observe them. …

I’ve been thinking a lot about invalid and zero temporal values and how to detect them with MySQL date and time functions because mk-table-checksum has to handle “everything” correctly and efficiently. The requirements are complex because we have to take into account what MySQL allows to be stored verses what it allows to be used in certain operations and functions, how it sorts a mix of real and invalid temporal values for MIN() and MAX(), how to detect a temporal value as equivalent to zero, and how different MySQL versions might affect any of the aforementioned.

At base, the four guiding requirements are:

1. Detect and discard invalid time, date, and datetime values
2. Detect zero-equivalent temporal values
3. Do #1 and #2 using only MySQL functions
4. Work in MySQL 4.0 …

From a functional perspective, the core SQL support in all major and minor RDBMS-es is reasonably similar. In this light, it's sometimes quite disturbing to find how some very basic things work so differently across different products. Consider this simple statement:

SELECT  'a' /* this is a comment */ 'b'
FROM    onerow

What should the result be? (You can assume that onerow is an existing table that contains one row)

It turns out popular RDBMS-es mostly disagree with one another.

In Oracle XE, we get this:

SELECT  'a' /* comment */ 'b'
                         *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

PostgreSQL 8.4 also treats it as a syntax error, and thus seems compatible with Oracle's behavior:

ERROR:  syntax error at or near "'b'"
LINE 1: SELECT  'a' /* this is a comment */ 'b'

…

There are a lot of weird things which MySQL does to handle its mix of transactional and non-transactional behaviour, but this one was new to me :)

create table t1 (ID INT NOT NULL PRIMARY KEY, V INT NOT NULL);

Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 (ID,V) VALUES (2,NULL);
ERROR 1048 (23000): Column 'V' cannot be null

mysql> insert into t1 (ID,V) VALUES (3,1),(4,1);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

insert into t1 (ID,V) VALUES (5,1),(6,NULL);
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> show warnings;
+---------+------+---------------------------+
| Level   | Code | Message                   |
+---------+------+---------------------------+
| Warning | 1048 | Column 'V' cannot be null | …

Percona is pleased to officially welcome Justin Swanhart to our team of consultants.

Before joining Percona, Justin worked as a MySQL DBA at Gazillion, Yahoo, and Kickfire. Justin has become a regular contributor here on the MySQL Performance Blog as well as being an active blogger at http://swanhart.livejournal.com/. He is very active in the community, maintaining FlexViews, a stored procedure managed solution for materialized view creation and maintenance in MySQL 5.1 as well as instrumentation-for-php, a suite of PHP classes for easing the implementation of instrumentation in applications.

Justin, a big welcome - we are fortunate indeed you're working with us!

Entry posted by Ryan Lowe | …