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 61 to 90 of 132 Next 30 Older Entries

Displaying posts with tag: tips (reset)

Debugging problems with row based replication
+3 Vote Up -3Vote Down

MySQL 5.1 introduces row based binary logging.  In fact, the default binary logging format in GA versions of MySQL 5.1 is 'MIXED' STATEMENT*;   The binlog_format  variable can still be changed per sessions which means it is possible that some of your binary log entries will be written in a row-based fashion instead of the actual statement which changed data, even when the global setting on the master is to write binary logs in statement mode.   The row-based format does offer advantages particularly if triggers or stored procedures are used, or if non deterministic functions like RAND() are used in DML statements.

A statement based replication slave can get out of sync with the master fairly easily, especially if data is changed on the slave.   It is possible for a statement to execute successfully on a slave even if the data is not 100% in

  [Read more...]
Simple Backup Restore Trick
+2 Vote Up -1Vote Down
I don't usually post these simple tricks, but it came to my attention today and it's very simple and have seen issues when trying to get around it. This one tries to solve the question: How do I restore my production backup to a different schema? It looks obvious, but I haven't seen many people thinking about it.

Most of the time backups using mysqldump will include the following line:

USE `schema`;

This is OK when you're trying to either (re)build a slave or restore a production database. But what about restoring it to a test server in a different schema?

The actual trick

Using vi (or similar) editors to edit the line will most likely result in the editor trying to load the whole backup file into memory, which might cause paging or even crash the server if the backup is big

  [Read more...]
MySQL master/slave support merged into Linux-HA
+0 Vote Up -0Vote Down

(Re-posted from Florian’s blog.)

MySQL replication support for the Pacemaker cluster manager (the stuff that we explained in this webinar) has made it into the Linux-HA resource agents default branch. If you are interested in testing — and you should! — please read the extended announcement. Feedback is extremely welcome on the linux-ha-dev mailing list.

We are expecting to release this as part of resource-agents 1.0.4, in late May/early June.

Developer Tips using MySQL
+0 Vote Up -0Vote Down

I get ask, by application developers,  “how do you optimize MySQL”.  I do lots of things that don’t really relate to a developer. I analyze the percent of queries are being pulled from cache for instance.  What a developer can do to optimize the SQL they develop is a different questions.   So here is a quick list of things applications developers should know about MySQL.

Explain will analyze your query.

This example shows the possible indexes (keys) that could be used and the index that was selected.  2,262 rows where selected and then sorted (Using file sorts) and one record was returned (limit 1).

mysql> explain SELECT 5/9*(temp_F-32) as t, 5/9*(dewpt_F-32) as td, speed_mps as spd, dir
 > where stn='KLDM' and
  [Read more...]
fadvise – may be not what you expect
+3 Vote Up -0Vote Down

I often hear suggestion to use fadvise system call to avoid caching in OS cache.
We recently made patch for tar, which supposes to create archive without polluting OS cache, as like in case with backup, you do not really expect any benefits from caching.

However working on the patch, I noticed, that fadvise with FADV_DONTNEED, does not really do what I expected (I used this call as it is often suggested for this purpose). In fact it does not prevent caching, it only releases already cached data.

And if we do man fadvise, it says exactly:
Do not expect access in the near future. Subsequent access of pages in this range will succeed, but will
result either in reloading of the memory contents from the underlying mapped

  [Read more...]
Too many connections? No problem!
+8 Vote Up -0Vote Down

