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.