An overview of the Falcon engine in mySQL

Original author: Vadim
  • Transfer
Despite the fact that back in mid-June 2008, Jim Starkey (at that time the head of the team working on the Falcon engine) left the project , and in general on very vague prospects for using this engine in future versions of mySQL, I would like to share what the developers wanted to implement in it.

So, some of the features of Falcon (hereinafter free translation):
  • Lack of spinlocks. Falcon does not use spinlocks. It uses its own lock mechanism, which forces the operating system to go into standby mode if the resource is unavailable. Let's see what this will lead to, but I believe that under OS conditions with support for several processors, for some types of locks, when there can be several conflicts, it will be necessary to use spinlocks. Now even several laptops are already installed on laptops, so you can forget about the situation when spinlock wastes CPU time on a single CPU without any practical benefits. At the same time, spinlocks need to be used wisely.
  • Caching strings. The fact that Falcon allows you to cache individual rows of tables, because this means that in the same amount of RAM you can save more data. You may need to cache only one line from a page of 8-16 KB in size, but page-level caching forces you to save the entire page; that is, if you need to cache 1000 100-byte lines, each located on a new page, in the cache, then with Falcon you only need to allocate 100 KB instead of 8-16 MB. In practice, there are special techniques that allow you to optimize page caching, so in reality the gain will be slightly less.
  • Instead of a fixed cache volume, a range. Minor change, besides ambiguous. Vryat you can see that Falcon uses the minimum or maximum limits of the amount of memory allocated for the cache, and not some fixed value. If I have 8 GB of RAM, and I decided to allocate 4 GB for the cache, then what do I care how mySQL will manage these gigabytes? And what range in this case should be set so that caching is as efficient as possible - 3 GB or maybe 3.8 GB? What is the difference? Will memory cleaning not start too often if I set the minimum and maximum values ​​close to each other? The documentation on this subject says nothing concrete.
  • A small number of settings.Perhaps this is somewhat contrary to the previous paragraph, but let's look at it from the point of view of the experimenter, and not the average user. I would like to have more settings to customize the database behavior to my needs. I love the principle “does not require administration”, and I would like the database to do everything for me, but first it must prove that it can do it better than me, and this can only be achieved by comparing the system performance on two configurations: automatic and the one that I made myself with my own hands. So far, I have not seen automatic configurations that could suit all server load modes - they can do fine with “typical” modes, but be ineffective when working in non-standard modes, which happens quite often in the case of mySQL.
  • Small indices. Indices that take up little space are great. Huge indexes, on the other hand, are the key to InnoDB's high performance. Therefore, in fact, you will need to see how “small” the indices will turn out and how much the system performance will suffer (or how much it will benefit). In the release on which we tested Falcon, the SHOW TABLE STATUS command does not show the size of the index, so it is difficult to evaluate this innovation.
  • Missing USING INDEX command. Falcon is always forced to view the contents of a row, even if the selection retrieves only the data contained in the index. I think this is a huge problem, because “Covering indexes” (covering index) can significantly improve the performance of many queries.
  • Orderly reading of data. In contrast to other engines that read data in accordance with index passes, Falcon can optimize this process by going through the entire index first (and maybe by combining the indexes), and only then reading the ordered data. This can help a lot if you have to select a large amount of data from a table. However, I believe that such a possibility should be made higher than the level of the mySQL engines (Storage Engine level) - reading lines in the order of their physical location can be implemented for most types of engines, and certainly for MyISAM and InnoDB. I remember that such plans sounded some time ago, but I do not know if anything is being done in this direction.
  • Lack of support for index clustering.Unlike InnoDB, Falcon does not group data by either Primary index or any other index. In some cases, this is good, in some - bad. Many existing applications rely on this feature of InnoDB, so this feature is more likely to the negative side of the new engine. In my opinion, this feature should be made optional (for example, as it has already been done in InnoDB - you can create a hidden Primary key), however, this feature would be poorly portable, as You would have to delete the Primary key, etc. Some presentations claim that Falcon does not need to index clustering, as uses special optimization when reading them. However, this is not the case. If you need to read a small part of the Primary key in InnoDB, then mySQL will only view a few pages, while Falcon (in case the lines were added at different times and “scattered” by index) will need to read significantly more pages. For example, think about typical cases of clustering in InnoDB - user mailboxes in a table with a clustered key (user_id, message_id).
  • Line compression Falcon implements a mechanism for fast data compression in rows; for example, it uses exactly as many bytes for an integer value as required (and not how many are allotted for the maximum), does not store the value in the column if it is equal to the default value, etc. This is a great innovation, although it can cause some difficulties: for example, if you want to change the default value of a column, then Falcon will have to rebuild the entire table, and not just change its meta-data (however, this feature is also not implemented in mySQL). It will be very interesting to look at Falcon's relative performance-to-compression ratio relative to the transparent gzip compression implemented in InnoDB.
  • Database level tablespace. As you may have noticed in the current release, Falcon creates a separate tablespace and its own logs for each database. With the standard mySQL approach with creating a separate directory for each table, this can cause certain difficulties: if a transaction generates several databases, then when it is committed (i.e. when commit), you will need to update several logs. In addition, to synchronize transaction fixes in several databases, you will either need to use XA transactions(which is very resource-intensive), or a situation may arise when a transaction was committed in one database, but not in another. Another problem is that sequential “snapshots” of the server will be sequential only within the database, so if you start a transaction that accesses database A, and a little later to database B, then in database B you will see lines that were Added / updated after the transaction has started.
  • Isolation modes.Falcon currently supports a very limited set of query isolation (including it does not support SELECT FOR UPDATE queries). The Falcon engine uses an Optimistic lock model with competitive access, which can lead to problems in a server working with a large number of UPDATE queries that will have to wait until the lock is released at the row level. In addition, a funny situation now arises when the engine is waiting for the transaction to complete with an UPDATE query, after which it still crashes with the error "ERROR 1020 (HY000): Record has changed since last read in table 't'” (“The record was changed since the last read of the table "). This is not to say that this behavior of the engine is worse than in the case of InnoDB, it is different, and this must be taken into account when developing applications based on Falcon.
  • There is no protection against partial writing to the page. This means that if the entry in this page was not atomic, i.e. if some part of the page changes, another part of it remains old, then the table may be irreparably damaged. That is why InnoDB uses a double-write buffer ( note: InnoDB before writing pages to a file first writes them to an adjacent section of the table area, so after a failure during recovery InnoDB can find a copy of the data in the adjacent section) Jim does not consider this a significant problem, although I have come across it more than once when using the InnoDB engine before introducing a double-write buffer into it. Talking with the developers of PostgreSQL and Oracle, I came to the conclusion that they are all aware of this problem and have their own methods of dealing with it, so I don’t understand why Jim doesn’t believe in it too much.
  • Data on the disk changes only after the transaction is committed. The InnoDB engine changes the data in files immediately after such a command was issued, Falcon, on the other hand, only after commit. This moment has both positive aspects (for example, the absence of long rollbacks for large transactions) and negative aspects (increased requirements for the amount of RAM). I agree that most transactions are small, but in the case of, for example, batch processing, you may need to either increase the amount of memory or rewrite requests so that transactions are committed more often.
  • Optimization of work with data of type BLOB. Jim loves BLOBs as his own children, so Falcon is optimized for working with them, for example, direct writing of BLOBs to the database is implemented. It will be interesting to see the results of this optimization in reality.

Another interesting point about which I did not find any information in the documentation is fragmentation processing: what happens when updating rows - are the updated rows stored in the same place or in a new one? Are the lines divided into several parts, as happens in MyISAM, or are they always stored compactly? These are very important questions regarding the I / O performance of the engine.

PS from the translator: by now, the engine has been under development for more than 4 years, and the beta is still so crude that Falcon loses in almost all testsnot only to its "progenitor" InnoDB, but also MyISAM. At the same time, the Maria engine is developing much faster (this engine is a development of MyISAM, not InnoDB, although it is completely transactional and has all the ACID properties), so it’s not at all clear why mySQL develops so many engines at the same time that are practically equal to each other opportunities.

Also popular now: