Page 1 of 2 12 LastLast
Results 1 to 40 of 53

Thread: Future of Free-DC stats

  1. #1
    Administrator Bok's Avatar
    Join Date
    Oct 2003
    Location
    Wake Forest, North Carolina, United States
    Posts
    24,451
    Blog Entries
    13

    Future of Free-DC stats

    I'm going to post a few ideas in this thread. Please feel free to post your own ideas.

  2. #2
    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.

  3. #3
    Administrator Bok's Avatar
    Join Date
    Oct 2003
    Location
    Wake Forest, North Carolina, United States
    Posts
    24,451
    Blog Entries
    13
    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.

  4. #4
    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.

  5. #5
    Registered User
    Join Date
    Jan 2013
    Location
    Santa Ana, CA
    Posts
    2
    Quote Originally Posted by Bok View Post
    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

  6. #6

    Possible ideas

    Quote Originally Posted by Bok View Post
    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.

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

  8. #8
    Administrator Bok's Avatar
    Join Date
    Oct 2003
    Location
    Wake Forest, North Carolina, United States
    Posts
    24,451
    Blog Entries
    13
    Quote Originally Posted by kmanley57 View Post
    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).

  9. #9
    Administrator Bok's Avatar
    Join Date
    Oct 2003
    Location
    Wake Forest, North Carolina, United States
    Posts
    24,451
    Blog Entries
    13
    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.

  10. #10
    Administrator Bok's Avatar
    Join Date
    Oct 2003
    Location
    Wake Forest, North Carolina, United States
    Posts
    24,451
    Blog Entries
    13
    Quote Originally Posted by Rudy Toody View Post
    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.

  11. #11
    Registered User
    Join Date
    Nov 2012
    Location
    Central Europe
    Posts
    4
    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...

  12. #12
    Ancient Programmer Paratima's Avatar
    Join Date
    Dec 2001
    Location
    West Central Florida
    Posts
    3,296
    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.
    HOME: A physical construct for keeping rain off your computers.

  13. #13
    Administrator Bok's Avatar
    Join Date
    Oct 2003
    Location
    Wake Forest, North Carolina, United States
    Posts
    24,451
    Blog Entries
    13
    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.

  14. #14
    Junior Member
    Join Date
    Oct 2006
    Location
    Big Rock, TN
    Posts
    3
    Bok,

    Since I belong to AMDUser's of course I like AMDave's idea. Not because he's a teammate but I get to see his work more than most. Might I suggest a compromise in that you try the 'oscillate' idea and a reduction of the frequency of the updates. Even the sick numbers freaks like me would rather have less updates than to see you chuck the whole thing due to ongoing frustration. Take it from 20 to 10 for awhile and just see how it works out. I would be interested to see the frequency of people checking their stats and see just how many check more than daily. With that said I want to thank you for the time, effort, and money you put into these stats that we get to enjoy. They are a big part in why some people crunch and fun for many more.

    Terry

  15. #15
    Bok,

    As a Techno illiterate, but as one who reads and dreams about building THE system I have to agree with a couple of the initial posters above.

    1) Use the SSD for just the O/S and the program(s);
    2) Get 2 or 3 HDDs and use them set up as hot swappable or raid-0 so that there is always at least one HDD online to be used for the data (Velociraptors sound good); and
    3) Reduce the updates to just 12 a day at most. I mean, if I truly really want to know the latest credit score from a site, I go to the site, but look forward to my sig file generated here being updated. Having just read last sentence again.... Why not just do four updates a day, at 0600, 1200, 1800 and 2400hrs your time.

    It would be a pity to see this site go the way of... hmmmmmm was going to say the Dodo, but probably more accurately, the way of Geocities.

    (I know... nothing new, but did warn you I was a Techno Illiterate *grin*sigh*)

  16. #16
    Obviously most of the programming presented is way over my head. But I agree with the HDD RAID idea. If you leave the OS on a fast SSD, and Swap on a separate drive, why not take four 500GB HDDs and create a RAID 10 for the database? Then you have speed as well as redundancy. Also as mentioned, we appreciate your attention to detail, but honestly we could all live with less demands on your hardware - what about 4 updates per day - one every 6 hours?

  17. #17
    Registered User
    Join Date
    Jun 2009
    Location
    Plymouth UK
    Posts
    5
    May I ask: Which Intel Drives are you using?. To what degree have you over-provisioned them?

    I use an Intel 520 120GB with 20% set aside unused to give the trim some extra space. Chose to do this after reading This

    It only does around 7-10TB per month but has not been a problem this past year or so.

    I too run a ramdisk which in my case reduces the writes by around 50%.

    Just thinking aloud here in the hope it may evoke some thoughts your end.

  18. #18
    Junior Member
    Join Date
    Oct 2005
    Location
    Colorado Springs, CO
    Posts
    5
    You should at least be able to get a replacement drive since the Intel SSDs carry a 3 - 5 year warranty... unless running a db voids the warranty? Hope you're able to get it worked out, Bok. Free-DC is my favorite BOINC stats site.

  19. #19
    Hi, I have a "personnal" server which isn't actually in use, do you want some parts ? (24 Gb ram, SAS disks...)

    (the server is a Dell Poweredge2900, I have a fiber connexion at home...)

  20. #20
    IMO - posting once a day should be more than adequate. WCG does their posting at around 6PM - 7PM PT daily and no one seems to complain about that.
    "I refuse to answer that question on the grounds that I don't know the answer " [Douglas Adams (11 March 1952 – 11 May 2001)]




  21. #21
    Quote Originally Posted by vince120 View Post
    Hi, I have a "personnal" server which isn't actually in use, do you want some parts ? (24 Gb ram, SAS disks...)

    (the server is a Dell Poweredge2900, I have a fiber connexion at home...)

    Donations as well!...I have a few 146GB 15K SAS drives, sealed & never been used....

    Steve

  22. #22
    Member zombie67's Avatar
    Join Date
    Jan 2008
    Location
    Dublin, CA
    Posts
    88
    Quote Originally Posted by Gandalf_the_Grey View Post
    IMO - posting once a day should be more than adequate. WCG does their posting at around 6PM - 7PM PT daily and no one seems to complain about that.
    Gonna have to disagree.

    I hate that WCG exports only daily. Very annoying.

    When in a challenge (which seems to be all the time lately), we need to be able to see what's going on way more frequently than daily. Also, I visit the stats many times per day, just to check in and make sure everything is progressing properly for my farm and the projects. Every 2-4 hours is probably good enough.


  23. #23

    Give it all up!?!

    It's Clank [MM]

    First, what would you do with all that time on your hands. Second you could collapse Boinc. It's YOUR site that keeps everyone going. Stats, stats, stats. Let me know if you need anything. Patrick

  24. #24
    Junior Member
    Join Date
    Feb 2010
    Location
    Raleigh, North Carolina, United States
    Posts
    25
    Bok,

    Before making suggestions I need to understand your DB setup better.

    You said you have two databases dcfree and stats; are these in one or two mysql servers on the host?
    Why are you using the the temp and regular table setup?
    - website performance
    - consistency, need to update multiple tables separately but they all need to be updated before used
    - backup
    - update performance
    After you update the tables is the data static until the next update?
    How big is this data?
    - total mysql db size GB
    - stats db size [30GB?]
    - largest table GB
    - largest table rows
    - largest table web users can cause updates to
    Which storage engine are you using?
    What collation are you using?
    Do you know your level of db reads and writes?

    Jeff

  25. #25
    You might look into the Intel X25-E SSDs. These have the SLC flash and the 64GB version is rated for around 2 Petabytes of writes. I have seen these go on E-bay with low writes for around $100 a piece. In general, the enterprise grade SSDs come overprovisioned from the factory.

    If you have sufficient memory, eliminate the swap file all together. I never use swap files any more since memory costs very little. Have syslogd and other logging daemons write to non-SSD based storage. You could mount a partition on a hard disk to /var/log for example or mount /var/log to a remote system with NFS. With a high traffic website, I can see log files growing rapidly.

    You can also check the number of writes that your Intel SSD has sustained using smartctl.

    smartctl -a /dev/sda (replace sda with the appropriate device name matching the SSD that you want to check)

    Intel has a field called Host_Writes_32MiB. Multiple the returned value by 32 to get the amount of megabytes written to the SSD. The SSDs with MLC flash are fairly limited in writes and I can see a database like the one hosting your site wearing the flash out fairly quick. Almost all the consumer grade SSDs have MLC or TLC flash.

    If you have a RAID array, you may also want to setup a hot spare.

    Jeroen

  26. #26
    www.amdusers.com
    Join Date
    Apr 2002
    Location
    Sydney, Australia
    Posts
    266
    Quote Originally Posted by zombie67 View Post
    Gonna have to disagree.

    I hate that WCG exports only daily. Very annoying.

    When in a challenge (which seems to be all the time lately), we need to be able to see what's going on way more frequently than daily. Also, I visit the stats many times per day, just to check in and make sure everything is progressing properly for my farm and the projects. Every 2-4 hours is probably good enough.
    I agree with zombie67; one of the reasons I don't run much WCG is the lousy stats updates.
    bok please keep going. When I was made redundant and un / under-employed for 16 months it was your stats that kept me alive. I had something to do each day, all day!

    If you need donations let us know.

    Cheers,
    Vaughan
    AMD Users

  27. #27
    Administrator Bok's Avatar
    Join Date
    Oct 2003
    Location
    Wake Forest, North Carolina, United States
    Posts
    24,451
    Blog Entries
    13
    Quote Originally Posted by Jeff17 View Post
    Bok,

    Before making suggestions I need to understand your DB setup better.

    You said you have two databases dcfree and stats; are these in one or two mysql servers on the host?
    Why are you using the the temp and regular table setup?
    - website performance
    - consistency, need to update multiple tables separately but they all need to be updated before used
    - backup
    - update performance
    After you update the tables is the data static until the next update?
    How big is this data?
    - total mysql db size GB
    - stats db size [30GB?]
    - largest table GB
    - largest table rows
    - largest table web users can cause updates to
    Which storage engine are you using?
    What collation are you using?
    Do you know your level of db reads and writes?

    Jeff
    Both databases are under the same mysql on one machine, residing on different SSD's
    I use the temp and regular setup, just to allow it to be able to replicate raw data across databases, then drop and rename the tables. Yes, for consistency. Backups are down nightly to external drives.

    Website performance is another big question and is causing me a lot of grief, but it's nothing to do with the database, likely all due to too much javascript (I could use any help on that too!)

    Data is mostly static after an update, but updates happen frequently..
    Total size of data on each is ~ 70Gb, difficult as there are also static tables like historic milestones and movement data.
    largest table would be the historic milestones at around 25M rows. used to have a weekly data on host that got close to 1B rows, but scrapped that.
    Row size on hosts is about 4k.
    Web users can only affect a few minor tables.
    I'm doing this all on myISAM in order to allow the raw data replication.
    Massive amounts of reads and writes. For instance for Seti@Home one update will involve close to 5M writes.

    It's all pretty well optimized, but the replication is just killing the drives. I've now had 4 drives fail, but they have all been the drive that gets replicated TO. The one that does all of the calculations has not failed yet in almost 4 years. I have replaced it once, but just to move to a SATAIII setup.

    So, this new way of doing it will alleviate it totally. And I'm pretty far into recoding for it now.

    I do all of the same updates and calculations in one database (let's call it stats1), then switch a variable so that the webpages read all the data from that database.
    Next update run will do the updates and calculations in the second database (stats2) and then switch the webpages to read from that one.

    I've moved all static(ish) tables into a 3rd database on another separate drive, so there is recoding to point to those instead.

    My biggest hurdle is the end of day calculations and rollover. I need to make sure that both databases are consistent before doing that rollover in both of them, so that the dailies match in both. Otherwise during the oscillation during the day, there could possibly be discrepancies. Updating less frequently will mitigate this and I'm just planning on a 2 hour window where I don't download any new files.

    I still intend to update fairly frequently, probably just hourly checks.

    I may switch all the tables over to InnoDB now that I'm not doing the raw replication.

    The non boinc projects are a little more challenging in tha tdata is not really downloaded from all of them in the form of xml files, it's scraped. But I'm not that worried about those. I'll get the BOINC ones working first, then do something for them.

  28. #28
    Registered User Fire$torm's Avatar
    Join Date
    Jan 2010
    Location
    U.S.A. Eastcoast
    Posts
    25
    Hi Bok,

    Like some of the other posters, I believe RAID is the way to go. My twist to this theme is to use a separate RAID Box consisting of a CPU with a low TDP (To reduce power consumption).

    My suggestion for a RAID box
    OS: FreeNAS (Link)

    CPU: AMD A8-5500 Trinity 3.2GHz Quad-Core (Link)

    MB: (The following support (x6) SATA III + (x1) eSATA, Gigabit Ethernet, USB 3.0)
    *ASUS F2A85-M/CSM (Link)
    *ASRock FM2A85X Extreme4-M (Link)
    Note:
    *eSATA port would be used for boot drive.

    HDD: (x6) WD Scorpio Black WD7500BPKT 750GB 2.5" (Link)
    Notes:
    *The WD SB series are the most robust 2.5" HDDs that I know of at their price point.
    *The 2.5" form factor allows for better airflow, helping to keep them cool.
    *A way to improve read/write and reduce latency of an HDD, is formatting it to <= 50% capacity. This is called Short Stroking.
    *Using RAID 10 will give you a total capacity of 1,125GB (Link) and give performance near/at SATA III (single disk) levels.

    Case: Cooler Master HAF 912 (Link)
    *Best airflow performance for a small mid-tower at its price point.

    Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin

  29. #29
    Junior Member
    Join Date
    Oct 2006
    Location
    Big Rock, TN
    Posts
    3
    Bok,
    To cut your writes to any specific drive couldn't you just add more drives to the mix, stats3, stats4,...?

  30. #30
    Administrator Bok's Avatar
    Join Date
    Oct 2003
    Location
    Wake Forest, North Carolina, United States
    Posts
    24,451
    Blog Entries
    13
    Adding more drives would mean I'd have to split up tables and do even more coding - it could get messy. However I have added a 3rd database.

    Here is the current progress.

    I've re-organized the tables and now have one with 'static-ish' tables in whilst the other two are virtual clones of each other.

    Redone the perl and shellscripts so they now flipflop between these two database when doing updates. Seems to be working well. Prior to starting any update, they will pass the other database over to the webserver so it knows to server from the one not being updated. Conversely after updates, it does the same thing so that the latest data is always shown.

    Not quite finished with all the custom scripts I have but the major ones are running.

    On the webside, I've started doing the changes necessary to make sure any of the static tables are accessed correctly. As long as the overnight tonight works ok, I should be in a position to switch them back on sometime tomorrow. Some things won't work, but I'll be able to fix it up fairly quickly over the coming days.

    Hang tight!

  31. #31
    Junior Member
    Join Date
    Feb 2010
    Location
    Raleigh, North Carolina, United States
    Posts
    25
    Our big server (holding the db for an active web application) is about the same size ~140GB total db space, 200+ tables, several tables with 10-70M rows and the largest at almost 200M rows. We are about 4-1 select vs insert/update on the database (see later). We only have two tables that get big push updates, 300K and 1.3M, rows and that is just once a day.

    When we were performance testing two years ago we looked at myISAM vs InnoDB, SSD, SSD raid, standard disk raid and increasing memory. We saw the biggest performance gain by switching to InnoDB and getting row level locking; mainly because we have php sessions in the db (~20K rows but constant read/write/update) and the 200M row table (activity log that gets lots of inserts and large reads when reports are run). Neither SSD option gave us much performance gain over standard disk raid (especially for the price) and frequently the SSD raid was slower during our once a day update. Increasing the memory and then increasing the innodb-buffer-pool-size was a much better option for the money than the SSDs. We have 96GB of RAM in the server and the buffer set to 68GB. Because of this large buffer almost half our reads are cache hits saving a huge db and disk load, before the memory change our db read vs write was about 7-1. The disks are 4 x 7200rpm 2.5in 250GB in a raid 10.

    I think your change to swapping the db in the web config vs copying the tables should help with killing the SSDs. I would watch the disk stats to make sure the reads from the web queries are not also somehow helping to kill the SSDs; since in your previous setup all the web queries were also hitting the SSD that keeps dying.

    Next I would work on changing to InnoDB. Switching to InnoDB will give you some performance gains on the web queries and should give a lot on the updates (row vs table locking). However, before making the change be sure to review the InnoDB configuration settings especially related to the various buffers, there is a whole separate set of config values for InnoDB and if you leave them at defaults you will kill the server. Set the buffer as large as you can, probably with your machine 16GB is about your max.

    Long term I think you would be better off if you can get more memory in the server and having larger buffers, you can then probably go back to standard disks in a raid to save some money. It would be great if you had enough memory for 48GB or 64GB buffer.

    I use the temp table swap trick in a few places also. It is a nice way to update data without impacting the user and then suddenly all the updates are there. In mine I have the temp table in the same db to start with so no copies.

  32. #32
    Now while my expertise in database is restricted to the fact I know they exist and are run on computers...

    I'd just like to say thank you, Bok. BOINCing is half the fun without Free-DC and knowing what my rigs are doing. Please don't give up and while it looks like you found one or more better solutions for hassle-free running, please don't hesitate to ask us for donations. On many other places I have become sceptical about use of donations, but Free-DC always is a no-brainer to me as I know how hard you are working on that.

    Thank you so much, Bok.

  33. #33
    Quote Originally Posted by Mad Matt View Post
    Now while my expertise in database is restricted to the fact I know they exist and are run on computers...

    I'd just like to say thank you, Bok. BOINCing is half the fun without Free-DC and knowing what my rigs are doing. Please don't give up and while it looks like you found one or more better solutions for hassle-free running, please don't hesitate to ask us for donations. On many other places I have become sceptical about use of donations, but Free-DC always is a no-brainer to me as I know how hard you are working on that.

    Thank you so much, Bok.
    I don't post here much, if ever. However, I am a fan of Free-dc and would like to help, as best I can. That would be by sending you a portion of the not-so-large paycheck I get, once in a while. If there is something I do know about DB's, it costs money. Let me help out a little. I know some folks at TPU feel the same way. You have enough to do, let us help defray some costs!

  34. #34
    Administrator Bok's Avatar
    Join Date
    Oct 2003
    Location
    Wake Forest, North Carolina, United States
    Posts
    24,451
    Blog Entries
    13
    I always appreciate any donation, they always go towards extra hardware. Now that I've got this 'rotational' database updating working pretty well, I might even put in another SSD and rotate through 3 db's. In fact, in perfect world, if I had the extra hardware I'd run a redundant database server doing exactly the same thing, so I could just switch between them if anything went wrong

  35. #35
    Quote Originally Posted by Mad Matt View Post
    Now while my expertise in database is restricted to the fact I know they exist and are run on computers...

    I'd just like to say thank you, Bok. BOINCing is half the fun without Free-DC and knowing what my rigs are doing. Please don't give up and while it looks like you found one or more better solutions for hassle-free running, please don't hesitate to ask us for donations. On many other places I have become sceptical about use of donations, but Free-DC always is a no-brainer to me as I know how hard you are working on that.

    Thank you so much, Bok.
    +1

  36. #36
    Quote Originally Posted by Arjai View Post


    I don't post here much, if ever. However, I am a fan of Free-dc and would like to help, as best I can. That would be by sending you a portion of the not-so-large paycheck I get, once in a while. If there is something I do know about DB's, it costs money. Let me help out a little. I know some folks at TPU feel the same way. You have enough to do, let us help defray some costs!
    +1 all of our Team members are fans of your site!!!

    Quote Originally Posted by Bok View Post
    I always appreciate any donation, they always go towards extra hardware. Now that I've got this 'rotational' database updating working pretty well, I might even put in another SSD and rotate through 3 db's. In fact, in perfect world, if I had the extra hardware I'd run a redundant database server doing exactly the same thing, so I could just switch between them if anything went wrong
    Our TechPowerUp (TPU) WCG Team will be discussing some fund raising efforts for you over the coming weekend to see if we can assist in some measurable way.

    Just joined your forum today and really wanted to let you know that you're site has real value to us and to thank you for all of the work you do!

  37. #37
    Administrator Bok's Avatar
    Join Date
    Oct 2003
    Location
    Wake Forest, North Carolina, United States
    Posts
    24,451
    Blog Entries
    13
    Thanks. I've already received enough for a decent spare DB server mobo just in the last few hours.

    As an update, I've just integrated all of the nonBOINC updates into the main BOINC update script so it keeps in sync now with the database flip-flop. It's all running as one for the first time and no issues so far. I do need one more rollover at end of day to make sure all of that works ok, but if it does, I'll switch on the site first thing tomorrow morning. I'm going to bring the server down for a short while anyway to put in the spare SSD regardless.

    As an added bonus, we've finally figured out the cause of the 'slowness' on the site. It's only taken 6 months of debug. It's the dynamic resize javascript code in there that's doing it. We removed it as a test and it massively improved the performance to near instantaneous! We need to find a better way to do it but Gopher is on the case and he's a whiz at this type of thing, so I have every confidence!

  38. #38
    Free-DC Semi-retire gopher_yarrowzoo's Avatar
    Join Date
    Mar 2002
    Location
    Knoxville, TN
    Posts
    3,985
    Quote Originally Posted by Bok View Post
    As an added bonus, we've finally figured out the cause of the 'slowness' on the site. It's only taken 6 months of debug. It's the dynamic resize javascript code in there that's doing it. We removed it as a test and it massively improved the performance to near instantaneous! We need to find a better way to do it but Gopher is on the case and he's a whiz at this type of thing, so I have every confidence!
    Gee thanks...
    required_once("Coffee.php");
    required("css3_styler.php");
    required("no_sleep.php");

    I'm on it.
    Semi-retired from Free-DC...
    I have some time to help.....
    I need a new laptop,but who needs a laptop when you have a phone...
    Now to remember my old computer specs..


  39. #39
    Junior Member
    Join Date
    Feb 2010
    Location
    Raleigh, North Carolina, United States
    Posts
    25
    Quote Originally Posted by gopher_yarrowzoo View Post
    required_once("Coffee.php");
    Hmm I think this must be a typo. Not being a coffee drinker I am basing this on observational data but I am fairly certain that coffee and once can never go together.

  40. #40
    Registered User
    Join Date
    Jun 2009
    Location
    Plymouth UK
    Posts
    5
    Looking at the number of views to this thread it occurs to me that everybody who uses your stats should pay.

    Shall we start with $1.00

    If everybody paid $1 I think you could procure a fix

    I think I should propose such an idea on our site.

    Sure is a key component for so many of us using BOINC

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •