Open Source Database

MariaDB 10.1.0 Overview and Highlights

Chris on MySQL - July 19, 2014 - 12:37am GMT

MariaDB 10.1.0 was recently released, and is available for download here:

https://downloads.mariadb.org/mariadb/10.1.0/

This is the first alpha release of MariaDB 10.1, so there are a lot of new changes and functionalities added, which cover a wide variety of areas such as: Performance, InnoDB/XtraDB, WebScaleSQL, Optimizer, Security, Storage Engine functionality, & Administration Improvements.

These are 9 of the most notable changes in MariaDB 10.1.0 (but do check out the release notes and changelogs below for the full list of changes):

  1. InnoDB: Allow > 16K pages on InnoDB – InnoDB now allows page size to be configured as 16K, 32K and 64K. Note that single row size must be < 16K. This feature will allow especially more blob columns to be created.
  2. InnoDB: Force PK option – Added a new dynamic configuration variable innodb_force_primary_key default off. If option is true, create table without primary key or unique key where all keyparts are NOT NULL is not accepted. Instead an error message is printed. Variable value can be changed with set global innodb_force_primary_key = .
  3. Security: Enhance security using special compilation options – MariaDB is now compiled with security hardening options by default. It is an additional protection layer that makes new, yet unknown, security vulnerabilities more difficult to exploit.
  4. Optimizer: ANALYZE $stmt – Check how close the optimizer’s estimates about the query plan are to the reality. (more about this here also)
  5. WebScaleSQL: Added numerous improvements from WebScaleSQL (including MDEV-6314, MDEV-6315, MDEV-6323, MDEV-6325, MDEV-6329, and more).
  6. Performance: Disable Performance Schema by default.
  7. Performance: Merge 10.0.10-FusionIO to 10.1 – Atomic writes, multi-threaded flushing and page compression are available for Fusion-IO devices in 10.1.0.
  8. Storage Engine: Don’t create frm files for temporary tables – Temporary tables no longer create frm files on disk. Which means that if the temporary table is created in the MEMORY engine, it will not touch the disk at all.
  9. Administration: GUI-friendly cmake options to enable/disable plugins – MariaDB now uses PLUGIN_xxx cmake options to enable or disable plugins, not a combination of WITH_xxx, WITHOUT_xxx, WITH_PLUGINX_xxx, WITHOUT_PLUGIN_xxx, WITH_xxx_STORAGE_ENGINE, WITHOUT_xxx_STORAGE_ENGINE.

You can read more about the 10.1.0 release here:

https://mariadb.com/kb/en/mariadb-1010-release-notes/

And if interested, you can review the full list of changes in 10.1.0 (changelogs) here:

https://mariadb.com/kb/en/mariadb-1010-changelog/

Hope this helps.

 

MariaDB Replication, MaxScale and the need for a binlog server

Karlsson on databases and stuff - July 11, 2014 - 6:24pm GMT
IntroductionThis is an introduction to MariaDB Replication and to why we need a binlogs server and what this is. The first part is an introduction to replication basics, and if you know this already, then you want want to skip past the first section or two.
MariaDB ReplicationMySQL and MariaDB has a simple but very effective replication system built into it. The replication system is asynchronous and is based on a pull, instead of a push, system. What this means in short is that the Master keeps track of the DML operations and other things that might change the state of the master database and this is stored in what is called the binlog. The slave on the other hand is responsible for getting the relevant information from the master to keep up to speed. The binlogs consist of a number of files that the master generates, and the traditional way of dealing with slaves is to point them to the master, specifying a starting point in the binlogs consisting of a filename and a position.

When a slave is started it gets the data from the binlogs, one record at the time, from the given position in the master binlogs and in the process updates the current binlogs file and position. So the master keep track of the transactions and the slave follows behind as fast as it can. The slave has two types of threads, the IO thread that gets data from the master and to a separate relay log on the slave, and an SQL thread that applies the data from the relay log to the slave database.
This really is less complicated than it sounds, in a way, but the implementation of it on the other hand is probably more complicated than one might think. There are also some issues with this setup, some which is fixed by the recent GTID implementation in MySQL 5.6 and more significantly in MariaDB 10.

In a simple setup with just 1 master and a few slaves, this is all there is to it: Take a backup from the master and take to keep track of the binlogs position when this is done, then recover this backup to a slave and then set the slave starting position at the position when the backup was executed. Now the slave will catch up with the operations that has happened since the backup was run and eventually it will catch up with the master and then poll for any new events.

One that that is not always the case with all database systems is that the master and the slave are only different in the sense of the configuration. Except this, these servers run the same software and the same operations can be applied to them, so running DML on a slave is no different than on a master, but if there is a collision between some data that was entered manually on the slave and some data the arrives through replication, say there is a duplicate primary key, then replication will stop.
What do we use replication forReplication is typically used for one or more of three purposes:
•    Read Scale-out - In this case the slaves are used for serving data that is read, whereas all writes go to the master. As in most web applications there is a much larger amount of reads than writes, this makes for good scalability and we have fewer writes to be handled by the single master, whereas the many reads can be server by one or more slaves.
•    Backup - Using a slave for backup is usually a pretty good idea. This allows for cold backups even, as if we shut down the slave for backing it up, it will catch up with the master once restarted after the backup is done. Having a full database setup on a slave for backup also means that recovery times of we need to do that, is fast and also allows for partial recovery if necessary.
•    High Availability - As the master and Slave are kept in sync, at least with some delay, one can sure use the slave to fail over to should the master fail. The asynchronous nature of replication does mean though that failover is a bit more complex than one might think. There might be data in the relay slave log that has not yet been applied and might cause issues when the slave is treated as a master. Also, there might be data in the binlogs on the master which means that when the master is again brought on-line, it might be out of sync with the slave: some data that never was picked up by the slave might be on the master and data that entered the system after the slave was switched to a master is not on the old master.
The nature of replicated dataIn the old days, the way replication worked was by just sending any statement that modified data on the master to the slave. This way of working is still available, but over time it was realized that this was a bit difficult, in particular with some storage engines.

