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 ?

rwms
  • 201
  • 1
  • 3
  • 10

6 Answers6

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.

CalZ
  • 934
  • 4
  • 10
1

From the book, High Performance MySQL - 2nd Edition - 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 the 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/

Paul White
  • 94,921
  • 30
  • 437
  • 687
Daniel
  • 209
  • 1
  • 3
  • 10
1

You can achieve some mysql parallelism with xargs:

cat <./file.sql> | xargs -n <num of cores> -d '\n' -I {} mysql -e {}

The xargs -d '\n' statment make only \n be the delimiter (usefull if each line o .sql file has spaces)

-n Limits the number of arguments per command line generated by xargs. Without -n 1 and using -P 0, chances are that only one exec will be done sometimes.

-P Limits the number of xargs generated processes at a time (the default is 1). -P 0 (P zero) is the same of -P ($nproc).

But, despite of more than one instance o mysql run in parallel (in separate subshells), the performance don´t achieve 2x. Maybe some DB lock issue.

Rohit Gupta
  • 2,116
  • 8
  • 19
  • 25
Renato Romano
  • 11
  • 1
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.

Prabhat G
  • 179
  • 8
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.

Jmaurier
  • 797
  • 2
  • 6
  • 20
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.

Rick James
  • 80,479
  • 5
  • 52
  • 119