Início / dba / Perguntas / 75091
Accepted
jollyroger
Asked: 2014-08-28 04:29:18 +0800 CST

Por que SELECTs simples no InnoDB são 100x mais lentos que no MyISAM?

  • 772

Estou com um problema bem chato. Eu quero usar o INNODB como meu principal mecanismo de banco de dados e desistir do MyISAM, pois preciso do primeiro para usar o galera-cluster para redundância.

Copiei (a descrição segue) a newbb_posttabela para uma nova tabela chamada newbb_innoposte alterei para InnoDB. As tabelas atualmente contêm 5,390,146entradas cada.

Executando essas seleções em um banco de dados recém-iniciado (portanto, nenhum armazenamento em cache está envolvido neste momento!) o banco de dados produz os seguintes resultados (omitindo a saída completa, observe que nem peço ao banco de dados para classificar os resultados):

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

.
.
| 5401593 | 0 |
| 5401634 | 0 |
+---------+--------+
62510 linhas no conjunto (0,13 seg)

SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506;
.
.
| 5397410 | 0 |
| 5397883 | 0 |
+---------+--------+
62510 linhas em conjunto (1 min 22,19 seg)

0,13 segundos a 86,19 segundos (!)

Estou me perguntando por que isso está acontecendo. Eu li algumas respostas aqui no Stackexchange envolvendo o InnoDB e algumas estão sugerindo aumentar o innodb_buffer_pooltamanho para 80% da RAM instalada. Isso não resolverá o problema, que a consulta inicial a um ID específico levará pelo menos 50 vezes mais e paralisará todo o servidor da Web, enfileirando conexões e consultas para o banco de dados. Depois disso, o cache/buffer pode ser ativado, mas existem mais de 100.000 threads nesse banco de dados, portanto, é muito provável que o cache nunca mantenha todas as consultas relevantes a serem atendidas.

As consultas acima são simples (sem junções) e todas as chaves são usadas:

EXPLAIN SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506;
+------+-------------+-------+------+------------- -------------------+----------+---- -----+-------+--------+-------+
| identificação | select_type | mesa | tipo | chaves_possíveis | chave | key_len | ref | linhas | Extra |
+------+-------------+-------+------+------------- -------------------+----------+---- -----+-------+--------+-------+
| 1 | SIMPLES | postar | ref | threadid,threadid_2,threadid_visible_dateline | threadid | 4 | const | 120144 | |
+------+-------------+-------+------+------------- -------------------+----------+---- -----+-------+--------+-------+

Esta é a tabela MyISAM:

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',
  texto médio `pagetext`,
  `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',
  CHAVE PRIMÁRIA (`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

e esta é a Tabela InnoDB (é exatamente a mesma):

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',
  texto médio `pagetext`,
  `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',
  CHAVE PRIMÁRIA (`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

Servidor, com 32 GB de RAM:

Versão do servidor: 10.0.12-MariaDB-1~trusty-wsrep-log distribuição binária mariadb.org, wsrep_25.10.r4002

Se você precisar de todas as configurações de variáveis ​​innodb_, posso anexar isso a este post.

Atualizar:

Eu soltei TODOS os índices além do índice primário, depois o resultado ficou assim:

.
.
| 5402697 | 0 |
| 5402759 | 0 |
+---------+--------+
62510 linhas em conjunto (29,74 seg)

EXPLAIN SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506;
+------+-------------+-------+------+------------- --+------+---------+------+---------+------------- +
| identificação | select_type | mesa | tipo | chaves_possíveis | chave | key_len | ref | linhas | Extra |
+------+-------------+-------+------+------------- --+------+---------+------+---------+------------- +
| 1 | SIMPLES | postar | TODOS | NULO | NULO | NULO | NULO | 5909836 | Usando onde |
+------+-------------+-------+------+------------- --+------+---------+------+---------+------------- +
1 linha em conjunto (0,00 seg)

Depois disso, apenas adicionei um índice de volta ao mix, threadid, os resultados foram os seguintes:

.
.
| 5402697 | 0 |
| 5402759 | 0 |
+---------+--------+
62510 linhas no conjunto (11,58 seg)

EXPLAIN SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506;
+------+-------------+-------+------+------------- --+----------+---------+-------+--------+-------+
| identificação | select_type | mesa | tipo | chaves_possíveis | chave | key_len | ref | linhas | Extra |
+------+-------------+-------+------+------------- --+----------+---------+-------+--------+-------+
| 1 | SIMPLES | postar | ref | threadid | threadid | 4 | const | 124622 | |
+------+-------------+-------+------+------------- --+----------+---------+-------+--------+-------+
1 linha em conjunto (0,00 seg)

Estranho é que, sem nenhum índice relevante, a varredura completa levou apenas 29 segundos em comparação com os 88 segundos usando índices (!).

Com apenas um índice perfeitamente adaptado, ele ainda leva 11 segundos para ser concluído - ainda muito lento para qualquer uso no mundo real.

