Benchmarking MySQL in Memory Using tmpfs

June 14, 2009 by Amr

At my (new) day job I joined the team responsible for the scalability and reliability of a heavily loaded LAMP stack. Since our MySQL server has plenty of memory, we were recently wondering if we could load all MySQL into memory and what would be the benefit.

A bit of googling, and I found out that there were few others asking the same question, however, I didn't see any satisfactory answers that are coming from real experience or benchmarks, and so we decided to benchmark for ourselves and see.

Some readers who are not aware of the OS/kernel disk cache (also called page cache) might wonder, why benchmark, memory is definitely going to be faster. However, the disk cache could be already caching your MySQL into memory. In fact, if you have enough free memory, chances are your MySQL data is in memory, thanks to the kernel page cache. This benchmark tries to measure how much benefit there is by explicitly putting MySQL data in memory using a ram file system, like tmpfs.

With that out of the way, let's get to the benchmarks for which I'd like to thank my employer for allowing me to publish.

The setup

1. 2 Databases, copies of each other but on different file systems:
   • normal: On a normal file system (ext3 in this case).
   • tmpfs: On a ram file system (tmpfs here).
2. The test data are 2 tables. each table is 400 mb, filled with random, variable length data. Table structure is simple:
   • id (primary key)
   • data (blob).
3. Each table has approx. 83574 rows.
4. Query cache disabled.
5. First sample of the benchmarking results is discarded (i.e. for MySQL to warm up).
6. MySQL is restarted after each benchmark.

Benchmark (1): SELECT * .. WHERE data = 'foobar'

• The test:
  • PHP Script. Runs from terminal.
  • Selects a random table.
  • Executes: SELECT * FROM table WHERE data = 'foobar' for 100 times.
  • MySQL will need to scan the whole table looking for 'foobar'.
• Results:
  • normal: 5 queries per second, in a total time of 18.224 seconds.
  • tmpfs: 5 queries per second, in a total time of 19.375 seconds.
• Comments and observations:
  • The first run for the normal database recorded only 2 queries per second in a total time of 40.587 seconds. This benchmark was discarded (i.e. not included in the results). This makes sense, because the first query (of the 100 executed) was probably too expensive because it wasn't cached by the OS yet.
  • The overall difference is neglectable.
  • Summary: In both cases, the complete table was being served out of memory with unnoticeable performance difference.

Benchmark (2): SELECT * .. WHERE data = 'foobar', with OS cache disabled

• The test:
  • Same as Benchmark (1) test, except:
  • Operating system cache is disabled.
• Results:
  • normal: less than 1 queries per second, in a total time of 7 minutes 8.194 seconds
  • tmpfs: 4 queries per second, in a total time of 26.287 seconds.
• Comments and observations:
  • Without operating system cache, normal database is much much more slower, uncomparable.
  • tmpfs is almost unaffected, because it's running off ram anyway. It's slightly affected, but that's understable, because the operating system cache is being flushed with every execution and that operation has its own overhead (which is measured to be about 3.856 seconds of the total time, so the actual time execution on mysql took is less than 23 seconds).

Benchmark (3): SELECT * .. ORDER BY data

• The test:
  • PHP Script. Runs from terminal.
  • Executes: SELECT * FROM table ORDER BY data for 10 times.
  • MySQL will need to perform a sorting algorithm on the data.
  • Swap was being reset between every benchmark.
• Results:
  • normal: 0.0598 queries per second, in a total time of 2 minutes 48.056 seconds.
  • tmpfs: 0.0195 queries per second, in a total time of 8 minutes 33.659 seconds.
• Comments and observations:
  • For the normal database benchmark:
    • System swapped heavily, almost the 2 tables were completely swapped (about 808 mb in total).
    • Clearly I/O bound.
    • Clearly need more memory.
    • OS cache never topped 500 mb (i.e. the 2 tables could never be fully cached by the OS).
  • For the tmpfs database benchmark:
    • System swapped heavily as well.
    • A pattern observed: Tries to cache to the OS, but occasionally hits the memory limit and throws everything back to swap.
    • That pattern seem to hurt performance a lot.
    • Clearly I/O bound.
    • Clearly need more memory.
    • Much worse performance, it appears that being on ram didn't help with the sorting algorithm. On the contrary, it limited the system available resources (since the tmpfs database itself consumed 800 mb).

Benchmark (4): SELECT * .. ORDER BY data (with only 1 table)

• The test:
  • Exactly the same as benchmark (3), except:
  • Only 1 table instead of 2, needed to prevent system from swapping in that benchmark.
• Results:
  • normal: 0.675 queries per second, in a total time of 15.417 seconds.
  • tmpfs: 0.653 queries per second, in a total time of 15.523 seconds.
• Comments and observations:
  • For the normal database benchmark:
    • First run took 50 seconds, which makes sense. It used the OS cache, but this time, the data it needed could fit the system OS cache.
    • No I/O activity (except for the first run)
    • OS cache fully utilized.
  • For the tmpfs database benchmark:
    • First run much quicker than first run in normal database, which makes sense. No need for OS cache.
    • No I/O Activity.
    • OS Cache was utilized.

Conclusions

1. The OS Cache is critical and needed, even when the complete database is in memory, because processing data, like sorting or grouping, uses external temporary files which goes to disk (this can be greatly enhanced by mounting /tmp with tmpfs, but that's for another round). The lesson here, make sure there is enough memory for the OS cache to kick in as much as possible.
2. The first run of a tmpfs database is always faster than normal database, but in the continous execution, they are both the same (or actually, the normal database with OS cache seem to have a very small increase in performance).
3. If you have MySQL as the only service main service using the system resources, then putting all MySQL in memory is not needed. The OS Cache will use the memory more effeciently, leaving more of it free for other operations (like sorting, grouping, ..etc).
4. If you have MySQL hosted with other services which frequently access disk (but you have no problem of that, they are not as critical as MySQL to you) then tmpfs might make some sense if you want to give MySQL an edge over the other services. OS Cache while great, doesn't have priorities. So any disk activity passing by the kernel will be cached in memory, even if it's a simple cp or rsync in the background. You probably don't want this, and in that case, specifically allocating some of the memory to MySQL seem to be a better option. You have to be careful not to allocate too much and end up swapping though.
5. Finally, and in relation to the previous point, please note that those benchmarks were run for a system with ram larger than its database, and so they were modeled so the data could almost always fit in memory. If your system has barely enough memory to fit MySQL or have other servers like HTTPD on the same machine, serving files larger 10mb or larger, then using something like tmpfs to force MySQL data into memory could bring real change since the kernel will be caching the files served by the HTTPD and thus kicking MySQL data out of the cache (unless the HTTPD is smart and uses, or allows the use of, posix_fadvise() or O_DIRECT which I've yet to see available in an HTTPD or even day to day tools, but that's also for another round).

Permalink