Monday, July 24, 2006

Fluctuating number of rows during data insertion

Today I noticed something interesting. As I was migrating a table from MyISAM to InnoDB, I saw the number of rows fluctuate up and down as time passed suggesting that SHOW PROCESSLIST wasn't actually reporting the exact number of rows but rather an estimate.

mysql> show table status like 'guestbook_v2' \G
*************************** 1. row ***************************
Name: book_v2
Engine: InnoDB
Version: 9
Row_format: Dynamic
Rows: 7505110
Avg_row_length: 272
Data_length: 2043674624
Max_data_length: NULL
Index_length: 391118848
Data_free: 0
Auto_increment: 7980185
Create_time: 2006-07-24 20:40:23
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 6144 kB
1 row in set (0.01 sec)

mysql> show table status like 'guestbook_v2' \G
*************************** 1. row ***************************
Name: book_v2
Engine: InnoDB
Version: 9
Row_format: Dynamic
Rows: 7138176
Avg_row_length: 287
Data_length: 2055208960
Max_data_length: NULL
Index_length: 392167424
Data_free: 0
Auto_increment: 8018719
Create_time: 2006-07-24 20:40:23
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 9216 kB
1 row in set (0.00 sec)

mysql> show table status like 'guestbook_v2' \G
*************************** 1. row ***************************
Name: book_v2
Engine: InnoDB
Version: 9
Row_format: Dynamic
Rows: 7434343
Avg_row_length: 277
Data_length: 2059403264
Max_data_length: NULL
Index_length: 393216000
Data_free: 0
Auto_increment: 8035298
Create_time: 2006-07-24 20:40:23
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 11264 kB
1 row in set (0.05 sec)

No comments: