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 67 Next 7 Older Entries

Displaying posts with tag: information_schema (reset)

Evolution of MySQL metadata
+5 Vote Up -0Vote Down
I was looking at the latest MySQL versions, and I happened to notice that there has been a great increment in the number of metadata tables, both in the information_schema and performance_schema databases. So I made a simple count of both schemas in the various versions, and draw a graph. The advance looks straightforward.

versionInformation_schemaperformance_schema5.0.921705.1.542805.1.54 with innodb plugin3505.5.837175.6.24823

The difference between 5.0 and 5.6 is staggering. We came from 17 to 71 metadata total tables. A stacked bar chart helps visualize the changes.

I noticed, BTW, that

  [Read more...]
Some hidden goods in MySQL 5.5
+11 Vote Up -0Vote Down
The announcement of MySQL 5.5 released as GA has outlined the improvements in this version, which indeed has enough good new features to excite most any user.
There are two additions, though, that were lost in the noise of the bigger features, and I would like to spend a few words for each of them.The first addition is something that users of stored routines have been waiting for since MySQL 5.0. No, it is not SIGNAL and its close associate RESIGNAL, which have been publicized enough. I am talking about the stored routine parameters, for which now there is a dedicated table in the information_schema.
Let's see

  [Read more...]
Monitoring MySQL SQL statements the way it SHOULD be done!
+8 Vote Up -2Vote Down
You may have read a previous post of mine, back in April this year, where I wrote about using the MySQL 5.5 Audit interface to SQL Statement monitoring. There was a bunch of comments and some ideas, but not much happened. Until now that is.

Hereby I release the first version of SQLStats, which is a much enhanced version of what I described in the previous post. This is a MySQL Plugin for MySQL 5.5 that allows you to monitor the most recently executed, and the most frequently executed statements using 2 INFORMATION_SCHEMA tables. The thing is not very complex, to be honest, but it does do the job. So what was the job then? Well, looking at what this plugin does, it goes something like this:
  • Allows you to monitor ALL SQL statements executed by the

  [Read more...]
Thoughts and ideas for Online Schema Change
+6 Vote Up -0Vote Down

Here’s a few thoughts on current status and further possibilities for Facebook’s Online Schema Change (OSC) tool. I’ve had these thoughts for months now, pondering over improving oak-online-alter-table but haven’t got around to implement them nor even write them down. Better late than never.

The tool has some limitations. Some cannot be lifted, some could. Quoting from the announcement and looking at the code, I add a few comments. I conclude with a general opinion on the tool’s abilities.

“The original table must have PK. Otherwise an error is returned.”

  [Read more...]
MySQL analytics: information_schema polling for table engine percentages
+1 Vote Up -0Vote Down

If you’ve ever needed to know how the data and index percentages per table engine were laid out on your MySQL server, but didn’t have the time to write out a query… here it is!

(select (sum(DATA_LENGTH)+sum(INDEX_LENGTH))/(POW(1024,3)) as total_size from tables) as total_size_gb,
(select sum(INDEX_LENGTH)/(POW(1024,3)) as index_size from tables) as total_index_gb,
(select sum(DATA_LENGTH)/(POW(1024,3)) as data_size from tables) as total_data_gb, 

