Why are simple SELECTs on InnoDB 100x slower than on MyISAM?

Question

I have quite an annoying problem. I want to use INNODB as my main database engine and give up on MyISAM as I need the former for using galera-cluster for redundancy.

I copied (description follows) the newbb_post table to a new table called newbb_innopost and changed that to InnoDB. The tables currently hold 5,390,146 entries each.

Running these selects on a freshly started database (so no caching is involved at this point!) the database yields the following results (omitting the complete output, please note that I do not even ask the database to sort the results):

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

.
.
| 5401593 |      0 |
| 5401634 |      0 |
+---------+--------+
62510 rows in set (0.13 sec)

SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506;
.
.
| 5397410 |      0 |
| 5397883 |      0 |
+---------+--------+
62510 rows in set (1 min 22.19 sec)

0.13 seconds to 86.19 seconds (!)

I am wondering why this is happening. I did read some answers here on Stackexchange involving InnoDB and some are suggesting increasing innodb_buffer_pool size to 80% of installed RAM. This won't solve the problem, that the initial query to a particular ID will take at least 50x longer and stalling the whole websever, queueing up connections and queries for the database. Afterwards the cache/buffer might kick in, but there are over 100.000 threads in this database, so it is very likely that the cache will never hold all the relevant queries to be served.

The queries above are simple (no joins), and all keys are used:

EXPLAIN SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506;
+------+-------------+-------+------+-----------------------------------------------+----------+---------+-------+--------+-------+
| id   | select_type | table | type | possible_keys                                 | key      | key_len | ref   | rows   | Extra |
+------+-------------+-------+------+-----------------------------------------------+----------+---------+-------+--------+-------+
|    1 | SIMPLE      | post  | ref  | threadid,threadid_2,threadid_visible_dateline | threadid | 4       | const | 120144 |       |
+------+-------------+-------+------+-----------------------------------------------+----------+---------+-------+--------+-------+

This is the MyISAM-Table:

