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 中文
Previous 30 Newer Entries Showing entries 91 to 120 of 332 Next 30 Older Entries

Displaying posts with tag: Insight for DBAs (reset)

Innotop: A real-time, advanced investigation tool for MySQL
+2 Vote Up -0Vote Down

GUI monitoring tools for MySQL are not always suitable for all our needs or situations. Most of them are designed to provide historical views into what happens to our database over time rather then real-time insight into current MySQL server status. Excellent free tools for this include Cacti, Zabbix, Ganglia, Nagios, etc. But each of them needs to be properly configured to provide details on what is going on in our MySQL instances. And setting up one of these monitoring solutions is neither

  [Read more...]
Handling long-running queries in MySQL with Percona XtraBackup
+0 Vote Up -0Vote Down

I recently had a case where replication lag on a slave was caused by a backup script. First reaction was to incriminate the additional pressure on the disks, but it turned out to be more subtle: Percona XtraBackup was not able to execute FLUSH TABLES WITH READ LOCK due to a long-running query, and the server ended up being read-only. Let’s see how we can deal with that kind of situation.

In short

Starting with Percona XtraBackup 2.1.4, you can:

  • Configure a timeout after which the backup will be aborted (and the global lock released) with the lock-wait-threshold, lock-wait-query-type and lock-wait-timeout options
  • Or automatically kill all queries that prevent the lock to be granted with the kill-long-queries-timeout and
  [Read more...]
SSL Performance Overhead in MySQL
+1 Vote Up -0Vote Down

NOTE: This is part 1 of what will be a two-part series on the performance implications of using in-flight data encryption.

Some of you may recall my security webinar from back in mid-August; one of the follow-up questions that I was asked was about the performance impact of enabling SSL connections. My answer was 25%, based on some 2011 data that I had seen over on yaSSL’s website, but I included the caveat that it is workload-dependent, because the most expensive part of using SSL is establishing the connection. Not long thereafter, I received a request to conduct some more specific benchmarks surrounding SSL usage in MySQL,

  [Read more...]
Tuning MySQL 5.6 configuration – Webinar followup
+2 Vote Up -0Vote Down

We had a wonderful time during the Sept. 25 webinar, “MySQL 5.6 Configuration Optimization,” and I got a lot more questions than I could answer during the hour. So here is a followup with answers to the most interesting questions you guys asked. (You can also watch a recording of entire webinar here.)

Q: What is the impact of having innodb_stats_on_metadata=off in terms of seeing actual table size in

  [Read more...]
Inexpensive SSDs for Database Workloads
+2 Vote Up -0Vote Down

The cost of SSDs has been dropping rapidly, and at the time of this writing, 2.5-drives have reached the 1TB capacity mark.  You can actually get inexpensive drives for as little as 60 cents per GB. Even inexpensive SSDs can perform tens of thousands of IOPs and come with 1.5M – 2M hous MTBF and a 5-year warranty: check out the Intel SC S3500 specs as an example. There is however one important factor you need to take into account when considering  SSDs as opposed to conventional hard drives – Write Endurance.

Many of us have heard about SSDs having limits in terms of how many writes SSDs can handle, many however assume this is what is already

  [Read more...]
Join my Oct. 2 webinar: ‘Implementing MySQL and Hadoop for Big Data’
+0 Vote Up -0Vote Down

MySQL DBAs know that integrating MySQL and a big data solution can be challenging. That’s why I invite you to join me this Wednesday (Oct. 2) at 10 a.m. Pacific time for a free webinar in which I’ll walk you through how to implement a successful big data strategy with Apache Hadoop and MySQL. This webinar is specifically tailored for MySQL DBAs and developers (or any person with a previous MySQL experience) who wants to know about how to use Apache Hadoop together with MySQL for Big Data.

The webinar is titled, “Implementing MySQL and Hadoop for Big Data,” and you can

  [Read more...]
How to reclaim space in InnoDB when innodb_file_per_table is ON
+1 Vote Up -0Vote Down

When innodb_file_per_table is OFF and all data is going to be stored in ibdata files. If you drop some tables of delete some data then there is no any other way to reclaim that unused disk space except dump/reload method.

When Innodb_file_per_table is ON, each table stores data and indexes in it’s own tablespace file. However, the shared tablespace-ibdata1 can still grow and you can check more information here about why it grows and what are the solutions.

http://www.mysqlperformanceblog.com/2013/08/20/why-is-the-ibdata1-file-continuously-growing-in-mysql/

Following

  [Read more...]
MySQL 5.6 Configuration Optimization Webinar, Sept. 25
+1 Vote Up -0Vote Down

