Производительность таблицы с памятью хуже, чем таблица на дисках

У меня есть таблица в SQL Server 2014, которая выглядит следующим образом:

CREATE TABLE dbo.MyTable
(
[id1] [bigint] NOT NULL,
[id2] [bigint] NOT NULL,
[col1] [int] NOT NULL default(0),
[col2] [int] NOT NULL default(0)
)

с (id1, id2), являющимся PK. В принципе, id1 является идентификатором для группировки набора результатов (id2, col1, col2), pk - id2.

Я пытаюсь использовать таблицу in-memory, чтобы избавиться от существующей таблицы на диске, которая является моим узким местом.

  • Данные в таблице записаны -> read -> удаляется один раз.
  • Каждое значение id1 имеет несколько (десятки /сотни) тысяч id2.
  • Данные хранятся в таблице в течение очень короткого промежутка времени, например. 20 секунд.

Запросы, выполняемые в этой таблице, следующие:

-- INSERT (can vary from 10s to 10,000s of records):
INSERT INTO MyTable
  SELECT @fixedValue, id2, col1, col2 FROM AnotherTable

-- READ:
SELECT id2, col1
FROM MyTable INNER JOIN OtherTbl ON MyTable.id2 = OtherTbl.pk
WHERE id1 = @value
ORDER BY col1

-- DELETE:
DELETE FROM MyTable WHERE id1 = @value

Вот текущее определение, которое я использовал для таблицы:

CREATE TABLE dbo.SearchItems
(
  [id1] [bigint] NOT NULL,
  [id2] [bigint] NOT NULL,
  [col1] [int] NOT NULL default(0),
  [col2] [int] NOT NULL default(0)

  CONSTRAINT PK_Mem PRIMARY KEY NONCLUSTERED (id1,id2),
  INDEX idx_Mem HASH (id1,id2) WITH (BUCKET_COUNT = 131072)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)

К сожалению, это определение приводит к ухудшению производительности по сравнению с предыдущей ситуацией с таблицей на диске. Порядок величины на 10% выше (что в некоторых случаях достигает 100%, поэтому удваивается).

Больше всего я ожидал получить супер-преимущество в сценариях с высоким уровнем параллелизма, учитывая архитектуру без блокировки, рекламируемую Microsoft. Вместо этого наихудшие показатели - это точно, когда несколько параллельных пользователей запускают несколько запросов в таблице.

Вопросы:

  • Каков правильный BUCKET_COUNT для установки?
  • какой индекс я должен использовать?
  • почему производительность хуже, чем с таблицей на диске?

Запрос sys.dm_db_xtp_hash_index_stats возвращает:

total_bucket_count = 131072
empty_bucket_count = 0
avg_chain_len = 873
max_chain_length = 1009 пре>

Я изменил подсчет ведро, поэтому вывод из sys.dm_db_xtp_hash_index_stats :

total_bucket_count = 134217728
empty_bucket_count = 131664087
avg_chain_len = 1
max_chain_length = 3

Тем не менее, результаты почти одинаковы, если не хуже.

11 голосов | спросил Cristiano Ghersi 19 MarpmSat, 19 Mar 2016 14:22:29 +03002016-03-19T14:22:29+03:0002 2016, 14:22:29

1 ответ


7

Хотя этот пост не будет полным ответом из-за отсутствия информации, он должен иметь возможность указать вас в правильном направлении или иначе получить представление о том, что вы позже можете поделиться с сообществом.

  

К сожалению, это определение приводит к ухудшению производительности по сравнению с предыдущей ситуацией с таблицей на диске. Порядок величины на 10% выше (что в некоторых случаях достигает 100%, поэтому удваивается).

     

Больше всего я ожидал получить супер-преимущество в сценариях с высоким уровнем параллелизма, учитывая архитектуру без блокировки, рекламируемую Microsoft. Вместо этого наихудшие показатели - это точно, когда несколько параллельных пользователей запускают несколько запросов в таблице.

Это беспокоит, так как это определенно не так. Некоторые рабочие нагрузки не используются в таблицах памяти (SQL 2014), и некоторые рабочие нагрузки поддаются ему. В большинстве ситуаций может быть минимальное влияние производительности только путем миграции и выбора правильных индексов.

Первоначально я думал очень узко о ваших вопросах относительно этого:

  

Вопросы:

     
  • Каков правильный BUCKET_COUNT для установки?
  •   
  • какой индекс я должен использовать?
  •   
  • почему производительность хуже, чем с таблицей на диске?
  •   

Первоначально я считал, что проблема связана с тем, что фактическая таблица памяти и индексы не являются оптимальными. Хотя есть некоторые проблемы с оптимизированным по памяти индексом хеш-индекса, я считаю, что реальная проблема связана с используемыми запросами.

-- INSERT (can vary from 10s to 10,000s of records):
INSERT INTO MyTable
  SELECT @fixedValue, id2, col1, col2 FROM AnotherTable

