One question which comes up very often is when one should use SAN with MySQL, which is especially popular among people got used to Oracle or other Enterprise database systems which are quite commonly deployed on SAN. My question in such case is always what exactly are you trying to get by using SAN ?
How would you compress your MySQL Backup
June 5, 2008 By 29 Comments
Backing up MySQL Database most people compress them – which can make a good sense in terms of backup and recovery speed as well as space needed or be a serious bottleneck depending on circumstances and approach used. First I should mention this question mainly arises for medium and large size databases – for databases [...]
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; |
Idea: Couple of more string types
April 18, 2008 By 12 Comments
MySQL has a lot of string data types – CHAR, VARCHAR, BLOB, TEXT, ENUM and bunch of variants such as VARBINARY but I think it is not enough I would also like to see type HEXCHAR which would be able to store hex strings, such as those returned as MD5() and SHA1() efficiently. With little [...]
MySQL Blob Compression performance benefits
January 11, 2008 By 17 Comments
When you’re storing text of significant size in the table it often makes sense to keep it compressed. Unfortunately MySQL does not provide compressed BLOB/TEXT columns (I would really love to have COMPRESSED attribute for the BLOB/TEXT columns which would make them transparently compressed) but you well can do it yourself by using COMPRESS/UNCOMPRESS functions [...]
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 [...]
MySQL Users Conference – Innodb
May 9, 2007 By 6 Comments
It might look like it is too late to write about stuff happened at Users Conference but I’m just starting find bits of time from processing accumulated backlog. The Theme of this Users Conference was surely Storage Engines both looking at number of third party storage engine presented, main marketing message – Storage Engine partnership [...]
To pack or not to pack – MyISAM Key compression
May 13, 2006 By 4 Comments
MyISAM storage engine has key compression which makes its indexes much smaller, allowing better fit in caches and so improving performance dramatically. Actually packed indexes not a bit longer rows is frequent reason of MyISAM performing better than Innodb. In this article I’ll get in a bit more details about packed keys and performance implications [...]
