Future of Free-DC stats

[Jim1900]

I have mentioned this before, but there are two ways to prevent writes from killing your SSD:
(1) If the size of the write files can fit in main memory, use a Ramdisk.
http://http://www.romexsoftware.com/en-us/primo-ramdisk/index.html

(2) If the main memory is not big enough, use a write cache (as large as you have memory for) with a long latency.
An hour will greatly reduce writes to disk in most cases. I used a 24-hour latency and reduced them by 99%, but it depends on the application.
http://www.romexsoftware.com/en-us/f...che/index.html

Linux has a comparable feature of using a virtual drive, but I don't know the details of that; I am sure plenty of other people around here can help.

[Bok]

Good ideas of course - this is all on linux and I certainly could use Ramdisks (or mysql memory tables). I would love to fit the stats tables into Ram but without removing host data and potentially freeing up some other types of data I would need at least 64Gb Ram and more likely 128Gb which would require some high end hardware.

I currently have 32Gb on the DB server which is the max for that particular motherboard.

Here is what I've posted in our private thread.

I have TWO separate databases on two distinct SSD drives.

All updates and calculations are done in the dcfree database to tables name like temp_boinc_user, temp_boinc_team etc etc.

Second database (stats) contains the web facing data with tables named boinc_user, boinc_team etc etc.

Once all updates are done in a single iteration, I copy the database 'files' from dcfree to stats (outside of mysql), so stats then contains both boinc_user and temp_boinc_user. I then drop boinc_user and rename temp_boinc_user-> boinc_user.

This operation is pretty much instantaneous so anyone doing an update on the webpages never tries to access a table that is locked for update.

Problem being though that I write 30Gb of data to the 'stats' drive 20 times per day. That on top of the reads just wears them down.

I could lower the frequency considerably.

I'd love to have the whole 'stats' database in memory personally, but would need 128Gb Ram likely be able to do that (totally new hardware) and it would take some rewrite. (Or remove the hosts part)

Probably other options, but I'm open to suggestions.

*EDIT* this one was an Intel SSD as well which are supposed to be the most robust. Lasted only 5 months.

[Bikermatt]

Bok, I have a Supermicro H8SGL motherboard that has not worked for the last year. I am going to RMA it right now. When it comes back I could donate it along with a 6172 processor and heatsink. You would still need $500 to $1000 dollars worth of ram but I imagine we could raise that through donations.

[Rudy Toody]

I have posted a thread at TeAm AnandTech to collect ideas, solutions, and opinions.

[Bok]

I've had one idea from AMDave that seems very feasible. Never considered it before but it should be doable with some small changes and would eliminate the writes.

Instead of replicating the data from one drive to another, instead 'oscillate' the database connection from the webserver.

i.e.

still have two distinct databases on distinct drives as I currently have, but make them identical for all intents and purposes (I'd actually have a 3rd for static data which I've been considering anyway).

1. Run the stats update on db1, whilst the webserver points to db2.
2. On completion change the pointer from the webserver to point to db1.
3. Run the stats update on db2 (and ensure any changes include the ones to the prior update as well as any new), webserver still points to db1
4. on completion change pointer from the webserver to point to db2
5. Rinse and repeat.

It will mean I'll be duplicating some updates, but that's actually fairly minor.

Part 3 is the tricky part but is doable.

Might need a 3rd SSD in there perhaps.

Still need to think this out a little more.

[Bok]

I have posted a thread at TeAm AnandTech to collect ideas, solutions, and opinions.

Thanks Rudy, I do appreciate it, not looking for donations at the moment until I at least have a plan. More looking for ideas. See my previous post for the best one I have so far.

[mihapiha]

I wonder why you don't run this off a VelociRaptor HDD.

If writing and rewriting is that big of a problem for the SSDs, the VelociRaptor might be a good alternative.

It would be a real shame if you'd have to shut this down. It's such a nice stats page, and I do check on daily bases...

[Paratima]

I tend to agree with the post by Raubhautz*, above. That is, SSD is just not ready for this kind of abuse (and may not be anytime soon). You can set up some mighty fast RAID-0's with rotational HDD's for mighty cheap! Not as fast, admitted, as the blinding speed of SSD's, but keep 'em reasonably cool and they run practically forever. On my business systems, SSD's are used only for OS and programs, i.e. very infrequent writes. All my precious data resides on spinning memory. AMDave's ideas sound good, too.

[Bok]

I do appreciate everyones input - am reading everything for sure.

Started work on the oscillation idea.

FYI, the machine on reboot brought up the drive just fine, no errors on it at all. Not going to switch the stats on just yet though.

[Raubhautz*]

Good ideas of course - this is all on linux and I certainly could use Ramdisks (or mysql memory tables). I would love to fit the stats tables into Ram but without removing host data and potentially freeing up some other types of data I would need at least 64Gb Ram and more likely 128Gb which would require some high end hardware.

[...]

*EDIT* this one was an Intel SSD as well which are supposed to be the most robust. Lasted only 5 months.

Hi,

