3 ms·
Great list. A few more (related or building on those there) Quick mysql "why is my database under so much load suddenly" that doesn't rely on the slow query lo
by falcolas 14y ago
Great list. A few more (related or building on those there)
Quick mysql "why is my database under so much load suddenly" that doesn't rely on the slow query log:
pt-query-digest --processlist h=<host> --interval=0.01 --print > /tmp/queries.out
pt-query-digest /tmp/queries.out
Tracking down what files exactly are filling up the disk. Go to /, and follow the big numbers:
du -smc *
Combine strace (for the file handle no), lsof (for the file handle to ip/port) and netstat (to find the process on the remote host) to track RPC calls that are hung.
For 'ps auxww', throw on an f to get a visual representation of child processes.
And finally, ssh tunnels (which can be chained together) to get past bastion servers easily:
Host <alias>
HostName <ip>
ProxyCommand ssh -q <bastion host, can be another alias in .ssh/config> "nc -w 3600 %h %p"
- AaronBBrown 14y agoI prefer using tcpdump instead of processlist when dealing with pt-query-digest. You get everything instead of just what happens to show up during the interval specified. This is, of course, assuming that you don't allow localhost socket connections.
- falcolas 14y agoVery true - extremely fast queries can slip between the processlist checks. The problem I've encountered with tcpdump is that you can cause packet loss by running it. It's not common, but it happens enough that I've become gun shy about using it on production systems. The pt-query-digest function will cause additional load on the DB, but it won't interrupt communications.
- X-Istence 14y agoWhat OS will drop packets while tcpdump is running?
- falcolas 14y agoLinux. :) If tcpdump can't keep up with the incoming traffic, the kernel will drop the packets in its buffer (or rather overwrite them with new packets). Throw in TCP's flow and congestion control protocols, and dropped packets can have disastrous effects on your database. Google has many references you might find useful on this subject.
- sudara 14y agoThanks for that! I'll take a look at the pt-query-digest stuff again and add!
- olasd 14y agoWith recent enough SSH (> 5.4), SSH tunnels can be achieved with Host <alias> HostName <ip> ProxyCommand ssh -W %h:%p <bastion host> Which doesn't require nc (or anything else) installed on the bastion host.