Personal blog of Yzmir Ramirez

 Enterprise software maker Icertis has scored $6 million in series A funding led by Greycroft Partners and Fidelity Growth Partners India. Based in Pune, India and Bellevue, WA, Icertis will use the capital to expand and market its cloud-based contract management software. Read More

 Once upon a time, when you wanted to secure something of value, you put it in a vault and distributed the keys. Today, when you want to secure software credentials especially as you move across services, you can use a digital secrets manager and distribute the virtual keys. HashiCorp announced an early release of an open source secrets manager today appropriately called Vault. The tool… Read More

I often talk with people who are very interested in the features of Percona XtraDB Cluster (PXC) such as synchronous and parallel replication, multi-node writing and high availability. However some get confused when operating a real PXC cluster because they do not fully realize the implications of these features. So here is a fun way to test your PXC knowledge: try to solve these 12 questions related to PXC! (you will find the answers at the end of the post.)

Workload

1. With Galera 3.x, support for MyISAM is experimental. When can we expect to have full MyISAM support?
a. This will never happen as Galera is designed for transactional storage engines.
b. This is planned for Galera 4.0.

2. Why aren’t all workloads a good fit for PXC?
a. Execution plans can change compared to a regular MySQL server, so performance is sometimes not as good as with a regular MySQL server.
b. Large transactions and write hotspots can create performance issues with Galera.

3. For workloads with a write hot spot, writing on all nodes to distribute the load is a good way to solve the issue.
a. True
b. False

4. Optimistic locking is used in a PXC cluster. What does it mean?
a. When a transaction starts on a node, locks are only set on this node but never on the remote nodes.
b. When a transaction starts on a node, locks are only set on the remote nodes but never on the local node.
c. Write conflict detection is built-in, so there is no need to set locks at all.

Replication

5. Galera implements virtually synchronous replication. What does it mean?
a. A transaction is first committed locally, and then it is committed on all remote nodes at the same exact point in time.
b. Transactions are replicated synchronously, but they are applied asynchronously on remote nodes.
c. Replication is actually asynchronous, but as it is faster than MySQL replication, so marketing decided to name it ‘virtually synchronous’.

6. When the receive queue of a node exceeds a threshold, the node sends flow control messages. What is the goal of these flow control messages?
a. They instruct the other nodes that they must pause processing writes for some time, to allow the slow node to catch up.
b. The other nodes trigger an election and if they have quorum they will evict the slow node.
c. The messages can be used by monitoring systems to detect a slow node, but they have no effect.

7. When you change the state of a node to Donor/Desynced, what happens?
a. The node stops receiving writes from the other nodes.
b. The node intentionally replicates writes at a slower pace, this is roughly equivalent to a delayed replica when using MySQL replication.
c. The node keeps working as usual, but it will not send flow control messages if its receive queue becomes large.

High Availability

8. You should always use an odd number of nodes, because with an even number (say 4 or 6), the failure of one node will create a split-brain situation.
a. True
b. False

9. With a 3-node cluster, what happens if you gracefully stop 2 nodes?
a. The remaining node can process queries normally.
b. The remaining node is up but it stops processing queries as it does not have quorum.

Operations

10. If a node has been stopped for less than 5 minutes, it will always perform an IST.
a. True: SST is only performed after a node crash, never after a regular shutdown.
b. False: it depends on the gcache size.

11. Even with datasets under 5GB, the preferred SST method is xtrabackup-v2 not mysqldump.
a. True
b. False

12. Migration from a master-slave setup to a PXC cluster always involves a downtime to dump and reload the database.
a. True, because MySQL replication and Galera replication are incompatible.
b. False, one node of the PXC cluster can be set up as an asynchronous replica of the old master.

Solutions

1. a      2. b      3. b
4. a      5. b      6. a
7. c      8. b      9. a
10. b    11. a    12. b

The post Test your knowledge: Percona XtraDB Cluster (PXC) quiz appeared first on MySQL Performance Blog.

 The online artisans’ marketplace sells some unique products and has some unusual needs when it comes to IT. The company sets itself apart in a number of ways. Instead of using cloud services like many young organizations, it mostly shuns them preferring to bring as much as possible in-house. Etsy, which recently had a hugely successful IPO, is remarkably conservative in some ways, yet… Read More

I have recently seen several cases when performance for MySQL queries on a single table was terrible. The reason was simple: the wrong indexes were added and so the execution plan was poor. Here are guidelines to help you optimize various kinds of single-table queries.

