At 02:21 PM 4/13/2009, Baron Schwartz wrote:
> > MySQL does not scale well beyond 4 processors, at least not like PostgreSql
> > does. MySQL seems to hit a plateau rather quickly. If XtraDb's modified
> > Innodb plugin scales better, then fine. But I haven't seen any benchmarks
> > showing the speed improvements relative to the number of processors
> used and
> > is something I'd really like to see.
>You can find such benchmarks on our blog. And Mark Callaghan and
>maybe some others have benchmarked it too. Of course, we would love
>to see more independent benchmarks. Vadim considers that we've solved
>scalability problems in XtraDB up to 16 cores, and I agree, though I
>am less of an expert than he is.
I did see one benchmark but more are definitely needed.
>However, many problems in MySQL
>itself remain even if all the storage engines are fixed.
Well Sun should throw some more logs on the fire and get it done.
> >> As others said, the major bottlenecks are likely to be internal (to the
> >> DB)
> >> locking and disk access speed.
> > Of course. When it comes to MySQL, I would invest more money into more
> > memory and fast SSD drives rather than more CPU's. You'll get a bigger bang
> > for the buck. :)
>None of MySQL's current storage engines takes advantage of a lot of
>memory or fast SSD drives either, in my opinion. Not like they could,
>Have you seen our (or Jignesh Shah's, or Matt Yonkovit's) benchmarks
>and discussion on SSD drives? When you disable the (unsafe,
>non-battery-backed) write cache, suddenly they aren't so fast anymore.
If you're talking about the Flash SSD's, it's not the RAM that is causing
the problem. These drives will slow down as the drive fills up with data.
There is a lot of overhead needed to recover deleted space when it needs to
write another page. These drives start out really fast, but like I said,
they slow down as the drive fills up with data and the only way to get it
back to its pristine speed is to do a secure erase on the drive which means
you lose all your data. Windows 7 will have a Trim command that will help
to minimize these delays but not eliminate them entirely. Corsair has
announced a new Flash SSD that looks interesting
http://hothardware.com/News/Corsair-Readying-Ultra-Fast-256GB-SSD/ but of
course is expensive.
Now the SSD that I would like to have is the Hyperdrive 5 from
http://www.hyperossystems.co.uk/. It is a DDR SSD and each drive has slots
for 8 DIMM's which means it can hold up to 32GB (64GB if you can find 8GB
DDR2's) per drive. They can be striped to give you a heck of a lot of drive
space using RAID. And yes, they are faster than spit and will never wear out.
So if I wanted to speed up my MySQL database, I'd definitely be buying
quite a few of these. (Maybe later this year when I've got some cache to
spare<g>) Are these drives expensive? Darn right. Are they worth it? Well,
they say time is money and if you need the results as fast as possible,
then load 'em on up. I find most databases are disk bound and not CPU bound
so switching to ram drives may be the best bang for the buck. :-)