Note: "permalinks" may not be as permanent as we would like,
direct links of old sources may well be a few messages off.
On Dec 15, 2007 1:45 PM, Florian Haas <florian.haas at linbit.com> wrote: > On Saturday 15 December 2007 20:32:51 Art Age Software wrote: > > It seems that there is a lot of uncertainty out there regarding how to > > best configure MySQL on DRBD. Some sort of "best practices" document > > with configuration settings for DRBD and MySQL would be a huge help in > > this regard. I realize there are a lot of variables to take into > > consideration. But having some solid tested examples to start from > > would be quite helpful. Anyone up to the task? :) > > That would be me, I suppose. :-) > > About the performance issues you mentioned. The performance tests I've > conducted were run using mysqlslap, and backed up using some lower-level > latency tests, but test results were similar initially. Going back to the > test results you've reported: > > > Old Servers (2 single-core CPUs): > > Database on Local Storage > > insert test: 212.00 sec. > > Database on DRBD Device > > insert test: 998.00 sec. > > ---------------------------- > > DRBD Overhead: 786 sec. = 370% > > Eliminate competition for CPU resources between DRBD an MySQL, by pinning > DRBD's kernel threads to one CPU core and mysqld on the other, like so > (assuming drbd0 is your device you're running your MySQL databases on): > > for thread in worker asender receiver; do > taskset -p 0x01 $(pidof drbd0_$thread) > done > taskset -p 0x02 $(pidof mysqld) > > If you're unfamiliar with taskset, be sure to read its man page to understand > what those CPU affinity masks mean. > > Do that, then re-run your tests (on your old server) and share your results. > > That tweak alone reduced my DRBD overhead for mysqlslap from 224% to 57% on > the test system I have at my disposal. > > > New Servers (2 quad-core CPUs): > > Database on Local Storage > > insert test: 164.00 sec. (22% better than old servers) > > Database on DRBD Device > > insert test: 1137.00 sec. (14% *worse* than old servers) > > ---------------------------- > > DRBD Overhead: 973 sec. = 590% > > Yup, now given the CPU resource competition issues described earlier, these > are probably being exacerbated by the fact that there are now 8 logical > CPUs (cores) available, versus 2 on your old server. You can do one of two > things here: > > 1. Pin the DRBD threads to one core, and allocate the others to MySQL. > 2. Pin the DRBD threads to one core, and allocate only a few of the others to > MySQL. I've heard some SMP issues are present in InnoDB; more cores doesn't > necessarily mean better performance. > > There are other settings that can be tweaked (I can come down to about 26% > overhead on my system, which is about as low as the network will let me), but > I'd be interested to learn whether you can confirm my results with regard to > CPU affinity tweaking. > > Cheers, > Florian Thank you very much for the suggestions, Florian. I will experiment with the CPU affinity today and report my findings back to the list. Unfortunately, I can only do so on the new servers as the old ones are currently running in production and I can't risk breaking them. :) Sam