I am of the attitude, the day you stop learning something is the day you die. I’m not prepared to induce MySQL into both sides of that equation, however some days it never ceases to amaze me what little thing I didn’t know about MySQL.

Today I saw in reviewing SQL statements for an application SELECT LAST_INSERT_ID(). No big deal, that is expected, however I then saw UPDATE … SET id=LAST_INSERT_ID(id+1) WHERE …

Having never seen this syntax I was forced to review it’s usage. See MySQL Documentation

If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID(). This can be used to simulate sequences:

Seems like a layman’s idea of Sequence support, but you are still restricted with the problems of a manual implementation. Transactions support, and if you use MyISAM, table level locking.

I was asked a question today, “DATETIME vs TIMESTAMP. When to use which & why?”

It’s a good MySQL introduction question, here are some general considerations for choosing one.

Do you need Date values other then an EPOCH value? (i.e. before 1970) If the answer is yes, then DATETIME is required.
If you do not however, then TIMESTAMP is the best choice for a few reasons.

1. The TIMESTAMP columns uses 4 Bytes to record it’s value, while DATETIME uses 8 bytes. Using the smallest storage is always a best practice for all columns.
2. The TIMESTAMP column supports the CURRENT_DATE syntax in the CREATE TABLE command. This enables the column to have a default value for INSERT or for UPDATE, but not both. Indeed this is the only data type that allows for any default value that is not a constant.
3. All date functions (at least the ones I use) work equally as well with TIMESTAMP and DATETIME.

I have yet to find any benchmarking to indicate any performance differences of not selecting TIMESTAMP.

And just for a piece of trivia, the DATE datatype is 3 bytes, the TIME datetype is 3 bytes, so why is the DATETIME 8 bytes?
Yes, for those that intend to reply I do know the answer, however others readers may not. Comments please!

If you have not been to the www.mysql.com website, it’s a new look.

Yesterday I attempted to get a working MySQL environment to support the number of utilities we all use including mytop, innotop, mybench, mysqltoolkit. These products require a number of Perl Dependencies, and while that may be a rather trivial task under Linux and with the power of cpan, working on Solaris is a whole different story.

For the record, I’m working with Solaris 9 SPARC 64bit.

I won’t detail you with how hard it was to get to this point, except to say thanks to Jeremy, Baron and Frank so far. Here is where I’m at.

You need a number of pre-requisites, most from sunfreeware.com

Pre-Requisites

$ wget ftp://ftp.sunfreeware.com/pub/freeware/sparc/9/libiconv-1.11-sol9-sparc-local.gz
$ wget ftp://ftp.sunfreeware.com/pub/freeware/sparc/9/gcc-3.4.6-sol9-sparc-local.gz
$ wget ftp://ftp.sunfreeware.com/pub/freeware/sparc/9/make-3.81-sol9-sparc-local.gz
$ gunzip libiconv-1.11-sol9-sparc-local.gz gcc-3.4.6-sol9-sparc-local.gz make-3.81-sol9-sparc-local.gz
$ pkgadd -d libiconv-1.11-sol9-sparc-local
$ pkgadd -d gcc-3.4.6-sol9-sparc-local
$ pkgadd -d make-3.81-sol9-sparc-local

$ wget http://search.cpan.org/CPAN/authors/id/A/AB/ABURLISON/Solaris-PerlGcc-1.3.tar.gz
$ gunzip Solaris-PerlGcc-1.3.tar.gz
$ tar xvf Solaris-PerlGcc-1.3.tar
$ cd Solaris-PerlGcc-1.3
$ perl Makefile.PL
$ make install
$ wget http://search.cpan.org/CPAN/authors/id/M/MS/MSCHWERN/ExtUtils-MakeMaker-6.36.tar.gz
$ gunzip ExtUtils-MakeMaker-6.36.tar.gz
$ tar xvf ExtUtils-MakeMaker-6.36.tar
$ cd ExtUtils-MakeMaker-6.36
$ perl Makefile.PL
$ make install

Back to installing

$ PATH=/usr/local/bin:$PATH;export PATH
$ PATH=/usr/perl5/5.6.1/bin/:$PATH;export PATH

