Overview of the Falcon engine in mySQL

Despite the fact that back in mid-June 2008, Jim Starkey (at that moment the head of the team working on the Falcon engine) left the project , and generally in 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 features of Falcon (further free translation):

• Lack of spinlock'ov. Falcon does not use spinlock. It uses its own locking mechanism, which causes the operating system to go into standby mode if the resource is not available. Let's see what this will lead to, but I believe that in an OS environment with support for multiple processors with certain types of locks, when there may be several conflicts, it will be necessary to use spinlock. Now, even in laptops, there are several processors installed, so you can forget about the situation where spinlock spends CPU time on a single CPU without practical benefit. At the same time, spinlock should be used wisely.
  
• String caching It can not but rejoice the fact that Falcon allows you to cache individual rows of tables, because This means that more data can be stored in the same amount of RAM. You may need to save to the cache only one line from a page of 8-16 KB, but caching at the page level forces you to save the entire page; that is, if you need to put 1000 100-byte lines in the cache on a new page into the cache, then with Falcon you will need to allocate only 100 KB instead of 8-16 MB. In practice, there are special techniques to optimize page-based caching, so in reality the gain will be somewhat less.
  
• Instead of a fixed cache size - 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 why should I care how mySQL will manage these gigabytes? And what range should be set in this case so that caching is as efficient as possible - 3 GB or maybe 3.8 GB? What is the difference? Will the memory cleaning be started too often if I set the minimum and maximum values ​​close to each other? The documentation on this subject does not say anything concrete.
  
• A small number of settings. Perhaps this is somewhat contrary to the previous point, but let's look at it from the point of view of the experimenter, and not the ordinary user. I would like to have more settings to customize database behavior for my needs. I love the principle of “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 making it possible to compare the system performance in two configurations: automatic and the one that I did myself with my own hands. Until now, I have not seen automatic configurations that could fit all server load modes - they can do a great job with “typical” modes, but be ineffective when working in non-standard modes, which happens quite often in the case of mySQL.
  
• Small indexes. Indexes that take up little space are great. On the other hand, huge indexes are the key to InnoDB high performance. Therefore, in reality, it will be necessary 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.
  
• Absence of the “USING INDEX” command. Falcon is forced to always scan the contents of the string, even if the sample 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.
  
• An orderly reading of data. B differs from other engines that read data in accordance with the passes on the index, Falcon can optimize this process by going through the entire index first (and maybe by combining the indexes), and only then reading the already ordered data. This can help a lot if you have to select a large amount of data from a table. However, I believe that this possibility should be made higher than the level of the mySQL (Storage Engine level) engines - reading the lines in the order their physical arrangement can be implemented for most types of engines, and certainly for MyISAM and InnoDB. I remember that such plans sounded a while ago, but I don’t know if anything is being done in this direction.
  
• No support for clustering index. Unlike InnoDB, Falcon does not group data by Primary index or by any other index. In some cases it is good, in some - it is bad. Many existing applications rely on this feature of InnoDB, so this feature is more likely to be 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), but this possibility would be poorly portable, since You would have to delete the Primary key, etc. Some presentations state that Falcon does not need index clustering, since 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 scan only a few pages, while Falcon (if 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).
  
• String compression Falcon has a fast data compression mechanism for strings; for example, it uses exactly as many bytes for a value of type integer as required (rather than allocated for maximum), does not store values ​​in a column if it is equal to the default value, etc. This is a remarkable innovation, although it may cause some difficulties: for example, if you want to change the default value of a column, Falcon will have to rebuild the entire table, and not just change its metadata (however, this feature is also not implemented in mySQL). It will be very interesting to look at the performance-compression ratio ratio of the Falcon with respect to the transparent gzip compression implemented in InnoDB.
  
• Tablespace database level. 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 the creation of a separate directory for each table, this can cause certain difficulties: if a transaction generates several databases, then when it is committed (that is, when you commit), you will need to update several logs. In addition, to synchronize transaction commit in several databases, you will need to either use XA transactions (which is very resource intensive), or a situation may arise when the transaction was fixed in one database, but not in another. Another problem is that consecutive "snapshots" of the server will be consistent only within the database, so if you start a transaction accessing database A, and a little later at database B, then in database B you will see the lines that were added / updated after the transaction began.
  
• Modes of isolation. Currently Falcon supports a very limited set of query isolations (including does not support SELECT FOR UPDATE queries). The Falcon engine uses an Optimistic model of blocking with competitive access, which can lead to problems in the server operating conditions with a large number of UPDATE requests that will have to wait for the lock to be released at the row level. In addition, there is now a funny situation when the engine waits for the end of a transaction with an UPDATE request, after which it still crashes with an error “ERROR 1020 (HY000): Record has changed since the last reading 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. Documentation on this issue is also not enough.
  
• There is no protection against partial writing to the page. This means that if the entry to this page was not atomic, i.e. when changing any part of the page, another part of it remains old, the table may be irreparably damaged. That is why InnoDB uses a double-entry buffer ( note: InnoDB, before writing pages to a file, first writes them to an adjacent section of the tablespace, so after a crash when restoring, InnoDB will be able to find a copy of the data in an adjacent section ). Jim does not consider this a significant problem, although I did not once encounter it when using the InnoDB engine before introducing a double entry 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 do not really 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 the files immediately after such a command has been given, but the Falcon only after the commit. This moment has both positive sides (for example, the absence of long rollbacks with large transactions) and negative ones (increased requirements to the amount of RAM). I agree that most of the transactions are small, but in the case of, for example, batch processing of something, you may need to either increase the amount of memory or rewrite requests so that the commit transaction occurs more often.
  
• Optimization of work with BLOB data. Jim loves BLOBs as his own children, so Falcon is optimized to work with them, for example, a direct BLOB is written to the database. It will be interesting to look at the results of this optimization in reality.
  

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

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