Tabellenvergleich performance

[mikexxl]

die Umstellung des index-Key dauert immer noch an.

Weiß nicht warum. Unter show processlist steht immer noch: copy to tmp table
Die zweite tabelle hat noch nicht den Index


Nur zum Verständnis, wenn ich tab1 mit tab 2 vergleiche, und als Ergenbis nur tab2 ohne tab1 (alle, welche nicht in tab1 sind), ist das nicht ein right join?

[mikexxl]

was ich gemachr habe:
habe tabelle tab1 und tab2 neu erstellt, x als VARCHAR 50 und index

tab 1 hat 2 und x spalten, tab 2 hat 11 und x spalten
Einspielen der Daten
mysql> insert into `db`.`tab1` (a,b,x)
-> select a,b,
-> CONCAT(a,' ',b) AS x
-> from `db`.`quelle`;
Query OK, 14372615 rows affected (6 hours 38,35 sec)
Records: 14372615 Duplicates: 0 Warnings: 0

mysql> insert into `coxulto`.`tab2` (spalten,x)
-> select spalten,
-> CONCAT(r,' ',p) AS x
-> from `db`.`quelle2`;
Query OK, 13629550 rows affected, 1 warning (9 min 2,78 sec)
Records: 13629550 Duplicates: 0 Warnings: 1

Warum diese Zeitunterschiede??

dann deine Befehle ausgeführt:
mysql> EXPLAIN
-> SELECT COUNT(*)
-> FROM tab2
-> LEFT JOIN tab1
-> ON a.x = a1.x
-> WHERE a.x IS NULL;
+----+-------------+-------+-------+---------------+---------+---------+--------------------+----------+--------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+--------------------+----------+--------------------------------------+
| 1 | SIMPLE | tab2 | index | NULL | x | 152 | NULL | 13629550 | Using index |
| 1 | SIMPLE | tab1 | ref | x | x | 152 | db.tab2.x | 10 | Using where; Using index; Not exists |
+----+-------------+-------+-------+---------------+---------+---------+--------------------+----------+--------------------------------------+
2 rows in set (0,03 sec)

mysql> SELECT COUNT(*)
-> FROM a1
-> LEFT JOIN a
-> ON a.Telefon = a1.Telefon
-> WHERE a.Telefon IS NULL;
+----------+
| COUNT(*) |
+----------+
| 643384 |
+----------+
1 row in set (24 min 59,76 sec)

mysql> show global variables LIKE '%buffer%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_size | 16777216 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 8388608 |
| join_buffer_size | 131072 |
| key_buffer_size | 16777216 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 8192 |
| preload_buffer_size | 32768 |
| read_buffer_size | 262144 |
| read_rnd_buffer_size | 524288 |
| sort_buffer_size | 524288 |
| sql_buffer_result | OFF |
+------------------------------+----------+
14 rows in set (0,02 sec)

Nur zum Verständnis, warum left join und nicht right join?

Kannst Du mir erklären, was ich da gerade gemacht habe?

Danke
MikeXXL

[thomas_w]

Zitat von mikexxl

mysql> SELECT COUNT(*)
-> FROM a1
-> LEFT JOIN a
-> ON a.Telefon = a1.Telefon
-> WHERE a.Telefon IS NULL;
+----------+
| COUNT(*) |
+----------+
| 643384 |
+----------+
1 row in set (24 min 59,76 sec)

Nur mal auf die schnelle dies, heute abend habe ich mehr Zeit.

a)

Zitat von mikexxl

mysql> show global variables LIKE '%buffer%';
| key_buffer_size | 16777216 |

Der key_buffer_size mit 16MB ist zu klein. Sie sollte so 25-30% des verfügbaren RAM sein. Bitte ändern. Anschließend ist ein restart der MySQL Engine notwendig.


b) Dies war ein Treffertest und ungefähre Abschätzung für den Abgleich und INSERT in die Zieletabelle. Der SQL könnte also so aussehen.

