Excessive contexting running MariaDB CE Query
I'm working on a MariaDB vs. Oracle query performance issue. We have about 25 queries that we run on MariaDB 10.1.11 vs. Oracle 11.2.0.4. In all cases Oracle is several times faster than MariaDB. The environments are similar: running RHEL 6.5 on VMware guest. I focused on one representative query to drill down. This query runs in 13 seconds on Oracle and 30 seconds on MariaDB. The access plans look the same using the index with columns in the where clause to access the data. I don't see heavy I/O. The longest running step in the profile is below:
| Sending data | 29.728961 | 32.786016 | 1.111831 | 100822 | 67799 |
The 100822 is Context_voluntary and the 67799 is context involuntary. A the "cs" field in a vmstat jumps up to around 8,000 as soon as I start the query and goes back to hundreds when it ends:
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu----- r b swpd free buff cache si so bi bo in cs us sy id wa st 2 3 89744 74424 103624 6447400 0 0 0 12 2322 9256 56 3 38 4 0 << query running 0 0 89744 74424 103636 6447404 0 0 0 0 152 241 1 0 91 9 0 << query ends
My assessment is that the problem is excessive context switches. I tried increasing thread_pool_oversubscribe from 3 to 10, thread_pool_size from 2 to 10, and thread_pool_stall_limit to 1,000 each parameter one at a time and bouncing the instance. In all cases the query runs at a consistent 27-29 seconds. We are also doing heavy parallel insert testing. When we run the job, vmstat cs jumps to the same 8,000 or so. Our Linux SA cannot see any context related configuration differences between the Oracle and MariaDB servers. Any ideas will be appreciated.
