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@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@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@list.azphp.org
> http://list.azphp.org/mailman/listinfo/azphp_list.azphp.org
>
>
>
> _______________________________________________
> azPHP mailing list
> azPHP@list.azphp.org
> http://list.azphp.org/mailman/listinfo/azphp_list.azphp.org
>
>
---------------------------------------------------
PLUG-discuss mailing list -
PLUG-discuss@lists.phxlinux.org
To subscribe, unsubscribe, or to change your mail settings:
http://lists.phxlinux.org/mailman/listinfo/plug-discuss
(text/plain)