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.