I had so much to say in response to a recent post asking about virtualization from Jennifer Glore that I realized it was long enough to be a blog post.It really depends on what you're looking to do. Many companies don't have the money and staff to have an in-house data center with proper power and network redundancy; others don't want the depreciati

I was doing some research over the weekend on how transactions work "under the hood" in MySQL. I thought it might be enlightening if I wrote about what I found.The database system must resolve the situation where, for whatever reason, the database server stops working in the middle of a transaction.  Perhaps the power goes out, the hard drive crashes, or the CPU disappears in a cloud of smoke. Maybe the MySQL Server itself crashes for some reason. What does the MySQL Server do when operation resumes? Potentially, there are transactions in an inconsistent state. How are inconsistent transactions resolved? They are resolved through a recovery process involving log files that are called transactional logs. There are two kinds of transactional logs: redo logs and undo logs.

The maximum length of a "VARCHAR" is only restricted by the maximum row length. In most storage engines, the maximum row length is the maximum allowed by MySQL, which is 65,535 bytes. Only the NDB storage engine has a different maximum value.The "VARCHAR" data type has some overhead. If the length of "VARCHAR" is less than 255, one byte per row is used to store the actual length of the string. If the length of "VARCHAR" is greater than 255, the

Hello everyone,Reading PlanetMySQL today, I discovered that Alex Gorbachev's announcement that he has released the first public beta of his Oracle Grid Control plugin for MySQL was not aggregated! This is probably because Alex is primarily working on our Oracle space and so his feed isn't on planet.This p

At the 2008 MySQL Conference and Expo, The Pythian Group gave away "EXPLAIN" cheatsheets. They were very nice, printed in full color and laminated to ensure you can spill your coffee* on it and it will survive.For those not at the conference, or those that want to make more, the file is downloadable as a 136Kb PDF at explain-diagram.pdf* or tea, for

Hi all,I was asked this question in an e-mail -- feel free to ask your questions in the comments, as I will point the original author to this post to answer those questions. There is not a lot of data here, so instead of me asking questions in an e-mail I figured I would open it up to the (MySQL) world. Without further ado, here's the question:Basically when we open more than one connection- we're opening 5, and we do processing in mysql (innodb tables) the server cpu and m

I was asked this question recently, and I thought it was a great little tidbit of knowledge to pass along. The short answer is "no". The slightly longer answer was written up by Jan Kneschke when dealing with a forum post about proxy + connection pooling.From http://forums.mysql.com/read.php?146,169265,169700The clear-text password is _never_ transfered in the authentication phase.On the network we have:

The answers to the last pop quiz are up: http://www.pythian.com/blogs/868/pop-quiz-mysql-clusterSo here's another pop quiz. Given the following:Welcome to the MySQL monitor. Commands end with ; or \\g.Your MySQL connection id is 16450949 to server version: 4.1.14-standard-logType 'help;' or '\\h' for help. Type '\\c' to clear the buffer.mysql> select count(*),length(password) from mysql.user group by l

While doing a standard audit for a new client, I recommended a few changes to get better performance. Because I had several changes, I used the documentation at http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.htmland found that "innodb_flush_log_at_trx_commit" is a dynamic variable. I was surprised, because most operations dealing with file sizes and operations are not dynamic.

MySQL Full Text Search LimitationsSuppose you have a MyISAM table containing a column with a full text index. This table starts to grow to a significant size (millions of rows) and gets updated fairly frequently. Chances are that you'll start to see some bottlenecks when accessing this table, since without row level locking, the reading and writing operations will be blocking each other.A solution that many people would suggest right away is to use the master for writes and a slave for reads, but this only masks the problem, and it won't take long before enough read traffic on the slave starts causing slave lags.Why Sphinx?

A broken VIEW was caused by the view's definer being non-existent. In this particular system, backups are done by replicating all the machines (production, development, test, etc) to one server and doing cold physical backups off that server, which currently has 12 instances running.So in order to find on what machine the user might still be defined, I went to the backup server. All the data directories are in one path, ie:instance 1 has a datadir of /data/mysql/instance1instance 2 has a datadir of /data/mysql/instance2Now, the unix tool "strings" can be used against many types of files. In particular, though, you can use "strings" on the mysql/user.MYD file to see the username, host, and password hash.

