Congratulations to the SUSE team, for the release of SUSE Linux Enterprise 12. It comes by default with MariaDB 10.0.11, and you can find out more information in the release notes (in particular MariaDB replaces MySQL). Let us know if you’re using this, even on systems like POWER – we love to hear from our users!
Let me start with a little story. You sit in your house near the fireplace in the living room and need a book from the library… Eh, no, sorry, wrong century. You’re building a robotic arm that will open your beer or brew your coffee or supply you with whatever other drinks of your choice… while you’ll be building the next robotic arm. So, you — soldering iron in one hand and Arduino in another — ask your little brother to bring a box with specific resistors (that you unexpectedly run out of) from the cellar. The problem — your brother is small and cannot tell a resistor from a respirator. You explain that it’s small thing with two wires sticking out of it. And he starts going back and forth brining you boxes after boxes of different small things with two wires.
This is approximately where we were in MySQL when NDB Cluster was just added. The use wants to find a row, say
WHERE number_of_wires=2 AND size='small' AND type='resistor' and ohm=10000. And the optimizer devises an execution plan to use the “ref” access for the
number_of_wires column. And NDB Cluster engine starts retrieving all rows that have
number_or_wires=2 from all nodes and send all these rows over the network to the server only to see them rejected over and over, because they don’t satisfy the rest of the
Thus, you see, originally engine condition pushdown was implemented for NDB Cluster. With engine condition pushdown the server can send the complete
WHERE clause to NDB, and the latter will send it to all the nodes, so only rows that actually satisfy the complete WHERE clause will be sent over the network back to the server. If the WHERE clause is too complex for nodes to understand (for example, it involves a stored function) the NDB can push only a part of it and let the server enforce the rest.
It wasn’t all perfect, though. If engine condition pushdown is used the server needs to prepare a condition for pushing. In a join it means extracting parts of the WHERE clause that don’t depend on data from tables which weren’t read yet. It creates a sizable overhead for every query even if it doesn’t use NDB Cluster. And most queries don’t. So, engine condition pushdown was disabled by default.
Fast forward to MariaDB. MariaDB doesn’t have NDB Cluster, but it has many other storage engines. For some of them, for example, for SphinxSE, engine condition pushdown is not just a useful optimization, but a requirement, SphinxSE simply cannot function properly if engine condition pushdown is disabled. We’ve solved it in MariaDB 5.2 by implementing an override — the engine could activate engine condition pushdown for its tables, even if it would be disabled for all other tables. No overhead for queries that don’t use SphinxSE. Later other engines started to use this override too.
That’s how we ended up in a strange situation, engine condition pushdown is disabled globally by default, but de facto all engines that can use engine condition pushdown, use it anyway, via this override. And if a user enables engine condition pushdown, it adds no additional optimizations or functionality, but only an overhead of the server preparing pushdown conditions for engines that cannot use them.
This is why in MariaDB 10.1.1 we deprecate
engine_condition_pushdown flag. You can still write
but it will not do anything besides printing a deprecation warning. Starting from MariaDB 10.1.1 engine condition pushdown is always enabled for engines that support it and always disabled for engines that do not support it.
I don’t think it’ll surprise anybody if I say that MariaDB or MySQL server knows a lot more about server system variables, then just their values. Indeed, every variable can be session or global only, read-only or writable, it has an associated help text (that is printed on
mysqld --help --verbose), certain variables only accept values from a given set of strings (this set of allowed values is also printed in
mysqld --help --verbose since MariaDB 10.1.0), numeric variables have lower and upper range boundaries of valid values (that are never printed anywhere), and so on. I always thought it’s kind of a waste that there is no way to query this information. That could’ve been very convenient, in particular for various GUI clients — they could show the help in tooltips, validate values and so on.
But recently we’ve got our users asking for it — precisely, for system variable metadata, whether a variable is read-only, how it was set and so on. You ask for it — you got it. Let me introduce the
INFORMATION_SCHEMA.SYSTEM_VARIABLES table. For every system variable it shows both its global and session values, where the global value comes from (config file or a command line, assigned from SQL, auto-configured by the server, compile-time default), the type and the scope of the variable, its default value, range of values for numeric variables, set of allowed values for ENUM/SET variables, whether a variable is read-only, whether it can be set from the command line. And a help text, of course.
It is said that a picture is worth a thousand words, so here you are:
MariaDB [test]> SELECT * FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME='JOIN_BUFFER_SIZE'\G
*************************** 1. row *****************************
VARIABLE_TYPE: BIGINT UNSIGNED
VARIABLE_COMMENT: The size of the buffer that is used for joins
1 row in set (0.01 sec)
MariaDB [test]> SELECT * FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME='PLUGIN_MATURITY'\G
*************************** 1. row ***************************
VARIABLE_COMMENT: The lowest desirable plugin maturity. Plugins less mature than that will not be installed or loaded
1 row in set (0.01 sec)
READ_ONLY only means “cannot be changed run-time”, the variable might still be writable from the command line or a config file. Here’s the list of all truly read-only variables that can not be modified at all:
MariaDB [test]> SELECT VARIABLE_NAME,VARIABLE_TYPE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE READ_ONLY='YES' AND COMMAND_LINE_ARGUMENT IS NULL ORDER BY 1;
| VARIABLE_NAME | VARIABLE_TYPE |
| ARIA_USED_FOR_TEMP_TABLES | BOOLEAN |
| CHARACTER_SET_SYSTEM | ENUM |
| ERROR_COUNT | BIGINT UNSIGNED |
| EXTERNAL_USER | VARCHAR |
| GTID_BINLOG_POS | VARCHAR |
| GTID_CURRENT_POS | VARCHAR |
| HAVE_COMPRESS | VARCHAR |
| HAVE_CRYPT | VARCHAR |
| HAVE_DYNAMIC_LOADING | VARCHAR |
| HAVE_GEOMETRY | VARCHAR |
| HAVE_OPENSSL | VARCHAR |
| HAVE_PROFILING | VARCHAR |
| HAVE_QUERY_CACHE | VARCHAR |
| HAVE_RTREE_KEYS | VARCHAR |
| HAVE_SSL | VARCHAR |
| HAVE_SYMLINK | VARCHAR |
| HOSTNAME | VARCHAR |
| IN_TRANSACTION | BIGINT UNSIGNED |
| LARGE_FILES_SUPPORT | BOOLEAN |
| LARGE_PAGE_SIZE | INT UNSIGNED |
| LAST_GTID | VARCHAR |
| LICENSE | VARCHAR |
| LOCKED_IN_MEMORY | BOOLEAN |
| LOG_BIN | BOOLEAN |
| LOWER_CASE_FILE_SYSTEM | BOOLEAN |
| PROTOCOL_VERSION | INT UNSIGNED |
| PROXY_USER | VARCHAR |
| SKIP_EXTERNAL_LOCKING | BOOLEAN |
| SYSTEM_TIME_ZONE | VARCHAR |
| VERSION | VARCHAR |
| VERSION_COMMENT | VARCHAR |
| VERSION_COMPILE_MACHINE | VARCHAR |
| VERSION_COMPILE_OS | VARCHAR |
| VERSION_MALLOC_LIBRARY | VARCHAR |
| WARNING_COUNT | BIGINT UNSIGNED |
35 rows in set (0.06 sec)
You might wonder what
LOG_BIN is doing in this list. It’s because on the command line you can never specify this boolean read-only variable, what you actually specify is a command-line option
--log-bin that takes a string. It happens that this new
SYSTEM_VARIABLES table is good at highlighting dark corners in the MariaDB/MySQL internal implementation of system variables.