…blog post is part two in what is now a continuing series on the Star… ALTER TABLE fast index creation (merge sort). For the MyISAM tests I used a 10GB key buffer. I used ALTER TABLE … symbolic-links=0 innodb_stats_persistent innodb_stats_auto_update=off [mysqld_safe] log-error=/var/log/mysqld.log …
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… spins 3463, OS waits 3163 There are two portions in this section. One is list… localhost root update insert into child values(2,2) Foreign key constraint fails for table `test/child`: … size 889, 2431891 inserts, 2672643 merged recs, 1059730 merges Hash table size 8850487, used cells 2381348, node…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…two tables: mysql> create view used_indexes as (select TABLE_SCHEMA, TABLE_NAME, INDEX_NAME from master_index_stats ) UNION DISTINCT (select TABLE_SCHEMA, TABLE…updates as our schema changes over time: mysql> create view all_indexes as select t.table_schema as TABLE_SCHEMA, t.table_name as TABLE…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… performance you need. Because I’ve built two such systems to manage online ads through Google…merge or hash joins, you’d have other possibilities, but it doesn’t, so you want to build your aggregate tables…replication in a way. When you’re updating big aggregate tables, don’t work with giant chunks …
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
…tree. I’ve updated column to have bunch of long random strings update sbtest … the following picture: Comparing these two graphs we can see few different… IO, which is what file merge process uses. This is important … large tables and need to run ALTER TABLE which rebuilds the table or OPTIMIZE TABLE …
Post: Some little known facts about Innodb Insert Buffer
January 13, 2009 By 12 Comments
…merge is performed it is impossible to check if the value is unique. Insert buffer is allocated in the Innodb system table…merges shows us there was about 3 records merged for each merge operation, meaning insert buffer could in theory reduce IO needed to update…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… Full_join: No Tmp_table: No Tmp_table_on_disk: No # Filesort: No Filesort_on_disk: No Merge_passes: 0 # InnoDB…2.4% 89 0.0022 1.00 0.01 UPDATE wp_options # 7 0x7AEDF19FDD3A33F1 0.1381 1.7% 909…needs to create on-disk tmp tables every time its executed. Those are the two important things that you …
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
… big tables, a secondary index reserves different extents from the clustered index. Q4: Does Innodb ever merges …decide (incorrectly) to swap out MySQL. There are two solutions here. Memlock and O_DIRECT. Memlock …you to answer second portion of the questions. UPDATE: Second Part of the answers is…
Post: Full Text Search Webinar Questions Followup
August 24, 2012 By 5 Comments
… the most current data. You would merge the delta index with the main index… row-by-row updates. But you would still have to update the RT index as…I assume the search would necessarily do table-scans. Jessy B. also asked: Q: … algorithm. It would have taken me two to three weeks to do something …
Comment: MySQL Server Memory Usage
September 1, 2006 By 21 Comments
…tables‘, ’102′ ‘Com_update‘, ’15146253′ ‘Com_update_multi’, ’0′ ‘Connections’, ’263413′ ‘Created_tmp_disk_tables‘, ’0′ ‘Created_tmp_files’, ’4001′ ‘Created_tmp_tables…
