MariaDB 10.3 – InnoDB Much Slower Than MyISAM

innodbmariadbmariadb-10.3myisam

I'm using MariaDB 10.3.12 on Arch Linux and haven't modified any of the default settings under /etc/mysql. The following script takes 0.75 s to run on average using the default InnoDB engine:

drop database if exists test_database;
create database test_database;
USE test_database;
CREATE TABLE test_table (namespace char(31) NOT NULL);

This is quite a lot for something that simple. With MyISAM (default_storage_engine=myisam in /etc/mysql/my.cnf.d/server.cnf) it runs in 0.016 s (0.07 s using aria). In an Ubuntu virtual machine, where I have MariaDB v 10.1.38, it runs in 0.04 s on average.

Is there any way to explain why InnoDB is so slow and which variables should I test? I know there are a lot of variables for fine-tuning the performance but it's puzzling to where an order of magnitude difference compared to other engines could possibly come from.

Here are my current InnoDB variables:

+---------------------------------------------+------------------------+
| Variable_name                               | Value                  |
+---------------------------------------------+------------------------+
| ignore_builtin_innodb                       | OFF                    |
| innodb_adaptive_flushing                    | ON                     |
| innodb_adaptive_flushing_lwm                | 10.000000              |
| innodb_adaptive_hash_index                  | ON                     |
| innodb_adaptive_hash_index_parts            | 8                      |
| innodb_adaptive_max_sleep_delay             | 150000                 |
| innodb_autoextend_increment                 | 64                     |
| innodb_autoinc_lock_mode                    | 1                      |
| innodb_background_scrub_data_check_interval | 3600                   |
| innodb_background_scrub_data_compressed     | OFF                    |
| innodb_background_scrub_data_interval       | 604800                 |
| innodb_background_scrub_data_uncompressed   | OFF                    |
| innodb_buf_dump_status_frequency            | 0                      |
| innodb_buffer_pool_chunk_size               | 134217728              |
| innodb_buffer_pool_dump_at_shutdown         | ON                     |
| innodb_buffer_pool_dump_now                 | OFF                    |
| innodb_buffer_pool_dump_pct                 | 25                     |
| innodb_buffer_pool_filename                 | ib_buffer_pool         |
| innodb_buffer_pool_instances                | 1                      |
| innodb_buffer_pool_load_abort               | OFF                    |
| innodb_buffer_pool_load_at_startup          | ON                     |
| innodb_buffer_pool_load_now                 | OFF                    |
| innodb_buffer_pool_size                     | 134217728              |
| innodb_change_buffer_max_size               | 25                     |
| innodb_change_buffering                     | all                    |
| innodb_checksum_algorithm                   | crc32                  |
| innodb_checksums                            | ON                     |
| innodb_cmp_per_index_enabled                | OFF                    |
| innodb_commit_concurrency                   | 0                      |
| innodb_compression_algorithm                | zlib                   |
| innodb_compression_default                  | OFF                    |
| innodb_compression_failure_threshold_pct    | 5                      |
| innodb_compression_level                    | 6                      |
| innodb_compression_pad_pct_max              | 50                     |
| innodb_concurrency_tickets                  | 5000                   |
| innodb_data_file_path                       | ibdata1:12M:autoextend |
| innodb_data_home_dir                        |                        |
| innodb_deadlock_detect                      | ON                     |
| innodb_default_encryption_key_id            | 1                      |
| innodb_default_row_format                   | dynamic                |
| innodb_defragment                           | OFF                    |
| innodb_defragment_fill_factor               | 0.900000               |
| innodb_defragment_fill_factor_n_recs        | 20                     |
| innodb_defragment_frequency                 | 40                     |
| innodb_defragment_n_pages                   | 7                      |
| innodb_defragment_stats_accuracy            | 0                      |
| innodb_disable_sort_file_cache              | OFF                    |
| innodb_disallow_writes                      | OFF                    |
| innodb_doublewrite                          | ON                     |
| innodb_encrypt_log                          | OFF                    |
| innodb_encrypt_tables                       | OFF                    |
| innodb_encryption_rotate_key_age            | 1                      |
| innodb_encryption_rotation_iops             | 100                    |
| innodb_encryption_threads                   | 0                      |
| innodb_fast_shutdown                        | 1                      |
| innodb_fatal_semaphore_wait_threshold       | 600                    |
| innodb_file_per_table                       | ON                     |
| innodb_fill_factor                          | 100                    |
| innodb_flush_log_at_timeout                 | 1                      |
| innodb_flush_log_at_trx_commit              | 1                      |
| innodb_flush_method                         | fsync                  |
| innodb_flush_neighbors                      | 1                      |
| innodb_flush_sync                           | ON                     |
| innodb_flushing_avg_loops                   | 30                     |
| innodb_force_load_corrupted                 | OFF                    |
| innodb_force_primary_key                    | OFF                    |
| innodb_force_recovery                       | 0                      |
| innodb_ft_aux_table                         |                        |
| innodb_ft_cache_size                        | 8000000                |
| innodb_ft_enable_diag_print                 | OFF                    |
| innodb_ft_enable_stopword                   | ON                     |
| innodb_ft_max_token_size                    | 84                     |
| innodb_ft_min_token_size                    | 3                      |
| innodb_ft_num_word_optimize                 | 2000                   |
| innodb_ft_result_cache_limit                | 2000000000             |
| innodb_ft_server_stopword_table             |                        |
| innodb_ft_sort_pll_degree                   | 2                      |
| innodb_ft_total_cache_size                  | 640000000              |
| innodb_ft_user_stopword_table               |                        |
| innodb_idle_flush_pct                       | 100                    |
| innodb_immediate_scrub_data_uncompressed    | OFF                    |
| innodb_io_capacity                          | 200                    |
| innodb_io_capacity_max                      | 2000                   |
| innodb_lock_schedule_algorithm              | fcfs                   |
| innodb_lock_wait_timeout                    | 50                     |
| innodb_locks_unsafe_for_binlog              | OFF                    |
| innodb_log_buffer_size                      | 16777216               |
| innodb_log_checksums                        | ON                     |
| innodb_log_compressed_pages                 | ON                     |
| innodb_log_file_size                        | 50331648               |
| innodb_log_files_in_group                   | 2                      |
| innodb_log_group_home_dir                   | ./                     |
| innodb_log_optimize_ddl                     | ON                     |
| innodb_log_write_ahead_size                 | 8192                   |
| innodb_lru_scan_depth                       | 1024                   |
| innodb_max_dirty_pages_pct                  | 75.000000              |
| innodb_max_dirty_pages_pct_lwm              | 0.000000               |
| innodb_max_purge_lag                        | 0                      |
| innodb_max_purge_lag_delay                  | 0                      |
| innodb_max_undo_log_size                    | 10485760               |
| innodb_monitor_disable                      |                        |
| innodb_monitor_enable                       |                        |
| innodb_monitor_reset                        |                        |
| innodb_monitor_reset_all                    |                        |
| innodb_old_blocks_pct                       | 37                     |
| innodb_old_blocks_time                      | 1000                   |
| innodb_online_alter_log_max_size            | 134217728              |
| innodb_open_files                           | 2000                   |
| innodb_optimize_fulltext_only               | OFF                    |
| innodb_page_cleaners                        | 1                      |
| innodb_page_size                            | 16384                  |
| innodb_prefix_index_cluster_optimization    | OFF                    |
| innodb_print_all_deadlocks                  | OFF                    |
| innodb_purge_batch_size                     | 300                    |
| innodb_purge_rseg_truncate_frequency        | 128                    |
| innodb_purge_threads                        | 4                      |
| innodb_random_read_ahead                    | OFF                    |
| innodb_read_ahead_threshold                 | 56                     |
| innodb_read_io_threads                      | 4                      |
| innodb_read_only                            | OFF                    |
| innodb_replication_delay                    | 0                      |
| innodb_rollback_on_timeout                  | OFF                    |
| innodb_rollback_segments                    | 128                    |
| innodb_scrub_log                            | OFF                    |
| innodb_scrub_log_speed                      | 256                    |
| innodb_sort_buffer_size                     | 1048576                |
| innodb_spin_wait_delay                      | 4                      |
| innodb_stats_auto_recalc                    | ON                     |
| innodb_stats_include_delete_marked          | OFF                    |
| innodb_stats_method                         | nulls_equal            |
| innodb_stats_modified_counter               | 0                      |
| innodb_stats_on_metadata                    | OFF                    |
| innodb_stats_persistent                     | ON                     |
| innodb_stats_persistent_sample_pages        | 20                     |
| innodb_stats_sample_pages                   | 8                      |
| innodb_stats_traditional                    | ON                     |
| innodb_stats_transient_sample_pages         | 8                      |
| innodb_status_output                        | OFF                    |
| innodb_status_output_locks                  | OFF                    |
| innodb_strict_mode                          | ON                     |
| innodb_sync_array_size                      | 1                      |
| innodb_sync_spin_loops                      | 30                     |
| innodb_table_locks                          | ON                     |
| innodb_temp_data_file_path                  | ibtmp1:12M:autoextend  |
| innodb_thread_concurrency                   | 0                      |
| innodb_thread_sleep_delay                   | 10000                  |
| innodb_tmpdir                               |                        |
| innodb_undo_directory                       | ./                     |
| innodb_undo_log_truncate                    | OFF                    |
| innodb_undo_logs                            | 128                    |
| innodb_undo_tablespaces                     | 0                      |
| innodb_use_atomic_writes                    | ON                     |
| innodb_use_native_aio                       | ON                     |
| innodb_version                              | 10.3.12                |
| innodb_write_io_threads                     | 4                      |
+---------------------------------------------+------------------------+

