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 [...]
Listing some useful sed / awk liners to use with MySQL. I use these on occasion. sed, awk & grep have many overlapping features. Some simple tasks can be performed by either. For example, stripping empty lines can be performed by either: grep '.' awk '/./' sed '/./!d' grep -v '^$' awk '!/^$/' sed '/^$/d' [...]
An InnoDB table must have a primary key (one is created if you don't do it yourself). You may have a natural key at hand. Stop! Allow me to suggest an AUTO_INCREMENT may be better. Why should one add an AUTO_INCREMENT PRIMARY KEY on a table on which there's a natural key? Isn't an AUTO_INCREMENT [...]
Question asked by a student: is there a way to limit a table's quote on disk? Say, limit a table to 2GB, after which it will refuse to grow? Note that the requirement is that rows are never DELETEd. The table must simply refuse to be updated once it reaches a certain size. There is [...]
Some of this is old stuff, but more people are now converting to InnoDB plugin, so as to enjoy table compression, performance boosts. Same holds for people converting to Percona's XtraDB. InnoDB plugin requires innodb_file_per_table. No more shared tablespace file. So your ibdata1 file is some 150GB, and it won't reduce. Really, it won't reduce. [...]
Risking some flames, I'd like to suggest only two options for choosing PRIMARY KEYs for InnoDB tables. I suggest they should cover 99% (throwing numbers around) of cases. PRIMARY KEY cases An integer (SMALLINT / INT / BIGINT), possibly AUTO_INCREMENT column. The combination of two columns on a many-to-many connecting table (e.g. film_actor, which connects [...]
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. [...]
This post follows up on Baron's How often should you use OPTIMIZE TABLE?. I had the opportunity of doing some massive purging of data from large tables, and was interested to see the impact of the OPTIMIZE operation on table's indexes. I worked on some production data I was authorized to provide as example. The [...]
I'm evidencing an increase in the planning, confidence & execution for MyISAM to InnoDB migration. How much can a single consultant observe? I agree Oracle should not go to PR based on my experience. But I find that: More companies are now familiar with InnoDB than there used to. More companies are interested in migration [...]
Actually this is (almost) all I wanted to say. This is intentionally posted with all related keywords in title, in the hope that a related search on Google will result with this post on first page. I'm just still encountering companies who use MyISAM as their storage engine and are unaware that their nightly backup [...]