MacLochlainns Weblog

Indexes are separate data structures that provide alternate pathways to finding data. They can and do generally speed up the processing of queries and other DML commands, like the INSERT, UPDATE, REPLACE INTO, and DELETE statements. Indexes are also called fast access paths.

In the scope of the InnoDB Database Engine, the MySQL database maintains the integrity of indexes after you create them. The upside of indexes is that they can improve SQL statement performance. The downside is that they impose overhead on every INSERT, UPDATE, REPLACE INTO, and DELETE statement, because the database maintains them by inserting, updating, or deleting items for each related change in the tables that the indexes support.

Indexes have two key properties—usability and visibility. Indexes are both usable and visible by default. That means they are visible to the MySQL cost-based optimizer and usable when statements run against the tables they support.

You have the ability to make any index invisible, in which case queries and DML statements won’t use the index because they won’t see it. However, the cost-based optimizer still sees the index and maintains it with any DML statement change. That means making an index invisible isn’t quite like making the index unusable or like dropping it temporarily. An invisible index becomes overhead and thus is typically a short-term solution to run a resource-intensive statement that behaves better without the index while avoiding the cost of rebuilding it after the statement runs.

It is also possible to make an index unusable, in which case it stops collecting information and becomes obsolete and the database drops its index segment. You rebuild the index when you change it back to a usable index.

Indexes work on the principal of a key. A key is typically a set of columns or expressions on which you can build an index, but it’s possible that a key can be a single column. An index based on a set of columns is a composite, or concatenated, index.

Indexes can be unique or non-unique. You create a unique index anytime you constrain a column by assigning a primary key or unique constraint, but they’re indirect indexes. You create a direct unique index on a single column with the following syntax against two non-unique columns:

1
2

CREATE INDEX common_lookup_u1
ON common_lookup (common_lookup_table) USING BTREE;

You could convert this to a non-unique index on two columns by using this syntax:

1
2

CREATE INDEX common_lookup_u1
ON common_lookup (common_lookup_table, common_lookup_column) USING BTREE;

Making the index unique is straightforward;, you only need to add a UNIQUE key wordk to the CREATE INDEX statement, like

1
2
3
4

CREATE UNIQUE INDEX common_lookup_u1
ON common_lookup ( common_lookup_table
                 , common_lookup_column
                 , common_lookup_type) USING BTREE;

Most indexes use a B-tree (balanced tree). A B-tree is composed of three types of blocks—a root branch block for searching next-level blocks, branch blocks for searching other branch blocks, or and leaf blocks that store pointers to row values. B-trees are balanced because all leaf-blocks are at the same level, which means the length of search is the same to any element in the tree. All branch blocks store the minimum key prefix required to make branching decisions through the B-tree.

Somebody asked why you can’t implement MySQL triggers that write information when you want to stop the DML statement, like autonomous procedures in Oracle. The question was a surprise but I didn’t find anything on it, so here’s how you can do it. This is more or less like an autonomous process by leveraging both the InnoDB and MyISAM engine’s behaviors. This post leverages an earlier explanation of MySQL Triggers.

1. First you create a MyISAM table, which is a persistent store that auto commits when you’re other InnoDB tables can be transactionally dependent. Here’s a simple MyISAM logger table.

CREATE TABLE logger
( logger_id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
, logger_event      VARCHAR(50)
, logger_table      VARCHAR(50)
, logger_instring   VARCHAR(100)
, logger_outstring  VARCHAR(100)
, created_by        INT UNSIGNED
, creation_date     DATE
, last_updated_by   INT UNSIGNED
, last_update_date  DATE) ENGINE=MyISAM;

2. Next, you create an on-insert trigger that changes an input but doesn’t stop the transaction. It also writes to the logger MyISAM table in the scope of the transaction.

