Results 1 to 18 of 18

Thread: Upgrading thor

  1. #1
    GrnEyedDvl's Avatar Barackolypse Now
    Artifex Technical Staff

    Join Date
    Jan 2007
    Location
    Denver CO
    Posts
    22,390

    Default Upgrading thor

    The database replication to thor has been killed, and thor is now shut down. This means we are running without a backup server for at least the next few days, and I am pulling database dumps to a machine at my house. I will be heading down to the datacenter to swap out hardware and install the OS fairly soon. Then Squid and I will start installing and configuring packages.

  2. #2
    GrnEyedDvl's Avatar Barackolypse Now
    Artifex Technical Staff

    Join Date
    Jan 2007
    Location
    Denver CO
    Posts
    22,390

    Default Re: Upgrading thor

    Thor is up and running on the new drives. Squid now has root access and I am installing some of the base packages.

    Packages installed, most need some config tweaks:

    lighttpd
    ufw
    fail2ban
    sphinxsearch
    logwatch
    postfix
    Last edited by GrnEyedDvl; November 30, 2012 at 07:13 PM.

  3. #3
    GrnEyedDvl's Avatar Barackolypse Now
    Artifex Technical Staff

    Join Date
    Jan 2007
    Location
    Denver CO
    Posts
    22,390

    Default Re: Upgrading thor

    Loading database backups to /data/backups

  4. #4
    GrnEyedDvl's Avatar Barackolypse Now
    Artifex Technical Staff

    Join Date
    Jan 2007
    Location
    Denver CO
    Posts
    22,390

    Default Re: Upgrading thor

    fast-cgi is installed with the basic config.

  5. #5
    Mangerman's Avatar Only the ladder is real
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    4,394

    Default Re: Upgrading thor

    Is everything still going according to plan or have you resolved into pulling your hair out?

  6. #6
    GrnEyedDvl's Avatar Barackolypse Now
    Artifex Technical Staff

    Join Date
    Jan 2007
    Location
    Denver CO
    Posts
    22,390

    Default Re: Upgrading thor

    Copying LOTS of data from mjollnir to thor, 300ish gigs. Its going to take a while.

  7. #7
    GrnEyedDvl's Avatar Barackolypse Now
    Artifex Technical Staff

    Join Date
    Jan 2007
    Location
    Denver CO
    Posts
    22,390

    Default Re: Upgrading thor

    This looks to be finished, I need to dig in and make sure everything copied. The data on thor is in /data if Squid wants to take a look as well.

  8. #8
    General Brewster's Avatar The Flying Dutchman
    Join Date
    Jul 2011
    Location
    Kingdom of The Netherlands
    Posts
    8,119

    Default Re: Upgrading thor

    Thanks for your work GED.
    and Squid too.
    Last edited by General Brewster; December 28, 2012 at 09:03 PM. Reason: Shamefull display!

  9. #9
    GrnEyedDvl's Avatar Barackolypse Now
    Artifex Technical Staff

    Join Date
    Jan 2007
    Location
    Denver CO
    Posts
    22,390

    Default Re: Upgrading thor

    I just posted an announcement, but I will be taking mjollnir down tomorrow afternoon so I can configure database replication. I will replicate the database to thor and to the Vault server so we have multiple copies in multiple locations.

    I am performing another rsync now. I have been doing this manually once a week or so for the last month but tomorrow I will also build a script to do this 2 or 3 times a day until we actually switch so that most of it is there and ready to go. The manual commands for this are:

    sudo rsync -avx --timeout=30 USER_NAME@twcenter.net:/mnt/data/www/ /data/www/

    sudo rsync -avx --timeout=30 USER_NAME@twcenter.net:/mnt/data/userfiles/ /data/userfiles/


    Once I have the database replicated we will have to change some filepaths around and make sure the site works on thor. Then I will configure caching and a few other things like our backup scripts. Once Squid and I are satisfied that the site is up and works normally on thor we will figure out when we are going to perform the upgrade.

  10. #10
    GrnEyedDvl's Avatar Barackolypse Now
    Artifex Technical Staff

    Join Date
    Jan 2007
    Location
    Denver CO
    Posts
    22,390

    Default Re: Upgrading thor

    Thanks for the comments guys but I would prefer to keep this clean for Squid and I.

    Ok the dump completed earlier but I had to run and do something. Now its importing into thor which will take a while, when that is done I will start the replication process that should keep it current so we dont have to update it again later. I will also start replicating both the database and the file structure to the Vault server for backup.

    While I am on that topic I think I am going to setup rsnapshot this weekend as well. We currently use that for specific file system stuff and we have hourly, daily, and monthly shots, but I dont believe it setup for everything. If it was the little hiccup we had last week would have been solved much faster. We have the Vault server and currently we only use it for modding stuff but saving key files from our main servers would be a good use for it as well.

    Squid you can look at how this is setup and what it saves by going to this location on mjollnir:

    /mnt/data/backup/rsnapshot

    I think I can get this to run over an internal network so its not a bandwidth hit. That might require a trip downtown but its worth it.

  11. #11
    GrnEyedDvl's Avatar Barackolypse Now
    Artifex Technical Staff

    Join Date
    Jan 2007
    Location
    Denver CO
    Posts
    22,390

    Default Re: Upgrading thor

    The databases (forum and wiki) have successfully imported into thor, and I am now setting up the Vault server as a replication slave so that there is always a live backup and several days worth of incremental backups on a separate machine.

    So basically right now mjollnir is the master, thor copies from mjollnir, atlas copied from thor. That is hardly the most efficient way to do this but before the end of the month mjollnir will be shut down for upgrades and thor will be the master, so slaving atlas of thor from the beginning is the right way to handle it.

    I will want this setup to run for a couple of days before I do anything else. Once I am satisfied that all the data is being replicated as it should, then Squid and I will move the site from mjollnir to thor. If that goes smoothly then we will let that run for a few days to make sure we didnt miss anything, and then schedule the vb4 upgrade.

  12. #12
    GrnEyedDvl's Avatar Barackolypse Now
    Artifex Technical Staff

    Join Date
    Jan 2007
    Location
    Denver CO
    Posts
    22,390

    Default Re: Upgrading thor

    Replication is up and running on thor from mjollnir, and is now caught up. We will get the vault server caught up tomorrow.

  13. #13
    GrnEyedDvl's Avatar Barackolypse Now
    Artifex Technical Staff

    Join Date
    Jan 2007
    Location
    Denver CO
    Posts
    22,390

    Default Re: Upgrading thor

    I have been playing with some mysql stuff and ran a script that gives performance increase suggestions. Here is the output:

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 82M (Tables: 6)
    [--] Data in InnoDB tables: 29G (Tables: 301)
    [--] Data in MEMORY tables: 1M (Tables: 5)
    [!!] Total fragmented tables: 85
    -------- Security Recommendations -------------------------------------------
    [OK] All database users have passwords assigned
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 10d 6h 27m 34s (250M q [282.620 qps], 12M conn, TX: 5808B, RX: 67B)
    [--] Reads / Writes: 79% / 21%
    [--] Total buffers: 5.4G global + 11.2M per thread (500 max threads)
    [OK] Maximum possible memory usage: 10.9G (34% of installed RAM)
    [OK] Slow queries: 0% (104K/250M)
    [OK] Highest usage of available connections: 73% (365/500)
    [OK] Key buffer size / total MyISAM indexes: 256.0M/76.8M
    [OK] Key buffer hit rate: 99.8% (78M cached / 150K reads)
    [OK] Query cache efficiency: 63.4% (122M cached / 193M selects)
    [!!] Query cache prunes per day: 971520
    [OK] Sorts requiring temporary tables: 0% (25K temp sorts / 17M sorts)
    [!!] Joins performed without indexes: 3284
    [!!] Temporary tables created on disk: 33% (6M on disk / 20M total)
    [OK] Thread cache hit rate: 99% (365 created / 12M connections)
    [!!] Table cache hit rate: 5% (1K open / 28K opened)
    [OK] Open file limit used: 0% (85/10K)
    [OK] Table locks acquired immediately: 99% (184M immediate / 184M locks)
    [!!] InnoDB data size / buffer pool: 29.2G/5.0G
    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
    query_cache_size (> 64M)
    join_buffer_size (> 1.0M, or always use indexes with joins)
    tmp_table_size (> 256M)
    max_heap_table_size (> 128M)
    table_cache (> 5000)
    innodb_buffer_pool_size (>= 29G)

    I am not going to mess with the table structure right now as we still have some replication that needs to catch up, but I am going to adjust these variables one at a time and see what happens. mysql will need to be restarted after each change but it only takes about 10 seconds to restart. I need to run this script again on a weekend when we are at peak load.

  14. #14
    GrnEyedDvl's Avatar Barackolypse Now
    Artifex Technical Staff

    Join Date
    Jan 2007
    Location
    Denver CO
    Posts
    22,390

    Default Re: Upgrading thor

    [OK] Maximum possible memory usage: 10.9G (34% of installed RAM)
    innodb_buffer_pool_size (>= 29G)
    This was set at 5G, I changed it to 16G. The suggested size is 29 gigs but that leaves us too little RAM for other stuff. What I am trying to do here is give mysql as much RAM as I can without eating into what lighttpd is set to use.

    Two possible solutions a bit down the road:

    1. Buy more RAM
    2. Use thor for a dedicated database server, and mjollnir for a dedicated web server.

    Neither option is viable right now, mjollnir needs its hardware upgraded first.


    tmp_table_size (> 256M)
    Set to 512M, just so its in even increments. I may need to reduce this a bit since I bumped the buffer size so much.


    query_cache_size (> 64M)
    Set to 128M, again this may need to be dropped a bit.


    max_heap_table_size (> 128M)
    Set to 256M


    table_cache (> 5000)
    Bumped to 5500. Sim left a note that he had it at 2500 once but upped it.


    [OK] Highest usage of available connections: 73% (365/500)
    Upped this to 600. If we are using 73% of our max connections on a Monday night then we are probably hitting our limit on the weekends.

    I made those changes one at a time and then restarted mysql each time just to make sure it came back up. I will let it run for a while and let some things cache and then run the script again.


    [mysqldump]
    quick
    quote-names
    max_allowed_packet = 16M
    I need to look this up, but I suspect this is why it takes sooo damn long to dump and import mysqldump files. On a box with 32 gigs of RAM we shouldnt limit it to 16M like this.

  15. #15
    GrnEyedDvl's Avatar Barackolypse Now
    Artifex Technical Staff

    Join Date
    Jan 2007
    Location
    Denver CO
    Posts
    22,390

    Default Re: Upgrading thor

    Load and CPU are down a bit from an hour ago, some of that will be due to normal traffic drop at this time of night. After a few days we can check observium and see if there is any noticeable difference vs our historical numbers.

  16. #16
    Squid's Avatar Opifex
    Artifex Technical Staff

    Join Date
    Feb 2007
    Location
    Frozen waste lands of the north
    Posts
    16,856

    Default Re: Upgrading thor

    Code:
    [!!] Joins performed without indexes: 3284
    [!!] Temporary tables created on disk: 33% (6M on disk / 20M total)
    Are the two that caught my eye as especially the first will slow things down, but it would be nice to know what percentage of total joins that is. I wouldn't try fixing either of those prior to upgrading to vb4 as I know that vb4 adds a number of indices and other things to the database.

    Under the patronage of Roman_Man#3, Patron of Ishan
    Click for my tools and tutorials
    "Two things are infinite: the universe and human stupidity; and I'm not sure about the universe." -----Albert Einstein

  17. #17
    GrnEyedDvl's Avatar Barackolypse Now
    Artifex Technical Staff

    Join Date
    Jan 2007
    Location
    Denver CO
    Posts
    22,390

    Default Re: Upgrading thor

    Yeah I saw those too. Those have dropped a bit since last night, but it reset everything when I restarted mysql last night. After a few days we will see if its climbing back up. Here is the most recent result.

    >> MySQLTuner 1.2.0 - Major Hayden <[email protected]>
    >> Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >> Run with '--help' for additional options and output filtering
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.67-0ubuntu0.10.04.1-log
    [OK] Operating on 64-bit architecture
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 82M (Tables: 6)
    [--] Data in InnoDB tables: 29G (Tables: 301)
    [--] Data in MEMORY tables: 1M (Tables: 5)
    [!!] Total fragmented tables: 85
    -------- Security Recommendations -------------------------------------------
    [OK] All database users have passwords assigned
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 7h 0m 36s (9M q [364.645 qps], 440K conn, TX: 188B, RX: 2B)
    [--] Reads / Writes: 79% / 21%
    [--] Total buffers: 16.6G global + 11.2M per thread (600 max threads)
    [OK] Maximum possible memory usage: 23.2G (73% of installed RAM)
    [OK] Slow queries: 0% (5K/9M)
    [OK] Highest usage of available connections: 60% (362/600)
    [OK] Key buffer size / total MyISAM indexes: 256.0M/76.8M
    [OK] Key buffer hit rate: 99.5% (1M cached / 6K reads)
    [OK] Query cache efficiency: 65.6% (4M cached / 7M selects)
    [!!] Query cache prunes per day: 477973
    [OK] Sorts requiring temporary tables: 0% (1K temp sorts / 564K sorts)
    [!!] Joins performed without indexes: 76
    [!!] Temporary tables created on disk: 26% (143K on disk / 544K total)
    [OK] Thread cache hit rate: 99% (362 created / 440K connections)
    [OK] Table cache hit rate: 55% (1K open / 2K opened)
    [OK] Open file limit used: 0% (86/11K)
    [OK] Table locks acquired immediately: 99% (6M immediate / 6M locks)
    [!!] InnoDB data size / buffer pool: 29.2G/16.0G
    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
    query_cache_size (> 128M)
    join_buffer_size (> 1.0M, or always use indexes with joins)
    innodb_buffer_pool_size (>= 29G)
    I abused my root privileges and copied the tuner script into your home folder so its easy for you to run as well. Just use this command:

    ./mysqltuner.pl

    Enter your mysql credentials when prompted, and it will start. It takes 30-45 seconds or so to run.


    Notice this line:
    [OK] Maximum possible memory usage: 23.2G (73% of installed RAM)
    Originally it was 10.9G, I was shooting for 16-18G so I have 14-16G for lighttpd and the OS. I will let it run like this for a while anyways, but some of those changes I made last night should probably be dropped a bit. If we start seeing a bunch of page file activity then I will have to make changes.
    Last edited by GrnEyedDvl; January 22, 2013 at 03:07 PM.

  18. #18
    GrnEyedDvl's Avatar Barackolypse Now
    Artifex Technical Staff

    Join Date
    Jan 2007
    Location
    Denver CO
    Posts
    22,390

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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