7
Gewaltige Performance Probleme mit MS SQL-Server 2000
Vor geraumer Zeit haben wir uns vorgenommen, eine vernünftige interne Backup-CD bzw. DVD-Datenbank zu erstellen mit richtig geilen Funktionen und toller Performance. Soviel zur Theorie 
. Praktisch ist es so, daß die Datenbank sehr schnell an ihre Grenzen kommt und unsere Ideen für weitere Optimierungen am Ende sind...
Wir "Endlagern" unsere Daten sobald Projekte abgeschlossen sind auf DVDs. Auf den DVDs sind teilweise sehr viele Dateien, da das auch komplette Webauftritte sein können. So kommt es, daß unsere DVD-Datenbank inzwischen knapp 70 DVDs enthält und 2,2 Millionen Dateieinträge (Nein - 70 DVDs sind bei weiten nicht all unsere Backup-DVDs). Hinzufügen weiterer DVDs ist inzwischen kaum mehr möglich aufgrund von Timeouts...
Verwendete Software:
- Microsoft Windows Server 2003 mit allen SPs / Updates
- Microsoft SQL-Server 2000 mit SP4
Verwendete Hardware:
- 2 AMD Opteron 280 Prozessoren (jeweils 2 Kerne á 2,4 GhZ), insg. also 4x 2,4 GhZ verfügbar
- 4x 1,0 GB Registered DDR400 Speicher
- Raid5 mit 5x 200 GB S-ATA an einem 3ware 9500er-Raid-Controller
- Anbindung via 1,0 Gbit/s Kupfer
Datenbank-Struktur:
DVDDB_Datentraeger (Enthält für jeden Datenträger 1 Zeile mit infos wie Wann eingelesen, fertig eingelesen, wer hats eingelesen, Beschreibung, Keywords, etc.) Alles nur INT oder VARCHARS mit KeyIndex auf einer ID-Spalte
DVDDB_Dateien (Enthält für jede Datei eine Zeile >
DVDDB_Dateien_ID > Int > PrimaryKey
DVDDB_Dateien_DateigroesseKB > bigint
DVDDB_Dateien_Erstellt_Am > dateTime
DVDDB_Dateien_Geaendert_Am > DateTime
DVDDB_Dateien_DatentraegerID > Int > Verweise auf die DVDDB_Datentraeger-KeyIndex-ID
DVDDB_Dateien_IstGepackt > Bit > Wir lesen auch den Inhalt von RAR-Archiven als eigene Dateien ein
DVDDB_Dateien_Langname > VarChar(300) > Pfad und Dateiname
DVDDB_Dateien_Kurzname > VarChar(25) > Nur 8+3 Dateiname > Praktisch um z.B. nach Endungen zu filtern oder den langen Dateinamen anzufassen
DVDDB_Dateien_Attribute > VarChar(25) > Attribute der Datei, RW SYSTEM oder sonstwas.
Es gibt 2 Indizes: einmal einen Keyindex nur auf die DVDDB_Dateien_ID und einmal einen Index auf Dateigroesse, Erstellt_aM, DatentraegerID, Istgepackt und dem Langnamen. Eben allem wonach gesucht werden wird.
Mach ich nun einen z.B.
DELETE FROM DVDDB_Dateien WHERE DVDDB_Dateien_DatentraegerID = 1234
von dem 30.000 Zeilen betroffen wären, so bekomme ich nach 3 Minuten einen Timeout.
Von einer Suchanfrage
SELECT DVDDB_Dateien_DatentraegerID FROM DVDDB_Dateien WHERE DVDDB_Dateien_Langname LIKE '%Suchbegriff%';
gar nicht erst zu sprechen - aber da verstehe ich zumindest die benötigte Zeit.
Bei einem Insert siehts übrigens auch nicht besser aus... 30.000 Zeilen mit Testinhalt kann ich auch nicht innerhalb von 300 Sekunden adden.
Übrigens bekommt er auch schon manchmal ein Timeout wenn ich nur einen
SELECT TOP 100 * FROM DVDDB_Dateien
mache.
Was weiss ich nun?
- An den CPUs liegt nicht. Wenn ich delete, update oder selecte langweilen sich die CPUs bzw. sind nach 5 Sekunden last wieder auf 0.
- Der RAM ist auch bei weitem nicht ausgereizt, trotzdem der Server auf alles zugreifen könnte.
- Mehr Indizes bringen nix, da er beim Delete oder insert auch die indizes mit updated und das dadurch deutlich länger dauert.
- Ich KANN und WILL nicht glauben, daß der SQL-Server mit 2,2 Millionen Zeilen in einer Tabelle überfordert ist.
- mit dem MS SQL 2005 ist es auch nicht besser. Habe eine 2. Testumgebung mit dem 2005er aufgebaut und der braucht ziemlich genau so lange für die gleichen Anweisungen mit dem gleichen Datenstamm.
Was brauche ich?
- HILFE! HILFE! HILFE!
- jemanden, der wirklich richtig richtig richtig fit mit dem SQL-Server und der Entwicklung davon ist und uns beratend zur Seite stehen kann - ist zwar ein internes Problem aber zur Not kann ich auch ein bischen Budget freimachen. Ausserdem wurmt es mich total und ich beisse mir schon seit geraumer Zeit daran die Zähne aus. Kann doch nicht sein...
Vielen Dank schonmal!
Verwendete Software:
- Microsoft Windows Server 2003 mit allen SPs / Updates
- Microsoft SQL-Server 2000 mit SP4
Verwendete Hardware:
- 2 AMD Opteron 280 Prozessoren (jeweils 2 Kerne á 2,4 GhZ), insg. also 4x 2,4 GhZ verfügbar
- 4x 1,0 GB Registered DDR400 Speicher
- Raid5 mit 5x 200 GB S-ATA an einem 3ware 9500er-Raid-Controller
- Anbindung via 1,0 Gbit/s Kupfer
Datenbank-Struktur:
DVDDB_Datentraeger (Enthält für jeden Datenträger 1 Zeile mit infos wie Wann eingelesen, fertig eingelesen, wer hats eingelesen, Beschreibung, Keywords, etc.) Alles nur INT oder VARCHARS mit KeyIndex auf einer ID-Spalte
DVDDB_Dateien (Enthält für jede Datei eine Zeile >
DVDDB_Dateien_ID > Int > PrimaryKey
DVDDB_Dateien_DateigroesseKB > bigint
DVDDB_Dateien_Erstellt_Am > dateTime
DVDDB_Dateien_Geaendert_Am > DateTime
DVDDB_Dateien_DatentraegerID > Int > Verweise auf die DVDDB_Datentraeger-KeyIndex-ID
DVDDB_Dateien_IstGepackt > Bit > Wir lesen auch den Inhalt von RAR-Archiven als eigene Dateien ein
DVDDB_Dateien_Langname > VarChar(300) > Pfad und Dateiname
DVDDB_Dateien_Kurzname > VarChar(25) > Nur 8+3 Dateiname > Praktisch um z.B. nach Endungen zu filtern oder den langen Dateinamen anzufassen
DVDDB_Dateien_Attribute > VarChar(25) > Attribute der Datei, RW SYSTEM oder sonstwas.
Es gibt 2 Indizes: einmal einen Keyindex nur auf die DVDDB_Dateien_ID und einmal einen Index auf Dateigroesse, Erstellt_aM, DatentraegerID, Istgepackt und dem Langnamen. Eben allem wonach gesucht werden wird.
Mach ich nun einen z.B.
DELETE FROM DVDDB_Dateien WHERE DVDDB_Dateien_DatentraegerID = 1234
von dem 30.000 Zeilen betroffen wären, so bekomme ich nach 3 Minuten einen Timeout.
Von einer Suchanfrage
SELECT DVDDB_Dateien_DatentraegerID FROM DVDDB_Dateien WHERE DVDDB_Dateien_Langname LIKE '%Suchbegriff%';
gar nicht erst zu sprechen - aber da verstehe ich zumindest die benötigte Zeit.
Bei einem Insert siehts übrigens auch nicht besser aus... 30.000 Zeilen mit Testinhalt kann ich auch nicht innerhalb von 300 Sekunden adden.
Übrigens bekommt er auch schon manchmal ein Timeout wenn ich nur einen
SELECT TOP 100 * FROM DVDDB_Dateien
mache.
Was weiss ich nun?
- An den CPUs liegt nicht. Wenn ich delete, update oder selecte langweilen sich die CPUs bzw. sind nach 5 Sekunden last wieder auf 0.
- Der RAM ist auch bei weitem nicht ausgereizt, trotzdem der Server auf alles zugreifen könnte.
- Mehr Indizes bringen nix, da er beim Delete oder insert auch die indizes mit updated und das dadurch deutlich länger dauert.
- Ich KANN und WILL nicht glauben, daß der SQL-Server mit 2,2 Millionen Zeilen in einer Tabelle überfordert ist.
- mit dem MS SQL 2005 ist es auch nicht besser. Habe eine 2. Testumgebung mit dem 2005er aufgebaut und der braucht ziemlich genau so lange für die gleichen Anweisungen mit dem gleichen Datenstamm.
Was brauche ich?
- HILFE! HILFE! HILFE!
- jemanden, der wirklich richtig richtig richtig fit mit dem SQL-Server und der Entwicklung davon ist und uns beratend zur Seite stehen kann - ist zwar ein internes Problem aber zur Not kann ich auch ein bischen Budget freimachen. Ausserdem wurmt es mich total und ich beisse mir schon seit geraumer Zeit daran die Zähne aus. Kann doch nicht sein...
Vielen Dank schonmal!
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 75642
Url: https://administrator.de/contentid/75642
Printed on: April 23, 2024 at 18:04 o'clock
7 Comments
Latest comment
ich hab zwar von der bedienung eines SQL-server keine ahnung, aber zur hardware kann ich dir ein paar erfahrungsberichte geben.
die ghz der CPUs ist nicht das einzige kriterium, sowohl die größe des CPU-cache, der mainboard chipsatz als auch der cpu-microcode können je nach anwendung signifikante unterschiede machen.
ein raid-controller mit PCIe hat einen größen busdurchsatz und zumeist eine schnellere CPU (geht inzwischen bis ~500 mhz) auf dem raidcontroller. als platte würde ich sata-2 in 500 gb oder 1000 gb nehmen, die haben höhere durchsatzraten und mehr cache.
wenn du die möglichkeit hast, teste es mal mit einem core2duo system, intel chipsatz, PCIe raidcontroller und als netzwerkkarte nur intel oder broadcom, die marvell etc chipsätze leisten deutlich weniger.
die ghz der CPUs ist nicht das einzige kriterium, sowohl die größe des CPU-cache, der mainboard chipsatz als auch der cpu-microcode können je nach anwendung signifikante unterschiede machen.
ein raid-controller mit PCIe hat einen größen busdurchsatz und zumeist eine schnellere CPU (geht inzwischen bis ~500 mhz) auf dem raidcontroller. als platte würde ich sata-2 in 500 gb oder 1000 gb nehmen, die haben höhere durchsatzraten und mehr cache.
wenn du die möglichkeit hast, teste es mal mit einem core2duo system, intel chipsatz, PCIe raidcontroller und als netzwerkkarte nur intel oder broadcom, die marvell etc chipsätze leisten deutlich weniger.
Wie gesagt - an der CPU-Power scheitert es nicht, da die CPUs sich langweilen. Auch Netzwerkkarte will ich ausschliessen, da der SQL-Server alles bei sich Lokal macht und man ihm quasi nur den Befehl gibt "Zeig mir...." und er den rest für sich entscheidet (und es wären Intel Onboard-NICs).
Der Raid-Controller ist PCI-X, sollte für nen SQL-Server auch bei weitem ausreichen...
Ok raid5 ist vermutlich die falsche Entscheidung für nen SQL-Server, aber auch die Platten sind bei weitem nicht überfordert.
Alles in allem tippe ich auf ein reines Software-Problem. Die grosse Frage ist halt, ob es vielleicht für einen MS SQL-Server 2000 "normal" ist einzubrechen, wenn man in einer Tabelle > 2 Mio. Datensätze mit nem knappen Gigabyte an Daten hat. ABER DAS KANN UND WILL ICH NICHT GLAUBEN. Also hoffe ich darauf, daß mir jemand verraten kann, was ich falsch mache.
Der Raid-Controller ist PCI-X, sollte für nen SQL-Server auch bei weitem ausreichen...
Ok raid5 ist vermutlich die falsche Entscheidung für nen SQL-Server, aber auch die Platten sind bei weitem nicht überfordert.
Alles in allem tippe ich auf ein reines Software-Problem. Die grosse Frage ist halt, ob es vielleicht für einen MS SQL-Server 2000 "normal" ist einzubrechen, wenn man in einer Tabelle > 2 Mio. Datensätze mit nem knappen Gigabyte an Daten hat. ABER DAS KANN UND WILL ICH NICHT GLAUBEN. Also hoffe ich darauf, daß mir jemand verraten kann, was ich falsch mache
.
ist noch irgendwas an backup spftware installiert? ich hab teilweise seltsame "nebenwirkungen" erlebt, wenn z.b. acronis oder was anderes mit open-file funktionen installiert war. bei mir was so, das der eingebaute vnc von VMware workstation alle 3 mausklicks eine sanduhr gezeigt hatte, ob wohl massig speicher da war und auch sonst keine last auf dem system. einfach nur durch das "vorhandensein" der software, ohne backup-aktivität.
ne keine Backup-Software. Ist ja eigentlich auch nicht nötig, da man den SQL-Server mit Boardmitteln gut backuppen kann .
Ich hab ja auch kein grundsätzliches Problem - "normale" Tabellen laufen rasend schnell. Mein Problem ist eigentlich nur diese eine Tabelle, die soooooo umfangreich ist... und ich befürchte, daß sie einfach zu gross ist. Andererseits muss es doch wohl noch menschen auf der Welt geben, die grössere Datenbanken haben als ich...
.Ich hab ja auch kein grundsätzliches Problem - "normale" Tabellen laufen rasend schnell. Mein Problem ist eigentlich nur diese eine Tabelle, die soooooo umfangreich ist... und ich befürchte, daß sie einfach zu gross ist. Andererseits muss es doch wohl noch menschen auf der Welt geben, die grössere Datenbanken haben als ich...
Hallo,
also löblich erstmal deine Beschreibung. Hier würde man sich mehr davon wünschen. Das ist aber zunächst auch alles was ich loben kann.
Zur Hardware:
Für einen SQL Server völlig falsche konfiguration. Zunächst einmal gehören hier hochdrehende SAS oder SCSI320 Platten zum Einsatz und kein IDE. In Zeitschriften wird immer lineares lesen oder schreibne verglichen. Das trifft hier nicht zu. Hier geht es um Zugriffszeiten und dabei sind SATA oder IDE Platten ein paar mal langsamer.
Auch das Raidlevel ist falsch gewählt. Weiterhin gehört System, Datenbank und Logdatei bei einem SQL Server getrennt. Beispielsweise Raid 1 für System mit zwei Platten, Raid 10 für Daten und Raid 1 für Log. WAS GLAUBST DU WAS LOS IST bei deiner Abfrage, wenn das Sytstem arbeiten muß, in deinen Daten sequenziell gelesen und geschrieben wird und gleichzeitig linear das Transaktionsprotokoll geschrieben wird. Das MUSS langsam sein !!!!!!!!
Bei einem Datenbankserver ist die größte Performancebremse zunächst mal das Festplattensubsystem.
Zur Umgebung:
Du hast nicht geschrieben, was auf dem Server noch so läuft. Ist das gleichzeitig noch Webserver, Domaincontroller oder sonst was ? Dann wäre das auch nicht so toll. Wenn du einen Nagel in die Wand hauen willst nimmst du ja auch einen Hammer und wenn du etwas Sägen willst, nimmst du eine Säge und nicht als Universalmittel das Schweizer Taschenmesser. Das mag in der Not helfen, aber ist für das tägliche Brot nichts.
Bezüglich RAM sollte du die /3GB Option benutzen, damit der RAM auch genutzt wird.
Im SQL Server sollte der RAM begrenzt und auf feste Größe gestellt werden.
Wichtig ist auch der Umgang - man glaube es nicht mit Pagefiles und Größe
Auch hier könnte ich mit den Windows Configs zu fortfahren.
Zu SQL:
Hier ist auch die Frage, wie gut die Datenbank aufgebaut ist. Das kann bereits eine riesen Perofrmancebremse sein. Gleiches gilt für die Qualität der Selects. Auch die parallelen Zugriffe und deren Abarbeitung. Schau mal, was tatsächlich auf deinem SQL Server passiert in dieser Zeit. Sperren, Locks - benutze den Profiler etc.....
Ich weiß, ich haue dir jetzt ein Brett vor den Kopf und vielleicht denkst du, was will der von mir, aber das ist nicht ein.... wie ändere ich den Hintergrund Problem....
Um dir zu helfen muß man das Netz, die Struktur, den SQL Server selbst, dessen Konfig, die Datenbank, die Zugriffe und die Selects kennen.
Zu deiner Frage: 2,2 Millionen Zeilen an sich sind erstmal kein Problem. Wir verwalten Millionen von Buchungen in einzelnen Tabellen.
Also wenn du aus dem Dilemma rauskomen willst, dann würde ich mal das Geld in die Hand nehmen, mir einen Consultant ins Haus holen, der dich hier richtig berät. Zunächst gehört das Netz und die Config auf richtige Beine gestellt. Das macht aber nur einen kleinen Teil aus. Mit Hardware kannst du nur bis zu einem gewissen Grad Performaceprobleme ausgleichen. Der Rest ist abhängig vom Aufbau und Güte deines SQL Server, der Datenbank, der Zugriffssteuerung und den Selects......
also löblich erstmal deine Beschreibung. Hier würde man sich mehr davon wünschen. Das ist aber zunächst auch alles was ich loben kann.
Zur Hardware:
Für einen SQL Server völlig falsche konfiguration. Zunächst einmal gehören hier hochdrehende SAS oder SCSI320 Platten zum Einsatz und kein IDE. In Zeitschriften wird immer lineares lesen oder schreibne verglichen. Das trifft hier nicht zu. Hier geht es um Zugriffszeiten und dabei sind SATA oder IDE Platten ein paar mal langsamer.
Auch das Raidlevel ist falsch gewählt. Weiterhin gehört System, Datenbank und Logdatei bei einem SQL Server getrennt. Beispielsweise Raid 1 für System mit zwei Platten, Raid 10 für Daten und Raid 1 für Log. WAS GLAUBST DU WAS LOS IST bei deiner Abfrage, wenn das Sytstem arbeiten muß, in deinen Daten sequenziell gelesen und geschrieben wird und gleichzeitig linear das Transaktionsprotokoll geschrieben wird. Das MUSS langsam sein !!!!!!!!
Bei einem Datenbankserver ist die größte Performancebremse zunächst mal das Festplattensubsystem.
Zur Umgebung:
Du hast nicht geschrieben, was auf dem Server noch so läuft. Ist das gleichzeitig noch Webserver, Domaincontroller oder sonst was ? Dann wäre das auch nicht so toll. Wenn du einen Nagel in die Wand hauen willst nimmst du ja auch einen Hammer und wenn du etwas Sägen willst, nimmst du eine Säge und nicht als Universalmittel das Schweizer Taschenmesser. Das mag in der Not helfen, aber ist für das tägliche Brot nichts.
Bezüglich RAM sollte du die /3GB Option benutzen, damit der RAM auch genutzt wird.
Im SQL Server sollte der RAM begrenzt und auf feste Größe gestellt werden.
Wichtig ist auch der Umgang - man glaube es nicht mit Pagefiles und Größe
Auch hier könnte ich mit den Windows Configs zu fortfahren.
Zu SQL:
Hier ist auch die Frage, wie gut die Datenbank aufgebaut ist. Das kann bereits eine riesen Perofrmancebremse sein. Gleiches gilt für die Qualität der Selects. Auch die parallelen Zugriffe und deren Abarbeitung. Schau mal, was tatsächlich auf deinem SQL Server passiert in dieser Zeit. Sperren, Locks - benutze den Profiler etc.....
Ich weiß, ich haue dir jetzt ein Brett vor den Kopf und vielleicht denkst du, was will der von mir, aber das ist nicht ein.... wie ändere ich den Hintergrund Problem....
Um dir zu helfen muß man das Netz, die Struktur, den SQL Server selbst, dessen Konfig, die Datenbank, die Zugriffe und die Selects kennen.
Zu deiner Frage: 2,2 Millionen Zeilen an sich sind erstmal kein Problem. Wir verwalten Millionen von Buchungen in einzelnen Tabellen.
Also wenn du aus dem Dilemma rauskomen willst, dann würde ich mal das Geld in die Hand nehmen, mir einen Consultant ins Haus holen, der dich hier richtig berät. Zunächst gehört das Netz und die Config auf richtige Beine gestellt. Das macht aber nur einen kleinen Teil aus. Mit Hardware kannst du nur bis zu einem gewissen Grad Performaceprobleme ausgleichen. Der Rest ist abhängig vom Aufbau und Güte deines SQL Server, der Datenbank, der Zugriffssteuerung und den Selects......
Hui - viel Antwort... vielen Dank schonmal
Aaaalso:
- Der Server macht nix ausser SQL. Dediziert also (sonst hätte ich das auch erwähnt bei meiner Beschreibung des Problems)
- RAM ist fest eingestellt - und der SQL-Server kann ihn auch nutzen
- Raid-Level haste definitiv recht
. Ist nur im nachhinein etwas schwierig zu ändern . Ich dachte darüber nach vielleicht die TempDb auf nen Stripe aus 2 oder 3 Platten zu legen... damit sollte ich zumindest die masse an Schreibzugriffen abfedern können, oder?
- Immerhin isses nen richtiger Raidcontroller mit eigenem Cache, das rettet zumindest ein bisches was von der Latenz
Aaaalso:
- Der Server macht nix ausser SQL. Dediziert also (sonst hätte ich das auch erwähnt bei meiner Beschreibung des Problems)
- RAM ist fest eingestellt - und der SQL-Server kann ihn auch nutzen
- Raid-Level haste definitiv recht
. Ist nur im nachhinein etwas schwierig zu ändern . Ich dachte darüber nach vielleicht die TempDb auf nen Stripe aus 2 oder 3 Platten zu legen... damit sollte ich zumindest die masse an Schreibzugriffen abfedern können, oder?- Immerhin isses nen richtiger Raidcontroller mit eigenem Cache, das rettet zumindest ein bisches was von der Latenz
Hallo ByteBandito,
einiges hat linkit oben schon angesprochen, aber was ich bis jetzt nicht gesehen habe, was auch zu gewaltigen Problemen führen kann, sind nicht aktualisierte Statistiken für den Optimizer. Ein "exec sp_updatestats" im Query Analyzer hat schon Wunder bewirkt. Außerdem gibt es noch die Möglichkeit, die Indices zu reorganisieren, schau mal beim Wartungsplan.
Fehlerhafte HDD-/Raid-Controller haben auch schon zu Performanceproblemen geführt.
Gruß, Mad Max
einiges hat linkit oben schon angesprochen, aber was ich bis jetzt nicht gesehen habe, was auch zu gewaltigen Problemen führen kann, sind nicht aktualisierte Statistiken für den Optimizer. Ein "exec sp_updatestats" im Query Analyzer hat schon Wunder bewirkt. Außerdem gibt es noch die Möglichkeit, die Indices zu reorganisieren, schau mal beim Wartungsplan.
Fehlerhafte HDD-/Raid-Controller haben auch schon zu Performanceproblemen geführt.
Gruß, Mad Max
