[MySQL] DB/Hardware-Optimierung für schnelles INSERT/UPDATE

[dow]

Hallo,

ich stehe gerade vor der Herausforderung 1,5GB an xml-Files in eine MySQL-Datenbank zu mappen.
Dazu nutze ich ein C# Programm welches die Umsetzung übernimmt.

Wenn ich die aktuelle Performance hochrechne, werde ich nicht vor Ende Januar fertig sein . Ziel wäre wenigstens dieses jahr .

Es wird immer zuerst ein UPDATE ausgeführt und bei Nicht-Erfolg ein INSERT nachgeschoben, dies gilt für alle Tabellen.
Grund ist, dass Produkte in neueren xml-Files teilweise aktualisiert werden, INSERT IGNORE musste ich verwenden, da manchmal attribute aktualisiert wurden welche zwar in der xml-Datei vorhanden sind ich aber in MySQL nicht brauche.

Im Moment befinden sich in der Tabelle products 11.000 Einträge, entries 135.000 Einträge, files 214.000 Einträge. 76 von 1600 xml-files sind eingespielt

MySQL-Server-Hardware:
Ein CoreDuo 6300 1.86GHz, 1GB RAM mit normaler 7200rpm SATA Platte hat vorher mit der Datenbank im MyISAM-Format ca. 20min pro 1MB xml-File gebraucht, CPU-Last dabei 50% also eine CPU voll ausgelastet.
Jetzt läuft die DB im InnoDB-Format auf einem Xeon HT 2.4GHz, 2GB RAM mit einem aktuellen 10.000rpm-RAID10-Verbund CPU-Last dabei ca. 50%, das erste Testfile lief in 16min durch, denke erst wenn noch drei durch sind kann es wirklich Aufschluss geben.
System ist dabei überall WinXP oder 2003 Server, es muss nichts anderes laufen.
Dummerweise bekomme ich nichts von der Plattenlast mit, da alles Remote läuft.

Möglichkeiten zur Optimierung:
Ich hätte ca. 10 P4 mit 2.8GHz HT, 1GB RAM mit 7200rpm SATA falls Clustering etwas bringt.
Im Notfall könnte ich zum 1. Weihnachtsfeiertag den Xeon für 2 Tage komplett mißbrauchen um z.B. ein Linux-System darauf zu installieren.
In den CoreDuo kann man eine 10krpm SCSI-Platte setzen, RAID auszuschließen mangels PCI 32bit-Controller, ausserdem wäre es möglich den RAM auf 2GB aufzustocken.
Vorbehaltlich es bringt wirklich etwas, experimentell wäre jeglicher Aufwand einfach zu groß.

Als Client mit dem C#-Programm nutze ich einen CoreDuo, welcher allerdings keine merkliche Last zeigt, weder CPU noch HDD noch Netzwerk.

Beispiel-Code für Insert:

UPDATE `entries` SET `name`="Testeintrag",`description`="Dies ist die Beschreibung dazu" WHERE (UPPER(`entries`.`article_id`)=UPPER("JI34567890")) AND (UPPER(`entries`.`entryno`)=UPPER(12)) AND (UPPER(`entries`.`setno`)=UPPER(1));
INSERT IGNORE INTO `entries` (`article_id`,`entryno`,`setno`,`name`,`description`) VALUES ("JI34567890",12,1,"Testeintrag","Dies ist die Beschreibung dazu");

Die Tabellen habe ich von MyISAM auf InnoDB umgestellt, da ich an einigen Stellen gelesen habe, dass InnoDB bei INSERT und UPDATE schneller ist.

An der Struktur ist wahrscheinlich nichts wesentliches zu ändern, habe schon ein paar Sachen herausgeworfen bzw. Datenformate geändert.

Datenbankstruktur aus phpMyAdmin