EDIT. Here is a test for adding 204 values into the database.

USE test_database;
insert into test_table values
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),
('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a');

It takes 0.22 s on average using InnoDB and 0.01 s using MyISAM.

EDIT. This is the profile for creating the table with InnoDB:

+------------------------+----------+----------+------------+
| Status                 | Duration | CPU_user | CPU_system |
+------------------------+----------+----------+------------+
| Starting               | 0.000035 | 0.000000 |   0.000079 |
| Checking permissions   | 0.000005 | 0.000000 |   0.000009 |
| Opening tables         | 0.000011 | 0.000000 |   0.000030 |
| After opening tables   | 0.000003 | 0.000000 |   0.000009 |
| System lock            | 0.000002 | 0.000000 |   0.000004 |
| Table lock             | 0.000030 | 0.000000 |   0.000061 |
| Creating table         | 0.455613 | 0.036331 |   0.107722 |
| After create           | 0.000022 | 0.000004 |   0.000015 |
| Query end              | 0.000011 | 0.000069 |   0.000240 |
| Commit                 | 0.000011 | 0.000005 |   0.000017 |
| Closing tables         | 0.000010 | 0.000005 |   0.000015 |
| Unlocking tables       | 0.000009 | 0.000056 |   0.000193 |
| Closing tables         | 0.000009 | 0.000031 |   0.000109 |
| Commit_implicit        | 0.000017 | 0.000051 |   0.000175 |
| Starting cleanup       | 0.000008 | 0.000007 |   0.000026 |
| Freeing items          | 0.000010 | 0.000003 |   0.000010 |
| Updating status        | 0.000035 | 0.000008 |   0.000028 |
| Reset for next command | 0.000054 | 0.000342 |   0.000000 |
+------------------------+----------+----------+------------+

