4 ms·

Yes and no. Fusion-io removes the io bottleneck but ALTER TABLE is still single threaded. So you're looking at a CPU bottleneck where only 1 core can be used.

by jzawodn 16y ago

Yes and no. Fusion-io removes the io bottleneck but ALTER TABLE is still single threaded. So you're looking at a CPU bottleneck where only 1 core can be used. We just did a lot of ALTER TABLE work on one of our clusters WITH fusion-io drives (2 in a RAID-0) for a 360GB table and it still took ~8 hours to finish.

ck2 16y ago
So what was the CPU on that bottleneck, out of curiosity? If single core is the problem, throw a massive clock rate at it? The i5-680 is out this month and is 3.6ghz native ("turbo" near 4ghz) should do 5ghz with good air cooling (it's 32nm) and then you could use ECC memory to be certain in a mission-critical environment?
- jzawodn 16y ago
  This was... lemme see. less /proc/cpuinfo Intel(R) Xeon(R) CPU X5570 @ 2.93GHz So it was a pretty high-end Nehalem proc already. And, yeah, we do have ECC RAM in the box, of course. Plus InnoDB does page-level checksumming to guard against corruption too. But still, it was ~8 hours or so. Using all 8 cores on the box would have brought it down to ~1 hour if it could have been perfectly parallelized (and much of it could have been, I bet).
  - ck2 16y ago
    Ah okay, so even with the fastest clocks available today, the best you could hope for is cutting the 8 hours to 4, or one month to two weeks. As far as the parallel cores it sounds like you already have thought about possible "end runs" around MySQL's "transaction safe" ALTER (which is probably why it's single threaded). InnoDB supposedly has a few performance improvements for ALTER over MyISAM so you are already taking advantage of that.
- soundsop 16y ago
  Memory ECC won't help if computations are incorrect.
  - pyre 16y ago
    Good computations won't help if your memory is corrupt.
    - reitzensteinm 16y ago
      That's not what soundsop was saying - he was just pointing out the obivous error in the suggestion to overclock the CPU, and then use ECC to be safe. The ECC, though a good idea by itself, will not help with any errors introduced by the overclock.
ck2 16y ago
I just had another thought in case you check back on this thread someday. Do you know if FusionIO drives internally run as Raid5? Not your external Raid0, but since flash chips are so relatively small in capacity right now, they must using an array internally, maybe Raid5? If so, Raid5 has a severe performance penalty when very small amount of data are being written (smaller than a single stripe) because it has to then go update the parity across the volume on every write and that defeats any caching. We already know ALTER itself is slow but I wonder if your storage is working against you in this case because ALTER does a single write for every row in the column when rebuilding the table...?

by jzawodn 16y ago

ck2 16y ago

So what was the CPU on that bottleneck, out of curiosity? If single core is the problem, throw a massive clock rate at it? The i5-680 is out this month and is 3.6ghz native ("turbo" near 4ghz) should do 5ghz with good air cooling (it's 32nm) and then you could use ECC memory to be certain in a mission-critical environment?

jzawodn 16y ago
This was... lemme see. less /proc/cpuinfo Intel(R) Xeon(R) CPU X5570 @ 2.93GHz So it was a pretty high-end Nehalem proc already. And, yeah, we do have ECC RAM in the box, of course. Plus InnoDB does page-level checksumming to guard against corruption too. But still, it was ~8 hours or so. Using all 8 cores on the box would have brought it down to ~1 hour if it could have been perfectly parallelized (and much of it could have been, I bet).
- ck2 16y ago
  Ah okay, so even with the fastest clocks available today, the best you could hope for is cutting the 8 hours to 4, or one month to two weeks. As far as the parallel cores it sounds like you already have thought about possible "end runs" around MySQL's "transaction safe" ALTER (which is probably why it's single threaded). InnoDB supposedly has a few performance improvements for ALTER over MyISAM so you are already taking advantage of that.
soundsop 16y ago
Memory ECC won't help if computations are incorrect.
- pyre 16y ago
  Good computations won't help if your memory is corrupt.
  - reitzensteinm 16y ago
    That's not what soundsop was saying - he was just pointing out the obivous error in the suggestion to overclock the CPU, and then use ECC to be safe. The ECC, though a good idea by itself, will not help with any errors introduced by the overclock.

ck2 16y ago

I just had another thought in case you check back on this thread someday. Do you know if FusionIO drives internally run as Raid5? Not your external Raid0, but since flash chips are so relatively small in capacity right now, they must using an array internally, maybe Raid5? If so, Raid5 has a severe performance penalty when very small amount of data are being written (smaller than a single stripe) because it has to then go update the parity across the volume on every write and that defeats any caching. We already know ALTER itself is slow but I wonder if your storage is working against you in this case because ALTER does a single write for every row in the column when rebuilding the table...?