The latest from the MySQL community

Since this is my first blog I feel it's necessary to introduce myself. I'm Tim Callaghan, I work at Tokutek (makers of TokuDB and TokuMX), and I love benchmarking. While some of the content on this blog will certainly be about Tokutek technologies, I plan on exploring a wide variety of others as well. These are strictly my own personal views and opinions, and comments/feedback are always welcome. Lets get started...

A few weeks ago I noticed an EnterpriseDB NoSQL Benchmark that measured Data Load, Insert, Select, and Size. It wasn't just a NoSQL benchmark, it was specifically calling out …

Mon, 2014-10-20 09:31maria-luisaraviol

I am back from 2014 Amsterdam Drupalcon where MariaDB Corporation was present as sponsor. It was my first time there and I must say I was really impressed by the amount of people attending the conference (around 2300 people) and the interest that the people showed for MariaDB.

We had many conversations with several kind of engineers, developers, providers and just for a few of them MariaDB was something new to discover; the great majority of them either were already using it or were planning to do it but they did not manage to find some “free” time to do it yet.

What impressed me, was that almost all of the MariaDB happy users just replaced their previous database server installation (MySQL or Percona) with MariaDB with the same approach they might have had for a standard database server upgrade: switch off, backup, …

Mon, 2014-10-20 09:31maria-luisaraviol

I am back from 2014 Amsterdam Drupalcon where MariaDB Corporation was present as sponsor. It was my first time there and I must say I was really impressed by the amount of people attending the conference (around 2300 people) and the interest that the people showed for MariaDB.

We had many conversations with several kind of engineers, developers, providers and just for a few of them MariaDB was something new to discover; the great majority of them either were already using it or were planning to do it but they did not manage to find some “free” time to do it yet.

What impressed me, was that almost all of the MariaDB happy users just replaced their previous database server installation (MySQL or Percona) with MariaDB with the same approach they might have had for a standard database server upgrade: switch off, backup, …

Yes! In MariaDB 10.1.1 tables in PERFORMANCE_SCHEMA do not use .frm files. These files are not created, not read — in fact, PERFORMANCE_SCHEMA tables never touch the disk at all.

This became possible due to a lesser-known feature of MariaDB — new table discovery (“old table discovery” was implemented in MySQL for NDB Cluster in 2004), implemented in MariaDB 10.0.2. Instead of reading and parsing .frm files, MariaDB simply asks PERFORMANCE_SCHEMA table, what structure it has, and because these tables always have a fixed structure, the table directly returns it to MariaDB with no need for any external data dictionary.

It also means, you never need to upgrade PERFORMANCE_SCHEMA tables, they always have the correct structure corresponding to the MariaDB version …

In our efforts to improve MySQL monitoring, we recently enhanced our fault diagnosis UI. Adaptive Fault Detection has been an integral part of our suite, and we are excited for the UI updates that will help you better manage your databases.

The new release provides a more compact view, allowing you to quickly assess potential problems before they become bigger. Notice how a tiny, tiny server stall was caught by our algorithm. Fault detection has allowed us to get remarkable results from our weak EC2 boxes by keeping them running really cleanly.

We have also added more sections showing metrics such as top processes, network sockets, and network ports. These sections have quick-links to navigate to each of the various tools in the exact time range of the selected fault.

Further, you can click on the zoom buttons on sparklines to …

A very old post of mine in 2009, MySQL’s stored procedure language could be so much more Useful suggested that it would be nice if MySQL could be adapted to use compound statements directly from the command line in a similar way to the language used for stored procedures. I’ve just seen that this seems to … Continue reading MariaDB 10.1 Brings Compound Statements

As you all know MariaDB supported roles since the MariaDB release 10.0.5. They were implemented almost exactly as specified in the SQL Standard 2003, features T331 “Basic roles” and T332 “Extended Roles”.

But we were often hearing complains, users were not satisfied with purely standard set of features. In particular, the standard specified that one had to do

SET ROLE foobar;

to be able to use privileges, granted to the role foobar. This was not always convenient and sometimes not even possible (imagine, you need to grant role privileges to an account used by a closed-source application). There had to be some way to enable a given role automatically, when a user connects.

To solve this issue we have introduced the concept of a default role. A default role for given user is automatically enabled when this user …

Deadlock mechanism is not good enough for product requirement, for example, we hit such case that the UNLOCK TABLE is blocked outside due to WAITING+ACTIVE > overscrible+1. Schedule deadlock must be more philosophical.

Concurrency is not handled well also. For 32 groups, it’s a disaster if lots of BINLOG_DUMP for client/slave. Yes, we also hit this case. Limit special account or special handle in group are both solutions.

With the product experience, I think the essential issue that thread pool must handle is: the low priority queue must be scheduled in case of exception, and there must be a balance, the thread pool behavior and the legacy behavior.

Thread pool feature still needs product environment chasten, and we are enhancing it for our usage. I agree with most of the changes Inaam mentioned in this blog, and of course, looking forward the stable enhancement from Twitter:)

…

Recently came across the problem where mysql server stop running and refusing to start with an error

The server quit without updating PID file (/var/run/mysqld/mysqld.pid)

Checked Mysql error log and found that an invalid usage of charset with collation causing problem.

error log:
141017 12:20:41 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
while 15088 [ERROR] COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'
15088 [ERROR] Aborting

In this case mysqld trying to start  with  character-set-server = latin1 and collation-server = utf8_general_ci, which is not valid.
The following is the right charset value for COLLATION ‘utf8_general_ci’

node1 [localhost] {msandbox} ((none)) > SHOW COLLATION LIKE ‘utf8_general_ci’;

…

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 …