A useful overview of options, syntax and tools that have been deprecated or removed for the upcoming MySQL 5.7 release.
This is a little quiz (could be a discussion). I know what we tend to prefer (and why), but we’re interested in hearing additional and other opinions!
Given the way MySQL/MariaDB is architected, what would you prefer to see in a new server, more cores or higher clock speed? (presuming other factors such as CPU caches and memory access speed are identical).
For example, you might have a choice between
- 2x 2.4GHz 6 core, or
- 2x 3.0GHz 4 core
which option would you pick for a (dedicated) MySQL/MariaDB server, and why?
And, do you regard the “total speed” (N cores * GHz) as relevant in the decision process? If so, when and to what degree?
Open Query is supporting the mysql-cli Kickstarter project (for MySQL and MariaDB) by Amjith Ramanujam who already successfully completed a similar tool for PostgreSQL.
It is a new MySQL client with Auto-Completion and Syntax Highlighting. From the info provided, it’s Python based, thus portable, and can be installed without root access. Could be a very useful tool. The good old mysql command line client does lack some things, yet a relatively low-level command line client is often useful for remote tasks (as opposed to graphical tools) so we reckon it’s good that this realm gets a bit of attention!
Because interactivity with the trainer (our classes are not dry lectures) and discussions are an important and intrinsic part of our teaching approach, we’ve long tracked development of technologies for online training, but previously were not satisfied.
High costs of various corporate offerings would negatively impact our pricing, given the relatively small scale use and our purposely small classes. The student system requirements would often be problematic – obviously students use different operating systems (Windows, Mac, Linux) and we cannot prescribe that people use a particular OS.
Big Blue Button has long looked like it had the right potential, and it’s now developed to a point where were happy with it. For more tech and practical details, see our Interactive Online Training page.
After our successful trial runs, we have the following course modules scheduled in the next few weeks, others to follow soon:
- 25-26 Mar 2015: MariaDB 10.0 & MySQL 5.6 Upgrade and Features (AUD 195 + GST)
- 30 Mar – 2 Apr 2015: MySQL/MariaDB Backup, Recovery and Replication (incl Galera) Workshop (AUD 595 + GST)
The date ranges may appear a tad odd at first, but what we do is run each original day-module across two sessions over two days, in this case noon-3.30pm Sydney time. An online session has three 10 minute breaks.
As you can also see the pricing is pretty neat – we can do that since we control the infrastructure and obviously don’t have trainer travel, venue and catering to worry about. No travel hassles for you, either! You should find a quiet spot and try and not get interrupted.
All the interactivity, discussion and hands-on work is there as normal, Open Query provides the VMs – and students (and trainer) can access each other’s session, where needed. We’re pretty pleased with the set up.
Naturally we can also do custom training in this format – we do still offer on-site training as well.
For bookings, or if you’d like more information or have other questions, contact us today!
Maish Saidel-Keesing writes:
VMware announced last week the launch of VIO and there are a number of things that I think people are missing and should be pointed out.
Building on the original kSplice idea and combining the efforts of the work done at Red Hat and SuSE, common infrastructure is now ready to be put into the Linux 3.20 mainline kernel – Red Hat and SuSE have already committed to using this.
I still reckon it’s freaky trickery, but heck – it works, and it’s great for server environments that have no redundancy (I prefer to fix that issue!) and can’t afford any downtime.
The MySQL/MariaDB optimiser likes to know things like the cardinality of an index – that is, the number of distinct values the index holds. For a PRIMARY KEY, which only has unique values, the number is the same as the number of rows. For an indexed column that is boolean (such as yes/no) the cardinality would be 2.
There’s more to it than that, but the point is that the optimiser needs some statistics from indexes in order to try and make somewhat sane decisions about which index to use for a particular query. The statistics also need to be updated when a significant number of rows have been added, deleted, or modified.
In MyISAM, ANALYZE TABLE does a tablescan where everything is tallied, and the index stats are updated. InnoDB, on the other hand, has always done “index dives”, looking at a small sample and deriving from that. That can be ok as a methodology, but unfortunately the history is awkward. The number used to be a constant in the code (4), and that is inadequate for larger tables. Later the number was made a server variable innodb_stats_sample_pages and its default is now 8 – but that’s still really not enough for big(ger) tables.
We recently encountered this issue again with a client, and this time it really needed addressing as no workarounds were effective across the number of servers and of course over time. Open Query engineer Daniel filed https://mariadb.atlassian.net/browse/MDEV-7084 which was picked up by MariaDB developed Jan Lindström.
Why not just set the innodb_stats_sample_pages much higher? Well, every operation takes time, so setting the number appropriate for your biggest table means that the sampling would take unnecessarily long for all the other (smaller, or even much smaller) tables. And that’s just unfortunate.
So why doesn’t InnoDB just scale the sample size along with the table size? Because, historically, it didn’t know the table size: InnoDB does not maintain a row count (this has to do with its multi-versioned architecture and other practicalities – as with everything, it’s a trade-off). However, these days we have persistent stats tables – rather than redoing the stats the first time a table is opened after server restart, they’re stored in a table. Good improvement. As part of that information, InnoDB now also knows how many index pages (and leaf nodes in its B+Tree) it has for each table. And while that’s not the same as a row count (rows have a variable length so there’s no fixed number of rows per index page), at least it grows along with the table. So now we have something to work with! The historical situation is no longer a hindrance.
In order to scale the sample size sanely, that is not have either too large a number for small tables, or a number for big tables that’s over the top, we’ll want some kind of logarithmic scale. For instance, log2(16 thousand) = 14, and log2(1 billion) = 30. That’s small enough to be workable. The new code as I suggested:
n_sample_pages = max(min(srv_stats_sample_pages, index->stat_index_size), log2(index->stat_index_size) * srv_stats_sample_pages);
This is a shorter construct (using min/max instead of ifs) of what was already there, combined with the logarithmic sample basis. For very small tables, either the innodb_stats_sample_pages number if used or the actual number of pages, whichever is smaller – for bigger tables, the log2 of the #indexpages is used, multiplied by the dynamic system variable innodb_stats_sample_pages. So we can still scale and thus influence the system in case we want more samples. Simple, but it seems effective – and it any case we get decent results in many more cases than before, so it’s a worthwhile improvement. Obviously, since it’s a statistical sample, it could still be wrong for an individual case.
Jan reckons that just like MyISAM, InnoDB should do a table scan and work things out properly – I agree, this makes sense now that we have persistent stats. So the above is a good fix for 5.5 and 10.0, and the more significant change to comprehensive stats can be in a near future major release. So then we have done away with the sampling altogether, instead basing the info on the full dataset. Excellent.
Another issue that needed to be dealt with is when InnoDB recalculates the statistics. You don’t want to do it on every change, but regularly if there has been some change is good as it might affect which indexes should be chosen for optimal query execution. The hardcoded rule was 1/16th of the table or 2 billion rows, whichever comes first. Again that’s unfortunate, because for a bigger table 1/16th still amounts to a very significant number. I’d tend towards setting the upper bound to say 100,000. Jan put in a new dynamic system variable for this, stat_modified_counter. It essentially replaces the old static value of 2 billion, providing us with a configurable upper bound. That should do nicely!
Once again horay for open source, but in particular responsive and open development. If someone reports a bug and there is no interaction between developer and the outside world until something is released, the discussion and iterative improvement process to come to a good solution cannot occur. The original code definitely worked as designed, but it was no longer suitable in the context of today’s usage/needs. The user-dev interaction allowed for a much better conclusion.
Multi-threaded replication is a new feature introduced in MySQL 5.6 and MariaDB 10.0. In traditional single-threaded replication, the slaves have a disadvantage as they have to process in sequence what a master executed in parallel. This, plus the fact that slaves usually have a lot of read-only connections to deal with as well, can easily create performance problems. That is, a single-threaded slave needs to be set to allow fewer connections, otherwise there’s a higher risk of it not being able to keep up with the replication stream. There is no exact rule for this, as it relates to general I/O capacity and fsync latency, as well as general CPU and RAM considerations and query patterns.
Currently, it appears that the MariaDB implementation is a bit more mature in terms of design and effective implementation. For instance, MySQL 5.6 does not currently support retrying transactions while doing parallel replication. This can easily cause problems as commit conflicts are possible and obviously need to be handled. So for the purpose of this blog post, we’re going to focus on MariaDB 10.0, and it is what we currently use with some of our clients. MariaDB developer Kristian Nielsen has done awesome work and is very responsive to questions and bug reports. Rock on, Kristian!
The fundamental challenge for parallel replication is that some queries are safe to be executed in parallel, and some are not – and somehow, the server needs to know which is which. MariaDB employs two strategies to assist with this:
- Group commit. Since 5.5, transactions (remember, a standalone statement without START TRANSACTION/COMMIT is technically also a transaction) that happen around the same time in different connections are grouped in the binary log and effectively committed together. This is accomplished by the server trying to gather at least a certain number of transactions (binlog_commit_wait_count) and having individual connections wait just a fraction (binlog_commit_wait_usec) to increase the chances of gathering a nice number. This strategy reduces I/O and fsyncs, and thus helps quite a bit with write scaling. The miniscule delay that a transaction might incur because it has to wait is easily offset by the overall better performance. It’s good stuff. For the purpose of parallel replication, any transactions in the same group commit can in principle be executed in parallel on a slave – conflicts are possible, so deadlock handling and retries are essential.
- Global Transaction IDs (GTID) Domain IDs (gtid_domain_id) in MariaDB 10.0, which an application can set within a connection. Quite often, different applications and different components of applications use the same database server, but their actions are completely independent: no write operations will ever conflict between the different applications. GTID Domain IDs allows us to tell the server about this, allowing it to always run those transactions in parallel even if they weren’t part of the same group commit! Now that’s a real bonus!
Now, as a practicality, we’re not always able to modify applications to for instance set the GTID Domain ID. Plus, a magic (integer) number is required and so we need some planning/coordination between completely independent applications! Through database server consolidation, you may get applications on your server that were previously on a different one – strictly speaking having two applications use the same GTID Domain ID is harmless (after all, by default all transactions run in the same domain!) but obviously it doesn’t improve performance.
Open Query engineer Daniel Black and I came up with the following. It’s a combination of MySQL’s init_connect system variable (gets called when a user connects, except if they have SUPER privilege), a few stored procedures, and an event to keep the domain map reasonably up-to-date. The premise of this implementation is that each database username uniquely identifies an application, and that no two usernames refer to the same application. So, if you have for instance a general application user but also one for background scripts or one with special administrative privileges, then you need to modify the code in setdomain() a bit to take this into account. If you have transactions with a different GTID Domain ID execute in parallel on the same database, obviously this can cause conflicts. The MariaDB slave threads will retry, but in some cases conflicts cannot be resolved by retrying.
Obviously it’s not perfect, but it does resolve the issue for many situations. Feedback and improvements welcome!
# Automatic GTID Domain IDs for MariaDB 10.0 # Copyright (C) 2014 by Daniel Black & Arjen Lentz, Open Query Pty Ltd (http://openquery.com.au) # Version 2014-11-18, initial publication via OQ blog (http://openquery.com.au/blog/) # # This work is licensed under Creative Commons Attribution-ShareAlike 4.0 International # http://creativecommons.org/licenses/by-sa/4.0/ USE mysql DELIMITER // DROP PROCEDURE IF EXISTS setdomain // CREATE PROCEDURE setdomain(IN cuser varchar(140)) DETERMINISTIC READS SQL DATA SQL SECURITY DEFINER BEGIN DECLARE EXIT HANDLER FOR NOT FOUND SET SESSION gtid_domain_id=10; # modify this logic for your particular application/user naming convention SELECT domain INTO @l_gtid_domain_id FROM mysql.user_domain_map WHERE user=LEFT(cuser, LOCATE('@',cuser) -1 ); SET SESSION gtid_domain_id=@l_gtid_domain_id; END // DROP PROCEDURE IF EXISTS create_user_domain_map // CREATE PROCEDURE create_user_domain_map() MODIFIES SQL DATA BEGIN DECLARE u CHAR(80); DECLARE h CHAR(60); DECLARE userhostcur CURSOR FOR SELECT user,host FROM mysql.user; DECLARE EXIT HANDLER FOR NOT FOUND FLUSH PRIVILEGES; CREATE TABLE IF NOT EXISTS mysql.user_domain_map ( domain INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, user CHAR(80) COLLATE utf8_bin NOT NULL UNIQUE ) AUTO_INCREMENT=10, ENGINE=InnoDB; INSERT IGNORE INTO mysql.user_domain_map(user) SELECT user FROM mysql.user; OPEN userhostcur; LOOP FETCH userhostcur INTO u,h; INSERT IGNORE INTO mysql.procs_priv(Host,Db,User, Routine_name, Routine_type, Grantor, Proc_priv) VALUES(h, 'mysql', u, 'setdomain', 'PROCEDURE', CURRENT_USER(), 'Execute'); END LOOP; END;// DELIMITER ; # (re)create the user domain map CALL create_user_domain_map(); # set up event schedule CREATE EVENT update_user_domain_map ON SCHEDULE EVERY 1 DAY DO CALL create_user_domain_map(); # also set this in my.cnf so it's persistent # init_connect='CALL mysql.setdomain(current_user());' SET GLOBAL init_connect='CALL mysql.setdomain(current_user());';