Brent Ozar is to many a SQL god. I appreciate and value all of his internet posts. They have saved me more times that I can remember. So now that I am setting up a new vmware host which will hold our first virtualized SQL server, I turned to the internet to test out this host. First using VMware IO Analyzer, and then looking to SQLIO to test the performance of the host. And low and behold, Brent has a great article on using SQLIO to performance tune up your SQL server. So here is the link for that article, and a few others I found useful.
Things we did to get ready for SQL virtualization
- Added 4 200GB SSD’s to our VNX 5300 setup as fast cache. We want to avoid Littles Law. our SP’s were running healthy, but we saw our disks would be overtaxed if SQL was added. NLSAS and SAS drives currently.
- verified all iscsi ports support jumbo frames by using the VMKPing command passing 8792 ping size
- Use SQLIO and VMWare IO Analyzer to Verify latency is low during performance testing
- Made sure our SAN had the latest drivers, firmware, as well as the NIC drivers on the ESX
Things I learned
- ESX will report a “vmk# failed to login to ###### because of a network connection failure.” each time a host starts, HBA adapters are rescanned, and services are restarted on the ESX host. this is normal. Dont freak out
- A bunch of useful shell commands. thats a putty ssh terminal session for probies(the are below)
- Currently in ESX6 and ESX6.5 ui interface, you cannot browser a datastore that has large vmdks. you will get a error. Bug in the ui server that VMware provides right now. Works fine in the VMware client and vcenter web interface. Not sure on exact size the time out error occurs.
Useful Shell Commands
Note that dates/times are in UTC military. So get your head straight with that when working with Shell commands with ESX.
- #esxcfg-nics -l (See things like MTU size on interfaces, Mac address, duplie, link speed, drivers, vmnic#)
- #services.sh restart (restarts esx host services, do not do on production, its like restarting without restarting totally)
- #tail /var/log/hostd.log (tail command gets the latest stuff, the stuff at the tail end. so you dont have to scroll to the bottom to find out what recently happened.
- #tail -f /var/log/vobd.log shows you iscsi connection information like failed logins, connection issues, and even latency changes.
- #tail -f /var/log/vobd.log | grep -i failure (the pipe appends a command to the original command. the grep -i does a search for the word following. Basically filters out everything in the log but the word you finish with. nice when logs are big but you care about one specific thing
- #cat /var/log/vmksummary.log shows a high level log summer of things like reboots, performance, halts… you can use grep -i on any command keep in mind
Useful Reference links
- VMware IO Analyzer https://labs.vmware.com/flings/io-analyzer
- Mynew IO test Virtual Machine http://www.virtualtothecore.com/en/my-new-io-test-virtual-machine/
- useful shell commands http://wahlnetwork.com/2012/08/01/becoming-an-esxi-shell-ninja/
- VMware testing virtual machine IO storage https://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1006821