0

I have to find out the "EXACT" row count of 5000 tables running MySQL 5.5 on RHEL5. As far as I know the only method is "SELECT COUNT(*) from table_name". But this is taking too much time.

So I tried to run the queries in parallel. I split the queries in 10 different files each containing 500 queries and run them from 10 different terminals.I also tried by logging with different uid/pwd. Every time it is taking same time. What I am missing? What to do to run these queries in parallel.

It is a 16 vCPU and 94GB RAM system. Throughput is also decent, I think. Almost every table has a primary key . It takes more than 6 hrs in all.

0

3 Answers 3

0

If you are not using that ENGINE=INNODB, say so; this answer is totally wrong.

Regardless of how you run it, the same amount of data must be read from disk. That is why parallelism is useless, at least when there is more data than has been cached in RAM.

You should have set innodb_buffer_pool_size appropriately (70% of available RAM).

There is a trick that will speed up some of the Counts. Add a secondary index to any large table; have it index the smallest column, such as an ENUM or TINYINT. (Don't add an extra index if you already have such; don't bother with doing such for tiny tables.) This trick helps because InnoDB picks the least bulky index to use for the COUNT(*), thereby doing the least I/O.

0

We upgraded to MySQL 8.0. I think it handles parallelism better than 5.5

I ran these queries in parallel from 10 different sessions and execution time came down to 2.4hrs from 11hrs.

0
0

With your MySQL 8.0, try this QUERY please.

SELECT IFNULL(table_schema,'Total') db,COUNT(1) 
    table_count,SUM(information_schema.tables.table_rows) row_count 
    FROM information_schema.tables WHERE table_schema 
 NOT IN ('information_schema','performance_schema','mysql','sys','innodb')
  AND table_type='Base Table'  
GROUP BY table_schema WITH ROLLUP; 

You may need to put the 1st 2 lines displayed here together please.

Expect table count and row counts by schema with Totals on last line. View profile for contact info and get in touch, if needed.

Please post the differential between your EXACT count and the row count Total this query provided AND the amount of time this query consumed.

Credit original idea to RolandoMySQLDBA RDS MySQL I/O freeze during backup causes RDS instance to crash

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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