Hi, Zardosht! Thanks for your feedback! I'm looking at the storage engine API from the other side, but my opinion is mostly the same as yours. Current API works, but it could be simplified/clarified in few places. There are part of it that I needed to explain many times. And I was saying, like, "it's historically so, just accept the way it is" or something along these lines. In particular, external_lock/start_stmt - that's, perhaps, the worst part in this regard. There's unnecessary boilerplate code - lines that every engine has to have, and they just copy it from each other. Even if minor, like incrementing status variables, setting auto-increment and timestamp columns, table->status, SHARE's. This is getting fixed though. I don't like the fact that some methods assume that char *buf is table->record[0]. I'd rather fix it (remove either the assumption or the method's argument). It never occurred to me that one can be confuse TABLE::fields and TABLE_SHARE::fields, but now I see that you're right. TABLE_SHARE should have kind of a Field_share object, while TABLE - Field_instance. Currently Field object inclused both, so the shared part is unnecessary duplicated and the "instance" part is unused in the TABLE_SHARE::fields. Come to think of it, almost everything I had to explain about storage engine API needs fixing :) Good parts of the API just work, there's no need to explain them. So, thanks for asking - it highlights problems! Besides, we're trying to add more features too. Like engine-specific attributes in the CREATE TABLE or the new table discovery API. Regards, Sergei On Aug 16, Zardosht Kasheff wrote:

I've worked on the TokuDB storage engine for quite a while now. I have had many experiences over the years, so I guess it's hard to know where to begin. I guess I will start small, and if the conversation evolves, I can contribute more thoughts. I think the current API is really good, as evidenced by the fact that many storage engines have used it to plug into MySQL. The two areas that I see we can really benefit from are the following: - documentation of the API, discussing clearly what the contracts for the functions are. There are still several functions I am not fully clear on, but am glad that they "just work". This includes documentation on the locking mechanisms. For instance, clear documentation on what external_lock, store_lock, and other related functions do would be great. - general API cleanup. In too many places, the functions just assume things, such as the buffer passed into write_row is the same as table->record[0]. In other instances, the well defined way to answer certain questions is not obvious. For instance: - If I want information on the fields, do I use the table object or table_share? - There seem to be several ways to determine if a field is NULL - There is redundant information in the KEY* structure and the fields that make up the KEY. I recall when working on MySQL 5.1, this information was inconsistent and it led to bugs, such as MySQL bug 37292 - I am sure there are plenty of other instances of this that I can find. - Transactions. The API seems to not be designed with transactions taken into account. Transactions are started in some cases of external_lock, or in start_stmt. Transactions are committed either in the handlerton's commit function or in some cases in external_lock. A clear API that takes transactions into consideration would be quite helpful.

Those are my early thoughts. I am happy to share more if people are interested.

I think PBXT is the best example till now that the SE API actually works. Paul, who created it, also was not grown in MySQL but he managed to figure out good enough to write a complete transactional general-purpose storage engine. It did not quite reach GA level. But I don't think the API is to blame. I think Paul needed to turn his efforts towards activities earning him a living (and maybe also he realized that after Oracle's aquisition of Sun that there were no chances that PBXT would be aquired). But that does not exclude that better and more detailed docs as well as 'guides' would be useful. -- Peter Laursen On Mon, Aug 19, 2013 at 5:36 PM, MARK CALLAGHAN wrote:

Thanks for your response.

On Fri, Aug 16, 2013 at 11:23 AM, Zardosht Kasheff wrote:

...

I've worked on the TokuDB storage engine for quite a while now. I have had many experiences over the years, so I guess it's hard to know where to begin. I guess I will start small, and if the conversation evolves, I can contribute more thoughts. I think the current API is really good, as evidenced by the fact that many storage engines have used it to plug into MySQL. The two areas that I see we can really benefit from are the following:

Many were written in the long-ago past. Besides TokuDB how many new storage engines have reached GA in the past decade? I worked on a custom storage engine and I am sure others have done the same, but there hasn't been much innovation in the public. Aria is also GA, but that was written by people who know and wrote parts of the API, so it isn't a sign that the API is something people want to use.

Was TokuMX easier to implement than TokuDB?

-- Mark Callaghan mdcallag@gmail.com

_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp

On 08/19/2013 07:43 PM, MARK CALLAGHAN wrote:

Welcome back to the MySQL community Jan. It is good to see SkySQL/MariaDB get more InnoDB expertise.

Thanks, I have really enjoyed back on MySQL community. Many things are changed but many are as they were when I was moved to develop other databases by IBM. R: Jan

Were I interested in writing a loosely coupled storage engine for MySQL/MariaDB I like the API and docs available for PG and SQLite. We don't have docs like that and the API was much more difficult back in the day when I supported something that did this: http://www.postgresql.org/docs/9.3/static/fdwhandler.html http://www.postgresql.org/docs/9.3/static/fdw-callbacks.html http://www.sqlite.org/vtab.html On Fri, Sep 6, 2013 at 12:10 PM, Thomas Jones-Low < thomas.jones-low@foundationdb.com> wrote:

On 8/13/13 6:41 PM, MARK CALLAGHAN wrote:

...

Do we need a better API or is the current code good enough? I hope to hear from people who have done serious storage engine work.

I'm another person who did a storage engine implementation for MySQL. The Akiban adapter for MySQL did make it to GA, but was not widely adopted.

Our frustrations with the existing API were it's inconsistencies. Specific aggravations were:

- The Create Table data didn't include the anything not supported by the MyISAM engine. Which meant digging through the Lex structure to find Foreign Keys declarations. This added a whole layer of complexity to the create and alter table processing.

- Data type access. As part of our engine, we ended up translating the MySQL data and Datatypes to our own internal one. But the interface on the types was so inconsistent, we couldn't make one call to the type to extract/insert the data for each type.

- No Access to the Optimizer. The Akiban engine was doing some interesting things with data layout to improve join performance. We had no way of hinting to the Optimizer to prefer x,y join over y,z join.

- In addition to the begin/end transaction, we were looking for begin and end of a statement. We were doing this because the data for, what looked to MySQL as multiple tables, query may be loaded in one large chunk. Access to the list of tables used in a single query was not available easily, and the definitive start and end processing of a query to know when to stop our processing.

- Returning errors is inconsistent and dependent upon the MyISAM engine assumptions of what could and could not error. So we would have problems notifying the user that their query had failed

Many of the other oddities and badness we also encountered and found a work around for.

______________________________**_________________ Mailing list: https://launchpad.net/~maria-**developershttps://launchpad.net/~maria-developers Post to : maria-developers@lists.**launchpad.net Unsubscribe : https://launchpad.net/~maria-**developershttps://launchpad.net/~maria-developers More help : https://help.launchpad.net/**ListHelphttps://help.launchpad.net/ListHelp

-- Mark Callaghan mdcallag@gmail.com