I’m evidencing an increase in the planning, confidence & execution for MyISAM to InnoDB migration. How much can a single consultant observe? I agree Oracle should not go to PR based on my experience. But I find that: More companies are now familiar with InnoDB than there used to. More companies are interested in migration [...]
Actually this is (almost) all I wanted to say. This is intentionally posted with all related keywords in title, in the hope that a related search on Google will result with this post on first page. I’m just still encountering companies who use MyISAM as their storage engine and are unaware that their nightly backup [...]
In a period of two weeks, I had two cases with the exact same symptoms. Database users were experiencing low responsiveness. DBAs were seeing locks occurring on seemingly normal tables. In particular, looking at Innotop, it seemed that INSERTs were causing the locks. In both cases, tables were InnoDB. In both cases, there was a [...]
TRUNCATE is usually a fast operation (much faster than DELETE FROM). But sometimes it just hangs; I’ve has several such uncheerful events with InnoDB (Plugin) tables which were extensively written to. The TRUNCATE hanged; nothing else would work; minutes pass. TRUNCATE on tables with no FOREIGN KEYs should act fast: it translate to dropping the [...]
mycheckpoint (see announcement) allows for both graph presentation and quick SQL access to monitored & analyzed data. I’d like to show the power of combining them both. InnoDB performance Taking a look at one of the most important InnoDB metrics: the read hit ratio (we could get the same graph by looking at the HTML [...]
Following Baron Schwartz’ post: How to calculate a good InnoDB log file size, which shows how to make an estimate for the InnoDB log file size, and based on SQL: querying for status difference over time, I’ve written a query to run on MySQL 5.1, which, upon sampling 60 seconds of status, estimates the InnoDB [...]
I find myself converting more and more customers’ databases to InnoDB plugin. In one case, it was a last resort: disk space was running out, and plugin’s compression released 75% space; in another, a slow disk made for IO bottlenecks, and plugin’s improvements & compression alleviated the problem; in yet another, I used the above [...]
I wish to present some compelling reasons to use the InnoDB plugin. The plugin is a drop-in replacement for “normal” InnoDB tables; enabling many new features. It is the outcome of a long termed silence from InnoBase (Oracle), which were thought to be neglecting the InnoDB engine. I’m going to leave out “performance” for the [...]
When working with InnoDB, you have two ways for managing the tablespace storage: Throw everything in one big file (optionally split). Have one file per table. I will discuss the advantages and disadvantages of the two options, and will strive to convince that innodb_file_per_table is preferable. A single tablespace Having everything in one big file [...]
InnoDB and MyISAM use B+ and B trees for indexes (InnoDB also has internal hash index). In both these structures, the depth of the index is an important factor. When looking for an indexed row, a search is made on the index, from root to leaves. Assuming the index is not in memory, the depth [...]