Impact of memory allocators on MySQL performance

た入场券 2022-08-27 13:59 64阅读 0赞

本文转载自：http://www.mysqlperformanceblog.com/2012/07/05/impact-of-memory-allocators-on-mysql-performance/

# Impact of memory allocators on MySQL performance #

July 5, 2012 by [Alexey Stroganov][] [11 Comments][]

[Tweet][]

MySQL server intensively uses dynamic memory allocation so a good choice of memory allocator is quite important for the proper utilization of CPU/RAM resources. Efficient memory allocator should help to improve scalability, increase throughput and keep memory footprint under the control. In this post I’m going to check impact of several memory allocators on the performance/scalability of MySQL server in the read-only workloads.

For my testing i chose following allocators: lockless, jemalloc-2.2.5, jemalloc-3.0, tcmalloc(gperftools-2.0), glibc-2.12.1(new malloc)(CentOS 6.2), glibc-2.13(old malloc), glibc-2.13(new malloc), glibc-2.15(new malloc).

Let me clarify a bit about malloc in glibc. Starting from glibc-2.10 it had two malloc implementations that one can choose with configure option*–enable-experimental-malloc*. (You can find details about new malloc [ here).][here_.] Many distros switched to this new malloc in 2009. From my experince this new malloc behaved not always efficiently with MySQL so i decided to include old one to comparison as well. I used glibc-2.13 for that purpose because later*–enable-experimental-malloc* option was removed from glibc sources.

I built all allocators from sources(except system glibc 2.12.1) with stock CentOS gcc(version 4.4.6 20110731). All of them were built with -O3. I used LD\_PRELOAD for lockless, jemalloc-2.2.5, jemalloc-3.0, tcmalloc and for glibc I prefixed mysqld with:

Shell

/\[path\]/glibc-\[X\].root/lib/ld-\[X\].so --library-path /\[path\]/glibc-\[X\].root/lib:/lib64:/usr/lib64

<table> 
 <tbody> 
 <tr> 
 <td> 
 <div style="line-height:15px!important"> 
 <div>
 1 
 </div> 
 </div> </td> 
 <td> 
 <div style="line-height:15px!important"> 
 <div> 
 
 / 
 [ 
 path 
 ] 
 / 
 glibc 
 - 
 [ 
 X 
 ] 
 .root 
 / 
 lib 
 / 
 ld 
 - 
 [ 
 X 
 ] 
 .so 
 
 -- 
 library 
 - 
 path 
 
 / 
 [ 
 path 
 ] 
 / 
 glibc 
 - 
 [ 
 X 
 ] 
 .root 
 / 
 lib 
 : 
 / 
 lib64 
 : 
 / 
 usr 
 / 
 lib64 
 </div> 
 </div> </td> 
 </tr> 
 </tbody> 
</table>

*  Testing details: 
    
     *  [Cisco USC\_C250 box][Cisco USC_C250 box]
     *  Percona Server 5.5.24
     *  2 read only scnearios: OLTP\_RO and POINT\_SELECT from the latest sysbench-0.5
     *  dataset consists of 4 sysbench tables(50M rows each) ~50G data / CPU bound case
     *  innodb\_buffer\_pool\_size=52G
 *  For every malloc allocator perform the following steps:
    
     *  start Percona server either with LD\_PRELOAD=\[allocator\_lib.so\] or glibc prefix(see above)/get RSS/VSZ size of mysqld
     *  warmup with ‘select avg(id) from sbtest$i FORCE KEY (PRIMARY)’ and then OLTP\_RO for 600sec
     *  run OLTP\_RO/POINT\_SELECT test cases, duration 300 sec and vary number of threads: 8/64/128/256/512/1024/1536
     *  stop server/get RSS/VSZ size of mysqld

[![tps\_OLTP\_RO.data][tps_OLTP_RO.data]][tps_OLTP_RO.data]

The best throughput/scalability we have with lockless/jemalloc-3.0/tcmalloc. jemalloc-2.2.5 slightly drops with higher number of threads. On the graph with response time(see below) there are spikes for it that may be caused by some contention in the lib. All variations of glibc that are based on new malloc with increasing concurrency demonstrate notable drops – almost two times at high threads. In the same time glibc-2.13 built with old malloc looks good, results are very similar to lockless/jemalloc-3.0/tcmalloc.

[![pct99\_OLTP\_RO.data][pct99_OLTP_RO.data]][pct99_OLTP_RO.data]

[![tps\_POINT\_SELECT][tps_POINT_SELECT]][tps_POINT_SELECT]

For POINT\_SELECT test with increasing concurrency we have two allocators that handle load very well – tcmalloc and only slightly behind … glibc-2.13 with old malloc. Then we have jemalloc-3.0/lockless/jemalloc-2.2.5 and last ones are glibc allocators based on new malloc. Along with the best throughput/scalability runs with tcmalloc also demonstrate best response time (30-50 ms at the high threads).

[![pct99\_POINT\_SELECT][pct99_POINT_SELECT]][pct99_POINT_SELECT]

Besides throughput and latency there is one more factor that should be taken into account – memory footprint.

<table> 
 <tbody> 
 <tr> 
 <th>memory allocator</th> 
 <th>mysqld RSS size grow(kbytes)</th> 
 <th>mysqld VSZ size grow(kbytes)</th> 
 </tr> 
 <tr> 
 <td>lockless</td> 
 <td>6.966.736</td> 
 <td>105.780.880</td> 
 </tr> 
 <tr> 
 <td>jemalloc-2.2.5</td> 
 <td>214.408</td> 
 <td>3.706.880</td> 
 </tr> 
 <tr> 
 <td>jemalloc-3.0</td> 
 <td>216.084</td> 
 <td>5.804.032</td> 
 </tr> 
 <tr> 
 <td>tcmalloc</td> 
 <td>456.028</td> 
 <td>514.544</td> 
 </tr> 
 <tr> 
 <td>glibc-2.13-new-malloc</td> 
 <td>210.120</td> 
 <td>232.624</td> 
 </tr> 
 <tr> 
 <td>glibc-2.13-old-malloc</td> 
 <td>253.568</td> 
 <td>1.006.204</td> 
 </tr> 
 <tr> 
 <td>glibc-2.12.1-system</td> 
 <td>162.952</td> 
 <td>215.064</td> 
 </tr> 
 <tr> 
 <td>glibc-2.15-new-malloc</td> 
 <td>5.106.124</td> 
 <td>261.636</td> 
 </tr> 
 </tbody> 
</table>

The only two allocators lockless and glibc-2.15-with new malloc notably incressed RSS memory footprint of mysqld server – more than on 5G. Memory usage for others allocators looks more or less acceptable.

Taking into account all 3 factors – throughput, latency and memory usage for above POINT\_SELECT/OLTP\_RO type of workloads the most suitable allocators are tcmalloc, jemalloc-3.0 and glibc-2.13 with old malloc.

Important point to take is that new glibc with new malloc implementation may be NOT suitable and may show worse results than on older platforms.

**UPDATE:**

To cover some questions raised in the comments I rerun OLTP\_RO/POINT\_SELECT tests with jemalloc-2.2.5/jemalloc-3.0/tcmalloc, varied /sys/kernel/mm/transparent\_hugepage/enabled(always|never) and gathered mysqld size with ‘ps –sort=-rss -eopid,rss,vsz,pcpu’ during the test run. Just to remind whole test run cycle looks like following:  
start server, warmup, OLTP\_RO test, POINT\_SELECT test. So on charts below you will see how mysqld footprint is changed during the test cycle and what is the impact of disabling of hugepages.

[![jem.225.672][]][jem.225.672]

[![jem.3.655][]][jem.3.655]

[![tcm.658][]][tcm.658]

*You can read Part 2 of this topic [ here][here].*

注：近期参加MySQL运维学习，老师推荐该文章作为学习和技术提高的扩展阅读，先记录到自己的博客中，随后慢慢消化、学习、提高。本文与MySQL数据库 “性能优化”主题有关。

[Alexey Stroganov]: http://www.mysqlperformanceblog.com/author/alexey-stroganov/
[11 Comments]: http://www.mysqlperformanceblog.com/2012/07/05/impact-of-memory-allocators-on-mysql-performance/#comments
[Tweet]: http://twitter.com/share
[here_.]: http://udrepper.livejournal.com/20948.html
[Cisco USC_C250 box]: http://www.percona.com/docs/wiki/benchmark:hardware:cisco_ucs_c250
[tps_OLTP_RO.data]: /images/20220823/3d41118f27ed46aeb2257689e42dacb1.png
[pct99_OLTP_RO.data]: /images/20220823/490429756d9a439a8727db4c37869284.png
[tps_POINT_SELECT]: /images/20220823/5e2482a8f3d1489194bcc6a4754e1260.png
[pct99_POINT_SELECT]: /images/20220823/c0c1b3df16de4dfd80b451d3e86258ec.png
[jem.225.672]: /images/20220823/bee6b2f4e12940209e25c87adca21881.png
[jem.3.655]: /images/20220823/feba70ff168b4edb85bc03e5a0dfd0ef.png
[tcm.658]: /images/20220823/3fe3766c2fa2436fb8a461473af80ca7.png
[here]: http://www.mysqlperformanceblog.com/2013/03/08/mysql-performance-impact-of-memory-allocators-part-2/