Negative numbers in InnoDB Status

Question

There are some negative numbers displayed in the InnoDB Engine Status output. Anybody has encountered this or know why this happens ?

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1467883704; in additional pool allocated 26603520
Dictionary memory allocated 7134560
Buffer pool size 79552
Free buffers 0
Database pages 74624
Modified db pages 15997
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 12607789, created 151074, written 3737768
-7.21 reads/s, -0.17 creates/s, -1.10 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
6 read views open inside InnoDB
Main thread id 7652, state: sleeping
Number of rows inserted 10757341, updated 2461678, deleted 1189294, read 7916897438
-14.00 inserts/s, -5.18 updates/s, -0.00 deletes/s, -14185.96 reads/s

I am using MySQL Version 5.1.51 (64 bit) on Windows Server 2008

Answer 1

Off the top of my head, I would say that since InnoDB was originally built for a 32-bit OS, I can easily see computations where numbers higher that the largest 32-bit signed number (2147483647 or 2^31 - 1) can create negative results due to sign overflow.

Takes add the values and their rates and divides them

• inserts : 10757341 / -14 = -768381.6
• updates : 2461678 / -5.18 = -475227.413127
• deletes : 1189294 / 0 = ????
• reads : 7916897438 / -14185.96 = -558079.7801488232

Since dividing row operations by the rates do not yield the same time elapsed for InnoDB's lifetime (or possibly just the real-time seconds elapsed per operation), you have to suspect a floating sign issue.

What would be a possible culprit in this instance?

• Uptime : The number of seconds that the server has been up.
• Uptime_since_flush_status : The number of seconds since the most recent FLUSH STATUS statement.

If MySQL were up for 2 Billion Seconds, MySQL would have to running 62.4196 years for Uptime to be a noticeable issue. MySQL has only been around since 1995. Notwithstanding, Uptime and Uptime_since_flush_status status values could be possible culprits due to the 32-bit nature of InnoDB and retrieval of this information is from SHOW ENGINE INNODB STATUS\G.

SUGGESTIONS

SUGGESTION #1 : Since you are using MySQL 5.1.51, you should install the InnoDB Plugin (available since MySQL 5.1.38).

SUGGESTION #2 : Upgrade to MySQL 5.5 (not 5.6 just yet)

CAVEAT

In either case, you just need to upgrade the InnoDB Storage Engine. Before doing so, please check for any release notes on the Latest Release of InnoDB to see if this was resolved.

Just so that this perspective is to be taken seriously, notice that negative results can be retrieved even in MySQL 5.6.7 (Negative values could be erroneously reported for some columns in the buffer_pool_pages_in_flush row in the information_schema.innodb_metrics table. (Bug #14090287))

This was also a noticeable problem with the Windows version of MySQL 5.1.55 : http://www.filehorse.com/download-mysql-32/7399/change-log/ say * Some unsigned system variables could be displayed with negative values. (Bug#55794)

This issue with SHOW ENGINE INNODB STATUS goes way back to MySQL 4.0.26 : http://forums.mysql.com/read.php?22,67566,67566#msg-67566

Answer 2

I was able to replicate a similar condition by setting the system clock on a server (not Windows) to a time before the time when the MySQL Server process was started. The numbers in my SHOW ENGINE INNODB STATUS are small since this is a test machine, but they're suddenly negative after changing the clock.

-0.00 inserts/s, -0.00 updates/s, -0.00 deletes/s, -0.01 reads/s

You might try this:

mysql> SHOW STATUS LIKE 'uptime';

This is a value in seconds that appears to be calculated as the difference between the current system time and what the system time was when the server was started... and may be the same number used to calculate these stats.

If you see a massive number (an artifact of unsigned subtraction) that seems completely inconsistent with the server's true uptime then that would be confirmation that this is the cause.

If this is what you find, then I suspect the only remedy is to restart MySQL Server with the system clock set accurately.