… float NOT NULL, PRIMARY KEY (`transactionid`), KEY `marketsegment` (`price`,`customerid`), KEY `registersegment` (`cashregisterid`,`price`,`customerid`), KEY `pdc` (`price`,`dateandtime`,`customerid`)… the table purchases_index which has 3 secondary indexes. Another thing I would like to share …
Post: MariaDB 5.3 is released as GA!
March 5, 2012 By 17 Comments
… discuss some of the improvements in MariaDB 5.3, let me mention a few things briefly. First, Peter and Ovais are doing… of useless tables in joins Thread pooling and a segmented key cache for improved scalability That’s really just a sampling… Percona’s PAM plugin. MariaDB 5.3 isn’t the only exciting thing, however. MariaDB 5.3 is based on MySQL 5.1…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… purpose of this benchmark, I have used TPC-H Query #3 and ran it on TPC-H dataset (InnoDB tables) with…. MariaDB 5.5 is close, yet slower by ~10s. Another thing that is important to know is that, just increasing the….6. Next interesting thing are the last two columns of the table above and the values for ‘Handler_read_key‘, ‘Handler_read…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 9 Comments
… range primary key lookup. Thereby, converting Random access to one or more sequential access. There is one another interesting thing that has…-01′ and o_orderdate < date_add( '1993-08-01' ,interval '3‘ month) and l_returnflag = ‘R’ and c_nationkey = n_nationkey… the best query execution plan. I had noted a similar thing when benchmarking ICP, the optimizer made a wrong choice. It…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… for filtering records. For example, suppose you have a key defined as: KEY `i_l_partkey` (`l_partkey`,`l_quantity`,`l_shipmode…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… buffers, such as innodb_buffer_size, key_buffer_size etc, you have reasonable …21 05:52:02 UPDATE_TIME: NULL *************************** 3. row *************************** SESSION_ID: 7231 TABLE_SCHEMA: test… connections and tables regularly. Another common thing about memory leaks is because it …
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 5 Comments
… you ORM by joining all tables connected with foreign keys just in case) which would take 5 seconds …time to less than 50ms. Low values, such as 3,4 provided a bit better performance but I …more important than backward compatibility. In MySQL 5.6 things are likely to get even better handling joins of…
Post: Percona Toolkit 2.1 with New Online Schema Change Tool
April 5, 2012 By 9 Comments
… of the tool, one of the most painful things anyone experiences with MySQL is significantly alleviated. If … fully featured and much easier to use. The key is that it is replication-aware and inspects … talks about various parts of the toolkit: A 3-hour tutorial about the toolkit on Tuesday Daniel…
Post: EXPLAIN EXTENDED can tell you all kinds of interesting things
June 15, 2010 By 7 Comments
…select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | … NULL | NULL | NULL | NULL | 4 | Using where | +—-+————-+——-+——+—————+——+———+——+——+————-+ 3 rows in set, 1 …(0.00 sec) The most important thing to notice is the…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… size : 4096 KB physical id : 1 siblings : 4 core id : 3 cpu cores : 4 fpu : yes fpu_exception : yes cpuid level… first place, and not just show “here’s the 3 bad things I found in 2 hours.” We also make sure that… of other things I’ll look at, including checking for default users in the mysql.* tables, running mk-duplicate-key-checker to…
