June 17, 2013

Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!

December 9, 2010 By 18 Comments

…be a lot faster, especially for large indexes which would get inserts …TABLES on Innodb table to ensure there is not ton of queries starting reading table…ALTER TABLE which requires table rebuild. Dropping all indexes; doing ALTER and when adding them back can be a lot faster than straight ALTER TABLE…

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

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

February 26, 2013 By 1 Comment

…introduced with InnoDB full-text search), that’s largely where … is generated: mysql> alter table dir_test_innodb ADD FULLTEXT KEY (full…INNODB_FT_* tables. In particular, the INNODB_FT_CONFIG table is supposed to “display metadata about the FULLTEXT index and associated processing for an InnoDB table…

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

Post: MySQL Users Conference - Innodb

May 9, 2007 By 6 Comments

… size for Innodb table, while data pages and pages for different indexes may well have very different compression ratio. As Innodb indexes are… days as Innodb user when I found out ALTERing Innodb tables may take a lot of time. Fast Index creation will allow Innodb to build… does which can be multiple orders of magnitude faster for large tables. There is fair number of questions about this feature which…

Filed Under: Events and Announcements Tagged With:

Post: Should you move from MyISAM to Innodb ?

January 12, 2009 By 59 Comments

… handling, concurrency issues, problems dealing with very large number of tables, slow data load and ALTER TABLE and others. Another big one is COUNT… storage engine (typically Innodb) and when use other tables when it really gives substantial gains. I would not switch table to MyISAM because… it can be as much as 10-50 times for Innodb tables in particular for write intensive workloads. Check here for details…

Filed Under: Insight for DBAs Tagged With: , ,

Post: Ultimate MySQL variable and status reference list

January 8, 2011 By 4 Comments

…innodb_status_fileblogpercona.commanual innodb_strict_modeblogpercona.commanual innodb_support_xablogpercona.commanual innodb_sync_spin_loopsblogpercona.commanual innodb_table_locksblogpercona.commanual innodb_thread_concurrencyblogpercona.commanual innodb…

Filed Under: Insight for DBAs, MySQL

Post: Working with large data sets in MySQL

July 5, 2007 By 12 Comments

…database is already loaded. So operating wit such large databases you need to be patient and …run ALTER TABLE because table will stay locked for too long you would need to do careful process of ALTERing table…because for MyISAM they will lock tables for very long time and Innodb can get too many old…

Filed Under: Insight for Developers Tagged With: ,

Post: Implications of Metadata Locking Changes in MySQL 5.5

February 1, 2013 By 9 Comments

…covered well, and since there are still a large number of MySQL 5.0 and 5….the table metadata, a table ALTER that alters a big table can keep executing without any hindrance, copying rows from the table… ENGINE=InnoDB; However, I feel that the CREATE TABLE should fail in such a case when the table …

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

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

June 27, 2012 By 4 Comments

… and index size: mysql> show table status like “sbtest” \G *************************** 1. row *************************** Name: sbtest Engine: InnoDB Version: 10 Row_format:…: If you’re having large tables and need to run ALTER TABLE which rebuilds the table or OPTIMIZE TABLE do not forget to enable …

Filed Under: Insight for DBAs, MySQL

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

June 19, 2012 By 2 Comments

… sort since Innodb Plugin for MySQL 5.1 which is a lot faster than building them through insertion, especially for tables much larger than memory and large uncorrelated indexes you might be looking at 10x difference or more. Yet for some reason Innodb… expect (courtesy of Alexey Kopytov), using 1Mil rows Sysbench table. Buffer Length | alter table sbtest add key(c) 1MB 34 sec 8MB 26…

Filed Under: Benchmarks, Insight for DBAs, MySQL

Post: 10+ Ways to Crash or Overload MySQL

November 13, 2007 By 20 Comments

…tables would be repaired at the same time. What if user uses all 100 of his allowed connections to ALTER 100 different tables… Innodb Table Cache Leak – Innodb never shrinks its internal table cache (data dictionary) so by creating and accessing large amount of Innodb tables you can allocate large …

Filed Under: Insight for DBAs Tagged With:
« Older Posts