Personal blog of Yzmir Ramirez

Welcome to another post 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.

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.

 

Bugs Fixed

• #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

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.

 Facebook is planning to open source LogDevice, the company’s custom-built solution for storing logs collected from distributed data centers. The company made the announcement as part of its Scale conference. Read More

 Salesforce and IBM are joining forces again as they attempt to deepen their alliance. The partnership began last March when they agreed to sell each other’s artificial intelligence products, Watson and Einstein. Today’s announcement is all about data integration. Salesforce will be adding the ability to include IBM weather data in Lightning apps, while IBM will enable customers… Read More

 Cloud storage company Box reported second quarter earnings after the bell on Wednesday. And although the company beat expectations on revenue and losses, it wasn’t enough to please Wall Street.  The stock fell almost 4% in after-hours trading.
It seems that part of the issue related to the company losing its cash flow positive status for the quarter, coming in at a negative $14.7 million. Read More

In this blog post, we’ll look at nested data structures in ClickHouse and how this can be used with PMM to look at queries.

Nested structures are not common in Relational Database Management Systems. Usually, it’s just flat tables. Sometimes it would be convenient to store unstructured information in structured databases.

We are working to adapt ClickHouse as a long term storage for Percona Monitoring and Management (PMM), and particularly to store detailed information about queries. One of the problems we are trying to solve is to count the different errors that cause a particular query to fail.

For example, for date 2017-08-17 the query:

"SELECT foo FROM bar WHERE id=?"

was executed 1000 times. 25 times it failed with error code “1212”, and eight times it failed with error code “1250”. Of course, the traditional way to store this in relational data would be to have a table "Date, QueryID, ErrorCode, ErrorCnt" and then perform a JOIN to this table. Unfortunately, columnar databases don’t perform well with multiple joins, and often the recommendation is to have de-normalized tables.

We can create a column for each possible ErrorCode, but this is not an optimal solution. There could be thousands of them, and most of the time they would be empty.

In this case, ClickHouse proposes Nested data structures. For our case, these can be defined as:

CREATE TABLE queries
(
    Period Date,
    QueryID UInt32,
    Fingerprint String,
    Errors Nested
    (
        ErrorCode String,
        ErrorCnt UInt32
    )
)Engine=MergeTree(Period,QueryID,8192);

This solution has obvious questions: How do we insert data into this table? How do we extract it?

Let’s start with INSERT. Insert can look like:

INSERT INTO queries VALUES ('2017-08-17',5,'SELECT foo FROM bar WHERE id=?',['1220','1230','1212'],[5,6,2])

which means that the inserted query during 2017-08-17 gave error 1220 five times, error 1230 six times and error 1212 two times.

Now, during a different date, it might produce different errors:

INSERT INTO queries VALUES ('2017-08-18',5,'SELECT foo FROM bar WHERE id=?',['1220','1240','1258'],[3,2,1])

Let’s take a look at ways to SELECT data. A very basic SELECT:

SELECT *
FROM queries
??????Period???QueryID???Fingerprint???Errors.ErrorCode?????????Errors.ErrorCnt??
? 2017-08-17 ?       5 ? SELECT foo  ? ['1220','1230','1212'] ? [5,6,2]         ?
? 2017-08-18 ?       5 ? SELECT foo  ? ['1220','1240','1260'] ? [3,16,12]       ?
?????????????????????????????????????????????????????????????????????????????????

If we want to use a more familiar tabular output, we can use the ARRAY JOIN extension:

SELECT *
FROM queries
ARRAY JOIN Errors
??????Period???QueryID???Fingerprint???Errors.ErrorCode???Errors.ErrorCnt??
? 2017-08-17 ?       5 ? SELECT foo  ? 1220             ?            5    ?
? 2017-08-17 ?       5 ? SELECT foo  ? 1230             ?            6    ?
? 2017-08-17 ?       5 ? SELECT foo  ? 1212             ?            2    ?
? 2017-08-18 ?       5 ? SELECT foo  ? 1220             ?            3    ?
? 2017-08-18 ?       5 ? SELECT foo  ? 1240             ?           16    ?
? 2017-08-18 ?       5 ? SELECT foo  ? 1260             ?           12    ?
???????????????????????????????????????????????????????????????????????????

However, usually we want to see the aggregation over multiple periods, which can be done with traditional aggregation functions:

SELECT
    QueryID,
    Errors.ErrorCode,
    SUM(Errors.ErrorCnt)
FROM queries
ARRAY JOIN Errors
GROUP BY
    QueryID,
    Errors.ErrorCode
??QueryID???Errors.ErrorCode???SUM(Errors.ErrorCnt)??
?       5 ? 1212             ?                 2    ?
?       5 ? 1230             ?                 6    ?
?       5 ? 1260             ?                12    ?
?       5 ? 1240             ?                16    ?
?       5 ? 1220             ?                 8    ?
?????????????????????????????????????????????????????

If we want to get really creative and return only one row per QueryID, we can do that as well:

SELECT
    QueryID,
    groupArray((ecode, cnt))
FROM
(
    SELECT
        QueryID,
        ecode,
        sum(ecnt) AS cnt
    FROM queries
    ARRAY JOIN
        Errors.ErrorCode AS ecode,
        Errors.ErrorCnt AS ecnt
    GROUP BY
        QueryID,
        ecode
)
GROUP BY QueryID
??QueryID???groupArray(tuple(ecode, cnt))???????????????????????????????
?       5 ? [('1230',6),('1212',2),('1260',12),('1220',8),('1240',16)] ?
????????????????????????????????????????????????????????????????????????

Conclusion

ClickHouse provides flexible ways to store data in a less structured manner and variety of functions to extract and aggregate it – despite being a columnar database.

Happy data warehousing!

 Dropbox continues to tune its products as it tries to tap a bigger audience within larger companies with some updates today to its collaboration tool Paper, including the ability to preview documents before loading them. As a blank slate, Paper is increasingly popular with designers you might talk to in Silicon Valley. It basically turns the process of designing and building a product spec into… Read More

www.talkingdrupal.com/154

Show Topics

 

• What is taxonomy – “the classification of something”
• Use Cases
• Hierarchical Taxonomy uses
• Implementation

Modules

• Hierarchical Select (7) – https://www.drupal.org/project/hierarchical_select
• Simple hierarchical select (7 and 8) – https://www.drupal.org/project/shs
• Client-side hierarchical select (7 and 8) – https://www.drupal.org/project/shs

Module of the Week

Mailchimp – Module of the Week

https://www.drupal.org/project/mailchimp

This module provides integration with MailChimp, a popular email delivery service. The module makes it easy for website users or visitors to control which of your email lists they want to be on (or off), lets you generate and send MailChimp email campaigns from your site, and lets you and your users view a history of emails they have been sent from MailChimp. More generally, it aspires to makes your email delivery world efficient and to make your user’s email receipt and control simple and precise. Thanks to MailChimp’s ongoing sponsorship, this module is actively maintained and regularly adding features

Hosts

Stephen Cross – www.ParallaxInfoTech.com @stephencross

Nic Laflin – www.nLightened.net @nicxvan

John Picozzi – www.oomphinc.com @johnpicozzi

Guest

www.talkingdrupal.com/154

Show Topics

 

• What is taxonomy – “the classification of something”
• Use Cases
• Hierarchical Taxonomy uses
• Implementation

Modules

• Hierarchical Select (7) – https://www.drupal.org/project/hierarchical_select
• Simple hierarchical select (7 and 8) – https://www.drupal.org/project/shs
• Client-side hierarchical select (7 and 8) – https://www.drupal.org/project/shs

Module of the Week

Mailchimp – Module of the Week

https://www.drupal.org/project/mailchimp

This module provides integration with MailChimp, a popular email delivery service. The module makes it easy for website users or visitors to control which of your email lists they want to be on (or off), lets you generate and send MailChimp email campaigns from your site, and lets you and your users view a history of emails they have been sent from MailChimp. More generally, it aspires to makes your email delivery world efficient and to make your user’s email receipt and control simple and precise. Thanks to MailChimp’s ongoing sponsorship, this module is actively maintained and regularly adding features

Hosts

Stephen Cross – www.ParallaxInfoTech.com @stephencross

Nic Laflin – www.nLightened.net @nicxvan

John Picozzi – www.oomphinc.com @johnpicozzi

Guest

 As blockchains begin to gain in popularity, a need is developing to enable them to communicate with one another. That would require a networking infrastructure to facilitate that communication, and that’s precisely what Nuco, a Toronto-based startup is trying to do with the release of the Aion blockchain network today.
Nuco CEO Matthew Spoke says that while each network is ultimately… Read More