github / github/gh-ost

Simplify the chunk range condition generate logic, reduce the risk of using wrong execute plan(enhancement)

Offen
#855 5 Kommentare 0 Reaktionen 0 zugewiesene Personen Auf GitHub ansehen
enhancement
Vorherrschende Sprache
Go
Sterne
13.6k
Forks
1.4k
Ø Merge
2 Std. 31 Min.
Gemergte PRs (30 T.)
4

Beschreibung

> We meet a performance case when use gh-ost to do ddl

### Problem Description

When we do a ddl on a table which has a composed primary key, active session arise quickly and the database hang。 But when we do the same ddl operation using pt-online-schema-change, it worked。 It is very confusing。 So we want to do a deep analysis to get the reason。

### The reason We got

when we compare the chunk sql of pt-osc and gh-ost . The Range condition has some different like below:
The table's primary key compose with three columns : shard_key, bt_id, object
for the same chunk
start : (973,50,.trash/9d16-4b95-bb50-xxxxxxxxxx155 )
end : (973,50,.trash/9d16-4b95-bb50-xxxxxxxxxx2ss )

#### the range condition from gh-ost

```
WHERE (`shard_key` > _binary '973'
OR (`shard_key` = _binary '973'
AND `bt_id` > _binary '50')
OR (`shard_key` = _binary '973'
AND `bt_id` = _binary '50'
AND `object` > _binary '.trash/9d16-4b95-bb50-xxxxxxxxxx155'))
AND (`shard_key` < _binary '973'
OR (`shard_key` = _binary '973'
AND `bt_id` < _binary '50')
OR (`shard_key` = _binary '973'
AND `bt_id` = _binary '50'
AND `object` < _binary '.trash/9d16-4b95-bb50-xxxxxxxxxx155')
OR (`shard_key` = _binary '973'
AND `bt_id` = _binary '50'
AND `object` = _binary '.trash/9d16-4b95-bb50-xxxxxxxxxx2ss'))
```

#### The range condition from pt-online-schema-change

```
WHERE (`shard_key` > '973'
OR (`shard_key` = '973'
AND `bt_id` > '50')
OR (`shard_key` = '973'
AND `bt_id` = '50'
AND `object` >= '.trash/9d16-4b95-bb50-xxxxxxxxxx155'))
AND (`shard_key` < '973'
OR (`shard_key` = '973'
AND `bt_id` < '50')
OR (`shard_key` = '973'
AND `bt_id` = '50'
AND `object` <= '.trash/9d16-4b95-bb50-xxxxxxxxxx2ss'))
```
We Can see that the condition of gh-ost is more complex than pt-osc. when we check the sql execution plan, they are all using primary key .
pt-osc'sql scan only 499 rows , but gh-ost scan 20 millions rows and it take 30 seconds to accomplish .

We think the query condition is more complex, the database optimizer has more risk to using a wrong plan .

### Optimization

We change the chunk query condition generate logic to make gh-ost generate a chunk sql query like pt-osc does . Making the condition more simple and readable , We also do more test about data consistency, It worked well now . So we want give this pr to gh-ost .


> Thank you!

Beitragsleitfaden

Beitragsleitfaden öffnen

Rechercherichtung

Beginnen Sie damit, die Logik zur Erzeugung von Bereichsbedingungen für Chunks zu lokalisieren, und vergleichen Sie deren Ausgabe mit den Beispielen für pt-online-schema-change im Issue. Validieren Sie die überarbeiteten Bedingungen anhand des gemeldeten Falls mit zusammengesetztem Primärschlüssel, der Datenkonsistenz, der Zeilenanzahl und der Ausführungspläne; als erledigt gilt die Aufgabe, wenn einfacheres generiertes SQL ohne den übermäßigen Scan entsteht.

Vom Indexierungsmodell aus dem Issue-Text verfasst.

Bewertung

Tech-Stack
go, mysql
Bereich
databases
Issue-Typ
Feature
Schwierigkeit
4/5
Geschätzter Aufwand
3-5 Tage
Aktivitätsstatus
Veraltet
Klarheit
Größtenteils klar
Anfängerfreundlichkeit
35/100

Neue Issues direkt in Ihr Postfach

Eine kurze Übersicht über anfängerfreundliche GitHub-Issues.