Date: Tue, 4 Dec 2007 14:08:10 +0100 From: Gergely CZUCZY <phoemix@harmless.hu> To: Jeff Roberson <jroberson@chesapeake.net> Cc: freebsd-performance@freebsd.org Subject: Re: mysql scaling questions Message-ID: <20071204130810.GA77186@harmless.hu> In-Reply-To: <20071201122122.S884@192.168.1.107> References: <20071129101729.GA57985@harmless.hu> <20071130143023.I884@192.168.1.107> <20071201163334.GA21709@harmless.hu> <200712012055.lB1Kt5IQ005728@lava.sentex.ca> <20071201205609.GA54238@harmless.hu> <200712012108.lB1L8qAd005766@lava.sentex.ca> <20071201211012.GA55519@harmless.hu> <20071201122122.S884@192.168.1.107>
next in thread | previous in thread | raw e-mail | index | archive | help
--nFreZHaLTZJo0R7j Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Sat, Dec 01, 2007 at 12:22:34PM -1000, Jeff Roberson wrote: > On Sat, 1 Dec 2007, Gergely CZUCZY wrote: >=20 > >On Sat, Dec 01, 2007 at 04:06:55PM -0500, Mike Tancsa wrote: > >>At 03:56 PM 12/1/2007, Gergely CZUCZY wrote: > >>>I don't quite understand the question. It's the very same box, with > >>>a dualboot configuration. > >> > >>Fire up the 3ware controller's RAID management software and make sure t= he same write caching strategy is set for FreeBSD and Linux. The > >>driver my default to different values. > >> > >>i.e. under "controller settings" make sure "write cache" and "queuing" = are the same values for linux and freebsd. > >Let's get back to this on monday. I'm at home now, and the > >box is at me workplace, still running a test (i can't reboot it). >=20 > Also, can you verify with a read-only test to see where it's at? I have = not tested writes with that many threads. I notice mysql goes much faster= =20 > with a fresh table too. So can you blow away and recreate the sysbench t= ables and then do read-only? If that is much slower we'll know there is so= me=20 > configuration problem or similar. It will all be available here: http://phoemix.harmless.hu/mysql/ Some notes. With the ZFS tests, mysql seems to be a lot in zfs(&: state in top, and vmstate shows lots of the CPU spent in system: r b w avm fre flt re pi po fr sr da0 da1 in sy cs us= sy id (32 threads) 5 0 0 2904868 8563836 7259 0 0 0 7783 0 0 0 1009 33097 24196= 17 24 59 32 0 0 2921252 8565732 7445 0 0 0 7810 0 0 3 1579 48135 25277= 19 80 1 6 0 0 3167012 8563304 7731 0 0 0 7789 0 0 0 1581 49608 24088= 20 79 1 7 0 0 2861860 8564460 7226 0 0 0 7427 0 0 0 1547 47430 25276= 17 82 1 7 0 0 2968356 8563624 7591 0 0 0 7752 0 2 0 1588 48899 23958= 20 80 1 32 0 0 2984740 8562660 7495 0 0 0 7914 0 0 8 1583 48698 25508= 17 82 1 26 0 0 3040036 8563708 6852 0 0 0 7035 0 0 0 1446 44358 25176= 18 82 1 (64 threads) 5 0 0 3646244 8549136 6322 0 0 0 6552 0 0 0 1368 41438 30397= 17 83 0 47 0 0 3908388 8547924 6425 0 0 0 6525 0 0 0 1395 41779 33059= 18 81 1 65 0 0 3748644 8548356 6507 0 0 0 6689 0 0 0 1426 42855 29754= 18 82 0 57 0 0 3785508 8549040 6452 0 0 0 6583 0 0 0 1390 42103 30140= 18 81 1 8 0 0 4180772 8547492 6480 0 0 0 6604 0 0 0 1426 42261 30397= 15 84 1 So on. "zpool iostat" shows no activty on the zm pool i have, only occasionally 1-= 3K in 5sec intervals, that's nothing. So I think everything is returned from the fscac= he/zfs cache. I've increased vm.kmem_size a bit to fit for zfs: vm.kmem_size: 1073741824 The test hasn't yet finished, but it still seems to have a very poor perfor= mance: 1 2 4 8 16 32 64 threads 436.83 1038.33 879.85 826.63 757.92 969.31 845.84 qps (this is the read-only, keep in mind) With UFS: 1926.87 2172.59 2093.41 2478.06 2577.58 2543.55 2341.46 2166.81 2026.50 189= 1.09 1753.52 1647.64 and the linux-2.6.19.2+mysql-5.0.41+tcmalloc: 3431.56 4135.05 4984.12 5487.01 5448.19 5354.64 5226.64 5113.96 5011.94 470= 5.62 4362.06 3996.42 vmstat when running the test on UFS: procs memory page disks faults cpu r b w avm fre flt re pi po fr sr da0 da1 in sy cs us= sy id (8 threads) 7 0 0 2385660 9399000 19128 0 0 0 19601 0 0 0 3235 123806 4349= 0 37 61 2 8 0 0 2461436 9399180 18975 0 0 0 19468 0 0 0 3213 122856 5138= 9 39 60 1 6 0 0 2410236 9399508 19141 0 0 0 19706 0 0 0 3230 123783 5035= 3 38 61 2 5 0 0 2348796 9399744 19273 0 0 0 19817 0 0 0 3272 124558 5128= 1 38 60 2 (16 threads) 14 0 2 2664228 9393172 19988 0 0 0 20462 0 0 0 3148 123556 1747= 5 35 65 0 9 0 0 2666276 9393004 20146 0 0 0 20661 0 0 0 3231 125252 1734= 0 37 63 0 16 0 0 2596644 9394436 20157 0 0 0 20704 0 0 0 3204 124366 1742= 1 38 62 0 9 0 0 2590500 9394556 19712 0 0 0 20197 0 0 0 3113 122209 1761= 0 36 64 0 (32 threads) 30 0 0 2930468 9386688 19357 0 0 0 19919 0 0 0 3096 120375 1828= 5 39 61 0 26 0 0 2760484 9386848 19372 0 0 0 19913 0 0 0 3112 121284 1802= 0 39 60 0 10 0 0 2908964 9385772 19238 0 0 0 19672 0 1 0 3019 119013 1803= 7 35 64 0 17 0 0 2981668 9384308 19265 0 0 0 19715 0 0 0 3088 120462 1804= 0 39 61 0 (64 threads) 43 1 0 3662632 9372396 18201 0 0 0 18612 0 0 0 2864 113344 2006= 3 38 62 0 18 0 0 4131624 9372004 17703 0 0 0 18172 0 0 0 2808 110922 2134= 8 36 64 0 58 0 0 3562280 9374428 18016 0 0 0 18593 0 0 0 2840 111615 2107= 8 36 64 0 58 0 0 3990312 9375276 17834 0 0 0 18361 0 0 0 2886 112559 2066= 2 38 61 0 There was around 20% of CPU time spent in system state when mysql was runni= ng off a ZFS filesystem, then a UFS one. And also, there were more context switche= s, but less system caalls and interrupts. So, the result is basically the same as in the RW case. Where should I start investigating this issue? May I try again with the 4BSD scheduler? Currentl as you can see, I'm using the new ULE one. Sincerely, Gergely Czuczy mailto: gergely.czuczy@harmless.hu --=20 Weenies test. Geniuses solve problems that arise. --nFreZHaLTZJo0R7j Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.4 (FreeBSD) owG1WE2PG0kZDqw40NIKrRCHPfEyEiSr2D311dXVRpMQks0qkBVLJmFFOECPXR63 xu72drfHcSSOCKTlgLghJMSB0yIEB34AEhJHfgDcET9gOSLxvNXt+egs3JjR2ON2 1VNPPfXW+z5VP3v9tRuffuOvf/jj927/9Oe//NTvvvCXk9urTduWp+NVXp8X5VgK IcdpKp0d23GSySQT6TxVNjVy5t959u3J/apsfdmOn+7WfkKtf9Eerpd5UX6Vpou8 bnx7tGnnYxft2z0omnXVFG1RlRMqymVR+ovvntZ52cx9PX67nFazojyd0AebqvWz 8bouyjY/Wfoo+lZJx3k7ogd+SkKOSAmRUt6SVBOlJtq89y4xazGib/j5nJ5UJ75u qpK2NZAm0R3aA8gAwd1H9I6vT/1yR/efP7v//LsXbY+UQIc7/21IYSbCTpKEhxQJ D/lucebpaV5Om/xywDt37qGtniSW0FKqQ3n4P0dFhzuPaFaVN1vMv2g9bcoZJtHm 5YzahcdD37CAMT1qbzbh0bmvd9TkK08n1YsRbYt20QHlNNvky5OqamlalfPidFPn oW/4Orw8LGoMsQ44epvjA1q2dbVc+hrwT+49ekCrvMxP/QqrRE01b0MrprPKMeFm g0/tUYT+gcK2ZtLTfLrAGlLTYkR/uqOiIcQDzauaHtbef/34QYB4XJSbFzE9XfjA ZlYXmAytdjTz83yzbKmtaFbMERc8+nm+xOyvsC9iH3cC0cElbx6pxejNwRWKB5fE /EEY+wBSbtDsgI4inlK7n0I3TOC6ZH6h9RysT5pZGPyxZ+lPMZ+TfHrGHNsFJohA W1XlLN9hbW6uOEgWFeDKajuifvm4NxaJ5cDXLFdVn2HPTP0IWhXLJdWbsmTlcuyn pqVbBShzMNQ+rGPRvhXvY/PesqlG/DXtqg1HQTHfhdVH59rns3FVIsACDCg2HqMt ICQwbvLwd4ke0SI/95h/CWhu6GfdAjYdTrtglnkJlAUjNjH3QeNi6rFKzQdLOq3Q eLWZLmieo399FHXkeh6QrQEOb1+QqND/uLqgfLKstpRv812Qp/ZTjNH2C7FrTnwJ VIQWd272CpbYHJez4znMO5pFT6MBKGJg629CzTOIz7141ojA6iha+Y7ftQ1B67rC ICvCkjfFqljmdRw9ajEJYOT4O0HEn+d4zvNguEm0aNv15PBwvaj8qngRI+GtQLOJ F5vDoMxhFB13y99yzEbvd4J6ev7wOGjdjHoJsTKrhpeIR6Elr3JJL+fNra9MEBSs CD631TpEUXS+6p41i2rbcGsE3jwA33/vGTVr3ihoDwFbv5pEVNMJbYl/8vMVv2FJ +BWbi/i/dYG/qvuCqKlplgv8STwADJ7s8DJtaNMcRfyhmEW3tNoHxFsRJSTwqzJh nHXkEqudtkSpSjL0FJd/aer0tSdI1RlpLbKUlJGZxQCS/6UkizBEB6ukShTDJime UWpMMoB18son0iSTNCPjpE4IXdOUYTNy+FJGZAOsljYVMsBifMPctHyF7XX+MnGS TGaFA0XhHGCVIAzFsGnH1llUS8GwxlgGUcoOYI1KB7AmJZMaLQLbXgSnrsJm1mlU EGZrFbNFNR6yhUjdJ7Vn6yCCyzJSOkt6tp0IF9o6jBvYKhvYmmyobSbNlSeOYTVg bQYRkkS4q2xVr60wQuiObQqxyLoLbntYoa8PJCEXGaMThpWdCK6HvWXNK9GmrYEQ BmOYTHK0Wa0GY9hkMCraQRFptANFnaU9dYSkiEwntM6E046D2KQZC22NSoawgycS 6gI2TUMoJ1lPXTJ127NNoVjHNqwjQEQ6gLXDaDOQ0yiXIIizNDGXiogo6dmmjpcg iADNme1QaJsMtxymCFgpNESQRlxli+VlWCOdSFMVRDCZYlgnhmyF+SS2yspLbRNy hmGRBok9x8HLdVUtqag4fx30+ausKJ+2xXm74/LJWezlirp2oTaNKBSxajrNGyRq 5OIdyfFRpL/J2Slp/DSCO/Q1ajbyKReCmwzasvuIudY84tpcnpFnlxQecyWofbup S89lvVqFYecNrAHbmENk3s4moATcPOfky3WpQePzVXy28qvvN8VLztQnRSis86Lz Neg3ia42mSDBpVh76ZSJInicrhYv8oYL+o7tUFEWzcLPRnSy4dree4CLghCqc94Z PIhS09rXGApVDDYTNUhS/6O6N9O9ue5N2u5dh29t+LLfSJHRNkZoIA5crDW5NIsd FkzZ2GqklzTGwmc2i5EVnUlirOQH6ya6FXxO0dnOiyI8ojPv17weq6KcvdVVumcP j8EvA6BDSpepilEOlMh0bCTyZ+piYbHX0zQOe97omCNdGxkjEyhp0Q/tBPonCC+H bSVjVAuZJmipMDeTxtZEe1scjNpYxTaWWaxuh8I6TmKB0W630xXipppOIm20jLEB DWpDLLBtkQFjlAHsyzQWEu/GAYASnRigU6JYELxLqePMUiKkjDODdI2Nk8RWEXRU PBOdZTY2KurLMzut8sLLtfulR4gHXdhvoKSGH0RLVe+6/9fw2fQJP7OiOeuaB1fc 95yuN/+f8u6u5Nu+Amlkc5SIDPPE+Qr1VCp3PQOgMF6vSVohyUv0hDxGG6wgpyxC iiC1zzXKWCRkG3CRdnih00F6zYx1A1yJwFVIjVgQqVGCkHkzslfrO+yEUD0up0jw NXKAmwo75CuYL7uUBAUKSYZQK3q+vcvRiJSsw4UTAYpKBwk2czId4CKZSmUSBHoC 2RDJAVcw7i1s0guxUWsxBKEYI5U6HkRL7ptl7roESqD8XR9EGsfkE4giU4MEnBDk RwFCvaCsI2+thcEIuAIJXKEA2AGuta8soiR2YLznUm32ixiqpuzFTjLL5Q24hhcT uEk6wE0HBUNjCiyKtoGv2ouirvJNMj5ZB9wwryyVaoCLZwMduuBQIVdYGfjakPzE dd+qRW+C4Fcsi+1QgzlS9IA85JfXazMcK9gooRGrSO4uiJ2FSBF7FwShYYhNwHUm 4KbqFVw9JM+RghiBNk6oQL6LbIi95ytcZgNukobgwA4b7sSLkWTPFxOQMhOsjRP6 KEpDcARR5N5jOiTdoAOsKPNVdrgTU5kMdGDBumAErtnzDTpcs20GA3MHBCCcGrEW mjV0apA0YJ+HawzrBPJaG45YYffbMkSK3FsWGHplAi5HNtyYGGxLJ9MhLs9SigzG ERkF5N1lpCQdLswaNiJHIDY7diQmKe0AN8n0ABdWTEppIRZ8VvqJuMiiWgYd2PKD r9MDQ4UzlBzgOjTEVgx11EIHtU9PIrgLZPttjnNyXW1QFJX4Mp8L+UzYFjiGDg6G /cEylKnuFMp9Q8E6ilCyqvk8ysNRdV7gWBv6jLrzd841DKXMx3QPA+XhFqI7Y2/5 BeWsu0XyL2BotkULK3UUNZ3H4SNy1FOY5ijL3cE+eLh6s275pAzDOOodRsN3QPAb J3B/02D+Lm5pQLfoHOOT9wHVeD5jBxZwlpvlDM4Pc6x5zud8ZXaat10tDg6m2fi7 0bv5jv0hqm9+mgNsuz+iG76fasB7tln6+i7d39R8AbXkQfnigi8wYNJG4Z5n0wA3 4m6l39Kzx28HbTALGEfQgT+Koovbvpeb6csd7FuxbKsJnXaP42l4/LUrdwhRNB7z RcX73peFb4J9iOkdfNg0nm8ylnCG/a1F012A5HXBIvzk7mufucG3s/ur3Tc+/a9/ 3/j1R3/6+4dvfun2n9XzJ8c//IH42z+Pv/P6jV+JN3780Zu//9Hnf/vFXzz++ON/ fPib9Wc/9x8= =VNjA -----END PGP SIGNATURE----- --nFreZHaLTZJo0R7j--
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?20071204130810.GA77186>