Memory allocators: MySQL performance improvements in Percona Server 5.5.30-30.2

In addition to the problem with trx_list scan we discussed in Friday’s post, there is another issue in InnoDB transaction processing that notably affects MySQL performance – for every transaction InnoDB creates a read view and allocates memory for this structure from heap. The problem is that the heap for that allocation is destroyed on each commit and thus the read view memory is reallocated on the next transaction.

There are two aspects of this problem:

1) memory allocation is an costly operation and if memory allocator has scalability problems (like allocator from glibc) this will notably slowdown MySQL-transaction creation and many threads will get stuck on glibc/kernel syscalls, which will in turn result in contention on kernel_mutex (trx_sys->mutex in 5.6), as memory allocation occurs under that mutex. See an example of such an issue. Related bugs: BUG#54982, BUG#49169.

2) memory allocation for read-view structure is not a direct malloc() call, but rather goes through the InnoDB heap layer – so InnoDB allocates heap area and then creates requested block(s) there. That optimization helps to avoid fragmentation in case of many small allocations and allows to free all blocks from specific heap at once. But in the case when we need memory only for a single block this 2 layers approach is quite inefficient and in some cases can be the reason for notable MySQL performance drop.

Now in Percona Server, for each connection we use a preallocated read view structure, reuse that memory during the entire connection lifetime and free it at disconnect. If some transactions require a larger amount of memory – we just reallocate memory to fulfill it needs.

To demonstrate the difference we have run sysbench POINT_SELECT test for glibc and jemalloc allocators.



= MySQL 5.5.30
– throughput of MySQL 5.5.30 with glibc is limited first of all by inefficiency of transaction list handling (see our previous post) and also by bad scalability of glibc malloc itself
– jemalloc helps MySQL 5.5.30 to fix issues with malloc scalability but still scanning of the transaction list
causes performance drop

= MySQL 5.6.10
– in autocommit mode 5.6.10 has no problem with transaction list scanning (due to the read-only transactions optimization), but it still allocates/frees memory for read view structure and that causes drops at high threads with glibc. Jemalloc helps to solve that.

= Percona Server 5.5.30-30.2
– both issues are solved in our recent release and such we have almost no difference in results between runs either with glibc or jemalloc

The post Memory allocators: MySQL performance improvements in Percona Server 5.5.30-30.2 appeared first on MySQL Performance Blog.


MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown

Percona Webinars

Catch the webinar: “Learn How MySQL 5.6 Makes Query Optimization Easier” for more tips on the 5.6 optimizer

While preparing the webinar I will deliver this Friday, I ran into a quite interesting (although not very impacting) optimizer issue: a “SELECT *” taking half the time to execute than the same “SELECT one_indexed_column” query in MySQL 5.6.10.

This turned into a really nice exercise for checking the performance and inner workings of one of the nicest features of the newer MySQL optimizer: the Index Condition Pushdown Optimization, or ICP, which we have previously discussed on our blog.

It was the following query in particular that had this surprising outcome:

mysql> SELECT * FROM cast_info WHERE role_id = 1 and note like '%Jaime%';

On a table like this:

CREATE TABLE `cast_info` (
  `person_id` int(11) NOT NULL,
  `movie_id` int(11) NOT NULL,
  `person_role_id` int(11) DEFAULT NULL,
  `note` varchar(250),
  `nr_order` int(11) DEFAULT NULL,
  `role_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `role_id_note` (`role_id`,`note`)

The table had 22 million rows, with approximately 8 million of them having role_id = 1, and 266 have role_id = 1 and containing the word ‘Jaime’ somewhere in the field note.

The original query had a stable execution time of 1.09 sec, while the following one, which selects less amount of data (just one column) and can take advantage of the covering index technique, did actually take more time to execute:

mysql> SELECT role_id FROM cast_info WHERE role_id = 1 and note like '%Jaime%'\G
266 rows in set (1.82 sec)

Please note that the times were very stable and the contents of the buffer pool did not affect the results.

What was happening? Well, in order to understand it I must provide you with more background information. My buffer pool was big enough to hold the whole database (data and indexes fit completely in memory). Also, I was testing, as I said before, index condition pushdown. Let’s have a look at the EXPLAIN output:

mysql> EXPLAIN SELECT * FROM cast_info WHERE role_id = 1 and note like '%Jaime%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cast_info
         type: ref
possible_keys: role_id_note
          key: role_id_note
      key_len: 4
          ref: const
         rows: 10259274
        Extra: Using index condition
1 row in set (0.00 sec)

With ICP, the actual number of rows read at SQL layer is actually very different from the “rows” value seen above. This is because the second part of the condition –note like '%Jaime%'– is actually tested at engine level, not at handler level.

Condition pushdown is one of the new features of MySQL 5.6, and actually is a great improvement over MySQL 5.5. For example, in this case, the actual number of “Handler_read_next” calls was reduced from 8346769 (5.5) to just 266 (5.6), reducing the executing time by almost 5 times. Pro tip: make sure you always check the Handler status variables for post-execution analysis.

So why is the “SELECT note” actually slower? It seems that whenever the covering index technique is available, this is always preferred over the ICP optimization:

mysql> EXPLAIN SELECT role_id FROM cast_info WHERE role_id = 1 and note like '%Jaime%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cast_info
         type: ref
possible_keys: role_id_note
          key: role_id_note
      key_len: 4
          ref: const
         rows: 10259274
        Extra: Using where; Using index
1 row in set (0.00 sec)

I reported this issue to Oracle and they confirmed that this is the intended/current status of the MySQL 5.6.10 optimizer. Other interesting things to notice:

  • ICP is a great new feature that already saved us a lot of execution time, probably its cost has to be tuned better in the feature. There are more ways to make a query faster, which means you need more manual care and tuning now.
  • MySQL is conservative about “Using index” -in most cases it will be the right solution because our SELECT will only be faster when the condition is very selective and the buffer pool is effective.
  • There is no workaround, using FORCE-like commands or optimizer_switch flags- we can disable ICP, but not “using index”.

So, I wouldn’t call this a bug, and I’m not especially concerned about this particular case — you may or may not consider it an edge case — but I would call it a limitation of the current query planner. However I would like to see better algorithms, statistics computation and monitoring variables about index usage in the future, now that we have more complex optimization strategies. Even row-level operation counters are sometimes not enough.

Do you want to know more about the MySQL 5.6 query optimization improvements in a practical way, with real-life examples? Do you want to know a 3-party, independent and technical opinion about the new features of MySQL query planner? Are you not yet familiar with terms like MRR, BKA or ICP? Are you a Developer or a DBA and want to be prepared for the MySQL 5.6 release, and get advantage of the latest integrated tools that MySQL provides with its last GA release? Then I invite you to join me at the webinar I have prepared for this Friday, March 15: “Learn How MySQL 5.6 Makes Query Optimization Easier”

The post MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown appeared first on MySQL Performance Blog.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com