Mysql – estimate the row count of a large thesql table using the disk space

MySQL

Sysadmin here (not a developer by trade), so go easy please 😉

I have a mysql table, on a linux server that uses innodb_file_per_table.

When I try to do select count(foo) from table; It doesn't finish. I gave it a good 5 minutes before interrupting.

The table.ibd file is about 43G (45176848384). Since the count() seemed like it was never going to finish, I decided to try the following to get an estimate of the row count:

I dumped the first 10,000 rows of the table and imported it to a local database that also has innodb_file_per_table and I hope all else equal.

That file, after I imported it, (with only 10,000 rows) is about 2M (2097152)

So:

45176848384/2097152
21542
21542*10000
215420000

Then, is it correct to assume that the ~43G table is around 215 million rows? Are there other considerations I'm overlooking?

Or is there a better way for me to determine how many rows are in this table?

Best Answer

You cannot make that kind of approximation work if the InnoDB table experiences DELETE and UPDATEs. With innodb_file_per_table enabled and a table called mydb.mytable, this is what you get

/var/lib/mysql/mydb/mytables.frm : Table Format Info
/var/lib/mysql/mydb/mytables.ibd : This file contains data pages and index pages making up the table and its indexes (Each page is 16KB).
- There will be fragmentation amongst the data pages due to DELETEs and UPDATEs
- There will be fragmentation amongst the index pages due to BTREE operations (node balancing, node splits, etc).

Even if you defragment the InnoDB with OPTIMIZE TABLE, there is no way of knowing if a single row occupies two or more pages.

ALTERNATIVE APPROACH

You could probably force the index statistics to give you a round figure. You could just run

SHOW INDEXES FROM mydb.mytable;

Look at the Cardinality column of the PRIMARY KEY. That will give you a very close guess at the rowcount. However, if you run SHOW INDEXES FROM mydb.mytable; multiple times on a large table, you get a different number each time. That's because InnoDB will scan the BTREE nodes and give a roundabout number. If you want to go by the Cardinality of the PRIMARY KEY as the rowcount, you should run this

SET GLOBAL innodb_stats_on_metadata = 0;

before running SHOW INDEXES FROM mydb.mytable;

Believe me, this is a much better guess that dumping a subset of the data and guessing from the filesize.

Related Solutions

Mysql – Cannot write to thesql database while select query in process

Your first query is asking for 20,000 rows. what is being victimized here ???

Your Innodb Buffer Pool and Log Files.

Because of SELECT * FROM history WHERE log LIKE '%a%' ORDER BY log ASC LIMIT 0, 20000;, all the data pages for the history table is hogging InnoDB resources.

Since you are using Amazon RDS, you cannot resize the InnoDB Log Files. They are always 128M under all RDS MySQL models. Your buffer pool may or may not be full (You have 5882511360 set for it, which is 5610M or 5.4785G). Naturally, bigger model means more IOPs.

You can run this to see how full the buffer pool is

SELECT FORMAT(A.num * 100.0 / B.num,2) BufferPoolFullPct FROM
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;

Another aspect I see is the log field (TEXT). You are asking for an ORDER BY on this big column.

You need to change the first query to throttle the SELECT. In other words, fetch smaller chunks of history.

If you cannot change the data or the query, there is nothing you can configure except to shift to m1.xlarge (Comes with 11922309120 as the Buffer Pool Size = 11370M = 11.1035G) although log file size would still be stuck at 128M.

Infrastructure aside, it is possible for SELECTs to block INSERTs, UPDATEs, and DELETEs in InnoDB.

I wrote about this before

MySQL – How to Recover Disk Space After Deleting Rows from Table

You will have to take the table offline to prevent any reads or writes that would get locked and probably have to roll back. If the table is called mydb.mytable, here is what you must do:

USE mydb
DROP TABLE IF EXISTS mytablenew;
CREATE TABLE mytablenew LIKE mytable;
ALTER TABLE mytable RENAME mytableold;
INSERT INTO mytablenew SELECT * FROM mytableold;
ANALYZE TABLE mytablenew;
ALTER TABLE mytablenew RENAME mytable;
DROP TABLE mytableold;

Any queries attempting to read from or write to mytable will fail immediately rather than hang on a lock from OPTIMIZE TABLE.

Give it a Try !!!

UPDATE 2013-08-26 15:24 EDT

Look back at the code I suggested. Please note the line

ALTER TABLE mytable RENAME mytableold;

That line takes the table offline with respect to the application.

To get a good idea how long it should take to optimize run the following:

USE mydb
CREATE TABLE mytablenew LIKE mytable;
INSERT INTO mytablenew SELECT * FROM mytable;

However long this takes, is how long it will actually take to run.