… Benchmark. In my previous blog post I compared MySQL 5.5.30 to MySQL 5.6.10, both with default settings using… the hot run, as hash indexes are faster than a b-tree index. Also accessing pages from the buffer pool should be… [mysqld] datadir=/mnt/mysql56/data basedir=/usr/local/mysql socket=/var/lib/mysql/mysql.sock user=justin innodb_buffer_pool_size=64G innodb…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… key significantly fragmented. I also would note there are some MySQL optimizer restrictions in how well it can deal with primary… when using a CMS where DB structure is prepdefined A: MySQL Server will not automatically define any indexes for you. Hopefully… in some cases for better index usage. Q: Is the b+ tree innodb index a single or double linked list at the…
Post: MySQL on Amazon RDS part 1: insert performance
March 15, 2011 By 37 Comments
Amazon’s Relational Database Service (RDS) is a cloud-hosted MySQL solution. I’ve had some clients hitting performance limitations on… a dramatic memory-to-disk threshold, as famously happens in B-Tree inserts (see InnoDB vs TokuDB for example). This doesn’t…
Post: Bug#12704861
November 20, 2011 By 1 Comment
… potentially be a very serious problem that is fixed in MySQL 5.1.60. I’ll repeat here the full documentation… that write the redo log about the B-tree changes. The buggy fix would log the B-tree changes first, meaning that after recovery… that write the redo log about the B-tree changes. The buggy fix would log the B-tree changes first, meaning that after recovery…
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…, and what is it aimed at. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range…
Post: Getting MySQL to use full key length
January 31, 2007 By 5 Comments
… for removing redundant indexes Here is example from NNSEEK database: mysql> explain SELECT thread_id FROM nn2_msg132.msg132 WHERE group… for the first query should not be smaller. Stats and B-Tree dives can give information which is quite far away from…) and (A,B) for query A=Const and B>Const we can see MySQL selecting (A) index instead of (A,B) or even worse…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 28 Comments
… folks arrived at this conclusion a long time before the MySQL world started to come around. There are even tuning utilities… prove, but seems reasonable based on the structure of a B-tree index, so let’s assume anyway: Hard-to-prove assumption… and compare the results to your IO system’s capabilities. MySQL doesn’t have good instrumentation for scientifically choosing a key…
Post: Announcing Percona Server 5.1.66-14.2
January 15, 2013 By Leave a Comment
… available here and from the Percona Software Repositories). Based on MySQL 5.1.66, including all the bug fixes in it… need to scan buffer pool for AHI entries after the B-trees for the tablespace have been dropped, as that will already…
Post: Announcing Percona Server 5.5.28-29.2
December 7, 2012 By 2 Comments
… available here and from the Percona Software Repositories). Based on MySQL 5.5.28, including all the bug fixes in it… need to scan buffer pool for AHI entries after the B-trees for the tablespace have been dropped, as that will already…
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 18 Comments
… root of the tree, and C is the leaf. We want to add new node D. A -> B -> C …add… D… NOT IN (SELECT descendant FROM TreePaths WHERE ancestor = ‘D’); But MySQL complains: “You can’t specify target table ‘TreePaths’ for update… the same table in a single query in MySQL. But we can use MySQL‘s multi-table DELETE syntax, to find any…
