2

I am running a sql script on mysql 5.7.18 ubuntu server 16.04 that does a large number of updates in one table. Each one of the 50k updates takes under a second but the total run time is very long (>20m).

I can see from PROCESSLIST that only one thread is running and is processing the updates one-by-one.

How can I use multiple threads to speed the execution ?

1

Because you wrote "each one of the..." it sounds like you are updating individual rows. Rather than doing a row at a time, have you explored writing the SQL so you are letting the DB update many rows in one SQL statement? That will likely get you much better performance than trying to multi-thread manually.

| improve this answer | |
  • You are correct. Unfortunately we cannot edit the query at this point. Can we at least make it run more parallel ? – rwms Jun 9 '17 at 13:23
  • 1
    Split your set of data to update into n equally sized sets and launch n copies of your update script that connect independently to the DB. This may be faster or slower depending on where your DB server's bottlenecks are. You can see them doing something like this with bash on Percona (MySQL fork) here: percona.com/blog/2014/01/07/… – CalZ Jun 9 '17 at 13:26
1

From Book: High Performance MySQL - 2nd Ddition - 2008

MySQL can’t execute a single query in parallel on many CPUs. This is a feature offered by some other database servers, but not MySQL. We mention it so that you won’t spend a lot of time trying to figure out how to get parallel query execution on MySQL!

https://www.oreilly.com/library/view/high-performance-mysql/9780596101718/ch04.html

From Percona blog - 23 Jan 2019

Finally: MySQL 8.0.14 has (for now limited) an ability to perform parallel query execution. At the time of writing it is limited to select count(*) from table queries as well as check table queries.

https://www.percona.com/blog/2019/01/23/mysql-8-0-14-a-road-to-parallel-query-execution-is-wide-open/

| improve this answer | |
0

Few suggestions:

Make sure you have defined proper index. To define them, read the beautiful answer posted by Gordon Linoff here a SO answer

Secondly, Refer this SO answer. It tells you about optimization tricks like

show create table table1;
explain select * from table1

and

OPTIMIZE TABLE table1;

Basically, both the answers talk about index.

| improve this answer | |
0

I don't think that MySQL natively supports any kind of multi-threading. There is concept called sharding that may help you. This requires that you install a plugin for running.

more information and a Shard tool can be found here: https://www.percona.com/blog/2014/01/07/increasing-slow-query-performance-with-parallel-query-execution/

EDIT: You have already made a comment that you can't change your query. I'm afraid that you may be stuck in a single threaded world. I think that Oracle does this on purpose to get you interested in their software and ready to upgrade.

| improve this answer | |
  • True. I think that MariaDB has a similar plugin for free but I was looking for a quick and dirty solution. Splitting into multiple queries is the way. Thanks anyway. – rwms Jun 9 '17 at 13:43
0

You can get some benefit (in InnoDB) with innodb_flush_log_at_trx_commit = 2.

You can avoid a lot of overhead if you first insert into a "staging" table, then copy into the 'real' table. This discusses such.

I hope that your 50K is just a burst. If you have that continually, your disk will quickly fill up.

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