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 31 to 60 of 110 Next 30 Older Entries

Displaying posts with tag: Maatkit (reset)

There is no apostrophe in Maatkit
+1 Vote Up -3Vote Down

Yes, Maatkit’s name was inspired by Ma’at, which has an apostrophe. But there is NO apostrophe in the name of the popular toolkit for MySQL users.

I’m just sayin’.

Related posts:

  • Learn about Maatkit at the MySQL Conference I’m
  • Speaking about Maatkit at CPOSC I’m
  • Making Maatkit more Open Source one step at a time If you
  • Related posts brought to you by Yet Another Related Posts Plugin.

    MySQL Permissions – Restarting MySQL
    +3 Vote Up -0Vote Down

    I am working with a client that is using managed hosting on dedicated servers. This has presented new challenges in obtaining the right permissions to undertake MySQL tasks but not have either ‘root’ or ‘mysql’ access and not have to involve a third party everytime.

    Adding the following to the /etc/sudoers file enabled the ability to restart MySQL.

    User_Alias	DBA = rbradfor, user2, etc
    Host_Alias 	DB_SERVERS = server1.example.com, server2.example.com, etc
    Cmnd_Alias	MYSQL = /etc/init.d/mysqld, /usr/sbin/tcpdump

    As you can see I also got tcpdump, which I find valuable to monitor via mk-query-digest.

    Next, permissions for log files.

    mk-query-digest now understands HTTP
    +1 Vote Up -0Vote Down

    You used to use mk-query-digest to aggregate and report on MySQL’s slow query log. Then it got the ability to grab query events from polling SHOW PROCESSLIST. Next we thought, really, how hard can it be to implement the libmysql wire protocol, so we can sniff TCP packets? … it’s hard, but not that hard as it turns out. But why stop there, why not implement memcached protocol too? I think you can see where this is headed.

    So now mk-query-digest is a tool that can understand and “do stuff with” a variety of query/response types of information. The latest is HTTP. HTTP traffic is just a query-response flow of events, perfectly suitable for response-time analysis. Example:

    baron@kanga:~$ mk-query-digest sample-http.txt --type http
    # 1.6s user time, 100ms system time, 14.20M
      [Read more...]
    Video: Building a MySQL Slave and Keeping it in Sync
    +5 Vote Up -0Vote Down

    Last night at the Boston MySQL User Group I presented on how to get a consistent snapshot to build a slave, how to use mk-table-checksum to check for differences between masters and slaves on an ongoing basis, and how to use tools such as mk-table-sync and mysqldump to sync the data if there are any discrepancies.

    The slides are online at http://technocation.org/files/doc/slave_sync.pdf.

    The video can be watched on youtube at http://www.youtube.com/watch?v=Un0wqYKmbWY or directly in your browser with the embedded player below:

    Catching erroneous queries, without MySQL proxy
    +8 Vote Up -0Vote Down

    MySQL Proxy is a really neat tool. I remember a few years back when I first saw Jan talking about it. Back in those days it was significantly different than it is now, but the concept remains the same: direct your database traffic through a man-in-the-middle. Chris Calender’s post on capturing erroneous queries with MySQL Proxy shows one use for Proxy. But wait. MySQL Proxy is just inspecting the MySQL protocol. And unless you’re using it for something else too, having a man in the middle to catch errors is like standing in the middle of the street and blocking traffic to count the cars on the street. Why don’t you stand on the sidewalk to count the cars instead?

    Observing without interrupting

    Maybe we can use tcpdump. If you search Google you’ll see lots of

      [Read more...]
    Zero is a big number
    +0 Vote Up -0Vote Down

    I made changes to mk-query-digest yesterday that I didn’t expect to cause any adverse affects. On the contrary, several tests began to fail because a single new but harmless line began to appear in the expected output: “Databases 0″. Perhaps I’m preaching to the choir, as you are all fantastic, thorough and flawless programmers, but as for myself I’ve learned to never take a single failed test for granted.

    One time a test failed because some values differed by a millisecond or two. Being curious I investigated and found that our standard deviation equation was just shy of perfect. I fixed it and spent hours cross-checking the myriad tiny values with my TI calculator. Probably no one cared about 0.023 vs. 0.022 but it’s the cultivation of a disposition towards perfection that matters.

    My innocuous changes yesterday introduced a case of

      [Read more...]
    Making changes to many tables at once
    +1 Vote Up -2Vote Down

    As an alternative to another recent blog post that answered the question “how can I truncate all the tables in my database,” I thought I’d show another way to do it, which does not use the INFORMATION_SCHEMA.

    $ wget http://www.maatkit.org/get/mk-find
    $ perl mk-find --exec 'TRUNCATE TABLE %D.%N'

    The other example is how to alter MyISAM tables to be InnoDB. That one’s easy, too. Let’s alter all MyISAM tables in the ‘test’ database:

    $ wget http://www.maatkit.org/get/mk-find
    $ perl mk-find test --engine MyISAM --exec 'ALTER TABLE %D.%N ENGINE=InnoDB'

    If you want to print out the commands instead of executing them, you can just use –printf instead of

      [Read more...]
    mk-table-sync and small tables
    +4 Vote Up -0Vote Down

    Issue 634 made me wonder how the various mk-table-sync algorithms (Chunk, Nibble, GroupBy and Stream) perform when faced with a small number of rows. So I ran some quick, basic benchmarks.

    I used three tables, each with integer primary keys, having 109, 600 and 16k+ rows. I did two runs for each of the four algorithms: the first run used an empty destination table so all rows from the source had to be synced; the second run used an already synced destination table so all rows had to be checked but none were synced. I ran Perl with DProf to get simple wallclock and user time measurements.

    Here are the results for the first run:

      [Read more...]
    Filtering and analyzing queries by year, month, hour and day with mk-query-digest
    +1 Vote Up -0Vote Down

    I originally posted this on the Maatkit discussion list:

    A little while ago a user asked in http://groups.google.com/group/maatkit-discuss/browse_thread/thread/256b6c780bdb066d if it was possible to use mk-query-digest to analyze queries per hour. I responded with a skeleton script for use with –filter, but I didn’t actually test this. Today, I filled out the script and tested it and found that it works. The script is available from trunk at:


      [Read more...]
    How to capture debugging information with mk-loadavg
    +1 Vote Up -2Vote Down

    Maatkit’s mk-loadavg tool is a helpful way to gather information about infrequent conditions on your database server (or any other server, really). We wrote it at Percona to help with those repeated cases of things like “every two weeks, my database stops processing queries for 30 seconds, but it’s not locked up and during this time there is nothing happening.” That’s pretty much impossible to catch in action, and these conditions can take months to resolve without the aid of good tools.

    In this blog post I’ll illustrate a very simple usage of mk-loadavg to help in solving a much smaller problem: find out what is happening on the database server during periods of CPU spikes that happen every

      [Read more...]
    Recap of CPOSC 2009, plus slides
    +1 Vote Up -0Vote Down

    Yesterday I attended CPOSC 2009. The conference was great. It was very well run, and I liked the sessions. I would definitely attend this conference again, and will recommend that Percona sponsor it next year. I attended the following talks:

    • Stop Worrying and Start Monitoring with Nagios (Andrew Libby)
    • DRBD, Network Raid, High Availability and General Awesomeness (Brian Gorka)
    • MySQL Performance Tuning for non-DBAs (Tom Clark)
    • Wonderful Desktop Tricks, and Aesthetics (Seth Jerome)
    • Jump Start Django: The Web Framework for Perfectionists with Deadlines (Rob Yates)
    • Watching and Manipulating Your Network Traffic (Josiah Ritchie)

    And then of course I gave my own talk on Maatkit (

      [Read more...]
    Unknown locale for statpack & maatkit
    +2 Vote Up -0Vote Down

    I had trouble today on a client site using my MySQL power tools Maatkit and Statpack.

    $ ~/scripts/statpack.py --files=mysql.status.091015.080001.txt,mysql.status.091015.090001.txt
    Traceback (most recent call last):
      File "/home/rbradfor/scripts/statpack.py", line 563, in ?
      File "/home/rbradfor/scripts/statpack.py", line 527, in main
        locale.setlocale(locale.LC_NUMERIC, '')
      File "/usr/lib64/python2.4/locale.py", line 381, in setlocale
        return _setlocale(category, locale)
    locale.Error: unsupported locale setting
    $ cat /var/log/slow-query.log | ./mk-query-digest
    perl: warning: Setting locale failed.
    perl: warning: Please check that your locale settings:
    	LANGUAGE = (unset),
    	LC_ALL = (unset),
    	LANG = "e_US"
        are supported and installed on your
      [Read more...]
    Take a look at mk-query-digest
    +4 Vote Up -0Vote Down

    Q: What SQL is running on your MySQL database server now?
    A: The bane of pain for MySQL DBA’s when there is no official MySQL instrumentation that is dynamic and fine grained sufficiently to solve this problem at the SQL interface.

    While hybrid solutions exist, the lack of dynamic and real-time are the issues. There is however great work being done by Baron and others on Maatkit mk-query-digest and packet sniffing the MySQL TCP packets.

    $ sudo tcpdump -i eth0 port 3306 -s 65535  -x -n -q -tttt | ./mk-query-digest --type tcpdump
    tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
    listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes
    # Caught SIGINT.
    5444 packets captured
    8254 packets received by filter
    2809 packets dropped by kernel

      [Read more...]
    Speaking at CPOSC 2009
    +0 Vote Up -0Vote Down

    I’ll be attending and presenting at the 2009 Central Pennsylvania Open-Source Conference. My session is on Maatkit. I see Tom Clark has a session on MySQL performance! I hope to see you there — I’ve really become a fan of these regional conferences.

    By the way, I’ve also created a speaker badge by adapting a wallpaper someone else made — you can find it on the sidebar of my blog if you’re also a speaker.

    Related posts:

  • Speaking about Maatkit at CPOSC I’m
  • Speaking at EdUI

  •   [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...]
    Verify master-master[||-slave] data consistency without locking or downtime
    +1 Vote Up -0Vote Down

    We all knew that we are risking with MMM. Risking, and placing availability as a more important like consistency.  But non of us can risk loosing data forever but we show using it, regarding to our conversations think:  "I can fix my data later on, but I can’t turn back time and prevent the downtime. (Pascal Hofmann@xaprb.com)".

    As I wrote before about staying online, now let me write about how to stay consistent.

    We all know, mmm is not like a key of salvation, but its getting close to it . While MySQL doesn't support multi-master-slave environments from it's source code, we will sleep badly wondering on the safety of our precious databases.

    But its not just about MMM, a few days ago we ran in to a well known InnoDB

      [Read more...]
    A script snippet for aggregating GDB backtraces
    +3 Vote Up -0Vote Down

    A short time ago in a galaxy nearby, Domas Mituzas wrote about contention profiling with GDB stack traces. Mark Callaghan found the technique useful, and contributed an awk script (in the comments) to aggregate stack traces and identify which things are blocking most threads. I’ve used it myself a time or five. But I’ve found myself wanting it to be fancier, for various reasons. So I wrote a little utility that can aggregate and pretty-print backtraces. It can handle unresolved symbols, and aggregate by only the first N lines of the stack trace. Here’s an example of a mysqld instance that’s really, really frozen up:

    bt-aggregate -4 samples/backtrace.txt | head -n12
      [Read more...]
    Speaking about Maatkit at CPOSC
    +1 Vote Up -0Vote Down

    I’m going to present on Maatkit at the CPOSC conference in central Pennsylvania on Saturday, October 17th 2009. I’ll give an overview of the toolkit, which is no longer an easy task in a single session. I see a number of other interesting sessions have been accepted. It looks like it’ll be a good conference.

    Related posts:

  • Speaking at EdUI Conference 2009 I’m
  • Learn about Maatkit at the MySQL Conference I’m
  • Presentation uploaded for Maatkit talk at MySQL
  •   [Read more...]
    How to find un-indexed queries in MySQL, without using the log
    +4 Vote Up -0Vote Down

    You probably know that it’s possible to set configuration variables to log queries that don’t use indexes to the slow query log in MySQL. This is a good way to find tables that might need indexes.

    But what if the slow query log isn’t enabled and you are using (or consulting on) MySQL 5.0 or earlier, where it can’t be enabled on the fly unless you’re using a patched server such as Percona’s enhanced builds? You can still capture these queries.

    The key is knowing what it really means for a query to “not use an index.” There are two conditions that trigger this — not using an index at all, or not using a “good” index.

      [Read more...]
    Warning: upgrade MySQL without testing at your own risk
    +2 Vote Up -1Vote Down

    Do you test your application systematically when you upgrade or reconfigure your database server? You should! Here’s a real (anonymized) story of what happens if you don’t.

    When we upgraded to 5.0.62 (from 5.0.27 and 5.0.45), our code broke for queries like this:

    SELECT SUM(amt) FROM daily_amt WHERE day = FROM_UNIXTIME(1222889772);

    The problem here was a wrong DATE/DATETIME comparison and other bug fixes in MySQL 5.0.62; it was stricter in enforcing the comparison.

    This resulted in an outage and revenue loss to the company.

    Daniel and I (mostly Daniel) continue to improve mk-upgrade to make it easy and inexpensive to find these kinds of

      [Read more...]
    Finding queries with duplicate columns
    +0 Vote Up -0Vote Down

    A while ago I wrote about a tool to help make upgrades safer. Since then, we have gotten several people to help sponsor development on this tool, and a few of our customers are using it to help find problems before they upgrade their systems.

    I can’t think of a single one of the Maatkit tools that didn’t grow out of the need for deeper insight into some part of the system. This tool is no exception. And as always, these tools are like flashlights. When you crouch down near the floor, and shine your flashlight under the refrigerator, you should expect to find a few things that make you cringe.

    The other day, one of our customers was using this tool and we started getting an error. The error was caused by the part of the tool that verifies that result sets are the same. Our

      [Read more...]
    Analyze and optimize memcached usage with Maatkit
    +5 Vote Up -0Vote Down

    Ryan posted an article on the MySQL Performance Blog about how to use mk-query-digest to analyze and understand your memcached usage with the same techniques you use for MySQL query analysis. This is an idea that came to me during the 2009 MySQL Conference, while talking to our friends from Schooner, who sell a memcached appliance.

    It suddenly struck me that the science of memcached performance is basically nonexistent, from the standpoint of developers and architects. Everyone treats it as a magical tool that just performs well and doesn’t need to be analyzed, which is demonstrably and self-evidently false. memcached itself is very fast, true, so it doesn’t usually become a performance bottleneck

      [Read more...]
    Maatkit Now Supports Memcached
    +3 Vote Up -0Vote Down

    Have you ever wondered how optimized your Memcached installation is? There is a common misconception that one doesn't have to think too deeply about Memcached performance, but that is not true. If your setup is inefficient, you could:

    • Burn Memory
    • Waste Network Round-Trips
    • Store Keys That Never Get Retrieved
    • Have a Low Cache Hit Ratio (i.e. query MySQL too much)
    • Suffer a fate too horrible to contemplate.

    Percona does a lot of consulting around Memcached, so we try to take a quantitative, scientific approach to measuring memcached performance, just like everything else we do.

    memcached is basically a key-value in-memory database, so it works well to analyze its traffic with Maatkit's

      [Read more...]
    Seeking input on a badness score for query execution
    +0 Vote Up -0Vote Down

    Suppose that you’re writing a new Maatkit tool (just a random example, really) and its job is to measure the difference in execution of queries. The simplest metric is execution time.

    Now suppose that you’re trying to figure out a metric of badness. The query executes in a second on machine 1 and 1000 seconds on machine 2.  That’s a pretty bad change.  How do you quantify this?

    Now you’ve got a query that executes in 1ms on machine 1, and 10ms on machine 2.  It’s a tenfold change.  Is it a bad change?  Maybe it’s just the difference in which files were cached in memory, or network latency because someone flooded the TCP pipe and the packets had to be backed off and retried, or something like that.  Is this significant?  How should it

      [Read more...]
    Writing a book about Maatkit
    +2 Vote Up -0Vote Down

    I’ve decided to start writing a book about Maatkit. The working title is Using Maatkit. The goal is to provide another angle onto the tools; the docs are quite good in my opinion, but they only say what the tools do. The book will tell you how to use the toolkit to accomplish tasks.

    I have no clue when it’ll be done. There is no schedule. At the current rate, it’ll take a while. I posted an outline to the Maatkit mailing list.

    I have a few goals for the project. I want it to be printable-quality material (with an index and professional copyediting, which I have already figured out will cost me a pretty penny), but I also want to be able to update it quickly. I’ve had a number of

      [Read more...]
    Seeking input for a new tool to verify MySQL upgrades
    +3 Vote Up -0Vote Down

    I’ve had several customers in the last week or so who need a way to verify that their application will work well after an upgrade. I’m seeking input on a new tool to help with MySQL upgrades. Please add comments, either here or on the bug report, or on the mailing list topic.

    If someone wants to sponsor this work, that would also be welcomed.

    Maatkit version 3519 released
    +2 Vote Up -0Vote Down

    Maatkit version 3519 is ready for download. There are a lot of changes in this release, many of which are incompatible with previous releases. There are also a lot of important new features. Read on for the details.

    First, thanks to everyone who contributed to this month’s release. A lot of people have jumped into Maatkit and started committing code. I attribute this to deliberately forcing a more open policy with decisions being made on the mailing list, rather than the former policy of “Percona pays for development, so they have more say than you do” — a snobby and ill-advised way to treat an open-source project. If you are interested in contributing to Maatkit, please ask. Subversion commit rights are being handed out

      [Read more...]
    Presentation uploaded for Maatkit talk at MySQL Conference
    +1 Vote Up -0Vote Down

    The slides for my session on Maatkit at the MySQL Conference are uploaded. They should appear on the speaker presentation files page, but I have heard that it can take a few days. Please let me know if they’re not there by May 1, 2009.

    I did not have time to plow through everything in the slides. (I deliberately prepared more material than I could cover, and just stopped when I ran out of time.) Alas, I did not remember to take my voice recorder that day, so I have no audio to share with you!

    Learn about Maatkit at the MySQL Conference
    +1 Vote Up -0Vote Down

    I’m presenting about Maatkit, the toolkit I created to make life better with MySQL, at the MySQL conference next week.

    I’m going to give you a whirlwind tour throught some of Maatkit’s features and functionality. The toolkit is much too large and complex to cover more than a small part of it in depth. So here is your advance warning: I’m going to go through a lot of material, and I won’t be stopping for lengthy discussions :-) The Maatkit documentation is very thorough, and I hope to introduce you to things that could be of use to you, so you can go learn about those topics from the documentation.

    Let me give you an idea: when I’m optimizing queries, I open up the output of

      [Read more...]
    Sessions of interest at MySQL Conference and Expo 2009
    +0 Vote Up -0Vote Down

    I haven’t really decided my schedule yet during the conference, but I thought I’d mention these sessions that look interesting to me.

    I’m presenting a session on how to use Maatkit, which I think attendees will get a lot of benefit from.

    • Tuesday
      • This is Not a Web App: The Evolution of a MySQL Deployment at Google (keynote). Mark Callaghan, need I say more?
      • MySQL and Search at Craigslist. Jeremy has gotten back into technical work after a career at Yahoo! that seemed to lead him away from his heart’s desire. I’d like to hear about the things he’s done, especially since I understand it involves replacing a lot of overloaded MySQL machines with a few
      [Read more...]
    Previous 30 Newer Entries Showing entries 31 to 60 of 110 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.