INNODB and strings

mysql> CREATE TABLE innodb_string_test (h varchar(512) ) TYPE=INNODB;
Query OK, 0 rows affected, 2 warnings (0.55 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------------------------+
| Level   | Code | Message                                                                  |
+---------+------+--------------------------------------------------------------------------+
| Warning | 1246 | Converting column 'h' from CHAR to TEXT                                  |
| Warning | 1287 | 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead |
+---------+------+--------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Technically innodb supports defining varchar's greater then 255 characters (utf8 are double bytes so don't assume that 255 characters mean bytes-FYI).

MySQL the server doesn't allow this.


INNODB as of mysql-5.0.3 store stings in COMPACT format. This increases CPU usage slightly but saves nearly double the diskspace, thus given an application twice the memory. (Smaller data, more of it fits in memory).

To turn this feature off, define table ROW_FORMAT=REDUNDENT.

For more details on the Physical structure of strings in INNODB go here

In summary: use varchar over char.



[edited for correctness - thanks Ken Jacobs]

Looking at this slide from INNOBASE.com - in 5.1 they will start using a COMPRESSED format based off of zlib. Here are some numbers and details:

mysqluc2007

	Uncompressed	Compressed
File Size	2.8GB	1.4 GB
Insert/sec	1300	1000
CPU Usage	5%-50%	15%-50%

Unique strings in a text field

So, I want to reduce data usage of a text field, by storing unique strings separated by a delimiter. So to do so I came up with this:

Given a table

CREATE TABLE `hmm` (
  `a` int(10) unsigned NOT NULL default '0',
  `b` text NOT NULL,
  `c` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

INSERT INTO hmm VALUES (1, 'hmm8',1) ON DUPLICATE KEY UPDATE b = IF(FIND_IN_SET('hmm8', b)>0, b, CONCAT_WS(',',b, 'hmm8')), c = c+1;

SELECT * FROM hmm;
+---+--------------------------+---+
| a | b                        | c |
+---+--------------------------+---+
| 1 | hmm4,hmm5,hmm6,hmm7,hmm8 | 7 |
+---+--------------------------+---+

Let's break apart the INSERT statement, if column 'a' produces a duplicate ON DUPLICATE KEY UPDATE will issue an IF statement

FIND_IN_SET will return the position of the found string. The logic of IF say if FIND_IN_SET is is greater then 0 i.e. the string was found, update column b with b itself (do nothing) else concat the column with the column contents and the new string separated by a comma. Additionally in all cases increment column c by 1.

Why do it this way? Well I removed the need to do a SELECT and provided a solution to store distinct text values instead of the raw value, thus saving space and resources on network transfers etc.

A little quick statement that might be useful for others.

Multi-Master Replication, looking over the code-base

I'm running into a situation, where I need real multi-master replication, yet as we all know this doesn't exist (yet) in mysql.

So, as a result I'm investigating how I could implement multi-master replication. Fortunately mySQL has documented some of the source code and hosts this documentation online.

http://dev.mysql.com/sources/doxygen/mysql-5.1/slave_8cc-source.html

There are already references and hooks for multi-master replication in slave.cc

 /*
      TODO: replace the line below with
      list_walk(&master_list, (list_walk_action)end_slave_on_walk,0);
      once multi-master code is ready.
 */

Of all the ideas that I have batted around, I've come to a common conclusion, modify mysql source to enable multi-master replication or make an external daemon that would essentially do the same thing.

In both cases here are my requirements.


• It would need to read multiple replication events


• It would need to log these multiple replication events


• It would need a command interface to dynamically add,remove,change hosts

I could either build a method for pushing replication events to a list of hosts, or build a subscribe method which I'm leaning towards.

INNODB Disk setup and mount options

Assumptions:

RHEL
x86_64
EXT3
RAID

What Raid to use?
RAID-10

Why?
It's faster. RAID-5 offers more disk space but the parity bit messes things up, unless you have some uber hardware-raid card that caches that operation. Personally I am not a fan.


Stripe Size:
128K - this is really good for INNODB, you'll see a huge boost in responsiveness by making your Stripe Size 128K. I had a 64K stripe size, and I was blown away by the improvement of 128K


Mount options:

mkfs.ext3 -T largefile | mkfs.ext3 -T largefile4

Unless your going to have millions of files, this is a good option.


Make sure /etc/fstab mounts the mysql partition or the data that mysql resides on with noatime.

atime is accesstime: this is a huge boost in performance, tracking each time your ibdata file was last access is worthless, so don't do it.



From other benchmarks that I ran, I think that IBM's open source File system may be better then ext3 for certain workloads. Once I have more information I will post it here.

TO COUNT(*) or NOT TO COUNT(*)

Counts, we all love to show counts in our applications. Under a high traffic website, it's visually appealing to show a big number. But the cost of generating that big number realtime can be very expensive on our mySQL backend.

For example:

$sql = SELECT COUNT(*) FROM VERY_BIG_TABLE WHERE key_part1=NUM AND no_key IN (1,2,3) AND key_part2=NUM

Would you think this is fast? In a dev environment yes, but this query has to get the exact row count that meets the requirements of the WHERE clause.

In many cases we use a count to solve a BOOLEAN term in our code, for example

$count_result = db_fetch($sql)

if ($count_result) {
   // do something
} else {
   $SHOW_ERROR = 1;
}

Don't use a count for this purpose. Just do something like

$sql = SELECT PRIMARY_KEY FROM VERY_BIG_TABLE WHERE key_part1=NUM AND no_key IN (1,2,3) AND key_part2=NUM LIMIT 1;

This will tell mysql to return the 1st row found and that's it. In comparison this is about 10 times faster then doing a COUNT(*) under high concurrency.


But what if you need the actual number of rows:

Well, I suggest that what ever triggers an INSERT/DELETE/UPDATE into VERY_BIG_TABLE, do the count(*) at the end of the query and store the result in a meta table or summary table that is small and queried fast.

The draw back is, this makes your application a bit slower on writes, but if you do a lot of reads like most sites do, then caching the count will be a huge win, and this cache account tied to a transaction, makes the count extremely accurate.