Mam dość irytujący problem. Chcę użyć INNODB jako mojego głównego silnika bazy danych i zrezygnować z MyISAM, ponieważ potrzebuję tego pierwszego do korzystania z klastra galera do nadmiarowości.

Skopiowałem (opis poniżej) newbb_posttabelę do nowej tabeli o nazwie newbb_innoposti zmieniłem ją na InnoDB. Każda tabela zawiera obecnie 5,390,146wpisy.

Uruchomienie tych selekcji na świeżo uruchomionej bazie danych (więc w tym momencie nie jest wymagane buforowanie!) Baza danych daje następujące wyniki (pomijając pełne dane wyjściowe, proszę pamiętać, że nawet nie pytam bazy danych o sortowanie wyników):

WYBIERZ post.postid, post.attach FROM newbb_post AS post GDZIE post.threadid = 51506;

.
.
| 5401593 | 0 |
| 5401634 | 0 |
+ --------- + -------- +
62510 rzędów w zestawie (0,13 s)

WYBIERZ post.postid, post.attach FROM newbb_innopost AS post GDZIE post.threadid = 51506;
.
.
| 5397410 | 0 |
| 5397883 | 0 |
+ --------- + -------- +
62510 rzędów w zestawie (1 min 22,19 s)

Od 0,13 sekundy do 86,19 sekundy (!)

Zastanawiam się, dlaczego tak się dzieje. Przeczytałem tutaj kilka odpowiedzi na Stackexchange dotyczących InnoDB, a niektóre sugerują zwiększenie innodb_buffer_poolrozmiaru do 80% zainstalowanej pamięci RAM. Nie rozwiąże to problemu polegającego na tym, że wstępne zapytanie do określonego identyfikatora zajmie co najmniej 50x dłużej i zatrzyma cały serwer sieciowy, ustawi w kolejce połączenia i zapytania do bazy danych. Potem pamięć podręczna / bufor mogą się uruchomić, ale w tej bazie danych znajduje się ponad 100 000 wątków, więc jest bardzo prawdopodobne, że pamięć podręczna nigdy nie pomieści wszystkich odpowiednich zapytań do obsłużenia.

Powyższe zapytania są proste (bez złączeń) i wszystkie klucze są używane:

WYBIERZ WYBÓR post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506;
+ ------ + ------------- + ------- + ------ + ------------- ---------------------------------- + ---------- + ---- ----- + ------- + -------- + ------- +
| id | wybierz typ | stół | typ | możliwe klucze | klucz | key_len | ref | rzędy | Extra |
+ ------ + ------------- + ------- + ------ + ------------- ---------------------------------- + ---------- + ---- ----- + ------- + -------- + ------- +
| 1 | PROSTE | post | ref | Threadid, Threadid_2, Threadid_visible_dateline | wątek | 4 | const | 120144 | |
+ ------ + ------------- + ------- + ------ + ------------- ---------------------------------- + ---------- + ---- ----- + ------- + -------- + ------- +

To jest tabela MyISAM:

UTWÓRZ TABELĘ `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',
  `nazwa użytkownika` 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,
  `allowmilie` smallint (6) NOT NULL DEFAULT '0',
  smallshow „showsignature” (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',
  `convert_2_utf8` int (11) NOT NULL,
  `htmlstate` enum ('off', 'on', 'on_nl2br') NOT NULL DEFAULT 'on_nl2br',
  KLUCZ PODSTAWOWY (`postid`),
  KEY `threadid` (` threadid`, `userid`),
  KEY `importpost_index` (` importpostid`),
  KLUCZOWA „linia danych” („linia danych”),
  KLUCZ `threadid_2` (` threadid`, `widoczny`,` linia danych`),
  KEY `przekonwertowany_2_utf8` (` przekonwertowany_2_utf8`),
  KLUCZ `threadid_visible_dateline` (` threadid`, `visible`,` dateline`, `userid`,` postid`),
  KLUCZOWY „ipaddress” („ipaddress”),
  KEY `userid` (` userid`, `parentid`),
  KEY `data_użytkownika` (` identyfikator użytkownika`, `linia danych`)
) SILNIK = MyISAM AUTO_INCREMENT = 5402802 DEFAULT CHARSET = latin1

a to jest tabela InnoDB (jest dokładnie taka sama):

UTWÓRZ TABELĘ `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',
  `nazwa użytkownika` 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,
  `allowmilie` smallint (6) NOT NULL DEFAULT '0',
  smallshow „showsignature” (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',
  `convert_2_utf8` int (11) NOT NULL,
  `htmlstate` enum ('off', 'on', 'on_nl2br') NOT NULL DEFAULT 'on_nl2br',
  KLUCZ PODSTAWOWY (`postid`),
  KEY `threadid` (` threadid`, `userid`),
  KEY `importpost_index` (` importpostid`),
  KLUCZOWA „linia danych” („linia danych”),
  KLUCZ `threadid_2` (` threadid`, `widoczny`,` linia danych`),
  KEY `przekonwertowany_2_utf8` (` przekonwertowany_2_utf8`),
  KLUCZ `threadid_visible_dateline` (` threadid`, `visible`,` dateline`, `userid`,` postid`),
  KLUCZOWY „ipaddress” („ipaddress”),
  KEY `userid` (` userid`, `parentid`),
  KEY `data_użytkownika` (` identyfikator użytkownika`, `linia danych`)
) SILNIK = InnoDB AUTO_INCREMENT = 5402802 DEFAULT CHARSET = latin1