This Wednesday in our next webinar I’ll share how to configure a better-performing MySQL 5.6 server. You’ll lean a practical approach to generating a sensible configuration file that sets what is needed and omits what is not.

Why dedicate an entire webinar to the new configuration settings within MySQL 5.6? Mainly because the default configuration files that come with MySQL 5.6 are not designed for high volume production use, and I’ve seen many MySQL incidents caused by poor configuration. Hopefully my

  [Read more...]
Percona Live London 2013: an insider’s view of the schedule
+1 Vote Up -0Vote Down

With the close of call for papers earlier this month, the Percona Live London conference committee was in full swing this past week reviewing all of the many submissions for November’s Percona Live London MySQL Conference.

The submissions are far ranging and cover some really interesting topics, making the lineup for Percona Live London really strong! What the committee looks for in a submission is how much “value” a talk will bring to the

  [Read more...]
Switching between versions using yum
+1 Vote Up -0Vote Down

One type of question we get very often (even in the form of filed bugs!) is how to switch from stock MySQL to Percona Server or switch from Percona Server 5.5 to Percona XtraDB Cluster using yum, but à la apt-get, i.e. having yum handle the replace.

In its simplest form, yum cannot replace a package¹ for another like apt-get does:

 

# yum -q -q install Percona-XtraDB-Cluster-server
Error: Percona-XtraDB-Cluster-server conflicts with Percona-Server-server-55
Error: Percona-XtraDB-Cluster-client conflicts with Percona-Server-client-55
Error: Percona-XtraDB-Cluster-shared


  [Read more...]
5 reasons why MySQL replication lag is flapping between 0 and XXXXX
+0 Vote Up -0Vote Down

Working day to day with Percona Remote DBA customers, we have been facing an issue from time to time when MySQL replication lag is flapping between 0 and XXXXX constantly – i.e. Seconds_Behind_Master is 0 for a few secs, then it’s like 6287 or 25341, again 0 and so on. I would like to note the 5 different scenarios and symptoms of this – some are obvious and some are not really.

1. Duplicate server-ids on two or more slaves.

Symptoms: MySQL error log on a slave shows the slave thread is connecting/disconnecting from a master constantly.
Solution: check whether all nodes in the replication have unique

  [Read more...]
Enabling crash-safe slaves with MySQL 5.6
+2 Vote Up -0Vote Down

Being able to configure slaves to be crash-safe is one of the major improvements of MySQL 5.6 with regards to replication. However we noticed confusion on how to enable this feature correctly, so let’s clarify how it should be done.

In short

1. Stop MySQL on slave
2. Add relay_log_info_repository = TABLE and relay_log_recovery = ON in my.cnf
3. Restart MySQL and relax

The gory details

To fully understand why you should change the above settings if you want crash-safe slaves, let’s first look at the reasons why replication can break when a slave crashes.

On a slave, replication involves 2 threads: the IO thread which copies the binary log of the master to a local copy called the relay log and the SQL thread which then executes the queries written in the relay log. The current



  [Read more...]
How to move the InnoDB log sequence number (LSN) forward
+1 Vote Up -0Vote Down

This post focuses on the problem of the InnoDB log sequence number being in the future.

Preface: What is an InnoDB log sequence number?

The Log sequence number (LSN) is an important database parameter used by InnoDB in many places.
The most important use is for crash recovery and buffer pool purge control.

Internally, the InnoDB LSN counter never goes backward.
And, when InnoDB writes 50 bytes to the redo logs, the LSN increases by 50 bytes.
As such we can count LSN in megabytes, gigabytes and etc.

Now for the problem: LSN being in the future!

When you have set innodb_force_recovery like




  [Read more...]
How InnoDB promotes UNIQUE constraints
+0 Vote Up -0Vote Down

The other day I was running pt-duplicate-key-checker on behalf of a customer and noticed some peculiar recommendations on an InnoDB table with an odd structure (no PRIMARY key, but multiple UNIQUE constraints). This got me thinking about how InnoDB promotes UNIQUE constraints to the role of PRIMARY KEYs. The documentation is pretty clear:

[DOCS]
When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index. Define a primary key for each table that you create. If there is no logical unique and non-null column or set of columns, add a new auto-increment column, whose values are filled in automatically.

If you do not define a PRIMARY KEY for


  [Read more...]
MySQL Security Webinar: Follow-up Q&A
+1 Vote Up -0Vote Down

Thanks to everyone who attended last week’s webinar on MySQL security; hopefully you’ve all gone out and set SELinux to enforcing mode if you weren’t already running that way. If you weren’t able to attend, the recording and slides are available for viewing/download. But now, without further ado, here are the questions which we didn’t have time to cover during the presentation.

