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

Displaying posts with tag: tips (reset)

9 Tips for Going in Production with Galera Cluster for MySQL
+0 Vote Up -0Vote Down
August 25, 2014 By Severalnines

Are you going in production with Galera Cluster for MySQL? Here are 9 tips to consider before going live. These are applicable to all 3 Galera versions (Codership, Percona XtraDB Cluster and MariaDB Galera Cluster). 

 

1. Galera strengths and weaknesses

 

There are multiple types of replication and cluster technologies for MySQL, make sure you understand how Galera works so you set the right expectations.

  [Read more...]
Q&A: Even More Deadly Mistakes of MySQL Development
+1 Vote Up -0Vote Down

On Wednesday I gave a presentation on “How to Avoid Even More Common (but Deadly) MySQL Development Mistakes” for Percona MySQL Webinars.  If you missed it, you can still register to view the recording and my slides.

Thanks to everyone who attended, and especially to folks who asked the great questions.  I answered as many as we had time for

  [Read more...]
How to improve InnoDB performance by 55% for write-bound loads
+2 Vote Up -0Vote Down

During April’s Percona Live MySQL Conference and Expo 2014, I attended a talk on MySQL 5.7 performance an scalability given by Dimitri Kravtchuk, the Oracle MySQL benchmark specialist. He mentioned at some point that the InnoDB double write buffer was a real performance killer. For the ones that don’t know what the innodb double write buffer is, it is a disk buffer were pages are written before being written to the actual data file. Upon restart, pages in the double write buffer are rewritten to their data files if complete. This is to avoid data file corruption with half written pages. I knew it has an impact on performance, on ZFS since it is transactional I always disable it, but I never realized how important the performance

  [Read more...]
Q&A: Common (but deadly) MySQL Development Mistakes
+1 Vote Up -0Vote Down

On Wednesday I gave a presentation on “How to Avoid Common (but Deadly) MySQL Development Mistakes” for Percona MySQL Webinars. If you missed it, you can still register to view the recording and my slides.

Thanks to everyone who attended, and especially to folks who asked the great questions. I answered as many as we had time for during the session, but here are all the questions with my complete answers:

Q: Does a JOIN operation between two tables always produce an “access table” on the rows of

  [Read more...]
Hot Off The Press: MySQL February Newsletter
Employee_Team +6 Vote Up -1Vote Down

The MySQL February Newsletter is available! Find out the latest news on MySQL products and MySQL Connect 2014, and read the technical tips written by MySQL experts at Oracle and in the community. Below are the highlights in this edition:

  • Start Preparing for MySQL Connect 2014 Call for Papers
  • New GA Release: MySQL for Visual Studio
  • Blog: State of the UNION
  • Blog: New MySQL Web Installer -- Have You Tried It Yet?
  • Blog: MySQL Workbench: Frequent Crashes on Mac OS X? This Might Be The Fix
  • Event: MySQL Seminars Are Coming to a City Near You

You can read it online or

  [Read more...]
Copy Data Between MySQL Databases with Sequel Pro
+0 Vote Up -0Vote Down

Sequel Pro

I often use Sequel Pro when I'm getting up to speed on the data model for a project or when I just want to debug in a more visual way than with the mysql command-line client. It's a free OS X application that lets you inspect and manage MySQL databases. I also find it very useful for making small changes to the data while I develop and test web apps.

Quickly Copy Data Between Databases

I recently needed a way to copy a few dozen records from one camp to another. I tried using the "SELECT...INTO OUTFILE" method but ran into a permissions issue with that approach. Using

  [Read more...]
innodb_stats_on_metadata and slow queries on INFORMATION_SCHEMA
+2 Vote Up -0Vote Down

INFORMATION_SCHEMA is usually the place to go when you want to get facts about a system (how many tables do we have? what are the 10 largest tables? What is data size and index size for table t?, etc). However it is also quite common that such queries are very slow and create lots of I/O load. Here is a tip to avoid theses hassles: set innodb_stats_on_metadata to OFF.

This is a topic we already talked about, but given the number of systems suffering from INFORMATION_SCHEMA slowness, I think it is good to bring innodb_stats_on_metadata back on the table.

The problem

Let’s look at a system I’ve seen recently: MySQL 5.5, working set fitting

  [Read more...]
Calculating timezone offsets
+4 Vote Up -0Vote Down

