Showing entries 21 to 30 of 143
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: tips (reset)
Analyzing Slow Query Table in MySQL 5.6

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 appliances like Amazon RDS must use table logs, since they can’t access the file-based logs on the server.

I read the logging …

[Read more]
Sphinx search performance optimization: multi-threaded search

Queries in MySQL, Sphinx and many other database or search engines are typically single-threaded. That is when you issue a single query on your brand new r910 with 32 CPU cores and 16 disks, the maximum that is going to be used to process this query at any given point is 1 CPU core and 1 disk. In fact, only one or the other.

Seriously, if query is CPU intensive, it is only going to be using 3% of the available CPU capacity (for the same 32-core machine). If disk IO intensive – 6% of the available IO capacity (for the 16-disk RAID10 or RAID0 for that matter).

Let me put it another way. If your MySQL or Sphinx query takes 10s to run on a machine with a single CPU core and single disk, putting it on a machine with 32 such cores and 16 such disks will not make it any better.

But you knew this already. Question is – can you do something about it?

In case of Sphinx – indeed you can! And with very little …

[Read more]
Sphinx search performance optimization: attribute-based filters

One of the most common causes of a poor Sphinx search performance I find our customers face is misuse of search filters. In this article I will cover how Sphinx attributes (which are normally used for filtering) work, when they are a good idea to use and what to do when they are not, but you still want to take advantage of otherwise superb Sphinx performance.

The Problem

While Sphinx is great for full text search, you can certainly go beyond full text search, but before you go there, it is a good idea to make sure you’re doing it the right way.

In Sphinx, columns are basically one of two kinds:

a) full text
b) attributes

Speaking in MySQL terms, Full text columns are always indexed and using the very …

[Read more]
How does MySQL Replication really work?

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 replication events are written on the master, how they are transferred to the replication slave and then how …

[Read more]
Webinar: Avoiding common traps when designing a MySQL application

On January 16th at 10 AM Pacific/1 PM Eastern, I will give a webinar about the main traps that are awaiting you when designing and building a stable and high-performance MySQL application.

I will discuss a broad range of topics, from hardware and backups to instrumentation and indexing. I often see during my consulting practice wrong configuration putting data at risk or huge wastes of money to buy powerful hardware where a few indexes could have done the same result.

So if you think your MySQL servers are not in an optimal state, I invite you to sign up on this page (follow the “Register” link).

See you next week!

The post Webinar: Avoiding common traps when …

[Read more]
Percona Toolkit by example – pt-stalk

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 to execute a custom command, or to gather the data on demand without waiting for the trigger to happen. …

[Read more]
How to STOP SLAVE on Amazon RDS read replica

We are doing a migration from Amazon RDS to EC2 with a customer. This, unfortunately, involves some downtime – if you are an RDS user, you probably know you can’t replicate an RDS instance to an external server (or even EC2). While it is annoying, this post isn’t going to be a rant on how RDS can make you feel locked in. Instead, I wanted to give you a quick tip.

So here’s the thing – you can’t stop replication on RDS read replica, because you don’t have (and won’t get) privileges to do that:

replica> STOP SLAVE;
ERROR 1045 (28000): Access denied for user 'usr'@'%' (using password: YES)

Normally, you don’t want to do that, however we wanted to run some pt-upgrade checks before we migrate and for that we needed the read replica to stop replicating. Here’s one way to do it:

WARNING! …

[Read more]
Tips for Leveraging Oracle OpenWorld 2012 From Pythian Marketing

With Oracle OpenWorld just around the corner & MySQL Connect already underway I can’t believe yet another year has passed.  This is my third OOW and I must have a following as folks are already reaching out to me on twitter @pythiansimmons (log buffer lady seems to be a handle I can’t seem to shake). [...]

Here’s a quick way to Foresee if Replication Slave is ever going to catch up and When!

If you ever had a replication slave that is severely behind, you probably noticed that it’s not catching up with a busy master at a steady pace. Instead, the “Seconds behind master” is going up and down so you can’t really tell whether the replica is catching up or not by looking at just few samples, unless these are spread apart. And even then you can’t tell at a glance when it is going to catch up.

Normally, the “severely behind” thing should not happen, but it does often happen in our consulting practice:

  • sometimes replication would break and then it needs to catch up after it is fixed,
  • other times new replication slave is built from a backup which is normally hours behind,
  • or, it could be that replication slave became too slow to catch up due to missing index

Whatever the case is, single question I am being asked by the customer every time this happens is this: …

[Read more]
How I use twitter to follow the MySQL Community

Here is a perfect post to read during your holidays :-)
There are many ways to get news on the internet : blogs, rss feeds, facebook, linkedin, twitter…
What I want to talk about here is how I use twitter to follow the MySQL Community and how I stay up to date of the latest news.
I use twitter only for focus on the MySQL news and the MySQL community, that’s why I would like to share this experience with you.

Step 1 : Choose your friends

Q: What is the most complicated with Twitter ?
A: Read all the tweets that we receive every day

Of course it can be complicated and that takes a long long time…
That’s why you must choose who is your best friends carefully.

Except for the MySQL rock stars, I advise you to use TweetStats before to blindly follow anyone who tweeted a dark grigri

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