[AzPHP] deciphering vmstat and iostat

Vimal Shah vimals at sokikom.com
Fri Feb 1 14:36:22 MST 2013


Brad,

Thanks for taking some time to research this. I greatly appreciate it.

RE: Jason's feedback. Can you expand on *r procs* and *b procs*? What
command will allow me to see that?

RE: Paul's feedback: Completely agreed and we are currently doing this. The
analysis of slow queries and how to optimize became clear via this:
http://www.slideshare.net/phpcodemonkey/mysql-explain-explained. A follow
up here is what determines the right threshold, ie, 1s, 500ms, 10ms?It
seems that there will never be NO slow queries, just depends on the
threshold. We are currently optimizing all queries above 500ms, with a goal
to reach 100ms.


On Thu, Jan 31, 2013 at 9:16 PM, Brad Jannenga <brad at jannenga.com> wrote:

> Here is a little feedback from my Sr. SysAdmin and DBA - hope it helps:
>
> Jason our hardcore linux sysadmin: *Looks cpu bound, based on mostly
> non-zero r procs versus mostly-zero b procs.*
>
> Paul our hardcore DBA: I can't speak to the stats directly, but any time
> the hardware appears to be stressed, it is important to make sure the DB is
> being used efficiently.  Is there any access to a Slow Query log, or has
> anyone even just watched the queries that are running during peak times?
>  Is there one or a few queries that are seen most often or run the longest?
>  Is there one or a few queries that run much more often than they
> reasonably should?  Are there complex joins that do not have supporting
> indexes? High CPU load can be caused by sorting unsupported by an index, or
> full table scans.  Before throwing hardware at a problem, it is worth
> analyzing the queries that are running.
>
> We've faced our fair share of database performance obsticles and the team
> has some good perspective on best practices if you need anything further.
>
> Brad
>
> On Jan 31, 2013, at 3:49 PM, Vimal Shah <vimals at sokikom.com> wrote:
>
> Hello all and thank you for your time.
>
> I'm not too familiar with vmstat/iostat and their intricacies. I'd like to
> get better. While their resource metric (si, so, bi,..etc) definitions are
> clear, their implications are not. These two commands were run today during
> peak traffic. The DB server has 4 CPUs, with 8GB RAM. Disk details (RAID
> type and rpms) are unavailable unfortunately due to the nature of the VPS.
> Most resources are being used by mysql (installed as MariaDB 5.5).
>
> *What question(s) would the experienced DBA develop upon viewing this? *Some
> of my naive questions:
>
>    - si and so are non-zero occasionally? does this
>    imply occasional thrashing?
>    - wa is non-zero occasionally? does this imply an occasional disk I/O
>    bottleneck?
>    - the output shows cache is ~ 1.5e6. is this too low (relative to 8GB)?
>
>
> *# vmstat 5*
> *
> procs -----------memory---------- ---swap-- -----io---- --system--
> -----cpu-----
> r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id
> wa st
>  0  0 862896  40368  16596 1547644    0    0     2    72    1    1  6  0
> 93  0  0
> 2  0 862896  40236  16596 1547796    0    0    13  1524 3697 1323 26  1 73
>  0  0
>  2  1 862896  39988  16596 1547964    0    0    18   117 3655 1243 28  1
> 72  0  0
> 1  0 862896  39592  16596 1548108    0    0    25  1564 3743 1414 26  1 73
>  0  0
>  3  0 862896  39336  16596 1548344    0    0    13   197 4465 1468 35  1
> 64  0  0
> 1  0 862896  38956  16596 1548568    0    0    22  1580 4338 1589 31  1 67
>  1  0
>  2  1 862896  38832  16596 1548848    0    0    24   182 3653 1367 26  1
> 74  0  0
> 9  0 863032  40660  16588 1545628    6   28    25  1617 4676 1660 39  3 58
>  0  0
>  0  0 863032  41412  16588 1545736    0    0     3  1488 3859 1415 22  4
> 74  1  0
> 3  0 863032  41608  16592 1545832    0    0     8  1527 4589 1889 24  6 66
>  3  0
>  4  1 863032  42492  16592 1545996    0    0    10   161 4451 1539 34  1
> 65  0  0
> 1  0 863028  41996  16596 1546184    6    0    31  1563 4490 1605 34  1 64
>  1  0
>  1  0 863028  41848  16600 1546480    0    0    19   131 3968 1246 36  1
> 63  0  0
> 3  0 863028  41656  16604 1546764    0    0    31   329 3400 1276 24  0 76
>  0  0
>  1  0 863028  41532  16604 1546840    0    0     6   124 3738 1313 28  1
> 71  0  0
> 1  0 863028  41268  16604 1547084    0    0    21  1681 4106 1729 32  1 66
>  1  0
>  0  0 863028  40640  16604 1547468    0    0    42   485 4057 1765 29  1
> 69  1  0
> 0  0 863028  40500  16604 1547636    0    0    14   213 4350 1645 37  1 62
>  0  0
>  0  0 863028  40300  16608 1547800    0    0    15  1526 3996 1309 29  1
> 70  0  0
> 0  0 863028  40184  16608 1547972    0    0    13  1556 4933 1655 40  1 59
>  0  0
>  1  0 863028  39664  16608 1548056    0    0    26   202 4725 1799 35  1
> 64  0  0
> 0  0 863028  39756  16608 1548372    0    0    17   440 3882 1668 24  1 75
>  0  0
>  1  0 863028  39260  16608 1548904    0    0    60   374 4518 1917 31  1
> 67  0  0
> 1  0 863108  40964  16584 1546944    0   16    68   519 4874 1971 39  1 60
>  0  0
>  0  0 863108  40552  16584 1547356    0    0    45   307 5053 1951 38  1
> 61  0  0
> 2  0 863108  40172  16584 1547584    7    0    33   340 4298 1534 36  1 63
>  1  0
>  1  0 863108  40272  16584 1547704    0    0     6   159 3510 1325 23  1
> 76  0  0
> 1  0 863108  39940  16584 1548072    4    0    32  1616 4365 1582 33  1 66
>  0  0
>  2  0 863108  39692  16584 1548512    0    0    32  1625 3699 1496 25  1
> 74  0  0
> 6  0 863108  38436  16584 1549920    0    0   130   652 5934 2684 47  1 51
>  1  0
>  0  0 863188  40124  16560 1547648    0   16    62   514 5496 2185 44  1
> 55  0  0
> 2  0 863188  39720  16560 1547944    0    0    21   301 5442 2056 40  1 58
>  0  0
>  1  0 863188  39696  16560 1548180    0    0    14   374 4301 1863 31  1
> 67  0  0
> 1  0 863188  39424  16560 1548264    0    0     6   144 4274 1495 29  1 70
>  0  0
>  0  0 863188  39448  16564 1548468    0    0     9  1630 4269 1769 28  1
> 70  1  0
> 0  0 863188  39200  16564 1548628    0    0    20   126 3568 1242 23  1 75
>  1  0
>  2  0 863188  39068  16564 1548840    0    0     8  1645 3373 1609 19  1
> 80  0  0
> 1  0 863188  38812  16564 1548988    0    0    24   223 3468 1396 24  1 75
>  0  0
>  1  0 863268  41400  16552 1546596    0   16    23  1624 4332 1573 33  1
> 66  0  0
> 1  0 863268  41136  16552 1546844    0    0    27   243 4811 1789 35  1 64
>  0  0
>  0  0 863268  41168  16552 1547036    0    0    14   332 3993 1501 30  1
> 69  0  0
> 2  0 863268  41052  16552 1547312    0    0    30   187 4543 1470 37  1 62
>  0  0
>  1  0 863268  40672  16552 1547468    0    0     6  1491 4139 1317 33  1
> 66  0  0
> 2  0 863268  40664  16552 1547544    0    0     9    86 3872 1297 28  1 71
>  0  0
>  0  0 863268  40308  16552 1547760    0    0    21  1635 4700 1849 31  1
> 67  1  0
> 2  0 863264  40308  16552 1547928    0    0    12   137 3996 1473 30  1 68
>  1  0
>  2  0 863264  39564  16552 1548240    0    0    16  1550 5172 1875 33  1
> 66  0  0
> 1  0 863264  39556  16552 1548648    0    0    14   183 4079 1434 27  1 72
>  0  0
>  1  0 863260  38804  16552 1548980    0    0    37   318 4255 1563 27  1
> 72  0  0
> 0  0 863260  38548  16552 1549248    0    0    32   218 4401 1674 27  1 72
>  0  0
>  0  0 863380  41136  16540 1546780    0   24    14   355 4154 1589 31  1
> 68  0  0
> 1  0 863380  40872  16540 1546948    0    0    14   142 4042 1384 29  1 70
>  0  0
>  1  0 863380  40732  16540 1547268    0    0    22  1554 3403 1250 24  1
> 75  0  0
> 1  0 863380  40352  16540 1547776    0    0    55   386 4370 2072 24  1 74
>  1  0
>  0  0 863380  39484  16540 1548056    0    0    34   356 4190 1460 36  1
> 63  0  0
> 2  0 863380  39236  16540 1548424    0    0    41   251 3914 1456 30  1 69
>  0  0
>  2  0 863380  39184  16540 1548560    0    0    15   228 3968 1263 32  1
> 67  0  0
> 0  0 863380  39060  16540 1548908    0    0    36   247 4383 1672 32  1 67
>  0  0
>  1  0 863380  38912  16540 1549028    0    0     9  1540 3734 1367 32  1
> 67  0  0
> 1  0 863380  38664  16540 1549092    0    0     4   113 2639 1074 15  0 85
>  0  0
>  2  0 863380  38572  16540 1549348   13    0    23  1578 3099 1337 20  1
> 79  0  0
> 1  0 863492  41556  16528 1546644    0   22    53   440 4199 1908 28  1 71
>  1  0
>  2  0 863492  41308  16528 1546896   13    0    38   375 3480 1507 22  1
> 77  0  0
> 1  0 863492  41308  16528 1547156    0    0    21   176 3589 1295 27  1 72
>  1  0
>  0  0 863492  41060  16528 1547372    0    0    26   307 3343 1403 20  1
> 79  0  0
> 2  0 863492  40688  16528 1547616    0    0    26   304 3750 1728 24  1 75
>  0  0
>  3  0 863492  40192  16528 1547876    0    0    42   378 3955 1591 30  1
> 69  0  0
> 4  0 863736  43972  16516 1542336    0   49    11   147 4820 1635 38  3 58
>  0  0
>  1  0 863736  45156  16516 1542728    0    0    13  1830 5120 2471 27  4
> 68  0  0
> 1  0 863736  44412  16516 1543056    0    0    39   243 4635 1875 32  6 61
>  1  0
>  2  0 863736  45428  16516 1543396    0    0    13  1670 5408 2048 39  1
> 60  0  0*
>
>
>    - Does await > 0 imply anything significant?
>    - Can I conclude an average R:W ratio of 1:18?
>
>
> *# iostat -kx 5*
> *
> Linux 3.6.5-x86_64-linode28 (ca-db-003)         01/31/2013      _x86_64_
>        (4 CPU)
>
> avg-cpu:  %user   %nice %system %iowait  %steal   %idle
>             6.46    0.00    0.20     0.13    0.13    93.08
>
> Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz
> avgqu-sz   await  svctm  %util
> xvda              0.02     0.73    0.04    0.25     0.93     3.90    33.23
>     0.00    3.03   0.37   0.01
> xvdb              0.03     0.03    0.01    0.03     0.14     0.25    21.01
>     0.00    2.16   1.39   0.01
> xvdc              0.00     0.00    1.13   30.17     5.24   282.11    18.36
>     0.02    0.74   0.29   0.90
> dm-0              0.00     0.00    1.13   23.58     5.24   282.11    23.26
>     0.02    0.94   0.37   0.91
>
> avg-cpu:  %user   %nice %system %iowait  %steal   %idle
>          28.56    0.00    0.86    0.15    0.15   70.27
>
> Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz
> avgqu-sz   await  svctm  %util
> xvda              0.00     0.00    0.00    0.00     0.00     0.00     0.00
>     0.00    0.00   0.00   0.00
> xvdb              0.00     0.00    0.00    0.00     0.00     0.00     0.00
>     0.00    0.00   0.00   0.00
> xvdc              0.00     0.00    8.80  107.20    35.20   368.80     6.97
>     0.02    0.17   0.17   1.94
> dm-0              0.00     0.00    8.80   73.80    35.20   368.80     9.78
>     0.02    0.24   0.24   1.96
>
> avg-cpu:  %user   %nice %system %iowait  %steal   %idle
>           30.66    0.00    0.81    0.05    0.10   68.38
>
> Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz
> avgqu-sz   await  svctm  %util
> xvda              0.00     1.40    0.00    0.40     0.00     7.20    36.00
>     0.00    0.50   0.50   0.02
> xvdb              0.00     0.00    0.00    0.00     0.00     0.00     0.00
>     0.00    0.00   0.00   0.00
> xvdc              0.00     0.00    4.00   54.80    16.00   124.70     4.79
>     0.01    0.12   0.12   0.68
> dm-0              0.00     0.00    4.00   37.20    16.00   124.70     6.83
>     0.01    0.17   0.17   0.72*
>
> Thanks to everyone in advance!
>
> --
> Vimal
>  _______________________________________________
> azPHP mailing list
> azPHP at list.azphp.org
> http://list.azphp.org/mailman/listinfo/azphp_list.azphp.org
>
>
>
> _______________________________________________
> azPHP mailing list
> azPHP at list.azphp.org
> http://list.azphp.org/mailman/listinfo/azphp_list.azphp.org
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.phxlinux.org/pipermail/plug-discuss/attachments/20130201/d804e29a/attachment.html>


More information about the PLUG-discuss mailing list