And the same thing with MyISAM:

+------------------------+----------+----------+------------+
| Status                 | Duration | CPU_user | CPU_system |
+------------------------+----------+----------+------------+
| Starting               | 0.000062 | 0.000018 |   0.000043 |
| Checking permissions   | 0.000014 | 0.000004 |   0.000009 |
| Opening tables         | 0.000024 | 0.000007 |   0.000017 |
| After opening tables   | 0.000009 | 0.000003 |   0.000007 |
| System lock            | 0.000007 | 0.000002 |   0.000004 |
| Table lock             | 0.000052 | 0.000015 |   0.000037 |
| Creating table         | 0.006708 | 0.000235 |   0.000554 |
| After create           | 0.000018 | 0.000005 |   0.000012 |
| Query end              | 0.000009 | 0.000002 |   0.000006 |
| Commit                 | 0.000008 | 0.000002 |   0.000005 |
| Closing tables         | 0.000008 | 0.000003 |   0.000005 |
| Unlocking tables       | 0.000006 | 0.000001 |   0.000004 |
| Closing tables         | 0.000006 | 0.000002 |   0.000004 |
| Commit_implicit        | 0.000014 | 0.000004 |   0.000010 |
| Starting cleanup       | 0.000007 | 0.000002 |   0.000005 |
| Freeing items          | 0.000008 | 0.000003 |   0.000006 |
| Updating status        | 0.000028 | 0.000008 |   0.000019 |
| Reset for next command | 0.000008 | 0.000002 |   0.000006 |
+------------------------+----------+----------+------------+