Serwer z 32 GB pamięci RAM:

Wersja serwera: 10.0.12-MariaDB-1 ~ trusty-wsrep-log mariadb.org dystrybucja binarna, wsrep_25.10.r4002

Jeśli potrzebujesz wszystkich ustawień zmiennych innodb_, mogę dołączyć to do tego postu.

Aktualizacja:

Usunąłem WSZYSTKIE indeksy poza indeksem podstawowym, po czym wynik wyglądał następująco:

.
.
| 5402697 | 0 |
| 5402759 | 0 |
+ --------- + -------- +
62510 rzędów w zestawie (29,74 s)

WYBIERZ WYBÓR post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506;
+ ------ + ------------- + ------- + ------ + ------------- - + ------ + --------- + ------ + --------- + ------------- +
| id | wybierz typ | stół | typ | możliwe klucze | klucz | key_len | ref | rzędy | Extra |
+ ------ + ------------- + ------- + ------ + ------------- - + ------ + --------- + ------ + --------- + ------------- +
| 1 | PROSTE | post | WSZYSTKIE | NULL | NULL | NULL | NULL | 5909836 | Używając gdzie |
+ ------ + ------------- + ------- + ------ + ------------- - + ------ + --------- + ------ + --------- + ------------- +
1 rząd w zestawie (0,00 s)

Po tym właśnie dodałem jeden indeks z powrotem do mixu, Threadid, wyniki były następujące:

.
.
| 5402697 | 0 |
| 5402759 | 0 |
+ --------- + -------- +
62510 rzędów w zestawie (11,58 s)

WYBIERZ WYBÓR post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506;
+ ------ + ------------- + ------- + ------ + ------------- - + ---------- + --------- + ------- + -------- + ------- +
| id | wybierz typ | stół | typ | możliwe klucze | klucz | key_len | ref | rzędy | Extra |
+ ------ + ------------- + ------- + ------ + ------------- - + ---------- + --------- + ------- + -------- + ------- +
| 1 | PROSTE | post | ref | wątek | wątek | 4 | const | 124622 | |
+ ------ + ------------- + ------- + ------ + ------------- - + ---------- + --------- + ------- + -------- + ------- +
1 rząd w zestawie (0,00 s)

Dziwne, że bez odpowiednich indeksów pełne skanowanie zajęło tylko 29 sekund w porównaniu z 88 sekundami przy użyciu indeksów (!).

Ukończenie tylko jednego idealnie dopasowanego indeksu zajmuje 11 sekund - nadal jest zbyt wolne, aby można było z niego korzystać w prawdziwym świecie.

Aktualizacja 2:

Instaluję MySQL (5.5.38-0ubuntu0.14.04.1 (Ubuntu)) na innym serwerze z dokładnie taką samą konfiguracją sprzętową i dokładnie tą samą bazą danych / tabelami.

Wyniki są prawie takie same, najpierw tabela MyISAM:

.
.
| 5401593 | 0 |
| 5401634 | 0 |
+ --------- + -------- +
62510 rzędów w zestawie (0,14 s)

