Ten dollars a month buys a machine with 1 GB of RAM, an IP address and nothing else on it. No control panel, no PHP, no web server — just Ubuntu 14.04 and the SSH key you pasted in during checkout. By the end of this it will be running nginx, PHP-FPM and MySQL, configured for the memory the machine actually has rather than the memory the default config assumes it has.
The symptom
The application that finally pushed me off shared hosting was a product catalogue. Nothing exotic: about 40,000 rows, a filtered listing page, four or five joins. Rendering took between four and eleven seconds, and roughly one request in twenty did not finish at all.
PHP Fatal error: Maximum execution time of 30 seconds exceeded
in /home/u2841/public_html/lib/Catalogue.php on line 214The host’s support reply was accurate and completely useless: the query was slow, and I should optimise it. Which query? They could not say. I could not read the slow query log, because I did not have one. I could not raise max_execution_time, because php.ini was not mine. I could not install an opcode cache. Every instrument that would have told me why it was slow lived on the other side of a wall.
Why it happens
Shared hosting sells you a directory, not a machine. PHP runs as an Apache module inside a process pool shared with several hundred other accounts, with a memory_limit and a max_execution_time chosen to stop any one customer from hurting the others. Those limits are not unreasonable. They are simply not negotiable, and neither is the MySQL configuration, which is where the actual problem was.
The catalogue query was slow because InnoDB had roughly 8 MB of buffer pool to work with and the table was 300 MB. Every request went to disk. No amount of application-level cleverness fixes that, and on shared hosting there is no way to even observe it.
The fix
Build it in dependency order: MySQL first, because the data has to land somewhere; then PHP-FPM, which needs to talk to MySQL; then nginx in front of PHP-FPM. Doing it the other way round means testing nginx against a PHP that cannot reach a database, which tells you nothing.
The base system
$ apt-get update && apt-get -y upgrade
$ apt-get install -y software-properties-common
$ adduser deploy
$ usermod -aG sudo deployEverything after this runs as deploy with sudo. Locking the box down properly — key-only SSH, a firewall, fail2ban — is a job of its own and comes next; for now the machine is new enough that nothing has found it yet.
MySQL
$ sudo apt-get install -y mysql-server-5.6
$ sudo mysql_secure_installationThe packaged defaults assume a machine that also does other things. On a box whose only job is this application, the buffer pool is the setting that matters and everything else is rounding error. In /etc/mysql/my.cnf:
[mysqld]
innodb_buffer_pool_size = 384M
innodb_log_file_size = 96M
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = 1
query_cache_type = 0
query_cache_size = 0
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1384 MB of buffer pool on a 1 GB machine sounds aggressive and is not. The working set of that catalogue is around 300 MB; once it fits, the disk stops being involved in read queries at all. innodb_flush_log_at_trx_commit = 2 trades up to one second of committed transactions in a power loss for a large write throughput gain — acceptable for a catalogue, not acceptable for payments.
Warning
The query cache looks free and is not. Every write to a table invalidates every cached query touching it, and the invalidation takes a global mutex. On a read-mostly catalogue it can still cost more than it returns. Turn it off, measure, and only turn it back on if the numbers say so.
Changing innodb_log_file_size on an existing installation needs the old log files moved aside while MySQL is stopped, or it will refuse to start:
$ sudo service mysql stop
$ sudo mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0.bak
$ sudo mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1.bak
$ sudo service mysql startPHP-FPM
14.04 ships PHP 5.5.9. The application needed 5.6 for variadics, so it comes from Ondřej Surý’s PPA — the same one most of the Debian and Ubuntu PHP world runs on:
$ sudo add-apt-repository ppa:ondrej/php5-5.6
$ sudo apt-get update
$ sudo apt-get install -y php5-fpm php5-mysqlnd php5-curl php5-gd php5-mcrypt
$ php -v
PHP 5.6.7-1~trusty (cli)The settings worth changing in /etc/php5/fpm/php.ini are the opcode cache, which is the single largest performance win available on this machine, and two things that should never have shipped on by default:
memory_limit = 128M
max_execution_time = 30
expose_php = Off
cgi.fix_pathinfo = 0
opcache.enable = 1
opcache.memory_consumption = 96
opcache.max_accelerated_files = 8000
opcache.validate_timestamps = 1
opcache.revalidate_freq = 2OPcache has been bundled since 5.5 and is off by default in the Ubuntu build, which means a large number of installations are compiling every file on every request for no reason. On the catalogue it removed about 90 ms from every page before anything else was touched.
cgi.fix_pathinfo = 0 is a security setting, not a performance one. With it on, a request for /uploads/photo.jpg/x.php can be handed to the interpreter, which will happily execute photo.jpg if it happens to contain PHP. The nginx config below closes the same hole from the other side; do both.
Sizing the pool
This is the part that is usually copied from a blog post and left wrong. The default pool allows 50 children. On a 1 GB machine that is not a configuration, it is a promise to start swapping under load.
Start by measuring what a worker of this application actually costs, under real traffic rather than at rest:
$ ps -ylC php5-fpm --sort:rss | awk 'NR>1 {s+=$8; n++} END {print s/n/1024 " MB avg"}'
42.6 MB avgThen subtract everything else from the total. MySQL is holding 384 MB of buffer pool plus its own overhead, call it 460 MB. The base system wants around 120 MB. nginx is negligible at this size, perhaps 15 MB. That leaves roughly 430 MB for PHP, and at 43 MB a worker that is ten of them:
; /etc/php5/fpm/pool.d/www.conf
pm = dynamic
pm.max_children = 10
pm.start_servers = 3
pm.min_spare_servers = 2
pm.max_spare_servers = 5
pm.max_requests = 500pm.max_requests = 500 recycles a worker after 500 requests. It is a blunt instrument against slow leaks in extensions, and it costs almost nothing.
Caveat
Setting max_children too high does not fail gracefully. It fails by swapping, and a swapping server is dramatically slower than a server that makes requests queue. Ten workers with a short queue beats thirty workers fighting over memory, every time.
A database and a user that is not root
The application connects as its own user with rights to exactly one schema. This sounds like ceremony on a single-tenant box and stops sounding like it the first time an SQL injection turns up: the difference between a compromised account that can read one database and one that can read the mysql table and write files through INTO OUTFILE is the difference between an incident and a disaster.
CREATE DATABASE catalogue CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE USER 'catalogue'@'localhost' IDENTIFIED BY '...';
GRANT SELECT, INSERT, UPDATE, DELETE ON catalogue.* TO 'catalogue'@'localhost';
FLUSH PRIVILEGES;No DROP, no ALTER, no FILE. Migrations run as a separate user with schema rights, invoked from the deploy rather than from the application — which also means a runaway migration cannot be triggered by a web request.
Note utf8 rather than utf8mb4 here, and that is a compromise rather than a recommendation. utf8mb4 is the correct choice, but it needs four bytes per character where utf8 needs three, and an InnoDB index is capped at 767 bytes — so the existing VARCHAR(255) indexes stop fitting. Moving the catalogue over means shortening those columns to 191 or enabling innodb_large_prefix with the DYNAMIC row format, and neither is a change to make on the same afternoon as the server migration.
Ownership, and why the web server must not own the code
The fastest way to make a deployment work is chown -R www-data on the whole tree, and it is also how a single file upload vulnerability becomes arbitrary code execution. If the process serving requests can write the files it executes, an attacker who can write one file has the machine.
$ sudo chown -R deploy:www-data /var/www/catalogue
$ sudo find /var/www/catalogue -type d -exec chmod 750 {} ;
$ sudo find /var/www/catalogue -type f -exec chmod 640 {} ;
# the only writable paths, and they are not executable
$ sudo chown -R www-data:www-data /var/www/catalogue/storage
$ sudo chmod -R 770 /var/www/catalogue/storageThe deploy user owns the code and can write it; the web server group can read it and cannot. Only the upload and cache directories are writable by the server, and the nginx config below refuses to hand anything under them to PHP regardless.
nginx
server {
listen 80;
server_name catalogue.example.com;
root /var/www/catalogue/public;
index index.php;
location / {
try_files $uri $uri/ /index.php?$query_string;
}
location ~ .php$ {
# Only ever hand an existing .php file to the interpreter.
try_files $uri =404;
fastcgi_pass unix:/var/run/php5-fpm.sock;
fastcgi_index index.php;
include fastcgi_params;
fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
}
location ~* .(jpg|jpeg|png|gif|ico|css|js)$ {
expires 30d;
access_log off;
}
gzip on;
gzip_min_length 1000;
gzip_types text/plain text/css application/json application/javascript;
}The order of the two location blocks does not matter here, but the order of the arguments to try_files does: nginx takes the first one that exists and only falls through to the last if none did. Getting it backwards produces a site that serves directory listings, or one that routes every static file through PHP.
$ sudo nginx -t
nginx: configuration file /etc/nginx/nginx.conf test is successful
$ sudo service nginx reloadnginx -t before every reload. A syntax error in a reload leaves the old config running, which is fine; a syntax error in a restart leaves nothing running, which is not.
Verifying it worked
The same filtered catalogue URL, 200 requests at a concurrency of 10, against shared hosting and then against the VPS:
$ ab -n 200 -c 10 'http://catalogue.example.com/products?brand=17&sort=price'
# shared hosting
Requests per second: 1.42 [#/sec] (mean)
Time per request: 7043.118 [ms] (mean)
Failed requests: 11
# vps
Requests per second: 24.90 [#/sec] (mean)
Time per request: 401.516 [ms] (mean)
Failed requests: 0Seven seconds to four hundred milliseconds, and nothing failing. Most of that is the buffer pool: the query that was reading from disk on every request now reads from memory. OPcache and the removal of Apache’s per-request overhead account for the rest.
The more valuable outcome is the second one. There is now a slow query log, and it names the problem instead of implying it:
$ sudo tail -n 4 /var/log/mysql/slow.log
# Query_time: 1.884 Lock_time: 0.000 Rows_sent: 24 Rows_examined: 38104
SELECT p.* FROM products p
JOIN product_attributes a ON a.product_id = p.id
WHERE a.brand_id = 17 ORDER BY p.price ASC LIMIT 24;Rows_examined: 38104 to return 24 rows. That is a missing index, and now it is a fact rather than a guess.
Tip
Leave long_query_time = 1 on in production. The overhead of writing a line for queries slower than a second is trivial next to the cost of not knowing which queries those are.
What this costs
You now own a machine. That is the whole trade. Security updates are yours, and an Ubuntu box left alone for six months is a liability rather than a server. Backups are yours, and a backup nobody has ever restored from is not a backup. Uptime is yours, and at three in the morning the pager is also yours.
The minimum defensible position is unattended security upgrades from day one:
$ sudo apt-get install -y unattended-upgrades
$ sudo dpkg-reconfigure --priority=low unattended-upgradesEverything above assumes a machine nothing has found yet, and that assumption expires quickly — the scans start within days of an address going live. Hardening it is the next job, and it is not optional.