As I stated in my last post, I decided to use the Innodb Table monitor to diagnose an Index count mismatch error a customers found in their mysqld.err log to verify if the problem still existed.

The Innodb Table Monitor has existed for basically forever in Innodb (the MySQL manual discusses it back in the 4.1 manual), and is from a time when what was going on inside of Innodb was a lot murkier than it is now.   To use it, you create a table (in any database you choose), like this:

CREATE TABLE innodb_table_monitor (a INT) ENGINE=INNODB;

This, primitively, tells Innodb that it should start the Table monitor.  The monitor …

I had a customer recently who a few strange errors in their mysqld.err log:

[ERROR] Table database_name/table_name contains 8 indexes inside InnoDB, which is different from the number of indexes 7 defined in the MySQL

This customer was running Percona Server 5.1 and they got this error on two tables during a maintenance window when they were adding indexes to the same tables.  We had a suspicion that it had something to do with Fast index creation in Innodb, and that it had been corrected when the ALTER TABLE completed because the errors had not recurred.

Reproducing the error on a test system is simple:

1. create an Innodb table
2. make a copy of the .frm file
3. do an ALTER TABLE to add an index
4. then copy the old .frm file back into place
…

I work at ideeli and wrote up a two parter for our Tech Blog about finding problem queries in MySQL…

Finding Problem Queries, Part 1: The Slow Stuff

…

Monty Program and SkySQL invite you to an open discussion webinar on why MariaDB™ is the alternative and future of the MySQL® database, not only for the User Community, but also for the Enterprise.

Register for this webinar.

When:

Thursday, December 8, 2011

8:00am PT, 11:00am ET, 16:00 UK, 17:00 CET, or 18:00 EET

Agenda & speakers:

• Colin Charles, Chief Evangelist at Monty Program
  • When and why to use MariaDB in a commercial setting
• Rasmus Johansson, COO at Monty Program
  • The offerings of Monty Program Ab and how to interact with us commercially
• Kaj …

One thing I enjoy about this job is being able to meet face to face the with MySQL Community. We have a big community and it is hard to keep up ! Sadly, I will not be able to make the OTN MySQL User Forum in Brazil but I want to encourage the community to attend the event.

It is a free forum, full of technical sessions, taught by engineers and leaders of the MySQL open source community will also explore Oracle's strategy with regard to MySQL, as well as the new MySQL Enterprise innovation and what to expect from MySQL 5.6.


Best of luck to the MySQL Brazil Community.

Over the last year, the frustration of many of us at Percona regarding issues with MMM has grown to a level where we started looking at other ways of achieving higher availability using MySQL replication. One of the weakness of MMM is its communication layer, so instead of reinventing a flat tire, we decided, Baron Schwartz and I, to develop a solution using Pacemaker, a well known and established cluster manager with a bullet proof communication layer. One of the great thing about Pacemaker is its flexibility but flexibility may results in complexity. With the help of people from the Pacemaker community, namely Florian Haas and Raoul Bhatia, I have been able to modify the existing MySQL Pacemaker resource agent in a way that it survived our replication tests and offered a behavior pretty similar to MMM regarding Virtual IP addresses, VIPs, management. We decided to call this solution PRM for …

The MySQL administration SQL command SHOW PROCESSLIST may read "Waiting for table metadata lock" in its "State" column for a statement. The statement in question is waiting for another transaction to release a metadata lock. Its a state that may appear when using the global transaction ID injection feature of PECL/mysqlnd_ms 1.2.0-alpha. But only in case of errors and if not using default settings of the plugin. In the worst case, during testing only, I experienced a deadlock with MySQL 5.5.3 or higher which never got resolved automatically.

Provoking metadata lock

Let a transaction update a record in a table. In my specific case it was an failing UPDATE. It failed because the table did not exist. Let a second transaction run a DDL statement, for example, DROP TABLE on the same table. The second transaction is now in waiting for the first transaction to release a metadata lock. Yes, the …

At ideeli, we use a variety of techniques to eliminate single points of failure and contention. These involve load balancing multiple application servers, caching layers, and calls to asynchronous services. However, at the end of the day we have a single master MySQL database. It doesn’t matter how many dozens of application servers are handling requests if the database is stalled. Without the right tools, it can be difficult to determine exactly what the database is doing at any given time. In this two part post, I will discuss some techniques for finding those problematic queries.

FINDING THE SLOW STUFF

The MySQL slow query log should be the first stop when looking for queries to optimize. Even with tools like mysqldumpslow, the format of the …

A lot of things is happening in the MariaDB world; We just had our developers conference in Athens, we are about to bring out MariaDB 5.3 -RC and MariaDB 5.5-beta. MariaDB 5.5 will include all the closed source extensions that Oracle have announced for MySQL 5.6. It's nice to see that the open source community can continue to produce open source software to rival and exceed closed source offerings.

At the same time, we need commercial goals to support the growth of free software. The development crew in Monty Program Ab, all ex-MySQL developers, are happy to develop features based on commercial agreements. These will of course be released as open source to the benefit of the entire community of users. The support staff of SkySQL is happy to give you commercial 24x7 support of MariaDB, so you have the same level of support …

Happy hacking!

@Ulf_Wendel