Showing entries 1 to 10 of 470
10 Older Entries »
Displaying posts with tag: Insight for DBAs (reset)
How to Setup and Troubleshoot Percona PAM with LDAP for External Authentication

In this blog, we’ll look at how to setup and troubleshoot the Percona PAM authentication plugin.

We occasionally get requests from our support clients on how to get Percona Server for MySQL to authenticate with an external authentication service via LDAP or Active Directory. However, we normally do not have access to client’s infrastructure to help troubleshoot these cases. To help them effectively, we need to setup a testbed to reproduce their issues and guide them on how to get authentication to work. Fortunately, we only need to install Samba to provide an external authentication service for both LDAP and AD.

In this article, I will show you how to (a) compile and install Samba, (b) create a domain environment with Samba, (c) add users and groups to this domain and (d) get Percona Server …

[Read more]
The mysqlpump Utility

In this blog, we’ll look at the




 is a utility that performs logical backups (which means backing up your data as SQL statements instead of a raw copy of data files). It was added in MySQL Server version 5.7.8, and can be used to dump a database or a set of databases to a file and then loaded on another SQL server (not necessarily a MySQL server).

Its usage is similar to


, but it includes a new set of features. Many of the options are the same, but it was written from scratch to avoid being limited to


 compatibility. The Main Features Include:

  • To make the dump process faster, it allows parallel processing of databases and objects within databases. …
[Read more]
ProxySQL Rules: Applying and Chaining the Rules

In this post, I am going to show you how you can minimize the performance impact of ProxySQL rules by using some finesse.

Apply Test

In my previous post, we could see the effect of the rules on ProxySQL performance. As we could also see, the “apply” option does not help with 1000 tables. Are we sure about this? Let’s consider: if we know 90% of our traffic won’t match any rules, it doesn’t matter if we have 10 or 500 rules – it has to check all of them. And this is going to have a serious effect on performance. How can we avoid that?

Let’s insert rule number ONE, which matches all queries, like this:

insert into mysql_query_rules (username,destination_hostgroup,active,retries,match_digest,apply) values('testuser_rw',600,1,3,'(from|into|update|into table) …
[Read more]
Correct Index Choices for Equality + LIKE Query Optimization

As part of our support services, we do a lot of query optimization. This is where most performance gains come from. Here’s an example of the work we do.

Some days ago a customer arrived with the following table:

CREATE TABLE `infamous_table` (
  `member_id` int(11) NOT NULL DEFAULT '0',
  `email` varchar(200) NOT NULL DEFAULT '',
  `msg_type` varchar(255) NOT NULL DEFAULT '',
  `t2send` int(11) NOT NULL DEFAULT '0',
  `flag` char(1) NOT NULL DEFAULT '',
  `sent` varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `f` (`flag`),
  KEY `email` (`email`),
  KEY `msg_type` (`msg_type`(5)),
  KEY `t_msg` (`t2send`,`msg_type`(5))

And a query that looked like this:

  FROM `infamous_table`
 WHERE `t2send` > 1234 …
[Read more]
InnoDB Page Merging and Page Splitting

If you met one of the (few) MySQL consultants around the globe and asked him/her to review your queries and/or schemas, I am sure that he/she would tell you something regarding the importance of good primary key(s) design. Especially in the case of InnoDB, I’m sure they started to explain to you about index merges and page splits. These two notions are closely related to performance, and you should take this relationship into consideration when designing any index (not just PKs).

That may sound like mumbo jumbo to you, and you may be right. This is not easy stuff, especially when talking about internals. This is not something you deal with on a regular basis, and often you don’t want to deal with it at all.

But sometimes it’s a necessity. If so, this article is for you.

In this article, I want to shed some light in explaining some of the most unclear, behind the scenes operations in InnoDB: page index …

[Read more]
Non-Deterministic Order for SELECT with LIMIT

In this blog, we’ll look at how queries in systems with parallel processing can return rows in a non-deterministic order (and how to fix it).

Short story:

Do not rely on the order of your rows if your query does not use


. Even with


, rows with the same values can be sorted differently. To fix this issue, always add


 when you have



Long story:

While playing with MariaDB ColumnStore and Yandex ClickHouse, I came across a very simple case. In MariaDB ColumnStore …

[Read more]
Dealing with MySQL Error Code 1215: “Cannot add foreign key constraint”

In this blog, we’ll look at how to resolve MySQL error code 1215: “Cannot add foreign key constraint”.

Our Support customers often come to us with things like “My database deployment fails with error 1215”, “Am trying to create a foreign key and can’t get it working” or “Why am I unable to create a constraint?” To be honest, the error message doesn’t help much. You just get the following line:

ERROR 1215 (HY000): Cannot add foreign key constraint

But MySQL never tells you exactly WHY it failed. There’s actually a multitude of reasons this can happen. This blog post is a compendium of the most common reasons why you can get ERROR 1215, how to diagnose your case to find which one is affecting you and potential solutions for adding the foreign key.

[Read more]
New MariaDB Dashboard in Percona Monitoring and Management Metrics Monitor

In honor of the upcoming MariaDB M17 conference in New York City on April 11-12, we have enhanced Percona Monitoring and Management (PMM) Metrics Monitor with a new MariaDB Dashboard and multiple new graphs!

The Percona Monitoring and Management MariaDB Dashboard builds on the efforts of the MariaDB development team to instrument the Aria Storage Engine Status Variables related to Aria Pagecache and Aria Transaction Log activity, the tracking of Index Condition Pushdown (ICP), InnoDB Online DDL when using ALTER TABLE ... ALGORITHM=INPLACE, InnoDB Deadlocks …

[Read more]
Performance Evaluation of SST Data Transfer: With Encryption (Part 2)

In this blog post, we’ll look at the performance of SST data transfer using encryption.

In my previous post, we reviewed SST data transfer in an unsecured environment. Now let’s take a closer look at a setup with encrypted network connections between the donor and joiner nodes.

The base setup is the same as the previous time:

  • Database server: Percona XtraDB Cluster 5.7 on donor node
  • Database: sysbench database – 100 tables 4M rows each (total ~122GB)
  • Network: donor/joiner hosts are connected with dedicated 10Gbit LAN
  • Hardware: donor/joiner hosts – boxes with 28 Cores+HT/RAM 256GB/Samsung SSD 850/Ubuntu 16.04

The setup details for the encryption aspects in our testing:

  • Cryptography libraries: openssl-1.0.2, …
[Read more]
Performance Evaluation of SST Data Transfer: Without Encryption (Part 1)

In this blog, we’ll look at evaluating the performance of an SST data transfer without encryption.

A State Snapshot Transfer (SST) operation is an important part of Percona XtraDB Cluster. It’s used to provision the joining node with all the necessary data. There are three methods of SST operation available: mysqldump, rsync, xtrabackup. The most advanced one – xtrabackup – is the default method for SST in Percona XtraDB Cluster.

We decided to evaluate the current state of xtrabackup, focusing on the process of transferring data between the donor and joiner nodes tp find out if there is any room for improvements or optimizations.

Taking into account that the security of the network connections used for Percona XtraDB Cluster deployment is one of the most important factors that affects SST performance, we will evaluate SST operations in two setups: without network encryption, and in …

[Read more]
Showing entries 1 to 10 of 470
10 Older Entries »