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 table [...]
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 report):
SELECT innodb_read_hit_percent [...]
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 reason [...]
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 means all tables and indexes, [...]
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 of the [...]
This is a short note that the MySQL User Group Meetings in Israel are established (well, re-established after a very long period).
Thanks to Eddy Resnick from Sun Microsystems Israel who has set up the meetings. So far, we’ve had 2 successful meetings, and we intend to have more! First one was in Sun’s offices in [...]
In Part I of this article, we have seen how the internal structure of the storage engine’s index can affect an execution plan. We’ve seen that some plans are inherent to the way engines are implemented.
We wish to present a second scenario in which execution plans vary for different storage engines. Again, we will consider [...]
A popping question is: “Can an execution plan change for different storage engines?”
The answer is “Yes”. I will present two such cases, where the MySQL optimizer will choose different execution plans, based on our choice of storage engine.
We will consider MyISAM and InnoDB, the two most popular engines. The two differ in many respects, and [...]