This is the profile for inserting values into the table with InnoDB:

+------------------------+----------+----------+------------+
| Status                 | Duration | CPU_user | CPU_system |
+------------------------+----------+----------+------------+
| Starting               | 0.000439 | 0.000093 |   0.000345 |
| Checking permissions   | 0.000013 | 0.000003 |   0.000010 |
| Opening tables         | 0.000029 | 0.000006 |   0.000022 |
| After opening tables   | 0.000010 | 0.000002 |   0.000008 |
| System lock            | 0.000009 | 0.000002 |   0.000007 |
| Table lock             | 0.000009 | 0.000002 |   0.000007 |
| Init for update        | 0.000041 | 0.000009 |   0.000033 |
| Update                 | 0.001986 | 0.000000 |   0.002014 |
| End of update loop     | 0.000020 | 0.000000 |   0.000018 |
| Query end              | 0.000008 | 0.000000 |   0.000008 |
| Commit                 | 0.089969 | 0.011578 |   0.006221 |
| Closing tables         | 0.000030 | 0.000006 |   0.000019 |
| Unlocking tables       | 0.000011 | 0.000002 |   0.000008 |
| Closing tables         | 0.000019 | 0.000004 |   0.000016 |
| Starting cleanup       | 0.000009 | 0.000002 |   0.000006 |
| Freeing items          | 0.000023 | 0.000005 |   0.000018 |
| Updating status        | 0.000042 | 0.000009 |   0.000033 |
| Reset for next command | 0.000012 | 0.000002 |   0.000009 |
+------------------------+----------+----------+------------+

And the same thing with MyISAM:

+------------------------+----------+----------+------------+
| Status                 | Duration | CPU_user | CPU_system |
+------------------------+----------+----------+------------+
| Starting               | 0.000569 | 0.000561 |   0.000000 |
| Checking permissions   | 0.000015 | 0.000013 |   0.000000 |
| Opening tables         | 0.000031 | 0.000032 |   0.000000 |
| After opening tables   | 0.000010 | 0.000009 |   0.000000 |
| System lock            | 0.000009 | 0.000009 |   0.000000 |
| Table lock             | 0.000010 | 0.000010 |   0.000000 |
| Init for update        | 0.000062 | 0.000062 |   0.000000 |
| Update                 | 0.000246 | 0.000247 |   0.000000 |
| End of update loop     | 0.000018 | 0.000017 |   0.000000 |
| Query end              | 0.000008 | 0.000008 |   0.000000 |
| Commit                 | 0.000007 | 0.000007 |   0.000000 |
| Closing tables         | 0.000008 | 0.000007 |   0.000000 |
| Unlocking tables       | 0.000036 | 0.000037 |   0.000000 |
| Closing tables         | 0.000016 | 0.000016 |   0.000000 |
| Starting cleanup       | 0.000007 | 0.000007 |   0.000000 |
| Freeing items          | 0.000014 | 0.000013 |   0.000000 |
| Updating status        | 0.000030 | 0.000030 |   0.000000 |
| Reset for next command | 0.000009 | 0.000010 |   0.000000 |
+------------------------+----------+----------+------------+

Best Answer

TLDR; The problem turned out to be the file system. Don't use BTRFS for database storage.

I did an analysis of MariaDB timings for different file systems using some of the data that I have to work with. Even though this is specific to my situation I thought I'd post the results anyway in case there is interest.

I tried the following configurations:

"BTRFS (subvolume)" - This is what I had prior to posting this question. One BTRFS partition for everything with subvolumes for things like / and /home. The directory /var/lib/mysql was located on the / subvolume.
"BTRFS" - Separate BTRFS partition for /var/lib/mysql. No subvolumes here.
"ext4" - Separate ext4 partition for /var/lib/mysql.
"XFS" - Separate XFS partition for /var/lib/mysql. After having done some more research this is probably what I should use in a production environment. XFS is known for high performance.
"F2FS" - Separate F2FS (Flash-Friendly File System) partition for /var/lib/mysql. This is fast but not considered stable enough for production environments. Only use this if potential data loss is acceptable.
"F2FS (no_barrier)" - Same as "F2FS" but mounted with the fsync_mode=nobarrier option, which reduces the number of cache flushes. This is faster but even riskier than regular F2FS.

In all cases the file systems reside on a LUKS partition prepared with dm-crypt.

Test case 1.

First, I measured the time for creating tables and foreign key constraints for a total of 27 tables. I can't post the actual content here but each table has about 15 fields and there are a total of 81 FK constraints, just to give you a rough idea. The script that I ran also includes some other stuff like truncating all tables after they are created and altering some columns here and there. Here are the timings, in s. Each number is an average over two runs.

|                   | InnoDB | Aria |
|-------------------+--------+------|
| BTRFS (subvolume) |  473.9 | 86.7 |
| BTRFS             |   53.0 | 40.9 |
| ext4              |   35.1 | 29.0 |
| XFS               |   29.2 | 27.2 |
| F2FS              |   18.0 | 26.3 |
| F2FS (nobarrier)  |    5.6 | 16.9 |
|-------------------+--------+------|

As you can see, using InnoDB and keeping the databases on the same subvolume as / is an order of magnitude slower than other configurations. This is why I actually used Aria for my work until now. Using a separate BTRFS partition is much faster and switching to one of the other file systems even faster. I was surprised by the margin by which F2FS beats all other file systems.

Test case 2.

Here I'm inserting data into the tables created in case 1. Again, I can't post the actual contents but the total size of SQL statements executed is about 28 MiB, so a good amount for producing meaningful timings. These are the timings:

|                   | InnoDB |  Aria |
|-------------------+--------+-------|
| BTRFS (subvolume) | 1483.6 | 144.3 |
| BTRFS             |  134.3 | 137.7 |
| ext4              |   90.2 | 121.5 |
| XFS               |   82.6 | 116.3 |
| F2FS              |   51.1 | 105.6 |
| F2FS (nobarrier)  |   20.4 |  26.9 |
|-------------------+--------+-------|

The conclusions are the same as for case 1. BTRFS is significantly slower than the other file systems.

Test case 3.

This is just like case 2 but everything if wrapped between the START TRANSACTION; and COMMIT; statements.

|                   | InnoDB | Aria |
|-------------------+--------+------|
| BTRFS (subvolume) |  351.7 | N/A  |
| BTRFS             |   38.0 | N/A  |
| ext4              |   31.4 | N/A  |
| XFS               |   30.8 | N/A  |
| F2FS              |   26.1 | N/A  |
| F2FS (nobarrier)  |   18.8 | N/A  |
|-------------------+--------+------|

No results for Aria because it doesn't support transactions. Otherwise, wrapping everything into a single transaction reduces the time significantly for all cases except for F2FS (nobarrier), which is still the fastest solution, but only a small improvement compared to case 2.

Test case 4.

This is a short test where I'm using the same test_table as in the original question. The time measured is for creating the table and entering the 203 values into it. Each reported time is an average over five runs.

|                   | InnoDB |  Aria |
|-------------------+--------+-------|
| BTRFS (subvolume) |  0.914 | 0.105 |
| BTRFS             |  0.164 | 0.015 |
| ext4              |  0.122 | 0.080 |
| XFS               |  0.099 | 0.070 |
| F2FS              |  0.068 | 0.070 |
| F2FS (nobarrier)  |  0.037 | 0.053 |
|-------------------+--------+-------|

The fact that we are looking at a much smaller timescale here doesn't change the conclusions. Using BTRFS were the databases are kept on the same subvolume as / is still unacceptably slow.

Conclusion

I've decided to go with F2FS mounted with the fsync_mode=nobarrier option, which is clearly the fastest solution by a large margin. Since I'm using my personal computer for development only I'm fine with the potential loss of SQL data (should be quite unlikely, but still). Otherwise I'd go with XFS.