A to wynik tabeli InnoDB

.
.
| 5397410 | 0 |
| 5397883 | 0 |
+ --------- + -------- +
62510 rzędów w zestawie (1 min 17,63 s)

AKTUALIZACJA 3: zawartość my.cnf

# Plik konfiguracyjny serwera bazy danych MariaDB.
#
# Możesz skopiować ten plik do jednego z:
# - „/etc/mysql/my.cnf”, aby ustawić opcje globalne,
# - „~ / .my.cnf”, aby ustawić opcje specyficzne dla użytkownika.
# 
# Można użyć wszystkich długich opcji obsługiwanych przez program.
# Uruchom program przy pomocy --help, aby uzyskać listę dostępnych opcji i przy pomocy
# --print-defaults, aby zobaczyć, co faktycznie zrozumie i użyje.
#
# Wyjaśnienia patrz
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# To zostanie przekazane wszystkim klientom mysql
# Zgłoszono, że hasła powinny być dołączone do haczyków / cudzysłowów
# zwłaszcza jeśli zawierają znaki "#" ...
# Pamiętaj o edycji /etc/mysql/debian.cnf podczas zmiany lokalizacji gniazda.
[klient]
port = 3306
socket = /var/run/mysqld/mysqld.sock

# Oto wpisy dotyczące niektórych konkretnych programów
# Poniższe wartości zakładają, że masz przynajmniej 32M pamięci RAM

# To było formalnie znane jako [safe_mysqld]. Obie wersje są obecnie analizowane.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
niezły = 0

[mysqld]
#
# * Podstawowe ustawienia
#
użytkownik = 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
pominąć blokowanie zewnętrzne
#
# Zamiast przeskakiwania w sieci domyślnie jest teraz tylko nasłuchiwanie
# localhost, który jest bardziej kompatybilny i nie jest mniej bezpieczny.
adres powiązania = 127.0.0.1
#
# * Strojenie
#
max_connections = 100
connect_timeout = 5
wait_timeout = 600
max_allowed_packet = 16 mln
thread_cache_size = 128
sort_buffer_size = 4M
bulk_insert_buffer_size = 16 mln
tmp_table_size = 32M
max_heap_table_size = 32M
#
# * MyISAM
#
# To zastępuje skrypt startowy i sprawdza tabele MyISAM w razie potrzeby
# przy pierwszym dotknięciu. W przypadku błędu wykonaj kopię i spróbuj naprawić.
myisam_recover = BACKUP
key_buffer_size = 128 mln
# 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
#
# * Konfiguracja pamięci podręcznej zapytań
#
# Buforuj tylko małe zestawy wyników, abyśmy mogli zmieścić więcej w pamięci podręcznej zapytań.
query_cache_limit = 128 KB
query_cache_size = 64M
#, aby uzyskać więcej ustawień intensywnego zapisu, wybierz WYMAGANIE lub WYŁ
#query_cache_type = DEMAND
#
# * Rejestrowanie i replikacja
#
# Obie lokalizacje zostają obrócone przez cronjob.
# Należy pamiętać, że ten typ dziennika jest zabójcą wydajności.
# Od 5.1 możesz włączyć rejestr w czasie wykonywania!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
#
# Rejestrowanie błędów przechodzi do syslog z powodu /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# chcemy wiedzieć o błędach sieciowych i tym podobnych
log_warnings = 2
#
# Włącz dziennik wolnych zapytań, aby zobaczyć zapytania o szczególnie długim czasie trwania
#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 = plan zapytania