Disclaimer: I will be presenting general guidelines and I do not intend to cover all scenarios. I am pretty confident that you can find examples where what I am writing does not work, but I am also confident that it will help you most of the time. Also I will not discuss features you can find in MySQL 5.6+ like Index Condition Pushdown to keep things simple. Be aware that such features can actually make a significant difference in query response time (for good or for bad).

What an index can do for you

An index can perform up to 3 actions: filter, sort/group and cover. While the first 2 actions are self-explanatory, not everyone may know what a ‘covering index’ is. Actually that’s very easy. The general workflow for a basic query is:
1. Use an index to find matching records and get the pointers to data.
2. Use the pointers to the corresponding data.
3. Return records

When a covering index can be used, the index already covers all fields requested in the query, so step #2 can be skipped and the workflow is now:
1. Use an index to find matching records
2. Return records

In many cases, indexes are small and can fit in memory while data is large and does not fit in memory: by using a covering index, you can avoid lots of disk operations and performance can be order of magnitudes better.
Let’s now look at different common scenarios.

Single equality

This is the most basic scenario:

SELECT * FROM t WHERE c = 100

The idea is of course to add an index on (c). However note that if the criteria is not selective enough, the optimizer may choose to perform a full table scan that will certainly be more efficient.
Also note that a frequent variation of this query is when you only select a small subset of fields instead of all fields:

SELECT c1, c2 FROM t WHERE c = 100

Here it could make sense to create an index on (c, c1, c2) because it will be a covering index. Do not create an index on (c1, c2, c)! It will still be covering but it will not be usable for filtering (remember that you can only use a left-most prefix of an index to filter).

Multiple equalities

SELECT * FROM t WHERE c = 100 and d = 'xyz'

It is also very easy to optimize: just add an index on (c, d) or (d, c).

The main mistake here is to add 2 indexes: one on (c) and one on (d). Granted, MySQL is able to use both indexes with the index_merge algorithm, but it is almost always a very bad option.

Equality and inequality

SELECT * FROM t WHERE c > 100 and d = 'xyz'

Here we must be careful because as long as we are using a column with an inequality, this will prevent us from using further columns in the index.

Therefore if we create an index on (d, c), we will be able to filter both on c and d, this is good.
But if we create an index on (c, d), we will only be filtering on c, which is less efficient.

So unlike the situation when you have equalities, order of columns matters when inequalities are used.

Multiple inequalities

SELECT * FROM t WHERE c > 100 and b < 10 and d = 'xyz'

As we have 2 inequalities, we already know that we will not be able to filter on both conditions (*). So we have to make a decision: will we filter on (d, b) or on (d, c)?

It is not possible to tell which option is better without looking at the data: simply choose the column where the inequality is the most selective. The main point is that you must put the column(s) with an equality first.

(*) Actually there is a way to ‘filter’ on both inequalites: partition on b and add an index on (d, c) or partition on c and add an index on (d, b). The details are out of the scope of this post but it might be an option for some situations.

Equalities and sort

SELECT * FROM t WHERE c = 100 and d = 'xyz' ORDER BY b

As mentioned in the first paragraph, an index can filter and sort so this query is easy to optimize. However like for inequalities, we must carefully choose the order of the columns in the index: the rule is that we will filter first, and then sort.

With that in mind, it is easy to know that (c, d, b) or (d, c, b) will be good indexes while (b, c, d) or (b, d, c) are not as good (they will sort but not filter).

And if we have:

SELECT c1, c2 FROM t WHERE c = 100 and d = 'xyz' ORDER BY b

We can create a super efficient index that will filter, sort and be covering: (c, d, b, c1, c2).

Inequality and sort

We have 2 main variations here. The first one is:

SELECT * FROM t WHERE c > 100 and d = 'xyz' ORDER BY b

Two options look reasonable in this case:
1. filter on d and sort by b.
2. filter on d and c.

Which strategy is more efficient? It will depend on your data, so you will have to experiment.

The second variation is:

SELECT * FROM t WHERE c > 100 ORDER BY b

This time we have no equality so we have to choose between filtering and sorting. Most likely you will choose filtering.

Conclusion

Not all cases have been covered in this post but you can already see that in some cases you will create poor MySQL indexes if you are not careful. In a future post, I will present a case that can look confusing at first sight but which is easy to understand if you already know everything mentioned here.