CREATE TABLE `newbb_post` (
  `postid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `threadid` int(10) unsigned NOT NULL DEFAULT '0',
  `parentid` int(10) unsigned NOT NULL DEFAULT '0',
  `username` varchar(100) NOT NULL DEFAULT '',
  `userid` int(10) unsigned NOT NULL DEFAULT '0',
  `title` varchar(250) NOT NULL DEFAULT '',
  `dateline` int(10) unsigned NOT NULL DEFAULT '0',
  `pagetext` mediumtext,
  `allowsmilie` smallint(6) NOT NULL DEFAULT '0',
  `showsignature` smallint(6) NOT NULL DEFAULT '0',
  `ipaddress` varchar(15) NOT NULL DEFAULT '',
  `iconid` smallint(5) unsigned NOT NULL DEFAULT '0',
  `visible` smallint(6) NOT NULL DEFAULT '0',
  `attach` smallint(5) unsigned NOT NULL DEFAULT '0',
  `infraction` smallint(5) unsigned NOT NULL DEFAULT '0',
  `reportthreadid` int(10) unsigned NOT NULL DEFAULT '0',
  `importthreadid` bigint(20) NOT NULL DEFAULT '0',
  `importpostid` bigint(20) NOT NULL DEFAULT '0',
  `converted_2_utf8` int(11) NOT NULL,
  `htmlstate` enum('off','on','on_nl2br') NOT NULL DEFAULT 'on_nl2br',
  PRIMARY KEY (`postid`),
  KEY `threadid` (`threadid`,`userid`),
  KEY `importpost_index` (`importpostid`),
  KEY `dateline` (`dateline`),
  KEY `threadid_2` (`threadid`,`visible`,`dateline`),
  KEY `converted_2_utf8` (`converted_2_utf8`),
  KEY `threadid_visible_dateline` (`threadid`,`visible`,`dateline`,`userid`,`postid`),
  KEY `ipaddress` (`ipaddress`),
  KEY `userid` (`userid`,`parentid`),
  KEY `user_date` (`userid`,`dateline`)
) ENGINE=MyISAM AUTO_INCREMENT=5402802 DEFAULT CHARSET=latin1

and this is the InnoDB Table (it's exactly the same):

CREATE TABLE `newbb_innopost` (
  `postid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `threadid` int(10) unsigned NOT NULL DEFAULT '0',
  `parentid` int(10) unsigned NOT NULL DEFAULT '0',
  `username` varchar(100) NOT NULL DEFAULT '',
  `userid` int(10) unsigned NOT NULL DEFAULT '0',
  `title` varchar(250) NOT NULL DEFAULT '',
  `dateline` int(10) unsigned NOT NULL DEFAULT '0',
  `pagetext` mediumtext,
  `allowsmilie` smallint(6) NOT NULL DEFAULT '0',
  `showsignature` smallint(6) NOT NULL DEFAULT '0',
  `ipaddress` varchar(15) NOT NULL DEFAULT '',
  `iconid` smallint(5) unsigned NOT NULL DEFAULT '0',
  `visible` smallint(6) NOT NULL DEFAULT '0',
  `attach` smallint(5) unsigned NOT NULL DEFAULT '0',
  `infraction` smallint(5) unsigned NOT NULL DEFAULT '0',
  `reportthreadid` int(10) unsigned NOT NULL DEFAULT '0',
  `importthreadid` bigint(20) NOT NULL DEFAULT '0',
  `importpostid` bigint(20) NOT NULL DEFAULT '0',
  `converted_2_utf8` int(11) NOT NULL,
  `htmlstate` enum('off','on','on_nl2br') NOT NULL DEFAULT 'on_nl2br',
  PRIMARY KEY (`postid`),
  KEY `threadid` (`threadid`,`userid`),
  KEY `importpost_index` (`importpostid`),
  KEY `dateline` (`dateline`),
  KEY `threadid_2` (`threadid`,`visible`,`dateline`),
  KEY `converted_2_utf8` (`converted_2_utf8`),
  KEY `threadid_visible_dateline` (`threadid`,`visible`,`dateline`,`userid`,`postid`),
  KEY `ipaddress` (`ipaddress`),
  KEY `userid` (`userid`,`parentid`),
  KEY `user_date` (`userid`,`dateline`)
) ENGINE=InnoDB AUTO_INCREMENT=5402802 DEFAULT CHARSET=latin1

Server, with 32GB RAM:

Server version: 10.0.12-MariaDB-1~trusty-wsrep-log mariadb.org binary distribution, wsrep_25.10.r4002

If you need all of the innodb_ variables setting, I can attach that to this post.

Update:

I dropped ALL indexes apart from the primary index, afterwards the result looked like this:

.
.
| 5402697 |      0 |
| 5402759 |      0 |
+---------+--------+
62510 rows in set (29.74 sec)

EXPLAIN SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506;
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | post  | ALL  | NULL          | NULL | NULL    | NULL | 5909836 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

After this I just added one index back to the mix, threadid, the results were the following:

.
.
| 5402697 |      0 |
| 5402759 |      0 |
+---------+--------+
62510 rows in set (11.58 sec)

EXPLAIN SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506;
+------+-------------+-------+------+---------------+----------+---------+-------+--------+-------+
| id   | select_type | table | type | possible_keys | key      | key_len | ref   | rows   | Extra |
+------+-------------+-------+------+---------------+----------+---------+-------+--------+-------+
|    1 | SIMPLE      | post  | ref  | threadid      | threadid | 4       | const | 124622 |       |
+------+-------------+-------+------+---------------+----------+---------+-------+--------+-------+
1 row in set (0.00 sec)

Strange it is, that without any relevant indexes, the full scan only took 29 seconds compared to the 88 seconds using indexes (!).

With only one perfectly tailored index it is still taking 11 seconds by to complete - still far too slow for any real world usage.

Update 2:

I setup MySQL (5.5.38-0ubuntu0.14.04.1 (Ubuntu)) on another server with the exact same hardware configuration and exactly the same database/tables.

The results are nearly the same, first the MyISAM Table:

.
.
| 5401593 |      0 |
| 5401634 |      0 |
+---------+--------+
62510 rows in set (0.14 sec)

And this is the result of the InnoDB table

