none
Vor-/Nachteile Komprimierung

    Frage

  • Hallo,
    ich wollte nochmal generell fragen, ob es irgendeine "Fausregel" oder so gibt, wann sich eine Komprimierung für Indizees/Tabellen lohnt.

    Vorteile sollen ja höhere Performance/weniger Speicherplatz zu kosten der CPU-Belastung sein (und die Indexerstellung dauert länger).

    Aber welche Indizees sollte man dann komprimieren?

    Danke für Eure Antworten :).

    Gruß,
    Frank

    Freitag, 9. Februar 2018 09:40

Antworten

  • Und um es mal etwas in Relation zu setzen:

    der CPU-Overhead dürfte niemals auch nur 4% erreichen. Wenn man dann wiederum 20% IO sparen kann, macht es das bei Mengenabfragen oft mehr als wett. Es ist natürlich eine Frage der Menge. Christophs Hinweis mit 1GB ist da schon valide. Aber es kann auch beim 200 MB eine Rolle spielen - wenn diese eben noch nicht im RAM liegen muss man nur mal ausrechnen, wie lange das Lesen dauert.

    Ansonsten gilt die Komprimierung ja auch im RAM, auch dort wird gespart. Um genau zu sein im "Buffer Pool" des SQL Server. (Der "Filecache" gehört zu Windows und spielt hier keine Rolle.) Weniger IO bedeuten übrigens wiederum auch weniger CPU-Instruktionen. Man sieht, ganz so trivial ist die Rechnung nicht. Also, wie Dirk schon sagte, einfach mal testen. Das ist recht unkompliziert.


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
    MCM SQL Server 2008
    MVP Data Platform MCSE Data Platform
    MCSM Charter Member, MCITP Charter Member etc.
    www.SarpedonQualityLab.com
    (Founder)

    • Als Antwort markiert F. Peters Donnerstag, 15. Februar 2018 10:43
    Freitag, 9. Februar 2018 17:28
  • Hallo Frank,

    aus meiner Erfahrung kann ich sagen, dass die Komprimierung eigentlich nur Vorteile bietet, wenn Du noch ein paar CPU-Ressourcen zur Verfügung hast. 

    Der Gewinn, der durch das reduzierte IO entsteht macht diesen Nachteil in der Regel mehr als wett. Probier es mal aus, mit großen Tabellen und lang laufenden Abfragen, die viel IO machen müssen. Oder auch mit INSERTS in eine neue Tabelle, einmal mit und einmal ohne. Aber wirklich merken wirst du es erst, wenn Du hier auch eine ganze Menge Daten bewegst, also so ab 1 GB aufwärts (geschätzt). Bei mir reduzieren wir das IO dann oft auf 30%.

    Hast Du aber ein schnelles IO Subsystem (SSD) dann wirst du natürlich weniger davon merken.



    Einen schönen Tag noch, Christoph -- Data Platform MVP - http://www.insidesql.org/blogs/cmu

    • Als Antwort markiert F. Peters Donnerstag, 15. Februar 2018 10:42
    Freitag, 9. Februar 2018 10:10
    Beantworter
  • Hallo,
    ich wollte nochmal generell fragen, ob es irgendeine "Fausregel" oder so gibt, wann sich eine Komprimierung für Indizees/Tabellen lohnt.

    Vorteile sollen ja höhere Performance/weniger Speicherplatz zu kosten der CPU-Belastung sein (und die Indexerstellung dauert länger).

    Aber welche Indizees sollte man dann komprimieren?

    Danke für Eure Antworten :).

    Gruß,
    Frank

    Hallo Frank,

    um auf Deine Frage einzugehen: einfach erst einmal durchtesten. In den meisten Fällen ist es jedoch so, dass man durchaus einen Geschwindigkeitsgewinn erzielt. Es geht halt einfach zu Lasten der CPU.

    https://www.mssqltips.com/sqlservertip/3187/demonstrating-the-effects-of-using-data-compression-in-sql-server/

    Hier ist ein Vergleich uncompressed, Row und page compression.

    Zwar schon was älter, aber um halt einfach mal einen Vergleich zu haben.

    Gruß Dirk


    May you never suffer the sentiment of spending a day without any purpose


    • Bearbeitet Dirk Hondong Freitag, 9. Februar 2018 12:41 Vertipper korrigiert
    • Als Antwort markiert F. Peters Donnerstag, 15. Februar 2018 10:43
    Freitag, 9. Februar 2018 11:15

