June 19, 2013

Post: MySQL Users Conference - Innodb

May 9, 2007 By 6 Comments

… compression settings. The same utility could be used to advice on the compression page settings by looking at compressed page size… out ALTERing Innodb tables may take a lot of time. Fast Index creation will allow Innodb to build indexes by sorting rather by… to make Innodb IO scheduling more aggressive which is important for serious IO subsystems. Many Innodb IO settings were optimized on systems with…

Filed Under: Events and Announcements Tagged With:

Post: Improved InnoDB fast index creation

November 6, 2011 By 4 Comments

… that when using merge sort, InnoDB has to scan the clustered index only once, even though the actual sorting is done separately for… FOREIGN KEY constraint; mysqldump –innodb-optimize-keys ignores foreign keys because InnoDB requires a full table rebuild on foreign key changes. So adding… dump would actually make the restore slower; mysqldump –innodb-optimize-keys ignores indexes on AUTO_INCREMENT columns, because they must be indexed…

Filed Under: Benchmarks, Insight for DBAs, MySQL

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

February 26, 2013 By 1 Comment

… that would help these sorts of customers realize all of the benefits that have been engineered into InnoDB over the past few… of type BIGINT UNSIGNED NOT NULL with a unique index on it, loading your data, and then creating the FT index… FT index (the same sort of configuration data that can be queried out of the I_S.INNODB_FT_CONFIG table, as…

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

Post: Building Indexes by Sorting In Innodb (AKA Fast Index Creation)

June 19, 2012 By 2 Comments

Innodb can indexes built by sort since Innodb Plugin for MySQL 5.1 which is a lot faster … building index by sort with small buffer – this comes from building index on long character field with very short length, Innodb would use fixed size records for sort space which results…

Filed Under: Benchmarks, Insight for DBAs, MySQL

Post: Innodb Recovery - Is large buffer pool always better ?

July 17, 2007 By 3 Comments

… Innodb crash recovery on box with large amount of memory (64GB) with Innodb Buffer pool configured to be just a bit over 20GB. Innodb… overly smart inserting pages to the flush list – keeping simple sorted linked list, so being forced to scan good portion of… as Innodb is not doing anything else. I however would guess such inefficient flush list handling can be affecting performance on normal…

Filed Under: Insight for DBAs Tagged With:

Post: Heikki Tuuri Innodb answers - Part I

October 26, 2007 By 19 Comments

… create physically sorted indexes with Innodb (with index built by sort) this is when this should be helpful the most. Q3: Does Innodb secondary… thread. Only one read-ahead can be happening on Unix at a time. On Windows, InnoDB uses native async I/O, and can… any specific thoughts here? INNODB was originally written on single core CPUs. HT: Users have reported lots of InnoDB scalability problems from multicore…

Filed Under: Insight for Developers Tagged With: ,

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

… limitation with the InnoDB FTS feature that will be removed in a later version. Now that we’ve sorted this out, let’s drop and rebuild our FT index on the InnoDB table and try the above… that InnoDB is actually behaving more correctly than MyISAM. Imagine if we were searching through newspaper articles or something of that sort…

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

Post: Aligning IO on a hard disk RAID – the Benchmarks

June 9, 2011 By 13 Comments

… have significantly reduced InnoDB buffer pool to make sure the benchmark is IO bound. That said, benchmark was running on a Percona…_commit = 2 innodb_flush_method = O_DIRECT innodb_log_buffer_size = 8M innodb_buffer_pool_size = 128M innodb_log_file_size = 64M innodb_log_files…_size = 8M bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 8M myisam_max_sort_file_size = 10G myisam_repair_threads = 1…

Filed Under: MySQL

Post: Review of MySQL 5.6 Defaults Changes

October 8, 2012 By 10 Comments

… lot better than 5MB. Good to see innodb log re-sizing is made automatic now. innodb_old_blocks_time = 1000 Great. This… exactly the value I’ve been recommending for a while. innodb_stats_on_metadata = off Yet another change I actively recommended max… does not need to be configured. sort_buffer_size = 256K Good choice for very small sorts but more complicated queries will suffer…

Filed Under: Insight for DBAs, MySQL

Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size

June 27, 2012 By 4 Comments

… as initially created only has index on column K which initially contains only …sbtest” \G *************************** 1. row *************************** Name: sbtest Engine: InnoDB Version: 10 Row_format: Compact Rows: …. Creating Index by sort is great Creating index by sort can provide over 20x …

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