List:General Discussion« Previous MessageNext Message »
From:Dan Rogart Date:February 21 2008 4:54pm
Subject:Re: Number of rows not constant
View as plain text  
Hi,

On 2/21/08 11:41 AM, "Mike Spreitzer" <mspreitz@stripped> 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


Thread
Number of rows not constantMike Spreitzer21 Feb
  • Re: Number of rows not constantDan Rogart21 Feb
    • Re: Number of rows not constantMike Spreitzer21 Feb
  • Re: Number of rows not constantDan Nelson21 Feb