Atualização 2:

Eu configurei o MySQL (5.5.38-0ubuntu0.14.04.1 (Ubuntu)) em outro servidor com exatamente a mesma configuração de hardware e exatamente o mesmo banco de dados/tabelas.

Os resultados são quase os mesmos, primeiro a tabela MyISAM:

.
.
| 5401593 | 0 |
| 5401634 | 0 |
+---------+--------+
62510 linhas em conjunto (0,14 seg)

E este é o resultado da tabela InnoDB

.
.
| 5397410 | 0 |
| 5397883 | 0 |
+---------+--------+
62510 linhas em conjunto (1 min 17,63 seg)

ATUALIZAÇÃO 3: o conteúdo de my.cnf

# Arquivo de configuração do servidor de banco de dados MariaDB.
#
# Você pode copiar este arquivo para um dos seguintes:
# - "/etc/mysql/my.cnf" para definir opções globais,
# - "~/.my.cnf" para definir opções específicas do usuário.
#
# Pode-se usar todas as opções longas que o programa suporta.
# Execute o programa com --help para obter uma lista de opções disponíveis e com
# --print-defaults para ver qual ele realmente entenderia e usaria.
#
# Para explicações veja
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# Isso será passado para todos os clientes mysql
# Foi relatado que as senhas devem ser colocadas entre tiques/aspas
# especialmente se eles contiverem "#" caracteres...
# Lembre-se de editar /etc/mysql/debian.cnf quando mudar a localização do socket.
[cliente]
porta = 3306
socket = /var/run/mysqld/mysqld.sock

# Aqui estão as entradas para alguns programas específicos
# Os valores a seguir pressupõem que você tenha pelo menos 32M de ram

# Isso era formalmente conhecido como [safe_mysqld]. Ambas as versões estão atualmente analisadas.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
legal = 0

[mysqld]
#
# * Configurações básicas
#
usuário = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
porta = 3306
baseadoir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc_messages_dir = /usr/share/mysql
lc_messages = en_US
salto-bloqueio externo
#
# Em vez de pular a rede, o padrão agora é ouvir apenas em
# localhost que é mais compatível e não menos seguro.
endereço de ligação = 127.0.0.1
#
# * Afinação
#
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 = 16 milhões
tmp_table_size = 32M
max_heap_table_size = 32 milhões
#
# * MeuISAM
#
# Isso substitui o script de inicialização e verifica as tabelas MyISAM se necessário
# na primeira vez em que são tocados. Em caso de erro, faça uma cópia e tente um reparo.
myisam_recover = BACKUP
key_buffer_size = 128 milhões
#open-files-limit = 2000
table_open_cache = 400
myisam_sort_buffer_size = 512M
inserção_concorrente = 2
read_buffer_size = 2M
read_rnd_buffer_size = 1M
#
# * Configuração do Cache de Consulta
#
# Cache apenas pequenos conjuntos de resultados, para que possamos encaixar mais no cache de consulta.
query_cache_limit = 128K
query_cache_size = 64M
# para configurações mais intensivas de gravação, defina como DEMAND ou OFF
#query_cache_type = DEMANDA
#
# * Log e Replicação
#
# Ambos os locais são girados pelo cronjob.
# Esteja ciente de que esse tipo de log é um assassino de desempenho.
# A partir do 5.1 você pode habilitar o log em tempo de execução!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
#
# O log de erros vai para o syslog devido a /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# queremos saber sobre erros de rede e tal
log_warnings = 2
#
# Habilite o log de consultas lentas para ver consultas com duração especialmente longa
#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
#
# O seguinte pode ser usado como logs de backup fáceis de reproduzir ou para replicação.
# nota: se você estiver configurando um slave de replicação, veja README.Debian sobre
# outras configurações que você pode precisar alterar.
#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
# não é fabuloso para o desempenho, mas é mais seguro
#sync_binlog = 1
expire_logs_days = 10
max_binlog_size = 100M
# escravos
#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
#somente leitura
#
# Se os aplicativos o suportarem, esse sql_mode mais estrito impede alguns
# erros como inserir datas inválidas etc.
#sql_mode = NO_ENGINE_SUBSTITUTION,TRADITIONAL
#
# * InnoDB
#
# O InnoDB é habilitado por padrão com um arquivo de dados de 10 MB em /var/lib/mysql/.
# Leia o manual para mais opções relacionadas ao InnoDB. Existem muitos!
default_storage_engine = InnoDB
# você não pode simplesmente alterar o tamanho do arquivo de log, requer um procedimento especial
#innodb_log_file_size = 50 milhões
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
#
# * Recursos de segurança
#
# Leia o manual também, se você quiser chroot!
#chroot = /var/lib/mysql/
#
# Para gerar certificados SSL eu recomendo o OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
rápido
nomes de citações
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # início mais rápido do mysql, mas sem conclusão da guia

