Looking at new server for PC: SSD ok for MySQL?

I have more experience with MS SQL than MySQL, however, I think a lot of the best practices are the same.

Ideally you should have two seperate arrays for your database... one for the database itself and one for the log file. Those arrays should have seperate physical hardware... not just two partitions on one drive... that defeats the purpose. The log can just be a simple two drive RAID 1 array, but the more drives for the data array the better. 8 72G drives in a RAID 10 array will be much faster than 2 300G drives in a RAID 1 array and still provide about the same amount of space. Frankly, though, SQL data drives should be sized based on the number of drives in the array rather than the amount of space needed. Most likely to get enough drives for good performance you'll have way more space than you need.

As far as SSD goes, I'd probably stay away from it. You're likely going to have a lot of data writes and SSD's arent great at writes unless you go with a really expensive server level drive. You'd be better off with more physical drives.

 

Last server I built, instead of the traditional HP & SCSI drives, went with IBM dual cores & SATA2 tech.

Two drives of 1G mirrored, and 5x 120G SSD's in a Raid-5 array.

Typically the database for PriusChat shouldn't be larger than 100g, so a three disk array instead of five would be sufficient.

I would never use SSD's for a database app w/o a Raid-5 array. A Raid-10 would be too costly with SSD's, but I build Raid-10's with 8 to 10 regular disks.

When I build Raid-10's it's because I have a 5U server with plenty of lower capacity regular drives doing nothing, and use it for storing temp files.

Two hybrid SSD's might be interesting, to keep costs down, in a Raid-1 (mirror). Faster reads on the most accessed files.

Remember that disk-based caching & the OS do not improve databasing speeds. Raw RAM is what you want in huge quantity, and fast I/O.

 

Oh, stay away from virtuals, molasses-slow. Dedicated hardware.

PriusChat will survive should a 1/10,000 chance in five years happen - bad hardware. At most PC will be down for a day or so. So no need for redudancy other than the disks themselves.

You could use an ASUS laptop with two drive bays, remove the DVD for an extra bay, it fits in 1U space. However, I prefer "true" servers, and recently bought & configured four IBM Lenovo RD 240, 2U, 7 SATA bays.

Is PC hosted in a co-loc facility?

 

I dont have experience with SSD and MySQL but I am running two Intel X-25E drives in a raid 0 for my desktop OS.

I do know that SSD's are great for databases because of the high I/O performance. Dont believe anyone if they say the drive will die quick if you write to them a lot. At least good SLC drives wont die quick like the Intel drives I have. If I remember correctly Intel said the SLC drive will last 100GB of writes per day for 5 years. That was a minimum I think. Thats a lot of writing. Even if that holds true and your database is doing that much writing you'll be buying new drives in 5 years which will be cheaper and faster. Good SSD's are worth the investment when it comes to performance/cost.

 

My experience is decades old and with Oracle DB. As many spindles as you can get is good (120 1 gig drives was my largest, it way out performed 5 36 gig drives) Nonvolatile RAM in a RAID controller really speed up writes. <G> It does not take much, even 1 meg NVRAM is fine. Invest in RAM and CPU cache, they really help!

 

It's kind of counter intuitive thinking because most people think of using RAID to increase disk space, but it can also be used to increase disk throughput... and because SQL can be very disk intensive adding multiple spindles (drives) to increase the speed is a very good idea.

At a bare minimum I'd really like to see 6 drives in three RAID 1 arrays... one for the OS, one for the SQL data and one for the SQL log. It's very important to put the log on it's own drive because all SQL does is write data to the log... if it's on it's own array the drive heads can stay at the end of the log and keep writing data very quickly. If you try to share the log drive with anything else then the heads will be moving all over the drive and significanly slow things down.

Keeping the OS and the SQL data seperate will also help increase throughput.

 

Slashdot posts its setup. May be worth seeing how they do things.
As for speed, I tend to think most of the slowdowns I see are from outside servers involved in data mining and advertising.

 

I have an Intel X-25M G2 160 gig drive as my boot and programs drive. Sorry, I'm a database dummy myself but I can say I love my SSD. Since there are no spinning disks or heads to move, they have super quick access times and virtually no latency.

The Intel X25-M drives (I would think they're using the newer G2 drives) listed on at https://www.wiredtree.com/orders/order_wrapper.php?plan_group=quad_core are not SLC, they're MLC. One needs to go with the a very expensive drive like the X25-E to get SLC.

AnandTech - Intel X25-M SSD: Intel Delivers One of the World's Fastest Drives talks about write cycles. I don't know how long Danny wants to keep the proposed system, but I'd be a little leary of using a MLC SSD as a database drive due to the # of writes it'll receive (but again, I'm a database dummy). For MLC SSDs like the X25-M G2, there's some confusion about the 100 GB/day vs. 20 GB/day, IIRC. http://download.intel.com/design/flash/nand/mainstream/Specification322296.pdf states
"Minimum Useful Life
The drive will have a minimum of 5 years of useful life under typical client workloads with up to 20 GB of host writes per day."

One can view how much data has been written to the drive via http://downloadcenter.intel.com/Detail_Desc.aspx?agr=Y&DwnldID=18455.

SSDs, specifically the Intels aren't so great at sequential writes but BLOW the doors off of hard drives doing random writes (and random reads). Glance thru the graphs starting at http://www.anandtech.com/show/2865/5.

 

You're better off with more RAM than using an SSD, because MySQL can cache results in memory -- I'm guessing you won't see much performance gain using SSD.

And as stated before, with lots of writes, you want to use a regular HD. MySQL will queue the writes, and I believe it is simply a config setting on how much memory to use.

I concur above, set the Apache log to one drive, MySQL log to another drive, data to another drive, etc.

Put all the static files on another drive. You may even want to put the static files on a virtual (shared) server or on a 2nd dedicated server (divide & conquer, requires much less in CPU/RAM/HD).

 

While informally Googling around for benchmarks, I found these might interest Danny.

intel ssd Search Results intel ssd - MySQL Performance Blog
AnandTech - MySQL and the power of Intel SSDs
MySQL :: MySQL on SSD
AnandTech - SSD versus Enterprise SAS and SATA disks
AnandTech - SSD versus Enterprise SAS and SATA disks (glance thru the graphs)

Again, I'm a database dummy. However, I will agree the statement at AnandTech - The SSD Relapse: Understanding and Choosing the Best SSD (at least for non-server use/non-DB use):

Many people who poo-poo SSDs don't have one or have/had a crappy one (like the early JMicron ones that were lousy). I was for a long time wary of SSDs (esp. due to the crap early SSDs) until TRIM support came out for the Intel X25-M G2 line. Now, I'm a believer.

I can have more than enough physical RAM yet my mechanical hard drive can be a horrendous bottleneck w/its thrashing. (At least for non-server use) Sure, don't skimp on RAM to get a SSD, but that doesn't mean having a ton of RAM == having a SSD.

I wish my laptop had 2 drive bays so I could have a SSD boot + programs drive and another for data.

 

There's no doubt SSD will enhance this site, but there are many other tweaks and optimizations that can be done.

Is vBB a beast these days? I seem to recall many sites just as popular as this one running years ago on the hardware of years ago just fine, and I'm not even talking about any kind of extreme setup. People were on a budget back then also, you got a lot less for the money.

One wrong setting can cause thrashing and reduce throughput to 10% of what can be achieved.

 

Wow, 260$ per month. (Danny's link) (however transfer is 1000G/month)

Here in Montreal, where dedicated co-loc is not cheap (compared to Europe), if you buy your own hardware, a 1U costs 95$ / month for 100G (not lowercase g which is giga-bits).

Over three years that price difference would buy some sweet server hardware.

Maybe you should partner up with a LAMP geek in your hometown?

I paid under 1K$ for the Lenovo's RD240 with 1 Xeon CPU 8G ram no disks. One such cpu gives 4 'cores' in a 32-bit OS or 2 'cores' in a 64-bit.

So a LAMP & two cpu's would give you 8 concurrent threads working.

If you want a MySQL data app to fly, give it RAM. Look at the .idx file and make the total RAM that size + 4G, so that all the indexes are in ram.

Write speed doesn't need to be fast for PC. Two-cpu Xeon's, max ram, ideally a 5 disk array with a hotspare, should be under 2k$.

We're talking PriusChat here - tiny in comparison to systems I oversee. Virtuals will kill performance, and raw I/O. Perceived performance is being hosted in a crappy Co-Loc that has too many active servers behind his main pipe.

My personal experience so far on SSD's in servers - you MUST go with 3 or more in Raid, in order to compensate the lousy Write-back in the very same files, aka databasing. Writes to new files are fast.
Read speeds are nothing short of spectacular, and Raid-ed it's even better.
​

From an article :

Final Thoughts on SSD and MySQL AKA Battleship Spinn3r

February 22, 2008 in clustering, linux, mysql, SSD


I need to give it a bit more thought but it looks like we’re going forward with deploying Spinn3r on SSD. Specifically, machines with 3 SSDs on Linux software RAID.


​

 

I concur that PC could run perfectly with 3-to-5yr old hardware. So anything modern should do the trick as-is. KISS principle.

One thing to consider is managed hardware, as per Danny's link, or simply renting 1U space somewhere with a custom build.

For a no-brainer solution, managed -but not a virtual- should be fine. Competition is fierce, so I wouldn't worry much price-wise.
(As per my previous post)

Kinda like a Prius is managed hardware, and a regular GM car you better have a healthy background in mechanics.

I've so far resisted adding bells & whistles to my Prius...darn thing is fine as-is.

:focus:

OK, so if Danny is comfortable building his own server, Raid-5 array w/HS, and having access to be able to swap out a defective disk, etc, building a full LAMP on his own (ain't hard at all - lots of guides out there)...

Or, going a full "I just X$ per month". Based on the WiredTree link I took a gander at, the SSD seems overpriced for what little gain that will be produced, versus, having more RAM, faster CPU, etc.

I like car & server analogies...my job, I work in both !

 

So, Mark, you don't just happen to have a few old parts lying around, do you?

 

Actually I do, mostly HD's, that I recycle as oversized USB disks.
Every 3-4 years I have to garage sale to clear out...the garage.

My new GF went Wth over the 6th box full of random parts & wires. Those 4 years are overdue.

On Ebay.Ca I could do a 10$ surprise box with 100$ Canada Post shipping. Guaranteed worth more (personal opinion).

For Hyo - I could soup up the largest box possible Canada Post ships and stuff it. Interested?
I'll just charge you shipping. For the lulz.

 

For me? That's very generous of you, thank you, but I'd have no idea what to do with it all. I was thinking you could make a new PriusChat server.

 

Really? What drives were you using? Did they and the OS support TRIM and was TRIM working? From my memory of reading how SSDs work (look through AnandTech - Intel X25-M SSD: Intel Delivers One of the World's Fastest Drives and AnandTech - The SSD Anthology: Understanding SSDs and New Drives from OCZ), it shouldn't make any difference whether you're writing to the same files or a new file due to wear leveling algorithms.

However, as the SSD drive fills up, if the drive doesn't support TRIM and the OS doesn't send it the commands, write performance will degrade due to the lack of readily available clean blocks (should affect file creation and overwriting equally). See Feature - Why You Need TRIM For Your SSD | bit-tech.net for tests they did by filling drives w/pre-TRIM firmware with trash then repeating w/TRIM enabled.

 

Tada ! Win7 & Server 2008 deal properly with SSD's, no tweaking req'd.

I used OCZ's, the ones from Intell where too pricey and I was building a 5 disk SATA2 SSD array so couldn't care less about small performance differences.

Another problem is when SSD's write to many files at the same time, like my desktop machine boots on SSD. I get intermittent <pauses> when Firefox & other programs are writing to the C: drive.

When I rebuild my workstation, I'll up the ram, use 64 bit Win7, and do a Raid-0 with two SSD's.

These articles reflect my personal experience with these drives over the last 8 months or so.

Flashy fists fly as OCZ and DDRdrive row over SSD performance ? The Register
DDRdrive has recently exited stealth mode and makes the X1 hybrid DRAM/NAND SSD. It criticises OCZ and other flash suppliers for products exhibiting a dreadful performance decline after a little use fresh out of the box.
​

SNIA specifies SSD performance test ? The Register
When SSDs are first bought and fresh out of the box ( FOB ), their performance is much higher as there is no need for write-erase cycles that slow performance down.

​

I recommend reading the comments on The Register.