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 291 Next 30 Older Entries

Displaying posts with tag: Insight for DBAs (reset)

Rotating MySQL slow logs safely
+0 Vote Up -0Vote Down

This blog post is part two of two. Like part one, published Wednesday, this is a cross-post from Groupon’s engineering blog. Thanks again to Kyle Oppenheim at Groupon. And one more reminder that I’ll be at the Percona Live MySQL Conference and Expo next week in Santa Clara, California so look for me there. You can checkout the sessions I’ll be leading here.

In my last post, I described a solution for keeping the


  [Read more...]
Disconnecting a replication slave is easier with MySQL 5.5+ (RESET SLAVE vs. RESET SLAVE ALL)
+0 Vote Up -0Vote Down

It’s not uncommon to promote a server from slave to master. One of the key things to protect your data integrity is to make sure that the promoted slave is permanently disconnected from its old master. If not, it may get writes from the old master, which can cause all kinds of data corruption. MySQL provides the handy RESET SLAVE command. But as we’ll see, its behavior has changed along with the MySQL versions and it’s easy to shoot yourself in the foot if you use it incorrectly. So how do you safely disconnect a replication slave?

In short

  • For MySQL 5.0 and 5.1, run STOP SLAVE, CHANGE MASTER TO MASTER_HOST='' and then RESET SLAVE.
  • For MySQL 5.5 and 5.6, run STOP SLAVE and then RESET SLAVE
  [Read more...]
Is your MySQL buffer pool warm? Make it sweat!
+2 Vote Up -0Vote Down

Today’s blog post diving into the waters of the MySQL buffer pool is a cross-post from Groupon’s engineering blog, and is Part 1 of 2. Thank you to Kyle Oppenheim at Groupon for contributing to this project and post. We’ll be posting Part 2 on Thursday. I’ll be at the Percona Live MySQL Conference and Expo next week in Santa Clara, California so look for me there – I’d love to connect and talk more about MySQL buffer pools or anything else that’s on your mind!

There


  [Read more...]
Keynotes, BOFs, and the Community Networking Reception at Percona Live MySQL Conference and Expo
+1 Vote Up -0Vote Down

The Percona Live MySQL Conference and Expo begins next Monday and runs April 22-25, 2013. Attendees will see great keynotes from leaders in the industry including representatives from Oracle, Amazon Web Services, HP, Continuent, and Percona. They can also participate in thought provoking Birds of a Feather sessions on Tuesday night and the Wednesday night Community Networking Reception will be fun and entertaining with the presentation of the Community Awards and the Lightning Talks.

If you

  [Read more...]
10 years of MySQL User Conferences
+3 Vote Up -1Vote Down

In preparing for this month’s Percona Live MySQL Conference and Expo, I’ve been reminiscing about the annual MySQL User Conference’s history – the 9 times it previously took place in its various reincarnations – and there are a lot of good things, fun things to remember.

2003 was the year that marked the first MySQL user conference independently organized by MySQL AB. It was called the “MySQL Users Conference” and took place at the Double Tree

  [Read more...]
Why use encrypted backup with Percona XtraBackup 2.1 for MySQL?
+0 Vote Up -0Vote Down

We just released our first alpha of Percona XtraBackup 2.1 for MySQL and with it we included the ability to encrypt backups on the fly (full documentation here). This feature is different than simply piping the backup stream through the openssl or gpg binaries, which is what some people have used in the past. A big benefit of using the built-in encryption is that multiple CPU cores can be used for encryption

  [Read more...]
Learn About MySQL 5.6 at the Percona Live MySQL Conference
+3 Vote Up -0Vote Down

5.6 has redefined MySQL performance and usability. Some great talks at the Percona Live MySQL Conference and Expo will provide insight into the new features and benefits of this major release. The conference is April 22-25, 2013 at the Santa Clara Convention Center and Hyatt Santa Clara.

Monday evening features the conference Welcome Reception where attendees can relax over food and beverages in the exhibition area following the day’s tutorials. After the Welcome Reception, at 6:30 pm, Oracle is hosting a reception to celebrate MySQL 5.6 going GA which is open to the public (but space

  [Read more...]
Percona MySQL University coming to Toronto this Friday!
+0 Vote Up -0Vote Down

Percona CEO Peter Zaitsev leads a track at Percona MySQL University in Raleigh, N.C. on Jan. 29, 2013.

Percona MySQL University, Toronto is taking place this Friday and I’m very excited about this event because it is a special opportunity to fit a phenomenal number of specific and focused MySQL technical talks all into one day, for free.

Over the course of the day we will cover some of the hottest topics in the MySQL space. There will be talks covering topics like MySQL 5.6, MySQL in the Cloud and High Availability for MySQL, as well as

  [Read more...]
Facebook at Percona Live MySQL Conference and Expo and Advanced Registration Ending Soon
+1 Vote Up -0Vote Down

Facebook is a major user of MySQL and has pushed the performance limits of the technology. Their MySQL experts have deep, hands on knowledge of the technology. I’m pleased to welcome Mark Callaghan, Software Engineer for Database Infrastructure at Facebook, back again this year to the Percona Live MySQL Conference & Expo to share his expertise. Mark was a keynote speaker at last

  [Read more...]
MySQL Backup tools used by Percona Remote DBA for MySQL
+0 Vote Up -1Vote Down

As part of Percona Remote DBA for MySQL service we recognize that reliable backups are one of the most important things we can bring to the table. In my experience handling emergencies, the single worst thing that can happen is finding out you don’t have backups available when some sort of data loss or catastrophic event occurs.

With our Remote DBA service we can take care of backups for you, what follows are some of the internals of our implementation.

What kind of outages can happen?

  • Someone runs UPDATE or DELETE and forgets the
  [Read more...]
Oracle Technical Experts at the Percona Live MySQL Conference and Expo
+12 Vote Up -0Vote Down

I’m pleased to announce that Oracle is sending some of their top technical people to speak at the Percona Live MySQL Conference and Expo. The conference takes place April 22-25, 2013 at the Santa Clara Convention Center and Hyatt Santa Clara.

Tomas Ulin, VP, MySQL Engineering for Oracle, will present an invited keynote talk on “Driving MySQL Innovation” during the Tuesday morning opening keynotes. With the recent release of MySQL 5.6, conference attendees will hear about the latest developments of this

  [Read more...]
InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!
+4 Vote Up -1Vote Down

InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

This is part 2 in a 3 part series. In part 1, we took a quick look at some initial configuration of InnoDB full-text search and discovered a little bit of quirky behavior; here, we are going to run some queries and compare the result sets. Our hope is that the one of two things will happen; either the results returned from a MyISAM FTS query will be exactly identical to the same query when performed against InnoDB data, OR that the results returned by InnoDB FTS will somehow be

  [Read more...]
Investigating MySQL Replication Latency in Percona XtraDB Cluster
+2 Vote Up -0Vote Down

Investigating MySQL Replication Latency in Percona XtraDB Cluster

I was curious to check how Percona XtraDB Cluster behaves when it comes to MySQL replication latency — or better yet, call it data propagation latency. It was interesting to see whenever I can get stale data reads from other cluster nodes after write performed to some specific node. To test it I wrote quite a simple script (you can find it in the end of the post) which connects to one node in the cluster, performs an update and then immediately does the read from second node. If the data has been

  [Read more...]
MySQL 5.5 lock_wait_timeout: patience is a virtue, and a locked server
+1 Vote Up -0Vote Down

MySQL 5.5 lock_wait_timeout: patience is a virtue, and a locked server

Like Ovais said in Implications of Metadata Locking Changes in MySQL 5.5, the hot topic these days is MySQL 5.6, but there was an important metadata locking change in MySQL 5.5.  As I began to dig into the Percona Toolkit bug he reported concerning this change apropos 

  [Read more...]
InnoDB Full-text Search in MySQL 5.6 (part 1)
+0 Vote Up -0Vote Down

I’ve never been a very big fan of MyISAM; I would argue that in most situations, any possible advantages to using MyISAM are far outweighed by the potential disadvantages and the strengths of InnoDB. However, up until MySQL 5.6, MyISAM was the only storage engine with support for full-text search (FTS). And I’ve encountered many customers for whom the prudent move would be a migration to InnoDB, but due to their use of MyISAM FTS, the idea of a complete or partial migration was, for one reason or another, an impractical solution. So, when FTS for InnoDB was first announced, I thought this might end up being the magic bullet that would help these sorts of customers realize all of the benefits that have been engineered into InnoDB over the past few years and still keep their FTS capability without having to make any significant code

  [Read more...]
Why do we care about MySQL Performance at High Concurrency?
+1 Vote Up -0Vote Down

In many MySQL Benchmarks we can see performance compared with rather high level of concurrency. In some cases (http://www.mysql.com/products/enterprise/scalability.html) reaching 4,000 or more concurrent threads which hammer databases as quickly as possible resulting in hundreds or even thousands concurrently active queries.

The question is how common is it in production ? The typical metrics to use for concurrency defined as number of queries being actually processed is “Threads_Running” which you can easily see for your production system:

root@smt2:/mnt/data/ mysqladmin extended -i1 | grep Threads_running
| Threads_running                               | 60          |
| Threads_running                               | 61          |
| Threads_running
  [Read more...]
Percona Server on the Raspberry Pi: Your own MySQL Database Server for Under $80
+0 Vote Up -1Vote Down

There are many reasons for wanting a small MySQL database server:

  • You’re a uni student who wants to learn the SQL language better and needs a mini-testbox
  • You’re a Windows user who wants to play around with Percona Server on Linux
  • You’re a corporate application developer who wants a small SQL development & test box
  • You’re a Internet startup that just needs a tiny startup database server without all the added costs

So, how about if you could setup a small Arch Linux ARMv6-based hardware device which runs Percona Server for MySQL, in a space not much bigger than your mouse, with the power consumption of only a smartphone charger, fully networked, all for under $80?

Introducing the Raspberry Pi with Percona Server:

  [Read more...]
Percona Welcomes MySQL 5.6!
+2 Vote Up -1Vote Down

MySQL 5.6 was made generally available as a production-ready solution earlier this month. This release comes about 2 years after MySQL 5.5 was released, but MySQL 5.6 contains improvements started long before that – for example, work on the Innodb Full Text Search project was started over 6 years ago, in addition with many optimizer and replication features. We’re happy to congratulate MySQL development team at Oracle with making this release happen.

In this blog post, I will not go into a features overview of MySQL 5.6. You can check out Release Notes for a good overview as well as many blog posts and articles written on this matter. Instead, I will tell you what is

  [Read more...]
MySQL 5.5 and 5.6 default variable values differences
+2 Vote Up -0Vote Down

As the part of analyzing surprising MySQL 5.5 vs 5.6 performance results I’ve been looking at changes to default variable values. To do that I’ve loaded the values from MySQL 5.5.30 and 5.6.10 to the different tables and ran the query:

mysql [localhost] {msandbox} (test) > select var55.variable_name,left(var55.variable_value,40) value55, left(var56.variable_value,40) var56  from var55 left join var56 on var55.variable_name=var56.variable_name where  var55.variable_value!=var56.variable_value;
+---------------------------------------------------+------------------------------------------+------------------------------------------+
| variable_name                                     | value55                                  |
  [Read more...]
Mystery Performance Variance with MySQL Restarts
+4 Vote Up -0Vote Down

Based on a lot of surprising comments about my MySQL 5.5 vs 5.6 performance post I decided to perform deeper investigation to see where my results could go possibly wrong. I had set up everything to be as simple as possible to get maximally repeatable results. I did Read Only ran which is typically a lot more repeatable (though also less relevant for production like workload). I had done number of iterations for benchmark run and I used dedicated physical hardware box so external environment impact often causing problems in Virtualized environments can be eliminated. Still I found there could be large variance between the runs.

I set up the benchmarks run to go over night in the loop, doing the benchmark run for 5 runs when restarting MySQL server and

  [Read more...]
Analyzing Slow Query Table in MySQL 5.6
+2 Vote Up -0Vote Down

Analyzing SQL Queries with Percona Toolkit, Feb. 25-28, 9-11 a.m. PST

Next week I’m teaching an online Percona Training class, called Analyzing SQL Queries with Percona Toolkit.  This is a guided tour of best practices for pt-query-digest, the best tool for evaluating where your database response time is being spent.

This month we saw the GA release of MySQL 5.6, and I wanted to check if any improvement has been made to the slow query log in table format.  Users of some MySQL

  [Read more...]
Can’t Create Thread: Errno 11 (A Tale of Two Ulimits)
+2 Vote Up -0Vote Down

Recently some of my fellow Perconians and I have noticed a bit of an uptick in customer cases featuring the following error message:

SQLSTATE[HY000] [1135] Can't create a new thread (errno 11); if you are not 
out of available memory, you can consult the manual for a possible OS-dependent bug.

The canonical solution to this issue, if you do a bit of Googling, is to increase the number of processes / threads available to the MySQL user, typically by adding a line like this to /etc/security/limits.conf:

mysql  soft  nproc  4096

followed up by a restart of MySQL in a fresh user session. If you’re running RHEL/CentOS 5, Ubuntu, or versions of Fedora prior to Fedora 9, this solution will most likely solve your problem. But with RHEL/CentOS 6 (and derivatives) and Fedora 9 and later, you’ll likely find that this tweak no longer

  [Read more...]
Implications of Metadata Locking Changes in MySQL 5.5
+6 Vote Up -0Vote Down

While most of the talk recently has mostly been around the new changes in MySQL 5.6 (and that is understandable), I have had lately some very interesting cases to deal with, with respect to the Metadata Locking related changes that were introduced in MySQL 5.5.3. It appears that the implications of Metadata Locking have not been covered well, and since there are still a large number of MySQL 5.0 and 5.1 installations that would upgrade or are in the process of upgrading to MySQL 5.5, I thought it necessary to discuss what these implications exactly are.

To read what Metadata Locking exactly is please read this section here in the MySQL manual.

Let’s start off with having a look at the Meta Data Locking behavior prior to MySQL 5.5.3

Metadata  [Read more...]

How Can Percona MySQL Server Development Services Help ?
+2 Vote Up -0Vote Down

At Percona we offer a number of services. One of them, Custom MySQL Server Development, is commonly the most misunderstood and undervalued. There are a lot of ways Percona custom MySQL server development can help your business be more successful with MySQL. Here are some ways:

Bugs – There are Bugs in MySQL, Percona Server, and other products. By far the best and most cost efficient way to deal with Bugs is to have a Percona MySQL Support subscription which includes all you can eat bug fixes. If you need just one bug fixed this may be a way to go. Many bugs we’re tasked to fix are actually bugs in MySQL which the Oracle MySQL development team has not fixed. This does not only apply to “community reported

  [Read more...]
MySQL 5.6: Improvements in the Nutshell
+8 Vote Up -0Vote Down

Preparing for my talk for Percona MySQL University in Raleigh,NC, Tuesday 29th of January I have created the outline of improvements available in MySQL 5.6 which I thought was worth sharing to give a feel for how massive work have been done for this release in variety of areas. I’m sure the list is not complete so If I miss something significant please let me know through the comment and I’ll update the page

Scalability
- Scalable Read Only Transactions
- Concurrent Innodb data file extension
- Non-Recursive Deadlock Detection
- Faster Locking Primitives
- Improved Innodb Thread Concurrency
- Multiple background Purge Threads
- Improved Purge lag control (now works)
- Split of “Kernel Mutex”
-








  [Read more...]
Fun with the MySQL pager command
+2 Vote Up -0Vote Down

Last time I wrote about a few tips that can make you more efficient when using the command line on Unix. Today I want to focus more on pager.

The most common usage of pager is to set it to a Unix pager such as less. It can be very useful to view the result of a command spanning over many lines (for instance SHOW ENGINE INNODB STATUS):

mysql> pager less
PAGER set to 'less'
mysql> show engine innodb status\G
[...]

Now you are inside less and you can easily navigate through the result set (use q to quit, space to scroll down, etc).

Reminder: if you want to leave your custom pager, this is easy, just run pager:

mysql> pager
Default pager wasn't set, using stdout.

Or

  [Read more...]
Profiling MySQL Memory Usage With Valgrind Massif
+1 Vote Up -0Vote Down

There are times where you need to know exactly how much memory the mysqld server (or any other program) is using, where (i.e. for what function) it was allocated, how it got there (a backtrace, please!), and at what point in time the allocation happened.

For example; you may have noticed a sharp memory increase after executing a particular query. Or, maybe mysqld is seemingly using too much memory overall. Or again, maybe you noticed mysqld’s memory profile slowly growing overtime, indicating a possible memory bug.

Whatever the reason, there is a simple but powerful way to profile MySQL memory usage; the Massif tool from Valgrind. An excerpt from the Massif manual page (Heap memory being simply the allotted pool of memory for use by

  [Read more...]
How does MySQL Replication really work?
+1 Vote Up -0Vote Down

While we do have many blog posts on replication on our blog, such as on replication being single-threaded, on semi-synchronous replication or on estimating replication capacity, I don’t think we have one that covers the very basics of how MySQL replication really works on the high level. Or it’s been so long ago I can’t even find it. So, I decided to write one now.

Of course, there are many aspects of MySQL replication, but my main focus will be the logistics – how

  [Read more...]
Is there room for more MySQL IO Optimization?
+2 Vote Up -0Vote Down

I prefer to run MySQL with innodb_flush_method=O_DIRECT in most cases – it makes sure there is no overhead of double buffering and I can save the limited amount of file system cache I would normally have on database server for those things which need to be cached — system files, binary log, FRM files, MySQL MyISAM system tables etc. Starting MySQL 5.5 MySQL uses asynchronous IO which should allow it to load IO subsystem very effectively being able to issue many outstanding requests which when can be merged on OS level or RAID controller so we should expect at least as good performance from O_DIRECT as from buffered mode ? It turns out it is not always the case.

I came to this take by accident so there is not a lot of science in coming up with it but I think it is still pretty well representative. I have an old test

  [Read more...]
Percona Toolkit by example – pt-stalk
+1 Vote Up -0Vote Down

pt-stalk recipes: Gather forensic data about MySQL when a server problem occurs

It happens to us all from time to time: a server issue arises that leaves you scratching your head. That’s when Percona Toolkit’s pt-stalk comes into play, helping you diagnose the problem by capturing diagnostic data that helps you pinpoint what’s causing the havoc hitting your database.

From the documentation (http://www.percona.com/doc/percona-toolkit/pt-stalk.html):

pt-stalk watches for a trigger condition to become true, and then collects data to help in diagnosing problems. It is designed to run as a daemon with root privileges, so that you can diagnose intermittent problems that you cannot observe directly. You can also use it

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