CREATE TRIGGER contact_insert
BEFORE INSERT ON contact
FOR EACH ROW
BEGIN
 
  /* Check if last name contains a white space. */
  IF new.last_name REGEXP '^.* .*$' THEN
 
    /* Insert into an MyISAM table, which auto commits in the scope
       of a transaction. */
    INSERT INTO logger
    VALUES ( null
           ,'insert'
           ,'contact'
           , new.last_name
           , REPLACE(new.last_name,' ','-')
           , new.created_by
           , new.creation_date
           , new.last_updated_by
           , new.last_update_date );
 
    /* Replace the name for the INSERT INTO the CONTACT table. */
    SET new.last_name := REPLACE(new.last_name,' ','-');
  END IF;
END;
$$

3. Next, you create an on-update trigger that changes an update while aborting the transaction. It also writes to the logger MyISAM table because its outside the InnoDB scope of a transaction and auto committed on insert.

CREATE TRIGGER contact_update
BEFORE UPDATE ON contact
FOR EACH ROW
BEGIN
 
  /* Check if last name contains a white space. */
  IF new.last_name REGEXP '^.* .*$' THEN
 
    /* Insert into an MyISAM table, which auto commits in the scope
       of a transaction. */
    INSERT INTO logger
    VALUES ( null
           ,'update'
           ,'contact'
           , new.last_name
           , null           
           , old.created_by
           , old.creation_date
           , new.last_updated_by
           , new.last_update_date );
 
    /* Throw an exception to force the business user to see they
       can't update a last name with a white space. */
    SIGNAL SQLSTATE '42000';
  END IF;
END;
$$

4. Next, you create a test case with an INSERT and UPDATE statement that meets the condition of the triggers.

/* Insert a row meeting the trigger condition. */
INSERT INTO contact VALUES
( null, 1001, 1003,'Catherine', null,'Zeta Jones', 1001, UTC_DATE(), 1001, UTC_DATE());
 
/* Update a row meeting the trigger condition. */
UPDATE contact
SET    last_name = 'Zeta Jones'
,      last_updated_by = 1003
,      last_update_date = UTC_DATE()
WHERE  last_name = 'Zeta-Jones';

5. Last, query the logger table. You have a record inserted for both the allowed behavior and the aborted behavior. This means you have the ability to capture material that should never be inserted or updated into a table and who did it by leveraging the who-audit columns of the table.

SELECT * FROM logger;

+-----------+--------------+--------------+-----------------+------------------+------------+---------------+-----------------+------------------+
| logger_id | logger_event | logger_table | logger_instring | logger_outstring | created_by | creation_date | last_updated_by | last_update_date |
+-----------+--------------+--------------+-----------------+------------------+------------+---------------+-----------------+------------------+
|         1 | insert       | contact      | Zeta Jones      | Zeta-Jones       |       1001 | 2013-04-26    |            1001 | 2013-04-26       |
|         2 | update       | contact      | Zeta Jones      | NULL             |       1001 | 2013-04-26    |            1003 | 2013-04-26       |
+-----------+--------------+--------------+-----------------+------------------+------------+---------------+-----------------+------------------+
2 rows in set (0.00 sec)

This effectively delivers in MySQL the equivalent of an autonomous transaction in Oracle. The result from the non-critical trigger records the before and after value, while the results from the critical update trigger only record the before values because the event is aborted by raising an error in the trigger. As always, I hope this helps somebody looking for a solution.

A couple students in one of my classes ran into a problem when competing Java threads tried to insert new rows in a table. They raised an error when they tried the DELAY keyword to avoid the race (collision) condition in an INSERT statement. It was simple to explain to them that the DELAY keyword doesn’t work with an InnoDB table. Any attempt throws the following error:

ERROR 1616 (HY000): DELAYED OPTION NOT supported FOR TABLE 'message'

Important Update: INSERT DELAYED is gone in MySQL 5.6.6 (announcement) and the whole issue comes down to synchronizing threads (some dislike the solution) or using the ON DUPLICATE KEY clause.

