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 24

Displaying posts with tag: Analysis (reset)

Bash script: report largest InnoDB files
+1 Vote Up -0Vote Down

The following script will report the largest InnoDB tables under the data directory: schema, table & length in bytes. The tables could be non-partitioned, in which case this is simply the size of the corresponding .ibd file, or they can be partitioned, in which case the reported size is the sum of all partition files. It is assumed tables reside in their own tablespace files, i.e. created with innodb_file_per_table=1.

    mysql_datadir=$(grep datadir /etc/my.cnf | cut -d "=" -f 2)
    cd $mysql_datadir
    for frm_file in $(find . -name "*.frm")
        table_schema=$(echo $frm_file | cut -d "/" -f 2)
        table_name=$(echo $frm_file | cut -d "/" -f 3 | cut -d "." -f 1)
        if [ -f $tbl_file ]
  [Read more...]
common_schema 1.1 released: split(), try-catch, killall(), profiling
+2 Vote Up -0Vote Down

I'm very happy to announce the release of common_schema, version 1.1 (revision 300).

This version boasts with compelling new features: innovative QueryScript syntax, libraries, views which add to your skills as a DBA, making some maintenance and management tasks a breeze.

  • QueryScript, split statement: automagically break long queries into smaller chunks, avoid long locks and reduce query/transaction overhead
  • QueryScript, try-catch statement: just try { something; } catch { act_on_error; }.
  • killall(): quickly kill connections based on grantee/user/host information.
  • profiling/profiling_last: utility views to assist in query
  [Read more...]
Statistical functions in MySQL
+3 Vote Up -0Vote Down

Even in times of a growing market of specialized NoSQL databases, the relevance of traditional RDBMS doesn't decline. Especially when it comes to the calculation of aggregates based on complex data sets that can not be processed as a batch like Map&Reduce. MySQL is already bringing in a handful of aggregate functions that can be useful for a statistical analysis. The best known of this type are certainly:

Read the rest »

common_schema, rev. 178: foreach(), repeat_exec(), Roland Bouman, query analysis
+1 Vote Up -0Vote Down

common_schema, revision 178 is now released, with major additions. This revision turns common_schema into a framework, rather than a set of views and functions.

common_schema provides with query scripting, analysis & informational views, and a function library, allowing for easier administration and diagnostics for MySQL. It introduces SQL based tools which simplify otherwise complex shell and client scripts, allowing the DBA to be independent of operating system, installed packages and dependencies.

There's no Perl nor Python, and no dependencies to install. It's just a schema.

Some highlights for the new revision:

  • foreach(), aka $(): loop through a collection,
  [Read more...]
MySQL Global status difference using single query
+1 Vote Up -0Vote Down

Have just read MySQL Global status difference using MySQL procedures / functions, by Andres Karlsson. Have commented, but realized I did not provide with a direct answer. In the comment, I suggested checking out a solution based on views, found in common_schema. But the solution in common_schema is split into two views, due to the fact views cannot handle derived tables subqueries.

Well, here's a single query to do that: it checks GLOBAL_STATUS twice, 10 seconds apart in the following sample. It uses SLEEP() to actually wait between the

  [Read more...]
Announcing common_schema: common views & routines for MySQL
+1 Vote Up -0Vote Down

Today I have released common_schema, a utility schema for MySQL which includes many views and functions, and is aimed to be installed on any MySQL server.

What does it do?

There are views answering for all sorts of useful information: stuff related to schema analysis, data dimensions, monitoring, processes & transactions, security, internals... There are basic functions answering for common needs.

Some of the views/routines simply formalize those queries we tend to write over and over again. Others take the place of external tools, answering complex questions via SQL and metadata. Still others help out with SQL generation.

Here are a few highlights:

  • Did you know you can work out
  [Read more...]
Getting the best of Clouds, Internet and BI
+1 Vote Up -0Vote Down
The internet really is full of stuff, and we are starting to fill the Clouds with all sorts of software that moves from the serverrooms and in to the clouds. One aspect here that is often missing is what a Cloud environment and the Internet in itself can add to what we already know and have. Or in other words, running in the cloud is not only about lowering costs, and if you look at it by lowering costs by running things in the cloud the same way you used to run it on your private server, then chances are you might not gain anything, running in a cloud is also about having distinct advantages not easily available somewhere else.

Christopher Ahlberg here at Recorded Future just blogged about another way of looking at it: What do we have access to on the internet that is not readily available in the Server room, and the answer is: data. A lot of data.

  [Read more...]
Checking for AUTO_INCREMENT capacity with single query
+3 Vote Up -0Vote Down

Darn! This means oak-show-limits becomes redundant. Am I not supposed to speak about it on my coming presentation? Bad timing!

You have AUTO_INCREMENT columns. How far are you pushing the limits? Are you going to run out of AUTO_INCREMENT values soon? Perhaps you wonder whether you should ALTER from INT to BIGINT?

