SQL Server: maksymalna ilość wierszy w tabeli
Tworzę oprogramowanie, które przechowuje wiele danych w jednej ze swoich tabel bazodanowych(SQL Server w wersji 8, 9 lub 10). Powiedzmy, około 100,000 rekordów jest wstawianych do tej tabeli dziennie. To około 36 milionów płyt rocznie. W obawie, że stracę na wydajności, postanowiłem codziennie tworzyć nową tabelę (tabelę z aktualną datą w nazwie), aby zmniejszyć liczbę rekordów w tabeli.
Czy mógłbyś mi powiedzieć, czy to był dobry pomysł? Czy jest limit rekordów dla SQL stoły serwerowe? A może wiesz, ile rekordów (mniej więcej) można zapisać w tabeli, zanim wydajność zostanie znacząco obniżona?
12 answers
Trudno dać ogólną odpowiedź na to pytanie. To naprawdę zależy od ilości czynników:
- jaki jest rozmiar Twojego wiersza
- Jakie dane przechowujesz (ciągi, bloby, liczby)
- co robisz ze swoimi danymi (zachowaj je jako archiwum, regularnie odpytywaj)
- Czy masz indeksy na stole-ile
- Jaka jest specyfikacja serwera
Itd.
Jak gdzie indziej tutaj, 100.000 dziennie, a więc na stół jest przesada - proponuję miesięcznie albo tygodniowo, może nawet kwartalnie. Im więcej tabel masz, tym większy będzie koszmar konserwacji/zapytań.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-04-17 07:04:09
SQL Server 2008 R2 2008 R2 2008 R2 2008 R2 2008 R2 2008 R2 2008 R2 2008 R2 2008 R2 2008 R2 2008 R2]}
- Rozmiar bazy danych: 524 272 terabajty
- bazy danych na instancję SQL Server: 32,767
- Filegroups w bazie danych: 32767
- plików w bazie: 32767
- Rozmiar Pliku (dane): 16 terabajtów
- Rozmiar pliku (log): 2 terabajty
- wiersze w tabeli: ograniczone dostępnym magazynem
- tabele w bazie danych: ograniczone liczbą obiektów w baza danych
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-03-22 13:34:30
Mam trzy kolumny tabeli z nieco ponad 6 miliardów wierszy w SQL Server 2008 R2.
Zapytujemy go każdego dnia, aby stworzyć minutowe wykresy analizy systemu dla naszych klientów. Nie zauważyłem żadnych uderzeń wydajności bazy danych (choć fakt, że rośnie ~1 GB każdego dnia sprawia, że zarządzanie kopiami zapasowymi jest trochę bardziej zaangażowane niż bym chciał).
Aktualizacja Lipiec 2016
Dotarliśmy do ~24,5 miliarda wierszy zanim kopie zapasowe stały się na tyle duża, że możemy zdecydować się na okrojenie płyt starszych niż dwa lata (~700 GB przechowywanych w wielu kopiach zapasowych, w tym na drogich taśmach). Warto zauważyć, że wydajność nie była znaczącym motywatorem tej decyzji (tj. nadal działała świetnie).
Dla każdego, kto próbuje usunąć 20 miliardów wierszy z SQL Server, Gorąco polecam ten artykuł. Odpowiedni kod w przypadku, gdy link umrze (Przeczytaj cały artykuł Wyjaśnienie):
ALTER DATABASE DeleteRecord SET RECOVERY SIMPLE;
GO
BEGIN TRY
BEGIN TRANSACTION
-- Bulk logged
SELECT *
INTO dbo.bigtable_intermediate
FROM dbo.bigtable
WHERE Id % 2 = 0;
-- minimal logged because DDL-Operation
TRUNCATE TABLE dbo.bigtable;
-- Bulk logged because target table is exclusivly locked!
SET IDENTITY_INSERT dbo.bigTable ON;
INSERT INTO dbo.bigtable WITH (TABLOCK) (Id, c1, c2, c3)
SELECT Id, c1, c2, c3 FROM dbo.bigtable_intermediate ORDER BY Id;
SET IDENTITY_INSERT dbo.bigtable OFF;
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH
ALTER DATABASE DeleteRecord SET RECOVERY FULL;
GO
Aktualizacja Listopad 2016
Jeśli planujesz przechowywać tyle danych w jednej tabeli: nie rób tego. zdecydowanie polecam rozważyć partycjonowanie tabel (ręcznie lub z wbudowanymi funkcjami, jeśli używasz wersji Enterprise). To sprawia, że upuszczanie starych danych jest tak łatwe, jak obcinanie tabeli raz na tydzień (tydzień/miesiąc/itp.). Jeśli nie masz Enterprise (czego nie mamy), możesz po prostu napisać skrypt, który działa raz w miesiącu, spada tabele starsze niż 2 lata, tworzy tabelę następnego miesiąca i regeneruje dynamiczny widok, który łączy wszystkie tabele partycji w celu łatwego zadawania zapytań. Oczywiście "raz w miesiącu "i" starsze niż 2 lata " powinny być zdefiniowane przez Ciebie na podstawie tego, co ma sens dla Twojego przypadku użycia. Usunięcie bezpośrednio z tabeli zawierającej dziesiątki miliardów wierszy danych a) zajmie ogromną ilość czasu i B) wypełni dziennik transakcji setki lub tysiące razy.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-11-23 13:59:32
Nie znam limitu wierszy, ale znam tabele z ponad 170 milionami wierszy. Możesz go przyspieszyć za pomocą partycjonowanych tabel (2005+) lub widoków, które łączą wiele tabel.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-04-17 06:27:32
Nie znam MSSQL dokładnie, ale 36 milionów wierszy nie jest duże dla firmowej bazy danych - pracując z bazami mainframe, 100 000 wierszy brzmi jak Tabela konfiguracji dla mnie :-).
Chociaż nie jestem wielkim fanemniektórych oprogramowania Microsoftu, nie mówimy tu o Access: zakładam, że mogą obsługiwać dość znaczne rozmiary baz danych za pomocą korporacyjnych systemów DBMS.
Podejrzewam, że dni mogły być zbyt piękne, aby je podzielić, jeśli rzeczywiście w ogóle potrzebuje podziału.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-03-14 00:55:28
Mamy tabele w SQL Server 2005 i 2008 z ponad 1 miliardem wierszy w nim (30 milionów dodawanych dziennie). Nie wyobrażam sobie dzielenia tego na nowy stół każdego dnia.
Znacznie taniej dodać odpowiednie miejsce na dysku (które i tak potrzebujesz) i pamięć RAM.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-10-06 21:01:09
To zależy, ale powiedziałbym, że lepiej trzymać wszystko w jednym stole ze względu na prostotę.
100 000 wierszy dziennie to nie jest tak duża ilość. (W zależności od sprzętu serwera). Osobiście widziałem MSSQL obsługiwać do 100m wierszy w jednej tabeli bez żadnych problemów. Tak długo, jak zachować swoje indeksy w porządku powinno być wszystko dobrze. Kluczem jest posiadanie stosów pamięci, aby indeksy nie musiały być zamieniane na dysk.
Na z drugiej strony, zależy to od tego, w jaki sposób używasz danych, jeśli musisz wykonać wiele zapytań, a jego mało prawdopodobne dane będą potrzebne, które obejmują wiele dni (więc nie będziesz musiał dołączać do tabel), szybciej będzie oddzielić je na wiele tabel. Jest to często używane w aplikacjach takich jak kontrola procesów przemysłowych, gdzie można odczytać wartość na powiedzmy 50 000 instrumentów co 10 sekund. W tym przypadku szybkość jest niezwykle ważna, ale prostota nie jest.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-04-22 09:02:42
Przepełniliśmy raz klucz podstawowy integer (który wynosi ~2,4 miliarda wierszy) na tabeli. Jeśli istnieje limit wierszy, prawdopodobnie nigdy nie osiągniesz go w zaledwie 36 milionach wierszy rocznie.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-10-06 21:11:53
Możesz wypełnić tabelę, dopóki nie będziesz miał wystarczająco dużo miejsca na dysku. Dla lepszej wydajności można spróbować migracji do SQL Server 2005, a następnie partycji tabeli i umieścić części na różnych dyskach(jeśli masz konfigurację RAID, który może naprawdę pomóc). Partycjonowanie jest możliwe tylko w wersji enterprise SQL Server 2005. Możesz spojrzeć na przykład partycjonowania pod tym linkiem: http://technet.microsoft.com/en-us/magazine/cc162478.aspx
Możesz również spróbować utworzyć widoki dla większości wykorzystana część danych, która jest również jednym z rozwiązań.
Mam nadzieję, że to pomogło...
Największa tabela jaką spotkałem na SQL Server 8 na Windows2003 to 799 milionów z 5 kolumnami. Ale to, czy jest to dobra wola, należy zmierzyć w stosunku do SLA i przypadku użycia - np. załaduj rekordy 50-100,000,000 i sprawdź, czy nadal działa.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-08-10 21:49:55
SELECT Top 1 sysobjects.[name], max(sysindexes.[rows]) AS TableRows,
CAST(
CASE max(sysindexes.[rows])
WHEN 0 THEN -0
ELSE LOG10(max(sysindexes.[rows]))
END
AS NUMERIC(5,2))
AS L10_TableRows
FROM sysindexes INNER JOIN sysobjects ON sysindexes.[id] = sysobjects.[id]
WHERE sysobjects.xtype = 'U'
GROUP BY sysobjects.[name]
ORDER BY max(rows) DESC
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-12-30 23:21:56
Partycja tabeli miesięcznie.Jest to najlepszy sposób obsługi tabel z dużym dziennym napływem, czy to oracle czy MSSQL.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-09-07 18:10:55