Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 30 of 290 Next 30 Older Entries

Displaying posts with tag: Insight for DBAs (reset)

How to find bugs in MySQL
+1 Vote Up -0Vote Down

Finding bugs in MySQL is not only fun, it’s also something I have been doing the last four years of my life.

Whether you want to become the next Shane Bester (who is generally considered the most skilled MySQL bug hunter worldwide), or just want to prove you can outsmart some of the world’s best programmers, finding bugs in MySQL is a skill not reserved anymore to top QA engineers armed with a loads of scripts, expensive flash storage and top-range server hardware. Off course, for professionals that’s still the way to go, but now anyone with an average laptop and a standard HDD can have a lot of fun trying to find that

  [Read more...]
Heartbleed: Separating FAQ From FUD
+1 Vote Up -0Vote Down

If you’ve been following this blog (my colleague, David Busby, posted about it yesterday) or any tech news outlet in the past few days, you’ve probably seen some mention of the “Heartbleed” vulnerability in certain versions of the OpenSSL library.

So what is ‘Heartbleed’, really?

In short, Heartbleed is an information-leak issue. An attacker can exploit this bug to retrieve the contents of a server’s memory without any need for local access. According to the researchers that discovered it, this can be done without leaving any trace of compromise on the system. In other words, if you’re vulnerable, they can steal your keys and you won’t even notice that they’ve gone missing. I use the word

  [Read more...]
OpenSSL heartbleed CVE-2014-0160 – Data leaks make my heart bleed
+0 Vote Up -0Vote Down

The heartbleed bug was introduced in OpenSSL 1.0.1 and is present in

  • 1.0.1
  • 1.0.1a
  • 1.0.1b
  • 1.0.1c
  • 1.0.1d
  • 1.0.1e
  • 1.0.1f

The bug is not present in 1.0.1g, nor is it present in the 1.0.0 branch nor the 0.9.8 branch of OpenSSL some sources report 1.0.2-beta is also affected by this bug at the time of writing, however it is a beta product and I would really recommend not to use beta quality releases for something as fundamentally important as OpenSSL in production.

The bug itself is within the heartbeat extension of OpenSSL (RFC6520). The bug allows an attacker to leak the memory in up to 64k chunks, this is not to say the data being leaked is limited to 64k as the attacker can continually abuse this bug to leak data, until they are satisfied with

  [Read more...]
Optimizing MySQL Performance: Choosing the Right Tool for the Job
+0 Vote Up -0Vote Down

Next Wednesday, I will present a webinar about MySQL performance profiling tools that every MySQL DBA should know.

Application performance is a key aspect of ensuring a good experience for your end users. But finding and fixing performance bottlenecks is difficult in the complex systems that define today’s web applications. Having a method and knowing how to use the tools available can significantly reduce the amount of time between problems manifesting and fixes being deployed.

In the webinar, titled “Optimizing MySQL Performance: Choosing the Right Tool for the Job,” we’ll start with the basic top, iostat, and vmstat then move onto advanced tools like GDB, Oprofile, and Strace.

I’m looking forward to this webinar and invite you to join us April 16th at 10 a.m. Pacific time. You can learn more

  [Read more...]
Facebook’s Yoshinori Matsunobu on MySQL, WebScaleSQL & Percona Live
+3 Vote Up -0Vote Down

Facebook’s Yoshinori Matsunobu

I spoke with Facebook database engineer Yoshinori Matsunobu here at Percona Live 2014 today about a range of topics, including MySQL at Facebook, the company’s recent move to WebScaleSQL, new MySQL flash storage technologies – and why attending the Percona Live MySQL Conference and Expo each year is very important to him.

Facebook engineers are hosting several sessions at

  [Read more...]
How to add an existing Percona XtraDB Cluster to Percona ClusterControl
+0 Vote Up -0Vote Down

In my last blog post I explained how to use Percona ClusterControl to create a new Percona XtraDB Cluster from scratch. That’s a good option when you want to create a testing environment in just some mouse clicks. In this case I’m going to show you how to add your existing cluster to Percona ClusterControl so you can manage and monitor it on the web interface.

The environment will be pretty similar, we will have UI, CMON and 3 XtraDB Cluster nodes. The cluster should be already running and Percona ClusterControl also installed.

Adding an existing Cluster

The

  [Read more...]
ScaleArc: Benchmarking with sysbench
+0 Vote Up -0Vote Down

ScaleArc recently hired Percona to perform various tests on its database traffic management product. This post is the outcome of the benchmarks carried out by Uday Sawant (ScaleArc) and myself. You can also download the report directly as a PDF here.