Did that ever happen to you in production?

  • [percona@sandbox msb_5_0_87]$ ./use
  • ERROR 1040 (00000): Too many connections
  • Just happened to one of our customers. Want to know what we did?

    For demo purposes I'll use sandbox here (so the ./use is actually executing mysql cli). Oh and mind it is not a general-purpose best-practice, but rather a break-and-enter hack when the server is flooded. So, when this happens in production, the problem is - how do you quickly regain access to mysql server to see what are all the sessions doing and how do you do that without restarting the application? Here's the trick:

  • [percona@sandbox msb_5_0_87]$ gdb -p $(cat
  •   [Read more...]
    When should you store serialized objects in the database?
    +4 Vote Up -0Vote Down

    A while back Friendfeed posted a blog post explaining how they changed from storing data in MySQL columns to serializing data and just storing it inside TEXT/BLOB columns. It seems that since then, the technique has gotten more popular with Ruby gems now around to do this for you automatically.

    So when is it a good idea to use this technique?

    If the application really is schema-less and has a lot of optional parameters that do not appear in every record, serializing the data in one column can be a better idea than having many extra columns that are NULL. The restriction on this, would be that searching on these columns now becomes more difficult[1]. A good example of this optional nature of data is user

      [Read more...]
    Enabling IPv6 Support in nginx
    +0 Vote Up -0Vote Down

    This is going to be a really short post, but for someone it could save an hour of life.

    So, you’ve nothing to do and you’ve decided to play around with IPv6 or maybe you’re happened to be an administrator of a web service that needs to support IPv6 connectivity and you need to make your nginx server work nicely with this protocol.

    First thing you need to do is to enable IPv6 in nginx by recompiling it with --with-ipv6 configure option and reinstalling it. If you use some pre-built package, check if your nginx already has this key enabled by running nginx -V.

    The results should have --with-ipv6 option in configure arguments:

    [root@node ~]# nginx -V
    nginx version: nginx/0.7.64

      [Read more...]
    Getting around optimizer limitations with an IN() list
    +1 Vote Up -0Vote Down

    There was a discussion on LinkedIn one month ago that caught my eye:

    Database search by "within x number of miles" radius?

    Anyone out there created a zipcode database and created a "search within x numer of miles" function ?
    Thankful for any tips you can throw my way..


    A few people commented that some solutions wouldn't scale. To understand why these sorts of geographic search queries are problematic in MySQL, it's best to show some execution plans on dummy data:

      [Read more...]
    Linux/OSX: find out what network ports are in use
    +0 Vote Up -0Vote Down

    To get a quick idea of what ports you have open on your local box, you can use nmap.

    ~ jhaddad$ nmap localhost
    Starting Nmap 5.00 ( http://nmap.org ) at 2010-01-05 11:06 PST
    Interesting ports on localhost (
    Not shown: 499 closed ports, 492 filtered ports
    80/tcp open http
    88/tcp open kerberos-sec
    548/tcp open afp
    631/tcp open ipp
    3306/tcp open mysql
    3325/tcp open unknown
    5900/tcp open vnc
    9000/tcp open cslistener
    10000/tcp open snet-sensor-mgmt

    For more detailed information, try netstat:

    netstat -an

    You’ll get a breakdown of every socket open on your machine – useful for figuring out who’s connected and from where.

    The OSX

      [Read more...]
    A handy regular expression for 'tokenizing' a SQL statement
    +5 Vote Up -0Vote Down
    Often times I find myself having to handle a particular portion of a SQL statement via a script. I've written a lot of specialized regular expressions over time to handle these tasks, but the one that I've had to write the most is a basic 'tokenizer' which understands the quoting semantics of a MySQL statement. That is, it understands `BackTick`, 'Single Quote' and "Double Quoted" strings.

    #PHP Heredoc syntax
    $regex = <<< END_OF_REGEX
      [^ \"'`(),]*\([^)]*\)    #match functions like concat(x,"y",`a`.`z`) or sum(`xyz`);
      |\([^)]*?\)              #match grouped items
      |"[^"]*?"                #match double quoted items
      |'[^']*?'                #match single quoted items
      |`[A-Za-z0-9_ .'\"()+\\-&^%\$+?%\\/\\\\!`]+`  #match backtick mysql names
      |[^ ,]+                  #match keywords, operators and aliases

    EDIT: After some

      [Read more...]
    MySQL and Postgres command equivalents (mysql vs psql)
    +0 Vote Up -0Vote Down

    Users toggling between MySQL and Postgres are often confused by the equivalent commands to accomplish basic tasks. Here's a chart listing some of the differences between the command line client for MySQL (simply called mysql), and the command line client for Postgres (called psql).

    MySQL (using mysql)Postgres (using psql)Notes \c Clears the buffer\r (same) \d string Changes the delimiterNo equivalent \e Edit the buffer with external editor\e (same)Postgres also allows \e filename which will become the new buffer \g Send current query to the server\g (same) \h Gives help - general or specific\h (same) \n Turns the pager off\pset pager off (same)The pager is only used when needed based on number of rows; to force it on, use  [Read more...]
    Effect of adaptive_flushing
    +1 Vote Up -0Vote Down

    I recently had the chance to witness the effects of innodb_adaptive_flushing on the performance of InnoDB Plugin 1.0.5 in the wild, which Yasufumi wrote about previously here and here.

    The server in question was Solaris 10 with 8 disk RAID10 and 2 32GB SSDs used for ZIL and L2ARC, 72G RAM and 40G buffer pool. We started it up with innodb_adaptive_flushing=OFF and innodb_doublewrite=OFF, then ramped up traffic and everything looked stable ... but I noticed one troubling thing: ~2GB of uncheckpointed data.

    Database pages      2318457
    Old database pages  855816
    Modified db pages   457902
    Log flushed up to   10026890404067
    Last checkpoint at
      [Read more...]
    “Shard early, shard often”
    +1 Vote Up -0Vote Down

    I wrote a post a while back that said why you don't want to shard.  In that post that I tried to explain that hardware advances such as 128G of RAM being so cheap is changing the point at which you need to shard, and that the (often omitted) operational issues created by sharding can be painful.

    What I didn't mention was that if you've established that you will need to eventually shard, is it better to just get it out of the way early?  My answer is almost always no. That is to say I disagree with a statement I've been hearing recently; "shard early, shard often".  Here's why:

    • There's an order of magnitude better performance that can be gained by focusing on query/index/schema optimization.  The gains from sharding are usually much
      [Read more...]
    Tokyo Tyrant -The Extras Part III : Write Bottleneck
    +1 Vote Up -0Vote Down

    This is part 3 of my Tyrant extra's, part 1 focused on durability, part 2 focused on the perceived performance wall.

    #3.  Tokyo Cabinet Can have only a single writer thread, bottlenecking performance

    When writing an application using Tokyo Cabinet only one connection can be opened as a “writer”  while the rest are readers.  Tyrant allows for multiple “writes”  to be sent in from multiple applications but it still single threads them when writing out to disk.   If you run several threads all just inserting into Tyrant your will see tyrant hit 100% Cpu on 1 core, and your writes will start to peter out quickly.

    In my tests when I was not disk bound (FS Cache writes) I was able to complete 4Million

      [Read more...]
    Tokyo Tyrant – The Extras Part II : The Performance Wall
    +1 Vote Up -0Vote Down

    Continuing my look at Tokyo Tyrant/Cabinet and addressing some of the concerns I have seen people have brought up this is post #2.

    #2.  As your data grows does  Tokyo Cabinet slow down?

    Yes your performance can degrade. One obvious performance decrease with a larger dataset  is you start to increase the likelihood that your data no longer fits into memory.  This decreases the number of memory operations and trades them for more expensive disk based operations.    As fast as any application is, as you read off disk opposed to memory performance is going to drop off substantially.  One of the more difficult things to test with Tyrant is disk bound performance.  The FS Cache can make Tyrant seem like small amounts of memory will still make it scream.  Once your data set is larger then that, people start to claim they hit the

      [Read more...]
    MySQL-Memcached or NOSQL Tokyo Tyrant – part 3
    +4 Vote Up -1Vote Down

    This is part 3 of our series.  In part 1 we talked about boosting performance with memcached on top of MySQL, in Part 2 we talked about running 100% outside the data with memcached, and now in Part 3 we are going to look at a possible solution to free you from the database.  The solution I am going to discuss here is Tokyo Cabinet and Tyrant.

    I am not going to give you a primer  or Tutorial on Tyrant and Cabinet, there are plenty of these out there already.  Instead I want to see what sort of performance we can see compared to MySQL and Memcached, and later on other NoSQL solutions.  Tokyo actually allows you to use several types of databases that are supported,

      [Read more...]
    How (not) to find unused indexes
    +2 Vote Up -0Vote Down

    I've seen a few people link to an INFORMATION_SCHEMA query to be able to find any indexes that have low cardinality, in an effort to find out what indexes should be removed.  This method is flawed - here's the first reason why:

  • CREATE TABLE `sales` (
  • `id` int(11) NOT NULL AUTO_INCREMENT,
  • `customer_id` int(11) DEFAULT NULL,
  • `status` enum('archived','active') DEFAULT NULL,
  • PRIMARY KEY (`id`),
  • KEY `status` (`status`)
  • mysql&gt; SELECT count(*), STATUS FROM sales GROUP BY STATUS;
  • +----------+---------+
  • | count(*) | STATUS  |
  •   [Read more...]
    MySQL-Memcached or NOSQL Tokyo Tyrant – part 2
    +1 Vote Up -0Vote Down

    Part 1 of our series set-up our "test"  application and looked at boosting performance of the application by buffer MySQL with memcached.  Our test application is simple and requires only 3 basic operations per transaction 2 reads and 1 write.  Using memcached combined with MySQL we ended up nearly getting a 10X performance boost from the application.  Now we are going to look at what we could achieve if we did not have to write to the database at all.  So let's look at what happens if we push everything including writes into memcached.

    Wow that's shockingly fast isn't it! I guess being completely in memory helps for this app.  What is

      [Read more...]
    MySQL-Memcached or NOSQL Tokyo Tyrant – part 1
    +2 Vote Up -0Vote Down

    All to often people force themselves into using a database like MySQL with no thought into whether if its the best solution to there problem. Why?  Because their other applications use it, so why not the new application?  Over the past couple of months I have been doing a ton of work for clients who use their database like most people use memcached .  Lookup a row based on a key, update the data in the row, stuff the row back in the database.  Rinse and repeat.  Sure these setups vary sometimes, throwing in a “lookup” via username, or even the rare count.  But for the most part they are designed to be simple.

    A classic example is a simple online game.  An online game may only require that an application retrieve a single record from the database.  The record may contain all the vital stats for the game, be updated and stuffed back into

      [Read more...]
    Free and easy schema diff and patch
    +6 Vote Up -2Vote Down

    The easiest way to see the differences between two schemas on a non-Windows machine is to run:

    mysqldump -h server1 --no-data --all-databases > file1.sql
    mysqldump -h server2 --no-data --all-databases > file2.sql
    diff file1.sql file2.sql

    However, this will show also trivial differences, such as the value of AUTO_INCREMENT. It also does not give you a way to patch one schema to be like another.

    We frequently are asked to “do a schema diff and create a script that will ‘patch’ one server.” Usually this is done to take a development or test schema and move it to production for a release.

    We like to use the best tool for the job, and while diff is good, I like to use MySQL workbench. The OSS (Community) edition provides

      [Read more...]
    How to generate per-database traffic statistics using mk-query-digest
    +7 Vote Up -0Vote Down

    We often encounter customers who have partitioned their applications among a number of databases within the same instance of MySQL (think application service providers who have a separate database per customer organization ... or wordpress-mu type of apps). For example, take the following single MySQL instance with multiple (identical) databases:

    | Database |
    | db1      |
    | db2      |
    | db3      |
    | db4      |
    | mysql    |

    Separating the data in this manner is a great setup for being able to scale by simply migrating a subset of the databases to a different physical host when the existing host begins to get overloaded. But MySQL doesn't allow us to examine statistics on a per-database basis.

    Enter Maatkit.

    There is an often-ignored gem in

      [Read more...]
    Which adaptive should we use?
    +3 Vote Up -3Vote Down

    As you may know, InnoDB has 2 limits for unflushed modified blocks in the buffer pool. The one is from physical size of the buffer pool. And the another one is oldness of the block which is from the capacity of transaction log files.

    In the case of heavy updating workload, the modified ages of the many blocks are clustered. And to reduce the maximum of the modified ages InnoDB needs to flush many of the blocks in a short time, if these are not flushed at all. Then the flushing storm affect the performance seriously.

    We suggested the "adaptive_checkpoint" option of constant flushing to avoid such a flushing storm. And finally, the newest InnoDB Plugin 1.0.4 has the new similar option "adaptive_flushing" as native.

    Let's check the adaptive flushing options at this post.



      [Read more...]
    3 ways MySQL uses indexes
    +5 Vote Up -0Vote Down

    I often see people confuse different ways MySQL can use indexing, getting wrong ideas on what query performance they should expect. There are 3 main ways how MySQL can use the indexes for query execution, which are not mutually exclusive, in fact some queries will use indexes for all 3 purposes listed here.

    Using index to find rows The main purpose of the index is to find rows quickly - without scanning whole data set. This is most typical reason index gets added on the first place. Most popular index type in MySQL - BTREE can speed up equality and prefix range matches. So if you have index on (A,B) This index can be used to lookup rows for WHERE clauses like A=5 ; A BETWEEN 5 AND 10 ; A=5 AND B BETWEEN 5 AND 10 it however will NOT be able to help

      [Read more...]
    XtraDB: The Top 10 enhancements
    +4 Vote Up -1Vote Down

    Note: This post is part 2 of 4 on building our training workshop.

    Last week I talked about why you don't want to shard. This week I'm following up with the top 10 enhancements that XtraDB has over the built-in InnoDB included in MySQL 5.0 and 5.1.  Building this list was not really a scientific process - It's always difficult to say which feature is better than another, because a lot of it depends on the individual workload.  My ranking method was to pick the features that have the highest impact and are most applicable to all workloads first:

  • CPU scalability fixes - XtraDB improves performance on systems with

  •   [Read more...]
    Why you don’t want to shard.
    +6 Vote Up -1Vote Down

    Note: This blog post is part 1 of 4 on building our training workshop.

    The Percona training workshop will not cover sharding. If you follow our blog, you'll notice we don't talk much about the subject; in some cases it makes sense, but in many we've seen that it causes architectures to be prematurely complicated.

    So let me state it: You don't want to shard.

    Optimize everything else first, and then if performance still isn't good enough, it's time to take a very bitter medicine. The reason you need to shard basically

      [Read more...]
    Copying InnoDB tables between servers
    +2 Vote Up -0Vote Down

    The feature I announced some time ago http://www.mysqlperformanceblog.com/2009/06/08/impossible-possible-moving-innodb-tables-between-servers/ is now available in our latest releases of XtraBackup 0.8.1 and XtraDB-6.

    Now I am going to show how to use it (the video will be also available on percona.tv).
    Let's take tpcc schema and running standard MySQL ® 5.0.83, and assume we want to copy order_line table to different server. Note I am going to do it online, no needs to lock or shutdown server.

    To export table you need

      [Read more...]
    Recovery after DROP [ TABLE | DATABASE ]
    +2 Vote Up -0Vote Down

    In your recovery practice we often face the problem when data lost by execution DROP TABLE or DROP DATABASE statement. In this case even our InnoDB Data Recovery tool can't help, as table / directory with files was deleted (if you have innodb-file-per-table). And the same for MyISAM, all .MYD / .MYI / .frm - files are deleted in this case.

    So first step after DROP is to restore files, and for ext3 file system there are two utilities which can help of you are fast (and lucky) enough.
    First one is ext3grep http://code.google.com/p/ext3grep/, with some instruction on this page http://www.xs4all.nl/~carlo17/howto/undelete_ext3.html.
    And also there is

      [Read more...]
    Is DRBD the right choice for me?
    +3 Vote Up -0Vote Down

    It seems pretty common to find customers install DRBD for the wrong reasons. There are many pros/cons to compare DRBD to replication, but I've managed to cut down my spiel I give to customers to these two points:

    • DRBD's aim (assuming replication mode C) is to provide 100% consistency, and then as much uptime as possible.
    • MySQL Replication (with a manager such as MMM) aims to have 100% availability, at the potential loss of some data surrounding a failure.

    So if you are installing DRBD with the aim of purely "availability", and are not worried about losing that last write on the crash to your master database that (hopefully) happens only once every few years, you may be using the wrong technology.

    While the prized "1 minute failover" is possible in DRBD, it doesn't

      [Read more...]
    Just do the math!
    +2 Vote Up -0Vote Down

    One of the most typical reasons for performance and scalability problems I encounter is simply failing to do the math. And these are typically bad one because it often leads to implementing architectures which are not up for job they are intended to solve.

    Let me start with example to make it clear. Lets say you're doing some reports from your apache log files - how many distinct visitors hit the page and stuff like that. You picked full logs because they are great in flexibility - you can run any adhoc queries and drill down as much as you like. Initially traffic was small and young and with 10000 page views a day you few days of history the queries there instant which gave you a confidence this approach will work.

    As the time passes and you get 1.000.000 events per day and looking to do reporting for up to the whole year worth of data you find things not

      [Read more...]
    Previous 30 Newer Entries Showing entries 61 to 90 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.