Time zones are a tricky feature. You live in a given time zone, and most of the time you won’t think about that at all. You may live in a place where you are conscious of time zones, such as the United States, if your business spans across the country, where you know that New York is three hours ahead of San Francisco or Chicago and Dallas share the same time zone. Time Zone support in MySQL is a complicate business in itself. Once you have updated your time zone tables, you can set your time zone in an human readable format:

set global time_zone="America/Los_Angeles";

This is nice and well. It tells you which time zone your server is working with. However, things get



  [Read more...]
Working with comma separated list MySQL options
+1 Vote Up -0Vote Down
Over time, some options have crept into the MySQL server which are comma separated lists of options. These include SQL_MODE, optimizer_switch, optimizer_trace and a few other variables.

Optimizer_switch is particularly problematic to work with as it contains many options and is hard to interpret. This is what you usually see when you examine optimizer_switch:

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on



As you can see, seeing which option is on or off is rather difficult. You can use the REPLACE function to make this easier:
mysql> select replace(@@optimizer_switch,






  [Read more...]
Webinar: SQL Query Patterns, Optimized
+2 Vote Up -0Vote Down

Next Friday, May 31 at 10 a.m. Pacific, I’ll present Percona’s next webinar, “SQL Query Patterns, Optimized.”

Based on my experiences solving tough SQL problems for Percona training and consulting, I’ll classify several common types of queries with which developers struggle. I’ll test several SQL solutions for each type of query objective, and show how you can use MySQL 5.6 built-in methods to analyze them for optimal query

  [Read more...]
An incomplete list of what your developers would like to know before migrating to MySQL 5.5
+0 Vote Up -0Vote Down

A few years ago, I asked to check with me in the long (very long) change history of MySQL 5.5 documentation what are the changes in relation to the SQL syntax.
Chris Calender helped me to retrieve a list of the main changes, thanks again Chris.

Today, I would like to share this list with you.
It is simply a curated transcript of what you might find in the documentation but I’m sure it can help some of you.
 

INTO clause in nested SELECT statements

 
Previously, the parser accepted an INTO clause in nested SELECT statements, which is invalid because such statements must return their results to the outer context. As of MySQL 5.5.3, this syntax is no longer permitted and statements that use it must be changed.
 

Table aliases in DELETE statements






  [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...]
Sphinx search performance optimization: multi-threaded search
+2 Vote Up -0Vote Down

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



  [Read more...]
Sphinx search performance optimization: attribute-based filters
+3 Vote Up -0Vote Down

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)


  [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...]
Webinar: Avoiding common traps when designing a MySQL application
+1 Vote Up -0Vote Down

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

  [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...]
How to STOP SLAVE on Amazon RDS read replica
+1 Vote Up -0Vote Down

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



  [Read more...]
Tips for Leveraging Oracle OpenWorld 2012 From Pythian Marketing
+0 Vote Up -0Vote Down
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!
+1 Vote Up -0Vote Down

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
  [Read more...]
How I use twitter to follow the MySQL Community
+2 Vote Up -0Vote Down

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






  [Read more...]
How to selectively kill queries in MySQL?
+1 Vote Up -0Vote Down

For as long as it is only about a few of them, it is as simple as looking at the SHOW PROCESSLIST output for thread identifiers to kill. They can be found in the first column called Id. These values can be passed to KILL thread_id command in MySQL. The problems appear with more complex scenarios. What if one needs to terminate all queries running longer than ten seconds? Doing copy&paste repetitively could take a lot of time with twenty or so candidate threads. This can be done much more efficiently.

INFORMATION_SCHEMA to the rescue!

Earlier today we showed “Anohter way to work with MySQL process list”. That post presents how PROCESSLIST table can be used to retrieve any

  [Read more...]
Anohter way to work with MySQL process list
+1 Vote Up -0Vote Down

In an earlier post titled “How to work with a long process list in MySQL”, we showed a neat way to work with the process list by using various shell tools. But some of that can also be done using pure SQL.

Since version 5.0 a lot of MySQL meta and runtime information can be accessed by reading from predefined views in a database called INFORMATION_SCHEMA. The information which can be found there includes lists of threads, tables, user created views, triggers, stored procedures and many, many other things. The newer the MySQL version, the more items can found in there.

This post is about the process list, so it will focus on just one of the tables called PROCESSLIST. Its structure is virtually

  [Read more...]
Was a query served from MySQL Query Cache?
+1 Vote Up -0Vote Down

