Correctly configuring the use of available memory resources is one of the most important things you have to get right with MySQL for optimal performance and stability. As of MySQL 5.7, the default configuration uses a very limited amount of memory – leaving defaults is one of the worst things you can do. But configuring it incorrectly can result in even worse performance (or even crashes).
The first rule of configuring MySQL memory usage is you never want your MySQL to cause the operating system to swap. Even minor swapping activity can dramatically reduce MySQL performance. Note the keyword “activity” here. It is fine to have some used space in your swap file, as there are probably parts of the operating system that are unused when MySQL is running, and it’s a good idea to swap them out. What you don’t want is constant swapping going on during the operation, which is easily seen in the “si” and “so” columns in the vmstat output.
Example: No Significant Swapping
Example: Heavy Swapping Going
If you’re running Percona Monitoring and Management, you can also look into the Swap Activity graph in System Overview Dashboard.
If you have spikes of more than 1MB/sec, or constant swap activity, you might need to revisit your memory configuration.
MySQL Memory allocation is complicated. There are global buffers, per-connection buffers (which depend on the workload), and some uncontrolled memory allocations (i.e., inside Stored Procedures), all contributing to difficulties in computing how much memory MySQL will really use for your workload. It is better to check it by looking at the virtual memory size (VSZ) that MySQL uses. You can get it from “top”, or by running ps aux | grep mysqld.mysql 3939 30.3 53.4 11635184 8748364 ? Sl Apr08 9106:41 /usr/sbin/mysqld
The 5th column here shows VSZ usage (about 11GB).
Note that the VSZ is likely to change over time. It is often a good idea to plot it in your monitoring system and set an alert to ping you when it hits a specified threshold. Don’t allow the mysqld process VSZ exceed 90% of the system memory (and less if you’re running more than just MySQL on the system).
It’s a good idea to start on the safe side by conservatively setting your global and per connections buffers, and then increase them as you go. Many can be set online, including innodb_buffer_pool_size in MySQL 5.7.
So how do you decide how much memory to allocate to MySQL versus everything else? In most cases you shouldn’t commit more than 90% of your physical memory to MySQL, as you need to have some reserved for the operating system and things like caching binary log files, temporary sort files, etc.
There are cases when MySQL should use significantly less than 90% of memory:
- If there are other important processes running on the same server, either all the time or periodically. If you have heavy batch jobs run from cron, which require a lot of memory, you’ll need to account for that.
- If you want to use OS caching for some storage engines. With InnoDB, we recommend innodb_flush_method=O_DIRECT in most cases, which won’t use Operating System File Cache. However, there have been cases when using buffered IO with InnoDB made sense. If you’re still running MyISAM, you will need OS cache for the “data” part of your tables. With TokuDB, using OS cache is also a good idea for some workloads.
- If your workload has significant demands, Operating System Cache – MyISAM on disk temporary tables, sort files, some other temporary files which MySQL creates the need to be well-cached for optimal performance.
Once you know how much memory you want the MySQL process to have as a whole, you’ll need to think about for what purpose the memory should be used inside MySQL.The first part of memory usage in MySQL is workload related – if you have many connections active at the same time that run heavy selects using a lot of memory for sorting or temporary tables, you might need a lot of memory (especially if Performance Schema is enabled). In other cases this amount of memory is minimal. You’ll generally need somewhere between 1 and 10GB for this purpose.
Another thing you need to account for is memory fragmentation. Depending on the memory allocation library you’re using (glibc, TCMalloc, jemalloc, etc.), the operating system settings such as Transparent Huge Pages (THP) and workload may show memory usage to grow over time (until it reaches some steady state). Memory fragmentation can also account for 10% or more of additional memory usage.
Finally, let’s think about various global buffers and caching. In typical cases, you mainly only have innodb_buffer_pool_size to worry about. But you might also need to consider key_buffer_size, tokudb_cache_size, query_cache_size as well as table_cache and table_open_cache. These are also responsible for global memory allocation, even though they are not counted in bytes. Performance _Schema may also take a lot of memory, especially if you have a large number of connections or tables in the system.
When you specify the size of the buffers and caches, you should determine what you’re specifying. For innodb_buffer_pool_size, remember there is another 5-10% of memory that is allocated for additional data structures – and this number is larger if you’re using compression or set innodb_page_size smaller than 16K. For tokudb_cache_size, it’s important to remember that the setting specified is a guide, not a “hard” limit: the cache size can actually grow slightly larger than the specified limit.
For systems with large amounts of memory, the database cache is going to be by far the largest memory consumer, and you’re going to allocate most of your memory to it. When you add extra memory to the system, it is typically to increase the database cache size.
Let’s do some math for a specific example. Assume you have a system (physical or virtual) with 16GB of memory. We are only running MySQL on this system, with an InnoDB storage engine and use innodb_flush_method=O_DIRECT, so we can allocate 90% (or 14.4GB) of memory to MySQL. For our workload, we assume connection handling and other MySQL connection-based overhead will take up 1GB (leaving 13.4GB). 0.4GB is likely to be consumed by various other global buffers (innodb_log_buffer_size, Table Caches, other miscellaneous needs, etc.), which now leaves 13GB. Considering the 5-7% overhead that the InnodB Buffer Pool has, a sensible setting is innodb_buffer_pool_size=12G – what we very commonly see working well for systems with 16GB of memory.
Now that we have configured MySQL memory usage, we also should look at the OS configuration. The first question to ask is if we don’t want MySQL to swap, should we even have the swap file enabled? In most cases, the answer is yes – you want to have the swap file enabled (strive for 4GB minimum, and no less than 25% of memory installed) for two reasons:
- The operating system is quite likely to have some portions that are unused when it is running as a database server. It is better to let it swap those out instead of forcing it to keep it in memory.
- If you’ve made a mistake in the MySQL configuration, or you have some rogue process taking much more memory than expected, it is usually a much better situation to lose performance due to a swap then to kill MySQL with an out of memory (OOM) error – potentially causing downtime.
As we only want the swap file used in emergencies, such as when there is no memory available or to swap out idle processes, we want to reduce Operating System tendency to swap (echo 1 > /proc/sys/vm/swappiness). Without this configuration setting you might find the OS swapping out portions of MySQL just because it feels it needs to increase the amount of available file cache (which is almost always a wrong choice for MySQL).
The next thing when it comes to OS configuration is setting the Out Of Memory killer. You may have seen message like this in your kernel log file:
Apr 24 02:43:18 db01 kernel: Out of memory: Kill process 22211 (mysqld) score 986 or sacrifice child
When MySQL itself is at fault, it’s pretty rational thing to do. However, it’s also possible the real problem was some of the batch activities you’re running: scripts, backups, etc. In this case, you probably want those processes to be terminated if the system does not have enough memory rather than MySQL.
To make MySQL a less likely candidate to be killed by the OOM killer, you can adjust the behavior to make MySQL less preferable with the following:
echo '-800' > /proc/$(pidof mysqld)/oom_score_adj
This will make the Linux kernel prefer killing other heavy memory consumers first.
Finally on a system with more than one CPU socket, you should care about NUMA when it comes to MySQL memory allocation. In newer MySQL versions, you want to enable innodb_numa_interleave=1. In older versions you can either manually run numactl --interleave=all before you start MySQL server, or use the numa_interleave configuration option in Percona Server.
In this post, we’ll discuss how MySQL 5.7 handles the old temporal types during an upgrade.
MySQL changed the temporal types in MySQL 5.6.4, and it introduced a new feature: microseconds resolution in the TIME, TIMESTAMP and DATETIME types. Now these parameters can be set down to microsecond granularity. Obviously, this means format changes, but why is this important?Are they converted automatically to the new format?
If we had tables in MySQL 5.5 that used TIME, TIMESTAMP or DATETIME are these fields are going to be converted to the new format when upgrading to 5.6? The answer is “NO.” Even if we run mysql_upgrade, it does not warn us about the old format. If we check the MySQL error log, we cannot find anything regarding this. But the newly created tables are going to use the new format so that we will have two different types of temporal fields.How can we find these tables?
The following query gives us a summary on the different table formats:SELECT CASE isc.mtype WHEN '6' THEN 'OLD' WHEN '3' THEN 'NEW' END FORMAT, count(*) TOTAL FROM information_schema.tables AS t INNER JOIN information_schema.columns AS c ON c.table_schema = t.table_schema AND c.table_name = t.table_name LEFT OUTER JOIN information_schema.innodb_sys_tables AS ist ON ist.name = concat(t.table_schema,'/',t.table_name) LEFT OUTER JOIN information_schema.innodb_sys_columns AS isc ON isc.table_id = ist.table_id AND isc.name = c.column_name WHERE c.column_type IN ('time','timestamp','datetime') AND t.table_schema NOT IN ('mysql','information_schema','performance_schema') AND t.table_type = 'base table' AND (t.engine = 'innodb') GROUP BY isc.mtype;+--------+-------+ | FORMAT | TOTAL | +--------+-------+ | NEW | 1 | | OLD | 9 | +--------+-------+
Or we can use show_old_temporals, which will highlight the old formats during a show create table.CREATE TABLE `mytbl` ( `ts` timestamp /* 5.5 binary format */ NOT NULL DEFAULT CURRENT_TIMESTAMP, `dt` datetime /* 5.5 binary format */ DEFAULT NULL, `t` time /* 5.5 binary format */ DEFAULT NULL ) DEFAULT CHARSET=latin1
MySQL can handle both types, but with the old format you cannot use microseconds, and the default DATETIME takes more space on disk.Can I upgrade to MySQL 5.7?
Of course you can! But when mysql_upgrade is running it is going to convert the old fields into the new format by default. This basically means an alter table on every single table, which will contain one of the three types.
Depending on the number of tables, or the size of the tables, this could take hours – so you may need to do some planning..... test.t1 error : Table rebuild required. Please do "ALTER TABLE `t1` FORCE" or dump/reload to fix it! test.t2 error : Table rebuild required. Please do "ALTER TABLE `t2` FORCE" or dump/reload to fix it! test.t3 error : Table rebuild required. Please do "ALTER TABLE `t3` FORCE" or dump/reload to fix it! Repairing tables mysql.proxies_priv OK `test`.`t1` Running : ALTER TABLE `test`.`t1` FORCE status : OK `test`.`t2` Running : ALTER TABLE `test`.`t2` FORCE status : OK `test`.`t3` Running : ALTER TABLE `test`.`t3` FORCE status : OK Upgrade process completed successfully. Checking if update is needed. Can we avoid this at upgrade?
We can run alter tables or use pt-online-schema-schange (to avoid locking) before an upgrade, but even without these preparations we can still avoid incompatibility issues.
The following query returns the schema and the table names that still use the old formats.SELECT CASE isc.mtype WHEN '6' THEN 'OLD' WHEN '3' THEN 'NEW' END FORMAT, t.schema_name, t.table_name FROM information_schema.tables AS t INNER JOIN information_schema.columns AS c ON c.table_schema = t.table_schema AND c.table_name = t.table_name LEFT OUTER JOIN information_schema.innodb_sys_tables AS ist ON ist.name = concat(t.table_schema,'/',t.table_name) LEFT OUTER JOIN information_schema.innodb_sys_columns AS isc ON isc.table_id = ist.table_id AND isc.name = c.column_name WHERE c.column_type IN ('time','timestamp','datetime') AND t.table_schema NOT IN ('mysql','information_schema','performance_schema') AND t.table_type = 'base table' AND (t.engine = 'innodb');+--------+--------------+------------+ | FORMAT | table_schema | table_name | +--------+--------------+------------+ | OLD | test | t | | OLD | test | t | | OLD | test | t | | NEW | sys | sys_config | +--------+--------------+------------+ 4 rows in set (0.03 sec) mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.11-4 | +-----------+ 1 row in set (0.00 sec)
As we can see, we’re using 5.7 and table “test.t” still has the old format.
The schema:CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `t1` time DEFAULT NULL, `t2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `t3` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1mysql> select * from t; +----+----------+---------------------+---------------------+ | id | t1 | t2 | t3 | +----+----------+---------------------+---------------------+ | 1 | 20:28:00 | 2016-04-09 01:41:58 | 2016-04-23 22:22:01 | | 2 | 20:28:00 | 2016-04-09 01:41:59 | 2016-04-23 22:22:02 | | 3 | 20:28:00 | 2016-04-09 01:42:01 | 2016-04-23 22:22:03 | | 4 | 20:28:00 | 2016-04-09 01:42:03 | 2016-04-23 22:22:04 | | 5 | 20:28:00 | 2016-04-09 01:42:08 | 2016-04-23 22:22:05 | +----+----------+---------------------+---------------------+
Let’s try to insert a new row:mysql> insert into `t` (t1,t3) values ('20:28','2016:04:23 22:22:06'); Query OK, 1 row affected (0.01 sec) mysql> select * from t; +----+----------+---------------------+---------------------+ | id | t1 | t2 | t3 | +----+----------+---------------------+---------------------+ | 1 | 20:28:00 | 2016-04-09 01:41:58 | 2016-04-23 22:22:01 | | 2 | 20:28:00 | 2016-04-09 01:41:59 | 2016-04-23 22:22:02 | | 3 | 20:28:00 | 2016-04-09 01:42:01 | 2016-04-23 22:22:03 | | 4 | 20:28:00 | 2016-04-09 01:42:03 | 2016-04-23 22:22:04 | | 5 | 20:28:00 | 2016-04-09 01:42:08 | 2016-04-23 22:22:05 | | 6 | 20:28:00 | 2016-04-09 01:56:38 | 2016-04-23 22:22:06 | +----+----------+---------------------+---------------------+ 6 rows in set (0.00 sec)
It was inserted without a problem, and we can’t see any related info/warnings in the error log.Does the Replication work?
In many scenarios, when you are upgrading a replicaset, the slaves are upgraded first. But will the replication work? The short answer is “yes.” I configured row-based replication between MySQL 5.6 and 5.7. The 5.6 was the master, and it had all the temporal types in the old format. On 5.7, I had new and old formats.
I replicated from old format to old format, and from old format to new format, and both are working.Conclusion
Before upgrading to MySQL 5.7, tables should be altered to use the new format. If it isn’t done, however, the upgrade is still possible without altering all the tables – the drawbacks are you cannot use microseconds, and it takes more space on disk. If you had to upgrade to 5.7, however, you could change the format later using alter table or pt-online-schema-schange.
I recently had a client, Life360, that wanted to upgrade from Percona Server 5.5 to Percona Server 5.6, and implement GTID in their high transaction environment. They had co-masters and multiple read slaves.
Daniel from Life360 saw Orchestrator and was very interested. So here is how he setup Orchestrator in his own words:
I then moved to install the Orchestrator server, Orchestrator backend on RDS, and deploy the clients on the slaves and masters in our Amazon VPC MySQL instances.
Once the server setup was done, the clients were auto-detected through CNAME discovery of the masters, and the agents talked to the server (it took a while as CNAMES wasn’t working as expected, but that’s fixed in the new server version).
We were pretty amazed at the number of actions you can do through orchestrator itself, such as: moving slaves to a different master through drag and drop, enabling GTID on a node with the push of a button, setting up GTID based failover, taking LVM snapshots using Orchestrator Agent, etc.
We went ahead and tested the master change on drag and drop, and after a few successful attempts, we even brought it back to where it was initially. After those tests, we were pretty confident that we could leverage Orchestrator as one of our main tools to assist in the coming upgrade.
Here is a screenshot of the initial setup:
Manjot: Once Daniel had Orchestrator setup, he wanted to leverage it to help with the MySQL upgrade. We set out to create a plan that worked within his constraints and still kept best practices in mind.
First, we installed Percona Server 5.6 fresh on our dedicated backup slave. That first 5.6 slave was created with MyDumper to achieve forward compatibility and not have any legacy tablespaces. Since MyDumper was already installed with the Percona Backup Service that Life360 has, this was fairly easy to accomplish.
The MyDumper slave rebuild works in the following way:
To take a mydumper backup:
- Go to your desired backups directory
- Install mydumper (sudo apt-get install mydumper)
- mydumper -t 8 -L mydumper.log –compress
- Make sure MyDumper is installed: sudo apt-get install mydumper
- Copy the MyDumper backups over to a backups dir
- Export your BACKUP_DIR as env var
- Run this to restore with MyLoader (from https://gist.github.com/Dnile/4658b338d4a101cbe2eeb5080ebddf8e):
#!/usr/bin/env sh cd $BACKUP_DIR export DESTHOST=127.0.0.1 export BACKUP_DIR=/vol_mysql/backups mysqld --skip-grant-tables & for i in `ls -1 *-schema.dump.gz | cut -d'-' -f1`; do mysql -h $DESTHOST -e "CREATE DATABASE IF NOT EXISTS $i"; zcat $i-schema.dump.gz | mysql -h $DESTHOST $i; zcat $i-schema-post.dump.gz | mysql -h $DESTHOST $i; done /usr/bin/myloader --host=$DESTHOST --directory=$BACKUP_DIR --enable-binlog --threads=10 --queries-per-transaction=20 -v 3 chown -R mysql:mysql /var/lib/mysql/
Once the first 5.6 slave was caught up, we used Xtrabackup to backup 5.6 and then restored to each slave, cycling them out of the read slave pool one at a time.
Once all the slaves were upgraded, we created a new 5.6 master and had it replicate off our primary 5.5 master.
Then we moved all of the slaves to replicate off the new 5.6 master.
Life360 had long cron jobs that ran on the second 5.5 master. We moved the cron applications to write to the primary 5.5 master, and locked all tables. We then stopped replication on the second co-master. Daniel stopped MySQL and decommissioned it.
We then moved all application writes to the new 5.6 master. While Orchestrator can use external scripts to move IPs, we used a manual process here to change application DSNs and HAProxy configuration.
On the 5.5 master that remained, we used Orchestrator to set it to read only.
Daniel says this didn’t do a whole lot to get rid of connections that were still open on this server.
On the new master, we used the stop slave and reset slave buttons in the Orchestrator panel so it would no longer slave from the old master.
Once some of the thousands of connections had moved to the new master, we stopped MySQL on the 5.5 master, which took care of the rest and the application “gracefully” reconnected to the new 5.6 master.
There was some write downtime, as some connections did not drop off until they were forced to because php-fpm refused to let go. There is also always a high volume of transactions in this environment.
At this point our topology looks like this (ignore the globe icons for now):
But as always Daniel wanted MOAR. It was time for GTID. While we could have done this during the upgrade, Life360 wanted to manage risk and not make too many production changes at one time.
We followed Percona’s guide, Online GTID Deployment, but used Orchestrator to shuffle the old and new masters and toggle read_only on and off. This made our job a lot easier and faster, and saved us from any downtime.
The globes in the topology screenshot above show that the slaves are now using GTID replication.
Orchestrator makes upgrades and changes much easier than before, just use caution and understand what it is doing in the background.
Well, that is it for Percona Live 2016! This year was bigger and better than last year and included more sponsors, speakers, technologies, and talks than ever before. Once again we’d like to thank everybody who participated, and the entire open source community in general: without your dedication and spirit, none of this would be possible.
At the prize ceremony, many of the exhibitors gave away a prize to the people who filled out and turned in a completed conference passport, as well as some gifts for those who completed surveys for the talks. Prizes ranged from $50 gift certificates, signed copies of Bill Nye’s book Unstoppable, an Amazon Echo, GoPro cameras, an Oculus Rift, a Playstation, and more. All the winners left happy (except maybe the guy who got the Mr. Spock socks, although I would have been pleased).
Thanks for coming, and we’ll see you in Amsterdam (and next year)!
Below is a video of the ceremony in full (it’s about 15 minutes long).
It’s almost time for the closing remarks and passport prize give away at Percona Live 2016, but still the talks keep coming. I was able to quickly pop into a lecture on MongoDB Security with Stephane Combaudon, MySQL Consultant at Slice Technologies.
Stephane went over some of the reported security issues with MongoDB and explained that MongoDB has good security features. Data security is a concern for most people, and recent reports in the news show that significant amounts of user details stored in MongoDB are easily accessible. This doesn’t mean that MongoDB can’t secure your data. As Stephane explained, MongoDB can be correctly secured – but some features are not enabled by default. In this session, we learned the main items that need to be checked to get a robust MongoDB deployment. Those items include:
- Network security: Stopping people from connecting to your MongoDB instances
- Operating system security: stopping people from connecting to MongoDB and taking control of your servers
- User security: how to make sure that users can only interact with specific portions of the data
I had a chance to quickly speak with Stephane after his talk:
We’re starting to wind down here at Percona Live 2016, but there are still talks to see even this late in the conference! One of the talks I saw was Sometimes “Less is More” for dash-boarding and graphing with Michael Blue, Senior DBA at Big Fish Games.
In this talk, Michael discussed how monitoring specific metrics can be more help than all the metrics. In a monitor/measure everything environment, to get the big picture sometimes “Less is More.” There are good monitoring tools available for DBAs, with tons of metrics to measure, but at a larger scale it seems impossible to get that big picture view of your environment without spending much of your time going over all the metrics your graphing, sifting through emails of false positives alerts, and reading tons of your cron job outputs. In the talk, Michael explained the approaches he took at Big Fish to create better dashboards for all audiences without visual overload, which helped the DBAs find potential issues that were not caught via conventional monitoring. This session included:
This session included:
- The basics of visualization
- Picking metrics to measure at scale
- How they leverage custom dashboards, Graphite, and the MySQL Enterprise Monitor
I had a chance to speak with Michael after his talk:
Wednesday night at Percona Live 2016 was reserved for fun, fun, fun! Once again, the Percona Live Game Night proved to be a popular and amazing event. There were more games this year than last, as well as food, drinks, and lots of friendly competition!
This year, besides the ever-crowd-pleasing Meltdown Challenge, there were Segway Races, pool, foosball, shuffleboard, Wii Boxing, Pac-Man Attack, a shootout gallery, darts, as well as virtual reality stations and a death-defying trampoline.
You can see Percona’s CEO Peter Zaitsev demonstrating how you use it, pro-level:
Below are some more photos of this outstanding night:
Coed boxing: guys, you need to improve your skills!
Some very intense foosball action!
This Pac-Man Attack reminds me of 1983!
Keep an eye on your wallets, gentlemen, I detect a hustle.
For those who like less effort with their trampoline, virtual reality.
A little social lubrication.
Happy attendees (must have stopped at the previous picture).
Hmm, that guy looks a bit confused. Must be too much tech talk for one day!
I’d stay away from this table. Just saying.
Thanks to everybody who came out and participated in an awesome night! We’ll see you all next year!
We wrapped up Thursday morning’s Percona Live 2016 keynotes (April 21) with The MySQL Community Award Ceremony. The MySQL Community Awards is a community-based initiative, with the goal of publicly recognizing contributors to the MySQL ecosystem. The idea is to publicly recognize contributors to the MySQL ecosystem. The entire process of discussing, voting and awarding is controlled by an independent group of community members, typically based on past winners or their representatives, as well as known contributors. It is a self-appointed, self-declared, self-making-up-the-rules-as-it-goes committee. It is also very aware of the importance of the community; a no-nonsense, non-political, adhering to tradition, self-criticizing committee.
The categories can be found here.
We celebrated the members of the open source community, and some big open source contributors from the past year! The winners this year were:
Community Contributors of the Year:
- Bill Karwin, Senior Database Architect and Software Engineer at SchoolMessenger
- Domas Mituzas, Small Data Engineer at Facebook
- Yoshinori Matsunobu, Data Engineer at Facebook
Application of the Year:
Corporate Contributor of the Year:
Congratulation to all the winners! You can watch the whole ceremony below (it’s about 15 minutes long).
We’re heading into the final day here at Percona Live 2016! People are looking a little tired, but still excited for some excellent talks today. Once again the day started off with two great keynote lectures. Read to the end for an important announcement!
Peter discussed what makes the open source community so vibrant and powerful, and why it is essential to preserve that spirit. Data is critical to the success of your business. You rely on your database and its supporting systems to power the applications that drive your business. These systems must be reliable, scalable, efficient – and increasingly, open source. With the complexity of today’s web applications and the databases, systems and hardware that support them, organizations must use the right open source tools and technology for each job – without getting locked into a proprietary solution. With Percona, customers are assured a choice in technology options that are completely open source (and include enterprise features). We help our customers find the right technology for their specific needs, rather than sell a one-size-fits-all product. Percona is a true open source partner that helps you optimize your database performance to better run your business.
Patrick McFadin, Chief Evangelist at DataStax
Patrick discussed how cloud applications can help you to develop the applications you need in your business, but also outline why the cloud isn’t a panacea for every business issue. Database engineers have had to support the crazy dreams of application developers since the beginning of the internet. Patrick says it’s time to take back the power! He believes that Apache Cassandra is the tool that can help you eliminate downtime or span your data around the world with ease. Deploying to the cloud isn’t always easy, but Cassandra might be able to give your application developers the best chance they can get and sleep easy at night.
Post MongoDB World New York Conference, June 30th, Hilton Mid-Town Manhattan
Peter also made an important announcement: Percona and ObjectRocket/Rackspace will be sponsoring a free post-MongoDB World Community Event! The event will take place on Thursday, June 30th at the Hilton Mid-Town Manhattan.
All are welcome.
Don’t miss out on this amazing opportunity to share ideas and get insights after MongoDB World.
The second day of Percona Live 2016 is wrapping up, but I still wanted to get to one last lecture. Fortunately, there was a great one available: What’s New in MySQL with Geir Høydalsvik, Senior Software Development Director at Oracle, and Simon Mudd, DBA at booking.com.
MySQL 5.7 is an extremely exciting new version of the world’s most popular open source database that is 2x faster than MySQL 5.6, while also improving usability, manageability, and security. MySQL 5.7, now in GA, is the result of almost three years of development and brings improvements to all areas of the product. Some key enhancements include:
- MySQL improved InnoDB scalability and temporary table performance, enabling faster online and bulk load operations, and more.
- They’ve added native JSON support.
- For increased availability and performance, they’ve included multi-source replication, multi-threaded slave enhancements, online GTIDs, and enhanced semi-sync replication.
- They’ve added numerous new monitoring capabilities so the Performance Schema for better insights, reduced the footprint and overhead, and significantly improved ease of use with the new SYS Schema.
- They are fulfilling “secure by default” requirements, and many new MySQL 5.7 features will help users keep their database secure.
- They have rewritten large parts of the parser, optimizer, and cost model. This has improved maintainability, extendability, and performance.
- GIS is completely new in MySQL 5.7, as is InnoDB spatial indexes, the use of Boost.Geometry, and increased completeness and standard compliance.
I had a chance to talk with Geir and Simon after their lecture:
The afternoon sessions at Percona Live 2016 are just as intriguing as the morning sessions. I’m trying to hit as many as I can, especially talks on interesting and upcoming technologies that are improving deployments and manageability. One of the talks I saw in this vein was MySQL and Docker Strategies, given by Patrick Galbraith, Senior Systems Engineer at Hewlett Packard, and Giuseppe Maxia, Quality Assurance Director at VMware.
Docker is a great new project for managing containers. Containers provide operating system resource isolation and allocation benefits as virtual machines, yet are more lightweight and allow you a lot of flexibility in how you can design your application and database architecture. This talk covered many of the useful things one can do with Docker and MySQL, such as:
- Build ready-to-launch containers that are “batteries included” MySQL servers
- Backup and restore MySQL databases using a container strategy that allows both containers and host systems access to the same data
- Network containers across hosts
- Implement container clustering solutions such as CoreOS, Kubernetes, and Mesos and how each of these solutions solves a similar requirement
- Automate containerized MySQL using Ansible
Patrick and Giuseppe also provided summary details and a demonstration of their recent contribution to Kubernetes (a Galera cluster application), which showed how one can have a quickly deployable MySQL synchronous replication cluster in minutes and use it with other Kubernetes applications.
I had a chance to speak with Giuseppe after the talk:
Check out the Percona Live 2016 schedule for more talks!
The second day at Percona Live 2016 is moving along, and there is just too much to see to get to it all. Percona acquired Tokutek last year and with it a MongoDB platform. I was interested in learning more about MongoDB this year at Percona Live 2016, and I’m not disappointed! There is at least one MongoDB lecture ever hour, along with a MongoDB 101 track led by Percona experts who take attendees through the fundamentals of MongoDB tools and techniques.
One of the MongoDB lectures I attended was lead by Kimberly Wilkins, Principal Engineer/Databases at ObjectRocket by Rackspace. Her lecture was titled Indexing Strategies and Tips for MongoDB. In this presentation, Kimberly covered general indexing, options, and strategies. With the new indexing improvements and changes in MongoDB 3.2, there are more indexing options than ever before – including automated index builds and partial indexes. After this talk, I was able to walk away with real world examples of index use cases for running at scale and for high performance that have been tested with multiple high-level clients in small to very large MongoDB databases.
After the talk, I was able to chat with Kimberly for a minute:
It’s another packed day here at Percona Live 2016, with many, many database topics under discussion. Some technical, some strategical, and some operational. One such talk I sat in on was given by Ernie Souhrada, Database Engineer and Bit Wrangler at Pinterest. His talk was called Operational Buddhism: Building Reliable Services From Unreliable Components.
In it he discussed how the rise of utility computing has revolutionized much about the way organizations think about infrastructure and back-end serving systems, compared to the “olden days” of physical data centers. But success is still driven by meeting your SLAs. If services are up and sufficiently performant, you win. If not, you lose. In the traditional data center environment, fighting the uptime battle was typically driven by a philosophy Ernie calls “Operational Materialism.” The primary goal of OM is preventing failures at the infrastructure layer, and mechanisms for making this happen are plentiful and well-understood, many of which boil down to simply spending enough money to have at least N+1 of anything that might fail.
Ernie contends that in the cloud, Operational Materialism cannot succeed. Although the typical cloud provider tends to be holistically reliable, there are no guarantees that any individual virtual instance will not randomly or intermittently drop off the network or be terminated outright. Since we still need to keep our services up and running and meet our SLAs, we need a different mindset that accounts for the fundamentally opaque and ephemeral nature of the public cloud.
Ernie presented an alternative to OM, a worldview that he referred to as “Operational Buddhism.” Like traditional Buddhism, OB has Four Noble Truths:
- Cloud-based servers can fail at any time for any reason
- Trying to prevent this server failure is an endless source of suffering for DBAs and SREs alike
- Accepting the impermanence of individual servers, we can focus on designing systems that are failure-resilient, rather than failure-resistant
- We can escape the cycle of suffering and create a better experience for our customers, users, and colleagues.
To illustrate these concepts with concrete examples, he discussed how configuration management, automation, and service discovery help Pinterest to practice Operational Buddhism for both stateful (MySQL, HBase) and stateless (web) services. He also talked about some of the roads not taken, including the debate over Infrastructure-as-a-Service (IaaS) vs. Platform-as-a-Service (PaaS).
I was able to have a quick chat with Ernie after the talk:
We’ve started the second full day at Percona Live 2016, and again we had some pretty impressive and fascinating keynote speakers. This morning featured four keynote talks from some pretty heavy hitters in our industry:
Anurag Gupta, Vice President of Big Data Services, Amazon Web Services
AWS Big Data Services: Reimagining Big Data Processing in the Cloud
- Amazon DynamoDB – a NoSQL service
- Amazon Redshift – a petabyte-scale data warehouse service
- Amazon EMR – an elastic map-reduce service
- Amazon Aurora – a massively scalable relational database service
He also discussed best practices in understanding customer desires and behavior from Big Data and how to achieve 1:1 customer experience at scale.
- Reddy Chagam
Principal Engineer & Chief SDS Architect, Intel
- Thomas Hazel
Founder, CTO and Chief Scientist, Deep Information Sciences
- Peter Zaitsev
- Mark Callaghan|
Software Engineer, Database Infrastructure, Facebook
- Kyle Bader
Sr Solution Architect, Red Hat
Moderated by Matt Yonkovit, Percona’s Vice President of Professional Services, this panel discussion focused on the fact that larger and larger datasets are moving to the cloud, creating new challenges and opportunities in handling such workloads. As such, new technologies, revamped products, and a never ending stream of idea’s follow in the wake of this advance to keep pace. Even as these solutions attempt to improve the performance and manageability of cloud-based data, the question is are they enough? The panel brought our several areas that need to be addressed to keep ahead of the “data crisis,” including:
- Optimizing Storage
- The trifecta of storage/analytics/processing
- Security and privacy
In this talk, Tomas Ulin discussed how, now that MySQL 5.7 is GA, it’s delivered major improvements in performance, scalability and manageability. He explored how MySQL can deliver on the promises of NoSQL, while keeping all the proven benefits of SQL. This lecture allowed the audience to better understand the MySQL development priorities and what the future holds for the world’s most popular open source database.
In this lecture, we learned about RocksDB, MyRocks, and MongoRocks. RocksDB is an embedded database engine. MyRocks and MongoRocks are RocksDB storage engines for MySQL and MongoDB. All of this is open-source. Facebook started these projects to get better performance, efficiency and compression with fast storage devices like flash-based SSD but they also work great with disks. Mark explained how and why MyRocks provides better performance, efficiency and compression using real and synthetic workloads. In one case, they got 2X better compression compared to compressed InnoDB for a production workload. Mark (and Facebook) expect MyRocks to become the web-scale storage engine for MySQL.
Those were the morning lectures today! And there is more to come! Check out our schedule here.
It’s been a long first day at Percona Live 2016, filled with awesome insight and exciting topics. I was able to get to one more lecture before calling quits. For the final talk I saw today I listened to Igor Canadi, Software Engineer at Facebook, Inc., discuss Running MongoRocks in Production.
Facebook has been running MongoDB 3.0 with RocksDB storage engine (MongoRocks) at Parse since March of last year (2015). At this talk, they wanted to share some lessons learned about running MongoRocks in production. Igor was able to provide some interesting war stories and talk about performance optimization. Along with a little bit about RocksDB internals and which counters are most important to watch for.
RocksDB compares favorably to both the MMAP and WiredTiger storage engines when it comes to large write workloads.
The audience came away from the talk ready to get their feet wet with MongoRocks.
Below is a quick chat I had with Igor about RocksDB and MongoDB:
Percona Live 2016 had a great first day, with an impressive number of speakers and topics. I was able to attend a session in the afternoon with Luis Soares, Principal Software Engineer at Oracle, on High Availability Using MySQL Group Replication. MySQL Group Replication is a MySQL plugin under development that brings together group communication techniques and database replication, providing both high availability (HA) and a multi-master update everywhere replication solution.
At MySQL Group Replication’s core is a set of group communication primitives that act as the building blocks to creating reliable, consistent and dependable messaging between the servers in the group. This allows the set of MySQL servers to coordinate themselves and act as a consistently replicated state machine. As a consequence, the group itself is fault-tolerant, and so is the service it provides (i.e., the MySQL database service). The plugin also provides multi-master update-anywhere characteristics with automatic conflict detection and handling.
In this discussion, we learned about the technical details of the MySQL Group Replication plugin, and discussed how this fits into the overall picture of the MySQL HA. For instance, how it can be deployed together with MySQL Router to automate load balancing and failover procedures. We also discovered the newest enhancements and how to leverage them when deploying and experimenting with this plugin.
Listen to a brief chat I had with Luis on MySQL Group Replication:
Percona Live is humming along, and we had quite a whirlwind keynote session this morning. Bill Nye the Science Guy gave an amazing talk, Bill Nye’s Objective – Change the World, on how the process of science and critical thinking can help us not only be positive about the challenges we face in our world today, but also help us to come up with the big ideas we need to solve them. He discussed many topics, from how his parents met, their involvement in science (his mother worked on the Enigma Code in World War 2!), working at Boeing as an engineer, his involvement with Carl Sagan, and how he has worked to help harness renewable energy through solar panels, a solar water heater, and skylights at his own home in Studio City.
Bill Nye is also the CEO of The Planetary Society. The Planetary Society, founded in 1980 by Carl Sagan, Bruce Murray, and Louis Friedman, works to inspire and involve the world’s public in space exploration through advocacy, projects, and education. Today, The Planetary Society is the largest and most influential public space organization group on Earth.
After the talks, I was able to quickly catch Bill Nye and ask him a few questions.
We’re are rapidly moving through day one of the Percona Live Data Performance Conference, and I’m surrounded by incredibly smart people all discussing amazing database techniques and technology. The depth of solutions represented here, and the technical know-how needed to pull them off is astounding!
This afternoon I was able to catch Jenni Snyder, MySQL DBA at Yelp deliver her talk on Let Robots Manage your Schema (without destroying all humans). While vaguely frightening, it was a fascinating talk on how automating schema changes helped Yelp’s development.
You’re probably already using automation to build your application, manage configuration, and alert you in case of emergencies. Jenni asks what’s keeping you from doing the same with your MySQL schema changes? For Yelp, the answer was “lots of things”. Today, Yelp uses Liquibase to manage their schema changes, pt-online-schema-change to execute them, and Jenkins to ensure that they’re run in all of their environments. During this session, she explained the history of MySQL schema management at Yelp, and how hard it was for both developers and DBAs.
Below is a video of her summarizing her team’s efforts and outcomes.
Percona Live 2016: Performance of Percona Server for MySQL on Intel Server Systems using HDD, SATA SSD, and NVMe SSD as Different Storage Mediums
We’re moving along on the first day at Percona Live 2016, and I was able to attend a lecture from Intel’s Ken LeTourneau, Solutions Architect at Intel, on Performance of Percona Server for MySQL on Intel Server Systems using HDD, SATA SSD, and NVMe SSD as Different Storage Mediums. In this talk, Ken reviewed some benchmark testing he did using MySQL on various types of storage mediums. This talk looked at the performance of Percona Server for MySQL for Linux running on the same Intel system, but with three different storage configurations. We looked at and compared the performance of:
- a RAID of HDD,
- a RAID of SATA SSD, and
- a RAID of NVMe SSD
In the talk, Ken covered the hardware and system configuration and then discuss results of TPC-C and TPC-H benchmarks, as well as the overall system costs including hardware and software, and cost per transaction/query based on overall costs and benchmark results.
I got a chance to speak with Ken after his talk, check it out below!
Today was day one of the Percona Live Data Performance Conference! The day began with some excellent keynote speakers and exciting topics, and the packed room was eager to hear what our speakers had to say!
Peter Zaitsev, CEO, Percona
Percona Opening Keynote
Peter kicked it off today by thanking the sponsors, the speakers, the Percona Live committee, and the attendees for contributing and participating in this year’s event. It has grown and changed quite a bit from its initial creation. Peter emphasized how this a gathering of members of a community, one that changes and adapts, and discusses and debates many different points of views and opinions. No longer is just a conference about MySQL, but now includes MongoDB, Cassandra, and many other solutions and products that are all a part of the open source community. The purpose of the conference is to provide open and diverse opinions, quality content, a technical focus, and useful and practical ideas and solutions.
Chad Jones, Chief Strategy Officer, Deep Information
Transcending database tuning problems: How machine learning helps DBAs play more ping pong
Next up was Chad Jones discussing how just as machine learning enables businesses to gain competitive advantage through predictive analytics, by looking deeper into the data stack we find the need for the same predictive capabilities for MySQL tuning. With over 10^13 possible tuning permutations, some requiring reboots or a rebuild, DBAs spend way too much time on MySQL tuning for a point-in-time situation that changes constantly. He demonstrated how unsupervised machine learning based on resource, workload and information modeling could predictively and continuously tune databases. DBAs can transcend the tuning game, saving precious time to work on important things, like improving your mad ping pong skills!
Bill Nye, The Planetary Society, CEO
Bill Nye’s Objective – Change the World
Finally this morning, we were treated to an outstanding lecture from world-renown scientist and media personality Bill Nye the Science Guy. Bill spent his time discussing his life, how he came to love science, and the ability it brings to understand the world. His experiences as an engineer at Boeing helped him appreciate the value in investing time and money into excellent design strategies and processes. Through the power of critical thinking and science, we can embrace optimism in a world that has many touch challenges. Bill Nye fights to raise awareness of the value of science, critical thinking, and reason. He hopes that the data he brings will help inspire people everywhere to change the world!
Those were the morning lectures today! Such a great set of speakers, I can’t wait for tomorrow! Check out our schedule here.