When we tried to clean up a rather large (4.500.000 rows, 20GB) InnoDB table some days ago, we were astonished by the time MySQL took to complete the task. We had already LIMITed the transaction size, but every single chunk still took minutes to execute. The table itself contains some number columns, including a numeric primary key, and a blob. The delete condition was mainly based on the primary key (being smaller than a predefined value) and status field. After some mails between the support crew and us an optimizer bug was identified: MySQL Bug #28554.

The problem is that in some cases the optimizer makes a bad choice concerning which index to use. It will pick a secondary index that can be used to cover a WHERE indexed_column=<constant> condition, even though it will cause way more data to be scanned than necessary. The primary key for …

As I wrote we had been recovering corrupted Innodb Tablespace and it is finally done now.

As this was over than 1TB worth of data we really tried to avoid dumping the data and find some other way to recovery.
Examining Innodb page content and crash information we figured out it should be page of insert buffer itself, rather than page belonging to some of the pages touched by insert buffer.

If that would be just the table it should have been easy - dropping the table with corrupted page would remove all appropriate insert buffer records and be able just to load that table back. Unfortunately we were not that lucky.

If the page corrupted in insert buffer itself there seems to be no way to make Innodb avoid touching it. Even when we dropped all Innodb tables (for test purposes) Innodb still was crashing if we …

.. is not so easy. I have been traveling for 4 weeks in USA, then this weekend I was at a wedding back in Germany. This weekend I will be trying out for the German national mixed frisbee team at the BUM (I rate my chances of making it pretty low - injured finger, never found a top level team I wanted to play in, which means that all the top level players do not know me that well .. and unfortunately I am also simply not that uber-good .. but hey I can try).

The following weekend I was also planning on going to play frisbee at the HUT in Halle, but since I will be in Norway the following weekend and the Netherlands the weekend after that, I guess I should really skip Halle. So I will try to go out and buy a bed, mattress, table and a cupboard or …

It seems the slides that generated the most interested in my recent "Database meets OOP" talk were the ones on how to model inheritance inside your RDBMS. I wanted to mention two (and a half) additional approaches.

The first one I have no real experience with and is native inheritance support that can be found in Object-Relational DBMS (not to be confused with Object-Oriented DBMS, which are not relational). PostrgreSQL is a shining example of this, so they have native support for inheritance. Actually the PostgreSQL docs mention that since SQL99 inheritance is part of the spec, not sure if anyone has implemented it though. From my reading it looks like its just a convenience feature to spare you from having to writing joins (when fetching data from a child …

It's pretty cool to see link to your blog posted on a .gov domain :)

frankmash.blogspot.com is another one of my blogs where I post sometimes.

SAP Chairman and co-founder Hasso Plattner has been out discussing how SAP is redefining itself and investing in building the next generation of Web 2.0 software for the Enterprise.  SAP has been out doing a few acquisitions in recent months, presumably to accelerate the pace of change.  While SAP has long been a quiet supporter of open source technology and investor in companies like MySQL and Zend,

Plattner is down right enthusiastic about opening up processes to have more community involvement and a more accessible hosted approach.  Plattner cited Google and Salesforce.com as a good models of how to rapidly deliver high quality information to users.  SAP is investing heavily in a new initiative known as A1S, which is a new online hosted offering based on a collaborative service-oriented architecture (SOA).

• CNet: …

Well there hasn't been any actual coding work done yet, this is just the project setup phase. This blog is connected to the Google Summer of Code 2007 Project "MySQL Auditing Software", the project page can be found at this link.

The project team is composed of myself, Umair Mehmood and the project mentor, Sheeri Kritzer. We'll be holding a conference call this Friday on Skype in order to exchange notes and design ideas.

I already have a fair idea of how this software can be designed, based on discussions with the project team members and research done into similar products I believe that a design based on passive packet capture is a good way to go. The resulting captured data can then be interpreted and reformed in whatever way we see fit before being passed on to the database servers.

The consulting load keeps increasing so we’re looking for some help.

This job would be perfect for someone interested in high performance and scaling with decent knowledge of MySQL and eagerness to learn more.

We do encourage people from all countries to apply.

For the intensely curious, these are the final results from DEV, DBA and Cluster certification examinations at the MySQL Conference 2007.

Progress slows this week. Got hung up on a few things.

Since last update:
- Fixed a ton of bugs.
- Got the state machine working. Supports only a subset of commands right now, but easily extended.
- Lua is used to establish listener connections
- Lua callback structure
- Planned lua API in more detail.

After a lot of thought I've come up with a simple lua "interface"


callback = {}

function got_new_client(c)
print "Got a new client! Yay!"
callback[c:id()] = {["Received Command"] = start_new_cmd, ["Waiting for Command"] = end_cmd}
end

function setup_listener(c)
callback[c:id()] = {["New Client"] = got_new_client}
end

myp.new_listener("127.0.0.1", 5500, setup_listener)

etc.

Calling myp.new_listener() is a C function to start a new bind …