$ perlgcc Makefile.PL
Checking if your kit is complete...
Looks good
Writing Makefile for Term::ReadKey
netcontrol1:/tmp/TermReadKey-2.30# make install
make: *** No rule to make target `/usr/perl5/5.6.1/lib/sun4-solaris-64intConfig.pm', needed by `Makefile'.  Stop.

This is where the trail ends as my friend google has not helped me past this point, so I’m turning the community. If anybody can assist please let me know.

I realized recently from observation that some smaller websites which use MySQL do not have a working backup and recovery strategy. Some may have a backup approach but it will not work in a recovery. As part of a number of discussions on Backup & Recovery here is part 1.

Using straight ‘cp’ for Backup

Using ‘cp’ to simply copy your running database *DOES NOT WORK*. You may be lucky, but in a world of guarantees, this is no guarantee that your can recovery your system. Why is this so.

• The data is not consistent during the backup. If it takes say 5 minutes to copy your files, they are copied probably in alphabetical order, what if data is written during the backup to a table starting with ‘A’ and a table starting with ‘Z’, the A file has already been copied.
• When using MyISAM, Indexes are not flushed to disk unlike Table data. This means that while MyISAM has the facility of recover and rebuild indexes using myisamchk, you need to know if you have corruption, you need to rebuild your tables offline, and there is not assurance your data can be corrected.
• Likewise with using Innodb, Data and Indexes are not flushed to disk. While Innodb does flush the redo logs to enable crash recovery, I have seen on a production website, granted 4.1 that Innodb failed to recover and caused major downtime and serious business ramifications.

Using ‘cp’ correctly

In order to copy your database, there are two different ways. The first is to shutdown your database first then copy your data files. Be sure to also copy your my.cnf file, this is important in a recovery process. The second is to use FLUSH command. For example.

mysql> FLUSH TABLES WITH READ LOCK;
-- In second session copy all data
mysql> UNLOCK TABLES;

The advantage is you don’t have to shutdown your instance, the disadvantage is that FLUSH TABLES will halt any WRITE statements to your database, so depending on the time to copy your data files that could have significant effect on your system. In a high write environment the FLUSH command could also take a significant time to complete.

I’ll leave with one simple tip. You should always have sufficient diskspace on your machine for performing the backup. This is important for two parts. First, if you say compress your backup during the process, you are taking a longer time then a straight copy and your database is unavailable longer. Second, over 90% of any recovery occurs with last nights backup, and the time take to uncompress the backup affects your recovery time, particularly in a large system. Your ‘cp’ should indeed be a two phase process. First ‘cp’ on the local server for a local backup, then have an off server compressed backup.

In my next Part, I’ll discuss more alternatives to improving your backup strategy.

References

FLUSH TABLES

You have to love error messages some times. Today is was “ERROR 1289 (HY000): The ‘UNKNOWN’ feature is disabled;”

For those interested.

mysql> select version();
+-------------+
| version()   |
+-------------+
| 6.0.0-alpha |
+-------------+
1 row in set (0.00 sec)
mysql> set session sql_mode=NO_ENGINE_SUBSTITUTION;
Query OK, 0 rows affected (0.00 sec)

mysql> create table T2 (id INT UNSIGNED NOT NULL) ENGINE=InnoDB;
ERROR 1289 (HY000): The 'UNKNOWN' feature is disabled; you need MySQL built with 'UNKNOWN'
to have it working

In this case it’s obviously something that’s not quite right, so being a good community contributor I raised a bug. You can read more at Bug #29373.

MySQL values the contributions from the community, even as trivial as documentation or error messages. If you have an inquiry raise it at the forums or the lists, or if it’s obviously an issue raise it in the Bugs System

When you use SHOW STATUS can can restrict with the LIKE syntax, allowing for a subset of values. For example:

mysql> SHOW GLOBAL STATUS LIKE 'Com%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Com_admin_commands       | 0     |
| Com_alter_db             | 0     |
| Com_alter_table          | 0     |
| Com_analyze              | 0     |
| Com_backup_table         | 0     |
| Com_begin                | 0     |
| Com_change_db            | 0     |
| Com_change_master        | 0     |
...

That’s great, but sometimes you want specific values. Using WHERE can achieve this. For Example.