[isamchk]
key_buffer = 16M

#
# * IMPORTANTE: Configurações adicionais que podem substituir as deste arquivo!
# Os arquivos devem terminar com '.cnf', caso contrário serão ignorados.
#
!incluído em /etc/mysql/conf.d/

E o conteúdo das variáveis ​​inno:

MariaDB [(nenhuma)]> MOSTRAR VARIÁVEIS COMO 'inno%';
+-------------------------------------------+----- -------------------+
| Nome_variável | Valor |
+-------------------------------------------+----- -------------------+
| innodb_adaptive_flushing | LIGADO |
| innodb_adaptive_flushing_lwm | 10 |
| innodb_adaptive_hash_index | LIGADO |
| 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 | DESLIGADO |
| innodb_api_enable_binlog | DESLIGADO |
| innodb_api_enable_mdl | DESLIGADO |
| innodb_api_trx_level | 0 |
| innodb_autoextend_increment | 64 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_dump_at_shutdown | DESLIGADO |
| innodb_buffer_pool_dump_now | DESLIGADO |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | DESLIGADO |
| innodb_buffer_pool_load_at_startup | DESLIGADO |
| innodb_buffer_pool_load_now | DESLIGADO |
| innodb_buffer_pool_populate | DESLIGADO |
| innodb_buffer_pool_size | 21474836480 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | tudo |
| innodb_checksum_algorithm | innodb |
| innodb_checksums | LIGADO |
| innodb_cleaner_lsn_age_factor | high_checkpoint |
| innodb_cmp_per_index_enabled | DESLIGADO |
| 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 | afirmar |
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir | |
| innodb_disable_sort_file_cache | DESLIGADO |
| innodb_doublewrite | LIGADO |
| innodb_empty_free_list_algorithm | recuar |
| innodb_fake_changes | DESLIGADO |
| innodb_fast_shutdown | 1 |
| innodb_file_format | Antílope |
| innodb_file_format_check | LIGADO |
| innodb_file_format_max | Antílope |
| innodb_file_per_table | LIGADO |
| 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 | DESLIGADO |
| innodb_force_recovery | 0 |
| innodb_foreground_preflush | exponencial_backoff |
| innodb_ft_aux_table | |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_enable_diag_print | DESLIGADO |
| innodb_ft_enable_stopword | LIGADO |
| 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 | DESLIGADO |
| innodb_lock_wait_timeout | 50 |
| innodb_locking_fake_changes | LIGADO |
| innodb_locks_unsafe_for_binlog | DESLIGADO |
| innodb_log_arch_dir | ./ |
| innodb_log_arch_expire_sec | 0 |
| innodb_log_archive | DESLIGADO |
| innodb_log_block_size | 512 |
| innodb_log_buffer_size | 8388608 |
| innodb_log_checksum_algorithm | innodb |
| innodb_log_compressed_pages | LIGADO |
| 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 | DESLIGADO |
| innodb_page_size | 16384 |
| innodb_print_all_deadlocks | DESLIGADO |
| innodb_purge_batch_size | 300 |
| innodb_purge_threads | 1 |
| innodb_random_read_ahead | DESLIGADO |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_read_only | DESLIGADO |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | DESLIGADO |
| 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 | LIGADO |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | DESLIGADO |
| innodb_stats_persistent | LIGADO |
| innodb_stats_persistent_sample_pages | 20 |
| innodb_stats_sample_pages | 8 |
| innodb_stats_transient_sample_pages | 8 |
| innodb_status_output | DESLIGADO |
| innodb_status_output_locks | DESLIGADO |
| innodb_strict_mode | DESLIGADO |
| innodb_support_xa | LIGADO |
| innodb_sync_array_size | 1 |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | LIGADO |
| innodb_thread_concurrency | 0 |
| innodb_thread_sleep_delay | 10000 |
| innodb_track_changed_pages | DESLIGADO |
| innodb_undo_directory | . |
| innodb_undo_logs | 128 |
| innodb_undo_tablespaces | 0 |
| innodb_use_atomic_writes | DESLIGADO |
| innodb_use_fallocate | DESLIGADO |
| innodb_use_global_flush_log_at_trx_commit | LIGADO |
| innodb_use_native_aio | LIGADO |
| innodb_use_stacktrace | DESLIGADO |
| innodb_use_sys_malloc | LIGADO |
| innodb_version | 5.6.17-65.0 |
| innodb_write_io_threads | 4 |
+-------------------------------------------+----- -------------------+
143 linhas em conjunto (0,02 seg)

O número de núcleos da máquina é 8, é um

Intel(R) Xeon(R) CPU E3-1246 v3 @ 3.50GHza partir de/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.

mysql performance

5 respostas

  1. Best Answer

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

    • 24

  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.

    • 6

  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.

    • 1

  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

    • 0

  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.

    • 0

relate perguntas