Napisałem aplikację z zapleczem SQL Server, która gromadzi i przechowuje oraz bardzo dużą liczbę rekordów. Obliczyłem, że u szczytu średnia liczba zapisów wynosi około 3-4 miliardy dziennie (20 godzin pracy).
Moje oryginalne rozwiązanie (zanim wykonałem faktyczne obliczenie danych) polegało na tym, że moja aplikacja wstawiała rekordy do tej samej tabeli, do której pytają moi klienci. Rozbił się i spłonął dość szybko, oczywiście, ponieważ nie można wykonać zapytania do tabeli, w której umieszczono tak wiele rekordów.
Moje drugie rozwiązanie polegało na wykorzystaniu 2 baz danych, jednej dla danych otrzymanych przez aplikację i jednej dla danych gotowych dla klienta.
Moja aplikacja odbierałaby dane, dzieliła je na partie ~ 100 000 rekordów i wstawiała zbiorczo do tabeli pomostowej. Po zarejestrowaniu ~ 100 000 aplikacji aplikacja w locie utworzy kolejną tabelę pomostową o takim samym schemacie jak poprzednio i rozpocznie wstawianie do tej tabeli. Utworzyłby rekord w tabeli zadań o nazwie tabeli, która ma 100 000 rekordów, a procedura przechowywana po stronie programu SQL Server przenosiłaby dane z tabel pomostowych do tabeli produkcyjnej gotowej na klienta, a następnie upuszczałaby tabela tymczasowa utworzona przez moją aplikację.
Obie bazy danych mają ten sam zestaw 5 tabel o tym samym schemacie, z wyjątkiem bazy danych pomostowej zawierającej tabelę zadań. Baza danych pomostowych nie ma ograniczeń integralności, klucza, indeksów itp. W tabeli, w której będzie przechowywana większość rekordów. Pokazana poniżej nazwa tabeli to SignalValues_staging. Celem było, aby moja aplikacja jak najszybciej zatrzasnęła dane w SQL Server. Proces tworzenia tabel w locie, aby można je było łatwo migrować, działa całkiem dobrze.
Oto 5 odpowiednich tabel z mojej bazy danych pomostowych oraz tabela moich zadań:
Procedura składowana, którą napisałem, obsługuje przenoszenie danych ze wszystkich tabel pomostowych i wstawianie ich do produkcji. Poniżej znajduje się część mojej procedury składowanej, która wstawia do produkcji z tabel pomostowych:
-- Signalvalues jobs table.
SELECT *
,ROW_NUMBER() OVER (ORDER BY JobId) AS 'RowIndex'
INTO #JobsToProcess
FROM
(
SELECT JobId
,ProcessingComplete
,SignalValueStagingTableName AS 'TableName'
,(DATEDIFF(SECOND, (SELECT last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID(DB_NAME())
AND OBJECT_ID = OBJECT_ID(SignalValueStagingTableName))
,GETUTCDATE())) SecondsSinceLastUpdate
FROM SignalValueJobs
) cte
WHERE cte.ProcessingComplete = 1
OR cte.SecondsSinceLastUpdate >= 120
DECLARE @i INT = (SELECT COUNT(*) FROM #JobsToProcess)
DECLARE @jobParam UNIQUEIDENTIFIER
DECLARE @currentTable NVARCHAR(128)
DECLARE @processingParam BIT
DECLARE @sqlStatement NVARCHAR(2048)
DECLARE @paramDefinitions NVARCHAR(500) = N'@currentJob UNIQUEIDENTIFIER, @processingComplete BIT'
DECLARE @qualifiedTableName NVARCHAR(128)
WHILE @i > 0
BEGIN
SELECT @jobParam = JobId, @currentTable = TableName, @processingParam = ProcessingComplete
FROM #JobsToProcess
WHERE RowIndex = @i
SET @qualifiedTableName = '[Database_Staging].[dbo].['+@currentTable+']'
SET @sqlStatement = N'
--Signal values staging table.
SELECT svs.* INTO #sValues
FROM '+ @qualifiedTableName +' svs
INNER JOIN SignalMetaData smd
ON smd.SignalId = svs.SignalId
INSERT INTO SignalValues SELECT * FROM #sValues
SELECT DISTINCT SignalId INTO #uniqueIdentifiers FROM #sValues
DELETE c FROM '+ @qualifiedTableName +' c INNER JOIN #uniqueIdentifiers u ON c.SignalId = u.SignalId
DROP TABLE #sValues
DROP TABLE #uniqueIdentifiers
IF NOT EXISTS (SELECT TOP 1 1 FROM '+ @qualifiedTableName +') --table is empty
BEGIN
-- processing is completed so drop the table and remvoe the entry
IF @processingComplete = 1
BEGIN
DELETE FROM SignalValueJobs WHERE JobId = @currentJob
IF '''+@currentTable+''' <> ''SignalValues_staging''
BEGIN
DROP TABLE '+ @qualifiedTableName +'
END
END
END
'
EXEC sp_executesql @sqlStatement, @paramDefinitions, @currentJob = @jobParam, @processingComplete = @processingParam;
SET @i = @i - 1
END
DROP TABLE #JobsToProcessUżywam, sp_executesqlponieważ nazwy tabel tabel pomostowych pochodzą jako tekst z rekordów w tabeli zadań.
Ta procedura składowana jest uruchamiana co 2 sekundy przy użyciu sztuczki, której nauczyłem się z tego postu dba.stackexchange.com .
Problemem, którego nie mogę rozwiązać przez całe życie, jest szybkość, z jaką wkładki są produkowane. Moja aplikacja tworzy tymczasowe tabele pomostowe i bardzo szybko wypełnia je rekordami. Wkładka do produkcji nie nadąża za ilością stołów i ostatecznie istnieje nadwyżka stołów na tysiące. Jedyny sposób, jaki kiedykolwiek był w stanie nadążyć z danych przychodzących jest usunąć wszystkie klucze, indeksy, ograniczenia itp ... na produkcji SignalValuestabeli. Problem, z którym się wtedy spotykam, polega na tym, że tabela kończy się tak dużą liczbą rekordów, że zapytanie staje się niemożliwe.
Próbowałem podzielić tabelę na partycje za pomocą [Timestamp]jako kolumny partycjonowania bezskutecznie. Każda forma indeksowania spowalnia wstawki tak bardzo, że nie nadążają. Ponadto musiałbym stworzyć tysiące partycji (jedna co minutę? Godzina?) Lata wcześniej. Nie mogłem wymyślić, jak je stworzyć w locie
Próbowałem tworząc podział przez dodanie kolumny do tabeli komputerowej o nazwie TimestampMinute, której wartość była na INSERT, DATEPART(MINUTE, GETUTCDATE()). Wciąż za wolno.
Próbowałem uczynić go tabelą zoptymalizowaną pod kątem pamięci zgodnie z tym artykułem Microsoft . Może nie rozumiem, jak to zrobić, ale MOT jakoś spowolnił wkładki.
Sprawdziłem plan wykonania procedury składowanej i stwierdziłem, że (myślę?) Najbardziej intensywna operacja
SELECT svs.* INTO #sValues
FROM '+ @qualifiedTableName +' svs
INNER JOIN SignalMetaData smd
ON smd.SignalId = svs.SignalIdDla mnie to nie ma sensu: dodałem rejestrowanie zegara ściennego do procedury składowanej, która okazała się inna.
Jeśli chodzi o rejestrowanie czasu, ta konkretna instrukcja powyżej wykonuje się w ~ 300 ms na 100 000 rekordów.
Wyrok
INSERT INTO SignalValues SELECT * FROM #sValueswykonuje się w 2500-3000 ms na 100 000 rekordów. Usuwanie z tabeli dotkniętych rekordów według:
DELETE c FROM '+ @qualifiedTableName +' c INNER JOIN #uniqueIdentifiers u ON c.SignalId = u.SignalIdzajmuje kolejne 300ms.
Jak mogę to zrobić szybciej? Czy SQL Server może poradzić sobie z miliardami rekordów dziennie?
Jeśli jest to istotne, jest to SQL Server 2014 Enterprise x64.
Konfiguracja sprzętu:
Zapomniałem dołączyć sprzęt w pierwszym etapie tego pytania. Mój błąd.
Przedmówię to następującymi stwierdzeniami: Wiem, że tracę wydajność z powodu konfiguracji sprzętowej. Próbowałem wiele razy, ale ze względu na budżet, poziom C, wyrównanie planet itp. Nic nie mogę zrobić, aby uzyskać lepszą konfigurację. Serwer działa na maszynie wirtualnej i nie mogę nawet zwiększyć pamięci, ponieważ po prostu nie mamy już więcej.
Oto informacje o moim systemie:
Pamięć jest podłączona do serwera VM przez interfejs iSCSI do urządzenia NAS (spowoduje to obniżenie wydajności). Serwer NAS ma 4 dyski w konfiguracji RAID 10. Są to dyski wirujące WD WD4000FYYZ o pojemności 4 TB z interfejsem SATA 6 GB / s. Serwer ma skonfigurowany tylko jeden magazyn danych, więc tempdb i moja baza danych znajdują się w tym samym magazynie danych.
Maksymalna wartość DOP wynosi zero. Czy powinienem zmienić to na stałą wartość, czy po prostu pozwolić SQL Serverowi na to? Czytam na RCSI: Czy mam rację zakładając, że jedyną korzyścią z RCSI są aktualizacje wierszy? Nigdy nie będzie żadnych aktualizacji tych konkretnych zapisów, będą one INSERTedytowane i SELECTedytowane. Czy RCSI nadal na mnie skorzysta?
Moja tempdb wynosi 8 MB. Na podstawie poniższej odpowiedzi z jyao zmieniłem #sValues na zwykłą tabelę, aby całkowicie uniknąć tempdb. Wydajność była jednak prawie taka sama. Spróbuję zwiększyć rozmiar i wzrost tempdb, ale biorąc pod uwagę, że rozmiar #sValues będzie mniej więcej zawsze taki sam, nie spodziewam się dużego wzrostu.
Podjąłem plan wykonania, który załączyłem poniżej. Ten plan wykonania jest jedną iteracją tabeli pomostowej - 100 000 rekordów. Wykonanie zapytania było dość szybkie, około 2 sekund, ale należy pamiętać, że jest to bez indeksów w SignalValuestabeli, a SignalValuestabela, będąca celem INSERT, nie ma w nim żadnych rekordów.
