Upgrading thor

[GrnEyedDvl]

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.

[GrnEyedDvl]

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

[GrnEyedDvl]

Loading database backups to /data/backups

[GrnEyedDvl]

fast-cgi is installed with the basic config.

[Mangerman]

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

[GrnEyedDvl]

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

[GrnEyedDvl]

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.

[General Brewster]

Thanks for your work GED.
and Squid too.

[GrnEyedDvl]

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.

[GrnEyedDvl]

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.

[GrnEyedDvl]

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.

[GrnEyedDvl]

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

[GrnEyedDvl]

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.

[GrnEyedDvl]

[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.

[GrnEyedDvl]

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.

[Squid]

[!!] 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.

[GrnEyedDvl]

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 <major@mhtx.net>
>> 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.

[GrnEyedDvl]

Squid, these are a good read.

http://www.mysqlperformanceblog.com/...zation-basics/
http://www.mysqlperformanceblog.com/...fer_pool_size/