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

Displaying posts with tag: Maatkit (reset)

A clarification on mk-slave-prefetch
+1 Vote Up -0Vote Down

It seems to be a popular misconception that mk-slave-prefetch is designed to keep a MySQL replica server “warm” and ready to serve production traffic in case the master is demoted or fails. This is not what mk-slave-prefetch does. It’s related, and easy to confuse, but its purpose is different.

The mk-slave-prefetch tool is designed to try to execute a read-only approximation of the write workload that the replica is about to have to perform. It is meant to do this just a little bit before the replication thread (which can only be true if replication is lagging), so that when the replica replays writes to execute replication, it doesn’t have to wait for disk I/O.

Keeping caches warmed up for production traffic requires that the read workload, which does not flow through relay logs, is executed on the

  [Read more...]
Making mk-table-checksum less intrusive
+4 Vote Up -1Vote Down

About a month ago I needed to compare tens of thousands of tables in hundreds of databases between a few different servers. The obvious choice was, mk-table-checksum! The only problem was, that the tool needs to know the minimum and maximum value of the column by which each table is to be subdivided into chunks and checksummed. This select min(col), max(col) from table locks all write operations on the table and on a big table it meant downtime.

Looking at the source it was clear we could make mk-table-checksum run the select min(col), max(col) from table on the read-only slave and use the values to checksum the master.

It was subtle code changes in function:
get_range_statistics adding

my $cxn_string_dc = “DBI:mysql:;host=slavehost;port=3306;mysql_read_default_group=client”;
my $user = ‘user’;



  [Read more...]
7 Ways to Troubleshoot MySQL
+0 Vote Up -0Vote Down

MySQL databases are great work horses of the internet.  They back tons of modern websites, from blogs and checkout carts, to huge sites like Facebook.  But these technologies don't run themselves.  When you're faced with a system that is slowing down, you'll need the right tools to diagnose and troubleshoot the problem.  MySQL has a huge community following and that means scores of great tools for your toolbox. Here are 7 ways to troubleshoot MySQL.

1. Use innotop

Innotop is a great tool for MySQL which despite the name monitors MySQL generally as well as InnoDB usage.  It's fairly easy to install, just download the perl script. Be sure to include a [client] section to your local users .my.cnf file (you have one don't you?).  Inside that section, place one line with "user=xyz" and one line with "password=abc".

If you're concerned that

  [Read more...]
Planned change in Maatkit & Aspersa development
+1 Vote Up -0Vote Down

I’ve just sent an email to the Maatkit discussion list to announce a planned change to how Maatkit (and Aspersa) are developed. In short, Percona plans to create a Percona Toolkit of MySQL-related utilities, as a fork of Maatkit and Aspersa. I’m very happy about this change, and I welcome your responses to that thread on the discussion list.

