7

I have big db of size 8GB and having 9 million contacts in this db. When we try to retrieve all contacts, it takes nearly 1 or 2 hour (in some cases result in timeout) to retreive all contacts for the excecution for a single query. Also I have 8 CPU and 24GB RAM, Xeon Processor, the mysql process won't distribute accross the 8 CPU's. My question is that, does we can enable multi threading for a single query.

Awaiting for your Reply

  • Are the tables that have those 9 million rows in InnoDB or MyISAM format? – Craig Efrein Jan 10 '14 at 9:57
9

Neither MyISAM nor InnoDB have multithreaded query implementation. Nor do other XtraDB, TokuDB. It is a long standing issue and limitation of MySQL. I don't know that the limitation is within the MySQL server, but rather in the storage engine implementation. No one ever did it...

I'm currently evaluating the column-store InfiniDB and Infobright IEE. They both support multi-cpu queries and the difference is overwhelming (due to multiple reasons, but CPU being obvious one).

You might want to look at Shard Query, a proxy to MySQL which splits your query into multiple queries, executes them on parallel processes and returns the aggregated result.

| improve this answer | |
  • It's horrible this has not been fixed by now. Innodb: I have 10-13mb/sec read rates single query. When splitting the read up into 20 queries I can reach up to 400mb/sec read. From an IO point of few it's ridiculous but it's just how the engine behaves. – John Jan 24 '19 at 18:51
  • Sometimes it's difficult to discern what order the query should perform its task, but I think it would be good if MySQL had an option, acknowledging query-order will be unpredictable. Also, the link to Shard Query appears to be: github.com/greenlion/swanhart-tools/tree/master/shard-query as the link posted leads to something irrelevant. – JVE999 Mar 22 at 20:32
5

I read a good blog post on some approaches to this recently;

http://www.mysqlperformanceblog.com/2014/01/07/increasing-slow-query-performance-with-parallel-query-execution/

| improve this answer | |
0

MySQL does not offer a way to split the processing of a single query across multiple threads. Instead, I recommend to implement parallelism in the application layer. MySQL will happily process separate SELECT statements in parallel. If you have an auto-increment ID, you can subdivide the table into N parts by using SELECT ... WHERE id % N = 0. Use N processes or threads to handle each select statement. Multiple processes is a bit easier to set up than than multiple threads. You can use xargs to run multiple processes in parallel.

| improve this answer | |

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.