Q: Do you have a favorite software firewall you recommend that I can run on an EC2 instance in

  [Read more...]
Why is the ibdata1 file continuously growing in MySQL?
+1 Vote Up -0Vote Down

We receive this question about the ibdata1 file in MySQL very often in Percona Support.

The panic starts when the monitoring server sends an alert about the storage of the MySQL server – saying that the disk is about to get filled.

After some research you realize that most of the disk space is used by the InnoDB’s shared tablespace ibdata1. You have innodb_file_per_table enabled, so the question is:

What is stored in  [Read more...]

MySQL Security: Armoring Your Dolphin
+1 Vote Up -0Vote Down

My colleague and teammate Ernie Souhrada will be presenting a webinar on Wednesday, August 21, 2013 at 10 a.m. PDT titled “MySQL Security: Armoring Your Dolphin.”

This is a popular topic with news breaking routinely that yet another Internet company has leaked private data of one form or another. Ernie’s webinar will be a

  [Read more...]
Percona University at Washington, D.C. – Sept. 12
+1 Vote Up -0Vote Down

Following our events earlier this year in Raleigh, Montevideo, Buenos Aires, Toronto and Portland, we bring

  [Read more...]
InnoDB Full-text Search in MySQL 5.6: Part 3, Performance
+1 Vote Up -0Vote Down

This is part 3 of a 3 part series covering the new InnoDB full-text search features in MySQL 5.6. To catch up on the previous parts, see part 1 or part 2

Some of you may recall a few months ago that I promised a third part in my InnoDB full-text search (FTS) series, in which I’d actually take a look at the performance of InnoDB FTS in MySQL 5.6 versus traditional MyISAM FTS. I hadn’t planned on quite such a gap between part 2 and part 3, but as they say, better late than never. Recall that we have been working with two data sets, one which I call SEO (8000-keyword-stuffed web pages) and the other which I call

  [Read more...]
Let’s talk about Percona Server 5.6: Enterprise Grade MySQL (webinar)
+1 Vote Up -0Vote Down

The new Percona Server 5.6 is the most manageable, highest performance, and most scalable version of MySQL available. Percona Server 5.6 is the best open source MySQL choice for enterprise-grade applications because it combines new features with the best features of Percona Server 5.5 and MySQL 5.6 to provide unparalleled performance.

Join me tomorrow as I explain how Percona Server 5.6 takes MySQL performance to new heights. In this webinar, aptly titled “

  [Read more...]
Checking B+tree leaf nodes list consistency in InnoDB
+1 Vote Up -0Vote Down

If we have InnoDB pages there are two ways to learn how many records they contain:

  • PAGE_N_RECS field in the page header
  • Count records while walking over the list of records from infimum to supremum

In some previous revision of the recovery tool a short summary was added to a dump which is produced by the constraints_parser.

But if a page is lost and page_parser hasn’t found it, all records from this page are lost. In other words per-page recovery statistics gives us little idea about whether or not a recovered table is complete.

To cover this flaw a new tool index_check is introduced in

  [Read more...]
What kind of queries are bad for MySQL?
+0 Vote Up -1Vote Down

In writing a recommendation for our Web development team on how to use MySQL, I came up with the following list, which I want to share: What kind of queries are bad for MySQL?

  • Any query is bad. Send a query only if you must. (Hint: use caching like memcache or redis)
  • Queries that examine many rows are bad. Try instead to use…
    SELECT col1 FROM table1 WHERE primary_key_column=SOMETHING

    . Or at least
    secondary_key_column=SOMETHING

    . If it is still not possible, try to make the query examine the least amount of rows possible (zero is ideal, as we come to the first case here)
  • Queries with JOINS are bad. Try to denormalize the table to avoid JOINS. Example: original query
    SELECT t2.value FROM t2 JOIN t1 ON (t1.id=t2.tid) WHERE t1.orderdate=NOW()

    . This can be denormalized





  [Read more...]
Recovering temporal types in MySQL 5.6: TIME, TIMESTAMP and DATETIME
+2 Vote Up -0Vote Down

MySQL 5.6 introduces a new feature – microseconds resolution in some temporal types. As of 5.6.4 TIME, TIMESTAMP and DATETIME can have a fractional part. To create a field with subseconds you can specify precision in brackets: TIME(3), DATETIME(6) etc.

Obviously, the new feature requires the format change. All three types may now have a tail with a fractional part. The tail may be one, two or three bytes long and can store up to six digits after the point.

