sorted by: recent | see : popular

Content Tagged with tuning + MySQL

At the 2008 MySQL Conference and Expo, Ronald Bradford delivered "The Top 20 Design Tips for Enterprise Data Architects". See the slides on the Forge at http://forge.mysql.com/wiki/MySQLConf2008ThursdayNotes#Top_20_DB_Design_Tips_Every_Architect_Needs_to_Know

At the 2008 MySQL User Conference and Expo, Baron Schwartz spoke on "The MySQL Query Cache". Download the slides, see people's notes, and more on the MySQL Forge Wiki at http://forge.mysql.com/wiki/MySQLConf2008WednesdayNotes#The_MySQL_Query_Cache

At the 2008 MySQL User Conference and Expo, Robert Hodges spoke on "Portable Scale-out Benchmarks for MySQL". Download slides and see links to blog postings at the MySQL Forge Wiki at http://forge.mysql.com/wiki/MySQLConf2008WednesdayNotes#Portable_Scale-out_Benchmarks_for_MySQL

vmstat is a very useful tool, especially when you are doing performance tuning of an application like MySQL. The only problem with the output from vmstat is that it is rather non-visual. A simple solution is to import the output in a spreadsheet like Openoffice Calc and create a graph, but it is long and painful. Creating many such graphs is not fun at all.

Fortunately, in the Linux world, there are tools to generate graph nearly automatically. Here is my own set of tools to creates graphs from vmstat output. You need the gnuplot program and awk, which is on all Linux version.

The output of vmstat looks like this:

procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
1  0    164  48604  45136 4871464    0    0    94   111    4    7  1  0 98  0  0
0  2    164  48476  45188 4871552    0    0    35    15 1062  133  1  0 99  0  0
0  6    164  44960  46540 4870400    0    0  3159   497 4149 5618  2  1 78 19  0
2  1    164  64420  46116 4843140    0    0  8651  1794 4815 7719 10  3 73 15  0
2 11    164  45180  46452 4855156    0    0  2325  3827 5846 10320 13  3 72 11  0
1  3    164  45528  45652 4852376    0    0  8652  1824 4857 6831  7  5 67 22  0
0  3    164  59024  45764 4834444    0    0  2394  4118 4768 6544 12  3 70 14  0
...

We first need a script to get rid of the titles and to create an X axis number. I wrote a small script, filtervmstat, which look like this:

 $ more ~/bin/filtervmstat

cat $1 | grep -v memory | grep -v buff | perl -p -e "s|  | |g" -| perl -p -e "s|  | |g" - | perl -p -e "s|  | |g" - | awk 'BEGIN {x=1}; {print x++ " " $1 " " $2 " " $3 " " $4 " " $5 " " $6 " " $7 " " $8 " " $9 " " $10 " " $11 " " $12 " " $13 " " $14 " " $15 " " $16;}'

I am not a awk expert, maybe the column enumeration could be simplified. This will give use the following output:

1 1 0 164 48604 45136 4871464 0 0 94 111 4 7 1 0 98 0
2 0 2 164 48476 45188 4871552 0 0 35 15 1062 133 1 0 99 0
3 0 6 164 44960 46540 4870400 0 0 3159 497 4149 5618 2 1 78 19
4 2 1 164 64420 46116 4843140 0 0 8651 1794 4815 7719 10 3 73 15
5 2 11 164 45180 46452 4855156 0 0 2325 3827 5846 10320 13 3 72 11
6 1 3 164 45528 45652 4852376 0 0 8652 1824 4857 6831 7 5 67 22
7 0 3 164 59024 45764 4834444 0 0 2394 4118 4768 6544 12 3 70 14
8 1 12 164 46284 45792 4842704 0 0 5980 2804 6378 9984 10 6 68 16
9 1 3 164 58760 45568 4823504 0 0 5203 4712 6470 9687 15 4 62 19

Next, we need a gnuplot template in order to plot column 14, 15 and 16 which are respectively, user, system and idle cpu. My cpugraph.plt template is here:

#!/usr/bin/gnuplot -persist
#
#
#       G N U P L O T
#       Version 4.0 patchlevel 0
#       last modified Thu Apr 15 14:44:22 CEST 2004
#       System: Linux 2.6.22
#
#       Copyright (C) 1986 - 1993, 1998, 2004
#       Thomas Williams, Colin Kelley and many others
#
#       This is gnuplot version 4.0.  Please refer to the documentation
#       for command syntax changes.  The old syntax will be accepted
#       throughout the 4.0 series, but all save files use the new syntax.
#
#       Type `help` to access the on-line reference manual.
#       The gnuplot FAQ is available from
#               http://www.gnuplot.info/faq/
#
#       Send comments and requests for help to
#
#       Send bugs, suggestions and mods to
#
#
# set terminal x11
# set output
unset clip points
set clip one
unset clip two
set bar 1.000000
set border 31 lt -1 lw 1.000
set xdata
set ydata
set zdata
set x2data
set y2data
set timefmt x "%d/%m/%y,%H:%M"
set timefmt y "%d/%m/%y,%H:%M"
set timefmt z "%d/%m/%y,%H:%M"
set timefmt x2 "%d/%m/%y,%H:%M"
set timefmt y2 "%d/%m/%y,%H:%M"
set timefmt cb "%d/%m/%y,%H:%M"
set boxwidth
set style fill empty border
set dummy x,y
set format x "% g"
set format y "% g"
set format x2 "% g"
set format y2 "% g"
set format z "% g"
set format cb "% g"
set angles radians
unset grid
set key title ""
set key right top Right noreverse enhanced box linetype -2 linewidth 1.000 samplen 4 spacing 1 width 0 height 0 autotitles
unset label
unset arrow
unset style line
unset style arrow
unset logscale
set offsets 0, 0, 0, 0
set pointsize 1
set encoding default
unset polar
unset parametric
unset decimalsign
set view 60, 30, 1, 1
set samples 100, 100
set isosamples 10, 10
set surface
unset contour
set clabel '%8.3g'
set mapping cartesian
set datafile separator whitespace
unset hidden3d
set cntrparam order 4
set cntrparam linear
set cntrparam levels auto 5
set cntrparam points 5
set size ratio 0 1,1
set origin 0,0
set style data points
set style function lines
set xzeroaxis lt -2 lw 1.000
set yzeroaxis lt -2 lw 1.000
set x2zeroaxis lt -2 lw 1.000
set y2zeroaxis lt -2 lw 1.000
set tics in
set ticslevel 0.5
set ticscale 1 0.5
set mxtics default
set mytics default
set mztics default
set mx2tics default
set my2tics default
set mcbtics default
set xtics border mirror norotate autofreq
set ytics border mirror norotate autofreq
set ztics border nomirror norotate autofreq
set nox2tics
set noy2tics
set cbtics border mirror norotate autofreq
set title "" 0.000000,0.000000  font ""
set timestamp "" bottom norotate 0.000000,0.000000  ""
set rrange [ * : * ] noreverse nowriteback  # (currently [0.00000:10.0000] )
set trange [ * : * ] noreverse nowriteback  # (currently [-5.00000:5.00000] )
set urange [ * : * ] noreverse nowriteback  # (currently [-5.00000:5.00000] )
set vrange [ * : * ] noreverse nowriteback  # (currently [-5.00000:5.00000] )
set xlabel "Sample" 0.000000,0.000000  font ""
set x2label "" 0.000000,0.000000  font ""
set xrange [ * : * ] noreverse nowriteback  # (currently [-10.0000:10.0000] )
set x2range [ * : * ] noreverse nowriteback  # (currently [-10.0000:10.0000] )
set ylabel "CPU" 0.000000,0.000000  font ""
set y2label "" 0.000000,0.000000  font ""
set yrange [ * : * ] noreverse nowriteback  # (currently [-10.0000:10.0000] )
set y2range [ * : * ] noreverse nowriteback  # (currently [-10.0000:10.0000] )
set zlabel "" 0.000000,0.000000  font ""
set zrange [ * : * ] noreverse nowriteback  # (currently [-10.0000:10.0000] )
set cblabel "" 0.000000,0.000000  font ""
set cbrange [ * : * ] noreverse nowriteback  # (currently [-10.0000:10.0000] )
set zero 1e-08
set lmargin -1
set bmargin -1
set rmargin -1
set tmargin -1
set locale "C"
set pm3d scansautomatic flush begin noftriangles nohidden3d implicit corners2color mean
unset pm3d
set palette positive nops_allcF maxcolors 0 gamma 1.5 color model RGB
set palette rgbformulae 7, 5, 15
set colorbox default
set colorbox vertical origin 0.9,0.2 size 0.1,0.63 bdefault
set loadpath
set fontpath
set fit noerrorvariables
plot "/tmp/cpudata.dat" using 1:16 with lines title "Idle", "/tmp/cpudata.dat" using 1:15 with lines title "System", "/tmp/cpudata.dat" using 1:14 with lines
title "User"
#    EOF

It is a very simple template that produce the graph on screen in a X windows with the data coming from /tmp/cpudata.dat. I wrote another small script just to ease the process:

$ more ~/bin/cpuvmstat
HOME=/home/yves
cat $1 | filtervmstat > /tmp/cpudata.dat
if [ "a$2" == "apng" ] ; then
$HOME/bin/cpugraphpng.plt
exit
else
$HOME/bin/cpugraph.plt
fi
rm -f /tmp/cpudata.dat

The cpugraphpng.plt is the same template as before except the output has been change to a png file. So, calling this script with a vmstat output as parameter give the following result on you screen:

The graph is created is less then one second, a very desirable feature for an impatient guy like me…

If you want to save the graph as a png file, create another template as cpugraphpng.plt (copy cpugraph.plt) and change the first two lines to this:

set terminal png
set output "/tmp/cpugraph.png"

Yves

Files:

cpugraph.plt

cpuvmstat

filtervmstat

So, the article at:

http://mysql-dba-journey.blogspot.com/2007/11/mysql-and-vmware.html says:

Don’t get seduced to the dark side unless you understand all the issues.

And that’s wonderful and all, but….what are all the issues? What are some of the issues? Is it related more to VMware, or more to MySQL, or more to MySQL on VMware? Is it something like “VMware isn’t stable” or more like “load testing on vmware isn’t always going to work because you won’t have full resources”?

Many people talk about using virtualization for development and testing….but if you develop and test on a virtual machine and then put it on a physical machine for production, isn’t that basically having differing environments for dev/testing and production, which is usually seen as bad? If a line of code crashes a virtual machine but is fine on production, is it worth tracking the bug down? How many hours will you spend doing that?

Also, how is using a virtual machine better/worse/different from using something like mysqld_multi on a machine with many IPs, or other strategies folks use in dev/test so they don’t have to buy the exact same hardware as in production, but still have the same separation of databases, etc?