(select ((sum(INDEX_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_index from tables) as perc_index,
(select ((sum(DATA_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_data from tables) as perc_data,

(select ((sum(INDEX_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_index from tables where ENGINE='innodb') as innodb_perc_index,
(select ((sum(DATA_LENGTH) / (
  [Read more...]
Improving InnoDB Transaction Reporting
Employee +2 Vote Up -0Vote Down

Everybody knows that parsing the output of SHOW ENGINE INNODB STATUS is hard, especially when you want to track the information historically, or want to aggregate any of the more dynamic sections such as the TRANSACTIONS one.

Within the InnoDB plugin the INFORMATION_SCHEMA.INNODB_TRX table was added, which allowed you to at least get some of the information on each transaction, but not the full breadth of information that SHOW ENGINE INNODB STATUS provided.

“This is nice..” I thought “..but why not go the whole hog..?”.. And so I set about doing that, and opened up Bug#53336. In a very short time, I was in a review process with the

  [Read more...]
INFORMATION_SCHEMA tables are case sensitive
+2 Vote Up -1Vote Down

I wanted to get examples of some of the extra information that the Percona server has in its INFORMATION_SCHEMA metadata, and in doing so, I stumbled across an interesting MySQL bug/feature/point — INFORMATION_SCHEMA tables (which are actually system views) are case sensitive when used in comparisons:

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>  select @@version;
| @@version          |
| 5.1.36-xtradb6-log |
1 row in set (0.00 sec)

mysql> use information_schema;
Database changed
mysql> show tables like 'innodb%';
Empty set (0.00 sec)

mysql>  show tables like 'INNODB%';

  [Read more...]
Verifying GROUP_CONCAT limit without using variables
+4 Vote Up -0Vote Down

I have a case where I must know if group_concat_max_len is at its default value (1024), which means there are some operation I cannot work out. I’ve ranted on this here.

Normally, I would simply:

SELECT @@group_concat_max_len

However, I am using views, where session variables are not allowed. Using a stored function can do the trick, but I wanted to avoid stored routines. So here’s a very simple test case: is the current group_concat_max_len long enough or not? I’ll present the long version and the short version.

The long version

  [Read more...]
MySQL: Partition-wise backups with mysqldump
+4 Vote Up -0Vote Down
To whom it may concern,

in response to a query from André Simões (also known as ITXpander), I slapped together a MySQL script that outputs mysqldump commands for backing up individual partitions of the tables in the current schema. The script is maintained as a snippet at MySQL Forge.

How it works

The script works by querying the

  [Read more...]
MySQL status variables
+1 Vote Up -0Vote Down
In MySQL5 MySQL implemented changes so you can view global status (counters) and session counters. In 5.1 MySQL extended this and implemented this information in the INFORMATION_SCHEMA.GLOBAL_STATUS and INFORMATION_STATUS.SESSION_STATUS tables.

Global status

mysql> SELECT * FROM GLOBAL_STATUS WHERE VARIABLE_NAME = 'COM_SELECT'\G*************************** 1. row *************************** VARIABLE_NAME: COM_SELECTVARIABLE_VALUE: 2076641 row in set (0.02 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Com_select';+---------------+--------+| Variable_name | Value |+---------------+--------+| Com_select | 207664 | +---------------+--------+1 row in set (0.03 sec)
Session status
mysql> SHOW SESSION STATUS LIKE 'Com_select';+---------------+-------+| Variable_name | Value |+---------------+-------+| Com_select | 13 |

  [Read more...]
MySQL - the best stored routine is the one you don't write
+5 Vote Up -0Vote Down
At Fosdem 2010, already two weeks ago, I had the pleasure of hearing Geert van der Kelen explain the work he has been doing on connecting MySQL (http://www.mysql.com/" target="mysql) and Python. I don't know anything about Python, but anybody that has the courage, perseverance and coding skills to create an implementation of the the MySQL wire protocol from scratch is a class-A programmer in my book. So, I encourage everyone that needs MySQL connectivity for Python programs to check out Geert's brainchild, MySQL Connector/Python.

In relation to MySQL Connector/Python, I just read

  [Read more...]
How to tell when using INFORMATION_SCHEMA might crash your database
+8 Vote Up -2Vote Down

There are those that are very adamant about letting people know that using INFORMATION_SCHEMA can crash your database. For example, in making changes to many tables at once Baron writes:

“querying the INFORMATION_SCHEMA database on MySQL can completely lock a busy server for a long time. It can even crash it. It is very dangerous.”

Though Baron is telling the truth here, he left out one extremely important piece of information: you can actually figure out how dangerous your INFORMATION_SCHEMA query will be, ahead of time, using EXPLAIN.

In MySQL 5.1.21 and higher, not only were optimizations made to the INFORMATION_SCHEMA, but new values were added so that EXPLAIN had better visibility into what MySQL is actually

  [Read more...]
Drizzle FRM replacement: the table proto
+0 Vote Up -0Vote Down

Drizzle originally inherited the FRM file from MySQL (which inherited it from UNIREG). The FRM file stores metadata about a table; what columns it has, what type those columns are, what indexes, any default values, comments etc are all stored in the FRM. In the days of MyISAM, this worked relatively well. The row data was stored in table.MYD, indexes on top of it in table.MYI and information about the format of the row was
in table.FRM. Since MyISAM itself wasn’t crash safe, it didn’t really matter if creating/deleting the FRM file along with the table was either.

As more sophisticated engines were introduced (e.g. InnoDB) that had their own data dictionary, there started to be more of a problem. There were now two places storing information about a table: the FRM file and the data dictionary specific to the engine. Even if the data

  [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...]
How to calculate a good InnoDB log file size – recap
+4 Vote Up -0Vote Down

Following Baron Schwartz’ post: How to calculate a good InnoDB log file size, which shows how to make an estimate for the InnoDB log file size, and based on SQL: querying for status difference over time, I’ve written a query to run on MySQL 5.1, which, upon sampling 60 seconds of status, estimates the InnoDB transaction log bytes that are expected to be written in the period of 1 hour.

Recap: this information can be useful if you’re looking for a good innodb_log_file_size value, such that will not pose too much I/O (smaller values will make for more frequent flushes), not will make for a too long recovery time

  [Read more...]
SQL: querying for status difference over time
+2 Vote Up -0Vote Down

The InnoDB plugin has a nice INFORMATION_SCHEMA concept: resetting tables. For example, the INNODB_CMP table lists information about compression operation. A similar table, INNODB_CMP_RESET, provides the same information, but resets the values. The latter can be used to measure, for example, number of compression operations over time.

I wish to present a SQL trick which does the same, without need for resetting tables. Suppose you have some status table, and you wish to measure the change in status per second, per minute etc. The trick is to query for the value twice in the same query, with some pause in between, and make the difference calculation.

For sake of simplicity, I’ll demonstrate using 5.1’s INFORMATION_SCHEMA.GLOBAL_STATUS. Please refer to

  [Read more...]
+3 Vote Up -0Vote Down

MySQL 5.1 boasts some new and useful INFORMATION_SCHEMA tables. Among them is the GLOBAL_STATUS table.

At last, it is possible to ask questions like:

| INNODB_OS_LOG_WRITTEN | 512            |
1 row in set (0.00 sec)

| QUESTIONS     | 28             |
1 row in set (0.00
  [Read more...]
Calculating your database size
+2 Vote Up -0Vote Down

I generally use the following MySQL INFORMATION_SCHEMA (I_S) query to Calculate Your MySQL Database Size. This query and most others that access the MySQL INFORMATION_SCHEMA can be very slow to execute because they are not real tables and are not governed by physical data, memory buffers and indexes for example but rather internal MySQL data structures.

Mark Leith indicates in his post on innodb_stats_on_metadata that Innodb performs 8 random(ish) dives in to the index, when anybody accesses any of SHOW TABLE STATUS, SHOW INDEX, INFORMATION_SCHEMA.TABLES,INFORMATION_SCHEMA.STATISTICS for InnoDB tables. This can have an effect on performance, especially with a large number of Innodb tables, and a poor ratio of innodb_buffer_pool_size to disk data+index

  [Read more...]
How do I find the storage engine of a MySQL table
+2 Vote Up -0Vote Down

This seems quite a trivial question, but developers don’t often know what a MySQL storage engine is and how to determine what storage engine is used for a table.

The first choice is to describe the table with the DESC[RIBE] command. Side Note: people often don’t realize that DESC is a short acceptable version here.

mysql> desc stats;
| Field   | Type                | Null | Key | Default           | Extra          |
| stat_id | int(10) unsigned    | NO   | PRI | NULL              | auto_increment |
| created | timestamp           | NO   |     | CURRENT_TIMESTAMP |                |
| version | tinyint(3)
  [Read more...]
InnoDB I_S.tables.table_rows out by a factor of 100x
+1 Vote Up -0Vote Down

I’ve always believed that the MySQL Information_schema.tables.table_rows figure for Innodb tables to be while approximate, approximately accurate.

Today I found that the figures varied on one table from 10x to 100x wrong.

Before performing an ALTER I always verify sizes for reference.

| table_s | table_name | engine | row_format | table_rows | avg_row_length | total_mb   | data_mb    | index_mb  | today      |
| XXXXX   | s_a        | InnoDB | Compact    |     208993 |           7475 |  1491.5312 |  1490.0156 |    1.5156 | 2009-09-09 |

mysql> alter table

  [Read more...]
Past Presentations Now Online
Employee +0 Vote Up -0Vote Down

I uploaded all of my past presentations to Slideshare recently, and realized that I hadn’t actually posted some of these on my blog in the past as well.

So I’ve created a new Presentations Page that has all of these together now.

It’s kind of funny to see the “MySQL for Oracle DBAs” presentation again - a lot has changed since 2006!

In any case, enjoy if you haven’t seen them - give them a look over if interested, and feel free to post comments or questions on the page!

More fun visualizing MySQL Database Size
+0 Vote Up -0Vote Down
Hi again!

As a sidekick for my previous post, I came up with a snippet of code that generates the Google Chart URL to visualize table size for the current database. For example, for the sakila sample database, we get URL's like

  [Read more...]
A Faster MySQL Database Size Google Chart
+0 Vote Up -0Vote Down
Abstract - As described by Walter Heck, MySQL database size can be visualized using Google Charts. With a minor code improvement the URL for the chart can be obtained twice as fast. With some more modification, the number of lines can be cut down resulting in a function that is half as long.


It's been a while since I posted - I admit I'm struggling for a bit to balance time and attention to the day job, writing a book, preparing my talks for the MySQL user's conference and of

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

When using Oracle, the data dictionary provides us with tons of tables and views, allowing us to fetch information about pretty much anything within the database. We do have information like that in MySQL 5.0 (and up) in the information_schema database, but it’s scattered through several different tables.

Sometimes a client asks us to change the datatype of a column, but forgets to mention the schema name, and sometimes even the table name. As you can imagine, having this kind of information is vital to locate the object and perform the requested action. This kind of behaviour must be related to Murphy’s Law.

In any case, I’d like to share with you a simple stored procedure that has helped us a lot in the past.

USE dba;
  [Read more...]
New in MySQL 5.1: Sheeri’s Presentation
+0 Vote Up -0Vote Down

In a nutshell: What’s New in MySQL 5.1.

Release notes: Changes in release 5.1.x (Production).

And yes, very early on (at about two minutes in), I talk about my take on Monty’s controversial post at Oops, we did it again.

To play the video directly, go to http://technocation.org/node/663/play. To download the 146 Mb video to your computer for offline playback, go to http://technocation.org/node/663/download. The slides can be downloaded as a

  [Read more...]
Table Sizes
+0 Vote Up -0Vote Down

During the course of my daily work I occasionally search for mysql queries which are cool and helpful. I once found the following query on http://forge.mysql.com/:

SELECT table_name article_attachment,
ROUND(data_length/1024/1024,2) total_size_mb,
ROUND(index_length/1024/1024,2) total_index_size_mb,
FROM information_schema.tables
WHERE table_schema = ‘dbname’
ORDER BY 3 desc;

A generally  lite version is:

select table_schema, table_name, (data_length)/pow(1024,2) AS ‘Data Size in Meg’, (index_length)/pow(1024,2) AS ‘Index Size in Meg’  from tables order by 3 desc;

You can add or remove columns etc and but this query shows the table size (data wise) index size, approx number of rows, size in MB etc. If you would like to know what else is available to add to this query, just do a “desc tables” while using the informations chema (database).

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...]
Information_Schema.Partitions – table_rows
+0 Vote Up -0Vote Down
I’m in the process of converting some very large data tables to partitioned tables. By “In the process” I mean “scripts are running as we speak and I’m monitoring what’s going on.” When I did this in our test environment (2 or 3 times to be sure), I got familiar with the information_schema.partitions table. There […]
+0 Vote Up -0Vote Down

As I putter around the MySQL INFORMATION_SCHEMA, I am finding lots of undocumented behavior for fields that should be straightforward. For example, the VIEWS table holds information about views, and the VIEW_DEFINITION field contains the view definition, right?

Well, when I was looking at the VIEW_DEFINITION today, I noticed an odd thing. Even though I had permissions to see the view definition (as proven by the SHOW CREATE VIEW command), the INFORMATION_SCHEMA.VIEWS table sometimes came up blank for the VIEW_DEFINITION. I had to figure out why, and now that I know, I’m not sure if it’s a bug or a feature…..can you figure it out?

Database changed
  [Read more...]
Inspect the Query Cache using MySQL Information Schema Plug-ins
+0 Vote Up -0Vote Down
A while ago I wrote about MySQL 5.1 information schema plug-ins.

At the time, I wrote a plug-in to report the contents of the query cache, but for all kinds of reasons, I never found the time to write a decent article about it, nor to release the code.

I am not sure if I'll ever find the time to write that article, but I just tidied up the code, and installed it in a new MySQL 5.1.26-rc server. It seems to work there, so I put the code up on the web.

Inside the source file, there's instructions to build

  [Read more...]
Previous 30 Newer Entries Showing entries 31 to 60 of 67 Next 7 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.