# log-queries-not-using-indexes
#log_slow_admin_statements
#
# Poniższych elementów można użyć jako łatwych do odtworzenia dzienników kopii zapasowych lub do replikacji.
Uwaga: jeśli konfigurujesz slave replikacji, zobacz README.Debian o
# inne ustawienia, które mogą wymagać zmiany.
# 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
# nie jest super pod względem wydajności, ale bezpieczniejszy
#sync_binlog = 1
expire_logs_days = 10
max_binlog_size = 100 mln
# niewolnicy
#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
#tylko czytać
#
# Jeśli aplikacje obsługują tę funkcję, ten bardziej rygorystyczny tryb sql zapobiega niektórym
# błędy, takie jak wstawianie nieprawidłowych dat itp.
#sql_mode = NO_ENGINE_SUBSTITUTION, TRADITIONAL
#
# * InnoDB
#
# InnoDB jest domyślnie włączony z plikiem danych 10 MB w katalogu / var / lib / mysql /.
# Przeczytaj instrukcję, aby uzyskać więcej opcji związanych z InnoDB. Jest wiele!
default_storage_engine = InnoDB
# nie można po prostu zmienić rozmiaru pliku dziennika, wymaga specjalnej procedury
#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
#
# * Funkcjonalność związana z bezpieczeństwem
#
# Przeczytaj także instrukcję, jeśli chcesz chroot!
# chroot = / var / lib / mysql /
#
# Do generowania certyfikatów SSL polecam GUI OpenSSL „tinyca”.
#
# ssl-ca = / etc / mysql / cacert.pem
# ssl-cert = / etc / mysql / server-cert.pem
# ssl-key = / etc / mysql / server-key.pem



[mysqldump]
szybki
nazwy cytatów
max_allowed_packet = 16 mln

[mysql]
# no-auto-rehash # szybszy start mysql, ale bez uzupełniania tabulatorów

[isamchk]
key_buffer = 16 mln

#
# * WAŻNE: Dodatkowe ustawienia, które mogą zastąpić te z tego pliku!
# Pliki muszą kończyć się na „.cnf”, w przeciwnym razie zostaną zignorowane.
#
! includedir /etc/mysql/conf.d/

A zawartość zmiennych inno:

MariaDB [(brak)]> POKAŻ ZMIENNE JAK „inno%”;
+ ------------------------------------------- + ----- ------------------- +
| Zmienna nazwa | Wartość |
+ ------------------------------------------- + ----- ------------------- +
| 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 | WYŁ
| innodb_api_enable_binlog | WYŁ
| innodb_api_enable_mdl | WYŁ
| innodb_api_trx_level | 0 |
| innodb_autoextend_increment | 64 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_dump_at_shutdown | WYŁ
| innodb_buffer_pool_dump_now | WYŁ
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | WYŁ
| innodb_buffer_pool_load_at_startup | WYŁ
| innodb_buffer_pool_load_now | WYŁ
| innodb_buffer_pool_populate | WYŁ
| innodb_buffer_pool_size | 21474836480 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | wszystkie |
| innodb_checksum_algorithm | innodb |
| innodb_checksums | ON |
| innodb_cleaner_lsn_age_factor | high_checkpoint |
| innodb_cmp_per_index_enabled | WYŁ
| 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 Activity | twierdzić |
| innodb_data_file_path | ibdata1: 12M: autoextend |
| innodb_data_home_dir | |
| innodb_disable_sort_file_cache | WYŁ
| innodb_doublewrite | ON |
| innodb_empty_free_list_al algorytm | wycofanie |
| innodb_fake_changes | WYŁ
| innodb_fast_shutdown | 1 |
| innodb_file_format | Antylopa |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antylopa |
| 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_corrupt | WYŁ
| innodb_force_recovery | 0 |
| innodb_foreground_preflush | exponential_backoff |
| innodb_ft_aux_table | |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_enable_diag_print | WYŁ
| 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 | WYŁ
| innodb_lock_wait_timeout | 50 |
| innodb_locking_fake_changes | ON |
| innodb_locks_unsafe_for_binlog | WYŁ
| innodb_log_arch_dir | ./ |
| innodb_log_arch_expire_sec | 0 |
| innodb_log_archive | WYŁ
| innodb_log_block_size | 512 |
| innodb_log_buffer_size | 8388608 |
| innodb_log_checksum_algorithm | innodb |
| innodb_log_compressed_pages | ON |
| innodb_log_file_sile | 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 | WYŁ
| innodb_page_size | 16384 |
| innodb_print_all_deadlocks | WYŁ
| innodb_purge_batch_size | 300 |
| innodb_purge_threads | 1 |
| innodb_random_read_ahead | WYŁ
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_read_only | WYŁ
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | WYŁ
| 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 | WYŁ
| innodb_stats_persistent | ON |
| innodb_stats_persistent_sample_pages | 20 |
| innodb_stats_sample_pages | 8 |
| innodb_stats_transient_sample_pages | 8 |
| innodb_status_output | WYŁ
| innodb_status_output_locks | WYŁ
| innodb_strict_mode | WYŁ
| 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 | WYŁ
| innodb_undo_directory | . |
| innodb_undo_logs | 128 |
| innodb_undo_tablespaces | 0 |
| innodb_use_atomic_writes | WYŁ
| innodb_use_fallocate | WYŁ
| innodb_use_global_flush_log_at_trx_commit | ON |
| innodb_use_native_aio | ON |
| innodb_use_stacktrace | WYŁ
| innodb_use_sys_malloc | ON |
| innodb_version | 5.6.17–65.0 |
| innodb_write_io_threads | 4 |
+ ------------------------------------------- + ----- ------------------- +
143 rzędy w zestawie (0,02 s)