.
.
| 5397410 |      0 |
| 5397883 |      0 |
+---------+--------+
62510 rows in set (1 min 17.63 sec)

UPDATE 3: the contents of my.cnf

# MariaDB database server configuration file.
#
# You can copy this file to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc_messages_dir = /usr/share/mysql
lc_messages = en_US
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 127.0.0.1
#
# * Fine Tuning
#
max_connections     = 100
connect_timeout     = 5
wait_timeout        = 600
max_allowed_packet  = 16M
thread_cache_size       = 128
sort_buffer_size    = 4M
bulk_insert_buffer_size = 16M
tmp_table_size      = 32M
max_heap_table_size = 32M
#
# * MyISAM
#
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched. On error, make copy and try a repair.
myisam_recover          = BACKUP
key_buffer_size     = 128M
#open-files-limit   = 2000
table_open_cache    = 400
myisam_sort_buffer_size = 512M
concurrent_insert   = 2
read_buffer_size    = 2M
read_rnd_buffer_size    = 1M
#
# * Query Cache Configuration
#
# Cache only tiny result sets, so we can fit more in the query cache.
query_cache_limit       = 128K
query_cache_size        = 64M
# for more write intensive setups, set to DEMAND or OFF
#query_cache_type       = DEMAND
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# we do want to know about network errors and such
log_warnings        = 2
#
# Enable the slow query log to see queries with especially long duration
#slow_query_log[={0|1}]
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 10
#log_slow_rate_limit    = 1000
log_slow_verbosity  = query_plan

#log-queries-not-using-indexes
#log_slow_admin_statements
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#report_host        = master1
#auto_increment_increment = 2
#auto_increment_offset  = 1
log_bin         = /var/log/mysql/mariadb-bin
log_bin_index       = /var/log/mysql/mariadb-bin.index
# not fab for performance, but safer
#sync_binlog        = 1
expire_logs_days    = 10
max_binlog_size         = 100M
# slaves
#relay_log      = /var/log/mysql/relay-bin
#relay_log_index    = /var/log/mysql/relay-bin.index
#relay_log_info_file    = /var/log/mysql/relay-bin.info
#log_slave_updates
#read_only
#
# If applications support it, this stricter sql_mode prevents some
# mistakes like inserting invalid dates etc.
#sql_mode       = NO_ENGINE_SUBSTITUTION,TRADITIONAL
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
default_storage_engine  = InnoDB
# you can't just change log file size, requires special procedure
#innodb_log_file_size   = 50M
innodb_buffer_pool_size = 20G
innodb_log_buffer_size  = 8M
innodb_file_per_table   = 1
innodb_open_files   = 400
innodb_io_capacity  = 400
innodb_flush_method = O_DIRECT
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer      = 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

And the contents of the inno variables:

MariaDB [(none)]> SHOW VARIABLES LIKE 'inno%';
+-------------------------------------------+------------------------+
| Variable_name                             | Value                  |
+-------------------------------------------+------------------------+
| innodb_adaptive_flushing                  | ON                     |
| innodb_adaptive_flushing_lwm              | 10                     |
| innodb_adaptive_hash_index                | ON                     |
| innodb_adaptive_hash_index_partitions     | 1                      |
| innodb_adaptive_max_sleep_delay           | 150000                 |
| innodb_additional_mem_pool_size           | 8388608                |
| innodb_api_bk_commit_interval             | 5                      |
| innodb_api_disable_rowlock                | OFF                    |
| innodb_api_enable_binlog                  | OFF                    |
| innodb_api_enable_mdl                     | OFF                    |
| innodb_api_trx_level                      | 0                      |
| innodb_autoextend_increment               | 64                     |
| innodb_autoinc_lock_mode                  | 1                      |
| innodb_buffer_pool_dump_at_shutdown       | OFF                    |
| innodb_buffer_pool_dump_now               | OFF                    |
| innodb_buffer_pool_filename               | ib_buffer_pool         |
| innodb_buffer_pool_instances              | 8                      |
| innodb_buffer_pool_load_abort             | OFF                    |
| innodb_buffer_pool_load_at_startup        | OFF                    |
| innodb_buffer_pool_load_now               | OFF                    |
| innodb_buffer_pool_populate               | OFF                    |
| innodb_buffer_pool_size                   | 21474836480            |
| innodb_change_buffer_max_size             | 25                     |
| innodb_change_buffering                   | all                    |
| innodb_checksum_algorithm                 | innodb                 |
| innodb_checksums                          | ON                     |
| innodb_cleaner_lsn_age_factor             | high_checkpoint        |
| innodb_cmp_per_index_enabled              | OFF                    |
| innodb_commit_concurrency                 | 0                      |
| innodb_compression_failure_threshold_pct  | 5                      |
| innodb_compression_level                  | 6                      |
| innodb_compression_pad_pct_max            | 50                     |
| innodb_concurrency_tickets                | 5000                   |
| innodb_corrupt_table_action               | assert                 |
| innodb_data_file_path                     | ibdata1:12M:autoextend |
| innodb_data_home_dir                      |                        |
| innodb_disable_sort_file_cache            | OFF                    |
| innodb_doublewrite                        | ON                     |
| innodb_empty_free_list_algorithm          | backoff                |
| innodb_fake_changes                       | OFF                    |
| innodb_fast_shutdown                      | 1                      |
| innodb_file_format                        | Antelope               |
| innodb_file_format_check                  | ON                     |
| innodb_file_format_max                    | Antelope               |
| innodb_file_per_table                     | ON                     |
| innodb_flush_log_at_timeout               | 1                      |
| innodb_flush_log_at_trx_commit            | 1                      |
| innodb_flush_method                       | O_DIRECT               |
| innodb_flush_neighbors                    | 1                      |
| innodb_flushing_avg_loops                 | 30                     |
| innodb_force_load_corrupted               | OFF                    |
| innodb_force_recovery                     | 0                      |
| innodb_foreground_preflush                | exponential_backoff    |
| 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_io_capacity                        | 400                    |
| innodb_io_capacity_max                    | 2000                   |
| innodb_kill_idle_transaction              | 0                      |
| innodb_large_prefix                       | OFF                    |
| innodb_lock_wait_timeout                  | 50                     |
| innodb_locking_fake_changes               | ON                     |
| innodb_locks_unsafe_for_binlog            | OFF                    |
| innodb_log_arch_dir                       | ./                     |
| innodb_log_arch_expire_sec                | 0                      |
| innodb_log_archive                        | OFF                    |
| innodb_log_block_size                     | 512                    |
| innodb_log_buffer_size                    | 8388608                |
| innodb_log_checksum_algorithm             | innodb                 |
| innodb_log_compressed_pages               | ON                     |
| innodb_log_file_size                      | 50331648               |
| innodb_log_files_in_group                 | 2                      |
| innodb_log_group_home_dir                 | ./                     |
| innodb_lru_scan_depth                     | 1024                   |
| innodb_max_bitmap_file_size               | 104857600              |
| innodb_max_changed_pages                  | 1000000                |
| innodb_max_dirty_pages_pct                | 75                     |
| innodb_max_dirty_pages_pct_lwm            | 0                      |
| innodb_max_purge_lag                      | 0                      |
| innodb_max_purge_lag_delay                | 0                      |
| innodb_mirrored_log_groups                | 1                      |
| 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                         | 400                    |
| innodb_optimize_fulltext_only             | OFF                    |
| innodb_page_size                          | 16384                  |
| innodb_print_all_deadlocks                | OFF                    |
| innodb_purge_batch_size                   | 300                    |
| innodb_purge_threads                      | 1                      |
| 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_sched_priority_cleaner             | 19                     |
| innodb_show_locks_held                    | 10                     |
| innodb_show_verbose_locks                 | 0                      |
| innodb_sort_buffer_size                   | 1048576                |
| innodb_spin_wait_delay                    | 6                      |
| innodb_stats_auto_recalc                  | ON                     |
| innodb_stats_method                       | nulls_equal            |
| innodb_stats_on_metadata                  | OFF                    |
| innodb_stats_persistent                   | ON                     |
| innodb_stats_persistent_sample_pages      | 20                     |
| innodb_stats_sample_pages                 | 8                      |
| innodb_stats_transient_sample_pages       | 8                      |
| innodb_status_output                      | OFF                    |
| innodb_status_output_locks                | OFF                    |
| innodb_strict_mode                        | OFF                    |
| innodb_support_xa                         | ON                     |
| innodb_sync_array_size                    | 1                      |
| innodb_sync_spin_loops                    | 30                     |
| innodb_table_locks                        | ON                     |
| innodb_thread_concurrency                 | 0                      |
| innodb_thread_sleep_delay                 | 10000                  |
| innodb_track_changed_pages                | OFF                    |
| innodb_undo_directory                     | .                      |
| innodb_undo_logs                          | 128                    |
| innodb_undo_tablespaces                   | 0                      |
| innodb_use_atomic_writes                  | OFF                    |
| innodb_use_fallocate                      | OFF                    |
| innodb_use_global_flush_log_at_trx_commit | ON                     |
| innodb_use_native_aio                     | ON                     |
| innodb_use_stacktrace                     | OFF                    |
| innodb_use_sys_malloc                     | ON                     |
| innodb_version                            | 5.6.17-65.0            |
| innodb_write_io_threads                   | 4                      |
+-------------------------------------------+------------------------+
143 rows in set (0.02 sec)