The post Indexing 101: Optimizing MySQL queries on a single table appeared first on MySQL Performance Blog.

 Apigee, a company that helps organizations build and manage API connectors, went public this morning at $17 per share, briefly jumping to $20 per share before closing at $16.68, down 1.88 percent in its first day of regular trading. After-hours, the company is flat.
The company sold 5,115,000 shares in its IPO, raising $87M according to its CEO Chet Kapoor. It trades under the symbol… Read More

With highlights that included news of Percona’s acquisition of Tokutek, a lively keynote discussion with Apple legend Steve “Woz” Wozniak, scores of technical sessions, tutorials and a festive MySQL community dinner and game night, last week’s Percona Live MySQL Conference and Expo had something for everyone.

More than 1,200 attendees from around the world converged upon Santa Clara, California for the event, which included for the first time a two-day OpenStack Live track alongside a two-day crash course for aspiring MySQL DBAs called “MySQL 101.”

With the Tokutek acquisition, announced last Tuesday by Peter Zaitsev, Percona becomes the first company to offer both MySQL and MongoDB software and solutions. Percona has also taken over development and support for TokuDB® and TokuMX™ as well as the revolutionary Fractal Tree® indexing technology that enables those products to deliver improved performance, reliability and compression for modern Big Data applications. Peter talks in depth about the technologies in last week’s post.

Also on Tuesday, “Woz” and Jim Doherty, EVP of Sales & Marketing, talked about a range of issues associated with technology and innovation.

The Apple co-founder and inventor of the Apple I and Apple II computers also shared his thoughts on what influenced him growing up, his approach to problem solving, childhood education, artificial intelligence and more. (You can view the entire 45-minute conversation by clicking on the Woz to the left.)

Tweets of #PerconaLive during the conference hit nearly 2,000 – many with photos that are worth looking at (see all of the tweets here).

The other keynotes included:

• Peter Zaitsev’s opening keynote
• Peter Zaitsev announces Percona’s acquisition of Tokutek
• Oracle’s Tomas Ulin’s keynote was titled “Celebrating MySQL“
• Facebook’s Harrison Fisk who spoke about “Polyglot Persistence @Facebook“
• A panel discussion on the next disruptive technology titled “What comes after the Cloud and Big Data?“
• “WebScaleSQL: The Power of Collaboration,” by Facebook’s Steaphan Greene
• MariaDB’s Patrik Sallner keynote was titled “1+1>2“
• VMware’s Robert Hodges answered the question, “What Has the Cloud Done for My Data Lately?“
• Rob Young presented “Percona’s Take on Smart Data Management – What’s Next for Products and Services“
• And Tesora’s Amrith Kumar spoke about “Building Databases for the ‘-as-a-Service’ World“

In addition to the above keynotes, all of the sessions were recorded and will be available soon for registered attendees who had access to them at the conference. Most of the slides will be available soon to everyone via the Percona Live 2015 site. Just click the session you’re interested in and scroll to the bottom of the page to view the slides.

Congratulations go out to all of the MySQL Community Awards 2015 winners with a special thanks to Shlomi Noach and Jeremy Cole for running the awards program.

Special thanks also goes out to the Percona Live and OpenStack Live 2015 conference committees, which together organized a fantastic week of events. And of course none of the events would have been possible without our generous Percona Live sponsors and OpenStack Live sponsors.

Finally, a round of applause for Percona’s director of conferences, Kortney Runyan, for her monumental efforts organizing the event. Kortney could not have succeeded without the support of our multiple service vendors including Ireland Presentations, Carleson Production Group, Tricord, the Hyatt Santa Clara, and the Santa Clara Convention Center, to name just a few.

Also announced last week was the new venue for Percona Live Europe, which will be held September 21-22 in Amsterdam. Percona Live Amsterdam promises to be bigger and better than ever. The Call for Papers is open for this exciting new venue so be sure to submit your proposals now.

See you in Amsterdam this September! And be sure to save the date for Percona Live 2016 – April 18-21 at the the Hyatt Santa Clara, and the Santa Clara Convention Center.

P.S. For more Percona Live and OpenStack Live 2015 photos…

• Day 1 (April 13)
• Day 2 (April 14)
• Day 3 (April 15)
• Day 4 (April 16)

The post Percona Live & OpenStack Live 2015 wrap-up appeared first on MySQL Performance Blog.

