In part 1 and part 2 of this blog series, I showed how a DBA could configure MySQL or MariaDB to automatically drop old partitions. Some readers mentioned that they would also like for new partitions to be automatically created. In this blog post, I wi…
Percona Live Europe Featured Talks: Orchestrating ProxySQL with Orchestrator and Consul with Avraham Apelbaum
Welcome to another post in our series of interview blogs for the upcoming Percona Live Europe 2017 in Dublin. This series highlights a number of talks that will be at the conference and gives a short preview of what attendees can expect to learn from the presenter.
This blog post is with Avraham Apelbaum, DBA and DevOps at Wix.com His talk is titled Orchestrating ProxySQL with Orchestrator and Consul. The combination of ProxySQL and Orchestrator solves many problems, but still requires some manual labor when the configuration changes when there is a network split (and other scenarios). In our conversation, we discussed using Consul to solve some of these issues:
Percona: How did you get into database technology? What do you love about it?
Avraham: On my first day as a soldier in a technology unit of the IDF, I received a HUGE Oracle 8 book and a very low-level design of a DB-based system. “You have one month,” they told me. I finished it all within ten days. Before that, I didn’t even know what a DB was. Today, I’m at Wix managing hundreds of databases that support 100M users!
Percona: You’re presenting a session called “Orchestrating ProxySQL with Orchestrator and Consul”. How do these technologies work together to help provide a high availability solution?
Avraham: ProxySQL is supposed to help you out with high availability (HA) and disaster recovery (DR) for MySQL servers, but it still requires some manual labor when the configuration changes – as a result of a network split, for example. Somehow all ProxySQL servers need to get the new MySQL cluster topology. So to automate all that, I added two more parts: a Consul KV store and a Consul template, which are responsible for updating ProxySQL on every architecture change in the MySQL cluster.
Percona: What is special about this combination of products that works better than other solutions? Is it right all the time, or does it depend on the workload?
Avraham: As DevOps I prefer not to do anything manually. What’s more, no one wants to wake up in the middle of the night because any one of our DB servers can fail. Most everyone, I guess, will have more than one ProxySQL server in their system at some point, so this solution can help them use ProxySql and Orchestrator.
Percona: What do you want attendees to take away from your session? Why should they attend?
Avraham: I am hoping to help people automate their HA and DR solutions. If as a result of my talk someone will earn even one minute off downtime, I’ll be happy.
Percona: What are you most looking forward to at Percona Live Europe 2017?
Avraham: In the DevOps and open source world, it’s all about sharing ideas. It was actually when I attended the talks by ProxySQL and Orchestrator’s creators that I thought of assembling it all up to solve our own problem. So I am looking forward to sharing my idea with others, and getting input from the audience so that everyone can benefit.
Want to find out more about Avraham and RDS migration? Register for Percona Live Europe 2017, and see his talk Orchestrating ProxySQL with Orchestrator and Consul. Register now to get the best price! Use discount code SeeMeSpeakPLE17 to get 10% off your registration.
Percona Live Open Source Database Conference Europe 2017 in Dublin is the premier European open source event for the data performance ecosystem. It is the place to be for the open source community as well as businesses that thrive in the MySQL, MariaDB, MongoDB, time series database, cloud, big data and Internet of Things (IoT) marketplaces. Attendees include DBAs, sysadmins, developers, architects, CTOs, CEOs, and vendors from around the world.
The Percona Live Open Source Database Conference Europe will be September 25-27, 2017 at the Radisson Blu Royal Hotel, Dublin.
Whether you choose to be a Database Administrator (DBA) or Developer, each demands certain skills and both benefit from understanding each other’s role. Some people start on one path and then decide to move to the other. The market demands are high and the pay is good for both. And, each allow you to progress and add additional responsibilities.
Take a look at some other smart career choices highlighted recently on Forbes.com in The Toughest Jobs To Fill In 2017.
- #3 Data Scientist with a median annual salary of $128,240 and a 8 year growth outlook of 16%.
- #5 Software Engineer with a median annual salary of $100,690 and a 8 year growth outlook of 17%.
- #7 Information Security Analyst with a median annual salary of $90,120 and a 8 year growth outlook of 18%.
How do you position yourself to build a career that allows you to secure one of these tough jobs to fill?
First, get started with training, experience and certification. The effort it takes to build a career that lasts and grows with you never stops. But it’s worth the effort. Read about the ability to build a long term career with what Oracle offers for Database certification in our recent article in GoCertify Big Data, Security, Database Management: Oracle Offers IT All.
Already Oracle Database certified? Let that certification help you get to your next career step. Stay up-to-date by passing this single exam Upgrade Oracle DBA 11g or 12c to 12c R2 | 1Z0-074. Nearly 40% of surveyed IT professionals said that certification helped them perform complex tasks more confidently, and a full 60% reported a positive impact on their professional image. See the full survey.
Certification shows you’re serious about your career. Gain the skills and confidence you need to continue your career path with the newest exciting Oracle Database certification. We really are excited about Oracle Database 12c R2. This is a big release because of the amount of new features, including Oracle Database Sharding, In-Memory FastStart and the new Trace File Analyzer Diagnostic tool. There are also massive improvements to Multitenancy, Performance, Security, and Availability.
Technology certainly doesn’t stand still and neither should you.
Learn more about the path to Oracle Database 12c Certification here.
Percona announces the release of Percona Server for MySQL 5.7.19-17 on August 31, 2017. Download the latest version from the Percona web site or the Percona Software Repositories. You can also run Docker containers from the images in the Docker Hub repository.
Based on MySQL 5.7.19, and including all the bug fixes in it, Percona Server for MySQL 5.7.19-17 is now the current GA release in the Percona Server for MySQL 5.7 series. Percona Server for MySQL is open-source and free – this is the latest release of our enhanced, drop-in replacement for MySQL. Complete details of this release are available in the 5.7.19-17 milestone on Launchpad.
NOTE: Percona software no longer supports Red Hat Enterprise Linux 5 (including CentOS 5 and other derivatives), Ubuntu 12.04 and older versions. These platforms have reached end of life, won’t be updated and are not recommended for use in production.
Included the Percona MyRocks storage engine
NOTE: MyRocks for Percona Server is currently experimental and not recommended for production deployments until further notice. You are encouraged to try it in a testing environment and provide feedback or report bugs.
#1708087: Added the mysql-helpers script to handle checking for missing datadir during startup. Also fixes #1635364.
Stopped providing packages for Ubuntu 12.04 due to its end of life.
#1669414: Fixed handling of failure to set O_DIRECT on parallel doublewrite buffer file.
#1705729: Fixed the postinst script to correctly locate the datadir. Also fixes #1698019.
#1709811: Fixed yum upgrade to not enable the mysqld service if it was disabled before the upgrade.
#1709834: Fixed the mysqld_safe script to correctly locate the basedir.
Other fixes: #1698996, #1706055, #1706262, #1706981
TDB-70: Removed redundant fsync of TokuDB redo log during binlog group commit flush stage. This fixes issue that prevented TokuDB to run in reduced durability mode when the binlog was enabled.
TDB-72: Fixed issue when renaming a table with non-alphanumeric characters in its name.
Release notes for Percona Server for MySQL 5.7.19-17 are available in the online documentation. Please report any bugs on the launchpad bug tracker.
Yesterday I shared results for in-memory sysbench on a large server. Today I have more results from a similar test but with more contention. The tests yesterday used 8 tables with 1M rows/table. The test here uses 1 table with 8M rows. In some ways the results are similar to yesterday. But there are some interesting differences that I will explain in part 2 (another post).tl;dr
MyRocks does worse than InnoDB 5.6.35 at low and mid concurrency for all tests except update-index. It suffers more on the read-heavy tests.
MyRocks and TokuDB are more competitive at mid and high concurrency than at low. MyRocks is faster than InnoDB 5.6.35 for many of the high concurrency tests.
InnoDB QPS at low concurrency tends to decrease after 5.6 for tests heavy on point queries but something in MySQL 5.7 made InnoDB range scans much faster.
InnoDB QPS at mid and high concurrency tends to increase after 5.6
ConfigurationEverything is the same as explained in the previous post except I used 1 table with 8M rows here and 8 tables with 1M rows/table there. Using 1 table instead of 8 means there can be more contention in the database engine on things like the InnoDB per-index mutex. A command line is:bash all.sh 1 8000000 180 300 180 innodb 1 0 /orig5717/bin/mysql none /sysbench.new
Results without chartsAll of the data is here. Below I share the QPS ratios to compare the QPS for one engine with the QPS from InnoDB from MySQL 5.6.35. The engine is slower than InnoDB 5.6.35 when the ratio is less than 1.0.QPS ratio:* rocks = myrocks / inno5635* inno = inno5717 / inno5635* toku = toku5717 / inno56351 connectionrocks inno toku0.460 0.946 0.195 update-inlist0.862 0.906 0.278 update-one1.496 1.239 0.569 update-index0.674 0.906 0.248 update-nonindex0.756 0.921 0.240 update-nonindex-special0.793 0.866 0.319 delete-only0.701 1.027 0.612 read-write.range1000.812 1.657 1.033 read-write.range100000.701 1.089 0.737 read-only.range1000.804 1.676 1.281 read-only.range100000.675 0.904 0.731 point-query0.508 0.923 0.732 random-points0.554 0.904 0.633 hot-points0.760 0.857 0.257 insert-only—– —– —–0.754 1.059 0.562 average8 connectionsrocks inno toku0.968 1.587 0.293 update-inlist1.014 0.843 0.190 update-one1.837 2.183 0.608 update-index0.879 1.090 0.307 update-nonindex0.928 1.094 0.312 update-nonindex-special0.968 1.068 0.340 delete-only0.722 1.045 0.560 read-write.range1000.814 1.626 1.108 read-write.range100000.714 1.126 0.825 read-only.range1000.811 1.639 1.255 read-only.range100000.690 0.914 0.727 point-query0.718 1.156 0.840 random-points0.966 1.354 0.832 hot-points0.859 1.104 0.310 insert-only—– —– —–0.921 1.274 0.608 average48 connectionsrocks inno toku1.679 3.087 0.788 update-inlist0.982 0.979 0.231 update-one1.222 1.986 0.606 update-index1.379 1.947 0.886 update-nonindex1.387 1.936 0.854 update-nonindex-special1.189 1.876 0.578 delete-only0.826 1.148 0.514 read-write.range1000.840 1.316 0.953 read-write.range100000.743 1.112 0.740 read-only.range1000.850 1.342 1.034 read-only.range100000.941 1.368 1.066 point-query2.042 1.445 0.686 random-points0.793 1.507 0.711 hot-points1.820 1.605 0.692 insert-only—– —– —–1.192 1.618 0.739 averageResults with chartsCharts using the data from the previous section. For some of them I truncate the x-axis to make it easier to see differences between engines.
You might already have seen the announcement earlier this summer – we made MCM 1.4.3 available for download from My Oracle Support.
The most notable changes to MCM 1.4.3 include
Significant reduction in cpu consumption when idle.
Added support for running your mysqlds with skip-networking enabled.
Here is a quick reference for MySQL Marinate:
Starting page – talks about the book, FAQs, who the course is for, what it’s all about.
How to submit homework
Lesson 1/Chapter 1 – Introduction
Lesson 2/Chapter 2 – Installing MySQL
We will be submitting the homework via GitHub. The reason for this is to have a centralized place for homework, as well as teaching how to use a revision control system and how to interact specifically with GitHub.
Learn about git at http://git-scm.com/book/en/Getting-Started-Git-Basics (You only need to read this one webpage, no need to go to the next page)
– Create a github account at www.github.com if you do not already have one.
– Install and configure git on your local machine as per https://help.github.com/articles/set-up-git
– Fork the MySQL Marinate repo as per https://help.github.com/articles/fork-a-repo
The URL for the MySQL Marinate repo is https://github.com/Sheeri/mysql-marinate
– To demonstrate that you can submit homework, open and modify the 01Intro/homework.txt file
– Then commit your work locally:git add 01Intro/homework.txt
– When you’re done, commit your work up to the github server:git push origin master
When doing homework, remember to commit locally often. I would recommend committing up to the github server whenever you finish a question or a part of a question. Pretend at any time your local machine may die; how much work are you willing to lose? (5 minutes’ worth? 3 days’ worth?) I do not look at individual commits, only the final pull request when you’re done.
When you’re done with your homework, submit a Pull Request as per https://help.github.com/articles/using-pull-requests
If you have trouble and need help, ask away in the comments!
Reference/Quick Links for MySQL Marinate
This is for chapter 1 of Learning MySQL – it’s only pages 3-8, so it is an easy one!
Homework for this week: How to Submit Homework
Notes/errata/updates for Chapter 1:
The book mentions MySQL AB, the company behind MySQL. Since the book was written, MySQL was acquired by Sun, and then Sun was acquired by Oracle. (see http://en.wikipedia.org/wiki/MySQL#History for the timeline).
Why MySQL is popular
Reference/Quick Links for MySQL Marinate
Want to learn or refresh yourself on MySQL? MySQL Marinate is the FREE virtual self-study group is for you!
MySQL Marinate quick links if you know what it is all about.
This is for beginners – If you have no experience with MySQL, or if you are a developer that wants to learn how to administer MySQL, or an administrator that wants to learn how to query MySQL, this course is what you want. If you are not a beginner, you will likely still learn some nuances, and it will be easy and fast to do. If you have absolutely zero experience with MySQL, this is perfect for you. The first few chapters walk you through getting and installing MySQL, so all you need is a computer and the book.
The format of a virtual self-study group is as follows:
Each participant acquires the same textbook (Learning MySQL, the “butterfly O’Reilly book”, published 2007). You can acquire the textbook however you want (e.g. from the libary or from a friend, hard copy or online). Yes, the book is old, but SQL dates back to at least the 1970’s and the basics haven’t changed! There are notes and errata for each chapter so you will have updated information. The book looks like this:
O’Reilly Butterfly book picture
Each participant commits to reading each chapter (we suggest one chapter per week as a good deadline), complete the exercises and post a link to the completed work.
Each participant obtains assistance by posting questions to the comments on a particular chapter.
Note: There is no classroom instruction.
How do I get started?
– Watch sheeri.com each week for the chapters to be posted.
– Get Learning MySQL
Acquire a book (the only item that may cost money). Simply acquire Learning MySQL – see if your local library has it, if someone is selling their copy, or buy it new.
When your book arrives, start your virtual learning by reading one chapter per week. Complete the exercises; if you have any questions, comments or want to learn more in-depth, that’s what the comments for!
Q: Does this cover the Percona patch set or MariaDB forks?
A: This covers the basics of MySQL, which are applicable to Percona’s patched MySQL or MariaDB builds, as well as newer versions of MySQL.
Q: What do I need in order to complete the course?
A: All you need is the book and access to a computer, preferably one that you have control over. Windows, Mac OS X or Unix/Linux will work. A Chromebook or tablet is not recommended for this course.
Q: Where can I put completed assignments?
A: Completed assignments get uploaded to github. See How to Submit Homework
Q: The book was published in 2007. Isn’t that a bit old?
A: Yes! The basics are still accurate, and we will let you know what in the book is outdated. I have contacted O’Reilly, offering to produce a new edition, and they are not interested in updating the book. We will also have optional supplemental material (blog posts, videos, slides) for those who want to learn more right away. We are confident that this self-study course will make you ready to dive into other, more advanced material.
Soak it in!
Reference/Quick Links for MySQL Marinate