INSERT INTO  zieltabelle
SELECT * 
  FROM tab2
  LEFT JOIN tab1
    ON tab1.x = tab2.x
 WHERE tab1.x IS NULL;

Jetzt kannst Du ja mal eine INSERT/Abgleich Test machen. Die ZIEL Tabelle sollte noch keine INDEX haben. Diese erst anlegen, wenn der INSERT fertig ist. Dann geht das Ganze schneller.


c) LEFT or RIGHT JOIN

Hat ja nur was mit der Reihenfolge der Tabellen in der FROM Klausel zu tun.

Grüße
Thomas

[mikexxl]

Hallo,
irgendetwas haut nicht hin.

wenn ich das eingebe
INSERT INTO zieltabelle
SELECT *
FROM tab2
LEFT JOIN tab1
ON tab1.x = tab2.x
WHERE tab1.x IS NULL;
bekomme ERROR 1136 (21S01): Column count doesn't match value count at row 1
tab1 hat 3 spalten, tab2 & ziel sind gleich, ziel hat kein index, kein key
was mach ich falsch?
nur zum verständnis, die 10er muss gegn die 3er abgeglichen werden. Welche nicht in der 3er vorhanden sind, müssen in die zieltabelle.
Danke
mikexxl

[thomas_w]

Zitat von mikexxl

nur zum verständnis, die 10er muss gegn die 3er abgeglichen werden. Welche nicht in der 3er vorhanden sind, müssen in die zieltabelle.

Verstehe ich nicht 10er .. 3er ??

Zitat von mikexxl

bekomme ERROR 1136 (21S01): Column count doesn't match value count at row 1

Nun ja, mein Fehler. Die Zieltabelle muss identisch zur Tabelle tab2 sein. Dann sollte folgendes gehn. Es sollen nur die tab2 Spalten übertragen werden.

INSERT INTO  zieltabelle
SELECT tab2.* 
  FROM tab2
  LEFT JOIN tab1
    ON tab1.x = tab2.x
 WHERE tab1.x IS NULL;

Grüße
Thomas

[mikexxl]

O.K, nujn meine hoffentlich letzte frage:
habe ja nun debian und LAMPP installiert.
wo kann ich den folgenden befehl ausführem?
mysqld_safe --key_buffer_size=1024M --sort_buffer_size=64M
im ordner root@debian:/opt/lampp/bin#
funktioniert der Befehl nicht
Danke
mikeXXL

[thomas_w]

Zitat von mikexxl

O.K, nujn meine hoffentlich letzte frage:
habe ja nun debian und LAMPP installiert.
wo kann ich den folgenden befehl ausführem?
mysqld_safe --key_buffer_size=1024M --sort_buffer_size=64M
im ordner root@debian:/opt/lampp/bin#

Ob dies so geht.. keine Ahnung.

Die MySQL Parameter werden eigentlich in der my.ini / my.cfg in Abschnitt

[mysqld]
key_buffer_size=1024M
sort_buffer_size=1M

angepaßt. Oder temporär für die MySQL Connection in der Kommandozeile.

/* 1024 MB */
mysql>SET GLOBAL KEY_BUFFER_SIZE = 1024 * 1000 * 1024;

/* 1 MB reicht aus */
mysql>SET GLOBAL SORT_BUFFER_SIZE = 1 * 1000 * 1024;

Wie lange dauert der Abgleich jetzt?

Grüße
Thomas

[mikexxl]

So, habe vor deinem posting die Parameter in der my.cfg geändert.

Dar abgleich dauerte nun TATAAA

Query OK, 643384 rows affected (25 min 29,33 sec)
Records: 643384 Duplicates: 0 Warnings: 0


super, danke

werde mir jetzt noch alles notieren, mache da ja nur einmal im Jahr.

Vielen Dank
MikeXXL