The answer is all there in INFORMATION_SCHEMA. The TABLES table shows the current AUTO_INCREMENT value per table, and the COLUMNS table tells us all about a column’s data type.

It takes some

  [Read more...]
MySQL community blogging – PlanetMySQL
+4 Vote Up -0Vote Down

Phew, here we go, this blog post has been long time coming! A few months ago I started toying around with the idea of analyzing the PlanetMySQL public blog feed. It doesn’t take long to extract the data and prepare it for analysis but between lots of work and procrastination this blog post was left unfinished.

It was partly out of pure curiosity and partly the fact that it seemed to me there were less posts than previous years that I decided to trend out the number of posts over the past years and here we go.

The blue line shows the blog posts per month over the past six years and the black line is a polynomial

  [Read more...]
oak-hook-general-log: your poor man’s Query Analyzer
+0 Vote Up -0Vote Down

The latest release of openark kit introduces oak-hook-general-log, a handy tool which allows for some analysis of executing queries.

Initially I just intended for the tool to be able to dump the general log to standard output, from any machine capable to connect to MySQL. Quick enough, I realized the power it brings.

With this tool, one can dump to standard output all queries using temporary tables; or using a specific index; or doing a full index scan; or just follow up on connections; or… For example, the following execution will only log queries which make for filesort:

oak-hook-general-log --user=root --host=localhost --password=123456
  [Read more...]
openark-kit (rev. 170): new tools, new functionality
+3 Vote Up -0Vote Down

I’m pleased to announce a new release of the openark kit. There’s a lot of new functionality inside; following is a brief overview.

The openark kit is a set of utilities for MySQL. They solve everyday maintenance tasks, which may be complicated or time consuming to work by hand.

It’s been a while since the last announced release. Most of my attention was on mycheckpoint, building new features, writing documentation etc. However my own use of openark kit has only increased in the past few months, and there’s new useful solutions to common problems that have been developed.

I’ve used and improved many tools over this time, but doing the final cut, along with proper documentation, took

  [Read more...]
dbbenchmark.com – now supporting MySQL on OSX 10.6
+2 Vote Up -0Vote Down

Just a quick note to let everyone know that our new benchmarking script now supports OSX 10.6 on Intel hardware. That means you can run one simple command and get all of the sequential and random INSERT and SELECT performance statistics about your database performance. As usual the script is open source and released under the new BSD license. Give is a try by downloading now! See the download page for more details.

dbbenchmark.com – Benchmarking script now available
+0 Vote Up -0Vote Down

You can download the first release of the benchmarking script here: http://code.google.com/p/dbbenchmark/

Please read the README file or consult the Support page before running the benchmarks.

EXPLAIN: missing db info
+0 Vote Up -0Vote Down

I’m further developing a general log hook, which can stream queries from the general log.

A particular direction I’m taking is to filter queries by their type of actions. For example, the tool (oak-hook-general-log) can be instructed to only stream out those queries which involve creation of a temporary table; or those which cause for a filesort, or full index scan, etc.