With highlights that included news of Percona’s acquisition of Tokutek, a lively keynote discussion with Apple legend Steve “Woz” Wozniak, scores of technical sessions, tutorials and a festive MySQL community dinner and game night, last week’s Percona Live MySQL Conference and Expo had something for everyone.

More than 1,200 attendees from around the world converged upon Santa Clara, California for the event, which included for the first time a two-day OpenStack Live track alongside a two-day crash course for aspiring MySQL DBAs called “MySQL 101.”

With the Tokutek acquisition, announced last Tuesday by Peter Zaitsev, Percona becomes the first company to offer both MySQL and MongoDB software and solutions. Percona has also taken over development and support for TokuDB® and TokuMX™ as well as the revolutionary Fractal Tree® indexing technology that enables those products to deliver improved performance, reliability and compression for modern Big Data applications. Peter talks in depth about the technologies in last week’s post.

Also on Tuesday, “Woz” and Jim Doherty, EVP of Sales & Marketing, talked about a range of issues associated with technology and innovation.

The Apple co-founder and inventor of the Apple I and Apple II computers also shared his thoughts on what influenced him growing up, his approach to problem solving, childhood education, artificial intelligence and more. (You can view the entire 45-minute conversation by clicking on the Woz to the left.)

Tweets of #PerconaLive during the conference hit nearly 2,000 – many with photos that are worth looking at (see all of the tweets here).

The other keynotes included:

• Peter Zaitsev’s opening keynote
• Peter Zaitsev announces Percona’s acquisition of Tokutek
• Oracle’s Tomas Ulin’s keynote was titled “Celebrating MySQL“
• Facebook’s Harrison Fisk who spoke about “Polyglot Persistence @Facebook“
• A panel discussion on the next disruptive technology titled “What comes after the Cloud and Big Data?“
• “WebScaleSQL: The Power of Collaboration,” by Facebook’s Steaphan Greene
• MariaDB’s Patrik Sallner keynote was titled “1+1>2“
• VMware’s Robert Hodges answered the question, “What Has the Cloud Done for My Data Lately?“
• Rob Young presented “Percona’s Take on Smart Data Management – What’s Next for Products and Services“
• And Tesora’s Amrith Kumar spoke about “Building Databases for the ‘-as-a-Service’ World“

In addition to the above keynotes, all of the sessions were recorded and will be available soon for registered attendees who had access to them at the conference. Most of the slides will be available soon to everyone via the Percona Live 2015 site. Just click the session you’re interested in and scroll to the bottom of the page to view the slides.

Congratulations go out to all of the MySQL Community Awards 2015 winners with a special thanks to Shlomi Noach and Jeremy Cole for running the awards program.

Special thanks also goes out to the Percona Live and OpenStack Live 2015 conference committees, which together organized a fantastic week of events. And of course none of the events would have been possible without our generous Percona Live sponsors and OpenStack Live sponsors.

Finally, a round of applause for Percona’s director of conferences, Kortney Runyan, for her monumental efforts organizing the event. Kortney could not have succeeded without the support of our multiple service vendors including Ireland Presentations, Carleson Production Group, Tricord, the Hyatt Santa Clara, and the Santa Clara Convention Center, to name just a few.

Also announced last week was the new venue for Percona Live Europe, which will be held September 21-22 in Amsterdam. Percona Live Amsterdam promises to be bigger and better than ever. The Call for Papers is open for this exciting new venue so be sure to submit your proposals now.

See you in Amsterdam this September! And be sure to save the date for Percona Live 2016 – April 18-21 at the the Hyatt Santa Clara, and the Santa Clara Convention Center.

P.S. For more Percona Live and OpenStack Live 2015 photos…

• Day 1 (April 13)
• Day 2 (April 14)
• Day 3 (April 15)
• Day 4 (April 16)

The post Percona Live & OpenStack Live 2015 wrap-up appeared first on MySQL Performance Blog.

 Salesforce.com announced a new set of human resources tools today designed to improve employee engagement. The tools have been mostly adapted from existing Salesforce products and represent a significant departure from Salesforce’s traditional sales and marketing targets.
“What we are doing is taking the best of our technologies and allowing customers to turn them inward and… Read More

 Box announced ‘Box Developer Edition‘ at its yearly BoxDev event today. The new approach allows developers to run their own Box “instance” inside of their application — their very own Box, in other words. This turns Box on its head in a sense because instead of running the Box application, and building apps on top of it, you are building something and embedding… Read More