… the MySQL configuration. MySQL Configuration I used Percona Server 5.5.22-55 for the tests. Following is the configuration that I used: ## InnoDB… to share is that, the size of the table without secondary indexes is 56G while the size of the table with secondary indexes is 181G. Now…
Comment: InnoDB's gap locks
May 7, 2012 By 15 Comments
… restarting transaction Here I tried to insert a value out of [21,30],but it came out my insert still be… 1100101952 inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1216, 1 row lock(s) MySQL thread id… page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`t` trx id 0 27638 lock_mode X insert…
Comment: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 11 Comments
…table fits in main memory. Each row is on the order of 30 bytes in the primary table…sizes…of the advantages of the newer version of iiBench (the python version that Mark wrote http://bazaar.launchpad.net/~mdcallag/mysql…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… it uses MySQL to identify potential causes. Is it working with large blobs ? Using user variables ? Prepared Statements ? memory tables ? In a lot of cases… tables you can create both as permanent and temporary. There is a max_heap_table_size variable which allows you to limit size of MEMORY tables…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… a hash table will be created based on the columns of table t1 that are two be used to join rows with table t2. This step is called the build step. After the hash table… counters. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ join_buffer_size=6M & read_rnd_buffer_size=6M MariaDB 5.5…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… is the combined size of the secondary key tuples fetched, and N is the buffer size. In MySQL 5.6 the buffer size used by MRR… had to be performed. As in the table above you can with default buffer size of 256K, MariaDB 5.5 shows that Handler… lower by a couple of seconds when buffer size of 4M is used. Another interesting thing to note is that MySQL 5.6 and MariaDB…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take… the part on which range condition is applied cannot be used for filtering records. For example, suppose you have a key…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
…4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1 MySQL thread id 3, …`age` of table `test`.`t` trx id 72C lock_mode X locks gap before rec If you have lot of …good source of information: http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE …
Comment: Finding out largest tables on MySQL Server
April 24, 2012 By 24 Comments
… do the results of these queries compare to the size of the tables on disk? For example, using file_per_table, if the total_size column equals 1GB, how will this relate to the actual size of…/lib/mysql/databasename # ls -lh tablename* -rw-rw—- 1 mysql mysql 8.7K 2010-08-05 16:31 tablename.frm -rw-rw—- 1 mysql mysql…
Comment: Too many connections? No problem!
March 15, 2012 By 28 Comments
…using-indexes query_cache_type=1 query_cache_size=32M thread_cache_size=30 table_cache=4096 join_buffer_size=6M key_buffer_size=12M server-id = 1 log-bin=/data/mysql… reaching the maximum of connections and I have…