^{Hardware used: i7-6700HQ@2.60GHz, 256 GB PCIe M.2 Samsung SM951}

YOUR QUERY

SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506;

At first glance, that query should only touches 1.1597% (62510 out of 5390146) of the table. It should be fast given the key distribution of threadid 51506.

REALITY CHECK

No matter which version of MySQL (Oracle, Percona, MariaDB) you use, none of them can fight to one enemy they all have in common : The InnoDB Architecture.

InnoDB Architecture

CLUSTERED INDEX

Please keep in mind that the each threadid entry has a primary key attached. This means that when you read from the index, it must do a primary key lookup within the ClusteredIndex (internally named gen_clust_index). In the ClusteredIndex, each InnoDB page contains both data and PRIMARY KEY index info. See my post Best of MyISAM and InnoDB for more info.

REDUNDANT INDEXES

You have a lot of clutter in the table because some indexes have the same leading columns. MySQL and InnoDB has to navigate through the index clutter to get to needed BTREE nodes. You should reduced that clutter by running the following:

ALTER TABLE newbb_innopost
    DROP INDEX threadid,
    DROP INDEX threadid_2,
    DROP INDEX threadid_visible_dateline,
    ADD INDEX threadid_visible_dateline_index (`threadid`,`visible`,`dateline`,`userid`)
;

Why strip down these indexes ?

The first three indexes start with threadid
threadid_2 and threadid_visible_dateline start with the same three columns
threadid_visible_dateline does not need postid since it's the PRIMARY KEY and it's embedded

BUFFER CACHING

The InnoDB Buffer Pool caches data and index pages. MyISAM only caches index pages.

Just in this area alone, MyISAM does not waste time caching data. That's because it's not designed to cache data. InnoDB caches every data page and index page (and its grandmother) it touches. If your InnoDB Buffer Pool is too small, you could be caching pages, invalidating pages, and removing pages all in one query.

TABLE LAYOUT

You could shave of some space from the row by considering importthreadid and importpostid. You have them as BIGINTs. They take up 16 bytes in the ClusteredIndex per row.

You should run this

SELECT importthreadid,importpostid FROM newbb_innopost PROCEDURE ANALYSE();

This will recommend what data types these columns should be for the given dataset.

CONCLUSION

MyISAM has a lot less to contend with than InnoDB, especially in the area of caching.

While you revealed the amount of RAM (32GB) and the version of MySQL (Server version: 10.0.12-MariaDB-1~trusty-wsrep-log mariadb.org binary distribution, wsrep_25.10.r4002), there are still other pieces to this puzzle you have not revealed

The InnoDB settings
The Number of Cores
Other settings from my.cnf

If you can add these things to the question, I can further elaborate.

UPDATE 2014-08-28 11:27 EDT

You should increase threading

innodb_read_io_threads = 64
innodb_write_io_threads = 16
innodb_log_buffer_size = 256M

I would consider disabling the query cache (See my recent post Why query_cache_type is disabled by default start from MySQL 5.6?)

query_cache_size = 0

I would preserve the Buffer Pool

innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1

Increase purge threads (if you do DML on multiple tables)

innodb_purge_threads = 4

GIVE IT A TRY !!!

Performance Schema – Understanding ‘stage/sql/Sending data’ in MySQL

Sadly, stage/sql/Sending data (something that you will see on SHOW PROCESSLIST as Sending data) does not tell us much about the query internals.

The thread is reading and processing rows for a SELECT statement...

Is like saying nothing, why is it different in both cases?

Your effort is not in vain, you have proved what is NOT affecting the query- permissions, optimization, opening tables, locking, logging and other support bits. There is something wrong with the query itself, likely either a different query plan or a configuration/hardware issue.

In order to continue answering, please provide the output of the following commands on both servers:

EXPLAIN SELECT ... [REST OF YOUR QUERY] \G

and

FLUSH STATUS;
SELECT ... [REST OF YOUR QUERY, do not show the actual results, we do not care];
SHOW STATUS like 'Hand%';

Those are 2 commands that will tell us more about query execution before checking other more complex possibilities. It is the most common cause so there is a high chance of getting something interesting out of it. Also please show us the table structure SHOW CREATE TABLE (in both servers) for reference.