Эта вставка должна быть очень быстрой, если она включает только таблицу памяти. Это, однако, также связано с таблицей на диске и подчиняется всем связанным с этим блокированию и блокировке. Таким образом, отходы реального времени здесь находятся на диске основе таблицы.

Когда я сделал быстрый тест против 100 000 вставки строки из таблицы на основе диска после загрузки данных в память - это было время ответа второй секунды. Однако большая часть ваших данных хранится только в течение очень короткого промежутка времени, менее 20 секунд. Это не дает ему много времени, чтобы действительно жить в кеше. Кроме того, я не уверен, насколько большой AnotherTable действительно есть и не знает, считываются ли значения с диска или нет. Мы должны полагаться на вас за эти ответы.

С запросом Select:

SELECT id2, col1
FROM MyTable INNER JOIN OtherTbl ON MyTable.id2 = OtherTbl.pk
WHERE id1 = @value
ORDER BY col1

Опять же, мы находимся во власти производительности таблицы на основе interop + disk. Кроме того, сортировки не являются дешевыми по индексам HASH, и должен использоваться некластеризованный индекс. Это вызывается в указателе-указателе , который я связал в комментариях.

Чтобы дать некоторые фактические данные, основанные на исследованиях, я загрузил SearchItems в таблицу памяти с 10 миллионами строк и AnotherTable с 100 000, поскольку я не знал фактического размера или статистики. Затем я использовал запрос select для выполнения. Кроме того, я создал сеанс расширенных событий на wait_completed и поместил его в кольцевой буфер. Он был очищен после каждого прогона. Я также запускал DBCC DROPCLEANBUFFERS для имитации среды, где все данные могут не быть резидентными.

Результаты не были впечатляющими, когда смотрели на них в вакууме. Поскольку ноутбук, на котором я тестирую это, использует SSD более высокого класса, я искусственно превратил производительность на основе диска для используемой мной виртуальной машины.

Результаты пришли без ожидания информации после 5 прогонов запроса только в таблице на основе памяти (удаление соединения и без подзапросов). Это в значительной степени, как ожидалось.

При использовании исходного запроса, однако, я ожидал. В этом случае PAGEIOLATCH_SH имеет смысл, поскольку данные считываются с диска. Поскольку я являюсь пользователем only в этой системе и не тратил время на создание массивной тестовой среды для вставок, обновлений, удаленийпротив объединенной таблицы я не ожидал, что блокировка или блокировка вступят в силу.

В этом случае еще раз значительная часть времени была потрачена на таблицу на диске.

Наконец, запрос на удаление. Поиск строк, основанных только на ID1, не очень эффективен с индексом. Хотя верно, что предикаты равенства - это то, что хэш-индексы правильны, ведро, в которое попадают данные, основывается на всех хешированных столбцах. Таким образом, id1, id2, где id1 = 1, id2 = 2 и id1 = 1, id2 = 3, будут хешировать в разные ковши, поскольку хэш будет поперек (1,2) и (1,3). Это не будет простое сканирование диапазона B-Tree, поскольку хеш-индексы не структурированы одинаково. Тогда я ожидал, что это не будет индексом ideal для этой операции, однако я бы не ожидал, что он будет принимать порядки дольше, чем опытные. Мне было бы интересно увидеть wait_info.

  

Больше всего я ожидал получить супер-преимущество в сценариях с высоким уровнем параллелизма, учитывая архитектуру без блокировки, рекламируемую Microsoft. Вместо этого наихудшие показатели - это точно, когда несколько параллельных пользователей запускают несколько запросов в таблице.

Хотя верно, что блокировки используются для логической согласованности, операции все равно должны быть атомарными. Это делается с помощью специального оператора сравнения на основе процессора (именно поэтому In-Memory работает только с некоторыми [хотя и почти всеми процессорами, произведенными за последние 4 года]). Таким образом, мы не получаем все бесплатно, все еще будет время для выполнения этих операций.

Еще один момент, который следует затронуть, заключается в том, что почти во всех запросах используется интерфейс T-SQL (а не скомпилированные изначально SPROC), которые касаются, по меньшей мере, одной таблицы на основе диска. Именно поэтому я считаю, что в конечном итоге мы не обладаем повышенной производительностью, поскольку мы все еще ограничены производительностью таблиц на дисках.

Follow-Up:

  1. Создайте расширенный сеанс событий для wait_completed и укажите известный вам SPID. Запустите запрос и дайте нам результат или используйте его внутри.

  2. Дайте нам обновление для выхода из # 1.

  3. Нет волшебного числа для определения количества ведра для хэш-индексов. В основном до тех пор, пока ведра не будут полностью заполнены, а цепи строк остаются ниже 3 или 4, производительность должна оставаться приемлемой. Это вроде как спрашивать: «Что я должен установить для своего файла журнала?» - это зависит от процесса, каждой базы данных, за тип использования.

ответил Sean Gallardy 26 MaramSat, 26 Mar 2016 10:16:05 +03002016-03-26T10:16:05+03:0010 2016, 10:16:05

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132