Related posts:

  • Aspersa, a new opensource toolkit
  • Four companies to sponsor Maatkit development
  • How Maatkit benefits from
  •   [Read more...]
    New Maatkit tool: mk-table-usage
    +1 Vote Up -0Vote Down

    This month’s Maatkit release includes a new tool that’s kind of an old tool at the same time. We wrote it a couple years ago for a client who has a very large set of tables and many queries and developers, and wants the database’s schema and queries to self-document for data-flow analysis purposes. At the time, it was called mk-table-access and was rather limited — just a few lines of code wrapped around some existing modules, with an output format that wasn’t generic enough to be broadly useful. Thus we didn’t release it with Maatkit. We recently changed the name to mk-table-usage (to match mk-index-usage), included it in the Maatkit suite of tools, and enhanced the functionality a lot.

    What’s this tool good for? Well, imagine that you’re a big MySQL user and you hire a new

      [Read more...]
    New in mk-query-digest: variance-to-mean ratio
    +0 Vote Up -0Vote Down

    This isn’t actually new — it has been out for a few releases. The mk-query-digest tool from Maatkit now outputs information about each class of queries’ variance-to-mean ratio. The new output goes in a couple of places, including perhaps most usefully the “profile” report. Here’s an example from a real MySQL system:

    # Profile
    # Rank Query ID           Response time    Calls R/Call Apdx V/M   Item
    # ==== ================== ================ ===== ====== ==== ===== =======
    #    1 0xBFCF8E3F293F6466 11256.3618 68.1% 78069 0.1442 1.00  0.21 SELECT [redacted]
    #    2 0x620B8CAB2B1C76EC  2029.4730 12.3% 14415 0.1408 1.00  0.21 SELECT [redacted]
    #    3 0xB90978440CC11CC7  1345.3445  8.1%  3520 0.3822 1.00  0.00 SHOW STATUS
    #    4 0xCB73D6B5B031B4CF
      [Read more...]
    Maatkit’s mk-query-digest learns about Apdex
    +0 Vote Up -0Vote Down

    This month’s Maatkit release, which just hit the download servers, contains a new feature for mk-query-digest: Apdex scores. The Apdex performance index is a sensible metric of how satisfactory the response time to a request is. Of all the scientific metrics I’ve seen, it’s the most practical; and of all the practical ones I’ve seen, it’s the most scientific.

    To use this feature, you need to know what the Apdex threshold means, and you need to know how to configure this in mk-query-digest.

    And then you can ask and answer practical questions such as “which classes of queries against this MySQL server are not satisfying users?”

      [Read more...]
    Aspersa gets a user manual
    +3 Vote Up -0Vote Down

    It doesn’t exist until it is nicely documented. Now Aspersa has documentation. Writing these tools has taught me how powerful and flexible Bash can be. Solving MySQL problems is a lot easier with good tools!

    Related posts:

  • Aspersa, a new opensource toolkit
  • Using Aspersa to capture diagnostic data
  • MySQL manual gets improved searching
  • I’m a Postgres user, as it turns
  •   [Read more...]
    Exploring mk-table-checksum
    +3 Vote Up -0Vote Down

    I recently started exploring the wonders of mk-table-checksum after spending too long dismissing the magic-like maatkit toolkit. What follows is not an exhaustive treatment of mk-table-checksum, but just an overview and some things I had to learn by trying the tool out.

     

    The basic principle is based on the CHECKSUM TABLE table maintenance statement supported in the MySQL Server. The mk-table-checksum runs a CHECKSUM TABLE command on all (or some) tables. The results of that are stored in another table on the server.

     

    -->

    read more

    Two subtle bugs in OUTER JOIN queries
    +7 Vote Up -0Vote Down

    OUTER JOIN queries in SQL are susceptible to two very subtle bugs that I’ve observed a number of times in the real world. Daniel and I have been hammering out ways to automatically detect queries that suffer from these bugs, in a relatively new Maatkit tool called mk-query-advisor. It’s part of our series of advisor tools for MySQL. I wrote a blog post about it a while ago. Automated analysis of bad query patterns is a good thing to write tools to do, because catching buggy queries is hard work if you do it manually.

    Let’s dive right in and analyze these subtle bugs. Warning: if you don’t understand how SQL handles NULL, you’re not going to understand the following. Many

      [Read more...]
    Online Verification That Master and Slaves are in Sync
    +5 Vote Up -1Vote Down

    In October 2008, Baron posted How to Check MySQL Replication Integrity Continually. Here at Pythian we have developed a method based on that post, and added “verifying that masters and slaves are in sync” to our standard battery of tests.

    We call it “Continual replication sync checking”. This article will explain how it works, how to test and make the procedure non-blocking, benchmarks from the real world, issues we encountered along the way, and finally

      [Read more...]
    Aspersa’s mysql-summary tool
    +5 Vote Up -0Vote Down

    For those of you who miss what Maatkit’s mk-audit tool (now retired) gave you, there’s a pair of tools in Aspersa that more than replaces it. I wrote previously about the summary tool. I don’t think I have mentioned the mysql-summary tool. It has been under development for a while, and at this point it has quite a lot of functionality. You can see a sample of the output on its wiki page.

    Related posts:

  • Apsersa’s summary tool supports Adaptec and MegaRAID
  •   [Read more...]
    Is Maatkit notable enough for Wikipedia now?
    +1 Vote Up -2Vote Down

    The Maatkit article on Wikipedia was removed some time ago, after being deemed not notable. I believe this is no longer the case. It’s hard to find a credible book published on MySQL in the last few years that doesn’t mention Maatkit, there’s quite a bit of blogging about it from MySQL experts and prominent community members, and the toolkit is certainly in wide use — it’s important enough that notable companies are supporting its development. It’s available through every major Unix-like operating system’s package repository. On Debian, it’s actually part of the mysql-client package, so if you install MySQL, you automatically get Maatkit too. I believe

      [Read more...]
    The little-known Maatkit man page
    +0 Vote Up -1Vote Down

    The Maatkit toolkit for MySQL has a lot of functionality that’s common across the tools. It’s not a good idea to document this in each tool’s man page, of course. So there is an overall maatkit man page. It explains concepts such as configuration file syntax. This and all the other Maatkit man pages are online.

    Related posts:

  • How PostgreSQL protects against partial page writes and data corruption
  • Writing a book about Maatkit
  • Learn about Maatkit
  •   [Read more...]
    mk-query-digest Tips – Showing all hosts & users
    +1 Vote Up -1Vote Down

    The Maatkit tools provide a suite of additional MySQL commands. There is one command I use constantly and that is mk-query-digest.

    Unfortunately the documentation does leave a lot to be desired for usability. While throughout, it is a man page and not a user guide. Several of us have discussed writing better documentation however it’s always a matter of time. I have however learned a number of tips and I’d like to share them in smaller digests.

    The first is showing additional display. Maatkit works on truncating per line output to a reasonable length of 73 characters?

    One of those lines is the list of hosts that connected to MySQL for a query, for example.

    # Hosts                  4 192.168.40... (2), 192.168.40... (2)... 2
      [Read more...]
    tcpdump errors on FreeBSD for mk-query-digest
    +0 Vote Up -0Vote Down

    While I use this tcpdump command for MySQL query analysis with mk-query-digest, I found recently that it didn’t work on FreeBSD

    $ tcpdump -i bge0 port 3306 -s 65535 -x -n -q -tttt -c 5
    tcpdump: syntax error
    

    It left me perplexed and reading the man page seemed to indicate my options were valid. I tried a few variances just to be sure without success.

    $ tcpdump -i bge0 -c 5 port 3306 -x
    tcpdump: syntax error
    $ tcpdump -i bge0 -c 5 port 3306 -q
    tcpdump: syntax error
    $ tcpdump -i bge0 -c 5 port 3306 -tttt
    tcpdump: syntax error
    

    The solution was actually quite simple in the end, it had nothing to do with the commands, it had everything to do with the order of them. Placing port as the last option solved the problem.

    $
      [Read more...]
    Hack MySQL tools retired, succeeded
    +4 Vote Up -0Vote Down

    I’m surprised, and flattered, to see that people still use, write and recommend mysqlsla, mysqlreport and–most surprisingly–mysqlsniffer. In truth, however, I consider all the original Hack MySQL tools as retired. Maatkit consumes the majority of my development time and provides better replacements for all the Hack MySQL tools. The mk tools are better because–most importantly–they’re tested, their code is more robust, and they benefit from the collected knowledge and experience of the community’s top minds (whereas the Hack MySQL tools are brain-children of only my knowledge and experience circa several years ago).

    Thus I created a new tools page where I list and briefly profile free, open-source MySQL tools. As the intro paragraph states, MySQL Forge does this, too, but imho the

      [Read more...]
    mk-query-digest uses less memory
    +2 Vote Up -0Vote Down

    Daniel changed mk-query-digest to use much less memory. It parsed and aggregated a 2GB MySQL slow query log file in a few dozen megabytes of memory for me yesterday. Thanks to Facebook for sponsoring this work.

    Related posts:

  • mk-query-digest now supports Postgres logs
  • Learn about mk-query-digest at PgEast 2010
  • Slides from my session on mk-query-digest at PgEast 2010
  • mk-query-digest now understands HTTP
  •   [Read more...]
    DBJ – Wonders of Maatkit for MySQL
    +2 Vote Up -3Vote Down

    If you’re new to the MySQL DBA role, you’ll be excited to learn about the Maatkit toolset.  It provides a whole host of valuable functionality and fills many of the DBAs day-to-day needs.

    DatabaseJournal – Wonders of Maatkit

    Apsersa’s summary tool supports Adaptec and MegaRAID controllers
    +0 Vote Up -1Vote Down

    I spent a little time yesterday doing some things with the “summary” tool from Aspersa. I added support for summarizing status and configuration of Adaptec and LSI MegaRAID controllers. I also figured out how to write a test suite for Bash scripts, so most major parts of the tool are fully tested now. I learned a lot more sed and awk this weekend.

    There is really only one way to get status of Adaptec controllers (/usr/StorMan/arcconf), but the LSI controllers can be queried through multiple tools. I added support for MegaCli64, as long as it’s located in the usual place at /opt/MegaRAID/MegaCli/MegaCli64. I am looking for feedback and/or help on supporting other methods of getting status from the LSI controllers, such as megarc and omreport. If you can contribute

      [Read more...]
    Tis a gift to be simple
    +0 Vote Up -1Vote Down

    I was just reading up on the syntax for index hints in MySQL, and noticed this:

    An index_name value need not be a full index name. It can be an unambiguous prefix of an index name. If a prefix is ambiguous, an error occurs.

    I actually prefer not to have extra “syntactic sugar” features such as this. It helps avoid bugs and unexpected behavior. Even if I don’t use it intentionally, I can get bitten by it, if someone adds another index whose name has the same prefix as one that I already use:

    mysql> create table t(a int primary key);
    mysql> select * from t force index(PRIMAR);
    Empty set (0.00 sec)
    
    mysql> alter table t add key PRIMARY_2(a);
    
    mysql> select * from t force index(PRIMAR);
    ERROR 1176 (HY000): Key 'PRIMAR' doesn't
      [Read more...]
    New Maatkit tool to compute index usage
    +1 Vote Up -0Vote Down

    In a couple of recent consulting cases, I needed a tool to analyze how a log of queries accesses indexes and tables in the database, specifically, to find out which indexes are not used. I initially hacked together something similar to Daniel Nichter’s mysqlidxchk, but using the framework provided by Maatkit, which gave me a pretty good start right out of the box. This was useful in the very tight time constraints I was under, but was not a complete solution. Alas, I could not use anything like Percona’s enhancements for finding unused indexes.

    So, in response to another consultant’s customer request (and sponsorship — thank you!) I spent more time actually writing a real tool in the Maatkit style, with

      [Read more...]
    Maatkit learns how to map-reduce
    +2 Vote Up -3Vote Down

    The May release of Maatkit included a new feature in mk-query-digest. This allows you to process queries in many pieces, write out intermediate results, and then combine the pieces in a separate step. Maybe it’s not exactly map-reduce, but it makes a good headline.

    The purpose is to enable query analysis across an arbitrarily large group of servers. Process queries on all of them, ship the results to a central place, and then combine them together. Pre-processing the results has some nice benefits, such as reduced bandwidth requirements, speeding up processing by doing it in parallel, and reducing the workload on the central aggregator. One Percona customer with many MySQL instances is trying this out.

    The --save-results option on mk-query-digest saves the digested results to a file, stopping just before the final stages of the query

      [Read more...]
    Videos of Pythian Sessions from the 2010 O’Reilly MySQL Conference and Expo
    +2 Vote Up -0Vote Down

    Here’s a sneak peek at a video matrix — this is all the videos that include Pythian Group employees at the MySQL conference. I hope to have all the rest of the videos processed and uploaded within 24 hours, with a matrix similar to the one below (but of course with many more sessions).

    TitlePresenterSlidesVideo link
    (hr:min:sec)Details (Conf. site link)

    Main Stage
    Keynote: Under New Management: Next Steps for the CommunitySheeri K. Cabral (Pythian)N/A18:16
    session 14808Ignite talk: MySQLtuner 2.0Sheeri K. Cabral (Pythian)PDF5:31N/A
    Interview
    Thoughts on Drizzle and

      [Read more...]
    Try mk-query-advisor, a new Maatkit tool
    +1 Vote Up -0Vote Down

    We have an early draft of a new tool available for you to use and test. It uses heuristics to find problems in SQL. Please use it and give feedback! Here’s how:

    $ wget http://www.maatkit.org/trunk/mk-query-advisor
    $ perl mk-query-advisor /var/log/mysql/slow.log
    

    By default it consumes a log file in MySQL’s slow query log format, but it can also parse the general-log format, and if you have any other kind of log you can feed it through mk-query-digest to transform the log into something it can recognize. Tell me if you’d be able to spot the mixture of join styles and use of leading % wildcards in the following query without help:

    # Query ID 0x643E813A9ABDA151 at byte 2001701
    # CRIT JOI.001 Mixing comma and ANSI joins.
    # WARN ARG.001 Argument with leading
      [Read more...]
    Maatkit BoF session at the MySQL conference
    +2 Vote Up -0Vote Down

    I’ve submitted a Birds of a Feather session for Maatkit at the upcoming MySQL conference. It’s not on the public schedule yet, but it has been accepted and scheduled for 19:00 on 13 Apr 2010. See you there!

    Related posts:

  • Presentation uploaded for Maatkit talk at MySQL Conference The slides
  • I’ll be speaking at the O’Reilly MySQL Conference 2010 I’m
  • Learn about Maatkit at the MySQL Conference I’m
  • Related posts brought to you by Yet Another Related Posts Plugin.

    Cary Millsap: Thinking Clearly about Performance
    +0 Vote Up -0Vote Down

    Cary Millsap has a concise, readable paper on performance. Anyone involved in database performance optimization should read it. Cary’s writing has heavily influenced the mk-query-digest tool for analyzing MySQL/PostgreSQL/Memcached/HTTP query performance, and I think you’ll get a lot more from mk-query-digest if you read this paper — and you should also read his book, reviewed here. It’s one of the top books on my Essential Books List.

    Related posts:

  •   [Read more...]
    Learn about mk-query-digest at PgEast 2010
    +1 Vote Up -0Vote Down

    I’ll be attending PgEast this year, as I’ve done for the last couple of years, and this year I’ll also be speaking. The topic is query analysis with mk-query-digest. The official description of my talk is as follows:

    mk-query-digest is a powerful open-source tool for capturing, filtering, transforming, and aggregating queries, with the ability to do all sorts of other advanced tasks too. By default, it aggregates similar queries together and presents a designed-for-DBAs report with statistics about the most important queries, so you can see where to focus your optimization efforts. This talk shows you how to use mk-query-digest to analyze your Postgres server’s

      [Read more...]
    Portable Maatkit
    +0 Vote Up -0Vote Down

    What do you do when you're in a situation where you REALLY need your
    favorite Maatkit tools, but are not allowed to load software on the
    server running MySQL, AND you're not allowed to install software on the
    provided workstation, especially a workstation that's running software
    from a large corporation in Redmond? Portable Maatkit to the rescue!

    DISCLAIMER:
    I've only tested a few maatkit tools, namely mk-find and
    mk-query-digest, but not completely. Some features and some tools may
    not function, such as mk-audit. Use your best judgment. I'm not
    responsible if your workstation or server looses all your data, your
    USB key bursts into flames, or your significant other leaves you. YMMV,
    use at YOUR OWN RISK!

    Strawberry Perl
    has been out for some time now, and recently, a portable











      [Read more...]
    Portable Maatkit
    +0 Vote Up -0Vote Down

    What do you do when you're in a situation where you REALLY need your
    favorite Maatkit tools, but are not allowed to load software on the
    server running MySQL, AND you're not allowed to install software on the
    provided workstation, especially a workstation that's running software
    from a large corporation in Redmond? Portable Maatkit to the rescue!

    DISCLAIMER:
    I've only tested a few maatkit tools, namely mk-find and
    mk-query-digest, but not completely. Some features and some tools may
    not function, such as mk-audit. Use your best judgment. I'm not
    responsible if your workstation or server looses all your data, your
    USB key bursts into flames, or your significant other leaves you. YMMV,
    use at YOUR OWN RISK!

    Strawberry Perl
    has been out for some time now, and recently, a portable











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