Have you wanted to compress only certain types of columns in a table while leaving other columns uncompressed? While working on a customer case this week I saw an interesting problem where a table had many heavily utilized TEXT fields with some read queries exceeding 500MB (!!), and stored in a 100GB table. In this [...]
Shard-Query EC2 images available
May 11, 2011 By 2 Comments
Infobright and InnoDB AMI images are now available There are now demonstration AMI images for Shard-Query. Each image comes pre-loaded with the data used in the previous Shard-Query blog post. The data in the each image is split into 20 “shards”. This blog post will refer to an EC2 instances as a node from here [...]
Innodb row size limitation
April 7, 2011 By 12 Comments
I recently worked on a customer case where at seemingly random times, inserts would fail with Innodb error 139. This is a rather simple problem, but due to it’s nature, it may only affect you after you already have a system running in production for a while.
Blob Storage in Innodb
February 9, 2010 By 13 Comments
I’m running in this misconception second time in a week or so, so it is time to blog about it. How blobs are stored in Innodb ? This depends on 3 factors. Blob size; Full row size and Innodb row format. But before we look into how BLOBs are really stored lets see what misconception [...]
Real-Life Use Case for “Barracuda” InnoDB File Format
April 23, 2008 By 11 Comments
In one of his recent posts Vadim already gave some information about possible benefits from using new InnoDB file format but in this post I’d like to share some real-life example how compression in InnoDB plugin could be useful for large warehousing tasks.
Testing InnoDB “Barracuda” format with compression
April 23, 2008 By 3 Comments
New features of InnoDB – compression format and fast index creation sound so promising so I spent some time to research time and sizes on data we have on our production. The schema of one of shards is
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 34 35 36 37 38 39 40 | CREATE TABLE `article87` ( `id` bigint(20) unsigned NOT NULL, `ext_key` varchar(32) NOT NULL, `site_id` int(10) unsigned NOT NULL, `forum_id` int(10) unsigned NOT NULL, `thread_id` varchar(255) CHARACTER SET latin1 NOT NULL, `published` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `crawled` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `subject` varchar(255) NOT NULL, `title` varchar(255) NOT NULL, `url` varchar(255) NOT NULL, `num_links` smallint(6) NOT NULL, `links_in` int(10) unsigned NOT NULL, `cache_author` varchar(255) NOT NULL, `cache_site` varchar(255) DEFAULT NULL, `anchor` varchar(255) NOT NULL, `isthread` tinyint(3) unsigned NOT NULL, `author_id` int(10) unsigned NOT NULL, `inserted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `fromfile` varchar(255) NOT NULL, `language_id` tinyint(3) unsigned NOT NULL, `encoding` varchar(255) NOT NULL, `warning` mediumtext NOT NULL, `is_thread_start` tinyint(3) unsigned NOT NULL, `source` mediumint(8) unsigned NOT NULL, `hash` char(32) NOT NULL, `mod_is` tinyint(3) unsigned NOT NULL DEFAULT '0', `is_adult` tinyint(3) unsigned NOT NULL DEFAULT '0', `bodyuc` mediumtext NOT NULL, PRIMARY KEY (`id`), KEY `ext_key` (`ext_key`), KEY `forum_id` (`forum_id`,`thread_id`,`published`), KEY `site_id` (`site_id`,`published`), KEY `hash` (`hash`), KEY `forum_id_2` (`forum_id`,`is_thread_start`,`published`), KEY `published` (`published`), KEY `inserted` (`inserted`), KEY `forum_id_3` (`forum_id`,`thread_id`,`is_thread_start`), KEY `site_id_2` (`site_id`,`author_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
Heikki Tuuri Innodb answers – Part I
October 26, 2007 By 19 Comments
Its almost a month since I promised Heikki Tuuri to answer Innodb Questions. Heikki is a busy man so I got answers to only some of the questions but as people still poking me about this I decided to publish the answers I have so far. Plus we may get some interesting follow up questions [...]
