Это сообщение от Джастина Суонхарта из MySQL Performance Blog.
До сих пор большинство тестов, опубликованных о MySQL 5.6, использовали рабочую нагрузку sysbench OLTP. Я хотел протестировать набор запросов, которые, в отличие от sysbench, используют соединения. Я также хотел получить легко воспроизводимый набор данных, который более богат, чем простая таблица sysbench. Тест Star Schema Benchmark (SSB) кажется идеальным для этого.
Я не собирался концентрироваться на производительности отдельных запросов в этом посте, но вместо этого намеревался сосредоточиться только на общем времени ответа для ответов на все запросы в тесте производительности. Однако я получил некоторые странные результаты, которые показали, что MySQL 5.6.10 намного медленнее, чем MySQL 5.5.30, даже с одним подключением. Я чувствовал, что эти результаты требуют более глубокого расследования, поэтому я провел некоторое исследование и подробно изложил свои выводы здесь.
Несколько замечаний:
я протестировал два сценария: буферный пул, намного меньший, чем набор данных (размер по умолчанию 128 МБ, что составляет 1/8 от объема данных), и я также тестировал буферный пул 4G, который больше, чем данные. Очень маленький тюнинг был сделан. Цель состояла в том, чтобы увидеть, как MySQL 5.6 работает «из коробки» по сравнению с 5.5.30 с настройками по умолчанию. Настройки не по умолчанию пытались глубже понять различия в производительности и описаны в посте.
Этот пост не является окончательным выводом о innodb_old_blocks_pct или innodb_old_blocks_time. Это подчеркивает, как набор данных, намного больший, чем буферный пул, может работать хуже при innodb_old_blocks_time = 1000, но, как я уже сказал, это требует дальнейшего изучения. Один конкретный момент исследования, которому необходимо следовать, включая тестирование innodb_old_blocks_time = 1000 на MySQL 5.5.30 и тестирование нескольких буферных пулов на MySQL 5.5.30. Наконец, MySQL 5.6.10 имеет много дополнительных опций настройки, которые необходимо изучить (MRR, BKA, ICP и т. Д.), Прежде чем делать дальнейшие выводы. Это будет тема дальнейших сообщений в блоге.
Детали теста:
В SSB используется генератор данных, который выдает данные для схемы типа «звезда». Схемы типа «звезда» обычно используются для аналитики, поскольку с их помощью чрезвычайно легко создавать запросы. Также очень легко определить куб OLAP по схеме «звезда», поэтому они популярны для использования с такими инструментами, как Mondrian, а также для интеллектуального анализа данных. Я написал более раннее сообщение в блоге, которое описывает различия между основными типами схем.
- Я использовал набор данных SSB с масштабным коэффициентом 1. Масштабный коэффициент 1 дает 587 МБ необработанных данных, в основном в одной таблице ( линейный порядок) .
- Каждый из 13 запросов выполнялся последовательно в одном соединении
- Я изменил запросы, чтобы использовать синтаксис ANSI JOIN. Других изменений в запросах не было.
Тестовая среда
- Версии MySQL, используемые на момент написания этой статьи, — это 5.5.30 и 5.6.10, каждая из которых — GA, когда она была написана.
- Я скомпилировал оба сервера из исходного кода (cmake -gui.; Make; make install)
- Единственными изменениями по умолчанию было то, что оба сервера компилировались без PERFORMANCE_SCHEMA, а пути уникальны для basedir и datadir.
- Я протестировал три конфигурации:
- Конфиг 1: Конфигурация по умолчанию для MySQL 5.5 и MySQL 5.6, без настройки вообще
- Конфиг 2: MySQL 5.6 со всеми настройками по умолчанию, кроме innodb_old_blocks_time = 0
- Конфиг 3: MySQL 5.5 и 5.6 с буферным пулом 4G вместо 128M по умолчанию
Обоснование:
- Так как O_DIRECT не используется по умолчанию, кеш файловой системы даст лучшую производительность чтения после первого запуска (но не так хорошо, как пул теплых буферов)
- Таким образом, результаты, помеченные как COLD, являются результатами после перезагрузки сервера, когда кэш FS холодный
- Остальные результаты выполняются без перезапуска сервера. Для размера по умолчанию BP это означает, что кэш FS теплый. Для 4G BP, BP полностью тепло.
- Идея здесь состоит в том, чтобы проверить ситуацию, когда буферный пул меньше, чем данные, и IO медленный (когда кэш FS холодный, происходит IO для медленной подсистемы IO)
- Повторные прогоны тестируют пул буферов, который меньше, чем данные, но базовый IO работает быстро (горячий кэш FS значительно снижает стоимость IO)
- И, наконец, тестирование с буферным пулом 4G показывает, как система работает, когда данные полностью помещаются в буферный пул (без повторного ввода-вывода)
Тестовый сервер:
- Intel Core i970-3,20 ГГц. 12 логических ядер (шесть физических ядер).
- 12 ГБ памяти
- Массив RAID 10 с 10 дисками 7200 об / мин и 512 МБ кэш-памяти с обратной записью
Тест Star Schema — коэффициент масштабирования 1 — Mysql 5.5 против 5.6
время отклика в секундах (чем ниже, тем лучше)
| Версия | буфер | Холодный | Run1 | RUN2 | RUN3 |
|---|---|---|---|---|---|
| 5.5.30 | 128M | 361,49 | 189,29 | 189,34 | 189,40 |
| 5.6.10 | 128M | 362,31 | 324,25 | 320,74 | 318,84 |
| 5.6.10 (innodb_old_blocks_time = 0) | 128M | 349,24 | 178,80 | 178,55 | 179,07 |
| 5.5.30 | 4G | 200,87 | 20,53 | 20,36 | 20,35 |
| 5.6.10 | 4G | 195,33 | 14,41 | 14,45 | 14,61 |
Я начал с запуска теста для MySQL 5.5.30. На выполнение всех 13 запросов потребовалось 361,49 секунды. Затем я повторил пробег еще три раза. Скорость очень стабильна, всего несколько десятых секунды на пробег. Затем я перезагрузил машину и запустил 5.6.10. Я запустил тест, и, к моему удивлению, MySQL 5.6.10 не стал намного быстрее во время повторных прогонов по сравнению с первоначальным холодным прогоном. Я остановил сервер MySQL 5.6, перезагрузился и снова проверил. Та же проблема. Это сильно отличалось от MySQL 5.5.30, который значительно лучше работает при повторных прогонах.
Чтобы убедиться, что это не проблема с диском, я указал MySQL 5.6.10 на каталог данных MySQL 5.5.30. Скорость была практически одинаковой. Я провел дополнительное исследование и определил, что во время выполнения MySQL 5.6 был более низкий коэффициент использования пула буферов, и, как следствие, MySQL 5.6.10 выполнял больше операций ввода-вывода. Чтобы убедиться, что это действительно проблема, я решил сравнить производительность с буферным пулом, намного превышающим размер данных, поэтому я настроил сервер с буферным пулом 4 ГБ. Я протестировал обе версии, и, как вы можете видеть выше, MySQL 5.6 превзошел MySQL 5.5.30 с большим буферным пулом.
Почему MySQL 5.6.10 с тестом настроек по умолчанию значительно медленнее, чем MySQL 5.5.30 при повторных запусках?
Я подумал о различиях по умолчанию между MySQL 5.5 и MySQL 5.6, и сразу пришло в голову innodb_old_blocks_time . Плагин InnoDB представил innodb_old_blocks_time, чтобы помочь управлять поведением нового механизма разделения LRU, который был реализован в плагине. В оригинальном InnoDB LRU был реализован как классический LRU, который подвергается «загрязнению» при полном сканировании таблицы. В классическом LRU полное сканирование таблицы выталкивает важные горячие страницы из пула буферов часто для нечастого сканирования, такого как резервное копирование или отчет. В системе OLTP это может иметь очень негативные последствия для производительности .
Плагин пытается решить эту проблему, разбивая LRU на горячие и холодные секции. Когда страница сначала читается в буферный пул, она сначала помещается в верхнюю часть холодной секции LRU, где она начинает естественным образом стареть. Если к странице снова коснуться холодной части, она перемещается в верхнюю часть горячей части.
Это звучит хорошо в теории, но на практике это проблематично. Как правило, происходит полное сканирование таблицы доступа к таблице по первичному ключу. Это вынуждает механизм хранения обращаться к одной и той же странице несколько раз подряд. Это неизменно перемещает страницу в горячую область, побеждая разделение. Чтобы этого не случилось, была введена другая переменная innodb_old_blocks_time .
Innodb_old_blocks_time контролирует, как долго страница должна находиться в холодной части LRU, прежде чем она будет разрешена для перемещения в горячую часть. В MySQL 5.5 и более ранних версиях innodb_old_blocks_time по умолчанию установлено значение 0 (ноль), что означает, что страницы быстро перемещаются из холодной части в горячую часть, поскольку они должны оставаться на холодном LRU в течение нуля миллисекунд, прежде чем они смогут перейти на горячую. список. В MySQL 5.6 значение по умолчанию innodb_old_blocks_time изменено на 1000. Местоположение, в котором страница первоначально помещается в LRU, определяется innodb_old_blocks_pct. Значение по умолчанию в обеих версиях — 38, что составляет 3/8 пула буферов.
Для этой рабочей нагрузки с небольшим буферным пулом (буферный пул меньше, чем рабочий набор), имеющим innodb_old_blocks_time = 1000, кажется, вызывает серьезное снижение производительности. Новый параметр изменяет, какие страницы остаются в пуле буферов, а какие устаревают.
Узнайте, почему innodb_old_blocks_time меняет производительность?
Каждый «полет» запросов представляет собой набор детализированных запросов для поиска аномалии. Я собираюсь сосредоточиться на первом запросе, который использует только одно соединение. Поскольку это удобно для запроса только с одним объединением, я проверил производительность запроса с объединением в обоих направлениях.
Объясните для запроса Q1.1:
mysql> select straight_join sum(lo_extendedprice*lo_discount) as revenue
from lineorder join dim_date on lo_orderdatekey = d_datekey
where d_year = 1993 and lo_discount between 1 and 3 and lo_quantity < 25;
+--------------+
| revenue |
+--------------+
| 446268068091 |
+--------------+
1 row in set (33.94 sec)
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: lineorder
type: ALL
possible_keys: LO_OrderDateKey
key: NULL
key_len: NULL
ref: NULL
rows: 5996539
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: dim_date
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: ssb.lineorder.LO_OrderDateKey
rows: 1
Extra: Using where
2 rows in set (0.00 sec)
После выполнения запроса посмотрите, сколько страниц было прочитано с диска и сколько запросов страниц было:
mysql> select * from information_schema.global_status where variable_name like '%innodb%read%';
+---------------------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------------------------------+----------------+
| INNODB_BUFFER_POOL_READ_AHEAD_RND | 0 |
| INNODB_BUFFER_POOL_READ_AHEAD | 38392 |
| INNODB_BUFFER_POOL_READ_AHEAD_EVICTED | 0 |
| INNODB_BUFFER_POOL_READ_REQUESTS | 6731100 |
| INNODB_BUFFER_POOL_READS | 570 |
| INNODB_DATA_PENDING_READS | 0 |
| INNODB_DATA_READ | 640536576 |
| INNODB_DATA_READS | 38972 |
| INNODB_PAGES_READ | 38961 |
| INNODB_ROWS_READ | 6611119 |
+---------------------------------------+----------------+
10 rows in set (0.00 sec)
Here are the contents of the buffer pool in pages afterwards:
mysql> select sq.*, pages / (@@innodb_buffer_pool_size / 16384) * 100 pct_buffer_pool
from (
select table_name, index_name, count(*) pages, sum(is_old='YES') old,
count(*) - sum(is_old='YES') hot, sum(number_records) records
from information_schema.innodb_buffer_page_lru
where table_name like '%ssb%' group by 1,2
) sq
order by pct_buffer_pool desc;
+-------------------+------------------+-------+------+------+---------+-----------------+
| table_name | index_name | pages | old | hot | records | pct_buffer_pool |
+-------------------+------------------+-------+------+------+---------+-----------------+
| `ssb`.`lineorder` | GEN_CLUST_INDEX | 6909 | 2559 | 4350 | 1083172 | 84.3384 |
| `ssb`.`lineorder` | LO_PartKey | 17 | 0 | 17 | 9979 | 0.2075 |
| `ssb`.`lineorder` | LO_CommitDateKey | 17 | 0 | 17 | 10776 | 0.2075 |
| `ssb`.`lineorder` | LO_OrderDateKey | 17 | 0 | 17 | 10376 | 0.2075 |
| `ssb`.`dim_date` | PRIMARY | 17 | 0 | 17 | 2481 | 0.2075 |
| `ssb`.`lineorder` | LO_CustKey | 16 | 0 | 16 | 8616 | 0.1953 |
| `ssb`.`lineorder` | LO_OrderKey | 16 | 0 | 16 | 10943 | 0.1953 |
| `ssb`.`lineorder` | LO_SuppKey | 15 | 0 | 15 | 11466 | 0.1831 |
+-------------------+------------------+-------+------+------+---------+-----------------+
8 rows in set (0.12 sec)
And the Innodb stats:
mysql> select * from information_schema.innodb_buffer_pool_stats\G
*************************** 1. row ***************************
POOL_ID: 0
POOL_SIZE: 8191
FREE_BUFFERS: 1024
DATABASE_PAGES: 7162
OLD_DATABASE_PAGES: 2623
MODIFIED_DATABASE_PAGES: 0
PENDING_DECOMPRESS: 0
PENDING_READS: 0
PENDING_FLUSH_LRU: 0
PENDING_FLUSH_LIST: 0
PAGES_MADE_YOUNG: 3
PAGES_NOT_MADE_YOUNG: 4824154
PAGES_MADE_YOUNG_RATE: 0
PAGES_MADE_NOT_YOUNG_RATE: 0
NUMBER_PAGES_READ: 38960
NUMBER_PAGES_CREATED: 0
NUMBER_PAGES_WRITTEN: 1
PAGES_READ_RATE: 0
PAGES_CREATE_RATE: 0
PAGES_WRITTEN_RATE: 0
NUMBER_PAGES_GET: 6731253
HIT_RATE: 0
YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
NUMBER_PAGES_READ_AHEAD: 38457
NUMBER_READ_AHEAD_EVICTED: 0
READ_AHEAD_RATE: 0
READ_AHEAD_EVICTED_RATE: 0
LRU_IO_TOTAL: 431
LRU_IO_CURRENT: 0
UNCOMPRESS_TOTAL: 0
UNCOMPRESS_CURRENT: 0
1 row in set (0.00 sec)
Теперь сравните разницу с innodb_old_blocks_time = 0;
mysql> set global innodb_old_blocks_time=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select straight_join sum(lo_extendedprice*lo_discount) as revenue
from lineorder join dim_date on lo_orderdatekey = d_datekey
where d_year = 1993 and lo_discount between 1 and 3 and lo_quantity < 25\G
*************************** 1. row ***************************
revenue: 446268068091
1 row in set (7.81 sec)
mysql> select * from information_schema.global_status where variable_name like '%innodb%read%';
+---------------------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------------------------------+----------------+
| INNODB_BUFFER_POOL_READ_AHEAD_RND | 0 |
| INNODB_BUFFER_POOL_READ_AHEAD | 38461 |
| INNODB_BUFFER_POOL_READ_AHEAD_EVICTED | 0 |
| INNODB_BUFFER_POOL_READ_REQUESTS | 6731687 |
| INNODB_BUFFER_POOL_READS | 550 |
| INNODB_DATA_PENDING_READS | 0 |
| INNODB_DATA_READ | 641339392 |
| INNODB_DATA_READS | 39021 |
| INNODB_PAGES_READ | 39010 |
| INNODB_ROWS_READ | 6611119 |
+---------------------------------------+----------------+
10 rows in set (0.00 sec)
mysql> select sq.*, pages / (@@innodb_buffer_pool_size / 16384) * 100 pct_buffer_pool
from (
select table_name, index_name, count(*) pages, sum(is_old='YES') old,
count(*) - sum(is_old='YES') hot, sum(number_records) records
from information_schema.innodb_buffer_page_lru
where table_name like '%ssb%' group by 1,2
) sq
order by pct_buffer_pool desc;
+-------------------+-----------------+-------+------+------+---------+-----------------+
| table_name | index_name | pages | old | hot | records | pct_buffer_pool |
+-------------------+-----------------+-------+------+------+---------+-----------------+
| `ssb`.`lineorder` | GEN_CLUST_INDEX | 7085 | 2547 | 4538 | 1104291 | 86.4868 |
| `ssb`.`dim_date` | PRIMARY | 17 | 17 | 0 | 2481 | 0.2075 |
+-------------------+-----------------+-------+------+------+---------+-----------------+
2 rows in set (0.11 sec)
So there is more of lineorder in the buffer pool and the other secondary indexes have been pushed out of the buffer pool.
mysql> select * from information_schema.innodb_buffer_pool_stats\G
*************************** 1. row ***************************
POOL_ID: 0
POOL_SIZE: 8192
FREE_BUFFERS: 1024
DATABASE_PAGES: 7163
OLD_DATABASE_PAGES: 2624
MODIFIED_DATABASE_PAGES: 0
PENDING_DECOMPRESS: 0
PENDING_READS: 0
PENDING_FLUSH_LRU: 0
PENDING_FLUSH_LIST: 0
PAGES_MADE_YOUNG: 29501
PAGES_NOT_MADE_YOUNG: 0
PAGES_MADE_YOUNG_RATE: 951.6144640495468
PAGES_MADE_NOT_YOUNG_RATE: 0
NUMBER_PAGES_READ: 39009
NUMBER_PAGES_CREATED: 0
NUMBER_PAGES_WRITTEN: 1
PAGES_READ_RATE: 1249.8306506241734
PAGES_CREATE_RATE: 0
PAGES_WRITTEN_RATE: 0.032257023966968806
NUMBER_PAGES_GET: 6731790
HIT_RATE: 995
YOUNG_MAKE_PER_THOUSAND_GETS: 4
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
NUMBER_PAGES_READ_AHEAD: 38459
NUMBER_READ_AHEAD_EVICTED: 0
READ_AHEAD_RATE: 1240.5728847456533
READ_AHEAD_EVICTED_RATE: 0
LRU_IO_TOTAL: 531
LRU_IO_CURRENT: 0
UNCOMPRESS_TOTAL: 0
UNCOMPRESS_CURRENT: 0
1 row in set (0.01 sec)
Вот разница между innodb_buffer_pool_stats рядом:
INNODB_OLD_BLOCKS_TIME=0 INNODB_OLD_BLOCKS_TIME=1000
*************************** 1. row ************************************************ 1. row *******
POOL_ID: 0 * POOL_ID: 0
POOL_SIZE: 8192 * POOL_SIZE: 8191
FREE_BUFFERS: 1024 * FREE_BUFFERS: 1024
DATABASE_PAGES: 7163 * DATABASE_PAGES: 7162
OLD_DATABASE_PAGES: 2624 * OLD_DATABASE_PAGES: 2623
MODIFIED_DATABASE_PAGES: 0 * MODIFIED_DATABASE_PAGES: 0
PENDING_DECOMPRESS: 0 * PENDING_DECOMPRESS: 0
PENDING_READS: 0 * PENDING_READS: 0
PENDING_FLUSH_LRU: 0 * PENDING_FLUSH_LRU: 0
PENDING_FLUSH_LIST: 0 * PENDING_FLUSH_LIST: 0
PAGES_MADE_YOUNG: 29501 * PAGES_MADE_YOUNG: 3
PAGES_NOT_MADE_YOUNG: 0 * PAGES_NOT_MADE_YOUNG: 4824154
PAGES_MADE_YOUNG_RATE: 951.6144640495468 * PAGES_MADE_YOUNG_RATE: 0
PAGES_MADE_NOT_YOUNG_RATE: 0 * PAGES_MADE_NOT_YOUNG_RATE: 0
NUMBER_PAGES_READ: 39009 * NUMBER_PAGES_READ: 38960
NUMBER_PAGES_CREATED: 0 * NUMBER_PAGES_CREATED: 0
NUMBER_PAGES_WRITTEN: 1 * NUMBER_PAGES_WRITTEN: 1
PAGES_READ_RATE: 1249.8306506241734 * PAGES_READ_RATE: 0
PAGES_CREATE_RATE: 0 * PAGES_CREATE_RATE: 0
PAGES_WRITTEN_RATE: 0.032257023966968806 * PAGES_WRITTEN_RATE: 0
NUMBER_PAGES_GET: 6731790 * NUMBER_PAGES_GET: 6731253
HIT_RATE: 995 * HIT_RATE: 0
YOUNG_MAKE_PER_THOUSAND_GETS: 4 * YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0 *NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
NUMBER_PAGES_READ_AHEAD: 38459 * NUMBER_PAGES_READ_AHEAD: 38457
NUMBER_READ_AHEAD_EVICTED: 0 * NUMBER_READ_AHEAD_EVICTED: 0
READ_AHEAD_RATE: 1240.5728847456533 * READ_AHEAD_RATE: 0
READ_AHEAD_EVICTED_RATE: 0 * READ_AHEAD_EVICTED_RATE: 0
LRU_IO_TOTAL: 531 * LRU_IO_TOTAL: 431
LRU_IO_CURRENT: 0 * LRU_IO_CURRENT: 0
UNCOMPRESS_TOTAL: 0 * UNCOMPRESS_TOTAL: 0
UNCOMPRESS_CURRENT: 0 UNCOMPRESS_CURRENT: 0
Как и было обещано, вот результаты объединения таблиц в другом направлении
mysql> select straight_join sum(lo_extendedprice*lo_discount) as revenue
from dim_date join lineorder on lo_orderdatekey = d_datekey
where d_year = 1993 and lo_discount between 1 and 3
and lo_quantity < 25\G
*************************** 1. row ***************************
revenue: 446268068091
1 row in set (22.54 sec)
mysql> explain select straight_join sum(lo_extendedprice*lo_discount) as revenue
-> from dim_date join lineorder on lo_orderdatekey = d_datekey
-> where d_year = 1993 and lo_discount between 1 and 3
-> and lo_quantity < 25\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: dim_date
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 2704
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: lineorder
type: ref
possible_keys: LO_OrderDateKey
key: LO_OrderDateKey
key_len: 4
ref: ssb.dim_date.D_DateKey
rows: 2837
Extra: Using where
2 rows in set (0.00 sec)
mysql> select * from information_schema.global_status where variable_name like '%innodb%read%';
+---------------------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------------------------------+----------------+
| INNODB_BUFFER_POOL_READ_AHEAD_RND | 0 |
| INNODB_BUFFER_POOL_READ_AHEAD | 0 |
| INNODB_BUFFER_POOL_READ_AHEAD_EVICTED | 0 |
| INNODB_BUFFER_POOL_READ_REQUESTS | 3776369 |
| INNODB_BUFFER_POOL_READS | 191571 |
| INNODB_DATA_PENDING_READS | 0 |
| INNODB_DATA_READ | 3140882432 |
| INNODB_DATA_READS | 191581 |
| INNODB_PAGES_READ | 191570 |
| INNODB_ROWS_READ | 910844 |
+---------------------------------------+----------------+
10 rows in set (0.01 sec)
mysql> select sq.*, pages / ((@@innodb_buffer_pool_size / 16384)) * 100 pct_buffer_pool from (select table_name, index_name, count(*) pages, sum(is_old='YES') old, count(*) - sum(is_old='YES') hot, sum(number_records) records from information_schema.innodb_buffer_page_lru where table_name like '%ssb%' group by 1,2) sq order by pct_buffer_pool desc;
+-------------------+------------------+-------+------+------+---------+-----------------+
| table_name | index_name | pages | old | hot | records | pct_buffer_pool |
+-------------------+------------------+-------+------+------+---------+-----------------+
| `ssb`.`lineorder` | GEN_CLUST_INDEX | 6001 | 2095 | 3906 | 964974 | 73.2544 |
| `ssb`.`lineorder` | LO_OrderDateKey | 31 | 28 | 3 | 18223 | 0.3784 |
| `ssb`.`dim_date` | PRIMARY | 17 | 11 | 6 | 2414 | 0.2075 |
| `ssb`.`lineorder` | LO_OrderKey | 17 | 17 | 0 | 11320 | 0.2075 |
| `ssb`.`lineorder` | LO_PartKey | 17 | 17 | 0 | 10095 | 0.2075 |
| `ssb`.`lineorder` | LO_CustKey | 17 | 17 | 0 | 9874 | 0.2075 |
| `ssb`.`lineorder` | LO_CommitDateKey | 16 | 16 | 0 | 10775 | 0.1953 |
| `ssb`.`lineorder` | LO_SuppKey | 16 | 16 | 0 | 11879 | 0.1953 |
+-------------------+------------------+-------+------+------+---------+-----------------+
8 rows in set (0.11 sec)
mysql> select * from information_schema.innodb_buffer_pool_stats\G
*************************** 1. row ***************************
POOL_ID: 0
POOL_SIZE: 8192
FREE_BUFFERS: 1024
DATABASE_PAGES: 6175
OLD_DATABASE_PAGES: 2259
MODIFIED_DATABASE_PAGES: 0
PENDING_DECOMPRESS: 0
PENDING_READS: 0
PENDING_FLUSH_LRU: 0
PENDING_FLUSH_LIST: 0
PAGES_MADE_YOUNG: 62
PAGES_NOT_MADE_YOUNG: 2054952
PAGES_MADE_YOUNG_RATE: 1.0508296469551364
PAGES_MADE_NOT_YOUNG_RATE: 34829.104591447605
NUMBER_PAGES_READ: 191834
NUMBER_PAGES_CREATED: 0
NUMBER_PAGES_WRITTEN: 1
PAGES_READ_RATE: 3246.91106930391
PAGES_CREATE_RATE: 0
PAGES_WRITTEN_RATE: 0.01694886527346994
NUMBER_PAGES_GET: 3777151
HIT_RATE: 950
YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 544
NUMBER_PAGES_READ_AHEAD: 0
NUMBER_READ_AHEAD_EVICTED: 0
READ_AHEAD_RATE: 0
READ_AHEAD_EVICTED_RATE: 0
LRU_IO_TOTAL: 186940
LRU_IO_CURRENT: 0
UNCOMPRESS_TOTAL: 0
UNCOMPRESS_CURRENT: 0
1 row in set (0.00 sec)
mysql> select straight_join sum(lo_extendedprice*lo_discount) as revenue
from dim_date join lineorder on lo_orderdatekey = d_datekey
where d_year = 1993 and lo_discount between 1 and 3 and lo_quantity < 25\G
*************************** 1. row ***************************
revenue: 446268068091
1 row in set (12.36 sec)
mysql> select * from information_schema.global_status where variable_name like '%innodb%read%';
+---------------------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------------------------------+----------------+
| INNODB_BUFFER_POOL_READ_AHEAD_RND | 0 |
| INNODB_BUFFER_POOL_READ_AHEAD | 0 |
| INNODB_BUFFER_POOL_READ_AHEAD_EVICTED | 0 |
| INNODB_BUFFER_POOL_READ_REQUESTS | 3811806 |
| INNODB_BUFFER_POOL_READS | 186407 |
| INNODB_DATA_PENDING_READS | 0 |
| INNODB_DATA_READ | 3056275456 |
| INNODB_DATA_READS | 186417 |
| INNODB_PAGES_READ | 186406 |
| INNODB_ROWS_READ | 910844 |
+---------------------------------------+----------------+
10 rows in set (0.00 sec)
mysql> select sq.*, pages / ((@@innodb_buffer_pool_size / 16384)) * 100 pct_buffer_pool
from (
select table_name, index_name, count(*) pages,
sum(is_old='YES') old, count(*) - sum(is_old='YES') hot,
sum(number_records) records
from information_schema.innodb_buffer_page_lru
where table_name like '%ssb%'
group by 1,2
) sq
order by pct_buffer_pool desc;
+-------------------+-----------------+-------+------+------+---------+-----------------+
| table_name | index_name | pages | old | hot | records | pct_buffer_pool |
+-------------------+-----------------+-------+------+------+---------+-----------------+
| `ssb`.`lineorder` | GEN_CLUST_INDEX | 6980 | 2563 | 4417 | 1119893 | 85.2051 |
| `ssb`.`lineorder` | LO_OrderDateKey | 47 | 17 | 30 | 30637 | 0.5737 |
| `ssb`.`dim_date` | PRIMARY | 12 | 0 | 12 | 1841 | 0.1465 |
+-------------------+-----------------+-------+------+------+---------+-----------------+
3 rows in set (0.12 sec)
mysql> select * from information_schema.innodb_buffer_pool_stats\G
*************************** 1. row ***************************
POOL_ID: 0
POOL_SIZE: 8192
FREE_BUFFERS: 1024
DATABASE_PAGES: 7047
OLD_DATABASE_PAGES: 2581
MODIFIED_DATABASE_PAGES: 0
PENDING_DECOMPRESS: 0
PENDING_READS: 0
PENDING_FLUSH_LRU: 0
PENDING_FLUSH_LIST: 0
PAGES_MADE_YOUNG: 194023
PAGES_NOT_MADE_YOUNG: 0
PAGES_MADE_YOUNG_RATE: 4850.4537386565335
PAGES_MADE_NOT_YOUNG_RATE: 0
NUMBER_PAGES_READ: 186422
NUMBER_PAGES_CREATED: 0
NUMBER_PAGES_WRITTEN: 1
PAGES_READ_RATE: 4653.858653533662
PAGES_CREATE_RATE: 0
PAGES_WRITTEN_RATE: 0.02499937501562461
NUMBER_PAGES_GET: 3811961
HIT_RATE: 952
YOUNG_MAKE_PER_THOUSAND_GETS: 50
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
NUMBER_PAGES_READ_AHEAD: 0
NUMBER_READ_AHEAD_EVICTED: 0
READ_AHEAD_RATE: 0
READ_AHEAD_EVICTED_RATE: 0
LRU_IO_TOTAL: 186024
LRU_IO_CURRENT: 0
UNCOMPRESS_TOTAL: 0
UNCOMPRESS_CURRENT: 0
1 row in set (0.00 sec)
Наконец, я собрал информацию ПОКАЗАТЬ ПРОФИЛИ для более быстрого направления соединения (факт -> измерение)
select p1.seq, p1.state state, p1.duration, p2.duration, p1.cpu_user + p1.cpu_system p1_cpu, p2.cpu_user + p2.cpu_system p2_cpu, p1.context_voluntary + p1.context_involuntary p1_cs, p2.context_voluntary + p2.context_involuntary p2_cs, p1.block_ops_in + p1.block_ops_out p1_block_ops, p2.block_ops_in + p2.block_ops_out p2_block_ops, p1.page_faults_major + p1.page_faults_minor p1_pf, p2.page_faults_major + p2.page_faults_minor p2_pf from p1 join p2 using(seq) where p1.state = p2.state order by p1.duration desc; +-----+----------------------+-----------+----------+-----------+-----------+-------+-------+--------------+--------------+-------+-------+ | seq | state | duration | duration | p1_cpu | p2_cpu | p1_cs | p2_cs | p1_block_ops | p2_block_ops | p1_pf | p2_pf | +-----+----------------------+-----------+----------+-----------+-----------+-------+-------+--------------+--------------+-------+-------+ | 12 | Sending data | 33.764396 | 7.523023 | 40.173893 | 13.027019 | 4979 | 21399 | 0 | 0 | 90 | 90 | | 5 | Opening tables | 0.270664 | 0.295955 | 0.025996 | 0.024996 | 34 | 35 | 2056 | 1488 | 48 | 48 | | 2 | starting | 0.000230 | 0.000192 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 29 | 29 | | 9 | statistics | 0.000130 | 0.000097 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 26 | 26 | | 6 | init | 0.000105 | 0.000138 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 26 | 26 | | 10 | preparing | 0.000068 | 0.000064 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 14 | 14 | | 16 | freeing items | 0.000049 | 0.000117 | 0.000000 | 0.001000 | 0 | 0 | 0 | 0 | 3 | 3 | | 8 | optimizing | 0.000048 | 0.000048 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 8 | 8 | | 7 | System lock | 0.000031 | 0.000031 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 1 | 1 | | 13 | end | 0.000027 | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 1 | 1 | | 4 | checking permissions | 0.000015 | 0.000014 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | | 15 | closing tables | 0.000015 | 0.000016 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | | 3 | checking permissions | 0.000014 | 0.000014 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | | 11 | executing | 0.000013 | 0.000013 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | | 14 | query end | 0.000011 | 0.000012 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | +-----+----------------------+-----------+----------+-----------+-----------+-------+-------+--------------+--------------+-------+-------+ 15 rows in set (0.00 sec)
Вот мои модифицированные версии запросов (только для использования синтаксиса ANSI JOIN):
-- Q1.1 select sum(lo_extendedprice*lo_discount) as revenue from lineorder join dim_date on lo_orderdatekey = d_datekey where d_year = 1993 and lo_discount between 1 and 3 and lo_quantity < 25; -- Q1.2 select sum(lo_extendedprice*lo_discount) as revenue from lineorder join dim_date on lo_orderdatekey = d_datekey where d_yearmonth = 199401 and lo_discount between 4 and 6 and lo_quantity between 26 and 35; -- Q1.3 select sum(lo_extendedprice*lo_discount) as revenue from lineorder join dim_date on lo_orderdatekey = d_datekey where d_weeknuminyear = 6 and d_year = 1994 and lo_discount between 5 and 7 and lo_quantity between 26 and 35; -- Q2.1 select sum(lo_revenue), d_year, p_brand from lineorder join dim_date on lo_orderdatekey = d_datekey join part on lo_partkey = p_partkey join supplier on lo_suppkey = s_suppkey where p_category = 'MFGR#12' and s_region = 'AMERICA' group by d_year, p_brand order by d_year, p_brand; -- Q2.2 select sum(lo_revenue), d_year, p_brand from lineorder join dim_date on lo_orderdatekey = d_datekey join part on lo_partkey = p_partkey join supplier on lo_suppkey = s_suppkey where p_brand between 'MFGR#2221' and 'MFGR#2228' and s_region = 'ASIA' group by d_year, p_brand order by d_year, p_brand; -- Q2.3 select sum(lo_revenue), d_year, p_brand from lineorder join dim_date on lo_orderdatekey = d_datekey join part on lo_partkey = p_partkey join supplier on lo_suppkey = s_suppkey where p_brand= 'MFGR#2239' and s_region = 'EUROPE' group by d_year, p_brand order by d_year, p_brand; -- Q3.1 select c_nation, s_nation, d_year, sum(lo_revenue) as revenue from customer join lineorder on lo_custkey = c_customerkey join supplier on lo_suppkey = s_suppkey join dim_date on lo_orderdatekey = d_datekey where c_region = 'ASIA' and s_region = 'ASIA' and d_year <= 1992 and d_year >= 1997 group by c_nation, s_nation, d_year order by d_year asc, revenue desc; -- Q3.2 select c_city, s_city, d_year, sum(lo_revenue) as revenue from customer join lineorder on lo_custkey = c_customerkey join supplier on lo_suppkey = s_suppkey join dim_date on lo_orderdatekey = d_datekey where c_nation = 'UNITED STATES' and s_nation = 'UNITED STATES' and d_year >= 1992 and d_year <= 1997 group by c_city, s_city, d_year order by d_year asc, revenue desc; -- Q3.3 select c_city, s_city, d_year, sum(lo_revenue) as revenue from customer join lineorder on lo_custkey = c_customerkey join supplier on lo_suppkey = s_suppkey join dim_date on lo_orderdatekey = d_datekey where (c_city='UNITED KI1' or c_city='UNITED KI5') and (s_city='UNITED KI1' or s_city='UNITED KI5') and d_year >= 1992 and d_year <= 1997 group by c_city, s_city, d_year order by d_year asc, revenue desc; -- Q3.4 select c_city, s_city, d_year, sum(lo_revenue) as revenue from customer join lineorder on lo_custkey = c_customerkey join supplier on lo_suppkey = s_suppkey join dim_date on lo_orderdatekey = d_datekey where (c_city='UNITED KI1' or c_city='UNITED KI5') and (s_city='UNITED KI1' or s_city='UNITED KI5') and d_yearmonth = 'Dec1997' group by c_city, s_city, d_year order by d_year asc, revenue desc; -- Q4.1 select d_year, c_nation, sum(lo_revenue - lo_supplycost) as profit from lineorder join dim_date on lo_orderdatekey = d_datekey join customer on lo_custkey = c_customerkey join supplier on lo_suppkey = s_suppkey join part on lo_partkey = p_partkey where c_region = 'AMERICA' and s_region = 'AMERICA' and (p_mfgr = 'MFGR#1' or p_mfgr = 'MFGR#2') group by d_year, c_nation order by d_year, c_nation; -- Q4.2 select d_year, s_nation, p_category, sum(lo_revenue - lo_supplycost) as profit from lineorder join dim_date on lo_orderdatekey = d_datekey join customer on lo_custkey = c_customerkey join supplier on lo_suppkey = s_suppkey join part on lo_partkey = p_partkey where c_region = 'AMERICA' and s_region = 'AMERICA' and (d_year = 1997 or d_year = 1998) and (p_mfgr = 'MFGR#1' or p_mfgr = 'MFGR#2') group by d_year, s_nation, p_category order by d_year, s_nation, p_category; -- Q4.3 select d_year, s_city, p_brand, sum(lo_revenue - lo_supplycost) as profit from lineorder join dim_date on lo_orderdatekey = d_datekey join customer on lo_custkey = c_customerkey join supplier on lo_suppkey = s_suppkey join part on lo_partkey = p_partkey where s_nation = 'UNITED STATES' and (d_year = 1997 or d_year = 1998) and p_category = 'MFGR#14' group by d_year, s_city, p_brand order by d_year, s_city, p_brand;
И схема:
DROP TABLE IF EXISTS customer;
CREATE TABLE IF NOT EXISTS customer
(
C_CustomerKey int primary key,
C_Name varchar(25),
C_Address varchar(25),
C_City varchar(10),
C_Nation varchar(15),
C_Region varchar(12),
C_Phone varchar(15),
C_MktSegment varchar(10),
KEY(C_Name),
KEY(C_City),
KEY(C_Region),
KEY(C_Phone),
KEY(C_MktSegment)
);
DROP TABLE IF EXISTS part;
CREATE TABLE IF NOT EXISTS part
(
P_PartKey int primary key,
P_Name varchar(25),
P_MFGR varchar(10),
P_Category varchar(10),
P_Brand varchar(15),
P_Colour varchar(15),
P_Type varchar(25),
P_Size tinyint,
P_Container char(10),
key(P_Name),
key(P_MFGR),
key(P_Category),
key(P_Brand)
);
DROP TABLE IF EXISTS supplier;
CREATE TABLE supplier
(
S_SuppKey int primary key,
S_Name char(25),
S_Address varchar(25),
S_City char(10),
S_Nation char(15),
S_Region char(12),
S_Phone char(15),
key(S_City),
key(S_Name),
key(S_Phone)
);
DROP TABLE IF EXISTS dim_date;
CREATE TABLE IF NOT EXISTS dim_date
(
D_DateKey int primary key,
D_Date char(18),
D_DayOfWeek char(9),
D_Month char(9),
D_Year smallint,
D_YearMonthNum int,
D_YearMonth char(7),
D_DayNumInWeek tinyint,
D_DayNumInMonth tinyint,
D_DayNumInYear smallint,
D_MonthNumInYear tinyint,
D_WeekNumInYear tinyint,
D_SellingSeason char(12),
D_LastDayInWeekFl tinyint,
D_LastDayInMonthFl tinyint,
D_HolidayFl tinyint,
D_WeekDayFl tinyint
);
DROP TABLE IF EXISTS lineorder;
CREATE TABLE IF NOT EXISTS lineorder
(
LO_OrderKey bigint not null,
LO_LineNumber tinyint not null,
LO_CustKey int not null,
LO_PartKey int not null,
LO_SuppKey int not null,
LO_OrderDateKey int not null,
LO_OrderPriority varchar(15),
LO_ShipPriority char(1),
LO_Quantity tinyint,
LO_ExtendedPrice decimal,
LO_OrdTotalPrice decimal,
LO_Discount decimal,
LO_Revenue decimal,
LO_SupplyCost decimal,
LO_Tax tinyint,
LO_CommitDateKey int not null,
LO_ShipMode varchar(10),
KEY(LO_OrderKey, LO_LineNumber),
KEY(LO_CustKey),
KEY(LO_SuppKey),
KEY(LO_PartKey),
KEY(LO_OrderDateKey),
KEY(LO_CommitDateKey)
);