Liczba rdzeni maszyny wynosi 8, to jest

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

Ostatnia uwaga: przeglądałem zapytania z indeksami sugerowanymi przez RolandoMYSQLDBA, a każde z nich zajmowało około 11-20 sekund. Chcę podkreślić, że dla mnie bardzo ważne (jest to główna tabela tablicy ogłoszeń) jest to, że pierwsze zapytanie o wątek powraca w niecałą sekundę, ponieważ istnieje ponad 60 000 wątków, a boty Google stale się czołgają te wątki.

TWOJE ZAPYTANIE

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

Na pierwszy rzut oka zapytanie powinno dotykać tylko 1,1597% (62510 z 5390146) tabeli. Powinno być szybkie, biorąc pod uwagę kluczową dystrybucję wątku 51506.

SPRAWDZENIE AUTENTYCZNOŚCI

Bez względu na to, jakiej wersji MySQL (Oracle, Percona, MariaDB) używasz, żaden z nich nie może walczyć z jednym wrogiem, którego wszyscy mają wspólną: architekturą InnoDB.

INDEKS KLASTROWANY

Należy pamiętać, że do każdego wpisu wątku dołączony jest klucz podstawowy. Oznacza to, że kiedy czytasz z indeksu, musi on przeprowadzić wyszukiwanie klucza podstawowego w ClusteredIndex (wewnętrznie nazwany gen_clust_index) . W ClusteredIndex każda strona InnoDB zawiera zarówno dane, jak i informacje o indeksie PRIMARY KEY. Zobacz mój post Best of MyISAM i InnoDB, aby uzyskać więcej informacji.

WSKAŹNIKI REDUNDACJI

