With this lab release, InnoDB now has its own Fulltext Search (FTS) support. People might wonder how different is this FTS comparing with that of MyISAM. From the usage standpoint, they seem very similar with no apparent difference. However, in the underlying FTS engine, the difference is huge, since two are built on different storage engines with totally different design. So in some aspect such as DML, it might be difficult to make it an apple to apple comparison. However, we do try to list and explain the difference, so to make it easier for user to understand our design and make the transition to use InnoDB FTS easier.

The Design:

The most significant difference is the design. Like most transaction storage engine, InnoDB stores the inverted index in auxiliary relational tables. All queries and DMLs eventually route to these tables. On the other hand, MyISAM stores the inverted list in a two-level B-Tree. The …

We were happy to see a lot of folks from Japan on Twitter this weekend having a discussion about MySQL and Tokutek. While we always endeavor to explain ourselves as simply as possible, hearing what users and peers have to say and ask in their native language is very helpful. Here is a sampling of several of the 30+ tweets and re-tweets (translations courtesy of a colleague I know from frequent past visits to Tokyo and Yokohama):

.

First, @frsyuki provided a general overview:

“TokuDB” 新種のMySQLストレージエンジン。INSERTが20〜80倍ほど速い、パーティションなしで数TBのデータを突っ込める、MVCCサポートなど。Fractal Treeというアルゴリズムを実装しているらしい。http://www.tokutek.com/

…

One of the features found in the summer 2011 labs release is the ability to select the InnoDB page size without recompiling.  Smaller page sizes may be useful for certain storage media such as SSDs where there is no need to minimize seek time between reads.

A new global setting called innodb-page-size can be set to 4k, 8k or 16k before creating a new MySQL instance. This sets the page size for all tablespaces used by that InnoDB instance.   This can be done in my.cnf or on the mysqld command line.  It is a read-only variable while the engine is running since it must be set before InnoDB starts up and creates a new system tablespace.  That happens when InnoDB does not find ibdata1 in the data directory.  If your system tablespace already exists using one page size and innodb-page-size is something else, the engine will not start.

A few bugs were found and fixed related to smaller page sizes in InnoDB …

InnoDB has an internal file system management module that primarily manages the space in the data files. One of the pain points was the coarse level of locking used when a data file has to be extended. More about this issue can be found here. In the latest labs release we have fixed this problem.
When we need to extend a data file inside InnoDB we write zero filled pages synchronously to the file. The user thread which is extending the data file holds fil_system::mutex during the whole operation. This mutex covers changes to all data structures related to file system management. What this means is that when we do a regular IO we do need to acquire fil_system::mutex though only for a short time. Because the thread doing the data file extension is holding the mutex during the whole IO operation any other thread (user or background) trying to access data file for regular read or …

The primary database architectures—shared-disk and shared-nothing—each have their advantages. Shared-disk has functional advantages such as high-availability, elasticity, ease of set-up and maintenance, eliminates partitioning/sharding, eliminates master-slave, etc. The shared-nothing advantages are better performance and lower costs. What if you could offer a database that is a hybrid of the two; one that offers the advantages of both. This sounds too good to be true, but it is fact what ScaleDB has done.
The underlying architecture is shared-disk, but in many situations it can operate like shared-nothing. You see the problems with shared-disk arise from the messaging necessary to (a) ship data among nodes and storage; and (b) synchronize the nodes in the cluster. The trick is to move the messaging outside of the transaction so it doesn’t impact performance. The way to achieve that is to exploit locality. Let …

Yes, we are reintroducing random readahead. We took the random readahead code out after our internal benchmarking and community feedback showed that random readahead tends to adversely affect the performance. However, some recent feedback from customers have shown that for some specific workloads random readahead can be good. Based on this information we have decided to reintroduce random readahead albeit with a dynamic switch to turn it on or off. By default, random readahead is not enabled. You can turn it on by setting innodb_random_read_ahead = TRUE.

The term ‘random readahead’ is a bit of an oxymoron. Readahead seems more related to sequential access pattern. To clarify this I’ll try to explain how InnoDB internally triggers a readahead request. Data pages in InnoDB are laid out on the disk in chunks of 64 pages called extents. The decision about readahead is essentially about whether it makes sense to read in the entire extent …

We have released SQLyog 9.2 GA with enhancements to the Data Search feature (and more).

Since we introduced the Data Search feature in SQLyog 9.1 we had a lot of positive responses – but also a few critical remarks that using the feature could cause unnecessary server load as well as unnecessarily long response times. Such scenario could arise when searching for a string in a BLOB column used for storing media files for instance. Or when searching all tables of a database when there was only need to search a few tables.

We have addressed this concern by adding filter options that let the user restrict the search to specific data types and to a subset of databases of a server, a subset of tables of a database or a subset of columns of a table.

Also we have added “+” and …

There are several types of indexes are available in MySQL: B-Tree Indexes: Normal Indexes – Normal indexes are the most basic indexes, and have no restraints such as uniqueness. It can contain duplicate value. Unique Indexes – Unique indexes are the same as “Normal” indexes with one difference: all values of the indexed column(s) must [...]

Today, SkySQL is announcing a new partnership with Yoshinori Matsunobu, an Oracle ACE Director and the creator of the newly announced MySQL Master High Availability Manager and Tools (MySQL MHA) for automating MySQL master failover. With this partnership, SkySQL will be the first MySQL database commercial services company to provide technical support, professional services, and training, for MySQL MHA.

“For the average organization, the difficulties of master failover are some of the biggest challenges of deploying a MySQL database,” said Yoshinori Matsunobu, creator of the MySQL MHA. “It’s a critical issue for any organization deploying a MySQL database, so the decision to choose a provider of professional services for the tool was not taken lightly. We believe that SkySQL, with its depth and breadth of MySQL database expertise, is in the best position to live up to expectations in supporting MySQL MHA. We are excited to work …

Today, SkySQL announced a partnership with Yoshinori Matsunobu, an Oracle ACE Director and the creator of the newly announced MySQL Master High Availability Manager and Tools (MySQL MHA) for automating MySQL master failover. With this partnership, SkySQL will be the first MySQL database commercial services company to provide technical support, professional services, and training, for MySQL MHA.

read more