EmergencyEMERGENCY? Get 24/7 Help Now!

MyISAM mmap feature (5.1)

 | May 26, 2006 |  Posted In: Insight for DBAs

PREVIOUS POST
NEXT POST

As you know MyISAM does not cache data, only indexes. MyISAM assumes OS cache is good enough and uses pread/pwrite system calls for reading/writing datafiles. However OS is not always good in this task, my benchmarks show Linux/Solaris aren’t scalable on intensive pread calls (I believe the same for Windows, but I did not test it).
In 5.1 I implemented a new feature: memory mapping for the datafiles. That can be enabled by –myisam_use_mmap=1 startup option.
In this case instead of systems call MyISAM will use memcpy function. There is a memory addressing limit for 32bit platforms – 2Gb, so the datafiles over 2GB will be used the old way – pread/pwrite functions. Mmap is available on all POSIX-compatible platforms. It will work faster for SELECT/UPDATE/INSERT inside file queries, and no performance gain (maybe a bit slower) for INSERT at the end of file. In case with INSERT at the end of file we have to use a remap technic – resize memory mmaped area to new extended size. Currenlty we call remap once per 1000 inserts at the end of file and on an exclusive operation (DELETE/UPDATE/INSERT inside file), for work with non-mmaped area we use pread/pwrite functions.
To approve effectiveness of memory mapping several benchmarks:


For benchmarks I used the sysbench tool and tested different workloads:

  1. SELECT by PRIMARY KEY (select-primary for further link)
  2. SELECT by range of PRIMARY KEY (select-range)
  3. OLTP ro queries, group of different SELECT queries (OLTP ro)
  4. UPDATE primary key (update-key)
  5. UPDATE non key column (updaye-nokey)
  6. OLTP rw queries, SELECT/INSERT/DELETE/UPDATE queries (OLTP rw)
  7. batch select queries – scan of 20.000 rows by primary key
More info about table structure and delailed queries you can find on sysbench documentation page.
Tested box:

      Red Hat Enterprise Linux AS release 3 (Taroon Update 2)
      Kernel 2.4.21-15.ELsmp
      4 x Intel(R) XEON(TM) MP CPU 2.00GHz with HyperThreading
      4GB of RAM
The results (queries/sec, more is better):
select-primary
Threads Pread Mmap Mmap/pread ratio
1 8894.00 9393.73 1.06
4 21774.82 22650.34 1.04
16 26161.71 27233.20 1.04
64 24330.44 24847.33 1.02
256 23868.33 24271.74 1.02

image0011.gif

select-range
Threads Pread Mmap Mmap/pread ratio
1 1555.05 2516.28 1.62
4 3055.64 4749.04 1.55
16 3193.56 4365.28 1.37
64 3190.75 4301.65 1.35
256 3183.87 4225.03 1.33

image004.gif

OLTP ro
Threads Pread Mmap Mmap/pread ratio
1 249.58 272.38 1.09
4 608.79 737.73 1.21
16 694.52 799.64 1.15
64 684.06 781.29 1.14
256 668.79 767.62 1.15

image005.gif

update key
Threads Pread Mmap Mmap/pread ratio
1 5266.30 6586.48 1.25
4 8443.85 11446.37 1.36
16 8080.29 10974.55 1.36
64 7654.28 10317.60 1.35
256 6474.26 8577.43 1.32

image003.gif

update nokey
Threads Pread Mmap Mmap/pread ratio
1 7665.51 9114.75 1.19
4 10963.06 14964.02 1.36
16 10472.81 14028.80 1.34
64 10013.07 13161.23 1.31
256 8734.11 10991.45 1.26

image006.gif

OLTP rw
Threads Pread Mmap Mmap/pread ratio
1 183.48 234.36 1.28
4 180.00 243.10 1.35
16 177.23 238.38 1.35
64 176.17 237.31 1.35
256 175.86 234.18 1.33

image002.gif

And finally the interesting results for select batch
Threads Pread Mmap Mmap/pread ratio
1 13.79 33.53 2.43
4 29.36 99.15 3.38
16 20.48 136.89 6.68
64 20.34 137.98 6.78
256 20.69 137.97 6.67

image007.gif

So in conclusion memory mapping gives benefit in different cases. The value of performance gain depends of count replaced pread/pwrite calls. For select-primary we replace only one pread call and gain is ~6%, for select-batch we replace 20.000 calls and the summary benefit is ~670%

PREVIOUS POST
NEXT POST
Vadim Tkachenko

Vadim Tkachenko co-founded Percona in 2006 and serves as its Chief Technology Officer. Vadim leads Percona Labs, which focuses on technology research and performance evaluations of Percona’s and third-party products. Percona Labs designs no-gimmick tests of hardware, filesystems, storage engines, and databases that surpass the standard performance and functionality scenario benchmarks. Vadim’s expertise in LAMP performance and multi-threaded programming help optimize MySQL and InnoDB internals to take full advantage of modern hardware. Oracle Corporation and its predecessors have incorporated Vadim’s source code patches into the mainstream MySQL and InnoDB products. He also co-authored the book High Performance MySQL: Optimization, Backups, and Replication 3rd Edition.

10 Comments

  • Not so hard, I even have a patch for 5.0 tree, but MySQL policy does not allow to include new features into GA trees.

  • One thing that bothers me is that I’m not able to determine my OS cache efficiency.

    I use munin and RRD to graph as many stats about MySQL as possible. Number of slow queries, query cache efficiency, etc.

    The problem is I can’t figure out where Linux keeps stats on its file cache hit rate.

    Anyone have any idea? email me please and I’ll write a munin plugin for it 🙂

    Kevin

  • Kevin,

    Heh, I also don’t know how to get cache hit rate.
    Some statistics is available in /proc/slabinfo (http://seth.positivism.org/man.cgi/5/slabinfo) but there is no hit statistics

  • Valeriy,

    I don’t think mmap require changes in ulimit -n. At least I have not heard about.

    Regarding that bug it could be out of memory error on 32bit.
    Maybe there is still restriction in 2GB summary for allocated memory and mmaped size.
    I’ll try to investigate this.

  • Great, thank you! I’ve got about 10-15% performance on SELECTs with multiple simple joins
    using unindexes LIKE and REGEXP searches throught a table with 1m records.

  • As this article is several years old, would it be possible to post the 5.0 series patch if it will still apply to the current 5.0.54 (or other) release?

    Thanks,
    Michael

  • I know this post is ancient, but I thought I’d ask anyway. Do you have a guideline for determining the value of myisam_mmap_size? The default is 18446744073709551615 (16 exabytes) which is clearly ridiculous. I currently have it set randomly to half the size of installed system RAM on a new cluster which is not yet in production. On an existing cluster, mmap was enabled before we noticed the default size, and we’re 99% certain this value caused MySQL to crash several times. No more crashes since disabling mmap. Thank you very much for any insight.

Leave a Reply

 
 

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.