CREATE TABLE `products` (
  `article_id` varchar(12) collate latin1_general_ci NOT NULL default '',
  `products_quantity` int(4) NOT NULL default '0',
  `products_image` varchar(64) collate latin1_general_ci default NULL,
  `products_price` decimal(15,4) NOT NULL default '0.0000',
  `products_date_added` datetime NOT NULL default '0000-00-00 00:00:00',
  `products_last_modified` datetime default NULL,
  `products_date_available` datetime default NULL,
  `products_weight` decimal(5,2) NOT NULL default '0.00',
  `products_status` tinyint(1) NOT NULL default '0',
  `products_tax_class_id` int(11) NOT NULL default '0',
  `manufacturers_id` int(11) default NULL,
  `products_ordered` int(11) NOT NULL default '0',
  `category` varchar(55) collate latin1_general_ci NOT NULL default '',
  `barcode` varchar(13) collate latin1_general_ci NOT NULL default '',
  `distributor_id` varchar(4) collate latin1_general_ci NOT NULL default '',
  `distributor` varchar(60) collate latin1_general_ci default NULL,
  `brand_code` varchar(5) collate latin1_general_ci NOT NULL default '',
  `brand_name` varchar(250) collate latin1_general_ci default NULL,
  `brand_short_name` varchar(10) collate latin1_general_ci default NULL,
  `name` varchar(255) collate latin1_general_ci default NULL,
  `shortname` varchar(50) collate latin1_general_ci NOT NULL default '',
  `fixed_price` double(7,2) default '0.00',
  `fixed_price_code` varchar(4) collate latin1_general_ci default NULL,
  `number_of_entries` int(3) default '0',
  `activation_date` date default '0000-00-00',
  `deactivation_date` date default NULL,
  `isused` tinyint(1) default NULL,
  `set_total` int(3) NOT NULL default '0',
  `variant` varchar(4) collate latin1_general_ci NOT NULL default '',
  `products_viewed` int(5) default NULL,
  `valid_date` date default NULL,
  `subcat_code_1` int(3) default NULL,
  `subcat_code_2` int(3) default NULL,
  `subcat_code_3` int(3) default NULL,
  `subcat_code_4` int(3) default NULL,
  `subcat_code_5` int(3) default NULL,
  `subcat_1` varchar(50) collate latin1_general_ci default NULL,
  `subcat_2` varchar(50) collate latin1_general_ci default NULL,
  `subcat_3` varchar(50) collate latin1_general_ci default NULL,
  `subcat_4` varchar(50) collate latin1_general_ci default NULL,
  `subcat_5` varchar(50) collate latin1_general_ci default NULL,
  PRIMARY KEY  (`article_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--
-- Dumping data for table `products`
--


-- --------------------------------------------------------

--
-- Table structure for table `files`
--

CREATE TABLE `files` (
  `article_id` varchar(12) NOT NULL default '',
  `entryno` int(2) NOT NULL default '0',
  `setno` int(3) NOT NULL default '0',
  `url` varchar(120) NOT NULL default '',
  `format` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`article_id`,`entryno`,`setno`,`url`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `files`
--


-- --------------------------------------------------------

--
-- Table structure for table `entries`
--

CREATE TABLE `entries` (
  `article_id` varchar(12) character set latin1 collate latin1_general_ci NOT NULL default '',
  `entryno` int(2) NOT NULL default '0',
  `setno` int(3) NOT NULL default '0',
  `name` text character set latin1 collate latin1_general_ci,
  `description` text character set latin1 collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`article_id`,`entryno`,`setno`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

my.ini auf XEON mit InnoDB

# The MySQL server
[mysqld]
port		= 3306
skip-locking
key_buffer = 384M
max_allowed_packet = 2M
table_cache = 512
sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 64M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4
#log mysqld.log
tmp_table_size = 256M
max_heap_table_size = 128M
#skip-networking
#log-bin
server-id	= 1
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/local/var/
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_group_home_dir = /usr/local/var/
#innodb_log_arch_dir = /usr/local/var/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 768M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

my.ini auf CoreDuo mit MyISAM

# The MySQL server
[mysqld]
port		= 3306
skip-locking
key_buffer = 384M
max_allowed_packet = 2M
table_cache = 512
sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 32M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4
#log mysqld.log
#tmp_table_size = 256M
#max_heap_table_size = 128M
#skip-networking
#log-bin
server-id	= 1
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/local/var/
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_group_home_dir = /usr/local/var/
#innodb_log_arch_dir = /usr/local/var/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 768M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

 

[Enigma]

Ich würde folgendes Ändern:
Schreib dir einen Parser der alle XML-Daten einliest und die generierten SQL-Anweisungen in eine Datei schreibt. Verwende statt dem UPDATE/INSERT Syntax lieber den REPLACE Syntax.

Die Datei mit den ganzen SQL befehlen, würde ich dann in eine MYISAM Datenbankstruktur einlesen über die mysql-kommandozeile (also mysql -u root -p <datei datenbank). Wenn das einlesen fertig ist, die MYISAM in eine INNODB umwandeln (ALTER TABLE).

Nachtrag: Kurze Frage: Läuft die mySQL auf dem gleichen Rechner wie der Parser? Falls nein, wieviel ms Latenz sind zwischen den beiden Rechnern?

 

[dow]

zum Nachtrag: es läuft auf zwei verschiedenen Rechnern, da es mir danach aussieht als wäre die CPU oder die Festplatte der Flaschenhals, da wollte ich die 1% CPU-Leistung durch den Parser sparen .
Aus eben diesem Grund dachte ich auch die Latenz spielt einfach mal keine Rolle.

Da ein Query ja kaum durch MySQL auf 2 Threads verteilt werden kann (?) denke ich mal mit 50% Auslastung beim Xeon HT und auch beim CoreDuoCPU ist die CPU limitierend.

Die Idee mit dem Replace wird wohl einiges helfen, ich frage mich nur warum ich es das letze mal verworfen habe.

Um die ganze Sache in ein Textfile auszugeben bin ich gerade erstmal zu blöd, beim rüberschieben durch MyODBC hilft mir ein Programm den Code zu erstellen und ich muss nur noch stellenweise anpassen.
Das schaue ich mir morgen mal an, theoretisch muss ich ja nur den Connector rausnehmen und irgendwie mit Dateizugriffen arbeiten .

Schönes Weihnachtsfest und Danke schonmal, bei mir wartet jetzt die Familie.

 

[Enigma]

Hmm ok - dann wandel aber trotzdem die INNODB in eine MYISAM. Das bringt einiges an Performance.
Was auch schneller als einzelne Inserts sind, sind Inserts bei denen gleich mehrere Datensätze auf einmal eingefügt werden. Also z.B: 10 Zeilen aus dem XML lesen und an die Datenbank schicken, statt 10 einzelne Inserts.

2 Threads bekommst du nur durch 2 Datenbankverbindungen hin. Also starte dein Programm 2x und jede instanz sollte andere Daten einfügen (z.B gerade/ungerade dateinamen).

 

[Keita]

Mit REPLACE solltest Du nur dann (und wirklich nur dann) arbeiten, wenn sichergestellt ist, daß die referenzielle Integrität nicht verletzt wird, da sich ein REPLACE aus einem DELETE und einem INSERT zusammensetzt. Bei hinreichend aktueller Version unterstützt MySQL auch das INSERT ... ON DUPLICATE KEY ... -Konstrukut, d.h. Du kannst MySQL dazu veranlassen bei einem fehlgeschlagenen INSERT ein UPDATE auszuführen, was im ON DUPLICATE KEY-Block näher spezifiziert wurde.
Neben den bereits erwähnten multiplen INSERTs hilft es auch alle Indizes sowie Foreign Key Constraints zu deaktivieren, die Inidizes müssten natürlich nachträglich gesetzt werden und Du mußt auf eine andere Weise sicherstellen, daß die referenzielle Integrität sichergestellt ist.
Zuguterletzt hilft es auch einfach eine temporäre Tabelle mit der gleichen Struktur zu erstellen, die Daten in diese Tabelle zu importieren und das INSERT aus dieser Tabelle heraus auszuführen, das minimiert den Overhead, den jeder Query zwangsläufig mit sich schleppt.

greetings, Keita

 

[ag3nt]

Hallo dow,
also ein richtiger Performance-Killer ist deine UPDATE-Anweisung. Ein zweiseitiger Funktionaler Vergleich zieht dir die Performance absolut in den Keller, insbesondere wenn sie auf Zahlen angewendet wird, wie in deinem Fall. Wenn du keine Case-sensitiven Collations nutzt, dann schmeiss die UPPER-Funktion komplett raus (latin1_general_ci ist zum Beispiel CASE-INSENSITIVE), für den anderen Fall sie dir mal das COLLATE-Statement etwas genauer an.
Ansonsten sind das Deaktivieren von PK und UK + Indizies ebenfalls eine Idee um den Vorgang zu beschleunigen aber das hat Keita ja bereits erläutert.
Generell ist für solche Massendaten-Imports allerdings der LOAD-Befehl eindeutig vorzuziehen. Der Geschwindigkeitsunterschied kann ziemlich groß ausfallen, von daher wäre das vermutlich der beste Ansatz.

cya ag3nt

 

[Enigma]

Da es aber XML-Daten sind kannste das mit LOAD leider nicht machen

 

[Keita]

Mittels XSLT hast Du binnen Sekunden das ganze so aufbereitet, daß sich der Wust mittels LOAD einlesen ließe

greetings, Keita

 

[dow]

Also, jetzt mit REPLACE statt UPDATE+INSERT 2 1/2 min pro 1MB xml-File und nach Änderung wieder auf MyISAM 3 sec pro File.

Ein xslt dazu habe ich übrigens auch, weiß nur nicht wie mir das genau beim Einspielen hilft.

 

[Enigma]

Sag mir bitte nicht, dass der Unterschied wirklich 5000% war - aso 5 gegen 150 sekunden Denn das ist ... jenseits von gut und böse und ich müsste ernsthaft überlegen, ob wir in der Firma weiterhin auf InnoDB setzen.

 

[XunnD]

Schon versucht, mehrere Inserts/Updates in einer Transaktion zusammenzufassen, dass er die Inserts erst puffert und dann schreibt!? Bei InnoDB geht das mit BEGIN...COMMIT

In den SQL-Dumps/Befehlen muss man am Anfang ...

SET AUTOCOMMIT=0;
START TRANSACTION;

...ausführen und am Ende dann mit...

COMMIT;

...alles schreiben, was man gepuffert hat.
Das hat bei mir so EINIGES an Performance rausgeholt (11.000 Datensätze in etwa 2-3 Sekunden importiert [via Shell] ) !

Ich würde allerdings auch vom Netzwerk und seine Latenz als Ursache in Betracht ziehen. Was spricht dagegen, die Files lokal umzuwandeln und auf dem Server in der Shell auszuführen?

MfG, XunnD

 

[dow]

Files lokal umwandeln -> bei mir spricht dagegen, dass ich mich dann erstmal mit C# und dem Filesystem beschäftigen müsste . Ich habe jetzt auch die kompletten Daten mehrmals eingespielt (Fehler->neu einspielen) und bin mit der Performance zufrieden.

Da es auch nur ein Erstbestand ist und sonst 3-6 Files pro Tag als update kommen ist es egal ob es 6 oder 30sec braucht .

Die Datenbank eignet sich jetzt auch super um etwas über performance-optimierungen bei den Queries zu lernen, habe schlussendlich jeweils 4,9 Mio, 3,3 Mio und 600.000 Einträge in den drei Tabellen, da merkt man recht schnell ob sich ein anderer Query auswirkt.

@enigma
es waren ja jetzt nur REPLACE-Queries
Mit der Performance im Betrieb muss ich jetzt erstmal etwas probieren, da das wohl zu 97% SELECT Statements sein werden allerdings soll da ja gerade MyISAM schneller sein.

 

[davidbaumann]

Ich hätte jetzt auch gesagt, dass es auf jeden Fall sehr viel rausholen würde, wenn du die Scripts auf dem einen PC erstellst und dann per shell einspielst.
Da würde ich auf jeden Fall dran bleiben!

Gruss,
David