This led to the introduction of Row-based Replication (RBR) where the data to be replicated is transferred not as a SQL statement but as a binary representation of the data to be modified.

Replicating the SQL statement is called Statement Based Replication (SBR). An example of a statement that can cause issues when using SBR is:
DELETE FROM test.tab1 WHERE id > 10 LIMIT 5
In this case RBR will work whereas when using statement based replication we cannot determine which rows will be deleted. There are more examples of such non-deterministic SQL statements where SBR fails but RBR works.

A third replication format mode is available, MIXED, where MariaDB decided ona statement by statement base which replication format is best.
Scaling replicationEventually many users ended up having many slaves attached to that single master. And for a while, this was not a big issue, the asynchronous nature of things means that the load on the master was limited when using replication, but with enough nodes, eventually this turned in to being an issue.

The solution then was to introduce an "intermediate master". This is a slave that is also a master to other slaves, and this is configured having log_slave_updates on, which means that data that is applied on the slave from the relay log and into the slave, are also written to the binlog.

This is a pretty good idea, but there are some issues also. To begin with, on the intermediate master, data has to be written several times, once in the relay log, once in the database (and if InnoDB is used, a transaction log is also written) and then we have to write it to the binlog.

Another issue that is in effect here is the single threaded nature of replication (this is different in MariaDB 10 and MySQL 5.7 and up), which means that a slave on a master that runs many threads, might get into a situation where the slave can't keep up with the master, even though the slave is similarly configured as the master. Also, a run running statement on the master will hold up replication for as long as that statement runs on the slave, and if we have an intermediate master, then the delay will be doubled (once on the intermediate master and once on the actual slave).

The combined effect of the duplication of the delay and the requirement to write data so many times, leads to the result that an intermediate master maybe isn't such a good idea after all.
As for the replication use-cases, intermediate masters are sometimes used as alternative masters when failing over. This might seems like a good idea, but the issue is that the binlogs on the intermediate master doesn't look the same as the binlogs on the actual master. This is fixed by using Global Transaction IDs though, but these have different issues and unless you are running MariaDB 10 or MySQL 5.6, this isn't really an option (and even with MySQL 5.6, there are big issues with this).

What we need then is something else. Something that is a real intermediate master. Something that looks like a slave to the master and as a master to the slave, but doesn't have to write data three times first and that doesn't have to apply all the replication data itself so it doesn't introduce delays into the replication chain.

The slave that attaches to this server should see the same replication files as it would see it it connected to the real master.
MaxScale and the Plugin architectureSo let's introduce MaxScale then, and the plugin architecture. MaxScale has been described before, but one that that might not be fully clear is the role of the plugins. MaxScale relies much more on the plugins that most other architectures, fact is, without the plugins, MaxScale can't do anything, everything is a plugin!

The MaxScale core is a multi-threaded epoll based kernel with 5 different types of plugins (note that there might be more than one plugin of each type, and this is mostly the case actually:
•    Protocols - These implement communication protocols, including debugging and monitoring protocols. From this you realize that without appropriate protocol plugins, MaxScale will not be able to be accessed at all, so these modules are key. Among the current protocols are MariaDB / MySQL Client and Server protocols.
•    Authentication - This type of plugin authenticates users connecting to MaxScale. Currently MariaDB / MySQL Authentication is supported.
•    Router - This is a key type of module that determines how SQL traffic is routed an managed.
•    Filter - This is an optional type of pluging there the SQL traffic can be modfied, checked or rejected,
•    Monitor - This type of modules is there to monitor the servers that MaxScale connects to, and this data is used by the routing mode.

Before we end this discussion on MaxScale, note that there might be several configurations through one single MaxScale setup, so MaxScale can listen to one prot for one set up servers and routine setup, and on another port for a different setup.

With this we have an idea how MaxScale work, so let's see if we can tie it all up.
MaxScale as a Binlog serverAs can be seen from the description of MaxScale a lot of what is needed to create a Binlog server to use as an intermediate server for slaves is there. What is needed is a router module that acts as a slave to the assigned master, downloads the binlogs from there, using the usual MariaDB / MySQL Replication protocol. This routing plugin also needs to serve the slaves with the downloaded binlogs files. In theory, and also in practice, the slaves will not know if it is connected to the real master or to MaxScale.

Using MaxScale this way as an intermediate Master, a slave that connects to the MaxScale can work from the same Binlog files and positions as when connected directly to the master, as the files are the same for all intents and purposes. There will be no extra delays for long running SQL statements as these aren't applied on MaxScale, the replication data is just copied from the master, plain and simple. As for parallel slaves, this should work better in when using MaxScale as a Binlog server, but this is yet to be tested.

So there should be many advantages to using MaxScale as a binlog server compared to using an intermediate MariaDB / MySQL server. On the other hand, this solution is not for everyone, many just doesn't drive replication that hard that the load on the master is an issue so that an intermediate Master is requited. On the other, many use an Intermediate Master also for HA, and in this case it would have be advantageous to use MaxScale instead of that Intermediate master, the latter which could still server the role as a fail-over HA server.

Now, there one issue with all of this that many of you might have spotted: That cool Binlog server plugin module for MaxScale doesn't exists. Well, I am happy to say that you are wrong, it does exist and it works. A Pilot for such a module has been developed by SkySQL together with Booking.com that had just this need for an intermediate server that wasn't just yet another MariaDB / MySQL server. For the details on the specific usecase, see the blog by Jean-François Gagné.

MariaDB 10.0.12 Overview and Highlights

Chris on MySQL - June 20, 2014 - 7:06pm GMT

MariaDB 10.0.12 was recently released, and is available for download here:

https://downloads.mariadb.org/mariadb/10.0.12/

This is the third GA release of MariaDB 10.0, and 13th overall release of MariaDB 10.0.

This is primarily a bug-fix release.

Here are the main items of note:

  1. Updated Spider storage engine to version 3.2.4.
  2. Updated Connect engine.
  3. Updated pcre to version 8.35.
  4. Exists-to-In optimization is now enabled by default.
  5. performance_schema is now disabled by default.

You can read more about the 10.0.12 release here:

https://mariadb.com/kb/en/mariadb-10012-release-notes/

And if interested, you can review the full list of changes in 10.0.12 (changelogs) here:

https://mariadb.com/kb/en/mariadb-10012-changelog/

Hope this helps.

 

MyQuery 3.5.5 Released

Karlsson on databases and stuff - June 16, 2014 - 1:10pm GMT
I have just released version 3.5.5 of MyQuery, which is a minor feature, cleanup and bugfix release. If you don't know MyQuery since before, this is an Open-Source Windows based MySQL and MariaDB ad-hoc query tool. What makes MyQuery slightly different from all the other similar tools is that MyQuery has a focus on SQL-scripting, allowing statements in a script to be run one at the time, to restart a script where it left of in the case of an error and some other features like this. MyQuery features colour coded syntax as it uses Scintilla for editing, and this is highly configurable. Also, multiple editing tabs are supported. Another thing that makes MyQuery stand out a bit is that it is highly flexible. If you have SQL statements that you run often to monitor the state of the server or your application, then it is real easy to implement this as a simple tool accessible from the MyQuery menu.

There is much more to MyQuery than this, so if you think this sounds cool, then download it from sourceforge and give it a shot, you might like it!

/Karlsson

MySQL 5.5.38 Overview and Highlights

Chris on MySQL - June 13, 2014 - 2:28am GMT

MySQL 5.5.38 was recently released (it is the latest MySQL 5.5, is GA), and is available for download here:

http://downloads.skysql.com/archive/index/p/mysql/v/5.5.38

This release, similar to the last 5.5 release, is mostly uneventful.

Out of the mere 13 bugs, most were benign, but I thought there were two worth mentioning (just because one is a slight performance improvement for InnoDB, and the other relates to slave consistency, so always important to see if it could affect you):

  1. InnoDB: For each insert, memset would be called three times to allocate memory for system fields. To reduce CPU usage, the three memset calls are now combined into a single call. (Bug #17858679, Bug #71014)
  2. Replication: When used on a table employing a transactional storage engine, a failed TRUNCATE TABLE was still written to the binary log and thus replayed on the slave. This could lead to inconsistency when the master retained data that was removed on the slave. Now in such cases TRUNCATE TABLE is logged only when it executes successfully. (Bug #17942050, Bug #71070)

For reference, the full 5.5.38 changelog can be viewed here:

http://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-38.html

Hope this helps.

 

MariaDB 5.5.38 Overview and Highlights

Chris on MySQL - June 12, 2014 - 5:11am GMT

MariaDB 5.5.38 was recently released (it is the latest MariaDB 5.5), and is available for download here:

https://downloads.mariadb.org/mariadb/5.5.38/

This is a maintenance release, and so there are not too many big changes of note, just a number of normal bug fixes. However, there are a few items worth mentioning:

  • Includes all bugfixes and updates from MySQL 5.5.38
  • XtraDB updated to the version from Percona Server 5.5.37-35.0
  • TokuDB updated to version 7.1.6
  • MariaDB on Power: With this release of MariaDB we’re introducing binary tarball builds created on a Power7 box running Red Hat 6. They’re listed on the downloads page in the “OS/CPU” column as “Linux ppc64″. We welcome your feedback on these new packages!

If interested, there is more about the 5.5.38 release here:

https://mariadb.com/kb/en/mariadb-5538-release-notes/

And the full list of fixed bugs and changes in MariaDB 5.5.38 can be found here:

https://mariadb.com/kb/en/mariadb-5538-changelog/

Hope this helps.

 

MySQL 5.6.19 Overview and Highlights

Chris on MySQL - June 11, 2014 - 5:26pm GMT

MySQL 5.6.19 was recently released (it is the latest MySQL 5.6, is GA), and is available for download here.

I should actually call this post “5.6.18 and 5.6.19 Overview and Highlights”.

The 5.6 “Release Notes” Index provides an entry/changelog for 5.6.18 and says it was released 2014-04-11. However, it’s not available in the community download archives. This isn’t mentioned in the 5.6.18 changelogs, but it is in the 5.6.19 changelogs, where it says:

“There is no MySQL Community Server 5.6.18. That version number was used for an out-of-schedule release of the Enterprise Edition to address the OpenSSL ‘Heartbleed’ issue. This issue did not affect the Community Edition because it uses yaSSL, not OpenSSL, so a new release of the Community Server was not needed, and 5.6.17 is followed by 5.6.19.”

While Heartbleed did not affect the *community* versions of MySQL 5.6, it does affect the *commercial* versions of MySQL 5.6 (less than 5.6.18). This is because the *community* version of MySQL 5.6 was built using yaSSL (unaffected SSL library), whereas the *commercial* version of MySQL 5.6 was build using OpenSSL (vulnerable SSL library). So take note of this if you’re running *commercial* MySQL 5.6.

There is also an important “known limitation” of this release, which affects MySQL 5.6.10 through and including 5.6.18, which is:

“If you have InnoDB tables with full-text search indexes and you are upgrading from MySQL 5.6.10 to a MySQL version up to and including MySQL 5.6.18, the server will fail to start after the upgrade (Bug#72079). This bug is fixed in MySQL 5.6.19. As a workaround, remove full-text search indexes prior to upgrading and rebuild full-text search indexes after the upgrade is completed.”

So beware of this if you’re running 5.6.10+, using InnoDB full-text search indexes, and plan on upgrading to 5.6.11 – 5.6.18.

There was also one other bug fixed in the 5.6.18 release notes, but just a rather obscure one related to running a collated subquery on an ARCHIVE table containing an AUTO_INCREMENT column.

Now, as for 5.6.19, there are more fixes:

2 Functionality Added or Changed, which all seem quite minor:

  1. The obsolete and unmaintained charset2html utility has been removed from MySQL distributions. (Bug #71897, Bug #18352347)
  2. The mysqlbug, mysql_waitpid, and mysql_zap utilities have been deprecated and will be removed in MySQL 5.7.

And I counted 32 bug fixes (6 InnoDB, 7 Replication, and 19 misc. bugs).

I found the more important/noteworthy bug fixes to be:

  • InnoDB: After upgrading from 5.6.10 to MySQL versions up to and including MySQL 5.6.18, InnoDB would attempt to rename obsolete full-text search auxiliary tables on server startup, resulting in an assertion failure. (Bug #18634201, Bug #72079)
  • InnoDB: The fix for Bug#17699331 caused a high rate of read/write lock creation and destruction which resulted in a performance regression. (Bug #18345645, Bug #71708). This regression bug affects versions 5.6.16 – 5.6.18.
  • InnoDB: For each insert, memset would be called three times to allocate memory for system fields. To reduce CPU usage, the three memset calls are now combined into a single call. (Bug #17858679, Bug #71014)
  • InnoDB: Enabling the InnoDB Table Monitor would result in a ib_table->stat_initialized assertion failure. (Bug #17039528, Bug #69641)
  • InnoDB: With innodb_max_dirty_pages_pct=0 buffer pool flushing would not be initiated until the percentage of dirty pages reached at least 1%, which would leave up to 1% of dirty pages unflushed. (Bug #13029450, Bug #62534)
  • Replication: Log rotation events could cause group_relay_log_pos to be moved forward incorrectly within a group. This meant that, when the transaction was retried, or if the SQL thread was stopped in the middle of a transaction following one or more log rotations (such that the transaction or group spanned multiple relay log files), part or all of the group was silently skipped. This issue has been addressed by correcting a problem in the logic used to avoid touching the coordinates of the SQL thread when updating the log position as part of a relay log rotation whereby it was possible to update the SQL thread’s coordinates when not using a multi-threaded slave, even in the middle of a group. (Bug #18482854)
  • Replication: In certain cases, the server mishandled triggers and stored procedures that tried to modify other tables when called by CREATE TABLE … SELECT. This is now handled correctly as an error. (Bug #18137535)
  • Replication: When used on a table employing a transactional storage engine, a failed TRUNCATE TABLE was still written to the binary log and thus replayed on the slave. This could lead to inconsistency when the master retained data that was removed on the slave. Now in such cases TRUNCATE TABLE is logged only when it executes successfully. (Bug #17942050, Bug #71070)
  • For indexes on prefixes or character string columns, index corruption could occur for assignment of binary data to the column due to improper character counting. (Bug #18359924)
  • mysqldump could create table definitions in the dump file that resulted in Too many columns errors when reloading the dump file. (Bug #17477959)
  • On Windows, REPAIR TABLE and OPTIMIZE TABLE failed for MyISAM tables with .MYD files larger than 4GB. (Bug #69683, Bug #17235179)

Conclusions:

  • If you’re running 5.6 *commercial*, and not running 5.6.18 or 5.6.19, you need to upgrade now.
  • If you’re running 5.6.10 through 5.6.18 and using InnoDB full-text indexes, and if you upgrade to a 5.6 version less than 5.6.19, you will encounter problems (though there is a work-around mentioned above).
  • The InnoDB fixes were somewhat specific, so I would only consider the regression bug that affected 5.6.16 – 5.6.18 to be an overall worrier, but read through them to be sure none of the others affect you.
  • Similarly, the couple replication fixes were also somewhat specific, so read through those to see if they could affect you.

The full changelogs can be viewed here (which has more details about all of the bugs listed above), 5.6.18 and 5.6.19 listed respectively:

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-18.html
http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-19.html

Hope this helps.

 

MariaDB is the default implementation of MySQL in Red Hat Enterprise Linux 7

Chris on MySQL - June 11, 2014 - 3:23am GMT

It was announced that MariaDB is now the default implementation of MySQL in the newly released Red Hat Enterprise Linux 7.

News of the Red Hat Enterprise Linux 7 can be found here.

And mention of MariaDB replacing MySQL as the default implementation is referenced in their release notes here.

The full change log entry reads:

MariaDB 5.5

  • MariaDB is the default implementation of MySQL in Red Hat Enterprise Linux 7. MariaDB is a community-developed fork of the MySQL database project, and provides a replacement for MySQL. MariaDB preserves API and ABI compatibility with MySQL and adds several new features; for example, a non-blocking client API library, the Aria and XtraDB storage engines with enhanced performance, better server status variables, and enhanced replication.

    Detailed information about MariaDB can be found at https://mariadb.com/kb/en/what-is-mariadb-55/.

 

What exactly does log_warnings=2 log?

Chris on MySQL - June 6, 2014 - 7:29pm GMT

Have you ever wondered exactly does log_warnings=2 log? Well, I have, and finally decided to check the code. (The manual used to mention setting this to 2 for diagnosing some connection-related problems, but I didn’t run into that comment in my most recent search.)

Basically, in recent 5.6 source code, we find “log_warnings > 1″ in 7 files. In 5.5 source, it is only in 5 files. Here are the 7 files in 5.6:

filesort.cc (line 460)
log_event.cc (lines 4873, 10020, 11209)
rpl_master.cc (line 912)
rpl_rli_pdb.cc (lines 1538, 1596, 1735, 2066)
rpl_slave.cc (lines 3585, 4684, 5405, 5436)
sql_acl.cc (lines 9591, 9613, 11351)
sql_connect.cc (line 791)

Long story short, the main (most common) ones are when a filesort fails (filesort.cc) or a failed login occurs (sql_acl.cc). Then there are some replication-specific instances where it logs extra info, such as master/slave/binlog info, “ignored” errors, and some summary stats for multi-threaded slave worker threads (rpl_master.cc, rpl_rli_pdb.cc, rpl_slave.cc) (All of the extra replication logging was new to 5.6, fwiw.). If it fails to close a connection that it should close, it logs some info (sql_connect.cc). And lastly, another new option in 5.6 is password expiration support. So if that is enabled and expired for the current user (and log_warnings > 1), then a note is logged to the error log as well (sql_acl.cc line 11351).

Long story long, … I debated posting the relevant code snippets, but after seeing how boring that was going to be, I’ve omitted them.

Fwiw, the max log_warnings in 5.6 on 32-bit is 4294967295 and for 64-bit is 18446744073709547520. However, at least for now anyway, it seems there is no difference between setting this to 2 or 18446744073709547520, as the only current references/actions are for “log_warnings > 1″.

In 5.7, there is some new info added to the log_warnings definition, mostly because of the new 5.7 variable log_error_verbosity. Here is what is says about “log_warnings=2 (or higher)”:

“Setting log_warnings=2 (or higher) is equivalent to log_error_verbosity=3 (errors, warnings, notes), and the server sets log_warnings to 2 if a larger value is specified.”

Hope this helps.

 

MaxScale 0.7.0 Overview and Highlights

Chris on MySQL - June 6, 2014 - 6:24pm GMT

MaxScale 0.7.0 was recently released (it is the 4th alpha, with the beta on the near horizon), and is available for download here.

The release contains a number of new enhancements as well as 8 bugs fixes.

Enhancements:

  1. Galera Support: Enhanced support for Galera cluster to allow Galera to be used as a High Available Cluster with no write contention between the nodes. MaxScale will control access to a Galera Cluster such that one node is designated as the master node to which all write operations will be sent. Read operations will be sent to any of the remaining nodes that are part of the cluster. Should the currently elected master node fail MaxScale will automatically promote one of the remaining nodes to become the new master node.
  2. Multiple Slave Connections: The Read/Write Split query router has been enhanced to allow multiple slaves connections to be created. The number of slave connections is configurable via a parameter in the MaxScale configuration file. Adding multiple connections allows for better load balancing between the slaves and in a pre-requisite for providing improved fault tolerance within the Read/Write Splitter. The selection of which slave to use for a particular read operation can be controlled via options in the router configuration.
  3. Debug Interface Enhancements: A number of new list commands have been added to the debug interface to allow more concise tabular output of certain object types within the interface.
    MaxScale> help list
    Available options to the list command:
       filters    List all the filters defined within MaxScale
       listeners  List all the listeners defined within MaxScale
       modules    Show all currently loaded modules
       services   List all the services defined within MaxScale
       servers    List all the servers defined within MaxScale
       sessions   List all the active sessions within MaxScale
    MaxScale>

    Those objects that are defined in the configuration file can now be referenced by the names used in the configuration file rather than by using memory addresses. This means that services, servers, monitors and filters can all now be referenced using meaningful names provided by the user. Internal objects such as DCB’s and sessions, which are not named in the configuration file still require the use of memory addresses. Two modes of operation of the interface are now available, user mode and developer mode. The user mode restricts access to the feature that allow arbitrary structures to be examined and checks all memory address for validity before allowing access.

  4. Maintenance Mode for Servers: MaxScale now provides a maintenance mode for servers, this mode allows servers to be set such that no new connections will be opened to that server. Also, servers in maintenance mode are not monitored by MaxScale. This allows an administrator to set a server into maintenance mode when it is required to be taken out of use. The connections will then diminish over time and since no new connections are created, the administrator can remove the node from use to perform some maintenance activities.

    Nodes are placed into maintenance mode via the debug interface using the set server command.

    MaxScale> set server datanode3 maintenance

    Nodes are taken out of maintenance using the clear server command.

    MaxScale> clear server datanode3 maintenance
  5. Configurable Monitoring Interval: All monitor plugins now provide a configuration parameter that can be set to control how frequently the MaxScale monitoring is performed.
  6. Replication Lag Heartbeat Monitor: The mysqlmon monitor module now implements a replication heartbeat protocol that is used to determine the lag between updates to the master and those updates being applied to the slave. This information is then made available to routing modules and may be used to determine if a particular slave node may be used or which slave node is most up to date.
  7. Filters API: The first phase of the filter API is available as part of this release. This provides filtering for the statements from the client application to the router. Filtering for the returned results has not yet been implemented and will be available in a future version.

    Three example filters are including in the release

    1. Statement counting Filter – a simple filter that counts the number of SQL statements executed within a session. Results may be viewed via the debug interface.
    2. Query Logging Filter – a simple query logging filter that write all statements for a session into a log file for that session.
    3. Query Rewrite Filter – an example of how filters can alter the query contents. This filter allows a regular expression to be defined, along with replacement text that should be substituted for every match of that regular expression.
  8. MariaDB 10 Replication Support: The mysqlmon monitor module has been updated to support the new syntax for show all slaves status in MariaDB in order to correctly determine the master and slave state of each server being monitor. Determination of MariaDB 10 is automatically performed by the monitor and no configuration is required.
  9. API Versioning: The module interface has been enhanced to allow the API version in use to be reported, along with the status of the module and a short description of the module. The status allows for differentiation of the release status of a plugin to be identified independently of the core of MaxScale. plugins may be designated as “in development”, “alpha”, “beta” or “GA”.
    MaxScale> list modules
    Module Name     | Module Type | Version | API   | Status
    ----------------------------------------------------------------
    regexfilter     | Filter      | V1.0.0  | 1.0.0 | Alpha
    MySQLBackend    | Protocol    | V2.0.0  | 1.0.0 | Alpha
    telnetd         | Protocol    | V1.0.1  | 1.0.0 | Alpha
    MySQLClient     | Protocol    | V1.0.0  | 1.0.0 | Alpha
    mysqlmon        | Monitor     | V1.2.0  | 1.0.0 | Alpha
    readwritesplit  | Router      | V1.0.2  | 1.0.0 | Alpha
    readconnroute   | Router      | V1.0.2  | 1.0.0 | Alpha
    debugcli        | Router      | V1.1.1  | 1.0.0 | Alpha
    MaxScale>
  10. Linking: Following reported issues with incompatibilities between MaxScale and the shared library used by MySQL this version of MaxScale will be statically linked with the MariaDB 5.5 embedded library that it requires. This library is used for internal purposes only and does not result in MaxScale support for other versions of MySQL or MariaDB being affected.

Bug Fixes:

  1. mysql/galera monitors hang when backend fails (443)
  2. Read/Write Splitter closes connection without sending COM_QUIT (424)
  3. Internal thread deadlock (438)
  4. Sessions in invalid state (436)
  5. Router options for Read/Write Split module (359)
  6. Some automated tests have invalid SQL syntax (435)
  7. rwsplit.sh test script has incorrect bash syntax (431)
  8. MaxScale crashes after prolonged use (425)

If you’re not too familiar with MaxScale, you can view a short, 4 minute, overview (as well as read some details) here.

Hope this helps.

 

MaxScale 0.6.0 Overview and Highlights

Chris on MySQL - June 5, 2014 - 2:34am GMT

MaxScale 0.6.0 was recently released (it is the 3rd alpha, with the beta on the near horizon), and is available for download here.

The particular release only contains 2 great additions and 2 important fixes (and note development continues with the 1.0 (GA) features, but these have not been put into this alpha version).

Important Additions:

  1. A feature-complete read/write splitting module, i.e. read and write operations are now balanced in a smarter way to master or slave servers.
  2. New client-based features, such as a version string that provides compatibility with the major connectors, the ability to connect through the root user and the use of the Unix socket when MaxScale is co-located with a client application on the same server.

Important Bug Fixes:

  1. The new parameter “version_string” parameter has been added to service section. This allows a specific version string to be set for each service, this version string is used in the handshake from MaxScale to clients and is reported as the server version to clients.

    The version_string is optional, the default value will be taken from the embedded MariaDB library which supplies the parser to MaxScale.

  2. Statements are not routed to master if a transaction is started implicitly by setting autocommit=0. In such cases statements were previously routed as if they were not part of a transaction.

    This fix changes the behavior so that is autocommit is disabled, all statements are routed to the master and in case of session variable updates, to both master and slave.

If you’re not too familiar with MaxScale, you can view a short, 4 minute, overview here. (And read the 50-foot overview.)

Also, just to throw my 2 cents in, if you’re looking for a read-write splitting load balancer, then you should definitely check out MaxScale (though it is much more robust than that). But that is just one feature that many are already using successfully in production environments. And it will not suffer from the limitations that other read-write splitting load-balancing solutions have encountered which have prevented their wide-spread use. And as I noted, the beta is coming soon (and GA not too far behind that), with even more read-write splitting enhancements.

And if interested in some more about MaxScale, there are some great posts here (some with excellent step-by-step setup instructs):

Hope this helps.

 

MariaDB 10.0.11 Overview and Highlights

Chris on MySQL - May 30, 2014 - 1:37am GMT

MariaDB 10.0.11 was recently released, and is available for download here:

https://downloads.mariadb.org/mariadb/10.0.11/

This is the second GA release of MariaDB 10.0, and 12th overall release of MariaDB 10.0.

This is primarily a bug-fix release.

Here are the main items of note:

  1. Updated TokuDB engine to version 7.1.6
  2. Updated Spider storage engine to version 3.2 (now Gamma)
  3. Updated XtraDB storage engine to version 5.6.17-65.0
  4. Updated InnoDB storage engine to version 5.6.17
  5. Updated performance_schema to version 5.6.17
  6. Updated Connect, and OQGraph engines.
  7. Online ALTER TABLE works for partitioned tables
  8. New system variable default_regex_flags. To make MariaDB RLIKE operator behave in a non-standard but backward compatible way use
    SET @@default_regex_flags='DOTALL';
  9. As per the MariaDB Deprecation Policy, this will be the last release of MariaDB 10.0 for both Ubuntu 12.10 “Quantal” and Mint 14 “Nadia”.

You can read more about the 10.0.11 release here:

https://mariadb.com/kb/en/mariadb-10011-release-notes/

And if interested, you can review the full list of changes in 10.0.11 (changelogs) here:

https://mariadb.com/kb/en/mariadb-10011-changelog/

Hope this helps.

 

MaxScale 0.6

Ivan's Blog - May 14, 2014 - 9:57am GMT
Kudos to the SkySQL Engineering team, who released the 3rd alpha version of MaxScale, a database proxy for MySQL, MariaDB and Percona servers, labeled MaxScale 0.6.
This version comes with two important additions:
  • A feature-complete read/write splitting module, i.e. read and write operations are now balanced in a smarter way to master or slave servers.
  • New client-based features, such as a version string that provides compatibility with the major MariaDB and MySQL connectors, the ability to connect through the root user and the use of the unix socket when MaxScale is co-located with a client application on the same server.

Binaries and source code are “hidden” here: http://downloads.skysql.com/files/SkySQL/MaxScale.
The project is on GitHub: https://github.com/skysql/MaxScale/
Other helpful links are:
  • IRC: irc:irc.freenode.net/maxscale

We have still some work to do before we can reach a beta stage. We definitely need to catch more bugs, but we also want to extend the features of the product. We are working on different types of load balancing, on the compatibility with Galera, MySQL Cluster/NDB, MariaDB Spider and on many other aspects.
At the moment we have two routing modules:
  • The readconnroute module is a connection load balancer that accepts 2 types of connections from the client (read/write and read/only) and it balances the connections among all the masters and the slaves in a MySQL and MariaDB Cluster. This is helpful for applications that have apre-defined read/write and read/only operations. If the backend is based on a Galera Cluster, readconnroute acts as a “simple” load balancer for all the connections.
  • The readwritesplit module is a statement load balancer that accepts a connection that can send read and write operations. The module parses each statement and decides whether must be sent to a master server, a slave server or all the servers. The backend is a MariaDB or a MySQL Replication cluster.

There is definitely more to come. For example, we want to provide read/write splitting for Galera clusters in order to avoid deadlocks and to guarantee read operations without replication lag. We are also working on a version that can handle slaves with different specification and clusters that can be organised on multiple locations, i.e. where there is a mix of local and remote servers. Last but not least, we are working on benchmarks to show the clear benefits of MaxScale, where it fits and where it doesn’t. in a typical IT infrastructure.
As usual, help, suggestions and comments are more than welcome.

Another reason for Xtrabackup error “log block numbers mismatch”

Chris on MySQL - May 7, 2014 - 2:15pm GMT

I ran into an issue the other day where Xtrabackup was not completing and threw the following error:

xtrabackup: error: log block numbers mismatch:
xtrabackup: error: expected log block no. 134860907,
but got no. 176803931 from the log file.
xtrabackup: Error: xtrabackup_copy_logfile() failed.

When researching this error, I found that this error is generally caused when the following 2 conditions must are met, and this is generally when “xtrabackup cannot catch up with log writing activity on the server, so the log wraps around before xtrabackup can copy records before they are overwritten”:

  1. The log block numbers must wrap around, which only happens once per 1 GB of log writes.
  2. The wrap-around point must be between the last checkpoint and the current log tail at the time the backup starts.

Discussed here:
https://bugs.launchpad.net/percona-xtrabackup/+bug/1206309
https://bugs.launchpad.net/percona-xtrabackup/+bug/1050355

When this does occur as a result of the above (and not a bug), then it’s usually an I/O issue, either on the MySQL-side or the disk-side. One you can check with the /dev/null redirect of the actual data (i.e., if it then completes, perhaps you have some IO issue, so investigate that, and/or perform during a period of low-activity).

In this specific case, however, it was a new version of Xtrabackup, low-load, 6G InnoDB log files, …

So what was the problem?

Here the user was trying to use the “xtrabackup” binary to dump a MySQL 5.6 instance. Once switched to “xtrabackup_56″ binary, as described here, it worked as expected.

Hope this helps.

 

SQLStats plugin version 1.4 released

Karlsson on databases and stuff - April 29, 2014 - 6:17pm GMT
I have now released version 1.4 of my sqlstats plugin for MySQL and MariaDB. As of this version I focus on test with MariaDB, but it should work with MySQL also. In addition, I have now made the code a bit more portable by removing some features that depended on the THD struct.

So, what is sqlstats then, you ask? Well, the idea is to keep track of executed SQL statements and gather up statistics on how often they are used. The interesting thing is that sqlstats, before counting the number of executions of a statement, "normalizes" it, by which I mean that it regards
SELECT prod_name FROM products WHERE prod_id = 90;
as the same statement as
SELECT prod_name FROM products WHERE prod_id = 212;
This is useful to track those statements that takes very little time to execute, so probably they don't show up that often in SHOW PROCESSLIST, but are executed to so often to the performance effect can be real bad.
Also, this is useful to track those fast statements that are executed often, but that actually doesn't need to be executed that often as the data is cached or the result is already known or something.

So how does all this work then?
Well, the plugin is an AUDIT and an INFORMATION_SCHEMA plugin in one. The AUDIT plugin part is used to track the statements, every statement that is execute in the server passes this. When a statement is processed by this plugin, the statement is normalized and then it is checked for existence in a list of least recently used statements. This is to ensure that I don't have to keep track of all statements executed to keep a top list. Then if the statements is executed so often that it should be on the top list, then it is placed there. The reason I can't do with the top list only is that if a "new" statement that is frequently executed gets in, and the top list is already full, it will never get on the top list at all (as it is not on the list, it's executing count is 1 and it doesn't reach the list, then the same thing happens next time etc).

These lists of statements are kept in memory inside the plugin itself.

Now, to see what the top SQL statements are, this is done by the plugin also being an INFORMATION_SCHEMA plugin, so the data is available by a simple select:
SELECT * FROM information_schema.sqlstats_topsql;
And that's it!
There is also an INFORMATION_SCHEMA table to show the last executed SQL statements:
SELECT * FROM information_schema.sqlstats_lastsql;

Now, you may ask what the performance overhead is of all this, and fact is that it's rather small, mostly hardly noticeable. In addition, you can switch it off using the sqlstats_enable global variable.
And the size of the top list and lru lists can also be adjusted by in the sqlstats_top_stmts and sqlstats_lru_stmts global variables respectively.

The plugin is downloadable from sourceforge which contains the sourcecode which uses GNU autotools to build it. Also, there is a full documentation pdf there.

/Karlsson 

MariaDB 5.5.37 Overview and Highlights

Chris on MySQL - April 23, 2014 - 3:55pm GMT

MariaDB 5.5.37 was recently released (it is the latest MariaDB 5.5), and is available for download here:

https://downloads.mariadb.org/mariadb/5.5.37/

This is a maintenance release, and so there are not too many big changes of note, just a number of normal bug fixes. However, there are a few items worth mentioning:

  • Includes all bugfixes and updates from MySQL 5.5.37
  • XtraDB updated to the version from Percona Server 5.5.36-34.0
  • TokuDB updated to version 7.1.5
  • Default compression for TokuDB is now TOKUDB_ZLIB (instead of TOKUDB_UNCOMPRESSED)
  • The MariaDB Audit Plugin now included.

Given the number of InnoDB and XtraDB fixes (in 5.5.37 and 5.5.36 respectively), if you are using InnoDB or XtraDB+ in MariaDB 5.5, then I would definitely recommend upgrading to MariaDB 5.5.37.

If interested, there is more about the 5.5.37 release here:

https://mariadb.com/kb/en/mariadb-5537-release-notes/

And the full list of fixed bugs and changes in MariaDB 5.5.37 can be found here:

https://mariadb.com/kb/en/mariadb-5537-changelog/

Hope this helps.

 

WebScaleSQL on Windows? I wish, but not quite yet, it seems …

Chris on MySQL - April 17, 2014 - 8:49pm GMT

For fun, I tried building WebScaleSQL on Windows, even though it’s not [yet?] a support platform.

Using the following (as I would to build MySQL/MariaDB):

cd c:\mysql\webscalesql-5.6.16
mkdir bld
cd bld
cmake ..
cmake --build . --config relwithdebinfo --target package

I end up with:

...
    238 Warning(s)
    110 Error(s)
Time Elapsed 00:05:08.53

Looking through the output, the main error is this:

C:\mysql\webscalesql-5.6.16\include\atomic_stat.h(33):
fatal error C1083: Cannot open include file: 'atomic':
No such file or directory
[C:\mysql\webscalesql-5.6.16\bld\storage\innobase\innobase.vcxproj]

Of course the directory does exist, and permissions are correct.

C:\mysql\webscalesql-5.6.16\include\atomic_stat.h contains the following line:

#include <atomic>

And this exists:

C:\mysql\webscalesql-5.6.16\include\atomic

So there must be some reason Windows doesn’t like that include. As far as I can tell, most other includes specify an exact file, and not a directory.

I tried replacing the single include by specifying each .h file included in atomic\, but that resulted in many more errors (776).

If any ideas, please feel free to share. And/or if this is futile, fine to mention that also.

 

Resolving Error 1918, System Error Code 126, When Installing MySQL ODBC Driver

Chris on MySQL - April 17, 2014 - 8:32pm GMT

If you are installing MySQL ODBC Driver and encounter the following error:

Error 1918. Error installing ODBC driver MySQL ODBC 5.1 Driver,
ODBC error 13: The setup routines for the MySQL ODBC 5.1 Driver
could not be loaded due to system error code 126:
The specified module could not be found.
...\myodbc5S.dll).. Verify...

Then you will need to install the Microsoft Visual C++ 2010 Redistributable Package (select the appropriate one for your OS architecture below):

64-bit version:

http://www.microsoft.com/en-us/download/confirmation.aspx?id=14632

32-bit version:

http://www.microsoft.com/en-gb/download/details.aspx?id=5555

After installing that, then re-attempt installing the MySQL ODBC connector, and things should work smoothly.

Hope this helps.

 

Resolving MySQL ODBC “architecture mismatch” Error

Chris on MySQL - April 17, 2014 - 7:12pm GMT

If you attempt to use ODBC to run a MySQL application and run into the following error:

[Microsoft][ODBC Driver Manager] The specified DSN contains an
architecture mismatch between the Driver and Application

This means there is a 64-bit versus 32-bit mismatch.

Most likely, you’re running 64-bit Windows, as well as 64-bit MySQL ODBC connector, but the application is 32-bit.

If this is the case, you will also need to install the 32-bit MySQL ODBC connector, and then create the connection from the 32-bit ODBC.

odbcad32.exe is the file to create the connections. Both 64-bit and 32-bit files have the same name, just differing locations.

This is the default location for the 64-bit ODBC:

C:\Windows\System32\odbcad32.exe

This is the default location for the 32-bit ODBC:

C:\Windows\SysWOW64\odbcad32.exe

And should you need to install MySQL ODBC, there are good instructions here (both for MSI and Manual installs).

Hope this helps.

 

Looking for Slave Consistency: Say Yes to –read-only and No to SUPER and –slave-skip-errors

Chris on MySQL - April 16, 2014 - 7:04pm GMT

The biggest concern with a slave is to ensure your data is consistent with the master! End of story!

3 of the biggest things I see when dealing with out-of-sync slaves:

  1. Many users do not use the --read-only option on their slaves.
  2. Some of those who do often have numerous users with SUPER who can still perform writes.
  3. Many users simply use --slave-skip-errors=… to avoid common errors.

Of course, if you have a slave, definitely use the --read-only option.

However, SUPER users can still write on slaves with --read-only, so blindly granting SUPER to all users just to save a little time when creating users won’t help. I’d suggest to use SUPER as sparingly as possible (not to mention it’s good for security also).

And the use of --slave-skip-errors=… is generally just a quick fix to avoid errors on the slave, but a sure fire way to end up with inconsistent data. Please don’t just use this blindly, but rather figure out why you are receiving such errors and resolve them the correct way. (We’re happy to assist if you need help with this.)

In conclusion, if the consistency of the data on your slave matters to you/your business/your customers, and/or if you’ve already had problems with inconsistent data, then add --read-only, remove --slave-skip-errors=…, and remove SUPER from as many users that use the slave as possible. That will be a terrific start.

If you are really serious about having a “read only” slave, and you use InnoDB, then as of MySQL 5.6 and MariaDB 10.0, there is also the innodb_read_only option, which has the following effect:

  • No user will be able to modify. Only DCL (like create user/grant/revoke etc) commands will work for the root user.

Note that when both --read-only and --innodb-read-only are given, then --innodb-read-only takes effect.

There is more information on this variable, and how to set up read only InnoDB on the following two pages:

https://blogs.oracle.com/MySQL/entry/innodb_read_only_mode
http://dev.mysql.com/../innodb-read-only-instance.html

Along this same line, and in addition to InnoDB, MariaDB 10.0 also has a spider_read_only_mode option which will make spider tables read only as well.

Hope this helps.

 

Syndicate content