We’ve spent a lot of time in the last few months organizing Percona Live Europe Dublin. I want to thank all the speakers, sponsors and attendees for helping us to pull off yet another great event. While we’ll provide some perspectives, thoughts and feedback soon, all the early mornings, jam-packed meetings and the 4 am bedtimes means I’ll probably talk about this event in my next column!
In the meantime, save the date for Percona Live Santa Clara, April 23-25 2018. The call for papers will open in October 2017.Releases
Percona’s website keeps track of community events, so check out where to listen to a Perconian speak. My upcoming appearances are:
The Percona Live Europe 2017 conference has been going strong for two days, and I’ve been to a bunch of presentations. An excellent one was High-Performance JSON – PostgreSQL Vs. MongoDB with Wei Shan Ang and Dominic Dwyer of GlobalSign.
This talk was very engaging and well attended and provided some enlightening stats from their experiments and tests. For GlobalSign, applications have to be both super fast and consistent, and achieving that balance requires dedicated and detailed testing and development. For example, while one configuration might offer incredibly fast throughput if the tradeoff is that there are dropouts from time to time – even for a second or two – it’s not a solution that would meet GlobalSign’s needs. Or as Wei Shan put it, using such a solution might lead to a few discussions with management!
There were lively questions from the floor that carried on outside the room well after the session. Since they presented the talk as PostgreSQL vs. MongoDB, there were advocates for both. These were handled with cool aplomb by both guys.
I caught up with them after the session:
Percona announces the release of Percona Monitoring and Management 1.3.1. This release only contains bug fixes related to usability.
For install and upgrade instructions, see Deploying Percona Monitoring and Management.Bug fixes
Percona Monitoring and Management (PMM) is an open-source platform for managing and monitoring MySQL and MongoDB performance. Percona developed it in collaboration with experts in the field of managed database services, support and consulting.
PMM is a free and open-source solution that you can run in your own environment for maximum security and reliability. It provides thorough time-based analysis for MySQL and MongoDB servers to ensure that your data works as efficiently as possible.
A live demo of PMM is available at pmmdemo.percona.com.
We’re always happy to help! Please provide your feedback and questions on the PMM forum.
If you would like to report a bug or submit a feature request, please use the PMM project in JIRA.
One of the widely discussed technologies at Percona Live Europe was the logistics of running MySQL in containers. Containers – particularly Docker – have become a hot topic, so there was a good-sized crowd on day one of the conference for Ashraf Sharif, Senior Support Engineer with Severalnines. He presented his talk “MySQL on Docker: Containerizing the Dolphin”.
During his presentation, Ashraf shared some recommendations for best practices when setting out on containerizing MySQL. He sees the trend of moving to containers as a progression from the use of virtual hosts.
After his talk on day one of the Percona Live Europe conference, I caught up with Ashraf and asked about his presentation. I was interested in which concepts are most important for ensuring a smoother implementation.
If you enjoy this brief presentation and would like to find out more, then you might like to subscribe to Ashraf’s blog on the Severalnines website where he regularly posts insights on his special interests of system scalability and high availability.
Percona is pleased to announce the General Availability of Query Analytics (QAN) from Percona Monitoring and Management 1.3.0 (PMM). This new release introduces the support of MongoDB.
In general, the purpose of QAN is to help detect queries that consume the most amount of time inside of your database server. It provides detailed real-time analysis of queries so that your application can work with data efficiently. In the Percona Monitoring and Management 1.3.0 release, QAN adds support for MongoDB.
MongoDB is conceptually different from relational database management systems, such as MySQL or MariaDB. Relational database management systems store data in separate tables that represent single entities, and you may need to link records from multiple tables to represent a complex object. MongoDB, on the other hand, allows a more flexible approach to data storage and stores all essential information pertaining to a complex object together.
In QAN, the difference between the monitored systems is transparent, and you can analyze queries in the same way regardless of the technology used in the database engine. QAN presents the monitored data in both visual and numeric form. The performance-related characteristics appear as plotted graphics.
To start working with QAN, click the Query Analytics button on the Percona Monitoring and Management 1.3.0 home page. Select a MongoDB database from the list of available database instances at the top of the page. The list of the top ten queries opens below. These are the queries that take the longest time to run. To view more queries, click the Load next 10 queries button below the list.
You can limit the list of available queries to only those that you are interested in by using the Query Filter field next to the database selection button.
In the Query Filter field, you can enter a query ID or its fingerprint. The ID is a unique signature of a query. A fingerprint is a simplified form of your query: it replaces all specific values with placeholders. You can enter only a fragment of the fingerprint to make the search less restrictive.
The queries that match your criterion appear below the Query Filter field in a summary table.
In the summary table represents each query as a row, with each column referring to an essential attribute of queries. The Load, Count, and Latency columns visualize their values graphically along with summaries in the numeric form.
The load attribute is the percentage of the amount of time expressed as a percentage value that the MongoDB server spent executing a specific query. The count attribute informs how often the given query appeared in the search traffic. The latency attribute is the amount of time that it takes to run the query and return its result.
If you hover the cursor over one of these attributes in a query, you can see a concrete value appear over your cursor. Move the cursor along the plotted line to watch how the value is changing. Click one of the queries to select it. QAN displays detailed information about the query. The detailed information includes the metrics specific to the query type. It also contains details about the database and tables that the query uses.
Hope this helps you explore your MongoDB queries and get better performance from them!
Percona Live Europe provides open source professionals with an opportunity to discuss how various technologies get used in order to solve database problems. Alexander Zaitsev of LifeStreet/Altinity gave one such session: Building Multi-Petabyte Data Warehouses with ClickHouse.
LifeStreet needed to scale their real-time ad analytics platform to multiple petabytes. They evaluated and used a number of open source and commercial solutions, but most solutions were not efficient enough or too expensive. When Yandex released ClickHouse to open source, LifeStreeet quickly realized its potential and started an implementation project. It took a bit of time and effort, but it finally worked out and became an excellent way to address scale in LifeStreet’s database environment.
In this presentation, LifeStreet/Altinity Director of Engineering Alexander Zaitsev talked about their experiences from an application developer’s viewpoint: what worked well and not so well, what challenges they had to overcome as well as share the best practices for building large-scale platforms based on ClickHouse.
I got a chance to talk with Alexander in the video below. Check it out!
Percona Live Europe 2017 keeps providing excellent sessions with useful information on great open source database technologies. Yandex’s Clickhouse was one of these technologies that was well covered at the conference this year. There were several talks that featured Clickhouse this year. I was able to attend two of them.
The first was a discussion of Clickhouse internals.
ClickHouse is an open source DBMS for high-performance analytics, originally developed at Yandex for the needs of Yandex.Metrica web analytics system. It is capable of storing petabytes of data and processing billions of rows per second per server, all while ingesting new data in real-time. In his talk A Quick Tour of Clickhouse Internals, Yandex’s Alex Zatelepin discussed architectural decisions made by ClickHouse, their consequences from the point of view of an application developer and how to determine if ClickHouse is a good fit for a particular use case.
He covered the following topics:
In a second Percona Live Europe talk, Aleksei Milovidov of Yandex presented ClickHouse: A DBMS for Interactive Analytics at Scale. In this presentation, Aleksei walked through Yandex’s development of ClickHouse, and how its iterative approach to organizing data storage resulted in a powerful and extremely fast open source system.
You can see my chat with both of these presenters in the video below. Check it out!
Percona Server for MongoDB is an enhanced, open-source, fully compatible, highly scalable, zero-maintenance downtime database that supports the MongoDB v3.2 protocol and drivers. It extends MongoDB with MongoRocks, Percona Memory Engine and PerconaFT storage engine, as well as enterprise-grade features like External Authentication, Audit Logging, Profiling Rate Limiting, and Hot Backup at no extra cost. The software requires no changes to MongoDB applications or code.
NOTE: The PerconaFT storage engine is deprecated as of 3.4. It is no longer supported and isn’t available in higher version releases.
This release is based on MongoDB 3.2.16 and includes the following additional changes:
The Percona Server for MongoDB 3.2.16-3.7 release notes are available in the official documentation.
One such talk I got to sit in was on Spatial Data in MySQL 8.0, given by Norvald Ryeng of Oracle.
MySQL 8.0 is still in development, but we already see a lot of improvement in GIS support. The latest development release comes with support for around 5000 different spatial reference systems, improved standard compliance and a lot of new functionality. How does it all work, and how can it be used to build applications?
This talk started with the basics of GIS and spatial data in MySQL: geometries, data types, functions, storage and indexes. Then Norvald walked through a demo of how all the parts fit together to support a GIS web application. We also got a sneak peek into the future, including what to do right now to prepare for the upgrade to MySQL 8.0.
Whether you’re currently storing or planning to store spatial data in MySQL, this talk was for you. It covers the topics in a way that is accessible to both novices and more advanced GIS users.
After the talk, I had a chance to interview Norvald, and here is the video:
Black coffee was flowing this morning for day two Percona Live Europe 2017 Keynotes after many of the delegates had spent a good few hours the night before enjoying Irish hospitality at the Community Dinner.
So today Laurie Coffin, Chief Marketing Officer for Percona, introduced proceedings for day two and later also took to the stage for a Q&A session with authors Laine Campbell and Charity Majors. More on that later…State of the Dolphin
First up Geir Høydalsvik, Development Director for MySQL at Oracle, delivered juicy tidbits of what to expect in MySQL 8.0 (beyond what you see in the current Developer Milestone Releases). He gave a comprehensive overview of plans and current developments to what had become an almost full house – despite the night before’s revelries.Many Faces of Continuent Tungsten
MC Brown brought the conference up to date with the latest Tungsten developments, as well as some thoughts for the future. He described the wide-ranging deployments of Tungsten out in the field and his thoughts on how it might look going forward.Database Reliability Engineering
Laurie Coffin took to the stage to quiz Laine Campbell, Senior Director Production Engineering at OpsArtisan, and Charity Majors, CEO of Honeycomb Q&A about the newly released O’Reilly title: Database Reliability Engineering. The book focuses on designing and operating resilient database systems and uses open-source engines such as MySQL, PostgreSQL, MongoDB, and Cassandra as examples throughout.Database Performance in High Traffic Environments
Pepper.com is purposely different than other platforms that list daily deals. Around the clock, the community seeks and finds the best offers in fashion, electronics, traveling and much more. Pavel described how Pepper optimizes their database performance to make sure their web applications remain responsive and meet users’ expectations.
Pepper.com, the world’s largest community deal platform, has selected Percona to manage its open source database performance.
Pepper.com’s around-the-clock community seeks and finds the best offers in fashion, electronics, traveling and much more. With 500 million page views, over 25 million users and over 65,000 user-submitted deals per month across communities in America, Europe and Asia, Pepper has quickly risen to be the largest community deal platform worldwide.
When Pepper.com’s primary MySQL database administrator left the company, Pepper decided to shift to a managed service to maintain uptime and responsiveness. Having previously attended Percona Live Europe, the premier European open source database conference, as well as being avid readers of the Percona Database Performance Blog, the Pepper team turned to Percona for open source database remote managed service expertise.
“Guaranteeing database performance is key to making sure our web applications are responsive and up-to-date,” said Pavel Genov, Head of Software Development at Pepper.com. “Percona Care Ultimate helps us to achieve these objectives.”
Pepper was already using Percona Server for MySQL. Following a Percona Database Performance Audit to review the Pepper.com environment, architecture and setup, Percona XtraBackup was deployed to provide online non-blocking, tightly compressed, highly secure backups.
Check out the case study on Pepper.com and Percona’s engagement to improve and manage Pepper’s database environment.
After yesterday’s very successful tutorial day, everyone looked forward to an inspiring first day of the conference and the Percona Live Europe 2017 keynotes. There were some fantastic keynotes delivered, and some excellent sessions scheduled.
Note. These videos are as shot, and the slides will be superimposed very soon so you can enjoy the full conference experience!
Laurie Coffin, Chief Marketing Office of Percona, opened proceedings with a welcome address where she paid tribute to Jaako Pesonen: a true champion of open source and friend to our community who passed away just this month. He will be missed.Championing Open Source Databases
Peter Zaitsev delivers his keynote “Championing Open Source Databases”
Laurie then introduced Peter Zaitsev, CEO of Percona, who delivered his keynote “Championing Open Source Databases.” He reiterating Percona’s commitment to remaining an unbiased champion of the open source database ecosystem.
At Percona, we see a lot of compelling open source projects and trends that we think the community will find interesting, and following Peter’s keynote there was a round of lightning talks from projects that we think are stellar and deserve to be highlighted.Percona Monitoring and Management Demo
Michael Coburn delivers his keynote “Percona Monitoring and Management Demo”
The second keynote was by Percona Product Manager Michael Coburn on Percona Monitoring and Management. How can you optimize database performance if you can’t see what’s happening? Percona Monitoring and Management (PMM) is a free, open source platform for managing and monitoring MySQL, MariaDB, MongoDB and ProxySQL performance. PMM uses Metrics Monitor (Grafana + Prometheus) for visualization of data points, along with Query Analytics, to help identify and quantify non-performant queries and provide thorough time-based analysis to ensure that your data works as efficiently as possible. Michael provided a brief demo of PMM.MySQL as a Layered Service: How to use Proxy SQL to Control Traffic and Scale-Out
René Cannaò delivers his keynote “MySQL as a Layered Service: How to use Proxy SQL to Control Traffic and Scale-Out”
The next keynote was from René Cannaò, Founder at ProxySQL. The inability to control the traffic sent to MySQL is one of the worse nightmares for a DBA. Scaling out and high availability are only buzz words if the application doesn’t support such architectures. ProxySQL is able to create an abstraction layer between the application and the database: controlling traffic at this layer hides the complexity of the database infrastructure from the application, allowing both HA and scale out. The same layer is able to protect the database infrastructure from abusive traffic, acting as a firewall and cache, and rewriting queries.Realtime DNS Analytics at Cloudflare with ClickHouse
Tom Arnfeld delivers his keynote “Realtime DNS Analytics at Cloudflare with ClickHouse”
Cloudflare operates multiple DNS services that handle over 100 billion queries per day for over 6 million internet properties, collecting and aggregating logs for customer analytics, DDoS attack analysis and ad-hoc debugging. Tom Arnfeld, Systems Engineer at Cloudflare, talks briefly in his keynote on how Cloudflare securely and reliably ingests these log events, and uses ClickHouse as an OLAP system to both serve customer real-time analytics and other queries.Why Open Sourcing our Database Tooling was a Smart Decision
Shlomi Noach delivers his keynote “Why Open Sourcing our Database Tooling was a Smart Decision”
Drawing from experience at GitHub, Senior Infrastructure Engineer Shlomi Noach, argues in his keynote that open sourcing your database infrastructure/tooling is not only a good, but a smart business decision, that may reward you in unexpected ways. Here are his observations.MyRocks at Facebook and a Roadmap
Yoshinori Matsunobu delivers his keynote “MyRocks at Facebook and a Roadmap”
A major objective of creating MyRocks at Facebook was replacing InnoDB as the main storage engine, with more space optimisations, and without big migration pains. They have made good progress and extended their goals to cover more use cases. In this keynote, Yoshinori Matsunobu, Production Engineer at Facebook, shares MyRocks production deployment status and MyRocks development plans.Prometheus for Monitoring Metrics
Brian Brazil, CEO of Prometheus, delivers his keynote “Prometheus for Monitoring Metrics”
From its humble beginnings in 2012, the Prometheus monitoring system has grown a substantial community with a comprehensive set of integrations. Brian Brazil, CEO of Prometheus, provides an overview of the core ideas behind Prometheus and its feature set.
That sums up today’s keynotes. Stay tuned for the next set tomorrow!
Percona announces the release of Percona Monitoring and Management 1.3.0 on September 26, 2017.
Percona Monitoring and Management 1.3.0 introduces basic support for the MyRocks storage engine. There is a special dashboard in Metrics Monitor that presents the essential metrics of MyRocks as separate graphs. Also, Metrics Monitor graphs now feature on-demand descriptions that remain visible as long as hover over them.
For example, this graph helps you visualize MyRocks database operations of Next and Seek attributes:
There are many improvements to QAN (Query Analytics) both in the user interface design and in its capabilities. In this release, QAN starts supporting all types of MongoDB queries. For example, if you need to limit the list of available queries to only those that you are interested in, use the Query Filter field next to the database selection button:
Orchestrator is not enabled by default because leaving it in a non-configured state was confusing to users. It is still possible to enable it along with the docker run command.
For install and upgrade instructions, see Deploying Percona Monitoring and Management.New Features
I’m pretty sure most of you have seen an UPDATE statement matching rows returned from a SELECT query:update ibreg set k=1 where id in (select id from ibcmp where id > 90000);
This query, when executed with autocommit=1, is normally harmless. However, this can have bad effects when combined with other statements in the same transaction that result in holding the shared locks from the SELECT query. But first, let me explain why the SELECT query would hold locks in the first place.
Due to InnoDB’s ACID properties, to make sure that the outer UPDATE statement has a consistent view of the matching rows from the SELECT query the server has to acquire a shared lock on those rows. No other thread should modify those matching rows to maintain consistency within the transaction. To demonstrate, let’s take two transactions executed in specific order below:mysql1> begin; mysql1> update ibreg set k=1 where id in (select id from ibcmp where id > 90000); mysql2> begin; mysql2> delete from ibcmp where id > 90000;
By the time the second session executes, it will be in a LOCK WAIT state (as confirmed from INFORMATION_SCHEMA):mysql1> select * from information_schema.innodb_trx G *************************** 1. row *************************** trx_id: 3932449 trx_state: LOCK WAIT trx_started: 2017-09-06 00:20:05 trx_requested_lock_id: 3932449:13:1354:31 trx_wait_started: 2017-09-06 00:20:05 trx_weight: 2 trx_mysql_thread_id: 9 trx_query: delete from test.ibcmp where id > 90000 trx_operation_state: starting index read ... mysql1> select * from information_schema.innodb_locks G *************************** 1. row *************************** lock_id: 3932449:13:1354:31 lock_trx_id: 3932449 lock_mode: X lock_type: RECORD lock_table: `test`.`ibcmp` lock_index: PRIMARY lock_space: 13 lock_page: 1354 lock_rec: 31 lock_data: 90001 *************************** 2. row *************************** lock_id: 3932174:13:1354:31 lock_trx_id: 3932174 lock_mode: S lock_type: RECORD lock_table: `test`.`ibcmp` lock_index: PRIMARY lock_space: 13 lock_page: 1354 lock_rec: 31 lock_data: 90001
Information_Schema.INNODB_LOCKS confirms that our first transaction has held a shared lock on the rows that matched the SELECT queries from the first transaction. This can be bad for a number of reasons:
If you really need the consistency of the view between the table being read from and the table getting updated, the lock is necessary and unavoidable. Avoiding the deadlocks and lock wait timeouts can be minimized, but not totally avoided.
On the other hand, if you’re not worried about view consistency, there are two ways you can avoid such problems: by using variables or making sure the SELECT becomes a transient read inside the transaction (i.e., by dumping the results into an OUTFILE).mysql1> begin; mysql1> select group_concat(id) into @ids from ibcmp where id > 90000; mysql1> update ibreg set k=1 where id in (@ids); mysql2> begin; mysql2> delete from ibcmp where iid > 90000;
The first method is bound by the group_concat_max_len variable. If you think you will only have a few resulting IDs that fit into group_concat_max_len, this is a good solution.mysql1> begin; mysql1> select id into outfile '/tmp/id.csv' from ibcmp where id > 90000; mysql1> create temporary table t (id int unsigned not null) engine=innodb; mysql1> load data infile '/tmp/id.csv' into table t; mysql1> update ibreg inner join t on ibreg.id = t.id; mysql2> begin; mysql2> delete from ibcmp where id > 90000;
The second approach is only meant to overcome the limitation of the GROUP_CONCAT method.
Again, these two approaches only work if you do not care if the result of the SELECT queries changes on the other table between the BEGIN statement and UPDATE within the transaction.
Welcome to the first day of the Percona Live Open Source Database Conference Europe 2017: Tutorials day! Technically the first day of the conference, this day focused on provided hands-on tutorials for people interested in learning directly how to use open source tools and technologies.
Today attendees went to training sessions taught by open source database experts and got first-hand experience configuring, working with, and experimenting with various open source technologies and software.
The first full day (which includes opening keynote speakers and breakout sessions) starts Tuesday 9/26 at 9:15 am.
Some of the tutorial topics covered today were:
Michael Coburn, Percona
This was a hands-on tutorial covering how to set up monitoring for MySQL database servers using the Percona Monitoring and Management (PMM) platform. PMM is an open-source collection of tools for managing and monitoring MySQL and MongoDB performance. It provides thorough time-based analysis for database servers to ensure that they work as efficiently as possible.
We learned about:
Rene Cannao, ProxySQL
ProxySQL is an open source proxy for MySQL that can provide HA and high performance with no changes in the application, using several built-in features and integration with clustering software. Those were only a few of the features we learned about in this hands-on tutorial.
Jason Terpko, ObjectRocket
Antonios Giannopoulos, ObjectRocket
This tutorial guided us through the many considerations when deploying a sharded cluster. It covered the services that make up a sharded cluster, configuration recommendations for these services, shard key selection, use cases, and how data is managed within a sharded cluster. Maintaining a sharded cluster also has its challenges. We reviewed these challenges and how you can prevent them with proper design or ways to resolve them if they exist today.
Peter Zaitsev, Percona
InnoDB is the most commonly used storage engine for MySQL and Percona Server for MySQL. It is the focus of most of the storage engine development by the MySQL and Percona Server for MySQL development teams.
In this tutorial, we looked at the InnoDB architecture, including new feature developments for InnoDB in MySQL 5.7 and Percona Server for MySQL 5.7. Peter explained how to use InnoDB in a database environment to get the best application performance and provide specific advice on server configuration, schema design, application architecture and hardware choices.
Peter updated this tutorial from previous versions to cover new MySQL 5.7 and Percona Server for MySQL 5.7 InnoDB features.
Join us tomorrow for the first full day of the Percona Live Open Source Database Conference Europe 2017!
For general inquiries, please send us your question and someone will contact you.