Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 24 Next 10 Older Entries

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")
    do
        tbl_file=${frm_file//.frm/.ibd}
        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 ]
        then
  [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...]
Showing entries 1 to 10 of 24 Next 10 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.