… remove auto_increment from 100G table. No matter if it’s InnoDB or MyISAM, you’d usually ALTER TABLE `huge_table` CHANGE `id` `id` int(6) NOT NULL and then wait hours for table rebuild… CREATE TABLE `huge_table`\G *************************** 1. row *************************** Table: huge_table Create Table: CREATE TABLE `huge_table` ( `id` int(6) NOT NULL, `text` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT…
Post: How innodb_open_files affects performance
November 18, 2009 By 4 Comments
… variable – innodb_open_files which defines how many files Innodb will keep open while working in innodb_file_per_table mode. Unlike MyISAM Innodb does not have to keep open file descriptor when table is open – open table is purely…. So if you create Innodb tables you better store some data in them otherwise there will be a huge waste of space. I…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
Really often in customers’ application we can see a huge tables with varchar/char fields, with small sets of possible values. … NULL, PRIMARY KEY (id), KEY state (state) ) ENGINE=MyISAM; 2) Table with VARCHAR: CREATE TABLE cities_varchar ( id int(10) unsigned NOT NULL… times faster than for MyISAM. This is great example of the case when Innodb is much faster than MyISAM for Read load. The…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… InnoDB tables — don’t use GUIDs. Which brings me to my next point: Use InnoDB Assuming… repairing huge MyISAM tables and taking downtime, I would not use MyISAM for anything but read-only tables when… Especially on huge tables, it lets you scan portions of a table instead of the whole table if …
Post: My Innodb Feature wishes
May 4, 2006 By 30 Comments
… difference in Performace with MyISAM and Innodb is huge for read only workload it is caused by the fact Innodb does not have prefix compression for its indexes, as MyISAM… and promoting real one to unique. Tablespaces. To call “innodb_file_per_table” option tablespaces is a joke. It is better than…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… might be an indication that it’s time to convert MyISAM tables to InnoDB. (But then again, it might not be.) Or maybe… analysis, and also might show me things like huge tables that should be archived, tables that I should be aware of if I…. You might find that there are InnoDB tables whose .frm files exist but have been dropped from InnoDB, for example. (The INFORMATION_SCHEMA…
Post: Performance gotcha of MySQL memory tables
February 1, 2008 By 5 Comments
… second while converting table to MyISAM I get about 600 deletes per second and about 4000 deletes per second for Innodb (with log… need to fetch all values anyway. It however becomes a huge problem when delete (or key update) operation needs to be… surprised to see MyISAM being significantly slower than Innodb – in this case the table is small and fully fits in Innodb Buffer Pool and…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 39 Comments
… with huge temporary tables and otherwise running complex queries. It is usually much better to simply check it. Start MySQL With 10GB Innodb… to bypass cache for your Innodb tables but there are other things you need OS cache for – MyISAM tables (mysql database, temporary etc) will…
Post: High Rate insertion with MySQL and Innodb
January 7, 2011 By 17 Comments
…system it used MyISAM and the system was built using multiple tables. Using multiple…time I worked with Innodb tables… it was a different system with different table structure, not…innodb_buffer_pool_instances=8 was very important. Second thing – Partitioning. Unfortunately MySQL 5.5 leaves the huge…
Comment: MySQL Partitioning - can save you or kill you
June 28, 2012 By 45 Comments
… over MyISAM and InnoDB. TokuDB may shine even with lots of indexes. INDEXes can be a killer of INSERT rates on huge tables. His table…. (XtraDB has a significant fix for smoothing out this operation; InnoDB periodically “stalls”.) In either engine, even without dropping the unused…
