…MySQL configuration: [mysqld] gdb innodb_file_per_table = true innodb_data_file_path = ibdata1:100M:autoextend innodb_flush_method = O_DIRECT innodb…myisam_sort_buffer_size = 8M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam…
Post: Innodb performance gotcha w Larger queries.
August 28, 2009 By 7 Comments
… nasty going on on Innodb level, so I tried running the test with MyISAM tables instead. The process … in a batch vs 40 seconds for single row statements. So with single statement MyISAM was about 10% ….0. At row0sel.c:row_search_for_mysql() “PHASE 3:”, InnoDB judge whether the SQL is “SELECT” or not…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… covering index) MySQL gets a “row pointer” which can be primary key value (for Innodb tables) physical file offset (for MyISAM tables) or… `i1` (`i1`), KEY `i2` (`i2`), KEY `combined` (`i1`,`i2`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I made columns i1 and i2 independent… seen before. What if we leave MySQL no choice but only to use combined index: mysql [localhost] {msandbox} (test) > explain select avg…
Comment: Unused indexes by single query
September 14, 2008 By 14 Comments
… for InnoDB and MyISAM: To insert 1 million records in InnoDB with 1 index took 80 sec, with 2 indexes – 136 seconds. For MyISAM the difference is bigger: 72 sec vs 470…, we ported this patch for BlueHost, who also uses patched MySQL in real environment and we usually recommend our release to…
Post: Full Text Search Webinar Questions Followup
August 24, 2012 By 5 Comments
… InnoDB FT index, I made sure to increase innodb_buffer_pool_size to 50% of my RAM, and then when I tested MyISAM… tried to create an InnoDB FT index before declaring the `FTS_DOC_ID` primary key column properly, MySQL crashed with an out… the blog post where I announced the webinar: We evaluated MySQL vs Solr. I was able to index all the data from…
Post: MySQL and PostgreSQL SpecJAppServer benchmark results
July 25, 2007 By 13 Comments
… 5% difference in CPU frequency alone (2600Mhz vs 2800Mhz) and it well may be more …The other big issue is using MySQL 5.0.27 which has Innodb scalability issues, which were … of MySQL sweet spots come from. MySQL performance gains usually come from MySQL unique features – non transactional MyISAM, …
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
…, jam packed rows – quite possible scenario for MyISAM tables. Now if we take the same…The times for full table scan vs range scan by index: mysql> select count(pad) from large; +————+ | count(pad) |… scanned. There are also clustered keys in Innodb which combine index access with data access,…
Post: Test Drive of Solid
September 22, 2006 By 5 Comments
… test2 values (1,’Mysql‘),(2,’Solid’),(3,’MyISAM‘); mysql> begin; mysql> update test2 set names=’mysql‘ where id=1; Session 2: mysql> begin; mysql> update test2 set… are InnoDB sessions: Test 1: Deadlock detection (deadlock is correctly detected): insert into test2 values (1,’Mysql‘),(2,’Solid’),(3,’MyISAM‘); Session1: mysql> begin…
Post: How many partitions can you have ?
December 5, 2009 By 19 Comments
… had an interesting case recently. The customer dealing with large MySQL data warehouse had the table which was had data merged…(10) unsigned NOT NULL, PRIMARY KEY (`id`), KEY(c) ) ENGINE=InnoDB PARTITION BY RANGE(id) ( PARTITION p100000 VALUES LESS THAN(100001…. Both MyISAM and Innodb are affected by this issue. The Update path for MyISAM without indexes took 10 seconds for 1 partition vs 52…
Post: How much overhead DRDB could cause ?
June 2, 2008 By 4 Comments
… which suffer overhead of DRBD. If you’re using MySQL with Innodb (and running MyISAM with DRBD makes little sense anyway) you will… system and configuration) more synchronous IO operations – MySQL binary logs are not preallocated as Innodb logs are which means both Data AND… overhead besides network IO we’re speaking about 300 microseconds vs 5000 microseconds and DRBD overhead can be well below 10…
