UESPWiki:Server Upgrade/August 2008

Overview

The goal of this upgrade is to seperate the current content1 server into two servers, a content and a database server, with zero visible downtime. Other servers (content, squid, utility, etc...) may be moved around, upgraded, or reconsolidated in the process to help improve performance and reduce costs.

New Servers

The following quickly reviews a few of the currently available dedicated servers from iWeb.

Database Server

A decent CPU and RAM is primarily needed for the DB server. Disk space and traffic are not particularily important.

A: Core2 Duo Budget Server #1

• CPU = Core2 Duo 2.4Ghz
• RAM = 2GB RAM
• Hard Drive = 160GB SATA2
• Traffic = 1500GB
• Monthly Fee = 114.45$
• 12-Month Fee = 1236.06$ (103.00$/month)
• 24-Month Fee = 2060.10$ (85.84$/month)

B: Core2 Duo Budget Server #2

• CPU = Core2 Quad 2.4GHz
• RAM = 4GB RAM
• Hard Drive = 160GB SATA2
• Traffic = 1500GB
• Monthly Fee = 172.20$
• 12-Month Fee = 1859.76$ (154.98$/month)
• 24-Month Fee = 3099.60$ (129.15$/month)

C: Quad Core Power Server

• CPU = Core2 Quad 2.4GHz
• RAM = 2GB RAM
• Hard Drive = 320GB SATA2
• Traffic = 2000GB
• Monthly Fee = 166.95$
• 12-Month Fee = 1803.06$ (150.26$/month)
• 24-Month Fee = 3005.10$ (125.21$/month)

Cache Server

An upgrade for the cache server would only likely be for monthly traffic since we are now exceeding the current 1500GB/month plan. Adding more RAM is also an option. Hopefully, upgrading traffic will not require the purchase of a new server. Any other upgrade, however, will require a new server.

D: Existing Server

• CPU = Celeron 2.4GHz
• RAM = 2GB
• Disk = 320GB
• Traffic = 1500GB
• Monthly Fee = ~100$

E: 2000GB/Month

• Keeps same server as above
• Additional Cost = 20$/month
• Each Additional 1000GB is 100$

F: Low Price Server #1

• CPU = Celeron 2.4Ghz
• RAM = 2GB RAM
• Hard Drive = 160GB SATA2
• Traffic = 2000GB
• Monthly Fee = 92.95$
• 12-Month Fee = 1003.86$ (83.66$/month)
• 24-Month Fee = 1673.10$ (69.71$/month)

G: Low Price Server #2

• CPU = Celeron 2.4Ghz
• RAM = 2GB RAM
• Hard Drive = 160GB SATA2
• Traffic = 3000GB
• Monthly Fee = 128.70$
• 12-Month Fee = 1404.00$ (117.00$/month)
• 24-Month Fee = 2316.60$ (96.53$/month)

Utility Server

A utility server would be used for backups, email, testing, and possibly serving low-traffic content (e.g., forums).

H: Low Price Server #1

• CPU = Celeron 2.4Ghz
• RAM = 1GB RAM
• Hard Drive = 160GB SATA2
• Traffic = 1500GB
• Monthly Fee = 72.45$
• 12-Month Fee = 782.46$ (65.21$/month)
• 24-Month Fee = 1304.10$ (54.34$/month)

Combination Server

While we're trying to separate the content and db server we should consider just keeping them together in a better server.

I: Power Server

• CPU = Quad2 Core 2.4Ghz
• RAM = 8GB RAM
• Hard Drive = 320GB SATA2
• Traffic = 2000GB
• Monthly Fee = 235.20$
• 12-Month Fee = 2540.16$ (211.68$/month)
• 24-Month Fee = 4233.60$ (176.40$/month)

Upgrade Plans

Below are listed a few possible upgrade plans using the existing servers and the new servers listed above.

Existing Servers

• squid1/content1 are on a monthly plan, backup1 is a cheap VPS on a 24 month plan.
• Total Costs = ~240.00$/month

Plan #1

• Keep existing squid1, content1, and backup1.
• Purchase server A as db1.
• Upgrade existing squid1 traffic to 2000GB/month
• New Cost = 2300.16$ for 24 months (105.84$/month)
• Total Cost = 8300.16$ for 24 months (345.84$/month)

Plan #2

• Keep existing content1
• Purchase server A as db1.
• Purchase server F as new squid1.
• Use old squid1 as backup1/util1.
• New Cost = 3733.20$ for 24 months (155.55$/month)
• Total Cost = 8893.2$ for 24 months (370.55$/month)

Plan #3

• Purchase server A as new content1
• Purchase server A as db1.
• Purchase server F as new squid1.
• Use old content1 as backup1/util1.
• New Cost = 5793.36$ for 24 months (241.39$/month)
• Total Cost = 8553.36$ for 24 months (356.39$/month)

Plan #4

• Purchase server A as new content1
• Purchase server A as db1.
• Purchase server F as new squid1.
• Purchase server H as new backup1/util1.
• New Cost = 7097.52$ for 24 months (295.73$/month)
• Total Cost = 7097.52$ for 24 months (295.73$/month)