The goal of these benchmarks is to identify the potential overhead of the ScaleArc software itself and the potential benefits of caching. The benchmarks were carried out with the trunk version of sysbench. For this reason, we used a very small set of data, so the measurements will be fast, and it’s known that caching has huge benefits when the queries themselves are rather expensive. We decided that we would rather show that benefit with a real-world application, which is coming later is this series.

  [Read more...]
Innodb redo log archiving
+1 Vote Up -0Vote Down

Percona Server 5.6.11-60.3 introduces a new “log archiving” feature. Percona XtraBackup 2.1.5 supports “apply archived logs.” What does it mean and how it can be used?

Percona products propose three kinds of incremental backups. The first is full scan of data files and comparison the data with backup data to find some delta. This approach provides a history of changes and saves disk space by storing only data deltas. But the disadvantage is a full-data file scan that adds load to the disk subsystem. The second kind of incremental

  [Read more...]
7 Key MySQL clustering technologies – A joint webinar with 451 Research
+0 Vote Up -0Vote Down

I’m looking forward to Wednesday’s joint webinar on MySQL clustering technologies with Matt Aslett, research director of data management and analytics over at 451 Research. We’ll be participating in a live, in-depth discussion of MySQL Clustering for High Availability and Scalability.

Matt will present an overview of the trends driving adoption of clustering technology. He’ll also discuss the key technologies and criteria that developers and administrators need

  [Read more...]
How to log slow queries on Slave in MySQL 5.0 with pt-query-digest
+0 Vote Up -0Vote Down

Working as a Percona Support Engineer, every day we are seeing lots of issues related to MySQL replication. One very common issue is slave lagging. There are many reasons for slave lag but one common reason is that queries are taking more time on slave then master. How to check and log those long-running queries?  From MySQL 5.1, log-slow-slave-statements variable was introduced, which you can enable on slave and log slow queries. But what if you want to log slow queries on slave in earlier versions like MySQL 5.0?  There is a good solution/workaround:

  [Read more...]
Tools and tips for analysis of MySQL’s Slow Query Log
+2 Vote Up -0Vote Down