The non-fractional part has changed too. Thus, DATETIME uses only five bytes comparing to eight in previous versions.

As of

  [Read more...]
MySQL and Hadoop integration
+0 Vote Up -0Vote Down

Dolphin and Elephant: an Introduction

This post is intended for MySQL DBAs or Sysadmins who need to start using Apache Hadoop and want to integrate those 2 solutions. In this post I will cover some basic information about the Hadoop, focusing on Hive as well as MySQL and Hadoop/Hive integration.

First of all, if you were dealing with MySQL or any other relational database most of your professional life (like I was), Hadoop may look different. Very different. Apparently, Hadoop is the opposite to any relational database. Unlike the database where we have a set of tables and indexes, Hadoop works with a set of text files. And… there are no indexes at all. And yes, this may be shocking,

  [Read more...]
LVM read performance during snapshots
+1 Vote Up -0Vote Down

For the same customer I am exploring ZFS for backups, the twin server is using regular LVM and XFS. On this twin, I have setup mylvmbackup for a more conservative backup approach. I quickly found some odd behaviors, the backup was taking much longer than what I was expecting. It is not the first time I saw that, but here it was obvious. So I recorded some metrics, bi from vmstat and percent of cow space used from lvs during a backup. Cow space is the Copy On Write buffer used by LVM to record the modified pages like they were at the beginning of the snapshot. Upon reads, LVM must scan the list to verify that there’s no newer version. Here’s the other details about the backup:

  • Filesystem: 2TB, xfs
  • Snapsize: 60GB
  • Amount to backup: ~600GB
  • Backup tool: mylvmbackup
  • Compressor: pbzip2

  [Read more...]
Schema changes – what’s new in MySQL 5.6?
+3 Vote Up -0Vote Down

Among many of the improvements you can enjoy in MySQL 5.6, there is one that addresses a huge operational problem that most DBAs and System Administrators encounter in their life: schema changes.

While it is usually not a problem for small tables or those in early stages of product life cycle, schema changes become a huge pain once your tables get a significant amount of data. Planning for maintenance is becoming more and more difficult, and your worldwide users want the service to be up and running 24/7, while on the other hand, your developers desire to introduce schema changes every week.

PITA

But what is the real problem here? Let me illustrate very

  [Read more...]
Limited disk space? Compact backups with Percona Xtrabackup 2.1
+1 Vote Up -0Vote Down

One very interesting feature, “Compact Backup,” is introduced in Percona XtraBackup 2.1. You can run “compact backups” with the  –compact option, which is very useful for those who have limited disk space to keep the database backup. Now let’s first understand how it works. When we are using –compact option with Innobackupex, it will omit the secondary index pages. This will make the backups more compact and this way they will take less space on disk but the

  [Read more...]
pt-online-schema-change and binlog_format
+2 Vote Up -0Vote Down

Statement-based or row-based, or mixed?  We’ve all seen this discussed at length so I’m not trying to rehash tired arguments.  At a high level, the difference is simple:

  • Statement based replication (SBR) replicates the SQL statements to the slave to be replayed
  • Row based replication (RBR) replicates the actual rows changed to the slave to be replayed
  • Mixed mode uses RBR in the event of a non-deterministic statement, otherwise uses SBR
  • Recently, I worked with a client to optimize their use of pt-online-schema-change and keep replication delay to a minimum.  We found that using RBR in conjunction with a smaller chunk-time was the best result in their environment due to reduced IO on the slave, but I wanted to recreate the test locally as well to see how it looked in the generic sense (sysbench for

      [Read more...]
    Setting up MySQL SSL and secure connections
    +1 Vote Up -0Vote Down

    There are different articles on how to setup MySQL with SSL but it’s sometimes difficult to end up with a good simple one. Usually, setting up MySQL SSL is not really a smooth process due to such factors like “it’s not your day”, something is broken apparently or the documentation lies I am going to provide the brief instructions on how to setup MySQL with SSL, SSL replication and how to establish secure connections from the console and scripts showing the working examples.

    Quick links:

      [Read more...]
    Migrating between MySQL schemas with Percona Xtrabackup
    +0 Vote Up -0Vote Down

    Recently, I was working with a client that asked about using Percona Xtrabackup to take a snapshot of a particular MySQL schema and then reload it with a different schema name on the same server.  It caught me off guard because I’d never really thought about it – typically, I’ve used Xtrabackup simply to clone a server (for replication or migration) or migrate an existing schema to a new server.  However, given the import/export functionality of Xtrabackup combined with Percona

      [Read more...]
    Previous 30 Newer Entries Showing entries 91 to 120 of 332 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.