how to execute mysql queries concurrently/parallelly



  • 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.



  • 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.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2