mysql> SHOW GLOBAL STATUS WHERE VARIABLE_NAME IN (’Com_insert’,'Innodb_buffer_pool_pages_latched’,'threads_running’);
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| Com_insert                       | 0     |
| Innodb_buffer_pool_pages_latched | 0     |
| Threads_running                  | 1     |
+----------------------------------+-------+
3 rows in set (0.00 sec)

Cool, the downside is you loose the wildcard capability, however you can string commands together with LIKE

mysql> SHOW GLOBAL STATUS WHERE VARIABLE_NAME LIKE 'innodb%' OR VARIABLE_NAME LIKE  'com%';

Last night at the MySQL NY Meetup we continued on from a very successful July presentation on “Practical Performance Tips & Tricks”. I must admit after speaking and standing all day for the MySQL DBA Bootcamp for the Oracle DBA it was a stretch, and we didn’t cover all material as expected, but the evening was still very productive to everybody. Links are here for my August Presentation and July Presentation.

Thanks to Marc and the team from LogicWorks for again sponsoring our NY Meetup Event. We don’t get the beer and food any other way.

As a consultant working on client sites even in the time from the previous meeting, I see a number of simple steps that can be applied to every single MySQL environment and last nights talk placed some focus on this. The following is the homework that has been set for attendees, so next month we can have a panel discussion and then continue more on the analysis of MySQL.

This homework focuses on three areas. It seems a lot, but it will be worth it.

1. Collection of SQL statements (in various forms)
2. Monitoring of raw MySQL Status information
3. Review of Schema Data Types for more optimal storage

Collection

This information is to be collected.

• Production is to have slow query log enabled and long_query_time =1
• Daily review of Production slow query log to view the longest running and most frequent (Slide Aug-13)
• All development and test environments have general log and slow query log enabled.
• All application paths to be tested in development or testing, to capture all SQL. Use self documenting approach (Slide: Aug-21)

Outputs
Your outputs will include offending SQL statements via the Slow Query log that require investigation, extra credit for doing an EXPLAIN and a SHOW CREATE TABLE for each table in the query for analysis of improving the queries.
You will have a review of every SQL statement. A simple desk check will determine are there any queries which are exact duplicates, are there any queries that could be combined (as they use the same where condition). Are there any queries that are repeated a lot, but with only changing values. These are a clear candidate for combining into a single query.

MySQL Status Information

The MySQL Status can provide help into where to look for problems. This is the raw fundamentals that either home grown scripts or MySQL Network Monitoring and Advisory Service use to provide monitoring of important information (e.g. Buffer Hit Ratio) and can provide alerts of present or potential pending problems. We will not be reviewing all these status (~250) or developing any detailed analysis, however this is designed to show you the building blocks.

You should collect figures for 1 minute, 1 hour and 1 day that represent appropriate load on your system.

1 Minute

$ mysqladmin -uroot -p -r -i 1 -c 60 extended-status | grep -v " | 0 " > minute.log

1 Hour

$ mysqladmin -uroot -p -i 60 -c 60 extended-status > hourly.log

1 Day

$ mysqladmin -uroot -p -i 3600 -c 24 extended-status > daily.log

Extra credit for downloading Statpack and doing an analysis of the statistics. The trick is to take the first and last status output from each of the Hourly and Daily logs to get a 1 hour picture and a 1 day picture.

Review Schema

For reviewing the schema, try to use a static production sized copy (e.g. a copy of production in a test environment). This ensures you don’t make changes that will cause your production system to fail, and before and after figures are for the same data set as it’s not changing during production use.

• Calculate size of present data
• Review schema for immediate size improvements (Slide Jul-59)
• Apply size improvements and re-run to see size improvements.

SQL

Calculate Size of all schemas

select table_schema, count(*) as tables,
           sum(data_length+index_length)/1024/1024 as total_mb,
           sum(data_length)/1024/1024 as data_mb,
           sum(index_length)/1024/1024 as index_mb
from information_schema.tables
group by table_schema
order by 3 desc;

Calculate Size of all tables for given schema

use test;
select table_name,engine,row_format,avg_row_length,table_rows,
          (data_length+index_length)/1024/1024 as total_mb,
          (data_length)/1024/1024 as data_mb,
          (index_length)/1024/1024 as index_mb
