…Innodb_scan_pages_contiguous | 37864 | | Innodb_scan_pages_jumpy | 574 | +——————————+——-+ 2 rows in set (0.00 sec) and extended…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…_INCREMENT PRIMARY KEY, c FLOAT) ENGINE=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t(c) VALUES (RAND…_index_creation on. Here and in later examples I’m extending the VARCHAR column to trigger table rebuilds without affecting the… TABLE OPTIMIZE TABLE is mapped to ALTER TABLE … ENGINE=InnoDB for InnoDB tables and thus, is just a special case of the…
Post: Optimizing InnoDB for creating 30,000 tables (and nothing else)
December 22, 2011 By 5 Comments
… exciting mysqltest language that will create 30,000 identical tables, insert a row into each of them and then drop them… can set some InnoDB options! I’m going to try the obvious first: innodb-flush-method, sync-frm and innodb-flush-log-at… was an fsync() call when extending the data file, so I tried setting a higher innodb-autoextend-increment and a larger initial…
Post: Impossible - possible, moving InnoDB tables between servers
June 8, 2009 By 9 Comments
… progress in error.log like: InnoDB: import: extended import of test/img_out59 InnoDB: import: 3 indexes are detected. InnoDB: Progress in %: 1 2…-xtradb:patch:innodb_expand_import If you want some internals: I expected the biggest problem with implementation would be merging insert buffer…
Post: Bug#12704861
November 20, 2011 By 1 Comment
…here the full documentation from the release notes: “InnoDB Storage Engine: Data from BLOB columns could be…a BLOB page leads to the tablespace being extended, crash recovery could see BLOB writes to pages… When columns are being made off-page in insert-by-update, invoke btr_mark_freed_leaves(nonfree=…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…optimizer knowing index is UNIQUE the drawback is insert buffer will not be able to be used…you really should worry about performance impact of extending index is when you increase its length dramatically,…”order by desc” indicates a double linked list. Innodb has double linked list – each leaf page …
Post: MySQL File System Fragmentation Benchmarks
March 21, 2008 By 28 Comments
… inserts going to random tables. I used default MySQL settings for MyISAM (table_cache=64) and set innodb_buffer_pool_size=8G innodb_flush_logs_at_trx_commit=2 innodb_log_file_size=256M innodb_flush_method=O_DIRECT for Innodb…: 5157.1389149296sec. Insert performance is close, the difference is perhaps explained by the fact files needed to be constantly extended (meta data…
Post: New patches, new builds
September 5, 2008 By 24 Comments
… | | microslow_innodb.patch | Extended statistics in slow.log | 1.0 | Percona | GPL | | | split_buf_pool_mutex_fixed_optimistic_safe.patch | InnoDB patch to…_merge_and_read.patch | InnoDB patch to control insert buffer and flushing 5.1 release Contains build-in InnoDB plugin, so there is…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
…, PRIMARY KEY (`c2`), KEY `c1` (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=2949071 DEFAULT CHARSET=latin1 1 … query to include the restriction: mysql> explain extended select c1, count(*) from v1 where c1 = … to this connection. When the table is inserted too (see below) the connection_id() function …
Post: Redundant index is not always bad
August 28, 2007 By 7 Comments
…row data from the table. So – we can extend index `state_id_idx` (`state_id`) by two …in first run. (This is for MyISAM, for InnoDB difference will be less. MyISAM uses key-compression…, address) The drawback is worse INSERT performance, here is time to INSERT 1million records: only with state_id KEY…