MySQL has a nice feature, slow query log, which allows you to log all queries that exceed a predefined about of time to execute. Peter Zaitsev first wrote about this back in 2006 – there have been a few other posts here on the MySQL Performance Blog since then (check this and

  [Read more...]
Open Source Appreciation Day at the Percona Live MySQL Conference
+1 Vote Up -0Vote Down

I am very pleased to announce a new event in conjunction with the Percona Live MySQL Conference and Expo this year: Open Source Appreciation Day on Monday, March 31st in the Santa Clara Convention Center! We are pleased to announce two separate groups holding events this year under this new umbrella. We are hosting an event called “OpenStack Today” for those interested in learning more about developments in the OpenStack world. CentOS is holding the “

  [Read more...]
Introducing backup locks in Percona Server
+1 Vote Up -0Vote Down

TL;DR version: The backup locks feature introduced in Percona Server 5.6.16-64.0 is a lightweight alternative to FLUSH TABLES WITH READ LOCK and can be used to take both physical and logical backups with less downtime on busy servers. To employ the feature with mysqldump, use mysqldump --lock-for-backup --single-transaction. The next release of Percona XtraBackup will also be using backup locks automatically if the target server supports the feature.

Now on to the gory details, but let’s start with some history.

In the beginning…

In the beginning there was FLUSH TABLES, and users messed with their MyISAM tables under a live server and were not ashamed. Users could do nice things like:

mysql> FLUSH TABLES;
# execute myisamchk, myisampack, backup / restore some


  [Read more...]
Many-table joins in MySQL 5.6
+3 Vote Up -0Vote Down

I recently worked on an uncommon slow query: less than 100 rows were read and returned, the whole dataset was fitting in memory but the query took several seconds to run. Long story short: the query was a join involving 21 tables, running on MySQL 5.1. But by default MySQL 5.1 is not good at handling joins with such a large number of tables. The good news is that MySQL 5.6 brings welcome improvements.

Isolating the problem

As always with a slow query, finding the execution plan with EXPLAIN is the 1st step to understand where time is spent. Here the plan was very good with almost all joins using the primary key or a unique key, but perhaps the most interesting part was that EXPLAIN was very slow as well. This indicates that the optimizer takes a lot of time finding the optimal execution plan. To double check, we can run SHOW

  [Read more...]
Oracle’s Mats Kindahl to weave MySQL Fabric into Percona Live session
+2 Vote Up -0Vote Down

Mats Kindahl of Oracle is lead developer of MySQL Fabric

MySQL Fabric is an integrated framework for managing farms of MySQL servers with support for both high-availability and sharding. Its development has been spearheaded by Mats Kindahl, senior principal software developer in MySQL at Oracle.

Mats is leading the MySQL Scaling and High-Availability effort covering the newly released MySQL Fabric and the MySQL Applier for Hadoop. He is also the architect and implementer of several features (mostly replication features), including the row-based replication available in 5.1 and the binary log group commit available in MySQL 5.6. Before starting MySQL he earned a doctoral degree in the area of automated

  [Read more...]
How rows_sent can be more than rows_examined?
+2 Vote Up -0Vote Down

When looking at queries that are candidates for optimization I often recommend that people look at rows_sent and rows_examined values as available in the slow query log (as well as some other places). If rows_examined is by far larger than rows_sent, say 100 larger, then the query is a great candidate for optimization. Optimization could be as simple as adding a few indexes or much more complicated as in generating summary tables so you do not need to rely on large aggregations for your real-time queries.

Just to be clear this is a great rule for your “real time” queries need to be handled very quickly and in high volumes. Batch jobs, reporting queries often will have to scan through millions of rows to get few rows of result set and it is fine.

So it is all clear with rows_sent being smaller than

  [Read more...]
DBA 101: Sometimes forgotten functionality in the MySQL client
+1 Vote Up -0Vote Down

The MySQL client has some functionalities some of us never use. Why would you use them and what is the added value of this?

Every DBA and developer has had a moment when he or she needs to connect to a MySQL database using the command line tool. Therefore I’ve written down an explanation of some command line commands you can insert in the CLI, most of them give added value and make your experience with the cli more enjoyable.

prompt

Who has never witnessed the scary feeling of not being connected to the write database when having several terminals open. I do, due to the fact I use the prompt functionality.

mysql >\R Production >
PROMPT set to 'Production > '

Or you can go a bit further and visualise the user, host and active database in:

mysql > \R \u@\h [\d]>
PROMPT set
  [Read more...]
The MySQL ARCHIVE storage engine – Alternatives
+0 Vote Up -0Vote Down

In my previous post I pointed out that the existing ARCHIVE storage engine in MySQL may not be the one that will satisfy your needs when it comes to effectively storing large and/or old data. But are there any good alternatives? As the primary purpose of this engine is to store rarely accessed data in disk space efficient way, I will focus here on data compression abilities rather then on performance.

The InnoDB engine provides compressed row format, but is it’s efficiency even close to the one from that available in archive engine? You can also compress MyISAM tables by using myisampack tool, but that also means a table will be read only after such operation.

Moreover, I don’t trust MyISAM nor Archive when it comes to data

  [Read more...]
Percona XtraDB Cluster performance monitoring and troubleshooting: Webinar
+1 Vote Up -0Vote Down

Next Wednesday, Severalnines CTO Johan Andersson and I will co-present a webinar about ClusterControl, a cluster management tool created by Severalnines that can monitor Percona XtraDB Cluster. It provides DBAs with the right metrics to manage and optimize applications during development and production.

In the webinar, titled “Performance Monitoring and Troubleshooting of Percona

  [Read more...]
PERFORMANCE_SCHEMA vs Slow Query Log
+4 Vote Up -0Vote Down

A couple of weeks ago, shortly after Vadim wrote about Percona Cloud Tools and using Slow Query Log to capture the data, Mark Leith asked why don’t we just use Performance Schema instead? This is an interesting question and I think it deserves its own blog post to talk about.

First, I would say main reason for using Slow Query Log is compatibility. Basic Slow query log with microsecond query time precision is available starting in MySQL 5.1, while

  [Read more...]
Analyze MySQL Query Performance with Percona Cloud Tools: Feb. 12 webinar
+0 Vote Up -0Vote Down

Next week (Wednesday, February 12 at 10 a.m. Pacific) I will host a webinar titled “Analyze MySQL Query Performance with Percona Cloud Tools.” Percona Cloud Tools, currently in beta, reveals new insights about MySQL performance enabling you to improve your database queries and applications. (You can request access to the free beta here).

For webinar attendees, Percona will raffle five (5) 60-minute MySQL query consulting sessions with me to analyze your Percona Cloud Tools query data and provide feedback and performance suggestions. Read below for how to win.

In the

  [Read more...]
Quick installation guide for Percona Cloud Tools for MySQL
+1 Vote Up -0Vote Down

Here in Percona Support, we’re receiving several requests per day for help with Percona Cloud Tools installation steps.

So I decided to prepare a step-by-step example of the installation process with some comments based on experience.  Percona Cloud Tools is a hosted service providing access to query performance insights for all MySQL uses. After a brief setup, you’ll unlock new information about your database and how to improve your applications. You can sign up here to request access to the free beta, currently under way.

Some notes

  • It’s recommended to do the installation under root.
  • If you’re
  [Read more...]
Percona Toolkit collection: pt-visual-explain
+0 Vote Up -0Vote Down

This is the first in a series of posts highlighting a few of the seldom-used but still handy Percona Toolkit tools.

Have you ever had a problem understanding the EXPLAIN statement output? And are you the type of person who would rather use the command line than a GUI application? Then I would recommend that you use Percona’s pt-visual-explain toolkit. This is one of many Percona Toolkit tools that is useful for those who want to have a different view and an easier time understanding the EXPLAIN output aside from the usual table and vertical views.

As described in the documentation –

  [Read more...]
10 MySQL settings to tune after installation
+1 Vote Up -0Vote Down

When we are hired for a MySQL performance audit, we are expected to review the MySQL configuration and to suggest improvements. Many people are surprised because in most cases, we only suggest to change a few settings even though hundreds of options are available. The goal of this post is to give you a list of some of the most critical settings.

We already made such suggestions in the past here on this blog a few years ago, but things have changed a lot in the MySQL world since then!

Before we start…

Even experienced people can make mistakes that can cause a lot of trouble. So before blindly applying the recommendations of this post, please keep in mind the following items:

  • Change one setting at a
  [Read more...]
MySQL server memory usage troubleshooting tips
+2 Vote Up -0Vote Down

There are many blog posts already written on topics related to “MySQL server memory usage,” but nevertheless there are some who still get confused when troubleshooting issues associated with memory usage for MySQL. As a Percona support engineer, I’m seeing many issues regularly related to heavy server loads – OR OOM killer got invoked and killed MySQL server due to high Memory usage… OR with a question like: “I don’t know why mysql is taking so much memory. How do I find where exactly memory is allocated? please help!”

There are many ways to check memory consumption of MySQL. So, I’m just trying here to explain it by combining all details that I know of in this

  [Read more...]
Beware of MySQL 5.6 server UUID when cloning slaves
+0 Vote Up -0Vote Down

The other day I was working on an issue where one of the slaves was showing unexpected lag. Interestingly with only the IO thread running the slave was doing significantly more IO as compared to the rate at which the IO thread was fetching the binary log events from the master.

I found this out by polling the SLAVE STATUS and monitoring the value of Read_Master_Log_Pos as it changed over time. Then compared it to the actual IO being done by the server using the pt-diskstats tool from the excellent Percona Toolkit. Note that, when doing this analysis, I had already stopped the slave SQL thread

  [Read more...]
Percona Cloud Tools: Making MySQL performance easy
+0 Vote Up -0Vote Down

One of our primary focuses at Percona is performance. Let me make some statements on what is “performance.”

In doing so I will refer to two pieces of content:

I highly recommend that you familiarize yourself with both of them.

Performance

Performance is about tasks and time.
We say that the system is performing well if it

  [Read more...]
The ARCHIVE Storage Engine – does it do what you expect?
+0 Vote Up -0Vote Down

Sometimes there is a need for keeping large amounts of old, rarely used data without investing too much on expensive storage. Very often such data doesn’t need to be updated anymore, or the intent is to leave it untouched. I sometimes wonder what I should really suggest to our Support customers.

For this purpose, the archive storage engine, added in MySQL 4.1.3, seems perfect as it provides excellent compression and the only DML statement it does allow is INSERT. However, does it really work as you would expect?

First of all, it has some serious limitations. Apart from lack of support for DELETE, REPLACE and UPDATE statements (which may be acceptable for some needs), another one is that it does not allow you to have indexes, although you can have an auto_increment column being

  [Read more...]
Multiple column index vs multiple indexes with MySQL 5.6
+4 Vote Up -0Vote Down

A question often comes when talking about indexing: should we use multiple column indexes or multiple indexes on single columns? Peter Zaitsev wrote about it back in 2008 and the conclusion then was that a multiple column index is most often the best solution. But with all the recent optimizer improvements, is there anything different with MySQL 5.6?

Setup

For this test, we will use these 2 tables (same structure as in Peter’s post):

CREATE TABLE t1000merge (
  id int not null auto_increment primary key,
  i int(11) NOT NULL,
  j int(11) NOT NULL,
  val char(10) NOT NULL,
  KEY i (i),
  KEY j (j)
) ENGINE=InnoDB;
CREATE TABLE t1000idx2 (
  id int not null auto_increment primary key,
  i int(11) NOT
  [Read more...]
How to recover table structure from .frm files with MySQL Utilities
+1 Vote Up -0Vote Down

Table structures are stored in .frm files and in the InnoDB Data Dictionary. Sometimes, usually in data recovery issues, we need to recover those structures to be able to find the lost data or just to recreate the tables.

There are different ways to do it and we’ve already written about it in this blog. For example, we can use the data recovery tools to recover table structures from InnoDB Dictionary or from the .frm files using a MySQL Server. This blog post will be an update of that last one. I will show you how to easily recover the

  [Read more...]
Showing entries 1 to 30 of 290 Next 30 Older Entries

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.