…memory is being used by current MEMORY tables: mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory‘; +——————————-+ | sum(data_length+index…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… that we have traded many point index lookups to one or more index range lookups. This means MySQL can employ many other optimizations… build step is such that the hash table fits in memory. You can read more about the hash join algorithm here. Now let’s… note that the following changes were made on MySQL 5.6 config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on…
Post: Performance gotcha of MySQL memory tables
February 1, 2008 By 5 Comments
… gotcha with MEMORY tables you might know about comes from the fact it is the only MySQL storage engine which defaults to HASH index type… again comes from the fact MEMORY tables use HASH indexes by default. I’ve created rather similar test table: CREATE TABLE `test` ( `id` int(11) NOT…
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
… looks something like this: SELECT * FROM table WHERE deleted = 0 AND group_id IN … cores. The workload was completely in memory in this test. I have compared…mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_conne ction,start_thread,clone P.S Disabling adaptive hash index…
Post: How much memory Innodb locks really take ?
July 13, 2006 By 11 Comments
… `j` (`j`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 mysql> show table status like “sample” \G; *************************** 1. row …Data_length: 100253696 Max_data_length: 0 Index_length: 128974848 Data_free: 0 Auto_… memory: History list length 5 Total number of lock structs in row lock hash table …
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 139 Comments
… indexes. In fact even MySQL optimizer currently does not take it into account. For In memory workload index … 1% of the table. The times for full table scan vs range scan by index: mysql> select count(…MySQL is lack of advanced join methods at this point (the work is on a way) – MySQL can’t do hash…
Post: How well does your table fits in innodb buffer pool ?
December 9, 2010 By 11 Comments
…tables and indexes they are accessing are not in cache, for example being washed away by other queries. MySQL…index fits in memory (fit_pct) For illustration purposes I’ve created one table…tables/indexes…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…memory requirements, the InnoDB versions are only available on 64 bit instances. MySQL…distribution, the test data was hashed over the values in … to 2010′; mysql> use ontime1; Database changed mysql> show table status like ‘… 1616904192 Max_data_length: 0 Index_length: 539279360 Data_free: 4194304…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… the amazing MySQL manual, especially the option and variable reference table. But just…_adaptive_flushingblogpercona.commanual innodb_adaptive_hash_indexblogpercona.commanual innodb_additional_mem_….commanual shared_memoryblogpercona.commanual shared_memory_base_nameblogpercona.commanual show_slave_…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 13 Comments
…table which is some 10 times larger than amount of memory I allocated for buffer pool: CREATE TABLE…hashes mysql> optimize table a; +——–+———-+———-+——————————————————————-+ | Table…