from information_schema.tables
where table_schema=database()
order by 6 desc;

We have all done it in the past, and probably most people that read this (will admit| or lie) to still doing it, but everybody must start making an effort to improving MySQL security in the usage on your MySQL Installation, including just on your laptop, and in presentations that people read.

I spotted a reference article on Planet MySQL this evening and without looking at the details the syntax presented typifies two basic and fundamental 101 MySQL security issues.

1. Always, always, always have a password for a MySQL account, especially for the ‘root’ user.
2. Don’t use the ‘root’ user unless you really have to. The SUPER privilege is just that SUPER, there are many things you really don’t want every person accessing to have. In a larger environment you want to ensure good principles, but also in single developer environment.

I was asked on Thursday at a seminar I was giving. “Our developers login as ‘root’ for development and use ‘root’ for MySQL access, is this bad?”. To which my simple response was “Yes”.

I think MySQL should improve the product and not allow a root user without a password. You can of course use mysql_secure_installation to set this, but it’s not a mandatory step. You can also use SQL_MODE to ensure users can’t be created without a password, in both instances the horse has already bolted.

While using the latest MySQL 5.1.20 yesterday I came across another situation that was not expected as with previous editions of MySQL. The background is experimenting with DRBD. When I configured MySQL to startup with a /etc/my.cnf file with data on a DRBD partition I got a failed startup error message with mysqld_safe.

$ bin/mysqld_safe &
[1] 12615
070720 10:10:42 mysqld_safe Starting mysqld daemon with databases from /drbd/data
070720 10:10:42 mysqld_safe mysqld from pid file /drbd/data/newyork.localdomain.pid ended

Ok. Well this happens so I went to the data directory to look for `hostname`.err.

$ cd /drbd/data
$ ls -l

What the! There is no error log. Then the discussion started about this. Apparently mysqld_safe now uses syslog (e.g. /var/log/messages) for logging messages. Ok, but where is the line between mysqld_safe and mysqld. There was some confusion here about this, however investigation of /var/log/messages showed what was necessary to at least find the error.

