MySQL Tech-Feed (en)

What is a sparse file?

“A sparse file is a file where space has been allocated but not actually filled with data. These space is not written to the file system. Instead, brief information about these empty regions is stored, which takes up much less disk space. These regions are only written to disk at their actual size when data is written to them. The file system transparently converts reads from empty sections into blocks filled with zero bytes at runtime.”
[ 1
]

In other words: Files are not …

A friend of me asked me long time ago: “How can I have a ranking on a result with MySQL?”. Now I found some time to write it down:

Lets do first some preparation for the example:

CREATE TABLE sales (
  id     INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, fruit  VARCHAR(32)
, amount DECIMAL
);

INSERT INTO sales
VALUES (NULL, 'apple', 12.75), (NULL, 'orange', 1.89), (NULL, 'pear', 19.23)
     , (NULL, 'banana', 4.25), (NULL, 'cherry', 123.75), (NULL, 'plum', 23.15)
;

Now lets query:

SELECT …

With MySQL 5.0 the database provides trigger functionality on INSERT, REPLACE, UPDATE and DELETE.

Those of you who know some other RDBMS know, that there are also some system events where one would like to have triggers.

Unfortunately MySQL does not (yet) provide such functionality. This is sad because as database administrator this would be sometimes very helpful.

But you can build your own LOGON and STARTUP trigger.

MySQL provides some hooks for these events…

Complete Story (PDF 160 kbyte).

Recently the question came up if it is faster to restore a MySQL cluster when all nodes are up or only ONE node from each node group during restore.

The answer from our gurus was: All nodes up during restore! I wanted to find out why. So I set up the following cluster and started to measure:

MySQL Cluster set up

MySQL Cluster backup

The backup is not that interesting. But I made the drawing for possible future use :-) :

MySQL Cluster restore

For the restore there are 4 different ways thinkable:

• Restore …

What is MyEnv?

MyEnv is a tool to run several MySQL or MariaDB database instances on ONE host. You can even run multiple database instances with different binary versions. We call this multi-instance set-ups.

With MyEnv a multi-instance set-up is more comfortable to handle than with mysqld_multi (old) or Systemd services (new) and it provides more useful functionality.

Using MyEnv does not need the use of the root user to operate MySQL and MariaDB database instances. Thus it is appropriate for classical …

It is possible to use an active - active shared-disk cluster in MySQL in some cases. For doing this you have to fulfill the following requirements:

• Works with MyISAM tables only.
• POSIX-locking compliant cluster file system on the device (such as OCFS2 or GFS).
• External locking must be enabled.
• The MySQL query cache must be turned off.
• The MySQL delay key write must be turned off.
• OS where file locking is supported in MySQL.

Interested? To read more, follow the link: MySQL active - active Cluster (PDF 157 …

Transaction performance relates among other things from I/O performance. This means hard disk performance.

Hard disk performance

When you select a hard disk, an important feature to consider is the performance (speed) of the drive. Hard disks come in a wide range of performance capabilities. As is true of many things, one of the best indicators of a drive’s relative performance is its price. An old saying from the automobile-racing industry is appropriate here: “Speed costs money. How fast do you want …

In a round-robin database (RRD) usually time-series data like network bandwidth, temperatures, CPU load etc. is stored. The data is stored in the way that system storage footprint remains constant over time. This avoids resource expensive purge jobs and reduces complexity.

MySQL does NOT yet provide this kind of storage engine. Although some people were thinking about and some prototypes exists.

Nevertheless in this paper it is shown how you can build your own RRD tables: Round-Robin Database Storage …

The price for a 160 Gbyte disk will be around USD 15'000. This is still a bit expensive. But the access time is around 0.5 ms (both for reading and writing) which is around 10 times faster than a normal 15'000 rpm SCSI disk! The disk has NO cache because it is a cache itself (according to the supplier. Maybe this will change in the future). And the lifetime of a cell is > 5 mio writes. For the same performance one needs usually an array of around 10 disks. If your database is heavily write-I/O bound you …

MySQL provides its replication for High Availability (HA) and for read Scale-out. Generally it is known that in a MySQL replication you can only replicate from one Master to many slaves. In this paper it is shown how a set-up can look like to replicate from two masters to one slave.

Caution: Handle this information with care!!!

Probably the answer to this question is already known. But we want to prove it and by the way learn to deal with MySQL and oprofile.

Materialised View (MV) is the pre-calculated (materialised) result of a query. Unlike a simple VIEW the result of a Materialised View is stored somewhere, generally in a table. Materialised Views are used when immediate response is needed and the query where the Materialised View bases on would take to long to produce a result. Materialised Views have to be refreshed once in a while. It depends on the requirements how often a Materialised View is refreshed and how actual its content is. Basically a …

A Federated Table is a table which points to a table in an other MySQL database instance (mostly on an other server). It can be seen as a view to this remote database table. Other RDBMS have similar concepts for example database links.

Read more about the pittfalls with MySQL Federated Tables…

We have added some more tools to our MySQL consulting tools collection.

We have added some more database benchmarks to our collection.

More details you can find on our Benchmarking page.

One of the big advantages of MySQL is its concept of pluggable Storage Engines (SE). This means you can choose the most optimal storage engine for your needs. This also has a disadvantage: You have to know what you are doing…

More details you can find here: MySQL pluggable Storage Engines (SE).

MySQL has also a Full-Text Search Engine built in. But this Search Engine is not as fast a you probably want to. Thus there are some alternative Full-Text Search Engines which might be working together with MySQL.

• MySQL/MyISAM Full-Text Search
• Sphinx Search
• Lucene
• MnogoSearch
• Solr (based on Lucene)
• Omikron Fact-Finder
• Ioda
• Kneobase (based on Lucene)
• Egothor
• Xapian

More information

• Wikimedia: Fulltext search engines
• Searching for Text Information in Databases

Who uses what Full-Text Search Engine …

Applications occasionally require redesign. However, redesigning an application cannot be done in one step because the application is distributed or several versions of applications must be supported. MySQL 5.0 provides the necessary means to stealthy migrate your data. In a short overview let’s look at what we plan to do: Stealthy Migration (PDF 98.7 kByte).