It is pretty understood the tables which have long rows tend to be slower than tables with short rows. I was interested to check if the row length is the only thing what matters or if number of columns we have to work with also have an important role. I was interested in peak row processing speed so I looked at full table scan in case data fits in OS cache completely. I created 3 tables - First containing single tinyint column which is almost shortest type possible (CHAR(0) could be taking less space), table with 1 tinyint column and char(99) column and table with 100 tinyint columns. The former two tables have the same row length but have number of column different 50 times. Finally I have created 4th table which is also 100 columns but one of them is VARCHAR causes raw format to be dynamic.

More specially:

PLAIN TEXT SQL:

1. CREATE TABLE `t1` (
…

This Thursday (October 1st, 14:00 UTC), Calvin Sun will give a session on InnoDB Internals: InnoDB File Formats and Source Code Structure. The InnoDB storage engine provides transactions, row-level locking, and automatic crash recovery. Its on-disk files play a pivotal role for those key features. This presentation describes how on-disk files are structured, how compressed tables are organized, and how long variable-length columns are stored. As we develop new features, it is inevitable for file format changes. We will explain how the new file format management works in the InnoDB Plugin.This talk also covers InnoDB source code structure.

For MySQL University sessions, point your browser …

This Thursday (October 1st, 14:00 UTC), Calvin Sun will give a session on InnoDB Internals: InnoDB File Formats and Source Code Structure. The InnoDB storage engine provides transactions, row-level locking, and automatic crash recovery. Its on-disk files play a pivotal role for those key features. This presentation describes how on-disk files are structured, how compressed tables are organized, and how long variable-length columns are stored. As we develop new features, it is inevitable for file format changes. We will explain how the new file format management works in the InnoDB Plugin.This talk also covers InnoDB source code structure.

For MySQL University sessions, point your browser …

I spent Friday examining the systems for a more traditional consulting gig (in case you did not know, Pythian has offered more traditional consulting and emergency services in addition to our remote DBA offering for our entire 12 year history). It is a familiar story to many people — the database performance was unacceptable.

The company had a few log tables that had huge amounts of inserts and foreign keys, so they used InnoDB. Unfortunately, they also used a GUID as a primary key (which is getting more and more common these days, but long primary keys with InnoDB slow down INSERT, UPDATE and DELETE commands a great deal) — varchar(32) and utf8.

That’s right — their primary key for many of these tables was 96 bytes long (32 characters * 3 bytes per character), and as an InnoDB table, the primary key is clustered with …

This is a very old draft, from early 2007 in fact. At that time I started to look into something interesting with the index cardinality statistics reported by InnoDB tables. The cardinality varies because it's derived from estimates, and I know a decent amount about that. The interesting thing I wanted to look into was why the cardinality varies in a particular pattern.

Here I'll grab a bunch of cardinality estimates from sakila.film on MySQL 5.0.45 and put them into a file:

PLAIN TEXT CODE:

1. baron@kanga:~$ while true; do mysql sakila -N -e 'show index from film' | head -n 2 | tail -n 1 | awk '{print $7}'; done> sizes

After a while I cancel it and then sort and aggregate them with counts:

PLAIN TEXT CODE:

1. baron@kanga:~$ sort sizes | uniq -c …

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 footprint.

What is even more …

The MySQL Innodb storage engine provides ACID compliance, the ‘I’ being isolation. There are four states defined in MySQL with the tx_isolation system variable, READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ and SERIALIZABLE.

Generally MySQL installations do not modify the default value of tx_isolation = REPEATABLE-READ, however I have seen with a number of clients the default value has been changed to READ-COMMITTED. When I attempt to identify the reason why, I have always received the same reason. Oracle uses a default transaction isolation of READ-COMMITTED. See how Oracle Manages Data …

This Thursday (September 24th, 14:00 UTC), Heikki Tuuri, the father of InnoDB, will give a session on Concurrency Control: How It Really Works. He'll describe how InnoDB manages concurrency control, so that the system protects data integrity. Beginning with the basics of transaction management, Heikki will include a discussion of the ACID (atomicity, consistency, isolation, and durability) properties, and explain various transaction modes, locking, deadlocks, and more advanced topics such as the impact of next-key (gap) locking, referential integrity, XA (distributed transaction management) support, and more. While the discussion will focus on the InnoDB implementation, many of the concepts presented apply to other database systems and storage engines.

For MySQL …

This Thursday (September 24th, 14:00 UTC), Heikki Tuuri, the father of InnoDB, will give a session on Concurrency Control: How It Really Works. He'll describe how InnoDB manages concurrency control, so that the system protects data integrity. Beginning with the basics of transaction management, Heikki will include a discussion of the ACID (atomicity, consistency, isolation, and durability) properties, and explain various transaction modes, locking, deadlocks, and more advanced topics such as the impact of next-key (gap) locking, referential integrity, XA (distributed transaction management) support, and more. While the discussion will focus on the InnoDB implementation, many of the concepts presented apply to other database systems and storage engines.

For MySQL …

This Thursday (September 24th, 14:00 UTC), Heikki Tuuri, the father of InnoDB, will give a session on Concurrency Control: How It Really Works. He'll describe how InnoDB manages concurrency control, so that the system protects data integrity. Beginning with the basics of transaction management, Heikki will include a discussion of the ACID (atomicity, consistency, isolation, and durability) properties, and explain various transaction modes, locking, deadlocks, and more advanced topics such as the impact of next-key (gap) locking, referential integrity, XA (distributed transaction management) support, and more. While the discussion will focus on the InnoDB implementation, many of the concepts presented apply to other database systems and storage engines.

For MySQL …