User:Daveh/Server Upgrade 2020

This page documents the server cluster upgrade performed in 2020.

Transition Theory and Notes[edit]

Overview[edit]

The following basic components have to be moved:

Databases
Content Servers
File Server
Squid Cache
Wiki Search Index
Zabbix Monitor
Backup Scripts
Uptime.com

OS[edit]

Use ulimit -a to check number of open files permitted.
keys
super user
denyhosts

Download https://github.com/denyhosts/denyhosts/archive/master.zip
Install Python components:

    yum install python python-ipaddr python-requests python-configparser

Install DenyHosts:

    python setup.py install
    ln -s /usr/share/denyhosts/daemon-control /etc/init.d/denyhosts
    systemctl enable denyhosts

Copy /etc/denyhosts.cfg config.

fail2ban

yum install fail2ban
Copy /etc/fail2ban/* config.

uptime.com
Changes:

iptables to firewalld
Ban IP:

    firewall-cmd --permanent --add-rich-rule="rule family='ipv4' source address='180.76.15.154' reject"

Ban Range:

    firewall-cmd --permanent --add-rich-rule="rule family='ipv4' source address='180.76.15.0/24' reject"

No Root Login

Change /etc/ssh/sshd_config line to read PermitRootLogin without-password and restart sshd.

Database[edit]

Open MySQL port 3306 on both firewalls between clusters.
Switch Wiki (and other services) to read from DB1 instead of DB2.
Stop slave on DB2. Note master read file and position.
Backup all databases (except mysql, percona, and information_schema).
Copy to new db1/db2/backup1 and restore.
Start mirroring on new db1 from old db1.
Start mirroring on new db2/backup1 from new db1.
Copy Users and Permissions

Can export with mysqldump -h db1.uesp.net -u backup -p mysql --extended-insert=FALSE --tables user db > users.sql but this drops the tables and recreates. Removing the drops doesn't work as new tables have different columns.
Can use pt-show-grants from Percona Toolkit to export users/grants but need to wait until databases are restored in order to use the script.

Add default-storage-engine=myisam to config file.
Modify open files limit:

Set open-files-limit=4000 in the [mysqld_safe] section of the MySQL config file.
Edit /etc/systemd/system/mariadb.service.d/limits.conf to include:

    [Service]
    LimitNOFILE=10000

Edit /etc/systemd/system.conf to include:

    DefaultLimitNOFILE=infinity
    DefaultLimitMEMLOCK=infinity

Run: systemctl daemon-reload
Run: service mariadb restart
Check setting in MySQL with show global variables like 'open_files_limit';.

Faster Database Restores[edit]

innodb_file_per_table=1
skip-name-resolve
key_buffer_size (for MyISAM tables) to ~25% of your RAM
innodb_buffer_pool_size to 50-75% of RAM
innodb_log_buffer_size (default of 8MB is probably ok)
innodb_log_file_size (default of 50MB is probably ok)
innodb_write_io_threads (default of 4 probably ok)
Temporarily set variables in my.ini:

innodb_flush_method = nosync
innodb_flush_log_at_trx_commit = 2

Default CentOS 7 Installs[edit]

mariadb = 5.5.64
squid = 3.5.20
apache = 2.4.6
php = 5.4.16 (need 5.6)
lighttpd = 1.4.47
zabbix = 2.2.23 (current 2.4.6)

Squid[edit]

New cache_dir Type

The coss store type was removed in 3.0.
Rock Disk Format meant for high performance caches
max-swap-rate = 200/s
swap-timeout start at 300ms
cache_dir rock /var/spool/squid 100000 max-swap-rate=200 swap-timeout=300

Other Performance Tuning Settings:

cache_mem = 4000MB
maximum_object_size_in_memory = 512KB

v3 Migration

cache_vary does not exist in Squid 3.5.
all acl is now built-in and will emit warnings if redefined.

Apache[edit]

2.2 to 2.4 Migration

Order... and Deny replaced by Require.... Use mod_access_compat to support old style format (do not mix with new style).
MaxRequestsPerChild to MaxConnectionsPerChild
MaxClients to MaxRequestWorkers
NameVirtualHost is deprecated and has no effect.

Modify conf.d/evasive.conf with new cluster IPs.
wikidiff2 PHP module

https://releases.wikimedia.org/wikidiff2/
Note that v1.10 and higher require PHP 7+.
Use 1.9.0 (previous version was 1.8.2).

Wiki Job Runner

Hardware Firewall[edit]

OldUespServers to Any (IP)

Permit connections from any of the old/existing UESP servers using public IPs.

MySQL IPs to Any (3306)

Dave/RobinHood public IP ranges for MySQL access (notably backup2).

Any to Any (MineCraft: 25565, 27005, 27015)
Any to Any (9090)?

Was only used for testing something at some point.

Any to Any (Teamspeak: 10911, 30033, 8087, 9987)
Any to Any (Lucene: 8123)?

No longer used.

Any to Any Deny (MySQL: 3306)

Varnish[edit]

Purge all objects in cache using varnishadm

ban req.http.host ~ uesp.net

Other[edit]

ElasticSearch

Move from files1 to content3.
Copy everything from old files1.

Services[edit]

Internal

Database = db1/db2/backup1
Memcached = content1
ElasticSearch = content3
Zabbix = backup1
Wiki Job Runner = content1/2/3

External

Load Balanced/Cache = squid1
Content/Apache = content1/2/3
Images = files1
TeamSpeak = content3
ESO Log Parser = content3
ESO Skill/CP/Item Tooltips = content3

Sites

UESP Wiki = content1/2/3
UESP Dev Wiki = content3 or backup1?
UESP Blog = content1
UESP Forums = content1
UESP Wiki Mobile = content1/2/3
UESP Short Link = content3
Beyond Skyrim = content2
DungeonHack = content1
Oblivion Character Planner (OCP) = content1
TamrielRebuilt = content1
ESO API = content2
ESO Log = content3
ESO Skills/CP/Items = content3
ESO Asayre = content3
ESO Character/Builds = content3
ESO Clock = content3
ESO Sales = content3
EQWiki = content1
Dave Wiki = content3
Emergent Game Design Blog = content3
Legends Cards/Raw Data = content3

New Server Upgrade History[edit]

Basic Status[edit]

Content1

Setup: Done
Copy /home: Done

Content2

Setup: Done
Copy /home: Done

Content3

Setup: Done
Copy /home: Done

Squid1

Setup: Done
Copy /home:

Db1

Setup: MySQL setup and mirroring from old db1
Copy /home: Done

Db2

Setup: MySQL setup and mirroring from new db1
Copy /home: Done

Files1

Setup: Done
Copy /home: Done

Backup1

Setup: MySQL mirroring from new db1, Zabbix server done (missing some templates)
Copy /home: Done

Backup2

Setup: MySQL mirroring from new db1, needs check/update on backups

Firewall

Setup: Done

Squid1[edit]

Run ./uesp-setup-content content1 10.12.222.25
yum install squid
yum install fail2ban
Install and test Squid...make config changes to get working.
Disable Squid.
Install Varnish/Nginx. Setup and get working using old content3 as backend for now.
Run https://www.ssllabs.com/ssltest/index.html and tweak SSL settings as needed.
Rerun: uesp-create-users
Rerun: uesp-create-localkey squid1
Copy public key to new content3 /etc/uesp-keys/.
Copy /etc/uesp-keys/ from new content3 (except localhost).
Run: uesp-update-keys
Run: uesp-update-keys dave
Setup random load balancer between content1/2 and optionally content3.
Disable root login and reset root password (ensure there's at least one other user with password set).

Content1[edit]

Run ./uesp-setup-content content1 10.12.222.25
Run ./uesp-setup-memcached
Rerun: uesp-create-users
Rerun: uesp-create-localkey content1
Copy public key to new content3 /etc/uesp-keys/.
Copy /etc/uesp-keys/ from new content3 (except localhost).
Run: uesp-update-keys
Run: uesp-update-keys dave
Copy /home/ from old content1.
Delete old files from .ssh for users dave and uespkey' and update authorized_keys.
Modify secrets file with new server LAN addresses.
Modify wiki config with new server LAN addresses.
Create /imagetmp and set ownership to apache:apache.
Test/benchmark wiki.
Run https://www.ssllabs.com/ssltest/ and make changes to config as needed.
Copy /etc/sudoers.d/ from old server.
Disable root login and reset root password (ensure there's at least one other user with password set).

Content2[edit]

Run ./uesp-setup-content content2 10.12.222.25
Rerun: uesp-create-users
Rerun: uesp-create-localkey content2
Copy public key to new content3 /etc/uesp-keys/.
Copy /etc/uesp-keys/ from new content3 (except localhost).
Run: uesp-update-keys
Run: uesp-update-keys dave
Copy /home/ from old content2.
Delete old files from .ssh for users dave and uespkey' and update authorized_keys.
Modify secrets file with new server LAN addresses.
Modify wiki config with new server LAN addresses.
Create /imagetmp and set ownership to apache:apache.
Test/benchmark wiki.
Run https://www.ssllabs.com/ssltest/ and make changes to config as needed.
Copy /etc/sudoers.d/ from old server.
Disable root login and reset root password (ensure there's at least one other user with password set).

Content3[edit]

Note: This was first server done so many script errors were fixed and re-run.
Run ./uesp-setup-content content3 10.12.222.25
ElasticSearch

yum install java-1.8.0-openjdk
Copy files1:/home/uesp/elasticsearch/ to new content3 (ignore logs).
Copy /etc/init.d/elasticsearch to new content3.
chkconfig --add elasticsearch
chkconfig --level 345 elasticsearch on
Quick Benchmarks

Using: ab -kc 10 -t 3 http://localhost:9200/uesp_net_wiki5_general/_search?q=vampire
From newcontent3: 13,500 req/sec
From newcontent2: 11,400 req/sec
Old files1: 1100-2000 req/sec

Run: ssh-keyscan newcontent1.uesp.net newcontent2.uesp.net newcontent3.uesp.net newdb1.uesp.net newdb2.uesp.net newfiles1.uesp.net newsquid1.uesp.net newbackup1.uesp.net > /etc/ssh/ssh_known_hosts
Copy /etc/ssh/ssh_known_hosts to all other servers.
Rerun: uesp-create-users
Rerun: uesp-create-localkey content3
Copy all public keys from other servers to /etc/uesp-keys/.
Copy /etc/uesp-keys/ to all other servers (except localhost).
Delete old files from .ssh for users dave and uespkey' and update authorized_keys.
Run: uesp-update-keys
Run: uesp-update-keys dave
Copy /home/ from old content3.
Modify secrets file with new server LAN addresses.
Modify wiki config with new server LAN addresses.
Create /imagetmp and set ownership to apache:apache.
Test/benchmark wiki.
Run https://www.ssllabs.com/ssltest/ and make changes to config as needed.
Start and test TeamSpeak server. Modify init.d script to work with chkconfig.
Get TeamSpeak running according to https://www.vultr.com/docs/how-to-install-teamspeak-3-server-on-ubuntu-16-04-64-bit
Copy /etc/sudoers.d/ from old server.
Copy/update the dev wiki images and wiki code.
Create /deploytmp
Disable root login and reset root password (ensure there's at least one other user with password set).

Backup1[edit]

Run ./uesp-setup-content backup1 10.12.222.25
yum install mariadb-server
systemctl enable mariadb
systemctl start mariadb
mysql_secure_installation
Run ./uesp-install-zabbixserver 10.12.222.25

Create zabbix database: create database zabbix character set utf8 collate utf8_bin;
Create zabbix user in MySQL: grant all privileges on zabbix.* to zabbix@localhost identified by '...';
zcat /usr/share/doc/zabbix-server-mysql-4.4.6/create.sql.gz | mysql -u root -p zabbix
Update /etc/zabbix/zabbix_server.conf with database password.
Update /etc/httpd/conf.d/zabbix.conf with valid timezone America/Toronto.
apachectl restart
systemctl start zabbix-server
Browse to http://newbackup1.uesp.net/zabbix/ and complete installation.
Login with Admin/zabbix and change password.

ln -sf /var/log/mariadb /var/log/mysql
mkdir /mysqltmp
chown mysql:mysql /mysqltmp
Copy /etc/my.cnf.d/uesp.cnf from new db1 to new backup1. Modify server-id variable. Comment out innodb_log_file_size line.
Run set global innodb_fast_shutdown=0 and stop mysql.
Run mv /var/lib/mysql/ib_logfile? /tmp. Uncomment innodb_log_file_size line and start mysql.
Rerun: uesp-create-users
Rerun: uesp-create-localkey backup1
Copy public key to new content3 /etc/uesp-keys/.
Copy /etc/uesp-keys/ from new content3 (except localhost).
Delete old files from .ssh for users dave and uespkey' and update authorized_keys.
Run: uesp-update-keys
Run: uesp-update-keys dave
Import database dump from old db2.
Comment out import specific settings and restart MySQL.
Start mirroring from new db1.
Copy all non-root grants from old content3.
Copy /etc/sudoers.d/ from old server.
Dump/restore latest wiki to the uesp_net_wikidev database.
Setup all cron scripts.
Disable root login and reset root password (ensure there's at least one other user with password set).

Db1[edit]

Run ./uesp-setup-content db1 10.12.222.25
yum install mariadb-server
systemctl enable mariadb
systemctl start mariadb
mysql_secure_installation
ln -sf /var/log/mariadb /var/log/mysql
mkdir /mysqltmp
chown mysql:mysql /mysqltmp
Copy /etc/my.cnf from old db1 to new db1 at /etc/my.cnf.d/uesp.cnf
Edit uesp.cnf to as needed.
Restart mysql.
Copy /home/ from old db1 to new.
Install Percona Toolkit

Download percona toolkit from https://www.percona.com/downloads/percona-toolkit/LATEST/
yum install perl-Digest-MD5
yum install perl-IO-Socket-SSL
rpm -ivh percona-toolkit-3.1.0-2.el7.x86_64.rpm

Run pt-variable-advisor localhost -u root --ask-pass and tweak settings as needed.
Install and run https://github.com/major/MySQLTuner-perl and tweak settings as needed.
Rerun: uesp-create-users
Rerun: uesp-create-localkey db1
Copy public key to new content3 /etc/uesp-keys/.
Copy /etc/uesp-keys/ from new content3 (except localhost).
Run: uesp-update-keys
Run: uesp-update-keys dave
Import database dump from old db2.
Comment out import specific settings and restart MySQL.
Start mirroring from old db1.
Copy all non-root grants from old db1.
Copy MySQL grants for the uespdeploy user from old content3.
Backup and restore the uesp_deploy database from old content3.
Disable root login and reset root password (ensure there's at least one other user with password set).

Db2[edit]

Run ./uesp-setup-content backup1 10.12.222.25
yum install mariadb-server
systemctl enable mariadb
systemctl start mariadb
mysql_secure_installation
ln -sf /var/log/mariadb /var/log/mysql
mkdir /mysqltmp
chown mysql:mysql /mysqltmp
Copy /etc/my.cnf.d/uesp.cnf from new db1 to new db2. Modify server-id variable. Comment out innodb_log_file_size line.
Restart mysql.
Run set global innodb_fast_shutdown=0 and stop mysql.
Run mv /var/lib/mysql/ib_logfile? /tmp. Uncomment innodb_log_file_size line and start mysql.
Copy /home from old db2.
Rerun: uesp-create-users
Rerun: uesp-create-localkey db2
Copy public key to new content3 /etc/uesp-keys/.
Copy /etc/uesp-keys/ from new content3 (except localhost).
Run: uesp-update-keys
Run: uesp-update-keys dave
Import database dump from old db2.
Comment out import specific settings and restart MySQL.
Start mirroring from new db1.
Copy all non-root grants from old db2.
Copy MySQL grants for the uespdeploy user from old content3.
Disable root login and reset root password (ensure there's at least one other user with password set).
Update open files limit:

Create/edit /etc/systemd/system/mariadb.service.d/limits.conf

      [Service]
      LimitNOFILE=320000

Reload with: systemctl daemon-reload
Modify the /etc/my.cnf.d/uesp.cnf file with:

      [mysqld_safe]
      open_files_limit=6000

Restart mysql
Check open files with: show variables like 'open%';

Files1[edit]

Run ./uesp-setup-common files1 10.12.222.25
Run ./uesp-install-lighttpd
Run ./uesp-setup-shares
Copy config from old files1 (no changes needed other than 2 deprecated settings).
Copy /shared from new backup1.
Start/enable lighttpd.
Run https://www.ssllabs.com/ssltest/index.html and tweak SSL settings as needed.
Rerun: uesp-create-users
Rerun: uesp-create-localkey files1
Copy public key to new content3 /etc/uesp-keys/.
Copy /etc/uesp-keys/ from new content3 (except localhost).
Run: uesp-update-keys
Run: uesp-update-keys dave
Copy /etc/sudoers.d/ from old server.
Disable root login and reset root password (ensure there's at least one other user with password set).

Comparisons and BenchMarks[edit]

Server Stats[edit]

Basic Setup: Unchanged from prior cluster although a physical switch was replaced with a VPN for LAN connections.

WAN: upgraded from 100 Mbps to 1Gpbs
LAN: 1Gpbs
Number: 8
Content Servers

RAM: Upgraded from 8GB to 32GB
CPU: Upgraded from Intel i5-2500 CPU @ 3.30GHz (x4 @ 6600 BMips) to Xeon E3-1270 v6 @ 3.80GHz (x8 @ 7600 BMip)
Disks: Upgraded from 1TB to 2TB

DB Servers

RAM: Upgraded from 24GB to 64GB
CPU: Xeon E5645 @ 2.40GHz (x24 @ 4800 Bmips) to Xeon E5-2620 v3 @ 2.40GHz (x24 @ 4800 Bmips)
Disks: Upgraded from 1TB to 2TB

File Server

RAM: Upgraded from 8GB to 16GB
CPU: Upgraded from Intel i5-2500 CPU @ 3.30GHz (x4 @6600 BMips) to Xeon E3-1270 v6 @ 3.80GHz (x8 @7600 BMip)
Disks: Upgraded to from 1TB HDD to 480GB SSD

Software[edit]

CentOS 6.6 to 7.7
Apache 2.2 to 2.4
PHP 5.6
MySQL 5.1 to 5.5
Squid 2.4 to Varnish 4.0/Nginx 1.16
Lighttpd 1.4
Zabbix 2.4 to 4.4

Benchmarks[edit]

General Notes

Make sure to turn off mod_evasive and fail2ban to ensure requests are not banned when running Apache Bench (ab).
Apache Bench (ab) doesn't work with HTTPS.

Google Stats

Server Response Time

Old: 0.20 sec
New: 0.28 sec (x0.7)
No obvious reason why new servers have a notably longer response time (Apache 2.4?).

Page Load Time

Old: 3.8 sec
New: 3.0 sec (x1.3)

Page Download Time

Old: 0.15 sec
New: 0.05 sec (x3)

Page Speed Log

Average values over 1-2 weeks used.
Content1

Old: 165 ms
New: 55 ms (x3)

Content2

Old: 267 ms
New: 57 ms (x4.7)

Content3 (All Requests)

Old: 440 ms
New: 260 ms (x1.7)

Content1 (Only External Requests)

Old: 250 ms
New: 92 ms (x2.7)

Server Load

Old Typical: 6-8
Old Spikes: 12-14
Old Capacity: 56 (total number of cores)
New Typical: 3-4
New Spikes: 6-8
New Capacity: 96 (x1.7, total number of cores)

BogoMips (from /proc/cpuinfo)

Old Total: 312,000
New Total: 595,200 (x1.9)

Database Dumps

Old Backup1

Backup + ZIP on DB2: 160m, 17GB

Database Restore

Gunzip: 25 min
DB Restore on new db1/db2: 382 / 357 min
DB Restore on Backup2: 1030 min (17.2 hours)

New Backup1

UESP Wiki Dump: 14min + 1min gzip, 8GB zipped
UESP Wiki Restore: 33GB unzipped, 48 min (without changing import settings)
All DBs Dump: 54min, 17GB zipped, 40 dbs

All Single DBs Dump: 54min, total of 17GB zipped, 41 dbs

Cache Hit Rate

Old Squid: 65.4%
Varnish: 60-70% typical, varies down to 40% for short periods

Content Serving

Tests done using Apache Bench (ab) with HTTP from backup1 (within the relevant cluster):

   ab -kc 10 -t 30 URL

Old

Content1

Main_Page: 28 req/s
RecentChanges: 13 req/s

Content2

Main_Page: 15 req/s
RecentChanges: 7 req/s

Content3

Main_Page: 34 req/s
RecentChanges: 15 req/s

Files1

Somerights.png (6kb): 9,800 req/s

Squid1

Main_Page: 34 req/s (Not Caching?)
RecentChanges: 16 req/s (Not Caching?)

New

Content1

Main_Page: 63 req/s (x2.3)
RecentChanges: 30 req/s (x2.3)

Content2

Main_Page: 62 req/s (x4.1)
RecentChanges: 32 req/s (x4.6)

Content3

Main_Page: 70 req/s (x2.1)
RecentChanges: 30 req/s (x2.0)

Files1

Somerights.png (6kb): 20,600 req/s (x2.1)

Squid1

Main_Page: 2800 req/s
RecentChanges: 1300 req/s

Notable Changes[edit]

Backup MySQL Server

Backup MySQL moved from content3 to backup1.
Backup1 is mirroring from the new db1.
Dev wiki and Zabbix databases are served only from backup1.

Zabbix Server

Zabbix server moved from content3 to backup1.
Database is also on backup1.

ElasticSearch

ElasticSearch index moved from files1 to content3.

Squid Cache

Squid cache changed to Varnish/Nginx.
Nginx is used just for HTTPS requests and forwards to Varnish.
Varnish only handles HTTP requests and forwards to content1/2 (and optionally content3 if needed).
Varnish uses a URL hash-based load balancer which means a particular page will always be served by the same server.
Currently has a 1 hour grace period to server expired content if the backend is down (not test).

HTTPS Auto-Forwarding

No longer automatically forwarding HTTP requests to HTTPS
This is due to change in page cache (see above). The Varnish cache only deals with HTTP so the content servers only ever see HTTP requests.
Doing the usual Redirect to HTTPS on the content servers causes an infinite loop of redirects:

Varnish receives HTTP request
Varnish forwards HTTP to Apache content server
Content server issues HTTPS redirect
Nginx receives the HTTPS request from redirect
Nginx terminates SSL and forwards a plain HTTP request to Varnish
Goto Line 1

Post Upgrade Issues/Notes[edit]

Intermittent Wiki Load Issue -- Mod evasive on content2 didn't have the new local server IPs set so it was banning squid1.
Failed to Upload Image File (Error 413) -- Nginx needed to have the client_max_body_size setting increased to 64M and reloaded on squid1.2020 (GMT)
Anonymous Edits Show as From 127.0.0.1 -- Was an issue with the Varnish configuration which has now been fixed (had to remove extra X-Forwarded-For entries).
Mobile Skin Not Working -- Had to redo how the site redirects to the X.uesp.net or X.m.uesp.net sub-domains. There were some cases where the two could become mixed up and serve the wrong content. Also had to tweak the /wikiredirect.php script to work correctly in all cases.
Old file.shtml Not Working -- The old file download SHTML file results in an error. Had to enable SSI on the main domain and update the file with a few fixes to prevent errors.
Php Mail Error -- Users receive a can't write to /var/log/phpmail.log when using the password reset form. Change log directory to /var/log/httpd/phpmail.log in /etc/php.d/00-php.ini.

To Do[edit]

Include engine type in table creation queries to avoid issue of default engine changing.

    CREATE TABLE x(...) ENGINE=MYISAM;

Look into the Scribunto extension.
Look into enabling WEBM/WEBP animation uploads/images.
UespMemcachedSession.php should use the server IPs defined in the uespserver.secrets file.
Put wiki config into its own repository. Add detection support for content3/dev/backup1.
Update old wiki pages:

References[edit]

General[edit]

https://www.howtoforge.com/nfs-server-and-client-on-centos-7
https://www.zabbix.com/documentation/current/manual/installation/install_from_packages/rhel_centos
https://www.vultr.com/docs/how-to-install-teamspeak-3-server-on-ubuntu-16-04-64-bit
Parse average page load time from page speed log:

    awk -F',' '{ total += $2; count++ } END { print total/count }' pagespeed.log

Parse average page load time from page speed log ignoring blank URIs (needed on content3):

    awk -F',' '{ if ($3 != " ") { total += $2; count++ }} END { print total/count }' pagespeed.log