… software RAID 10. Now let’s come to the MySQL configuration. MySQL Configuration I used Percona Server 5.5.22-55 for… the table purchases_index which has 3 secondary indexes. Another thing I would like 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… 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…would hit a memory wall. Given that Memory sizes are perhaps 10 times bigger than they…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
…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…_buffer_size/mrr_buffer_size were set to 6M, the query time dropped to ~40s. So the maximum possible benefit is when size of… 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… 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…
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…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
… sec 4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1 MySQL thread id 3, OS thread… page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`t` trx id 72C lock_mode X locks rec… index `age` of table `test`.`t` trx id 72C lock_mode X locks gap before rec If you have lot of gaps locks…
Comment: Finding out largest tables on MySQL Server
April 24, 2012 By 24 Comments
… 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 the table.ibd file on disk? I understand that the size of the table…/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
…_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/drbd…_binlog_size = 100M [client] socket=/data/mysql/mysql.sock When I start the database service that starts smoothly with a low number of connections… so sopresiva connections to the database increases reaching the maximum of connections and I have to re-restart the server. I…
