[Mifos-developer] faster Mifos integration tests: MySQL datadir on memory-backed storage

[Adam Monsen]

I found an interesting idea by a nice chap named Espen (
http://blog.grumblesmurf.org/ ): store mysql data in memory. Check it
out: http://tinyurl.com/38oj5xd (full url:
http://blog.grumblesmurf.org/2010/02/speeding-up-database-bound-tests-with.html
). We used to use a simple in-memory database for some integration
tests, so I thought, can't MySQL do that? It has a "memory" storage
engine or something, but I didn't want to change any Mifos code.

I also don't have SSD storage, but I thought maybe just the I/O-heavy
stuff (MySQL datadir) could be left in memory.

I was able to set up the MySQL datadir as follows on Ubuntu 10.04,
reducing the time for integration tests ("mvn integration-test" run
from the "application" directory) on my laptop from 23 minutes to 8
minutes. Acceptance tests should be faster too, but maybe not as much
because of lots of networking action, clicking around in firefox, and
maybe logging, which probably requires more tweaking to speed up. More
CPU and, again, SSD storage would help with all that.

Anyway, here's how I set up a separate MySQL instance with
memory-backed tablespaces and data.

* sudo mkdir /opt/ram
* sudo mount -t tmpfs -o size=500M tmpfs /opt/ram
* sudo mkdir -p /opt/ram/joeuser
* sudo chown joeuser /opt/ram/joeuser
* unpack mysql binary tarball into /home/joeuser/dist
* cd /home/joeuser/dist
* ln -s mysql-5.1.47-linux-x86_64-icc-glibc23 mysql
* cd mysql
* scripts/mysql_install_db --basedir=. --datadir=data
* mkdir /opt/ram/joeuser/mysqldata
* cp -a data/* /opt/ram/joeuser/mysqldata
* bin/mysqld_safe --defaults-file=my.cnf --skip-grant-tables

I just created one database for all Mifos tests, and configured
~/.mifos/local.properties to use it.

Notice I used a "Generic Linux" binary from mysql.com
(mysql-5.1.47-linux-x86_64-icc-glibc23.tar.gz). Using the MySQL server
that comes with Ubuntu requires tweaking apparmor conf
(/etc/apparmor.d/usr.sbin.mysqld), adding the paths to your
tmpfs-mounted storage. e.g.:

...
/opt/ram/mysql/ r,
/opt/ram/mysql/** rwk,
...

and restarting the apparmor service.

Here's the my.cnf I'm using. This could probably be further optimized
given "datadir" is in memory, but it works:

[client]
port = 3307
socket = /home/joeuser/dist/mysql/mysqld.sock

[mysqld]
user = joeuser
port = 3307
socket = /home/joeuser/dist/mysql/mysqld.sock
basedir = /home/joeuser/dist/mysql
#datadir = /home/joeuser/dist/mysql/data
datadir = /opt/ram/joeuser/mysqldata
tmpdir = /tmp
skip-external-locking
log_error = /home/joeuser/dist/mysql/error.log

# required by Mifos
lower_case_table_names=1
innodb_file_per_table

back_log = 50
max_connections = 100
max_connect_errors = 10
table_open_cache = 2048
max_allowed_packet = 16M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default-storage-engine = INNODB
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
key_buffer_size = 32M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 64M
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 64M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 32M
sort_buffer_size = 32M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 8192
nice = 0
socket = /home/joeuser/dist/mysql/mysqld.sock

# vim:ft=dosini

------------------------------------------------------------------------------

[Adam Monsen]

I ran a full build (on my laptop, using a MySQL datadir in RAM) and
was pleased with the result:

...
[INFO] Mifos - Application .......... SUCCESS [9:07.652s]
[INFO] Mifos - Acceptance Tests ..... SUCCESS [5:37.397s]
[INFO] -----------------------------------------------
[INFO] BUILD SUCCESSFUL
[INFO] -----------------------------------------------
[INFO] Total time: 15 minutes 55 seconds
[INFO] -----------------------------------------------
...

Not bad, eh? Ok, I hear you, over 15 minutes! But this is down from
the usual *hour* or so for running "mvn clean install" Mifos build on
my hardware.

The default "mvn clean install" (ie: without the "continuous-
integration" profile) does not run all the acceptance tests, just ones
in the "smoke" group.

------------------------------------------------------------------------------

[Udai Gupta]

> I ran a full build (on my laptop, using a MySQL datadir in RAM) and
> was pleased with the result:

Awesome, I am going to try this with ubuntu's default mysql, but just
changing the datadir location to tempfs.

How do you persist the datadir on tempfs, mine just goes away with a
restart. Are you using an script to restore the mysql datadir.

Cheers,
Udai

------------------------------------------------------------------------------

[Adam Monsen]

(here's what Udai and I discussed on IRC)

> Awesome, I am going to try this with ubuntu's default mysql, but just
> changing the datadir location to tempfs.

Yep, that works. Only issue there is the need to futz with apparmor,
as mentioned in my last email.

> How do you persist the datadir on tempfs, mine just goes away with a
> restart.

I don't restart. Well, not often, anyway. I generally just suspend
instead of powering down.

Yes, a script would be nice. I keep a nonvolatile mysql install in
$HOME/dist/mysql, and plan to script copying $HOME/dist/mysql/data to
my tmpfs partition.

------------------------------------------------------------------------------