Roman, ----- Original Message ----- From: "Roman Neuhauser" <[EMAIL PROTECTED]> To: "Heikki Tuuri" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, September 24, 2003 11:46 PM Subject: Re: innodb: storage requirements
> # [EMAIL PROTECTED] / 2003-09-24 17:41:29 +0300: > > the row count in SHOW TABLE STATUS is only an estimate based on 8 > > dives into the index tree. > > ok. > > > You had a typical symptom of a fragmented table: space usage much bigger > > than you would expect. > > that doesn't answer my questions :) notice the numbers: > > > > mysql> SHOW TABLE STATUS LIKE 'editor_competence_product'\G > > > *************************** 1. row *************************** > > > Name: editor_competence_product > > > Type: InnoDB > > > Row_format: Fixed > > > Rows: 225198 > > > ^^^^^^ > > here, InnoDB thinks the table has 225198 rows. > > > > mysql> ALTER TABLE editor_competence_product TYPE=InnoDB; > > > Query OK, 187654 rows affected (47.34 sec) > > > Records: 187654 Duplicates: 0 Warnings: 0 > > here, 187654 rows is reported. > > > > mysql> SHOW TABLE STATUS LIKE 'editor_competence_product'\G > > > *************************** 1. row *************************** > > > Name: editor_competence_product > > > Type: InnoDB > > > Row_format: Fixed > > > Rows: 187265 > > > ^^^^^^ > > yet another number. what I don't understand is why the second and > third number differ. ALTER TABLE reports an exact count. After the reorganization, also SHOW TABLE ... happens to report almost the right number. This is expected, because the reorganization packs pages full of records. If you insert rows (< 1000 bytes) sequentially in the order of the primary key, then actually SHOW TABLE STATUS is a good measure of fragmentation. If its estimate differs a lot from the real row count, that is a symptom of a f ragmented table. > but this question is more important, and I'd really *love* to know > an answer: > > > > Also, is there a way to *measure* the fragmentation of a table? If there > > > is, how should the info be interpreted? Sorry, no. But adding such a feature would be easy: just look at page numbers, page fillfactors, and the total size of the 'segment' storing the B-tree leaf pages. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
