… software RAID 10. Now let’s come to the MySQL configuration. MySQL Configuration I used Percona Server 5.5.22-55 for… which has no secondary index and only a primary index, and against the table purchases_index which has 3 secondary indexes. Another thing I… now there are pros and cons to using secondary indexes. While secondary indexes cause read performance to improve, but they have an…
Comment: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 11 Comments
… is to measure the cost of index maintenance. This experiment isn’t measuring the cost of index maintenance. Transaction-id is auto… disk seek.) I claim that this schema is not properly indexed. Here’s a theory that seems to explain the performance… python version that Mark wrote http://bazaar.launchpad.net/~mdcallag/mysql-patch/mytools/annotate/head%3A/bench/ibench/iibench.py) is…
Comment: Multi Column indexes vs Index Merge
May 10, 2012 By 30 Comments
… same mysql prod instance . The first SQL query executed in 0.03 secs whereas the second SQL query when using index merge executes in 3.8 secs. I dont understand why MySQL uses index merge after a couple of mins when previously it was using a single index. I would greatly…
Comment: InnoDB's gap locks
May 7, 2012 By 14 Comments
… 1207 sec, process no 2909, OS thread id 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 31929, query id 484616 localhost root update insert… space id 57 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`t` trx id 0 27638 lock…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… current MEMORY tables: mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory’; +——————————-+ | sum(data_length+index_length) | +——————————-+ | 126984… tables too: mysql> select sum(data_length+index_length) from information_schema.global_temporary_tables where engine=’memory’; +——————————-+ | sum(data_length+index_length…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5… is, and what is it aimed at. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the part on which range condition is applied…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… note that the following changes were made in the MySQL config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer… on IO bound workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read_rnd_bufer_size=4M… traditional index lookup (for non-index-only columns) involves, reading an index record, and then using the PK column value in the index record…
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… note that the following changes were made on MySQL 5.6 config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on… a look at the status counters. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ join_buffer_size=6M & read…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
… the same value twice. To accomplish that, InnoDB locks all index records found by the WHERE clause with an exclusive lock… space id 19 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`t` trx id 72C lock_mode…-set.html. Conclusion MySQL uses REPEATABLE READ as the default isolation level so it needs to lock the index records and the…
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
… BY OPTIMIZATION” section in MySQL manual but could not find a reason why MySQL does not use index in the following scenario: CREATE… if there is a rule that MySQL uses to determine whether to use index or filesort? On mysql manual I notice that the above query satisfies all conditions required for MySQL to use an index but…