I was writing up some notes about a client's status variables, and I'd had to remind myself of Handler_read_first. Handler_read_first is a counter that increments when the first entry in the index is read, which may indicate that a full index scan is being used. With this particular client, I ran a SHOW STATUS, waited 100 seconds, and ran it again. Handler_read_first jumped by about 53. So I wrote down in my notes:first in the index...every other second. Lots of table scans?As s

I just filed a very annoying bug when trying to compile with plugin engines using the 5.1.xx source tarball.DescriptionI am trying to test SphinxSE as a plugin instead of getting it statically linked and came across an annoying bug. When using the configure "--with-plugins" option only once, the engine is statically linked. When using it twice, the first engine is created as a plugin, and the 2nd one is linked statically. Here are a couple of examples:

This tip is to overcome a type of expensive query that I see all the time. In doing this, I uncovered a nasty bug in MySQL v5.1.ScenarioI've seen queries like these several times:SELECT COUNT(*) FROM sample WHERE status = n;Normally, this is OK, but if you have hundreds of millions of rows, only a handful of possible statuses and execute this query dozens of times per minute on an active InnoDB table, it quickly becomes a bottleneck.One SuggestionEstablis

Today's trivial MySQL system variable:old_alter_tableThe interesting bit is that this is a system variable, and shows up in SHOW GLOBAL VARIABLES, but is not documented on the Server System Variables manual page.Instead, it is documented on the manual page for Server options.Unfortunately, that documentation is v

A short time ago I posted how I was Using XtraDB Backup to backup InnoDB. Overall, the blog post was positive, but experiences that others have had (and commented to on that blog post) have made me want to put out another short article about using XtraDB backup.The first few points remain the same -- the backup process is stable, we were able to use the binaries without compiling, and using Innobackupex as the wrapper script, analogous to Innobackup.pl. However, we did figure out why Xtrabackup had to be run as the mysql user:Xtrabackup writes to the data dictionary file (ibdata1, for example). We have not examined if it also writes to the data and index files (still ibdata1 by default, or the .ibd files when using innodb_file_per_table). [EDIT: The authors of Xtrabackup have commented below as to why the write occurs:xtrabackup is kind of very small and restricted instance of InnoDB, and reuses a lot of InnoDB code.InnoDB by  []

At the November MySQL User Group, Patrick Galbraith ran into a problem where "binlog-do-db" was duplicated. It manifests itself like this:(copied from http://lists.mysql.com/replication/607) master my.cnf:binlog-do-db=db1slave my.cnf:replicate-do-db=db1;Relevant show slave status outputReplicate_Do_DB: db1;,db1;When db1 is modified on master, Read_Master_Log_Pos and Relay_Log_Pos dochanges, also

A friend asked for this, so I thought it'd be helpful:All tables with no primary key:use INFORMATION_SCHEMA;select CONCAT(t.table_name,".",t.table_schema) as tbl,from TABLES AS t LEFT JOIN KEY_COLUMN_USAGE AS c ON (t.TABLE_NAME=c.TABLE_NAME AND c.CONSTRAINT_SCHEMA=t.TABLE_SCHEMA AND constraint_name='PRIMARY') WHERE t.table_schema!="information_schema" AND constraint_name IS NULL;All tables and their primary keys, if exist:use INFORMATION_SCHEMA

In Spotting the Wolf in Sheep's Clothing, Frank Mash writes about a specific person who is spreading fear, uncertainty and doubt about MySQL.Now, this always gets me, especially with MySQL. For how long will MySQL be the bastard stepchild of the database world? Because really, it's been a full-fledged DBMS for at least 5 years. 10 years ago there were no transactions, but....that was 10 years ago! 10 years ago everyone made $100,000 per year, took 3 hour lunch breaks, played foosball in the office, coveting their IT stock and sat in massage chairs with Apple Cinema displays.How much have *you* changed in 10 years? Your digital world? 10 years ago we paid for text messages by the character, which is why we came up with rlly abbr wrds like kthxbye! and OMG, LOL, ROFL and my favorite, ROFLPMP -- because it makes me giggle whenever I meet a professional Project Manager who has PMP as a suffix.Whoops, mini-rant aside....don't hate  []

MySQL's manual page for InnoDB's adaptive hash states: If a table fits almost entirely in main memory, the fastest way to perform queries on it is to use hash indexes. InnoDB has a mechanism that monitors index searches made to the indexes defined for a table. If InnoDB notices that queries could benefit building a hash index, it does so automatically.Note that the hash index is always built base