… keys is simply because you are writing 4 times fewer B-tree values. The primary key and each secondary key each incur… auto-increment key, I would expect Inno to fill the B-tree pretty efficiently. With a 55GB buffer pool, we would expect… python version that Mark wrote http://bazaar.launchpad.net/~mdcallag/mysql-patch/mytools/annotate/head%3A/bench/ibench/iibench.py) is…
Post: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 11 Comments
… software RAID 10. Now let’s come to the MySQL configuration. MySQL Configuration I used Percona Server 5.5.22-55 for… was all about the configuration of the EC2 instance and MySQL. Now as far as the benchmark itself is concerned, I… performance is using partitioning to reduce the size of the B+tree, having multiple buffer pool instances to reduce contention problems caused…
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: 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: 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 27 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: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 7 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…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 17 Comments
… compression prevents one from using a binary search on a B-tree node. We could not compare a search key K to… a contiguous block of 64 pages, 16 kB each. Each B-tree index in InnoDB occupies two SEGMENTs. One segment is for… additional throughput? HT: ha_innodb.cc in 5.1: static MYSQL_SYSVAR_ULONG(thread_concurrency, srv_thread_concurrency, PLUGIN_VAR_RQCMDARG…
Post: Detailed review of Tokutek storage engine
April 28, 2009 By 21 Comments
… is based on special “fractal tree indexesâ€. Fractal Trees are a drop-in-replacement for a B-tree (based on current research in… case B-Tree does not fit into memory. As benchmark software I tried iiBench, which you can take there https://launchpad.net/mysql… operations to update index tree is significantly less than for usual B-Tree index. It’s as if Fractal Trees turn random IO into…
