I’m again returning to InnoDB scalability and related bug #15815 as it hurts many users and customers using multi-cpu servers.
Short intro into problem:
On 4-CPU box 1 thread executes full-table scan select query for 8 sec,
but with 4 threads – each thread executes query for 240 sec.
It is very strange as threads use only SELECT queries and ideally there should be no
any problem in concurrent enviroment, especially for CPU-bound workload.
I did the profiling which show the problem is with “buffer pool” mutex, which protects innodb_buffer_pool.
In details, for each scanned row InnoDB calls block_get / block_release functions
which aquire/release the block related to the current row. And the problem functions
block_get / block_release use mutex_lock(buffer_pool_mutex) / mutex_unlock(buffer_pool_mutex)
calls. So global mutex is accessed for each scanned rows, what in multi-CPU/multi-threading
enviroment results in “mutex ping-pong” problem.
Looking in source code of block_get / block_release function I don’t see obviosly reasons
to use global lock which can not be weaken to block level. I tried to replace buffer_pool_mutex in
these place to block_mutex and I got impressive results: now
each of 4 threads executes query for 11 sec.
So performance is increased by 240/11 ~= 21 times, and if before we had negative scalability, currently
scalabilty factor is 2.9 (the result with 4 threads = 2.9 * result with 1 thread).
The current patch can not be considered as stable as it touches many InnoDB subsystems,
but it looks like right direction to solve problem.
If possible could you post this patch ?
Thanks,
Wow, this patch would help me greatly — the whole problem explains a lot! haha, and here I thought it was on my side of things…
Just to let you know, I read / check this blog every day, and it has been a great resource to me and my efforts. Thanks.
Best,
Kristopher Tate
cto & founder — bluebridge tech / zooomr
Kristopher,
Thank you for your words!
It is very valuable to know my work is interesting.
Thanks,
Vadim.
Zemo,
I could post it, but I don’t want.
I know this patch crashes server on INSERT queries, so I’d want to fix obviously problems
before make it public.
Let me one-two week, maybe I will find a solution.
Thanks, this problem is really plagueing us at the momment , that is why I asked.
We currently run an 8way Opteron system, with MySQL/Innodb. MySQL just cant seem to scale very well. Our performance is 5x worse then our regular Dual Opteron server that our database was on. I find it mind blowing that MySQL AB allowed this problem to go unresolved for so long.
Zemo,
Yes, it is known problem with InnoDB, and no solution is available.
Well… you can use innodb_thread_concurrency=2 or 4, but in this case your 8way Opteron box will work as single Dual Core 🙂
There might be several sources of bad scalability.
Can you post SHOW MUTEX STATUS output ?
It would be interesting to look if you have contention on buffer_pool mutex or something else.
Zemo,
I modified patch and it does not crash InnoDB in my tests, so
I want to propose it for you.
http://www.mysqlperformanceblog.com/files/patches/patch.bufferpool1.diff
(Place it in 5.0.22 source tree and execute patch -p1 < patch.bufferpool1.diff). Don't consider this patch as stable! Please let me know how it works and report about crashes, thank you!
Valdim,
Thanks!.. I’ll try this out..
As for the information you requested.. The only workaround to make our server usable was to put innodb_thread_concurrency=4 in our my.conf .
For the SHOW MUTEX STATUS output , here you go ::
mysql> SHOW MUTEX STATUS ;
+—————————+————-+————–+————+————-+———-+———–+—————+
| Mutex | Module | Count | Spin_waits | Spin_rounds | OS_waits | OS_yields | OS_waits_time |
+—————————+————-+————–+————+————-+———-+———–+—————+
| &(trx->undo_mutex) | trx0trx.c | 1891 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 1927 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 1863 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 1951 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 1998 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2070 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 1977 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 1953 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2061 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2167 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 1911 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2027 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2035 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2030 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2170 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2106 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2037 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2121 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2176 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2094 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2100 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2098 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 1961 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2085 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2082 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2180 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2020 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2177 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2180 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2231 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2267 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2119 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2162 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2115 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2135 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2134 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2183 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2150 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2248 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2326 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2252 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2160 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2213 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2262 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2259 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2295 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2219 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2219 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2402 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2283 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2342 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2224 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2263 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2162 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2243 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2171 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2362 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2210 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2265 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2242 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2237 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2164 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2274 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2302 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2265 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2155 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2235 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2270 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2336 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2195 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2253 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2167 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2149 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2227 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2202 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2337 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2222 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2266 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2291 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2314 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2290 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2199 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2368 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2204 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2231 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2340 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2301 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2342 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2269 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2206 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2337 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2274 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2239 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2324 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2269 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2225 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2429 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2107 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2205 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2265 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2312 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2280 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2258 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2301 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2365 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2430 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2294 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2203 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2331 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2214 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2221 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2343 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2285 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2181 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2263 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2270 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2328 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2254 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2423 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2219 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2399 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2341 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2318 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2252 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2322 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2286 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2314 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2399 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2266 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2217 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2421 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2226 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2313 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2399 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2377 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2419 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2317 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2360 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2154 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2232 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2199 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2361 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2397 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2268 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2321 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2364 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2408 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2275 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2318 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2246 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2457 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2387 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2370 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2232 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2250 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2401 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2289 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2471 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2324 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2403 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2327 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2345 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2246 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2312 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2429 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2394 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2362 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2378 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2399 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2341 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2330 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2392 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2294 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2392 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2404 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2291 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2426 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2392 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2260 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2319 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2442 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2365 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2417 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2307 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2456 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2325 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2388 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2428 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2277 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2440 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2444 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 2375 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 58209 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 196943 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 1 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 51 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 23 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 1269 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 3386 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 50828 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 51 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 215 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 218 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 28 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 28 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 28 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 7182 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 28 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 92 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 92 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 30 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 30 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 30 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 30 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 30 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 30 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 30 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 30 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 32 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 30 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 30 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 30 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 41671 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 51 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 140242 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 51 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 1512980 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 174 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 205 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 1 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 1 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 51 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 51 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 23 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 23 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 639 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 174 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 291 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 178 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 23 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 6034 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 8 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 178 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 174 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 272 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 572 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 192331 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 174 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 195 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 7238 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 300 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 1218397 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 30 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 30 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 30 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 30 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 30 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 30 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 30 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 30 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 30 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 30 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 30 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 30 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 30 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 30 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 30 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 30 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 34 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 30 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 30 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 30 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 92 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 36 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 28 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 28 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 92 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 92 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 28 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 28 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 28 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 28 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 28 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 28 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 50 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 28 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 44 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 1044 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 30 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 32 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 32 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 28 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 85 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 87 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 19 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 85 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 91 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 126 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 12821 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 19708 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 202 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 205 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 224 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 53 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 53 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 25 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 25 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 1135 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 716 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 207 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 214 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 206 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 853 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 39 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 39 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 39 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 78 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 39 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 36 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 33 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 33 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 33 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 41 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 36 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 88 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 34 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 39 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 1830 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 95 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 579 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 102 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 395 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 144 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 144 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 144 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 144 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 144 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 144 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 150 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 146 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 144 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 144 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 144 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 144 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 144 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 144 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 144 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 144 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 468 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 144 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 144 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 144 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 217 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 16509 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 170 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 144 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 863234 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 144 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 19897 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 27028410 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 144 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 158 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 522 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 554 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 70211676 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 932554 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 144 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 3494 | 0 | 0 | 0 | 0 | 0 |
| &(purge_sys->mutex) | trx0purge.c | 229886976 | 0 | 0 | 0 | 0 | 0 |
| &(rseg->mutex) | trx0rseg.c | 589392072 | 582932 | 5876949 | 88884 | 197014 | 0 |
| &ibuf_bitmap_mutex | ibuf0ibuf.c | 371437 | 0 | 0 | 0 | 0 | 0 |
| &ibuf_mutex | ibuf0ibuf.c | 3849561 | 0 | 0 | 0 | 0 | 0 |
| &dict_foreign_err_mutex | dict0dict.c | 372930 | 0 | 0 | 0 | 0 | 0 |
| &(dict_sys->mutex) | dict0dict.c | 259527067 | 51378 | 274779 | 1632 | 3479 | 0 |
| &(recv_sys->mutex) | log0recv.c | 64 | 12 | 121 | 6 | 6 | 0 |
| &ios_mutex | srv0start.c | 13112284 | 4 | 0 | 0 | 0 | 0 |
| &(log_sys->mutex) | log0log.c | 773885913 | 466730 | 4249691 | 136192 | 180760 | 0 |
| &(buf_pool->mutex) | buf0buf.c | 341813408575 | 5131618035 | 5198457973 | 6753293 | 3944667 | 0 |
| &(system->mutex) | fil0fil.c | 81742208 | 66697 | 53105 | 35 | 40 | 0 |
| &srv_misc_tmpfile_mutex | srv0start.c | 372182 | 0 | 0 | 0 | 0 | 0 |
| &srv_dict_tmpfile_mutex | srv0start.c | 55459 | 0 | 0 | 0 | 0 | 0 |
| &srv_monitor_file_mutex | srv0start.c | 1 | 0 | 0 | 0 | 0 | 0 |
| &srv_innodb_monitor_mutex | srv0srv.c | 57877 | 0 | 0 | 0 | 0 | 0 |
| &kernel_mutex | srv0srv.c | 8326330740 | 17826281 | 130886065 | 1071867 | 2746135 | 0 |
| &thr_local_mutex | thr0loc.c | 811915 | 0 | 0 | 0 | 0 | 0 |
| &(pool->mutex) | mem0pool.c | 150512213895 | 1329851104 | 2160576205 | 459450 | 389068 | 0 |
| &rw_lock_list_mutex | sync0sync.c | 138309 | 0 | 0 | 0 | 0 | 0 |
| rw_lock_mutexes | | 395971608584 | 222851003 | 305839693 | 411527 | 114906 | 0 |
+—————————+————-+————–+————+————-+———-+———–+—————+
388 rows in set (0.03 sec)
Zemo,
Thank you,
As you see
buf_pool->mutex is most expensive one:
| &(buf_pool->mutex) | buf0buf.c | 341813408575 | 5131618035 | 5198457973 | 6753293 | 3944667 | 0 |
So I think you hit the described problem and my patch should relax load on the mutex.
Please inform me about results!
I will be testing this out on our quad opteron 875 server this weekend, I’ll keep you posted on the results.
Thanks!,
So far initial results are looking great. I raised innodb_thread_concurrency to 12. Currently I’m not seeing any of the problems I was seeing before when innodb_thread_concurrency was over 4.
On a side note, would you still recommend following the innodb_thread_concurrency = ( 2* cpu ) pattern ?
Thanks!,
Zemo,
Can you share what TPS (or whatever) you got with this patch and without it ? We all would like to know 🙂
Speaking about concurrency – theoretically 2*num_cpu is a target for CPU bound workloads but as we’ve seen in practice it is often not the best one so try and see what works for you.
Sharing SHOW MUTEX STATUS after you have patch applied also would be great.
Thank you Vadim.
I tried this patch.
However, it became a hit to the bug and MySQL stopped.
It reports on the bug.
==================test environment=================
OS=RedHatEL4 update3 EM64T
CPU=Xeon3.0GHz*4 with TH=ON
MEM=12G
innodb_thread_concurrency=20
innodb_buffer_pool_size=3G
===================================================
=================MySQL error log===================
060804 20:56:56 InnoDB: Started; log sequence number 45 3475747758
060804 20:56:56 [Note] /opt/mysql/bin/mysqld: ready for connections.
Version: ‘5.0.22-log’ socket: ‘/tmp/mysql.sock’ port: 3306 Source distribution
060804 21:25:22InnoDB: Assertion failure in thread 1115699552 in file buf0flu.c line 189
InnoDB: Failing assertion: (buf_pool->flush_list).count > 0
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html
InnoDB: about forcing recovery.
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.
key_buffer_size=33554432
read_buffer_size=2093056
max_used_connections=32
max_connections=250
threads_connected=31
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2591766 K
bytes of memory
Hope that’s ok; if not, decrease some variables in the equation.
060804 21:29:29 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files…
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer…
060804 21:29:29 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 45 3501426558.
InnoDB: Doing recovery: scanned up to log sequence number 45 3501428038
060804 21:29:29 InnoDB: Starting an apply batch of log records to the database…
InnoDB: Progress in percents: 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
060804 21:29:32 InnoDB: Started; log sequence number 45 3501428038
060804 21:29:32 [Note] /opt/mysql/bin/mysqld: ready for connections.
Version: ‘5.0.22-log’ socket: ‘/tmp/mysql.sock’ port: 3306 Source distribution
==========================================================
Toshikazu,
Thank you for testing the patch!
I think I found the reason of crash –
please try
http://www.mysqlperformanceblog.com/files/patches/patch.bufferpool2.diff
Vadim,
I immediately tried this patch.
Patch did not crash.
Thank you ^^/
Vadim,
Sorry for the delay, here is snip of SHOW MUTEX STATUS output with your original patch. I have not had problems with it, but just incase I’ll recompile with your patch.
…………
| &(block->blockmutex) | buf0buf.c | 32388 | 0 | 0 | 0 | 0 | 0 |
| &(block->blockmutex) | buf0buf.c | 2230 | 0 | 0 | 0 | 0 | 0 |
| &(buf_pool->mutex) | buf0buf.c | 2375090687 | 11868885 | 42507434 | 305779 | 307173 | 0 |
| &(system->mutex) | fil0fil.c | 15858707 | 5966 | 3656 | 5 | 9 | 0 |
| &srv_misc_tmpfile_mutex | srv0start.c | 20753 | 0 | 0 | 0 | 0 | 0 |
| &srv_dict_tmpfile_mutex | srv0start.c | 5187 | 0 | 0 | 0 | 0 | 0 |
| &srv_monitor_file_mutex | srv0start.c | 8 | 0 | 0 | 0 | 0 | 0 |
| &srv_innodb_monitor_mutex | srv0srv.c | 5757 | 0 | 0 | 0 | 0 | 0 |
| &kernel_mutex | srv0srv.c | 778621669 | 2111798 | 14420357 | 122435 | 306187 | 0 |
| &thr_local_mutex | thr0loc.c | 78340 | 0 | 0 | 0 | 0 | 0 |
| &(pool->mutex) | mem0pool.c | 15920112137 | 207890633 | 619535690 | 235717 | 196481 | 0 |
| &rw_lock_list_mutex | sync0sync.c | 131581 | 0 | 0 | 0 | 0 | 0 |
| rw_lock_mutexes | | 40258012646 | 50506378 | 140215245 | 317633 | 93041 | 0 |
+—————————+————-+————-+————+————-+———-+———–+—————+
131343 rows in set (0.17 sec)
Toshikazu, Zemo
Do you observe any changes in performance?
Does InnoDB perform better with patch ?
Thank you.
Vadim,
I used and tested TPCW.
In my environment, the performance went up by this patch by about 80%.
This patch is effective. Thank you.
==================test environment=================
OS=RedHatEL4 update3 EM64T
CPU=Xeon3.0GHz*4, enable HT
MEM=12G
innodb_buffer_pool_size=3G
===================================================
MySQL=5.0.22-standard
innodb_thread_concurrency=20
Throughput=311WIPS
MySQL=5.0.22-source + this Patch
innodb_thread_concurrency=20
Throughput=558WIPS
Toshikazu.
Vadim,
I think,
when we lock the block level mutexes, we must also lock a whole of buffer pool with a kind of “shared locks”,
to correspond to the intention of the remained “(buf_pool->mutex)”.
Therefore, I want to try the change of buf_pool’s mutex to s-lock&x-lock for safer implementation if I’ll have a chance.
Is this my idea correct?
Are there the large influences on the performance ?
Please tell me your opinion.
Regards,
Yasufumi,
Yeah, that makes sense…
However you have to find all places with buf_pool->mutex and replace to correct
(s-lock or x-lock) lock. That might take a lot of time and understanding of InnoDB internals – it is not impossible
but tough from my point of view.
Would reducing innodb_sync_spin_loops below its default of 20 be helpful here? With the patch there are still 2500 spin rounds per OS wait, so it looks as though it’s still burning a lot of CPU.
James,
I don’t think innodb_sync_spin_loops will help a lot, but you might try 🙂
Btw, how are results with patch and without ?
Is anyone using Vadim’s patch on a production system? We’re in the process of purchasing two Sun boxes (x4100, dual opteron 275) to run MySQL 5.0x on, and it seems obvious that we’re going to run into this issue. The bug report @ MySQL implies they may not backport a fix from 5.1. I’m left wondering what my options are — we need some of the additional functionality of 5.0, and we need the added power of the multi-core CPUs. Moving to 5.1 seems like a mistake at this point.
Andrew,
I’d not use my patch on production 😉
If the fix is available only for 5.1 I will try to port it in 5.0. But also I’d not expect the solution in 2-3 months.
Note, you may not meet scalability problems if you are not using long-range scan queries (index-scan or table-scan, no matter).
Otherwise you could buy two 2-way boxes and distibute load on two servers.
Can you tell by looking at the mutex status if we are affected by this bug? Right now we have the concurrency set to 16, but should probably be adjusted to 4. Both servers are Two way dual core servers. What ratios are you looking for when interpreting the data?
Server #1 (Xeon 5160)
+——————————–+————-+————–+————+————-+———-+———–+—————+
| Mutex | Module | Count | Spin_waits | Spin_rounds | OS_waits | OS_yields | OS_waits_time |
+——————————–+————-+————–+————+————-+———-+———–+—————+
| &(trx->undo_mutex) | trx0trx.c | 11097787 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 1 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 1 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 3 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 3 | 0 | 0 | 0 | 0 | 0 |
| &(purge_sys->mutex) | trx0purge.c | 14535880 | 0 | 0 | 0 | 0 | 0 |
| &(rseg->mutex) | trx0rseg.c | 43419570 | 2518 | 27246 | 317 | 897 | 0 |
| &ibuf_bitmap_mutex | ibuf0ibuf.c | 210191 | 0 | 0 | 0 | 0 | 0 |
| &ibuf_mutex | ibuf0ibuf.c | 265010 | 46 | 495 | 20 | 23 | 0 |
| &ibuf_pessimistic_insert_mutex | ibuf0ibuf.c | 528 | 0 | 0 | 0 | 0 | 0 |
| &(dict_sys->mutex) | dict0dict.c | 8415791 | 84 | 330 | 2 | 3 | 0 |
| &(trx_doublewrite->mutex) | trx0sys.c | 2412159 | 7 | 80 | 4 | 4 | 0 |
| &(recv_sys->mutex) | log0recv.c | 16 | 0 | 0 | 0 | 0 | 0 |
| &ios_mutex | srv0start.c | 2562463 | 0 | 0 | 0 | 0 | 0 |
| &(log_sys->mutex) | log0log.c | 104098379 | 40757 | 402675 | 14433 | 18328 | 0 |
| &(buf_pool->mutex) | buf0buf.c | 70739883283 | 4350334922 | 3239906212 | 2026290 | 562745 | 0 |
| &(system->mutex) | fil0fil.c | 53893011 | 940 | 3139 | 7 | 15 | 0 |
| &srv_innodb_monitor_mutex | srv0srv.c | 21679 | 8 | 80 | 4 | 4 | 0 |
| &kernel_mutex | srv0srv.c | 931679981 | 51334 | 347827 | 10652 | 13328 | 0 |
| &thr_local_mutex | thr0loc.c | 299278 | 0 | 0 | 0 | 0 | 0 |
| &(pool->mutex) | mem0pool.c | 3656780391 | 15352999 | 27614941 | 11730 | 9828 | 0 |
| &rw_lock_list_mutex | sync0sync.c | 655575 | 0 | 0 | 0 | 0 | 0 |
| rw_lock_mutexes | | 110855726270 | 1283069365 | 1479866288 | 1218471 | 206895 | 0 |
+——————————–+————-+————–+————+————-+———-+———–+—————+
Server #2 (Opteron 275)
| &(trx->undo_mutex) | trx0trx.c | 716 | 0 | 0 | 0 | 0 | 0 |
| &(trx->undo_mutex) | trx0trx.c | 453 | 0 | 0 | 0 | 0 | 0 |
| &(table->autoinc_mutex) | dict0mem.c | 3 | 0 | 0 | 0 | 0 | 0 |
| &(purge_sys->mutex) | trx0purge.c | 3493408 | 0 | 0 | 0 | 0 | 0 |
| &(rseg->mutex) | trx0rseg.c | 13029803 | 6669 | 81307 | 1251 | 3173 | 0 |
| &ibuf_bitmap_mutex | ibuf0ibuf.c | 11053 | 0 | 0 | 0 | 0 | 0 |
| &ibuf_mutex | ibuf0ibuf.c | 202058 | 355 | 3811 | 157 | 182 | 0 |
| &ibuf_pessimistic_insert_mutex | ibuf0ibuf.c | 949 | 0 | 0 | 0 | 0 | 0 |
| &(dict_sys->mutex) | dict0dict.c | 104532631 | 6180 | 23225 | 193 | 290 | 0 |
| &(trx_doublewrite->mutex) | trx0sys.c | 243102 | 10 | 100 | 5 | 5 | 0 |
| &(recv_sys->mutex) | log0recv.c | 36 | 0 | 0 | 0 | 0 | 0 |
| &ios_mutex | srv0start.c | 329737 | 0 | 0 | 0 | 0 | 0 |
| &(log_sys->mutex) | log0log.c | 31649419 | 121695 | 1236246 | 50548 | 58688 | 0 |
| &(buf_pool->mutex) | buf0buf.c | 3203737834 | 28984183 | 72561766 | 284450 | 410471 | 0 |
| &(system->mutex) | fil0fil.c | 25916775 | 2403 | 10082 | 46 | 84 | 0 |
| &srv_dict_tmpfile_mutex | srv0start.c | 3 | 0 | 0 | 0 | 0 | 0 |
| &srv_innodb_monitor_mutex | srv0srv.c | 5913 | 2 | 20 | 1 | 1 | 0 |
| &kernel_mutex | srv0srv.c | 512322207 | 3876530 | 19052043 | 148193 | 266380 | 0 |
| &thr_local_mutex | thr0loc.c | 259375 | 11 | 16 | 0 | 0 | 0 |
| &(pool->mutex) | mem0pool.c | 510796916 | 1408322 | 3189846 | 10851 | 13615 | 0 |
| &rw_lock_list_mutex | sync0sync.c | 655570 | 0 | 0 | 0 | 0 | 0 |
| rw_lock_mutexes | | 3167689045 | 404334 | 1608628 | 6657 | 3353 | 0 |
+——————————–+————-+————+————+————-+———-+———–+—————+
Andreas,
I think you are affected by scalability bug.
I’m looking on giant numbers on row with &(buf_pool->mutex). The same this mutex is problematic
and is fixed in my patch.
I installed your patch on two of the servers yesterday. Performance looks ok, but I can’t tell yet how big the improvment is.
Something worries me though. When I execute “show mutex status” I now get thousands of entries like this:
| &(block->blockmutex) | buf0buf.c | 4 | 0 | 0 | 0 | 0 | 0 |
| &(block->blockmutex) | buf0buf.c | 56 | 0 | 0 | 0 | 0 | 0 |
| &(block->blockmutex) | buf0buf.c | 16 | 0 | 0 | 0 | 0 | 0 |
| &(block->blockmutex) | buf0buf.c | 7 | 0 | 0 | 0 | 0 | 0 |
| &(block->blockmutex) | buf0buf.c | 2 | 0 | 0 | 0 | 0 | 0 |
|
Are we leaking something here?
Thanks, Andreas
Andreas,
That output is OK.
My patch just replaces one buffer_pool mutex on thousands of block->blockmutex, one mutex per block in innodb_buffer_pool.
Hi Vadim,
If you have any plans to include this patch into official source when could we expect it? What version could contain the patch?
If it will remain experimental some more time may be however it does make sense to include the patch into official source and add configure option for its activation for example, right? So, more people could try it.
What do you think?
Thanks,
Mikhail.
Mikhail,
I don’t work in MySQL anymore, so I can’t include the patch into the official source.
This question should be addressed to MySQL developers.
Best,
Vadim.
Mikhail,
I would even add it has to be approved by Innobase/Oracle as it is their code.
This has slight chance as, as I know they work on their own version of the patch which will be included in some later MySQL release…
Hi,
This is regarding MySQL5.0.20a performance difference between HP-UX and Linux. I ran Sysbench Read only test on both platform. For 4 thread, I got 280 transaction in Hp-UX and 710 transaction in RHEL. Also i ran Sysbench Read-Write OLTP test for 4 thread, i got 69 in HP-UX and 119 in RHEL.
Why this differences? Is this because of HP-UX specific codes in MySQL Source?
Thanks,
Prem
Prem,
There could be some source differences but these are mostly to work-around missing or different functionality, most of things work same across all Unix platforms. All of the platforms scale differently however so I’m not surprized – even different Linux versions can have 20%+ performance difference. Solaris can perform quite differently from Linux etc.
Peter,
Thanks for your immediate reply.
If the bottleneck is System (HP-UX), How to find out the system factors which affects the MySQL performance?
Please share your ideas….
Thanks in Advance,
Prem
Prem,
Please ask your question at out forum – forum.mysqlperformanceblog.com
Comments on unrelated topic is not great for discussion.
I think it is already disappointing that mysql is only tuned for Linux, like the PTHREAD_MUTEX_ADAPTIVE_NP which only exists on Linux, but why don’t you just use pthread_mutex_trylock and pthread_mutex_lock ? how do you expect that HP-UX or others will perfom well without stress testing and improving it ?
Alan,
We don’t expect that. This question is to MySQL developers, we are not.
afaik MySQL are not stress tested on HP-UX and other platforms, excluding Linux/Solaris.
I don’t know exactly Solaris or other OS. But, in case on x86 machine, The intel base cpus of nowadays are really bad and we should not believe the hyper threading. In my experience, It depends not on the cpu but on the machine. I’m not professional of mysql but I’ve many cases of poor performance under same config and cpu + ram.. It you want to check correct performance, I think you should turn off Hyper threading and C1~C7/e and all of the Intel low energy options of BIOS. If you use Windows2008R2, default BIOS setting G1610(2core) system’s table optimization speed exceeds the X5560(6core/4Ghz overclocked) one. I’m novice but mysql options are working correctly but they’re occasionally depends on the system main configuration. Thank you always for great articles.