by Andrew Johnstone
Currently I have been trying to migrate a 4 node production MySQL database to a blade G5/G7 with vmware ubuntu instances on top of netapp. Typically I would not use a virtual machine nor try to run MySQL on top of NFS, however this needs to fit within the clients infrastructure.
Base line performance unoptimized vms-gu-mysql04
root@vms-gu-mysql04:~# time dd if=/dev/zero of=/mnt/storage/io.test bs=16k count 16384+0 records in 16384+0 records out 268435456 bytes (268 MB) copied, 79.1257 s, 3.4 MB/s real 1m19.202s user 0m0.010s sys 1m8.810s
Base line performance on current production that it is being migrated from on a raid 10 sas disks.
db1gorkana:~# time dd if=/dev/zero of=/mnt/mysql.tmp/io.test bs=16 16384+0 records in 16384+0 records out 268435456 bytes (268 MB) copied, 1.22437 s, 219 MB/s real 0m1.227s user 0m0.008s sys 0m1.200s
Whilst dd tests only single-threaded disk access with a sequential write followed by a large sequential read, it is a very primitive test and fairly inline with MySQL IO usage.
Optimized NFS mount options and sysctl.
Mount mysql.tmp as a ramdisk and allocate sufficient swap.
root@vms-gu-mysql04:~# cat /etc/fstab # /etc/fstab: static file system information. # # Use 'blkid -o value -s UUID' to print the universally unique identifier # for a device; this may be used with UUID= as a more robust way to name # devices that works even if disks are added and removed. See fstab(5). # # <file system> <mount point> <type> <options> <dump> <pass> proc /proc proc nodev,noexec,nosuid 0 0 /dev/mapper/vms--gu--mysql04-root / ext4 errors=remount-ro 0 1 # /boot was on /dev/sda1 during installation UUID=9251f0b6-b3ea-4370-b1ae-368de078242f /boot ext2 defaults 0 2 /dev/mapper/vms--gu--mysql04-swap_1 none swap sw 0 0 /dev/fd0 /media/floppy0 auto rw,user,noauto,exec,utf8 0 0 #tc2-nas01:/vol/vms_gu_mysql04_dbStorage /mnt/storage nfs rw,hard,nointr,rsize=65536,wsize=65536,bg,vers=3,proto=tcp tc2-nas01:/vol/vms_gu_mysql04_dbStorage /mnt/storage nfs rw,hard,nointr,rsize=65536,wsize=65536,bg,vers=3,proto=tcp,noatime tc2-nas01:/vol/vms_gu_mysql04_dbStorage/mysql.tmp /mnt/mysql.tmp nfs rw,hard,nointr,rsize=65536,wsize=65536,bg,vers=3,proto=tcp,noatime tc2-nas01:/vol/vms_gu_mysql04_dbStorage/mysql.log /mnt/mysql.log nfs rw,hard,nointr,rsize=65536,wsize=65536,bg,vers=3,proto=tcp,noatime tc2-nas01:/vol/vms_gu_mysql04_dbStorage/mysql /mnt/mysql nfs rw,hard,nointr,rsize=65536,wsize=65536,bg,vers=3,proto=tcp,noatime # UAT-NAS02.durrants.uat:/vol/vms_gu_mysql01_dbStorage /mnt/storage_uat nfs rw,hard,nointr,rsize=65536,wsize=65536,bg,vers=3,proto=tcp #/.swap swap swap defaults 0 0
root@vms-gu-mysql04:~# cat /etc/sysctl.conf # # /etc/sysctl.conf - Configuration file for setting system variables # See /etc/sysctl.d/ for additional system variables. # See sysctl.conf (5) for information. # #kernel.domainname = example.com ##############################################################3 # Functions previously found in netbase # # Uncomment the next two lines to enable Spoof protection (reverse-path filter) # Turn on Source Address Verification in all interfaces to # prevent some spoofing attacks net.ipv4.conf.default.rp_filter=1 #net.ipv4.conf.all.rp_filter=1 # Uncomment the next line to enable TCP/IP SYN cookies net.ipv4.tcp_syncookies=0 ################################################################### # Additional settings - these settings can improve the network # security of the host and prevent against some network attacks # including spoofing attacks and man in the middle attacks through # redirection. Some network environments, however, require that these # settings are disabled so review and enable them as needed. # # Ignore ICMP broadcasts #net.ipv4.icmp_echo_ignore_broadcasts = 1 # # Ignore bogus ICMP errors #net.ipv4.icmp_ignore_bogus_error_responses = 1 # # Do not accept ICMP redirects (prevent MITM attacks) #net.ipv4.conf.all.accept_redirects = 0 #net.ipv6.conf.all.accept_redirects = 0 # _or_ # Accept ICMP redirects only for gateways listed in our default # gateway list (enabled by default) # net.ipv4.conf.all.secure_redirects = 1 # # Do not send ICMP redirects (we are not a router) #net.ipv4.conf.all.send_redirects = 0 # # Do not accept IP source route packets (we are not a router) net.ipv4.conf.all.accept_source_route = 0 #net.ipv6.conf.all.accept_source_route = 0 # # Log Martian Packets #net.ipv4.conf.all.log_martians = 1 ################################################################### # Enable packet forwarding for IPv4 & IPv6 net.ipv4.ip_forward=1 net.ipv6.conf.all.forwarding=1 ################################################################### # Stops low-level messages on console kernel.printk = 4 4 1 7 ################################################################### # Semaphores & IPC for optimizations in innodb kernel.shmmax=2147483648 kernel.shmall=2147483648 kernel.msgmni=1024 kernel.msgmax=65536 kernel.sem=250 32000 32 1024 ################################################################### # Swap vm.swappiness = 0 vm.vfs_cache_pressure = 50 ################################################################### # Increase max open files limit, this causes problems when opening # a number of files typically set to 1024 fs.file-max=65536 ################################################################### # Optimization for netapp/nfs increased from 64k, @see http://tldp.org/HOWTO/NFS-HOWTO/performance.html#MEMLIMITS net.core.wmem_default=262144 net.core.rmem_default=262144 net.core.wmem_max=262144 net.core.rmem_max=262144 net.ipv4.tcp_rmem = 4096 87380 16777216 net.ipv4.tcp_wmem = 4096 65536 16777216 net.ipv4.tcp_no_metrics_save = 1 # Guidelines from http://media.netapp.com/documents/mysqlperformance-5.pdf net.ipv4.tcp_sack=0 net.ipv4.tcp_timestamps=0 sunrpc.tcp_slot_table_entries=128 #nvfail on #fcp.enable on #iscsi.enable on #nfs.v3.enable on nfs.tcp.enable on nfs.tcp.recvwindowsize 65536 nfs.tcp.xfersize 65536 #iscsi.iswt.max_ios_per_session 128 #iscsi.iswt.tcp_window_size 131400 #iscsi.max_connections_per_session 16 net.ipv4.tcp_tw_reuse = 1 net.ipv4.ip_local_port_range = 1024 65023 net.ipv4.tcp_max_syn_backlog = 10240 net.ipv4.tcp_max_tw_buckets = 400000 net.ipv4.tcp_max_orphans = 60000 net.ipv4.tcp_synack_retries = 3 net.core.somaxconn = 10000 kernel.sysrq=0 net.ipv4.neigh.default.gc_thresh1 = 4096 net.ipv4.neigh.default.gc_thresh2 = 8192 net.ipv4.neigh.default.gc_thresh3 = 8192 net.ipv4.neigh.default.base_reachable_time = 86400 net.ipv4.neigh.default.gc_stale_time = 86400
Testing optimization of mounts and sysctl
root@vms-gu-mysql04:/mnt# time dd if=/dev/zero of=/mnt/storage/io.test bs=16k count=16384 16384+0 records in 16384+0 records out 268435456 bytes (268 MB) copied, 7.41033 s, 36.2 MB/s real 0m7.457s user 0m0.000s sys 0m0.320s
# # The MySQL database server configuration file. # # You can copy this to one of: # - "/etc/mysql/my.cnf" to set global options, # - "~/.my.cnf" to set user-specific options. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html # This will be passed to all mysql clients # It has been reported that passwords should be enclosed with ticks/quotes # escpecially if they contain "#" chars... # Remember to edit /etc/mysql/debian.cnf when changing the socket location. [client] port = 3306 socket = /var/run/mysqld/mysqld.sock # Here is entries for some specific programs # The following values assume you have at least 32M ram # This was formally known as [safe_mysqld]. Both versions are currently parsed. [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 open_files_limit=16384 [mysqld] # # * Basic Settings # datadir = /mnt/mysql tmpdir = /mnt/mysql.tmp/ performance_schema skip_name_resolve default-time-zone='UTC' ft_min_word_len=2 group_concat_max_len=8192 myisam-recover = BACKUP log_slow_queries = /mnt/mysql.log/slow.log max_allowed_packet=128M key_buffer = 512M table_cache = 2000 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 64M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 16 max_connections=1000 wait_timeout = 120 connect_timeout = 10 tmp_table_size = 64M max_connect_errors = 1000 ## INNODB innodb_file_per_table innodb_table_locks=0 innodb_doublewrite=0 innodb_open_files=4096 innodb_support_xa=off innodb_log_files_in_group=2 innodb_adaptive_checkpoint=keep_average innodb_thread_concurrency=0 innodb_flush_method = fsync Innodb_checksums=0 innodb_read_ahead = none innodb_flush_neighbor_pages = 0 #innodb_write_io_threads=16 #innodb_read_io_threads=16 innodb_io_capacity=2000 innodb_buffer_pool_size = 7G innodb_additional_mem_pool_size = 256M innodb_lock_wait_timeout = 5 innodb_flush_log_at_trx_commit = 1 innodb_data_file_path=ibdata1:100M:autoextend #innodb_log_file_size = 900M ## REPLICATION log-bin=mysql-bin server-id=7 sync_binlog=1 relay-log = relay.log relay-log-info-file = relay-log.info relay-log-index = relay-log.index log-error=repl.log binlog_cache_size = 10M expire-logs-days=5 [mysqldump] quick quote-names max_allowed_packet = 64M [mysql] default-character-set=utf8 [isamchk] key_buffer = 16M # # * IMPORTANT: Additional settings that can override those from this file! # The files must end with '.cnf', otherwise they'll be ignored. # !includedir /etc/mysql/conf.d/
In order to test traffic from our production environment we setup stunnel for both replicated and piped mysql traffic. I setup MySQL on a single node as a slave whilst the other acted as a test box for optimization. The replicated data would only test the write capacity of the new environment, however this would not simulate queries running through our production. Additionally routing traffic from production via iptables would not allow me to filter only selects.
Whilst maatkit provides mk-log-player this didn’t cover my needs to test the new infrastructure. As such I rewrote parts of mysqlsniffer and stripped it down to execute the statements on the other box and filtering select and creation of temporary tables and inserts into them.
After testing for a period of time, we decided against switching to use netapp and simply add disk packs to the blade datacenters. There were further issues with their configuration on there network/netapp which the client is looking into. It was expected that we could issue reads/writes at 120mb a second, however only managed to get up to 40mb a second. Contrasting 40mb/s to direct disk access on other machines that were optimized we could achieve 500mb/s. The optimizations improved reads/writes by up to 900%.
I have been a developer for roughly 10 years and have worked with an extensive range of technologies. Whilst working for relatively small companies, I have worked with all aspects of the development life cycle, which has given me a broad and in-depth experience.
1 Response to Optimizing and testing MySQL over NFS with NetAPP
darkfader
June 30th, 2011 at 7:10 pm
Very nice you put this up for reading!
VMWare networking is slow, unless tuned by absolute experts, and the Linux NFS code sucks beyond description, one of the reasons why Oracle implemented pNFS support right into the database kernel instead of relying on the OS. They made it go to wirespeed rate over multiple links to a single NetAPP filer.
But its smart to know when to not ride a dead horse (as in – aligning with Customer environment if it just ain’t fit!