The MySQL query cache is a special buffer, where database stores the text of a SELECT statement together with the corresponding result that was sent to the client. For as long as no table that a statement refers to changes in any way, including the contents, the cached result can be re-used to answer any identical sub-sequent SELECT statements. But how to tell whether a query was executed or returned from the cache?

There are at least three ways to check it.

Method 1

MySQL exposes a number of runtime statistics that are accessible with SHOW STATUS statement. Among the long list of various counters, one is called Com_select which shows how many times a SELECT statement was executed. However if a SELECT is served from the query cache, it does not actually execute, so it is not

  [Read more...]
How to find MySQL configuration file?
+2 Vote Up -0Vote Down

A customer called me today asking for help with locating the configuration file used by one of their production MySQL instances. From the description I was given it appeared that their server had at least six different copies of my.cnf file in different locations on disk. And all were similar enough that each could actually be the one. All superfluous files were the result of a bit negligent system administration. So what turned to be the quickest and the least destructive way to find the correct one?

Initially suspecting the server was simply running more than just one MySQL instance, I logged in to take a deeper look. But I found only one mysqld process and, indeed, several configuration files.

All of them seemed good candidates:

/etc/my.cnf
/etc/mysql/my.cnf
/var/lib/mysql/my.cnf
...

In many cases you

  [Read more...]
How to work with a long process list in MySQL
+0 Vote Up -0Vote Down

I am generally a big fan of command line tools. This also applies to MySQL client software such as mysql or mysqladmin. To those spoiled by graphical interfaces, working in text mode may seem crude or even difficult. But the truth is that once you get used to these tools, you will be able to accomplish many things a lot faster than with any GUI client. Of course, using text terminal, which is the environment for any command line tool, has its drawbacks and limitations. For example on a relatively busy MySQL server, every so often when you run SHOW [FULL] PROCESSLIST, which lists client threads connected to a database, you can receive an output that will be many screens long. Sometimes it might be due to the high number of established connections – each takes at least one line on the screen, or sometimes due to some longish

  [Read more...]
MySQL Backup and Restore
+0 Vote Up -0Vote Down
Making a compressed backup

mysqldump -u root -p database_name | bzip2 > output.sql.bz2
Restoring the compressed backup

shell> bunzip2 < output.sql.bz2 | mysql -u root -p
Copy database from one server to another

mysqldump db-name | ssh user@remote.box.com mysql -h remote.com db-name
OR

mysqldump -u username -p'password' db-name | ssh user@remote.com mysql -u username -p'password -h remote.com db-name
Copy only table foo to database bar

mysqldump db-name foo | ssh user@remote.box.com mysql bar
OR

mysqldump -u user -p'password' db-name foo | ssh user@remote.com mysql -u user -p'password' db-name foo
Add a Prefix to a fields value
+0 Vote Up -0Vote Down
Here's how to update your fields value by adding a prefix on it. You will have to use some functions in your WHERE statement to filter only those records that you want to be updated.

UPDATE `database`.`table`
SET field_value = CONCAT('PREFIX', field_value)
WHERE field_value = var;
Setting up XFS on Hardware RAID — the simple edition
+2 Vote Up -0Vote Down

There are about a gazillion FAQs and HOWTOs out there that talk about XFS configuration, RAID IO alignment, and mount point options.  I wanted to try to put some of that information together in a condensed and simplified format that will work for the majority of use cases.  This is not meant to cover every single tuning option, but rather to cover the important bases in a simple and easy to understand way.

Let’s say you have a server with standard hardware RAID setup running conventional HDDs.

RAID setup

For the sake of simplicity you create one single RAID logical volume that covers all your available drives.  This is the easiest setup to configure and maintain and is the best choice for operability in the majority of normal configurations.  Are there ways to squeeze more performance out of a server by dividing the logical volumes: perhaps,

  [Read more...]
TIL: Lookout For DEFINER
+0 Vote Up -0Vote Down

The Issue


I haven't blogged in a while an I have a long TODO list of things to publish: The repository for the SNMP Agent, video and slides of my OSCON talk and a quick overview of MHA master-master support. In the meantime, here's a little fact that I didn't know from MySQL CREATE VIEW documentation:

Although it is possible to create a view with a nonexistent DEFINER account, an error occurs when the view is referenced if the SQL SECURITY value is DEFINER but the definer account does not exist.
How can this be possible?

The Problem

For a number of reasons we don't have the same user accounts on the master than we have on the slaves (ie: developers shouldn't be querying the



  [Read more...]
Showing entries 1 to 30 of 132 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.