Производительность таблицы с памятью хуже, чем таблица на дисках
У меня есть таблица в 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Тем не менее, результаты почти одинаковы, если не хуже.
1 ответ
Хотя этот пост не будет полным ответом из-за отсутствия информации, он должен иметь возможность указать вас в правильном направлении или иначе получить представление о том, что вы позже можете поделиться с сообществом.
К сожалению, это определение приводит к ухудшению производительности по сравнению с предыдущей ситуацией с таблицей на диске. Порядок величины на 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:
-
Создайте расширенный сеанс событий для wait_completed и укажите известный вам SPID. Запустите запрос и дайте нам результат или используйте его внутри.
-
Дайте нам обновление для выхода из # 1.
-
Нет волшебного числа для определения количества ведра для хэш-индексов. В основном до тех пор, пока ведра не будут полностью заполнены, а цепи строк остаются ниже 3 или 4, производительность должна оставаться приемлемой. Это вроде как спрашивать: «Что я должен установить для своего файла журнала?» - это зависит от процесса, каждой базы данных, за тип использования.