1

I have a database table that according to TABLE STATUS has 7MM rows, but when I SELECT COUNT(*) it only has 500k rows.

This is a problem because table growth is increasing and we're running low on storage now.

here is schema: MySQL 8.0.15

CREATE TABLE `tasks` (
  `task_id` binary(24) NOT NULL,
  `task` json NOT NULL,
  `task_kryo` mediumblob,
  `task_type` varchar(180) COLLATE utf8_bin GENERATED ALWAYS AS (json_unquote(json_extract(`task`,_utf8mb4'$.t'))) STORED,
  `created` datetime GENERATED ALWAYS AS (cast(left(json_unquote(json_extract(`task`,_utf8mb4'$._task.timestamp')),19) as datetime)) STORED,
  `last_updated` datetime GENERATED ALWAYS AS (cast(left(json_unquote(json_extract(`task`,_utf8mb4'$._task.latestStatus.timestamp')),19) as datetime)) STORED,
  `latest_status` varchar(180) COLLATE utf8_bin GENERATED ALWAYS AS (json_unquote(json_extract(`task`,_utf8mb4'$._task.latestStatus.t'))) STORED,
  `marker` binary(24) GENERATED ALWAYS AS (json_unquote(json_extract(`task`,_utf8mb4'$.marker'))) STORED,
  PRIMARY KEY (`task_id`),
  KEY `task_type_index` (`task_type`),
  KEY `created_index` (`created`),
  KEY `last_updated_index` (`last_updated`),
  KEY `latest_status_index` (`latest_status`),
  KEY `marker_index` (`marker`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

This is MySQL RDS on AWS. We have system backups disabled because this table is entirely disposable data. As a result, there is no binary log for this table. AWS disables that when you disable system backups apparently

My suspicion is that we use JSON "Merge" operations in Update statements, and because binary logging is disabled by AWS (we don't have any backups for this table as it is fully disposable / scratch data) somehow it is implementing the updates as inserts on the table (old records remain but are not deleted).

See https://mysqlhighavailability.com/efficient-json-replication-in-mysql-8-0/

binlog-row-value-options=PARTIAL_JSON

Also, the MySQL log contains this warning on restart:

2020-05-21T20:02:08.506526Z 0 [Warning] [MY-013103] [Server] When binlog_row_image=FULL, the option binlog_row_value_options=PARTIAL_JSON will be used only for the after-image. Full values will be written in the before-image, so the saving in disk space due to binlog_row_value_options is limited to less than 50%.
3
  • Have you tried optimize table ? – JYOTI RAJAI May 28 '20 at 6:42
  • What is the real question? Is it about reported number of rows? That has been answered. Or is it about disk space consumed? If so, let's see more numbers relating to that. Or maybe about some form of fragmentation? Again, more numbers. – Rick James May 30 '20 at 0:59
  • The real question is why did my database "size" grow to 50 gb - and growing - while the size of data - as found by a query like select sum(octet_length(... on all the columns of the table reported a sum of < 10gb. The answer appears to be "problems with InnoDB, possibly bugs." – noahlz May 30 '20 at 1:06
2
+50

This has more to do with InnoDB

As stated in the documentation to table status

The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40% to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.

So COUNT(*) gives you the exact number.

8
  • I'm aware of this difference. But 500k vs 3MM rows? Something else is going on. – noahlz May 26 '20 at 23:55
  • that happens when you have a lot of traffic, the 50% are only rough estimate to signal that it differs a lot from COUNT(*), but if you are worried make dev.mysql.com/doc/refman/8.0/en/rebuilding-tables.html – nbk May 27 '20 at 0:21
  • Even so, innodb should reuse storage from deleted records. In this case, storage was plummeting with each record UPDATE, and not being reclaimed at all when we purged several 100k records – noahlz May 27 '20 at 0:37
  • mysql has still many faults, that aren't soved like all databases for that matter. InnoDB is still the best we can get, so we all have to live with it. but your problem with the row numbers isn't one, you can't rely on the row numbers given only count does the trick.Json like all other data that is not normalized, is in my opinion always bad, especially with updates. Json is good like xml to exchange data, but for storage it is bad, when you must update it. – nbk May 27 '20 at 2:47
  • Also, bulky columns (TEXT / BLOB / JSON) seem to aggravate the inaccuracy of the "row count". – Rick James May 28 '20 at 22:28
0

Try gathering the updated statistics with analyze table tasks; and see if show table status reflects the value from select count(*) from tasks. The select count(*) should always return the exact value while table statistics (shown in show table status) might not be up to date.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.