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.
Best Answer
YOUR QUERY
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:
Why strip down these indexes ?
threadid_2andthreadid_visible_datelinestart with the same three columnsthreadid_visible_datelinedoes not need postid since it's the PRIMARY KEY and it's embeddedBUFFER 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
importthreadidandimportpostid. You have them as BIGINTs. They take up 16 bytes in the ClusteredIndex per row.You should run this
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 revealedmy.cnfIf you can add these things to the question, I can further elaborate.
UPDATE 2014-08-28 11:27 EDT
You should increase threading
I would consider disabling the query cache (See my recent post Why query_cache_type is disabled by default start from MySQL 5.6?)
I would preserve the Buffer Pool
Increase purge threads (if you do DML on multiple tables)
GIVE IT A TRY !!!