They retested their Java application after redefining the target table using the MyISAM engine. They found it worked but that’s a bad fix in Java (a brief Java/MySQL tutorial post). They really needed to synchronize the Java thread (line #22), leave out the DELAY keyword, and manage the table with the InnoDB engine. Here’s the modified Java code (by the way, they named their project VulcanTech if you’re wondering about the packages in the import statement):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

package vulcantech.vth.server.commands;
 
import java.io.IOException;
import java.net.Socket;
 
import vulcantech.vth.server.combeans.MessageBean;
 
public class MessageHandler implements Handler {
 
  @Override
  public void handleIt(Object... args) {
 
    MessageBean message = (MessageBean) args[0];
    Socket sock = (Socket) args[1];
 
    DatabaseConnection dbconnection = new DatabaseConnection();
 
    String update = new String("INSERT INTO message(message_timestamp, sender, recipient, message, checked) VALUES(\'" 
                  + message.getTimeStamp() + "\', \'" + message.getSender() + "\', \'" 
                  + message.getRecipient() + "\', \'" + message.getMessage() + "\', b\'0\')");
 
    synchronized (this) {
      dbconnection.executeUpdate(update);
    }
 
    try {
      sock.getOutputStream().write(1);
    } catch (IOException e) {
      e.printStackTrace();
    }
    dbconnection.close();
  }
}

Hope this helps those who encounter race conditions against MySQL when you’re writing Enterprise Java Beans (EJBs).

Working with a Fedora 16 VM for my students (next term) and found that the MySQL Server’s my.cnf file worked with a Linux socket as opposed to a listener port, and that several configuration options where missing from the file. Here’s the default /etc/my.cnf file after the package installation from the Red Hat site:

[mysqld]
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Without rebuilding the log files, this seemed like the cleanest replacement for the MySQL Server my.cnf for a development instance running on Fedora 16. If you’ve other suggestions, please let me know.

[mysqld]
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
 
# Default directory.
datadir=/var/lib/mysql
 
# The TCP/IP Port the MySQL Server listens on.
# ------------------------------------------------------------
#   Find the machine's IP address with this command run as
#   the root user and use the port number specified in the
#   my.cnf file:
#   [root@localhost ~]# netstat -an | grep 3306
# ------------------------------------------------------------
 
bind-address=nnn.nnn.nnn.nnn
port=3306
 
# The Linux Socket the MySQL Server uses when not using a listener.
# socket=/var/lib/mysql/mysql.sock
 
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
 
# The default storage engine that will be used when creating new tables.
default-storage-engine=INNODB
 
# Set the SQL mode to strict.
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
 
# Set the maximum number of connections.
max_connections=100
 
# Set the number of open tables for all threads.
table_cache=256
 
# Set the maximum size for internal (in-memory) temporary tables.
tmp_table_size=26M
 
# Set how many threads should be kept in a cache for reuse.
thread_cache_size=8
 
# MyISAM configuration.
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=52M
key_buffer_size=36M
read_rnd_buffer_size=256K
sort_buffer_size=256K
 
# InnoDB configuration.
innodb_data_home_dir=/var/lib/mysql
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=25M
innodb_log_file_size=5M
innodb_thread_concurrency=8
 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

As always, I hope this helps somebody.

The merry-go-round is running and Oracle Open World 2010 has begun with MySQL Sunday. The merry-go-round is a fixture at the northwest corner of the Moscone South building, at the intersection shared between the Moscone North building.

The keynote was interesting because Oracle confirmed that they have and will continue to invest in MySQL. The MySQL 5.5 Candidate Release is now available for download. Key features that you’ll note are 200% to 300% improved performance, the InnoDB is now the default engine, backup and recovery are dramatically improved, and the enterprise model is integrated to provide more information about internals and performance.

A little research for the comprehensive new feature list for MySQL 5.5 found a well documented page in the MySQL 5.5 Reference. Examples of things left out of the presentation, not an inclusive list of all features, are: improved operation on the Solaris platform (surprise ;-)), support for semisynchronous replication, support for SQL standard SIGNAL and RESIGNAL statements, support for Unicode character sets, a LOAD XML statement, expanded partitioning options – including the ability to truncate only a partition of a table, and may new command options.

