Showing entries 1 to 10 of 472
10 Older Entries »
Displaying posts with tag: Insight for DBAs (reset)
Percona Live 2017: Day Two Keynotes

Welcome to the second day of the Percona Live Open Source Database Conference 2017, and the second set of Percona Live keynotes! It’s a bit rainy outside today, but that isn’t bothering the Percona Live attendees (we’re all indoors learning about new open source technologies)!

Day two of the conference kicked off with another four keynote talks, all of which discussed issues and technologies that are addressed by open source solutions:

The Open Source Database Business Model is Under Siege

Paul Dix (InfluxData)

Paul Dix’s keynote may have ruffled a few feathers, as he looked at possible futures for the open …

[Read more]
Better Than Linear Scaling

In this blog, we’ll look at how to achieve better-than-linear scaling.

Scalability is the capability of a system, network or process to handle a growing amount of work, or its potential to be enlarged to accommodate that growth. For example, we consider a system scalable if it is capable of increasing its total output under an increased load when resources (typically hardware) are added: https://en.wikipedia.org/wiki/Scalability.

It is often accepted as a fact that systems (in particular databases) can’t scale better than linearly. By this I mean when you double resources, the expected performance doubles, at best (and often is less than doubled).  

We can attribute this assumption to Amdahl’s law (https://en.wikipedia.org/wiki/Amdahl%27s_law), and later …

[Read more]
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

mysqlpump

 utility.

mysqlpump

 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

mysqldump

, 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

mysqldump

 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` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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))
) ENGINE=InnoDB DEFAULT CHARSET=latin1

And a query that looked like this:

SELECT COUNT(*)
  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

ORDER BY

. Even with

ORDER BY

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

ORDER BY ... ID

 when you have

LIMIT N

.

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]
Showing entries 1 to 10 of 472
10 Older Entries »