June 19, 2013

Comment: Benchmarking Percona Server TokuDB vs InnoDB

May 9, 2013 By 32 Comments

… after the other. This could not be related to primary key worth distribution, memory or CPU as repeating the same test… we are facing more and more big data challenges where innoDB and TokuDB will have to be compare with LevelDB at…

Filed Under: Benchmarks, MySQL, Percona Server Tagged With: , , ,

Post: Testing InnoDB "Barracuda" format with compression

April 23, 2008 By 3 Comments

…`), KEY `inserted` (`inserted`), KEY `forum_id_3` (`forum_id`,`thread_id`,`is_thread_start`), KEY `site_id_2` (`site_id`,`author_id`) ) ENGINE=InnoDB… compressed field, doing compress()/uncompress() to store/restore. To test InnoDB compression I use only uncompressed text, though I tested compression… InnoDB compression table I use ENGINE=InnoDB KEY_BLOCK_SIZE=8 and to test fast index creation I create table only with primary key…

Filed Under: Insight for DBAs Tagged With: ,

Post: InnoDB: look after fragmentation

November 5, 2009 By 12 Comments

…(10) unsigned default NULL, PRIMARY KEY (`tracker_id`,`username`,`time_id`), KEY `block_id` (`block_id`) ) ENGINE=InnoDB Table has 11864696 rows and… secondary key is much more likely than by primary key, and you cannot really control it (tough it is possible in XtraDB / InnoDB… records by secondary key To check if you query affected by fragmentation you can use Innodb_scan_pages_contiguous ; Innodb_scan_pages_jumpy…

Filed Under: Insight for DBAs, MySQL Tagged With:

Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1

January 8, 2007 By 117 Comments

… MyISAM / InnoDB: libexec/mysqld –no-defaults –user=root –key-buffer-size=1500M –innodb-buffer-pool-size=1500M –innodb-log-file-size=100M –innodb-thread-concurrency… accessed column is stored in key. MyISAM and InnoDB handle this case and retrive the value only from key. InnoDB is better by 25…

Filed Under: Benchmarks Tagged With: ,

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

…> alter table dir_test_innodb ADD FULLTEXT KEY (full_name, details), ADD FULLTEXT KEY (details); ERROR 1795 (HY000): InnoDB presently supports one FULLTEXT index… innodb_ft_aux_table=’test/dir_test_innodb‘; mysql> set global innodb_optimize_fulltext_only=1; mysql> select * from information_schema.innodb_ft_config; +—————————+——–+ | KEY…

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

Post: Improved InnoDB fast index creation

November 6, 2011 By 4 Comments

… keys as if expand_fast_index_creation is OFF to avoid dropping keys that are part of a FOREIGN KEY constraint; mysqldump –innodb-optimize-keys ignores foreign keys because InnoDB requires a full table rebuild on foreign key…

Filed Under: Benchmarks, Insight for DBAs, MySQL

Post: ANALYZE: MyISAM vs Innodb

September 3, 2008 By 9 Comments

… Duplicates: 0 Warnings: 0 mysql> show index from antest_innodb; +—————+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality…, “c” key prefix cardinality is underestimated 5 times and the combined (c,i) key cardinality is overestimated 2 times. So Innodb stats are…

Filed Under: Insight for DBAs Tagged With: ,

Post: Heikki Tuuri Innodb answers - Part I

October 26, 2007 By 19 Comments

… Innodb secondary Indexes trees are allocated in two extents same as primary key tree or stored in the same extent ? HT: An InnoDB… may not see a “free space” in Innodb tablespace to grow significantly. Q6: Does Innodb policy replacement algorithm takes into account page… different CPUs. Q25: In InnoDB, the referenced columns of a foreign key constraint need not form a primary key or a unique constraint…

Filed Under: Insight for Developers Tagged With: ,

Post: Hijacking Innodb Foreign Keys

January 2, 2012 By 2 Comments

… readers ! Now back to HardCore MySQL business – foreign Keys. MySQL supported Foreign Keys for Innodb for many years, yet rudimentary support initially added… key violation error. So Innodb Foreign Keys are pretty good at keeping you hostage. Do you have any better idea how to Hijack innodb Foreign Keys or…

Filed Under: Insight for DBAs, MySQL

Post: MySQL Optimizer and Innodb Primary Key

October 3, 2006 By 9 Comments

… id from innodb where a=3 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: innodb type: ref possible_keys: a key: a key_len… innodb where a=3 order by id \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: innodb type: ref possible_keys: a key: a key…

Filed Under: Insight for DBAs Tagged With: ,
« Older Posts
Newer Posts »