1

I've recently been doing some database maintenance & stumbled upon something rather odd. During a standard procedure cleaning up i found that the row count before running pt-online-schema-change was much lower than after running pt-online-schema-change. Initially i thought this was a display bug & tried reconnecting. However, the row count stayed the same.

Before running my database maintenance i removed 10.000.000 rows off of a table with 26.000.000 rows. After this i was down to 16.000.000 rows. Then i ran pt-online-schema-change to add a column. Suddenly after it was done 2 hours later it showed 18.000.000 rows. All this is during hours where there is almost no activity in this table. & further digging showed that there was only added about ~11.000 rows while pt-online-schema-change ran.

So. How can one explain this? Could it simply be that the row count from MySQL got inaccurate after a mass deletion? Because that is the only reasonable explanation i my-self can think of as pt-online-schema-change creates a new table & migrate data. Which would mean the row count on that table is fresh.

I use Sequel Pro which seems to use SHOW TABLE STATUS LIKE '...'; to read row count from.

Does anyone have any experience with row counts jumping after running pt-online-schema-change?

Paul Spiegel
  • 30,925
  • 5
  • 44
  • 53
danniehansenweb
  • 465
  • 4
  • 14
  • 1
    Where or how do you read the row count? – Paul Spiegel Apr 22 '19 at 20:34
  • Hi Paul, i use Sequel Pro which seems to use 'SHOW TABLE STATUS LIKE '...';' to read row count from what i can tell. – danniehansenweb Apr 22 '19 at 21:12
  • 1
    `SHOW TABLE STATUS LIKE 'table_name'` doesn't show exact row count. For my 30M rows table it is showing `28897829`. After `ANALYZE TABLE ..` it is showing `29206478`. So that number is not exact and can change without changes in the data. – Paul Spiegel Apr 22 '19 at 21:22
  • @PaulSpiegel Ah okay. Guess that explains my findings using pt-online-schema-change. Do you know if there is any official documentation on this? Explaining just that behavior? – danniehansenweb Apr 22 '19 at 21:31
  • @PaulSpiegel Nvm - found https://stackoverflow.com/questions/8624408/why-is-innodbs-show-table-status-so-unreliable on the subject. Feel free to submit a answer & i'll approve. – danniehansenweb Apr 22 '19 at 21:32
  • 1
    I think it's fine to mark it as duplicate. – Paul Spiegel Apr 22 '19 at 21:42

0 Answers0