Mark has some good thoughts on drizzle. I think they’re all valid… and have some extra thoughts too:
“I have problems to solve today”. This is (of course) an active concern in my brain… If we don’t have something out that solves some set of problems with reasonable stability and reliability (and soon), then we are failing. I feel we’re getting there, and will have a solid foundation to build upon.
Drizzle replication, MySQL replication: “I can’t compare the two until Drizzle replication is running in production.“. Completely agree. We need to only say replication is stable and reliable when it really is. Realistic test suites are needed. Very defensive programming of the replication system is needed (you want to know when something has gone wrong). We also need to have it constantly be verifying the right thing is going on. We want our problems to be user visible, not silent and invisible. Having high standards will hopefully pay off when people start running it in production….
3 byte int: “Does this mean that some of my tables will grow from 3GB to 4GB on disk?” I think we’re moving the responsibility down to the engines. The 3 byte int type says two things: use less storage space, limit the maximum value. Often you want the former, not the latter. There are many ways to more efficiently pack integers for storage when they are usually smaller than the maximum you want. The protobuf library does a good job of it.
I think it is the job of storage engines to do better here. Once you’re in memory, 3 byte numbers are horrible to work with.. copy out of a row buffer, convert into a 32bit number and then do foo. Modern CPUs favor 32 or 64bit alignment of data a *lot*. 3byte numbers do not align to 32 or 64bits very well… making things much slower for the common case of using cached data.
“I need stored procedures. They are required for high-performance OLTP as they minimize transaction duration for multi-statement transactions.” The reduction of network round trips is always crucial. I think a lot of round trips could go away if you could issue multiple statements at once (not via semicolon separating them, by protocol awesomeness).
There should be a way to send a set of statements that should be executed. There should also be a way to specify that if no error occurred, commit. This could then be (in the common case) a single round trip to the database. You then only have to make round-trips when what statement to issue next depends on the result of a previous one. The next step being to reduce these round trips… which can either be solved by executing something inside the database server (e.g. stored procedures) or something closer to the database server so that the round trips aren’t as large. This would be where Gearman enters.
I’m interested to see where these two approaches (issuing in batches and executing closer to the DB server) fall down… I know that latency may not be as good… but throughput should be a lot better.
I take heart with “I have yet to use them in MySQL” though. I have my own theories as to why this is… my biggest thought is that it’s because the many, many programmers writing SQL that Mark sees aren’t SQL Stored Procedure programmers. They spend their days in a couple of languages (maybe Perl, Python, PHP, Java, C, C++) and never programmed SQL:2003 Stored Procedures and it just doesn’t come as quickly (or as bug free) as writing code in the languages you use every day.
“Long Running insert, update and delete statements consume too many resources in InnoDB.” I wonder if this desire for MyISAM could be filled by PBXT or BlitzDB? The main reason that MyISAM is currently a temporary table only engine is that MyISAM and the server core were never that well separated.
My ultimate wish is that all engine authors take the approach of that there is an API to their engine and the Storage Engine is merely glue between the database server and their API.
The BlitzDB engine has this, Innobase partially does (and my Embedded InnoDB work goes the whole way) and MySQL Cluster is likely the oldest example.
As a side note, the BlitzDB plugin should go into the main Drizzle tree fairly soon. One of the joys of having an optional plugin that doesn’t touch the core of the server is that we can do this without much worry at all.
“Does Drizzle build on Windows?” Well… no. Funnily enough though, it is increasingly easy to make a Windows port. All the platform specific things are increasingly just plugins. The build system is a sticker… and no, we’re not going to switch to CMake. The C stands for something, and it’s something that even I may not print here… (I had never thought that being able to open up automake generated Makefiles and look at them would be a feature).
This next Drizzle milestone release should be exciting though…
I look forward to having Drizzle widely deployed and relied upon… I think we’ll do well..
Wouldn’t it be nice to have MyISAM available as a real storage engine to ease the migration from MySQL to Drizzle? I’d rather not try to switch DB and storage engine at once. But I guess the solution then is to switch to InnoDB while I’m still on MySQL, right?
“Modern CPUs favor 32 or 64bit alignment of data a *lot*”
This is actually not so.
On the contrary, modern CPUs favour data size reduction a lot.
Some time ago I did some experiments with storing data not even aligned to byte boundary, but to arbitrary bit boundary. I found that this was as fast or faster than word alignment, since memory bandwidth was the bottleneck.
One core can execute around 35 instructions in the time it takes to wait for one L2 cache *hit*.
A quad-core can do something like 4-5 instructions per byte the main memory can stream at full speed.
(Results described here: http://kristiannielsen.livejournal.com/4384.html)
Very well said about stored procedures. don’t understand why people and very fanatic about it. it is yet another programing language.
in effect its a application code deployed in database server.
if network overhead is a real botheration, anyone is free to put it in code on the database server itself (any language, including the compiled ones).
Anyway SP need to obey all rules including ACID ones just like any programing language. it is not allowed to do any “Magic”.
Steffen: probably easier to switch beforehand, yeah.
Unfortunately, it was just getting too difficult to maintain all the things in the server just there for MyISAM. You could add it back as a full engine with a bit of work though… I have some ideas on how, just not really a priority.
The “C” in CMake stands for “cross-platform”. You can open CMake generated Makefiles and look at them, they will open even with vi and emacs (nice feature, isn’t it ;). The Makefiles are well readable BTW.
Kristian: that is interesting.
Historically, I’ve always looked at this on non-intel CPUs… so my view has been a little skewed.
My end-game goal is to construct a standard interface to row formats. Then, for the most part, things are kept in whatever row format they originated in. Only at the last possible moment does any conversion happen (e.g. when about to send the row back to the user). Theory being that anywhere while executing a query, if wanting a value of a column, the code will need it in an easily comparable format (i.e. native type). But having the rows in memory, you don’t want to convert things you won’t ever need.
wlad: I’ve found debugging problems that you hit when using CMake to be *very* tricky… with the common answer being hope it’s been fixed in a future Cmake version (or grab the source). Also the lack of a defined programming language (m4 is pretty shocking, but at least it’s documented and an actual thing). Documentation is also lacking (the book is rubbish for anything but the simple stuff).
mind you… if you want to build on non-posix systems (e.g. Win32)… it’s not a bad choice.
I don’t find it any better than autotools, just different… and in some places, worse.
Kristian Nielsen,
Data in your Comparison table looks like, when data Aligned 64-bit words, it shows almost linear scalability against the number of threads.
while packed data is not scaling well with number of threads.
is that so?
Jobin,
Right, now that you mention it I see that the data in the table is presented in a really confusing way :-(
The numbers are time to process 2 billion elements in each thread. So with aligned words, two threads working on twice as many elements take twice the time, meaning no scalability (the reason for this is that memory bandwidth is the bottleneck, so adding threads gives no benefit).
The unaligned in contrast takes about the same time to process twice the amount of data in twice the number of threads, indicating linear scaling. The reason is that the data is smaller, so more elements can be processed with given memory bandwidth before hitting the limit.
Stewart, do not meant to start a flame war on this.
but I would be happy if someone unbiased can compare and contast MySQL’s build with CMake described here
http://forge.mysql.com/wiki/CMake to what you have with drizzle.
I expect the unbiased majority to prefer MySQL build then, I really do:)
@Stewart – we are likely to try the pipelining trick — send all statements at once, commit when there are no failures, rollback otherwise. But to make this work without problems we might close the connection on error. Some code depends on getting back the count of rows modified and I have figured out what to do there.
ahh… so that’s without modifying parts of the client API I guess.
The way I see it is that you can still get back all that information, you just have to say that you want the result and fetch it.
wlad: MySQL’s use of autotools shouldn’t be taken as a good one :)
I have no doubt that the CMake build system is probably better than MySQL’s autotools usage.
… and it really does suit the cross-platform problem…
One thing that may be worth adding to the MySQL CMake docs is how to put in a detection for a specific version of a library (or the presence of a particular function in a library).
Also how to have a plugin that is only built if certain dependencies are met, otherwise it isn’t.
e.g. for Drizzle we have some things (such as auth_pam) that are only built if you have PAM headers around. So, for example, if we were building on Win32, it would automagically not build the impossible.
In Drizzle, we also have (portable) code to put in strict compiler flags for a variety of compilers (and a way to disable it for specific files… e.g. generated ones that you can’t really fix).
Does CMake yet have the nice non-noisy build like new automake does?
yeah , MySQL’s use of autotools is no good, many people have already said so, I completely agree.
Re how to write platform checks I do have a link to Kitware’s Wiki on from MySQL CMake wiki
(wanted to avoid repeating what is already documented somewhere else)
http://forge.mysql.com/wiki/CMake#For_Developers_:_how_to_write_platform_checks
The presence of particilar function in library is detected with CHECK_FUNCTION_EXISTS() , one can use CHECK_SYMBOL_EXIST() when looking for a macro or #define and CHECK_C{XX}_SOURCE_COMPILES when doing unusual things, e.g when functon has different signatures dependend on the OS (gethostbyname_r comes to mind).
Conditional builds:
In MySQL/CMake, we do conditional builds too, for example the ibmdb2 storage engine is only built if a header qlgusr.h is found (must be some i5 specific thing, but not on every box, and I never succeded in finding this header so far:)
The basic usage would be
CHECK_LIBRARY_EXISTS(foo HAVE_LIBFOO)
CHECK_INCLUDE_FILE(foo.h HAVE_FOO_H)
IF(HAVE_LIBFOO AND HAVE_FOO_H)
# do something
ENDIF()
Strict compiler flags
..on a variety of compilers is a great thing in drizzle, and I have not done that yet. This appears to be more political/organizational thing than it is technical. Checking compiler and adding a flag is easy, getting people to fix the warnings (we do have a lot of them shown in pushbuild) is harder. It has to be be a concerted effort, otherwise adding strict flags to something that already does not build cleanly warnings adds nothing but frustration.
Silent builds …
by default was always done CMake. The output is colorized, by default as well, looks nice.
Also, make dependencies between targets are working , by default.
Pingback: Stored Procedures/Functions for Drizzle | Ramblings