List:General Discussion« Previous MessageNext Message »
From:Mike Spreitzer Date:February 21 2008 4:41pm
Subject:Number of rows not constant
View as plain text  
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.

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