You searched for innodb mysqldump slow - Page 2 of 2

InnoDB: look after fragmentation

November 5, 2009 By Vadim Tkachenko 12 Comments

One problem made me puzzled for couple hours, but it was really interesting to figure out what’s going on. So let me introduce problem at first. The table is

CREATE TABLE `c` (
  `tracker_id` int(10) unsigned NOT NULL,
  `username` char(20) character set latin1 collate latin1_bin NOT NULL,
  `time_id` date NOT NULL,
  `block_id` int(10) unsigned default NULL,
  PRIMARY KEY  (`tracker_id`,`username`,`time_id`),
  KEY `block_id` (`block_id`)
) ENGINE=InnoDB

CREATE TABLE `c` (

`tracker_id` int(10) unsigned NOT NULL,

`username` char(20) character set latin1 collate latin1_bin NOT NULL,

`time_id` date NOT NULL,

`block_id` int(10) unsigned default NULL,

PRIMARY KEY (`tracker_id`,`username`,`time_id`),

KEY `block_id` (`block_id`)

) ENGINE=InnoDB

Table has 11864696 rows and takes Data_length: 698,351,616 bytes on disk The problem is that after restoring table from mysqldump, the query that scans data [...]

Filed Under: Insight for DBAs, MySQL Tagged With: InnoDB

Detailed review of Tokutek storage engine

April 28, 2009 By Vadim Tkachenko 21 Comments

(Note: Review was done as part of our consulting practice, but is totally independent and fully reflects our opinion) I had a chance to take look TokuDB (the name of the Tokutek storage engine), and run some benchmarks. Tuning of TokuDB is much easier than InnoDB, there only few parameters to change, and actually out-of-box [...]

Filed Under: Uncategorized Tagged With: Storage Engine

Should you move from MyISAM to Innodb ?

January 12, 2009 By Peter Zaitsev 59 Comments

There is significant portion of customers which are still using MyISAM when they come to us, so one of the big questions is when it is feasible to move to Innodb and when staying on MyISAM is preferred ? I generally prefer to see Innodb as the main storage engine because it makes life much [...]

Filed Under: Insight for DBAs Tagged With: InnoDB, MyISAM, Production

Living with backups

November 6, 2008 By Maciej Dobrzanski 28 Comments

Everyone does backups. Usually itâ€™s some nightly batch job that just dumps all MySQL tables into a text file or ordinarily copies the binary files from the data directory to a safe location. Obviously both ways involve much more complex operations than it would seem by my last sentence, but it is not important right [...]

Filed Under: Insight for DBAs Tagged With: Ideas, Tips, Tools

How would you compress your MySQL Backup

June 5, 2008 By Peter Zaitsev 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 [...]

Filed Under: Insight for DBAs Tagged With: Production

Testing InnoDB “Barracuda” format with compression

April 23, 2008 By Vadim Tkachenko 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

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;

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;

Filed Under: Insight for DBAs Tagged With: InnoDB, Production

Updated msl (microslow) patch, installation walk-through!

April 20, 2008 By Maciej Dobrzanski 17 Comments

For a couple of months there have been no updates to our msl patch, however recently I managed some time to change this. The functionality was extended a little bit and whatâ€™s even more important the patch is available for all the recent MySQL releases. To remind anyone who has not yet come across this [...]

Filed Under: Percona Software Tagged With: Patches

Predicting how long data load would take

May 24, 2007 By Peter Zaitsev 14 Comments

I had this question asked many times during last week, and there is really no easy answer. There are just way too many variables to consider especially while loading large schemas with a lot of different table structures. So which variables affect the load speed: Table Structure This one is tricky. The shorter rows you [...]

Filed Under: Insight for DBAs Tagged With: Production, Tips

Performance impact of complex queries

January 17, 2007 By Peter Zaitsev 11 Comments

What is often underestimated is impact of MySQL Performance by complex queries on large data sets(ie some large aggregate queries) and batch jobs. It is not rare to see queries which were taking milliseconds to stall for few seconds, especially in certain OS configurations, and on low profile servers (ie having only one disk drive) [...]

Filed Under: Insight for Developers Tagged With: Production

Database problems in MySQL/PHP Applications

August 11, 2006 By Peter Zaitsev 13 Comments

Article about database design problems is being discussed by Kristian. Both article itself and responce cause mixed feellings so I decided it is worth commenting: 1. Using mysql_* functions directly This is probably bad but I do not like solutions proposed by original article ether. PEAR is slow as well as other complex conectors. I [...]

Filed Under: Insight for Developers Tagged With: LAMP, Production, Tips

InnoDB: look after fragmentation

Detailed review of Tokutek storage engine

Should you move from MyISAM to Innodb ?

Living with backups

How would you compress your MySQL Backup

Testing InnoDB “Barracuda” format with compression

Updated msl (microslow) patch, installation walk-through!

Predicting how long data load would take

Performance impact of complex queries

Database problems in MySQL/PHP Applications

Search

Top Searches

Percona Webinars

ABOUT PERCONA

Percona MySQL Training

Percona Careers

SOFTWARE FROM PERCONA

SERVICES FOR MYSQL USERS

ABOUT THIS BLOG AND PERCONA