The number of cores of the machine is 8, it's a

Intel(R) Xeon(R) CPU E3-1246 v3 @ 3.50GHz as of /proc/cpuinfo

One last note: Ran the queries with the indexes suggested by RolandoMYSQLDBA, and the queries took about 11-20s each. I do want to point out that it is crucial for me (this is the main table of a bulletin board) that the first query about a threadid returns in less than a second, as there are more than 60.000 threads and google-bots constantly crawl these threads.

Answer 1

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.

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 !!!

Answer 2

@RolandMySQLDBA has given the right hint to answer the question. The problem seems to lie in the query and that for the results to be given back, each of those fields has to be read (somehow from the database).

I dropped all indexes but the PRIMARY KEY, and inserted this new index:

ALTER TABLE newbb_innopost ADD INDEX threadid_visible_dateline_index (threadid,visible,dateline,userid,attach,ipaddress);

This link explains what happens here (covering index): The queried fields of the query which are postid,attach can now be extracted from the key itself. That saves checking the real data and using I/O to the hard disk.

All the queries now run with 0.00 seconds.. :)

Thanks a lot all for your help.

Edit: The actual underlying problem is not solved, I just circumvented it with this technique. InnoDB needs some serious fixing in this area.

Answer 3

Although not directly applicable to @jollyroger because he already has the correct setting, but I got a major improvement changing the innodb_buffer_pool_size to 70% of my RAM as explained in Why is myisam slower than Innodb

First MyISAM was slow, but oke. Then InnoDB made things bad, similar to the 100x slower in this question and after changing the setting InnoDB got 10x faster then MyISAM.

My default setting was on 8MB which is far to little.

Answer 4

Based on your both the query and table it seems like you are selectikg data from a time-series table. As such, it may be that the query time is slow because you are inserting simultaneously?

If those two things are true, than may I suggest looking into ScaleDB as an alternative? You will still be on MariaDB, just (maybe) a more appropriate engine.

http://www.scaledb.com - Homepage http://www.scaledb.com/download-form.php - our product

Answer 5

Both engines will run the query much faster with

INDEX(threadid, attach, postid)

This is because it will be a "covering" index, and will operate virtually the same way (using the index BTree).

Also, I will say that this is not possible for either engine on a "cold" server:

62510 rows in set (0.13 sec)

Please use SQL_NO_CACHE whenever running timings -- we don't want the Query cache to pollute the conclusions.

Another fast approach (regarless of I/O caching):

Use InnoDB, and change from PRIMARY KEY (postid) to

PRIMARY KEY(threadid, postid),
INDEX(postid)

The reason is that this will cause all the relevant rows to be adjacent, thereby requiring less I/O, etc. The INDEX(postid) is to keep AUTO_INCREMENT happy. Caveat: This messes with all the secondary keys -- some will be faster, some will be slower.