Pretty often I was asked by many people about solutions I'm using while
analyzing MySQL performance issues and generating various graphs
presented in my reports and blog posts.. - Well, I've always used and
continue to use dim_STAT -
the tool I'm developing myself since 15 years now, and which is freely
available on this site :-) The tool is mainly collecting any kind of
stats from the system and applications, and then giving you a way to
analyze them live or later via web interface - nothing new now days, but
well, I have my habits, like to play with collected data and want to
know exactly what I'm analyzing ;-)) (while many of you are using other
tools, including a really excellent MySQL Enterprise Monitor, and it's
all ok for me too - as in my mind changes are coming only since people
become unhappy with what they have, or hitting some limitations, etc.)
-- personally, I'm pretty happy, and wish you the same ;-))
But I have a small problem -- I'm very curious by problematic workloads
(and already started a call
for workload scenarios this year, and many thanks to all who
answered) -- but now I'm also very interested by workload stats,
specially if you're trying MySQL 5.6 and hitting some unexpected
performance issues on your system ;-)
If you're agree to collect MySQL and system stats during such a
workload, the following are the instructions about how to install a
small kit with my tools on your server, collect the stats in the way
I'll be able to analyze them more efficiently, and then archive the data
and send it to me.
The tool kit is part of standard dim_STAT distribution, but I've made a
simplified version with all the stuff already pre-configured for MySQL
stats, so the only things you'll need:
-
provide a connect info for your MySQL instance
(user/password/host:port)
-
check some system libs are not missed on your system to run my
binaries (and install few missed if any)
-
collect stats with 10sec interval during your workload
-
send me the result
Easy, no? ;-))
NOTE: there is no any traces collected about your data or any other kind
of sensible information -- all the info is collected within flat files,
and you may verify them one by one to be sure about their content..
Let's try?..
Setup STAT-service on your Linux host
1. Install STAT-service
should be logged as a "root" user.
# cd /etc
# tar xzf /path/to/STATsrv-Linux-x86-mysql.tgz
Check 32-bit libraries dependency within /etc/STATsrv/bin directory
(most of binaries are compiled in 32bit to be able to run on any Linux
system, however some of Linux distros today are not installing 32bit
libraries on 64bit systems by default) - mainly you have to check WebX
and WebX.mySQL:
ex. on OEL6.2:
# ldd /etc/STATsrv/bin/WebX
linux-gate.so.1 => (0xf77c1000)
libc.so.6 => /lib/libc.so.6 (0xf761f000)
/lib/ld-linux.so.2 (0xf77c2000)
# ldd /etc/STATsrv/bin/WebX.mySQL
linux-gate.so.1 => (0xf773e000)
libm.so.6 => /lib/libm.so.6 (0xf7702000)
libz.so.1 => not found <================= missing..
libc.so.6 => /lib/libc.so.6 (0xf7572000)
/lib/ld-linux.so.2 (0xf773f000)
Add missing packages:
# yum install libzip.i686
...
2. Edit MySQL connection parameters:
# vi /etc/STATsrv/bin/.env-mysql
Change user/password/host/port to yours:
# Set connection params:
User=dim
Passwd=dim
Host=127.0.0.1:3306
export User Passwd Host
3. Check STAT commands are ready to collect from your MySQL instance:
try to execute mysqlLOAD script to be sure to see some real numbers from
your MySQL server:
# /etc/STATsrv/bin/mysqlLOAD.sh 5
On Sessions InnDirty InnoFree KeyDirty OpFiles OpTables ByteRx/s
ByteTx/s Commit/s Delete/s Insert/s Select/s Update/s InnDsy/s InnDrd/s
InnDwr/s InnLwr/s InnLsy/s Key_Rd/s Key_Wr/s Query/s AbrtClnt AbrtConn
Connects SlowReqs TabLckWt Rollback
1 38.00 1.00 3958924.00 0.00 19.00 1147.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00
1 38.00 1.00 3958924.00 0.00 19.00 1147.00 4140059.75 18216136.00 0.00
0.00 0.00 206989.00 0.00 3.20 0.00 3.20 2.00 2.20 0.00 0.00 206972.80
0.00 0.00 0.00 0.00 0.00 0.00
^C
if you don't see any data coming from MySQL:
-
be sure your database is running ;-)
-
be sure MySQL access via IP is not protected by your firewall
-
be sure MySQL is listening on the IP port you've configured
-
be sure user/password pair is correct..
-
goto 1. and debug ;-)
4. Once everything is working..
Be sure InnoDB monitoring via METRICS table is enabled in your MySQL 5.6
config setting:
-
innodb_monitor_enable = '%'
If you're expecting to monitor the most hot wait event via MySQL
Performance Schema (PFS), be sure PFS is turned on MySQL start, for 5.6:
-
performance_schema=ON
-
performance_schema_instrument='%=on'
-
and once MySQL is started, execute the following SQL statements to
enable mutexes and I/O instrumentation only (if you don't need any
other):
-
use performance_schema;
-
update setup_consumers set enabled = 'no';
-
update setup_consumers set enabled = 'yes' where name like
'%instr%';
-
update setup_instruments set enabled = 'no', timed = 'no';
-
update setup_instruments set enabled = 'yes', timed = 'yes' where
name like '%wait/synch%';
-
update setup_instruments set enabled = 'yes', timed = 'yes' where
name like '%wait/io/file%';
Collecting your system and MySQL server stats
5. Start EasySTAT to collect stats from your workload (for ex. every
10sec during 24 hours):
# mkdir /var/tmp/stats <--- create output directory
# /etc/STATsrv/bin/EasySTAT.sh /var/tmp/stats 10 24
NOTE: you may always interrupt its execution by Ctrl+C.
Once collected, just make a tarball from /var/tmp/stats and send it to
me (if it's small, or place it somewhere and give me a way to get it via
Internet)..
EasySTAT is a part of STAT-service (client agent for dim_STAT tool)
Posted by
Dimitri at 23:46
Categories:
dim_STAT,
MySQL