Hi, On 2/21/08 11:41 AM, "Mike Spreitzer" <[EMAIL PROTECTED]> wrote:
> I have a table with millions of rows. I am not sure exactly how many rows > it has, I get a different answer every time I ask! What's going on here? > > This DB is used only by me, and only by explicit commands --- I have no > background or on-line tasks using the DB. This is the DB that took 2 days > to load and another two days to add a column and index. I decided to let > that column+index addition to complete, and it has now completed. I am > using the GUI administrator tool; in the Catalogs section I select the > relevant schema; in the right hand side I select the Tables tab. The > listing for my table (I have only the one) says Type=InnoDB, Row Format = > Compact, Data Length = 4.56 G, Index Length = 8.55 G, and Update Time is > blank. It is the Rows datum that is surprising --- every time I hit > "Refresh" I get a different number under Rows. It varies between 23 > million and 28 million. It is not monotonically increasing, nor > monotonically decreasing. Sometimes the number of rows goes up, sometimes > it goes down. BTW, before the late addition of a column+index, the Rows > datum was 27,413,306. I did not notice this Rows variability before > adding a column+index --- but probably would not have, I had no reason to > Refresh the display repeatedly. I first noticed this Rows variability > during the column+index addition. > > I am running MySQL 5.0.51a-community on RHEL 4 on a 4-processor (as far as > Linux is concerned) Intel 32-bit machine, with storage on the only local > HDD. I am using MySQL Administrator version 1.2.12, and it also says I am > using MySQL Client Version 5.0.30. I am running the admin tool on the > same machine as the MySQL server. That machine is otherwise idle. I > monitor CPU, network, and disk with Procmeter3, updated every 5 seconds. > It usually reports 0% CPU and 0 disk I/Os per period, and suitably low > network traffic. Sometimes I get a spike up to 5 disk I/Os in some 5 > second period, presumably to some background thing(s) Linux and/or MySQL > is doing. When I hit Refresh, I get 22 or 23 disk I/Os and 2% CPU for > about 5 seconds. > > Thanks, > Mike It sounds like the GUI Administrator is using SHOW TABLE STATUS to get the row count for you, since that's much faster than a count(*). SHOW TABLE STATUS can only provide an estimate for the innodb row count and it can vary a lot. The first two comments here are particularly apropos: http://mysqldatabaseadministration.blogspot.com/2006/07/where-did-records-go .html -Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
