I setup a 6 node XtraDB cluster (3 on one data center and the other 3 on a different data center). We are running a test in which we are using 30 threads to execute in parallel a sql file containing 3333 insert commands into a table. Each insert represents a transaction. IfI run 1 thread it takes about 8 sec to insert the 3333 records. If I run 3 threads in parallel it still takes 8 secs… If i run more than 3 threads in parallel every extra thread takes about 8 sec. For example 4 threads will load in 16 secs, 5 threads will load in 24 secs, etc… In the end I need about 250 secs (or more) to load 100000 records. For this cluster writes will happen on the same node (no multim master writing). wsrep_slave_threads is set to 10. What would you recommend to change (in the config file, etc…) in order to improve parallel inserts for the above test?
Thank you,
Liviu
can you look at the activity of the db during the insert process using “show engine innodb status \G;”
you can also check if the log buffer is waiting on flushing
or if fsync/s are high?
various parameter to consider here