This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What is a Multithreaded Database?
For any database system bottleneck is a disk and CPU which spikes during the heavy operations like arithmetic operations, bulk load operation.
In the Multithreaded Database, the result set of a query can split and process into different cores of the server.
Internally Multithreaded Database uses a thread for various purposes like background SQL Job, reading/writing, locking, unique operation, and Network IO.
In this post, I am going to show how to configure MySQL InnoDB Engine for the Multithreaded purpose.
InnoDB Engine is an already performing great with a single CPU environment, but if you want to take advantage of Multi Threading, you should configure below list of parameters.
innodb_thread_concurrency : you can set the number of concurrent threads between 0 to 1000. Default is 0 and this interpreted as infinite concurrency.
innodb_concurrency_tickets : Default is 500, the Min value is 1 and Max value is 4294967295. You can set large values in which large transactions spend less time for waiting and You can set small value in which small transactions spend less time for waiting.
You can balance this value by up or down to find the balance between larger and smaller transactions.
innodb_thread_sleep_delay : How long InnoDB threads sleep before joining the InnoDB queue, in microseconds. The default value is 10000. A value of 0 disables sleep.
innodb_read_io_threads : Default is 4, Minimum value is 1, Maximum value is 64. This defines the total number of threads for reading.
innodb_write_io_threads : Default is 4, Minimum value is 1, Maximum value is 64. This defines the total number of threads for writing.
innodb_read_ahead_threshold : Default value is 56 and Maximum value is 64. It uses to fetch pages in the buffer pool and read at least 56 pages sequentially. We can also find how many pages are read through this read-ahead mechanism and how many pages removed from buffer pool without access.
SHOW ENGINE INNODB STATUS shows the rate of how many pages are read per second and how many pages removed from the buffer pool.
innodb_rollback_on_timeout : This is a boolean parameter and if it is true and a timeout occurs, InnoDB to abort and roll back the entire transaction.
For more details you can visit this MySQL Official Link:
