MYSQL
TROUBLESHOOTING
Or, what to do when MySQL starts throwing a fit
Monday, June 6, 2011
ABOUT THE CLASS
24 hours over three days
Very Short Lecture and Lots of Labs
Hours:
8:30am - 5:00pm
Lunch: 11:45am - 1:00pm
Monday, June 6, 2011
ABOUT THE
INSTRUCTOR
Nathan Isburgh
instructor@[Link]
Unix user 15+ years
Teaching 10+ years
MySQL user 8+ years
RHCE, CISSP
Forgetful, goofy, patient :)
Monday, June 6, 2011
ABOUT THE COLLEGE
Rackspace Parking Sticker = good to go
Breaks when you need them
Breakroom downstairs - labeled Laundry
Sodas - bottles in machine ( $1.25 ) or cans in mini-fridge ( $0.50 )
Cafeteria
Do not speed!
No smoking anywhere. Can only smoke sitting in car.
Monday, June 6, 2011
ABOUT THE STUDENTS
Name?
Time served, I mean employed, at Rackspace?
Department?
Unix skill level?
MySQL skill level?
How would you teach someone to troubleshoot?
Monday, June 6, 2011
EXPECTATIONS OF
STUDENTS
Strong foundation in basic Linux use and administration
Ask Questions!
Complete the labs
Email if youre going to be late/miss class
Have fun
Learn something
Monday, June 6, 2011
OVERVIEW
Troubleshooting is a thorough methodology used to track
down the cause of problem.
Keywords: thorough and methodology
Without a thorough and exhaustive approach, the issue
might be overlooked
Without a strong and methodical approach, the issue may be
misdiagnosed
Monday, June 6, 2011
TROUBLESHOOTING
KEYS
Most Important: Only change one thing at a time
Check #1 most likely cause: You
Check logs for error messages
After that, check configuration and permissions
If all else fails, slowly, piece by piece, start removing
complexity from the system to narrow down the problem area.
DOCUMENT EVERYTHING
Monday, June 6, 2011
LOGS
One of the easiest places to find the cause of a problem is in
the log files.
Log files store informational messages from software. The
types of messages include debug information, status
information, warnings, errors and more.
MySQL manages all of its logging needs. If installed from
package, many distributions configure MySQL to log to:
/var/log/[Link]
Monday, June 6, 2011
WHEN LOGS FAIL...
Looking through logs is all fine and dandy, but really thats a
best case scenario. Your software and hardware rarely come
out and announce problems and solutions in the log files.
No, its not that easy!
More often, users will encounter symptoms of a problem,
and you, as the BOFH ( hopefully not yet! ), will be tasked
with finding and fixing the issue.
Monday, June 6, 2011
TROUBLESHOOTING
TOOLS
Troubleshooting is part science, part mystical art.
Hopefully, through this class, you will start to develop both
sides of the equation.
For now, a discussion of several tools to help the process of
troubleshooting MySQL will get you started.
Monday, June 6, 2011
DOCUMENTATION
Documentation.
Documentation.
DOCUMENTATION.
[Link]/doc
Monday, June 6, 2011
TOP
top: Self-updating tool displays combination summary at top,
followed by ordered list of processes. Fully customizable.
The summary includes uptime information, memory
breakdowns, CPU utilization and process state summaries
The process display can be customized and sorted to suit need
top - 16:39:32 up 682 days, 10:41, 2 users, load average: 0.01, 0.00, 0.00
Tasks: 118 total,
1 running, 116 sleeping,
1 stopped,
0 zombie
Cpu(s): 0.1%us, 0.0%sy, 0.0%ni, 99.8%id, 0.0%wa, 0.0%hi, 0.0%si, 0.1%st
Mem:
262316k total,
258024k used,
4292k free,
7380k buffers
Swap:
524280k total,
74564k used,
449716k free,
67808k cached
PID
1
2
3
Monday, June 6, 2011
USER
root
root
root
PR
15
RT
34
NI VIRT
0 10316
0
0
19
0
RES
648
0
0
SHR
592
0
0
S %CPU %MEM
S
0 0.2
S
0 0.0
S
0 0.0
TIME+
0:06.24
0:04.88
0:00.19
COMMAND
init
migration/0
ksoftirqd/0
DF
df: lists filesystem utilization
Breaks down size and use information for each mounted
filesystem
-h is useful option to display in human-friendly format
[root@dev1 ~]# df -h
Filesystem
/dev/sda1
none
[root@dev1 ~]#
Monday, June 6, 2011
Size
9.4G
129M
Used Avail Use% Mounted on
7.2G 1.8G 81% /
0 129M
0% /dev/shm
ULIMIT
ulimit: Sets resource limits
Can limit open files, memory use, cpu time, subprocesses
and more.
[root@dev1 ~]# ulimit -a
core file size
(blocks, -c)
data seg size
(kbytes, -d)
max nice
(-e)
file size
(blocks, -f)
pending signals
(-i)
max locked memory
(kbytes, -l)
max memory size
(kbytes, -m)
open files
(-n)
pipe size
(512 bytes, -p)
POSIX message queues
(bytes, -q)
max rt priority
(-r)
stack size
(kbytes, -s)
cpu time
(seconds, -t)
max user processes
(-u)
virtual memory
(kbytes, -v)
file locks
(-x)
[root@dev1 ~]#
Monday, June 6, 2011
0
unlimited
0
unlimited
2112
32
unlimited
1024
8
819200
0
8192
unlimited
2112
unlimited
unlimited
STRACE
strace: Traces each library call a process makes
Extremely useful to see what a process is doing
Can find errors, bugs, permission issues and more
Lets play with tracing MySQL for a few minutes...
Monday, June 6, 2011
ERROR MESSAGES
MySQL error messages contain useful information, which should
be reviewed prior to in-depth troubleshooting:
ERROR 1146 (42S02): Table 'blah' doesn't exist
The MySQL specific error code: 1146. Stable across GA
releases, can be looked up in documentation.
The five-character SQLSTATE value: 42S02. Standardized to
ANSI SQL and ODBC. HY000 means a MySQL specific error
not mappable to a SQLSTATE value.
Monday, June 6, 2011
PERROR
Also note that MySQL error messages will sometimes include an
additional error code in parenthesis. In this case, use perror to
figure out what happened:
ERROR 1005 at line 20: Cant create table ./
test/[Link] (errno: 150)
shell> perror 150
MySQL error code 150: Foreign key constraint is
incorrectly formed
Monday, June 6, 2011
REPLICATION
FAILURES
Generally, once replication is established and working, problems
only arise when replicated queries fail on the slave. To remedy the
problem ( if you are certain the data integrity is solid):
To skip just one query:
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
To skip all queries that are failing on an error code, add the following
to /etc/[Link]:
slave-skip-errors = code
Monday, June 6, 2011
[Link]();
Monday, June 6, 2011