Alle Antworten

  • Hier ein ganz netter Beitrag:

    https://sqlperformance.com/2017/01/sql-performance/compression-effect-on-performance

    Das Fazit ist aber:
    Wenn eine Tabelle eher selten abgefragt wird, kann man diese durchaus komprimieren (z.B. Archive).
    In einer OLTP-Umgebung mit vielen Abfragen und Veränderungen sollte man eher Plattenplatz und Hauptspeicher (Cache) favorisieren.

    Freitag, 9. Februar 2018 09:51
  • Hallo Frank,

    aus meiner Erfahrung kann ich sagen, dass die Komprimierung eigentlich nur Vorteile bietet, wenn Du noch ein paar CPU-Ressourcen zur Verfügung hast. 

    Der Gewinn, der durch das reduzierte IO entsteht macht diesen Nachteil in der Regel mehr als wett. Probier es mal aus, mit großen Tabellen und lang laufenden Abfragen, die viel IO machen müssen. Oder auch mit INSERTS in eine neue Tabelle, einmal mit und einmal ohne. Aber wirklich merken wirst du es erst, wenn Du hier auch eine ganze Menge Daten bewegst, also so ab 1 GB aufwärts (geschätzt). Bei mir reduzieren wir das IO dann oft auf 30%.

    Hast Du aber ein schnelles IO Subsystem (SSD) dann wirst du natürlich weniger davon merken.



    Einen schönen Tag noch, Christoph -- Data Platform MVP - http://www.insidesql.org/blogs/cmu

    • Als Antwort markiert F. Peters Donnerstag, 15. Februar 2018 10:42
    Freitag, 9. Februar 2018 10:10
    Beantworter
  • Das hängt eben stark von der Art und Weise der Abfragen ab.
    Mache ich statistische Auswertungen, wo die Daten durchaus sequentiell nah beieinander liegen und ich aus tausenden oder Millionen von Sätzen Aggregate ziehe, kann es durchaus zu einem Performancegewinn durch geringere IO zu Lasten der CPU kommen.
    Sind die Daten aber bereits im Filecache, bleibt die zusätzliche CPU-Last übrig.
    Sind die Daten gestreut, lohnt sich eher eine reine Indexkomprimierung, da die Datenblöcke und die darin enthaltenen Zeilen eben gestreuter sind. Der Worstcase wäre, aus 1 Mio. komprimierten Datenblöcken á 100 Zeilen jeweils nur einen zu benötigen.

    Bei stark veränderbaren Daten (also eher Delete/Updates als Inserts) kann es wiederum Overhead bedeuten, da auf Grund des Transaktionsverfahrens ja nie einen tatsächlichen update eines Datensatzes gibt sondern immer eine Kombination Insert/Delete. Bei der Komprimierung von Datenblöcken kann das dann schon nicht ganz ohne sein.

    Aber es ist halt von Fall zu Fall auszuprobieren.

    Habe ich einen SQL-Server auf einer Maschine, die bei kaum mehr als 10% Auslastung liegt, wird man von der Komprimierung wohl nichts bemerken.
    Habe ich aber 1000de von Abfragen je Sekunde über nur wenige Daten oder nur einzelne Zeilen, so ist das nur eine simple Rechnung:
    Schaffe ich bei 100% CPU 1000 Abfragen/Sekunde und der Komprimierungsoverhead beträgt nur 10%, reduziert sich der Grad der Parallelität um eben diese 10%, ich schaffe also nur 900 Abfragen.

    Auf Grund von Filecache (bei genügend großem Speicher) reduziert sich der physische IO sowieso schon und Writes werden auch noch asynchron durchgeführt, sind also nicht so relevant wie Reads.


    • Bearbeitet bfuerchau Freitag, 9. Februar 2018 10:41
    Freitag, 9. Februar 2018 10:38
  • Hier ein ganz netter Beitrag:

    https://sqlperformance.com/2017/01/sql-performance/compression-effect-on-performance

    Das Fazit ist aber:
    Wenn eine Tabelle eher selten abgefragt wird, kann man diese durchaus komprimieren (z.B. Archive).
    In einer OLTP-Umgebung mit vielen Abfragen und Veränderungen sollte man eher Plattenplatz und Hauptspeicher (Cache) favorisieren.

    Hallo,

    da sagt der Artikel so auch nicht aus. Erin Stellato hat hier den Vergleich zwischen Row Compression, Page Compression und den Einsatz der COMPRESS Function verglichen.

    Es wird am Ende ja auch noch mal explizit gesagt: "While the COMPRESS function provides significant space savings compared to page and row compression, the performance hit in terms of CPU, and the inability to index the compressed columns due to their data type, make it viable only for large volumes of data that will not be searched."


    May you never suffer the sentiment of spending a day without any purpose

    Freitag, 9. Februar 2018 10:53
  • Nun diese Aussage ist doch für mich eindeutig:

    " make it viable only for large volumes of data that will not be searched."

    Googleübersetzung:

    "macht es nur für große Datenmengen durchführbar, die nicht durchsucht werden"

    Freitag, 9. Februar 2018 10:58
  • Nun diese Aussage ist doch für mich eindeutig:

    " make it viable only for large volumes of data that will not be searched."

    Googleübersetzung:

    "macht es nur für große Datenmengen durchführbar, die nicht durchsucht werden"

    Reissen sie nicht immer die Sätze aus dem Kontext

    Es heißt " While the COMPRESS function provides significant space savings compared to page and row compression, the performance hit in terms of CPU, and the inability to index the compressed columns due to their data type, make it viable only for large volumes of data that will not be searched. "

    Auch hier wird wieder explizit auf die Compress Function eingegangen. Und es steht ja, COMPARED TO ROW AND PAGE COMPRESSION.


    May you never suffer the sentiment of spending a day without any purpose

    Freitag, 9. Februar 2018 11:03
  • Nun diese Aussage ist doch für mich eindeutig:

    " make it viable only for large volumes of data that will not be searched."

    Googleübersetzung:

    "macht es nur für große Datenmengen durchführbar, die nicht durchsucht werden"

    Im Übrigen erkennt man das auch, wenn man die Einleitung des Artikels genau liest-


    " The COMPRESS function solves this problem, and compresses data up to 2GB in size. Moreover, while I'd argue that the function should only be used for large, off-row data, I thought comparing it directly against row and page compression was a worthwhile experiment."

    Erin hat ja geschrieben, dass sie die COMPRESS Funktion mit der Row und der Page Compression vergleicht.

    "


    May you never suffer the sentiment of spending a day without any purpose


    Freitag, 9. Februar 2018 11:08
  • Hallo,
    ich wollte nochmal generell fragen, ob es irgendeine "Fausregel" oder so gibt, wann sich eine Komprimierung für Indizees/Tabellen lohnt.

    Vorteile sollen ja höhere Performance/weniger Speicherplatz zu kosten der CPU-Belastung sein (und die Indexerstellung dauert länger).

    Aber welche Indizees sollte man dann komprimieren?

    Danke für Eure Antworten :).

    Gruß,
    Frank

    Hallo Frank,

    um auf Deine Frage einzugehen: einfach erst einmal durchtesten. In den meisten Fällen ist es jedoch so, dass man durchaus einen Geschwindigkeitsgewinn erzielt. Es geht halt einfach zu Lasten der CPU.

    https://www.mssqltips.com/sqlservertip/3187/demonstrating-the-effects-of-using-data-compression-in-sql-server/

    Hier ist ein Vergleich uncompressed, Row und page compression.

    Zwar schon was älter, aber um halt einfach mal einen Vergleich zu haben.

    Gruß Dirk


    May you never suffer the sentiment of spending a day without any purpose


    • Bearbeitet Dirk Hondong Freitag, 9. Februar 2018 12:41 Vertipper korrigiert
    • Als Antwort markiert F. Peters Donnerstag, 15. Februar 2018 10:43
    Freitag, 9. Februar 2018 11:15
  • Habe ich nur wenige Daten, dann habe ich alles im RAM.

    Die Kombination RAM, CPU und IO ist interessant und zu betrachten. Daher würde ich auch keine pauschalen Aussagen machen. Siehe Posting von Dirk.

    BTW: Kommst Du evtl. zur SQL Konferenz, so dass man sich mal persönlich kennen lernen könnte?


    Einen schönen Tag noch, Christoph -- Data Platform MVP - http://www.insidesql.org/blogs/cmu

    Freitag, 9. Februar 2018 11:53
    Beantworter
  • Bezogen auf den oberen Link stört mich allerdings die Grundlage:

    For the setup, I am using a table of two million rows, two columns - an ID column (identity) and a fixed-length CHAR(100) column. The CHAR column contains the letter 'A' 50 times. On the millionth row ordered by UQID, instead of 'AAAA...AAA', is the word 'Bazinga!'. The benchmark is to measure how quickly 'Bazinga!' is found, and how much I/O is involved, in a table scan when the table is not compressed; compressed using row-level compression, and compressed using page-level compression.

    Mit anderen Worten, hier wird ein Vorgang getestet, der real in den Daten so nie vorkommen wird.
    Ich habe eher selten 2 Millionen - 1 Sätze mit dem selben Schlüssel und nur genau 1 anderen.
    Damit sind die Kompressionsraten extrem hoch und eben die Zugriffe dadurch niedrig.
    Mich würde da eher ein Vergleichtest interessieren, der genau 2 Mio verschiedene Schlüssel enthält.

    Bezogen auf den Test also das zufällige Finden einer der 2 Mio. verschiedenen ID's und nicht immer desselben Schlüssels. Das ist an der Realität einfach vorbei.

    Wie schnell ist das Ganze, wenn ich per Zufallszahl eine ID auswähle und dann eben 1 Mio Zugriffe ausführe.

    Freitag, 9. Februar 2018 12:12
  • Bezogen auf den oberen Link stört mich allerdings die Grundlage:

    For the setup, I am using a table of two million rows, two columns - an ID column (identity) and a fixed-length CHAR(100) column. The CHAR column contains the letter 'A' 50 times. On the millionth row ordered by UQID, instead of 'AAAA...AAA', is the word 'Bazinga!'. The benchmark is to measure how quickly 'Bazinga!' is found, and how much I/O is involved, in a table scan when the table is not compressed; compressed using row-level compression, and compressed using page-level compression.

    Mit anderen Worten, hier wird ein Vorgang getestet, der real in den Daten so nie vorkommen wird.
    Ich habe eher selten 2 Millionen - 1 Sätze mit dem selben Schlüssel und nur genau 1 anderen.
    Damit sind die Kompressionsraten extrem hoch und eben die Zugriffe dadurch niedrig.
    Mich würde da eher ein Vergleichtest interessieren, der genau 2 Mio verschiedene Schlüssel enthält.

    Bezogen auf den Test also das zufällige Finden einer der 2 Mio. verschiedenen ID's und nicht immer desselben Schlüssels. Das ist an der Realität einfach vorbei.

    Wie schnell ist das Ganze, wenn ich per Zufallszahl eine ID auswähle und dann eben 1 Mio Zugriffe ausführe.

    Artikel genau gelesen?

    Es geht darum, dass die erste Spalte unsere Identity Column ist und die 2. Spalte eine beliebige Zeichenfolge. Wir haben hier also 2 Millionen Datensätze mit unterschiedlichen Schlüsseln aber ähnlichem Text.

    CREATE TABLE dbo.TestData ( UQID INT IDENTITY(1,1) NOT NULL, SomeCharData CHAR(100) )

    (In der 2. Spalte könnte auch irgendein RANDOM Text sein,es geht hier nur um die Demonstration)

    Es wird aber über die 2. Spalte gesucht, nicht indiziert und der SQL Server muss einmal alles durchwühlen. Hier wird der Table Scan durch die Query ja erzwungen und eben nicht über den Schlüssel gesucht.

    SELECT UQID 
    FROM dbo.TestData 
    WHERE SomeCharData = 'Bazinga!'
    GO

    Und hier werden dann die Logical Reads und Laufzeiten miteinander verglichen.

    Mit anderen Worten: hier wird ein Vorgang getestet, der real durchaus vorkommen kann.

    Es wird nach einem String gesucht und man will die passende ID (also den Schlüssel) haben.


    May you never suffer the sentiment of spending a day without any purpose



    Freitag, 9. Februar 2018 12:32
  • Nun "eine beliebige Zeichenfolge." geht aus dem Text leider nicht hervor sondern 2Mio mal "AAAAA...AAA" und 1 Mal "Bazinga!" beim 1.Millonsten Satz.
    Und eine Kompressionsrate von 90% spricht auch eher dafür. Bei 2 Mio verschiedenen Schlüsseln kann die Kompressionsrate auch bei nur 10% liegen, dann ist das IO-Verhalten mit dem unkomprimierten Teil ja fast identisch, so dass der CPU-Overhead bleibt.

    Aber was solls, es kommt halt immer auf die Daten selber an, ob eine Kompression was bringt oder nicht.
    Eine generellle Empfehlung, da stimme ich zu, kann es da halt nicht geben.

    Freitag, 9. Februar 2018 13:22
  • Bezogen auf den oberen Link stört mich allerdings die Grundlage:

    For the setup, I am using a table of two million rows, two columns - an ID column (identity) and a fixed-length CHAR(100) column. The CHAR column contains the letter 'A' 50 times. On the millionth row ordered by UQID, instead of 'AAAA...AAA', is the word 'Bazinga!'. The benchmark is to measure how quickly 'Bazinga!' is found, and how much I/O is involved, in a table scan when the table is not compressed; compressed using row-level compression, and compressed using page-level compression.

    Mit anderen Worten, hier wird ein Vorgang getestet, der real in den Daten so nie vorkommen wird.
    Ich habe eher selten 2 Millionen - 1 Sätze mit dem selben Schlüssel und nur genau 1 anderen.
    Damit sind die Kompressionsraten extrem hoch und eben die Zugriffe dadurch niedrig.
    Mich würde da eher ein Vergleichtest interessieren, der genau 2 Mio verschiedene Schlüssel enthält.

    Bezogen auf den Test also das zufällige Finden einer der 2 Mio. verschiedenen ID's und nicht immer desselben Schlüssels. Das ist an der Realität einfach vorbei.

    Wie schnell ist das Ganze, wenn ich per Zufallszahl eine ID auswähle und dann eben 1 Mio Zugriffe ausführe.

    Wenn das zitierte Szenario nicht den eigenen Vorstellungen entspricht: Einfach mal selber eins bauen. ;-)

    Wenn man das Verhalten der Engine untersucht, ist es sehr üblich, konstruierte Cases zu bauen. Ansonsten ist es schwierig, Thesen zu untersuchen, wenn sie im Rauschen von x anderen Szenarien untergehen. Das gilt auch in der Wissenschaft.


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
    MCM SQL Server 2008
    MVP Data Platform MCSE Data Platform
    MCSM Charter Member, MCITP Charter Member etc.
    www.SarpedonQualityLab.com
    (Founder)

    Freitag, 9. Februar 2018 17:20
  • Und um es mal etwas in Relation zu setzen:

    der CPU-Overhead dürfte niemals auch nur 4% erreichen. Wenn man dann wiederum 20% IO sparen kann, macht es das bei Mengenabfragen oft mehr als wett. Es ist natürlich eine Frage der Menge. Christophs Hinweis mit 1GB ist da schon valide. Aber es kann auch beim 200 MB eine Rolle spielen - wenn diese eben noch nicht im RAM liegen muss man nur mal ausrechnen, wie lange das Lesen dauert.

    Ansonsten gilt die Komprimierung ja auch im RAM, auch dort wird gespart. Um genau zu sein im "Buffer Pool" des SQL Server. (Der "Filecache" gehört zu Windows und spielt hier keine Rolle.) Weniger IO bedeuten übrigens wiederum auch weniger CPU-Instruktionen. Man sieht, ganz so trivial ist die Rechnung nicht. Also, wie Dirk schon sagte, einfach mal testen. Das ist recht unkompliziert.


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
    MCM SQL Server 2008
    MVP Data Platform MCSE Data Platform
    MCSM Charter Member, MCITP Charter Member etc.
    www.SarpedonQualityLab.com
    (Founder)

    • Als Antwort markiert F. Peters Donnerstag, 15. Februar 2018 10:43
    Freitag, 9. Februar 2018 17:28
  • Hallo,
    erstmal danke für die Hinweise. Ich werd auch mal den Artikel lesen (schadet bestimmt nicht ;)).

    Bei einigen Tabellen ist es schon eingeschaltet (ext. Berater). Ich finde das Thema ganz Interessant, kann es nur recht schwer einschätzen.

    Wir haben auch fast immer CPU-Reserven übrig. Außer Nachts nicht und da laufen ein paar Zeitkritische Prozesse.

    Es fällt mir auch sehr schwer zu berurteilen, ob solche Maßnahmen Erfolg haben, da die Abfragelast irgendwo zwischen 4.000-10.000 (tagsüber) und 20.000 (Nachts) liegt, die DB-Größe nähert sich 1 TB, SSDs haben wir auch drin.

    Bei einzelnen Abfragen ist das kein Problem, aber in Summe finde Ich das äußerst schwierig zu berurteilen (zumal die Aussagen der Leute recht widersprüchlich sind :).

    Ich werde es mal auf unserem Testsystem probieren :).

    Gruß,
    Frank

    P.S.: > BTW: Kommst Du evtl. zur SQL Konferenz, so dass man sich mal persönlich kennen lernen könnte?
    Würde Ich an sich gerne, aber das haut zeitlich nicht hin.

    Montag, 12. Februar 2018 08:57
  • Da kann ich auch nur mal eine Antwort eines anderen Anbieters weitergeben:

    "Wenn dir die Software zu langsam erscheint, mach einfach die Hardware schneller."

    Montag, 12. Februar 2018 10:05