This is done by evaluating of query execution plans on the fly. I suspect the MySQL query analyzer (http://www.mysql.com/why-mysql/white-papers/mysql_wp_queryanalyzer.php) roughly does the same (as a small part of what it does).

It’s almost nothing one cannot do with sed/awk. However, I bumped into a couple of problems:

  • The general log (and the
  •   [Read more...]
    Static charts vs. interactive charts
    +1 Vote Up -1Vote Down

    I’m having my usual fun with charts. Working on mycheckpoint, I’ve generated monitoring charts using the Google Chars API. But I’ve also had chance to experiment and deploy interactive charts, JavaScript based. In particular, I used and tweaked dygraphs.

    I’d like to note some differences in using charts of both kinds. And I think it makes a very big difference.

    Static charts

    I’ll call any image-based chart by “static chart”. It’s just a static image. Example of such charts are those generated by Google Image Charts (they now also have new, interactive charts), or RRDtool. Show below is

      [Read more...]
    Performance analysis with mycheckpoint
    +1 Vote Up -1Vote Down

    mycheckpoint (see announcement) allows for both graph presentation and quick SQL access to monitored & analyzed data. I’d like to show the power of combining them both.

    InnoDB performance

    Taking a look at one of the most important InnoDB metrics: the read hit ratio (we could get the same graph by looking at the HTML report):

    SELECT innodb_read_hit_percent FROM sv_report_chart_sample \G
    *************************** 1. row ***************************
      [Read more...]
    Announcing mycheckpoint: lightweight, SQL oriented monitoring for MySQL
    +4 Vote Up -0Vote Down

    I’m proud to announce mycheckpoint, a monitoring utility for MySQL, with strong emphasis on user accessibility to monitored data.

    mycheckpoint is a different kind of monitoring tool. It leaves the power in the user’s hand. It’s power is not with script-based calculations of recorded data. It’s with the creation of a view hierarchy, which allows the user to access computed metrics directly.

    mycheckpoint is needed first, to deploy a monitoring schema. It may be needed next, so as to INSERT recorded data (GLOBAL STATUS, GLOBAL VARIABLES, MASTER STATUS, SLAVE STATUS) — but this is just a simple INSERT; anyone can do that, even another monitoring tool.

    It is then that you do not need it anymore: everything is laid at

      [Read more...]
    How NOT to test that mysqld is alive
    +5 Vote Up -0Vote Down

    I had a call from a new customer last week. They had issues with their MySQL server. Apparently, it was repeatingly crashing, every few hours. To have their production system kept alive, they used a script to periodically see if MySQL was still alive, and if not – start it.

    I was first thinking in directions of old installations; wrong memory allocations (too little memory for large content; to large memory allocation for weak machine). When I reached the customer’s premises, I quickly reviewed general settings, while explaining some basic configuration guidelines. There were peculiarities (e.g. query_cache_limit being larger than query_cache_size), but nothing to obviously suggest a reason for crash.

    I then observed the error log. Took some time to find it, since the log_error parameter appeared twice

      [Read more...]
    Tool of the day: inotify
    +2 Vote Up -0Vote Down

    I was actually exploring inotify-tools for something else, but they can also be handy for seeing what goes on below a mysqld process. inotify hooks into the filesystem handlers, and sees which files are accessed. You can then set triggers, or just display a tally over a certain period.

    It has been a standard Linux kernel module since 2.6.13 (2005, wow that’s a long time ago already) and can be used through calls or the inotify-tools (commandline). So with the instrumentation already in the kernel, apt-get install inotify-tools is all you need to get started.

     # inotifywatch -v -t 20 -r /var/lib/mysql/* /var/lib/mysql/zabbix/*
    Establishing watches...
    Setting up watch(es) on /var/lib/mysql/mysql/user.frm
    OK, /var/lib/mysql/mysql/user.frm is now being watched.
    Total of 212 watches.
      [Read more...]
    Network Management Data Reduction and Smoothing -- A MySQL Webinar
    Employee +1 Vote Up -0Vote Down

    ScienceLogic embeds MySQL (http://www.mysql.com/" target="_blank) in its EM7 network management appliances. An installation of EM7 can perform over half a billion database queries daily, storing massive amounts of data for both real-time and trended performance reporting.

    Michael McFadden, senior software architect with ScienceLogic, will discuss all this in an upcoming MySQL webinar (http://mysql.com/news-and-events/web-seminars/display-361.html).

    Network Management Data Reduction and Smoothing -- A MySQL Webinar
    Employee +0 Vote Up -0Vote Down

    ScienceLogic embeds MySQL (http://www.mysql.com/" target="_blank) in its EM7 network management appliances. An installation of EM7 can perform over half a billion database queries daily, storing massive amounts of data for both real-time and trended performance reporting.

    Michael McFadden, senior software architect with ScienceLogic, will discuss all this in an upcoming MySQL webinar (http://mysql.com/news-and-events/web-seminars/display-361.html).

    Network Management Data Reduction and Smoothing -- A MySQL Webinar
    Employee +0 Vote Up -0Vote Down

    ScienceLogic embeds MySQL (http://www.mysql.com/" target="_blank) in its EM7 network management appliances. An installation of EM7 can perform over half a billion database queries daily, storing massive amounts of data for both real-time and trended performance reporting.

    Michael McFadden, senior software architect with ScienceLogic, will discuss all this in an upcoming MySQL webinar (http://mysql.com/news-and-events/web-seminars/display-361.html).

    Useful database analysis queries with INFORMATION_SCHEMA
    +0 Vote Up -0Vote Down

    A set of useful queries on INFORMATION_SCHEMA follows. These queries can be used when approaching a new database, to learn about some of its properties, or they can be regularly used on an existing schema, so as to verify its integrity.

    I will present queries for:

    • Checking on database engines and size
    • Locating duplicate and redundant indexes
    • Checking on character sets for columns and tables, looking for variances
    • Checking on processes and long queries (only with MySQL 5.1)


    The following query returns the total size per engine per database. For example, it is common that in a given database, all tables are InnoDB. But once in a while, and even though default-engine is set to InnoDB, someone creates a MyISAM table. This may break transactional behavior, or may cause a mysqldump --single-transaction to be ineffective.

      [Read more...]
    SocGen, Facebook, and historical data analysis
    +0 Vote Up -0Vote Down

    Back in my formative years, during the tail end of the S&L crisis, I spent quite a bit of time working on financal software, including asset-liability management, risk management, and pool selection for the securitization of assets.

    Although I’m a ‘data’ guy, for a layman I have a fairly good understanding of risk management in the financial industry. A side effect of that understanding is that I’m been following the ongoing risk management fiasco with more than a passing interest. (The media might call it a ‘credit crunch’ or a ’subprime contagion’, but as far as I can tell, it all boils down to a lack of attention to risk.) It’s also turning out to be a good

      [Read more...]
    Showing entries 1 to 24

    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.