….5. This blog post is aimed at the optimizer enhancement Multi Range Read (MRR). Its available in both MySQL 5.6… what this optimization actually is and what benefits it brings. Multi Range Read With traditional secondary index lookups, if the columns…. Suppose you have the following query executed on the InnoDB table: SELECT non_key_column FROM tbl WHERE key_column=x…
Post: Announcement of Percona XtraDB Cluster 5.5.20 GA release
April 3, 2012 By 10 Comments
…: Synchronous replication. Transaction either commited on all nodes or none. Multi-master replication. You can write to any node. Parallel applying… uses the Galera library, version 2.x, a generic Synchronous Multi-Master replication plugin for transactional applications. The Galera library is…, Rolling Schema Update. Schema change does not block operations against table This is an General Availability release. We did our best…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition …
Post: Percona welcomes Drizzle 7.1
April 10, 2012 By Leave a Comment
…the new features over the coming weeks. Major features include: Multi sourced replication XtraBackup included IPv6 data type ZeroMQ plugin Log…a file, MySQL UNIX socket protocol, javascript plugin, authentication via tables (similar to MySQL), RabbitMQ, regex policy, logging to syslog, authentication…
Post: Performance Schema tables stats
May 2, 2011 By 2 Comments
… Schema was mainly in memory workload and against single tables. Now after adding multi-tables support to sysbench, it is interesting to see… get from performance schema tables. 1. table_io_waits_summary_by_table mysql> select * from table_io_waits_summary_by_table where OBJECT_NAME=’sbtest55…
Post: Sysbench with support of multi-tables workload
April 29, 2011 By 4 Comments
… pushed to sysbench support for workload against multiple tables ( traditionally it used only single table). It is available from launchpad source tree…=25 prepare run: ./sysbench –test=tests/db/oltp.lua –oltp-tables-count=25 –num-threads=5 run oltp.lua should understand… may use ./sysbench –test=tests/db/parallel_prepare.lua –oltp-tables-count=64 –num-threads=8 run ( please note that oltp…
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 7 Comments
… an SQL database, including the design I call Closure Table. In Closure Table, we store every path in a tree, not only… difficult if you store hierarchies according to textbook solutions. CREATE TABLE TreePaths ( ancestor CHAR(1) NOT NULL, descendant CHAR(1) NOT… SELECT from the same table in a single query in MySQL. But we can use MySQL’s multi-table DELETE syntax, to find…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… STATE columns. The better strategy is often to have combined multi-column index on (AGE,STATE). Lets see why it is… index only but it is by far better to use multi column indexes. But the problems with Index Merge do not… intersection) on values it gets from the single table. I have reverted table to have i1 and i2 as independent columns in…
Post: InnoDB compression woes
May 20, 2011 By 14 Comments
… impression about performance I decided to run some sysbench with multi-tables benchmarks. I actually was preparing to do complex research, but… data. Scripts for multi-table sysbench allow to load data in parallel, so let’s load in 16 tables in 16 parallel threads…=25000000 run Results: Load time for regular tables: 19693 sec, for compressed tables: 38278 sec. Compressed tables are create as: ROW_FORMAT=COMPRESSED…
Post: Innodb row size limitation
April 7, 2011 By 11 Comments
… production for a while. Suppose you have the following table structure: CREATE TABLE example ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, fname… means if you insert a 500 character string with all multi-byte characters into a VARCHAR(500) column, that value will…/modify data, as more tables are involved, and for example you will have to run any multi table update in a transaction. It…