W tabeli jest dużo bałaganu, ponieważ niektóre indeksy mają te same wiodące kolumny. MySQL i InnoDB muszą poruszać się po indeksie, aby dostać się do potrzebnych węzłów BTREE. Powinieneś zredukować ten bałagan, wykonując następujące czynności:

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`)
;

Po co usuwać te indeksy?

• Pierwsze trzy indeksy rozpoczynają się od wątku
• threadid_2i threadid_visible_datelinezacznij od tych samych trzech kolumn
• threadid_visible_dateline nie potrzebuje postid, ponieważ jest to KLUCZ PODSTAWOWY i jest osadzony

Buforowanie BUFORA

Pula buforów InnoDB buforuje dane i strony indeksowe. MyISAM buforuje tylko strony indeksowe.

Tylko w tym obszarze MyISAM nie marnuje czasu na buforowanie danych. To dlatego, że nie jest przeznaczony do buforowania danych. InnoDB buforuje każdą dotkniętą stronę danych i stronę indeksową (i jej babcię). Jeśli pula buforów InnoDB jest zbyt mała, możesz buforować strony, unieważniać strony i usuwać strony w jednym zapytaniu.

UKŁAD TABELI

Możesz ogolić trochę miejsca z rzędu, rozważając importthreadidi importpostid. Masz je jako WIELKIE. Zajmują 16 bajtów w ClusteredIndex na wiersz.

Powinieneś to uruchomić

SELECT importthreadid,importpostid FROM newbb_innopost PROCEDURE ANALYSE();

Spowoduje to zalecenie, jakie typy danych powinny mieć te kolumny dla danego zestawu danych.

WNIOSEK

MyISAM ma o wiele mniej do czynienia niż InnoDB, szczególnie w zakresie buforowania.

Chociaż ujawniłeś ilość pamięci RAM ( 32GB) i wersję MySQL ( Server version: 10.0.12-MariaDB-1~trusty-wsrep-log mariadb.org binary distribution, wsrep_25.10.r4002), w tej układance są jeszcze inne elementy, których nie ujawniłeś

• Ustawienia InnoDB
• Liczba rdzeni
• Inne ustawienia z my.cnf

Jeśli możesz dodać te rzeczy do pytania, mogę dalej opracowywać.

AKTUALIZACJA 28.08.2014 11:27 EDT

Powinieneś zwiększyć wątki

innodb_read_io_threads = 64
innodb_write_io_threads = 16
innodb_log_buffer_size = 256M

Rozważałbym wyłączenie bufora zapytań (zobacz mój ostatni post Dlaczego typ_wyszukiwania_wyszukiwania jest domyślnie wyłączony, zacznij od MySQL 5.6? )

query_cache_size = 0

Chciałbym zachować pulę buforów

innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1

Zwiększ wątki czyszczenia (jeśli robisz DML na wielu tabelach)

innodb_purge_threads = 4

SPRÓBUJ !!!

@RolandMySQLDBA dał właściwą wskazówkę, aby odpowiedzieć na pytanie. Wydaje się, że problem leży w zapytaniu i że aby otrzymać wyniki, każde z tych pól należy odczytać (jakoś z bazy danych).

Usunąłem wszystkie indeksy oprócz PRIMARY KEYi wstawiłem nowy indeks:

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

Ten link wyjaśnia, co się tutaj dzieje ( obejmujący indeks ): Zapytane pola zapytania, które postid,attachmożna teraz wyodrębnić z samego klucza. To oszczędza sprawdzanie rzeczywistych danych i używanie I / O na dysku twardym.

Wszystkie zapytania działają teraz z 0,00 sekund .. :)

Wielkie dzięki za pomoc.

Edycja : Rzeczywisty problem nie został rozwiązany, po prostu obejrzałem go za pomocą tej techniki. InnoDB wymaga poważnych napraw w tym obszarze.

Na podstawie zarówno zapytania, jak i tabeli wydaje się, że wybierasz dane z tabeli szeregów czasowych. W związku z tym może być tak, że czas zapytania jest wolny, ponieważ wstawiasz jednocześnie?

Jeśli te dwie rzeczy są prawdziwe, to czy mogę zasugerować rozważenie ScaleDB jako alternatywy? Nadal będziesz korzystał z MariaDB, po prostu (być może) bardziej odpowiedni silnik.

http://www.scaledb.com - Strona główna http://www.scaledb.com/download-form.php - nasz produkt

Oba silniki będą uruchamiać zapytanie znacznie szybciej

INDEX(threadid, attach, postid)

Wynika to z faktu, że będzie to indeks „zakrywający” i będzie działać praktycznie w ten sam sposób (przy użyciu indeksu BTree).

Powiem też, że nie jest to możliwe dla żadnego silnika na „zimnym” serwerze:

62510 rows in set (0.13 sec)

Używaj go SQL_NO_CACHEzawsze, gdy uruchomione są czasy - nie chcemy, aby pamięć podręczna zapytania zakłócała ​​wnioski.

Kolejne szybkie podejście (bez względu na buforowanie we / wy):

Użyj InnoDB i zmień z PRIMARY KEY (postid)na

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

Powodem jest to, że spowoduje to, że wszystkie odpowiednie wiersze będą sąsiadować, co będzie wymagało mniejszej liczby operacji we / wy itp. Chodzi o INDEX(postid)to, aby być AUTO_INCREMENTszczęśliwym. Zastrzeżenie: bałagan ze wszystkimi kluczami dodatkowymi - niektóre będą szybsze, inne wolniejsze.

Chociaż nie ma bezpośredniego zastosowania do @jollyroger, ponieważ ma już prawidłowe ustawienie, ale uzyskałem znaczną poprawę, zmieniając innodb_buffer_pool_size70% mojej pamięci RAM, jak wyjaśniono w Dlaczego moja wersja jest wolniejsza niż Innodb

Pierwszy MyISAMbył powolny, ale w porządku. Potem InnoDBźle to zrobiło, podobnie do 100x wolniej w tym pytaniu, a po zmianie ustawienia InnoDBbyło 10 razy szybsze MyISAM.

Moje domyślne ustawienie to 8 MB, co jest zdecydowanie za małe.