Ronald Bradford gave a good presentation on MySQL idiosyncrasies. I thought spelling out behaviors common to Windows and Mac platforms that differ from Linux and Unix platforms was great. He’s got a new book that he co-authored on MySQL and PHP. I’ve look into the book yet.

I also enjoyed Giuseppe Maxia’s presentation on partitioning. His regular web site is here, but beware if you’ve a slow web connection because the photos delay page loading. 😉

Update on MySQL Sunday

Here are some links provided by Oracle to presentations:

• Edward Screven’s presentation
• Live Webinar: “Delivering Scalability and High Availability with MySQL 5.5 Replication Enhancements

If you’re using referential integrity on MySQL, you can easily run into this error with the InnoDB engine. It frequently seems to occur with an ALTER TABLE statement. It can mean many things but typically it means the data types don’t match between the foreign key and primary key column. Likewise, it can mean one of the column data types disagrees in a multiple-column foreign to multiple-column primary key constraint.

The error by itself isn’t very handy. This is a sample:

ERROR 1005 (HY000): Can't create table 'sampledb.#sql-4a0_2' (errno: 150)

You can try SHOW WARNINGS but you’ll discover more about the error by running the following command as the root superuser:

mysql> SHOW engine innodb STATUS;

It returns a dump of the InnoDB’s activity. You can see it by unfolding the complete log, if you’re interested in the details. The significant part of the log to solve this type of problem is:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
100130 17:16:57 Error IN FOREIGN KEY CONSTRAINT OF TABLE sampledb/#sql-4a0_2:
FOREIGN KEY(member_type)
REFERENCES common_lookup(common_lookup_id):
Cannot find an INDEX IN the referenced TABLE WHERE the
referenced COLUMNS appear AS the FIRST COLUMNS, OR COLUMN types
IN the TABLE AND the referenced TABLE do NOT MATCH FOR CONSTRAINT.

While this is more information, it isn’t necessarily enough to solve the problem when you’re new to MySQL. The first place you should look is whether the data types agree between the foreign key and primary key columns.

The most common variation that I’ve run into is where the primary key column uses a int unsigned data type and the foreign key column uses an int data type. It’s quite nice that the InnoDB Engine stops this cold. Naturally, you fix it by changing the foreign key data type to match the int unsigned data type.

=====================================
100130 17:34:04 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 0 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 11, signal count 11
Mutex spin waits 0, rounds 80, OS waits 2
RW-shared spins 18, OS waits 9; RW-excl spins 1, OS waits 0
------------------------
LATEST FOREIGN KEY ERROR
------------------------
100130 17:16:57 Error in foreign key constraint of table sampledb/#sql-4a0_2:
FOREIGN KEY(member_type)
REFERENCES common_lookup(common_lookup_id):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
------------
TRANSACTIONS
------------
Trx id counter 0 12958
Purge done for trx's n:o < 0 12956 undo n:o < 0 0
History list length 7
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 3924
MySQL thread id 13, query id 800 localhost 127.0.0.1 root
show engine innodb status
---TRANSACTION 0 12957, not started, OS thread id 3276
MySQL thread id 2, query id 797 localhost 127.0.0.1 student
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
136 OS file reads, 1112 OS file writes, 704 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 195193, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 9122680
Log flushed up to   0 9122680
Last checkpoint at  0 9122680
0 pending log writes, 0 pending chkp writes
678 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 58341818; in additional pool allocated 1481088
Dictionary memory allocated 78248
Buffer pool size   3008
Free buffers       2879
Database pages     128
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 126, created 2, written 425
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 3408, state: waiting for server activity
Number of rows inserted 454, updated 6, deleted 0, read 6026
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================