Plan #5

• Upgrade squid1 monthly traffic to 2000GB/month.
• Purchase server I as new content1/db.
• New Cost = 4713.60$ for 24 months (196.40$/month)
• Total Cost = 7113.60$ for 24 months (296.40$/month)

Decision

Without too much discussion the current upgrade will be closest to Plan #1. This has the least number of changes and addresses the most important server issues at the moment. The content1/squid1/backup1 servers can be upgraded piece by afterwards to reduce the overall site costs.

Moving the Database

A few methods of moving the database from content1 to db1 with minimum downtime are discussed below.

Slave Replication

• Setup db1 as slave to content1.
• Lock content1 database and ensure db1 replication is complete.
• Shutdown mysql on db1 and stop replication on a single database.
• Switch relevant resources to use the database from db1.
• Unlock content1 database and restart MySQL on db1.

Server Setup

Required Software

• Mysql 5.0
• Apache (for phpMyAdmin)
• PHP 5

Databases

1. dhackforum
2. dhackwiki
3. eqwiki
4. mw_map_data
5. ob_map_data
6. si_map_data
7. uesp_map_data (no longer needed?)
8. uesp_net_counter
9. uesp_net_phpbb
10. uesp_net_wiki5

Database Server Locations

Code locations where the db server is explicitly specified:

• MediaWiki LocalSettings.php (all Wikis)
• PHPBB config.php (all forums)
• getmaplocs.php and putmaplocs.php (all UESP maps)
• EQ items/spells database files
• Old site counter page.

Database Accounts/Permissions

The following are the required db users and permission levels (other than the default users).

• backup -- SELECT, LOCK TABLES, RELOAD, SUPER, REPLICATION CLIENT on all databases
• uespcounter -- SELECT, INSERT, UPDATE, on uespcounter
• maps -- SELECT on all map databases
• mapsedit -- SELECT, UPDATE, INSERT on all map databases
• slave -- REPLICATION SLAVE on all databases
• eqitems -- SELECT, INSERT, UPDATE, DELETE on eqwiki (specifically the spells/items tables)
• eqwiki -- CREATE, SELECT, INSERT, UPDATE, DELETE, ALTER, LOCK TABLES on eqwiki database
• dhack -- CREATE, SELECT, INSERT, UPDATE, DELETE, ALTER, LOCK TABLES on DungenHack forum and wiki databases
• uesp -- CREATE, SELECT, INSERT, UPDATE, DELETE, ALTER, LOCK TABLES on all UESP related databases

Status

• Squid1 traffic upgrade to 2000GB/month ordered. -- Daveh 21:17, 31 July 2008 (EDT)
• New db1 server ordered (same as #A but with 4GB RAM). -- Daveh 01:07, 1 August 2008 (EDT)
• db1 server received! -- Daveh 21:24, 1 August 2008 (EDT)
• db1.uesp.net DNS entry made and tested. -- Daveh 10:57, 2 August 2008 (EDT)
• MySQL setup on db1. Waiting for lower site traffic before backing up DB on content1 for initial transfer todb1. -- Daveh 19:02, 2 August 2008 (EDT)
• Full backup of databases on content1. -- Daveh 09:33, 3 August 2008 (EDT)
• Restored backup on db1 and started replication. -- Daveh 13:33, 3 August 2008 (EDT)
• eqwiki and dhackwiki databases switched to using db1. Seems to be working fine. -- Daveh 14:27, 3 August 2008 (EDT)
• UESP and DHack forums now running off of db1. -- Daveh 15:05, 3 August 2008 (EDT)
• All maps now using the db1 server (I think). -- Daveh 15:56, 3 August 2008 (EDT)
• Old site counter now using the db1 server. Only main Wiki is using original content1 database. -- Daveh 16:10, 3 August 2008 (EDT)
• Formums displayed error message. Increased max_connections from default to 1000 on db1 and flushed connections. -- Daveh 18:49, 3 August 2008 (EDT)
• Still had the previous error after a while. Increased max_connect_errors from the default 10 to 10000 as recommended. -- Daveh 10:59, 4 August 2008 (EDT)
• Fixed permission issues on db1 with the uespcounter user. -- Daveh 14:04, 4 August 2008 (EDT)
• The searchindex table on the DHack wiki was corrupt. Repaired and tested sucessfully. -- Daveh 17:37, 6 August 2008 (EDT)
• Setup initial db backup and rotation scripts on db1. Will monitor and correct as needed. -- Daveh 00:26, 7 August 2008 (EDT)
• Added accounts and relevant permission levels for the different sites. -- Daveh 17:00, 9 August 2008 (EDT)
• Switched the UESP Wiki to using the db1 server. About 1 minute of downtime and 10 minutes of lock time. -- Daveh 11:43, 10 August 2008 (EDT)
• Switched Wiki back to content1 database after noting it wasn't actually using the db1 server. -- Daveh 14:00, 10 August 2008 (EDT)