Multithreading program

Shell Programming and Scripting
1

Hi

I need to insert 1million records into MySQL database, but it is taking lot of time as there is no bulk insert support.

I want to spawn 10 processes which will insert 100k records each parallely.

Can somebody help me with a example program to execute this task through shell scripting.

2

You mean bulk insert like in LOAD DATA? Have you tried using transactions, which will only commit to storage (aka "slow you down") when you're finished?

3

HINT:
use xargs -n <XXX> -P <XXX> for multithreading

4

Thanks for the reply but I didnt understand how xargs can be helpful in my case.
Can you please explain it in my case, how it can be used with an example?

Thanks.

5

echo -e {1..100}"\n" | xargs -n 1 -P 10 script.sh

in script.sh:
<insert file to db> $1.ext ...

split 1 000 000 records to files with names 1 to 100 (*.ext)

**one of the many ways

6
  1. Multithreading means "you must be stupid to do this in shell"
  2. Loading 1 huge set of data at once is in general faster than loading dzylion of microsets in parallel
  3. In Oracle you have SQLLoader and it has mode called "direct" - check if you cannot use similar thing in MySQL (I have no experience in MySQL)
  4. If there is no option of direct loading of the data you might want to use bulk load methods
  5. Loading in multiple threads means that you most probably are going to create dzylion of connections... if creation and closing of a single connection takes 1 second and inserting 1 row of data takes 0.0000000001 of a second... imagine how much slower would it work if you create multiple threads
  6. Transaction is your friend - until you commit your application (executable compiled application) should be able to insert rows with only a minor performance decrease - if this is really really required by you then you might assume that there is a probable chance that you might need to create an application that loads row-by-row and commits the changes afterward.... really no bulk methods? I was thinking that MySQL is a developed database but it is not if there are no bulk operations.