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.
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 (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...]
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...]
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...]
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:
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.
mysqldumpcommands for backing up individual partitions of the tables in the current schema. The script is maintained as a snippet at MySQL Forge.
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
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...]
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...]
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...]
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...]
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:
[Read more...]node1> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'innodb_os_log_written'; +-----------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +-----------------------+----------------+ | INNODB_OS_LOG_WRITTEN | 512 | +-----------------------+----------------+ 1 row in set (0.00 sec) node1> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'questions'; +---------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------+----------------+ | QUESTIONS | 28 | +---------------+----------------+ 1 row in set (0.00
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...]
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...]
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...]
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!
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.
CREATE DATABASE IF NOT EXISTS dba; USE dba;[Read more...]
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...]
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,
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).
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:
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.
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?
mysql> USE INFORMATION_SCHEMA; Database changed mysql> SELECT TABLE_NAME,VIEW_DEFINITION FROM VIEWS WHERE TABLE_SCHEMA='sakila';[Read more...]