Showing entries 21 to 30 of 992
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: Performance (reset)
MySQL EXPLAIN ANALYZE

MySQL 8.0.18 was just released, and it contains a brand new feature to analyze and understand how queries are executed: EXPLAIN ANALYZE.

What is it?

EXPLAIN ANALYZE is a profiling tool for your queries that will show you where MySQL spends time on your query and why.…

Facebook Twitter LinkedIn

MySQL 8.0 Clone Plugin and its internal process.

MySQL 8 has recently released clone plugin which makes DBA’s task of rebuilding the DB servers more easy.

  • Cloning is a process of creating an exact copy of the original. In technical terms cloning alias to (Backup + Recovery), MySQL database cloning requires a sequence of actions to be performed manually or in a scripted fashion with and without the tools involved.
  • Cloning is the first step when you want to configure the replication slave or Joining a new server to the InnoDB cluster. There was no native support for auto provisioning earlier. Percona XtraDB Cluster (MySQL + Galera Cluster) does cloning using xtrabackup tool by default when a new node joins the cluster.
  • Now MySQL simplified this task, In this post, We will see how to clone the database using clone plugin and its internals.
[Read more]
Save Space on MySQL data with Column Compression

                                   Recently, One of our client reached our Remote DBA team with a requirement to reduce the size of the table as it is having many text columns with huge number of records. At preliminary check , I have validated the table size and its row format, as it was  in compressed  format already. 

Later I checked on other possibilities to compress the text columns further, At that time, then I came across per-column compression feature in Percona MySQL server (From 5.7.17-11) which features individual column compression and we were using …

[Read more]
Make It Faster: Improving MySQL Write Performance for Tungsten Cluster Slaves

Overview The Skinny

In this blog post we explore various options for performance tuning MySQL server for better slave replication performance.

A Tungsten Cluster relies upon the Tungsten Replicator to move events from the master node to the slaves. Once the event has been transferred to the slave as THL on disk, the slave applier will then attempt to write it to the database. The Replicator can only apply events as fast as MySQL allows. If the MySQL server is somehow slow or blocking, then the Replicator will be as well.

A properly-tuned database server in addition to infrastructure and SysAdmin best practices will go quite a long way towards high-performance slave apply.

The Question Recently, a customer asked us:

During one of our load tests, we had a peak of 60k writes/min, averaging …

[Read more]
One, two, three, MySQLers… OOW 2019, Percona Live, ProxySQL Tech Day!

As people are getting back from vacations and application traffic is going back and hitting database backend hard, it is a good time to think about performance and prepare for the winter holiday spike. Whenever you are on-premise or in the cloud, questions are the same:

  • Are we satisfied with the current MySQL performance?
  • How much traffic are we currently serving?
  • Is there enough capacity to stay stable as traffic increases?
  • What would be the best way to stay cost-effective as we grow?

Unfortunately, the answers are not so obvious and require a thorough performance analysis. However, there is a chance that your problem has already been solved by someone else. I do not mean StackOverflow (which is a beautiful place, by the way), but some higher level questions which people usually discuss at conferences, and we have several of them ongoing or just a couple of weeks away.

[Read more]
What’s Faster? COUNT(*) or COUNT(1)?

One of the biggest and undead myths in SQL is that COUNT(*) is faster than COUNT(1). Or was it that COUNT(1) is faster than COUNT(*)? Impossible to remember, because there’s really no reason at all why one should be faster than the other. But is the myth justified?

Let’s measure!

How does COUNT(…) work?

But first, let’s look into some theory. The two ways to count things are not exactly the same thing. Why?

  • COUNT(*) counts all the tuples in a group
  • COUNT(<expr>) counts all the tuples in a group for which <expr> evaluates to something that IS NOT NULL

This distinction can be quite useful. Most of the time, we’ll simply COUNT(*) for convenience, but there are (at least) two cases where we don’t want that, for example:

When outer joining

[Read more]
What are the MySQL Metrics That Really Make a Difference?

Author: Robert Agar

MySQL is one of the most popular relational database platforms in the world. As such, it is used as the backend of many mission-critical applications across all sectors of business and industry. If you are a DBA or database developer there is a high probability that you are working with MySQL now or will be in the near future.

One of the primary responsibilities of a DBA is to optimize the performance of their databases. There are many ways to accomplish this feat, and all of them have an important point in common. You need knowledge concerning the operation of your systems before you can expect to make intelligent modifications to them. All of the methods used to tune and optimize your databases are identified by studying metrics regarding their current performance and using this data to plan appropriate action.

The right tools are required to gather the information needed to …

[Read more]
MySQL 5.6/Maria 10.1 : How we got from 30k qps to 101k qps…..

Late one evening, I was staring at one of our large MySQL installations and noticed the database was hovering around 7-10 run queue length (48 cores, ~500 gigs memory, fusionIO cards). I had been scratching my head on how to get more throughput from the database. This blog records the changes I made to tune performance in order to achieve a 300% better throughput in MySQL. I tested my theories on MySQL 5.6/Maria 10.1. While with 5.7 DBAs would turn to performance_schema for the supporting metrics, I hope that you find the process interesting nevertheless.

View from an Oracle RDBMS DBA…

For context, I came to MySQL from a background as an Oracle RDBMS DBA, and this informs my expectations. For this exercise, unlike with Oracle RDBMS, I had no access to view wait events so that I could see where my database was struggling. At least, no access in MySQL 5.6/Maria 10.1 without taking a performance hit by using …

[Read more]
Webinar 8/7: Performance Analyses and Troubleshooting Technologies for Databases

Please join Percona CEO Peter Zaitsev as he presents “Performance Analyses and Troubleshooting Technologies for Databases” on Wednesday, August 7th, 2019 at 11:00 AM PDT (UTC-7).

Register Now

Have you heard about the USE Method (Utilization – Saturation – Errors), RED (Rate – Errors – Duration) or Golden Signals (Latency – Traffic – Errors – Saturations)?

In this presentation, we will talk briefly about these different-but-similar “focuses” and discuss how we can apply them to data infrastructure performance analysis troubleshooting and monitoring.

We will use MySQL as an example, but most of this talk applies to other database technologies as well.

[Read more]
MySQL Optimizer: Naughty Aberrations on Queries Combining WHERE, ORDER BY and LIMIT

Sometimes, the MySQL Optimizer chooses a wrong plan, and a query that should execute in less than 0.1 second ends-up running for 12 minutes !  This is not a new problem: bugs about this can be traced back to 2014, and a blog post on this subject was published in 2015.  But even if this is old news, because this problem recently came yet again to my attention, and because this is still not fixed in MySQL 5.7 and 8.0, this is a subject worth writing about.

The MySQL Optimizer

Before looking at the problematic query, we have to say a few words about the optimizer.  The Query Optimizer is the part of query execution that chooses the query plan.  A Query Execution Plan is the way MySQL chooses to execute a specific query.  It …

[Read more]
Showing entries 21 to 30 of 992
« 10 Newer Entries | 10 Older Entries »