How THP and Kernel Version effects MySQL Stability

During last year , i have faced multiple stall issue. MySQL Service was getting stalled any time, without leaving any clue in any logs. MySQL Service was getting restarted after 15 Minute of stall. We (Me and My Team ) were clueless.

Initially ,we were under impression, we may hit any bug. We gone through all MySQL bug tried multiple options.

1. Moving Redo Logs to magnetic Disk

2. optimizing flushing method/Thread

3. Optimizing all Database (Recreate using mysqldump and restore)

Still no Luck , we were still clueless.

We move forward and tried capturing all information using pt-stalk.

pt-stalk --user= --ask-pass --collect --daemonize --run-time=10 --sleep=10 --cycles=3 –dest= --log=

On Next failure we analyzed and still unable to find RCA. We were under impression that there are some queries which causing this behavior . We changed our focus and try to optimize all possible. Still we were facing random downtime. MySQL got stall and We need to restart as it stops responding.

We used oprofile with pt-stalk which lead us to issues with THP. We also got clue from Oliver's blog post

Debugging a MySQL stall

We disabled THP, one system got stable while anothe was still stalling randonly. We started using perf to get more deep in system calls.

% time     seconds  usecs/call     calls    errors syscall

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

 70.81 1122.971901       27982     40132     14704 futex

 19.98  316.848873     1460133       217         6 restart_syscall

  5.70   90.315204       52448      1722           io_getevents

It lead us to futex Bug which was in kernel-2.6.32-504. According to Blooger

“The impact of this kernel bug is very simple: user processes can deadlock and hang in seemingly impossible situations. A futex wait call (and anything using a futex wait) can stay blocked forever, even though it had been properly woken up by someone.  If you are lucky you may also find soft lockup messages in your dmesg logs. If you are not that lucky (like us, for example), you'll spend a couple of months of someone's time trying to find the fault in your code, when there is nothing there to find.”
It was leaving is clueless every time. First we tested same by using strace. Which resumed MySQL process. Later we upgrdaed our Kernel version and MySQL service start working perfectly fine.
Detailed discussion about this bug is available at

https://groups.google.com/forum/#!searchin/mechanical-sympathy/futex/mechanical-sympathy/QbmpZxp6C64/BonaHiVbEmsJ



Learning

1. THP is not good for database.  
2. Linux expertise always for troubleshooting.
3. Start thinking outside of database for troubleshooting(MySQL always doesn't hit Bug :) )

Execute Linux command from MySQL prompt

When we are working on MySQL running on Linux, We also need to execute some Linux command.  As we need to see the listing the file, clear the MySQL prompt screen. There is no command in MySQL to clear the screen.  In MySQL help you will find the command “\c”, it will show you to clear the current input. It will just cancel the last statement on MySQL Prompt.

Now there are two ways to execute Linux commands on MySQL prompt.

1.    Using the keyword “system”

2.    Using the shortcut “\!”

If you want to clear the MySQL screen running on Linux, execute below command on MySQL prompt

mysql> system clear

or

mysql> \! Clear

These both command will clear the MySQL console.

To execute any other Linux add these keywords before the commands.  See the below example.

mysql> system ls -lrt /home/mysql/cluster

total 12

drwxrwxr-x.  2 mysql mysql 4096 Apr 24 23:46 ndb_data

drwxrwxr-x.  2 mysql mysql 4096 May  4 22:21 config

drwxrwxr-x. 16 mysql mysql 4096 May 16 20:28 MySQL_Data

mysql>

mysql> \! ls -lrt /home/mysql/cluster

total 12

drwxrwxr-x.  2 mysql mysql 4096 Apr 24 23:46 ndb_data

drwxrwxr-x.  2 mysql mysql 4096 May  4 22:21 config

drwxrwxr-x. 16 mysql mysql 4096 May 16 20:28 MySQL_Data

mysql>

The above example is showing the list of file in folder.

You can also execute MySQL specific commands (Taking backup etc). See below example.

mysql> system mysqldump -uroot  sakila>/home/mysql/sakila.sql

mysql>

mysql> system ls -lrt /home/mysql/

total 3280

drwxr-xr-x. 13 mysql mysql    4096 Apr 20 03:21 mysql-cluster-gpl-7.2.5-linux2.6-i686

lrwxrwxrwx.  1 mysql mysql      37 Apr 20 22:18 mysqlc -> mysql-cluster-gpl-7.2.5-linux2.6-i686

drwxrwxr-x.  5 mysql mysql    4096 Apr 24 23:46 cluster

-rw-r--r--.  1 root  root  3350468 May 21 02:00 sakila.sql

mysql>

mysql> \! tail  /home/mysql/sakila.sql

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2012-05-21  2:00:47

For taking backup I have executed below command on MySQL Prompt.

 mysql> system mysqldump -uroot  sakila>/home/mysql/sakila.sql

Once the execution of this statement is complete, I see the listing of file using below command.

mysql> system ls -lrt /home/mysql/

total 3280

drwxr-xr-x. 13 mysql mysql    4096 Apr 20 03:21 mysql-cluster-gpl-7.2.5-linux2.6-i686

lrwxrwxrwx.  1 mysql mysql      37 Apr 20 22:18 mysqlc -> mysql-cluster-gpl-7.2.5-linux2.6-i686

drwxrwxr-x.  5 mysql mysql    4096 Apr 24 23:46 cluster

-rw-r--r--.  1 root  root  3350468 May 21 02:00 sakila.sql

To check the backup file content

mysql> \! tail  /home/mysql/sakila.sql

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2012-05-21  2:00:47

See the below image for detail

Useful Linux Commands

Command		Description
•	man -t ascii | ps2pdf - > ascii.pdf	make a pdf of a manual page
	which command	Show full path name of command
	time command	See how long a command takes
•	time cat	Start stopwatch. Ctrl-d to stop. See also sw
dir navigation
•	cd -	Go to previous directory
•	cd	Go to $HOME directory
	(cd dir && command)	Go to dir, execute command and return to current dir
•	pushd .	Put current dir on stack so you can popd back to it
file searching
•	alias l='ls -l --color=auto'	quick dir listing
•	ls -lrt	List files by date. See also newest and find_mm_yyyy
•	ls /usr/bin | pr -T9 -W$COLUMNS	Print in 9 columns to width of terminal
	find -name '*.[ch]' | xargs grep -E 'expr'	Search 'expr' in this dir and below. See also findrepo
	find -type f -print0 | xargs -r0 grep -F 'example'	Search all regular files for 'example' in this dir and below
	find -maxdepth 1 -type f | xargs grep -F 'example'	Search all regular files for 'example' in this dir
	find -maxdepth 1 -type d | while read dir; do echo $dir; echo cmd2; done	Process each item with multiple commands (in while loop)
•	find -type f ! -perm -444	Find files not readable by all (useful for web site)
•	find -type d ! -perm -111	Find dirs not accessible by all (useful for web site)
•	locate -r 'file[^/]*\.txt'	Search cached index for names. This re is like glob *file*.txt
•	look reference	Quickly search (sorted) dictionary for prefix
•	grep --color reference /usr/share/dict/words	Highlight occurances of regular expression in dictionary
archives and compression
	gpg -c file		Encrypt file
	gpg file.gpg		Decrypt file
	tar -c dir/ | bzip2 > dir.tar.bz2		Make compressed archive of dir/
	bzip2 -dc dir.tar.bz2 | tar -x		Extract archive (use gzip instead of bzip2 for tar.gz files)
	tar -c dir/ | gzip | gpg -c | ssh user@remote 'dd of=dir.tar.gz.gpg'		Make encrypted archive of dir/ on remote machine
	find dir/ -name '*.txt' | tar -c --files-from=- | bzip2 > dir_txt.tar.bz2		Make archive of subset of dir/ and below
	find dir/ -name '*.txt' | xargs cp -a --target-directory=dir_txt/ --parents		Make copy of subset of dir/ and below
	( tar -c /dir/to/copy ) | ( cd /where/to/ && tar -x -p )		Copy (with permissions) copy/ dir to /where/to/ dir
	( cd /dir/to/copy && tar -c . ) | ( cd /where/to/ && tar -x -p )		Copy (with permissions) contents of copy/ dir to /where/to/
	( tar -c /dir/to/copy ) | ssh -C user@remote 'cd /where/to/ && tar -x -p'		Copy (with permissions) copy/ dir to remote:/where/to/ dir
	dd bs=1M if=/dev/sda | gzip | ssh user@remote 'dd of=sda.gz'		Backup harddisk to remote machine
rsync (Network efficient file copier: Use the --dry-run option for testing)
	rsync -P rsync://rsync.server.com/path/to/file file		Only get diffs. Do multiple times for troublesome downloads
	rsync --bwlimit=1000 fromfile tofile		Locally copy with rate limit. It's like nice for I/O
	rsync -az -e ssh --delete ~/public_html/ remote.com:'~/public_html'		Mirror web site (using compression and encryption)
	rsync -auz -e ssh remote:/dir/ . && rsync -auz -e ssh . remote:/dir/		Synchronize current directory with remote one
ssh (Secure SHell)
	ssh $USER@$HOST command		Run command on $HOST as $USER (default command=shell)
•	ssh -f -Y $USER@$HOSTNAME xeyes		Run GUI command on $HOSTNAME as $USER
	scp -p -r $USER@$HOST: file dir/		Copy with permissions to $USER's home directory on $HOST
	scp -c arcfour $USER@$LANHOST: bigfile		Use faster crypto for local LAN. This might saturate GigE
	ssh -g -L 8080:localhost:80 root@$HOST		Forward connections to $HOSTNAME:8080 out to $HOST:80
	ssh -R 1434:imap:143 root@$HOST		Forward connections from $HOST:1434 in to imap:143
	ssh-copy-id $USER@$HOST		Install public key for $USER@$HOST for password-less log in
wget (multi purpose download tool)
•	(cd dir/ && wget -nd -pHEKk http://www.pixelbeat.org/cmdline.html)		Store local browsable version of a page to the current dir
	wget -c http://www.example.com/large.file		Continue downloading a partially downloaded file
	wget -r -nd -np -l1 -A '*.jpg' http://www.example.com/dir/		Download a set of files to the current directory
	wget ftp://remote/file[1-9].iso/		FTP supports globbing directly
•	wget -q -O- http://www.pixelbeat.org/timeline.html | grep 'a href' | head		Process output directly
	echo 'wget url' | at 01:00		Download url at 1AM to current dir
	wget --limit-rate=20k url		Do a low priority download (limit to 20KB/s in this case)
	wget -nv --spider --force-html -i bookmarks.html		Check links in a file
	wget --mirror http://www.example.com/		Efficiently update a local copy of a site (handy from cron)
networking
	ethtool eth0		Show status of ethernet interface eth0
	ethtool --change eth0 autoneg off speed 100 duplex full		Manually set ethernet interface speed
	iwconfig eth1		Show status of wireless interface eth1
	iwconfig eth1 rate 1Mb/s fixed		Manually set wireless interface speed
•	iwlist scan		List wireless networks in range
•	ip link show		List network interfaces
	ip link set dev eth0 name wan		Rename interface eth0 to wan
	ip link set dev eth0 up		Bring interface eth0 up (or down)
•	ip addr show		List addresses for interfaces
	ip addr add 1.2.3.4/24 brd + dev eth0		Add (or del) ip and mask (255.255.255.0)
•	ip route show		List routing table
	ip route add default via 1.2.3.254		Set default gateway to 1.2.3.254
•	host pixelbeat.org		Lookup DNS ip address for name or vice versa
•	hostname -i		Lookup local ip address (equivalent to host `hostname`)
•	whois pixelbeat.org		Lookup whois info for hostname or ip address
•	netstat -tupl		List internet services on a system
•	netstat -tup		List active connections to/from system
text manipulation (Note sed uses stdin and stdout. Newer versions support inplace editing with the -i option)
	sed 's/string1/string2/g'		Replace string1 with string2
	sed 's/\(.*\)1/\12/g'		Modify anystring1 to anystring2
	sed '/ *#/d; /^ *$/d'		Remove comments and blank lines
	sed ':a; /\\$/N; s/\\\n//; ta'		Concatenate lines with trailing \
	sed 's/[ \t]*$//'		Remove trailing spaces from lines
	sed 's/\([`"$\]\)/\\\1/g'		Escape shell metacharacters active within double quotes
•	seq 10 | sed "s/^/      /; s/ *\(.\{7,\}\)/\1/"		Right align numbers
	sed -n '1000{p;q}'		Print 1000th line
	sed -n '10,20p;20q'		Print lines 10 to 20
	sed -n 's/.*