I know that I may just be another cruncher here, but I work in the computer industry dealing with this type of hardware and software (db's). In my professional opinion, at this time, SSD's are NOT the way to go for any kind of db that will be used continually (as in most web based db's). The problem is the Trim features and even the higher end SSD's with the partitioning cannot keep up with the writes and deletes, so at some point, though the OS shows disk space availability, the SSD actually has nowhere to put the data that should be able to fit on the drive. Sometimes this results in loss of partitions (possible to recover from this) and worse case, especially in db's, depending on what was lost... The Trim and partitioning require some time to perform their duties, even the high reliability drives by Intel, OWC, and Samsung.

I have equipment that works nearly 24hrs, though not a db, I found only 2 drives that worked without having failures. Crucial (Micron), Toshiba, SanDisk, Kingston, OCZ, Intel and Plextor do not cut the 24/7/565 test in my experience at all; they last 3 - 6 weeks and poop out if they have not been given at least several hours a week of no drive activity to do their garbage collection and Trim. The only drive of reputation I did not try was the Seagate - they are too new in the market to even spend time on. I have, and do run Samsung 840's and OWC drives, and they have been good for over 6 months without a hick-up. Now this is not to suggest they could take the continuous pounding of being db volumes.

My suggestion is to save the money on SSD's and get some quality disk drives, perhaps some hybrids which would still offer some performance, but they still will write to the platters if/when cache is full or not optimized. Use the money to put into the memory for the MB discussed earlier in the thread and as Jim1900 suggested (and I'm sure you would like as well), keep your main tables in memory, not swapfiles. If you need to ask for donations, so be it.

Another thing that I would consider is to reduce the frequency of updates from 20 to 12 or even as few as 4 (every 2-6 hours). I do not think that anyone requires the updates that frequent, do they? Just throwing some ideas out there.

Best wishes,
Phil

[kmanley57]

Good ideas of course - this is all on linux and I certainly could use Ramdisks (or mysql memory tables). I would love to fit the stats tables into Ram but without removing host data and potentially freeing up some other types of data I would need at least 64Gb Ram and more likely 128Gb which would require some high end hardware.

I currently have 32Gb on the DB server which is the max for that particular motherboard.

Here is what I've posted in our private thread.

I have TWO separate databases on two distinct SSD drives.

All updates and calculations are done in the dcfree database to tables name like temp_boinc_user, temp_boinc_team etc etc.

Second database (stats) contains the web facing data with tables named boinc_user, boinc_team etc etc.

Once all updates are done in a single iteration, I copy the database 'files' from dcfree to stats (outside of mysql), so stats then contains both boinc_user and temp_boinc_user. I then drop boinc_user and rename temp_boinc_user-> boinc_user.

This operation is pretty much instantaneous so anyone doing an update on the webpages never tries to access a table that is locked for update.

Problem being though that I write 30Gb of data to the 'stats' drive 20 times per day. That on top of the reads just wears them down.

I could lower the frequency considerably.

I'd love to have the whole 'stats' database in memory personally, but would need 128Gb Ram likely be able to do that (totally new hardware) and it would take some rewrite. (Or remove the hosts part)

Probably other options, but I'm open to suggestions.

*EDIT* this one was an Intel SSD as well which are supposed to be the most robust. Lasted only 5 months.

Make sure swap space is not on the SSD if it still is! The system uses swap TONS during Database/large file operations. If the database will not easily fit in memory it has to use the swap space even more. Your system drive ram buffer can grow over time to fill available 'free' memory, then start using swap space.

Windows and Unix are from before large files were commonly ran across, so they bang on swap in Unix and the Page file(aka Virtual memory) in Windows. One reason machines have more memory now than years ago!

Linux you can just mount a separate drive and change a couple settings in the mount table to use as swap space, while Windows it is a file you have to move to another drive and change settings in Windows to do it.

Frequently used file/s are saved in the ram buffer and the less frequent accessed file/s are put on the swap space.

Example: mysql and the part of the file worked on is swapped out of the ramdisk buffer, and the actual database it is working on from the swap space.

It is a VERY simple example, but the idea of how things are done follows this process.

[Bok]

Make sure swap space is not on the SSD if it still is! The system uses swap TONS during Database/large file operations. If the database will not easily fit in memory it has to use the swap space even more. Your system drive ram buffer can grow over time to fill available 'free' memory, then start using swap space.

Windows and Unix are from before large files were commonly ran across, so they bang on swap in Unix and the Page file(aka Virtual memory) in Windows. One reason machines have more memory now than years ago!

Linux you can just mount a separate drive and change a couple settings in the mount table to use as swap space, while Windows it is a file you have to move to another drive and change settings in Windows to do it.

Frequently used file/s are saved in the ram buffer and the less frequent accessed file/s are put on the swap space.

Example: mysql and the part of the file worked on is swapped out of the ramdisk buffer, and the actual database it is working on from the swap space.

It is a VERY simple example, but the idea of how things are done follows this process.

yup, swap is on a separate drive, and I also have a separate mysql tmp space too in a ramdisk for the 1! major sql I do for the combined user data which goes to a temp table. (no way around it at all that I can find and I've posted to mysql lists to no avail also - not that this one is too big a deal).