$ tail -30 /var/log/messages
Jul 19 16:59:51 newyork mysqld_safe[6156]: Starting mysqld daemon with databases from /drbd/data
Jul 19 16:59:54 newyork mysqld[6160]: /usr/local/mysql/bin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
Jul 19 16:59:54 newyork mysqld[6160]: [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: The first specified data file ./ibdata1 did not exist:
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: a new database to be created!
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Setting file ./ibdata1 size to 10 MB
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Database physically writes the file full: wait...
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Log file ./ib_logfile0 did not exist: new to be created
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Setting log file ./ib_logfile0 size to 5 MB
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Database physically writes the file full: wait...
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Log file ./ib_logfile1 did not exist: new to be created
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Setting log file ./ib_logfile1 size to 5 MB
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Database physically writes the file full: wait...
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Doublewrite buffer not found: creating new
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Doublewrite buffer created
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Creating foreign key constraint system tables
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Foreign key constraint system tables created
Jul 19 16:59:54 newyork mysqld[6160]: InnoDB: Started; log sequence number 0 0
Jul 19 16:59:54 newyork mysqld[6160]: [ERROR] /usr/local/mysql/bin/mysqld: Can't find file: './mysql/host.frm' (errno: 13)
Jul 19 16:59:54 newyork mysqld[6160]: [ERROR] Fatal error: Can't open and lock privilege tables: Can't find file: './mysql/host.frm' (errno: 13)

To ensure backward compatibility now with previous versions of MySQL, it is necessary to specific in my.cnf the log-error parameter.

#my.cnf
[mysqld]
log-error = /drbd/data/mysql.err

Starting gives you expected error logging.

$ more /drbd/data/mysql.err
070720 10:16:30 mysqld_safe Starting mysqld daemon with databases from /drbd/data
/usr/local/mysql/bin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
070720 10:16:30 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
070720 10:16:30  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.
070720 10:16:30 mysqld_safe mysqld from pid file /drbd/data/newyork.localdomain.pid ended

So, you need to read the fine print in the Changes in release 5.1.20 (25 June 2007) 5.1 Manual you get.

Incompatible change: mysqld_safe now supports error logging to syslog on systems that support the logger command. The new –syslog and –skip-syslog options can be used in conjunction with the –log-error option to control logging behavior, as described in Section 5.3.1, “mysqld_safe — MySQL Server Startup Script”. The default is to use syslog, which differs from the previous default behavior of writing an error log file. To maintain the older behavior, use the –log-error option. (Bug#4858)

If you read mysqld_safe — MySQL Server Startup Script you also see the following note.

Note

As of MySQL 5.1.20, the default error logging behavior is to write errors to syslog on systems that support the logger program. This differs from the default behavior of writing an error log file for versions prior to 5.1.20. To maintain the older behavior, use the –log-error option.

RTFM those release notes!

MySQL recently released 5.1.20 beta. I just ran into another gotcha, which prompted me to post this draft I’ve had for a while.

rbradford@newyork:/opt/mysql51$ scripts/mysql_install_db
Installing MySQL system tables...
070709 23:24:08 [Note] Plugin 'InnoDB' disabled by command line option
OK
Filling help tables...
070709 23:24:08 [Note] Plugin 'InnoDB' disabled by command line option
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h newyork password 'new-password'
See the manual for more instructions.
You can start the MySQL daemon with:
cd . ; ./bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl

Please report any problems with the ./bin/mysqlbug script!

The latest information about MySQL is available on the web at

http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

Wait, there is an Innodb error message I’ve not seen . I’ll have to look into that.

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| Engine     | Support | Comment                                                        | Transactions | XA  | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES | YES        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO  | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO  | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO  | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO  | NO         |
| FEDERATED  | YES     | Federated MySQL storage engine                                 | YES          | NO  | NO         |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO  | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO  | NO         |
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
8 rows in set (0.00 sec)

mysql> show plugins;
+------------+--------+----------------+---------+---------+
| Name       | Status | Type           | Library | License |
+------------+--------+----------------+---------+---------+
| binlog     | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| partition  | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| ARCHIVE    | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| BLACKHOLE  | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| CSV        | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| FEDERATED  | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| MEMORY     | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| InnoDB     | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| MyISAM     | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
+------------+--------+----------------+---------+---------+
10 rows in set (0.00 sec)

Yet, it works fine! So what does message “[Note] Plugin ‘InnoDB’ disabled by command line option” really mean. On closer inspection it doesn’t say Error, however it’s a different message from previous installs, so it requires more investigate to know what’s happening.

I’m at some internal MySQL training today at our US HQ. In a room of 11 people we are all using our laptops for access to additional machines. The beauty of this is we have Linux (Ubuntu 5.10, 6.10, 7.04, Red Hat Fedora), Mac OS/X and Windows Operating Systems on various staff machines. It’s great to realize we are not locked into just one infrastructure, and we have the ability to all do our work effectively and MySQL works just as well across these technologies. I can’t however SSH from my iPhone yet, unlike my previous Nokia E62 PDA.

MySQL uses disk. Ok, so everybody knew that. MySQL uses disk in two primary ways.

• Random I/O (Reading & Writing Data/Index blocks)
• Sequential I/O (Binary Log, InnoDB Redo Log)

Historically it’s been best practice to separate these onto different spindles, and also separating the OS and tmp space onto a third spindle. With commodity H/W that can be easily done, but today a lot of people use SAN. Is this a good thing for a MySQL Database?
That’s a topic of much discussion at a later time, however I’ll add two points. A lot of SAN configurations are RAID 5, and RAID 10 is a better choice due to removing the requirement to calculate the parity. Second, last week I observed a RAID disk failure and it took an incredible long time for the disk to be re-built. Just how many SAN uses our there have actually timed a disk rebuild on a loaded system and seen the impact on the system in general.

Back on topic, I don’t have access to any variety of hardware, so community here is where you can help. Those that can spare 5 mins, and have some free disk space (< 5GB), here is an exercise.

Commands

$ time dd if=/dev/zero count=100000 of=testfile.32k bs=32k
$ ls -lh testfile.32k
$ rm testfile.32k

We had the Top 5 wishes for MySQL started by Jay recently. So in true chain letter fashion I’m starting a new one this week. “The top 5 Best Practices for MySQL”. This like it’s predecessor is generally vague, so it can include points on development, design, administration etc.

I was reading some points on a Blog post today here. I didn’t see it via Planet MySQL but via a Google Alert.

I’ve been able to steal some more time to work on my script following My ‘hourly’ MySQL monitor script Version 0.03 almost a month ago.

I didn’t know you could actually do this before addressing this problem in a benchmark using 5.0.36.

DROP TABLE IF EXISTS a;
CREATE TABLE a(
id  INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
i   INT UNSIGNED NOT NULL,
c   CHAR(1) NOT NULL)
ENGINE=INNODB;
DROP TABLE IF EXISTS b;
CREATE TABLE b(
id  INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
i   INT UNSIGNED NOT NULL,
c   CHAR(1) NOT NULL,
t   TEXT NOT NULL)
ENGINE=INNODB;

INSERT INTO a VALUES(1,1,'a');
INSERT INTO a VALUES(2,2,'b');
INSERT INTO a VALUES(3,3,'c');
INSERT INTO a VALUES(4,4,'d');
INSERT INTO b VALUES(1,1,'a',REPEAT('The quick brown fox jumped over the lazy dog',100));
INSERT INTO b VALUES(2,2,'b',REPEAT('The quick brown fox jumped over the lazy dog',200));
INSERT INTO b VALUES(3,3,'c',REPEAT('The quick brown fox jumped over the lazy dog',300));
INSERT INTO b VALUES(4,4,'d',REPEAT('The quick brown fox jumped over the lazy dog',400));

SHOW SESSION STATUS LIKE '%tmp%tables';
SHOW SESSION STATUS LIKE '%tmp%tables';
UPDATE a,b SET a.i = 10, a.c = 'x', b.i = 20 WHERE a.id = b.id and a.id=1;
SHOW SESSION STATUS LIKE '%tmp%tables';
UPDATE a,b SET a.i = 10, a.c = 'x', b.i = 20,b.t=REPEAT('b',5000) WHERE a.id = b.id and a.id=3;
SHOW SESSION STATUS LIKE '%tmp%tables';
UPDATE b SET t = REPEAT('a',5000) WHERE b.id=2;
SHOW SESSION STATUS LIKE '%tmp%tables';

mysql> source test.sql
--------------
SHOW SESSION STATUS LIKE '%tmp%tables'
--------------

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_tables      | 3     |
+-------------------------+-------+
2 rows in set (0.00 sec)

--------------
SHOW SESSION STATUS LIKE '%tmp%tables'
--------------

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_tables      | 4     |
+-------------------------+-------+
2 rows in set (0.00 sec)

--------------
UPDATE a,b SET a.i = 10, a.c = 'x', b.i = 20 WHERE a.id = b.id and a.id=1
--------------

Query OK, 2 rows affected (0.02 sec)
Rows matched: 2  Changed: 2  Warnings: 0

--------------
SHOW SESSION STATUS LIKE '%tmp%tables'
--------------

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_tables      | 6     |
+-------------------------+-------+
2 rows in set (0.00 sec)

--------------
UPDATE a,b SET a.i = 10, a.c = 'x', b.i = 20,b.t=REPEAT('b',5000) WHERE a.id = b.id and a.id=3
--------------

Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

--------------
SHOW SESSION STATUS LIKE '%tmp%tables'
--------------

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     |
| Created_tmp_tables      | 8     |
+-------------------------+-------+
2 rows in set (0.00 sec)

--------------
UPDATE b SET t = REPEAT('a',5000) WHERE b.id=2
--------------

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

--------------
SHOW SESSION STATUS LIKE '%tmp%tables'
--------------

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     |
| Created_tmp_tables      | 9     |
+-------------------------+-------+
2 rows in set (0.00 sec)

First it was the SlashDot effect, then it was the Digg effect, now it’s the Facebook effect. I have a friend at Facebook and he was talking about the effect of the Facebook Platform API that was released a few weeks ago. Sites were now struggling to cope with the effect of massive amounts of new traffic, ensuring that experienced MySQL Consultants will have plenty of scale out opportunities.