Category: Systems Administration

Posted on

Linux: “Oops, forgot to copy directory ACLs in an rsync…”

Let’s say you want to move a (relatively large) filesystem, with an application’s data files (including a lot of subdirectories, many of them with specific ACLs), to a faster disk (for instance). The old FS is mounted as /PROD, while the new disk is mounted as /NEW; the idea is to stop the application, rsync the data, and switch mount points. Easy, you think:

rsync -av /PROD/ /NEW/

So, the application guys stop the app, you enter the command above, then switch mount points so the old /NEW is now /PROD, and the old /PROD is now /OLD . Everything looks fine, the application guys start their thing again…

… and then complain about access problems. Oops, the rsync didn’t copy the ACLs — you needed the -A argument to include them (e.g. rsync -aAv).

But they took a while to notice it, so that there’s a lot of new data in the new filesystem, so simply doing a new rsync from /OLD to /PROD is unacceptable. You’d simply like to copy directory ACLs (for simplification’s sake, let’s assume there are no file ACLs here — but see below). But there are a couple thousand directories, and of course you don’t want to do it manually…

The solution I came up with was:

cd /OLD
find . -type d | cut -d '.' -f 2- > /tmp/directories.txt
IFS=$'\n' ; for i in `cat /tmp/directories.txt`; do getfacl "/OLD$i" | setfacl --set-file=- "/PROD$i" ; done

Basically, it stores a list of directories in /OLD , then, for each of them, it copies the ACL from /OLD/<directory> to /PROD/<directory> . setfacl’s “–set-file=-” means use the standard input as a “file”, which comes from getfacl‘s output. The “IFS=$’\n’” bit means that the for loop cycles through entire lines, not “words” — otherwise, it would try to split paths with spaces in them.

A limitation of this is that any newly created directories in the new /PROD filesystem won’t be affected, but hopefully they won’t be too many.

What if you wanted to include files as well? Just remove the “-type d” in the find command. Note that, in this case, it’s much more likely that there are new files in the new /PROD FS that won’t have their ACLs corrected.

Posted on

Moving to TLSv1.2 or newer: Nginx, Apache, Postfix, Dovecot

Moving on from “should we do it?” (with the answer to most real-world scenarios being “yes, and as a bonus it can help block a lot of spambots“), here’s how to restrict several Internet services — Nginx, Apache, Postfix, and Dovecot — to TLSv1.2 or newer.

As usual, these are not complete guides for any of those servers; I’m assuming you already have them working fine (including TLS encryption), and just want to disable any TLS protocols lower than v1.2. (If you need to add TLS to a non-TLS server, see instructions for Nginx, Apache, Postfix, and Dovecot.)

Nginx:

In each virtual host’s server section — or, even better, if you’re using Let’s Encrypt, in /etc/letsencrypt/options-ssl-nginx.conf or its equivalent –, add the following (or replace any existing ssl_protocols entry):

ssl_protocols TLSv1.2 TLSv1.3;

Restart Nginx, and test it on SSL Labs. You should get something like this1:

TLS v1.2 and v1.3 only

(Note: if you’re using a very old version of Nginx, it may not accept the “TLSv1.3” parameter and refuse to start; in such a case, remove it — or, better yet, upgrade your system. 🙂 )

Apache:

Similarly to Nginx, you can add one of the following to each Virtual Host, or to the global HTTPS configuration (typically in /etc/httpd/conf.d/ssl.conf), or, if using Let’s Encrypt, to /etc/letsencrypt/options-ssl-apache.conf :

SSLProtocol            +TLSv1.2 +TLSv1.3

or:

SSLProtocol             all -SSLv2 -SSLv3 -TLSv1 -TLSv1.1

Right now, they’ll do the same thing: allow TLSv1.2 and v1.3 only. Personally, I like the second version (which disables older protocols) better, for two reasons: 1) it’ll work even with some ancient Apache version that doesn’t recognize “TLSv1.3”, and 2) when future TLS versions are added, they’ll be enabled, making it more future-proof.

Again, you can test the new configuration on SSL Labs.

Postfix:

Add the following to /etc/postfix/main.cf (replacing any equivalent entries, if they exist).

smtp_tls_mandatory_protocols=!SSLv2, !SSLv3, !TLSv1, !TLSv1.1
smtpd_tls_mandatory_protocols=!SSLv2, !SSLv3, !TLSv1, !TLSv1.1
smtp_tls_protocols=!SSLv2, !SSLv3, !TLSv1, !TLSv1.1
smtpd_tls_protocols=!SSLv2, !SSLv3, !TLSv1, !TLSv1.1

After restarting Postfix, you can test its available protocols with Immuniweb’s SSL Security Test (specify something like yourhostname:25, or yourhostname:465 if not using STARTTLS).

Dovecot:

Add the following (or replace it if it exists) to your SSL configuration (typically /etc/dovecot/conf.d/10-ssl.conf):

ssl_min_protocol = TLSv1.2

Restart Dovecot, and test it on Immuniweb’s test (use something like yourhostname:143 , or yourhostname:993 if not using STARTTLS).

Posted on

Moving to TLSv1.2 or newer: is now the time?

TLSv1.0, used not only for HTTPS but also for secure SMTP, IMAP, etc., has been around for a while — 1999, in fact –, and, while not “hopelessly broken” like SSL 2 and 3 are, there have been many successful attacks/exploits against it in the past two decades, and while most implementations (in terms of both servers and clients) have been patched to work around those, there may always be another one just around the corner. Also, TLSv1.0 came from a time where it was desirable to support as many different ciphers as possible, which means that, by default, some ciphers later found to be insecure may still be enabled.

Therefore, the end of TLSv1.0 has been talked about for a while, and, indeed, the main browser vendors — Google, Mozilla, Apple, and Microsoft — have announced that they’ll deprecate TLSv1.0 (and 1.1, which is little more than a revision of 1.0) by early 2020.

Now, until a few days ago I still had TLSv1.0 enabled on my servers (including Nginx, Postfix and Dovecot); my belief then being that some encryption was always better than no encryption, and besides we were mostly talking about public websites, that didn’t ask for user credentials or anything. Furthermore, any modern browsers/email clients default to secure protocols (TLSv1.2 or better), so there would never be, “in real life”, a situation where important/sensitive data (say, me logging on to my own mail server, or to one of my WordPresses) was being transmitted in an insecure way.

On the other hand… doesn’t the above mean that, “in real life”, nobody will really be using TLSv1.0 “legitimately”? Doesn’t it mean that setting up TLSv1.2 as a minimum will have no adverse consequences?

So I investigated it a little:

  • in terms of browsers/clients, requiring TLSv1.2 or above means: 1) no Internet Explorer before version 11; 2) no Safari before 6.x; 3) no default Android browser before Android 4.4.x (but those users can still use Chrome or other non-default browsers). (You can check this out on SSL Labs). I don’t have a way to check email clients that old, but you can assume that anything released after the browsers just mentioned will be OK;
  • in terms of my own server logs (mostly Nginx, but also Postfix, in terms of opportunistic encryption), apparently, only spambots and such are using TLSv1.0; any “legitimate” email servers use TLSv1.2 or 1.3. The same thing with my websites — any TLSv1.0 entries in my logs are always from some bot (and not one for a “well-known” search engine), not actual visitors. 1

In short: you can probably move to requiring TLSv1.2 or above now (and, indeed, I’ve done so since a few days ago), unless you’re in some very peculiar situation — maybe your site needs to be accessed by users of a company whose IT policy includes not using software newer than 20 years old? Or you have a CEO who’s always refused to upgrade his beloved Symbian phone, but who’d flip out if he couldn’t access your company’s webmail with it? 🙂 Otherwise, my advice is: go TLSv1.2 and don’t look back.

However, and since this post is already quite long, I’ll leave the specific instructions for disabling TLS before 1.2 in Nginx, Apache, Postfix, and Dovecot for the next one…

Posted on

Today I learned: root’s cron (including stuff like logrotate) doesn’t run if root’s password is expired

… which can be hard to spot, since in most places you never use root’s password anywhere (you “sudo su” to root using your user‘s password).

Today’s story:

  1. server has a logfile of several GB;
  2. head logfile shows it hasn’t been rotated in more than a year;
  3. running logrotate /etc/logrotate.conf manually works;
  4. /var/log/cron includes entries like:

Mar 22 14:10:01 xxxxxxxx crond[26561]: (root) PAM ERROR (Authentication token is no longer valid; new one required)
Mar 22 14:10:01 xxxxxxxx crond[26561]: (root) FAILED to authorize user with PAM (Authentication token is no longer valid; new one required)

  1. chage -l root shows that its password has expired…

Now, why did root have password expiration enabled? It’s a mystery 🙂 — probably someone ran a script configuring password expiration for all users and forgot to add some exceptions to it, root among them. Anyway,

chage -E -1 -M -1 root ; passwd root

solved the problem. Hope this is useful. 🙂

Posted on

Apache: How to get an A (or A+) rating on securityheaders.com

A commenter on my old post about configuring security headers on Nginx (and getting an A or A+ rating on securityheaders.io, now moved to securityheaders.com) asked for a version for Apache, so… here it is. It’s basically the same, just with the appropriate syntax changes:

Nginx setting Explanation
header always set Strict-Transport-Security “max-age=31536000; includeSubdomains; preload” Enforces HTTPS on the entire site. Don’t use if you still need to provide HTTP, of course.
header always set X-Content-Type-Options nosniff Prevents browsers from trying to “guess” MIME types and such, forcing them to use what the server tells them.
header always set X-Frame-Options SAMEORIGIN Stops your site from being included in iframes on other sites.
header always set X-Xss-Protection “1; mode=block” Activates cross-scripting (XSS) protection in browsers.
header always set Referrer-Policy “unsafe-url” Makes the site always send referrer information to other sites. NOTE: this is not the most secure setting, but it’s the one I prefer; see below.
header always set Content-Security-Policy “default-src https: data: ‘unsafe-inline’ ‘unsafe-eval'” Forces TLS (don’t use if you still need to provide HTTP); prevents mixed content warnings. NOTE: again, this is not the most secure setting; see below.

About the last two settings, I’ll copy from my old post:

I choose to send referring information to other sites for a simple reason: I like to see where my own visitors come from (I don’t sell, share or monetize that in any way, it’s just for curiosity’s sake), and I’m a firm believer in treating others as I want to be treated. If you don’t care about that, you may want to change this to “no-referrer-when-downgrade“, or “strict-origin“.

As for the Content Security Policy, anything more “secure” than the above prevents (or at least makes it a lot more headache-inducing) the use of inline scripts and/or external scripts, which would mean no external tools/scripts/content (or at least a lot of work in adding every external domain to a list of exceptions), and a lot of hacking on software such as WordPress (seriously: add the setting, but then remove the “unsafe-*” options and see what stops working…). You might use the most paranoid settings for an internally developed, mostly static site, but I fail to see the point. So, this and the paragraph above are the reasons for an A rating, instead of an A+.

Hope this was useful. 🙂

Posted on

How to get a 100% score on SSL Labs (Red Hat/CentOS 7.x, Apache, Let’s Encrypt)

There’s an Nginx version of this tutorial already, but since some people prefer/have to use Apache, here’s how to get 100% on SSL Labs’ server test with it.

For extra fun, instead of Debian/Ubuntu, we’ll be using Red Hat/CentOS (more precisely, CentOS 7.6 with the latest updates as of January 10th, 2019, but this shouldn’t change for any other 7.x versions of either CentOS or RHCE). And we’ll keep firewalld and SELinux enabled 1, even though, as far as I know, in most companies (well, at least the ones I’ve worked at) it’s, typically, official policy to disable both. 🙂

So, let’s assume you already have a CentOS/RHEL machine, with internet access and the default repositories enabled. First, we’ll enable the EPEL repository (to provide Let’s Encrypt’s certbot):

yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm

Now we’ll install certbot (which will also install Apache’s httpd and mod_ssl as dependencies) and wget (which we may need later):

yum install python2-certbot-apache wget

To be run certbot and allow it to verify your site, you should first create a simple vhost. Add this to your Apache configuration (e.g. a new file ending in .conf in /etc/httpd/conf.d/):

<VirtualHost *:80>
    DocumentRoot "/var/www/html"
    ServerName mysite.mydomain.com
</VirtualHost>

Now add Apache’s ports to firewalld, and start the service:

firewall-cmd --permanent --zone=public --add-service=http
firewall-cmd --permanent --zone=public --add-service=https
firewall-cmd --reload

systemctl enable httpd ; systemctl restart httpd

Run certbot to create and begin using the new certificate:

certbot --apache --rsa-key-size 4096 --no-redirect --staple-ocsp -d mysite.mydomain.com

You should now have the virtual host configured in /etc/httpd/conf.d/ssl.conf . Let’s add a few lines to it (inside the <VirtualHost></VirtualHost> section):

Header always set Strict-Transport-Security "max-age=15768000"
SSLProtocol             all -SSLv3 -TLSv1 -TLSv1.1
SSLCipherSuite          ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384::ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES256-SHA384
SSLCipherSuite          TLSv1.3 TLS_CHACHA20_POLY1305_SHA256:TLS_AES_256_GCM_SHA384
SSLHonorCipherOrder     on
SSLCompression          off

(Don’t mind the “TLSv1.3” line for now, it won’t be used — if at all  — until later in this post, and won’t have any effect if it isn’t.)

This should get you an A+ rating, with all bars at 100% except for Key Exchange. A good start, right? 🙂 However, that final bar can be a bit tricky. The problem here is that SSL Labs limits the Key Exchange rating to 90% if the default ECDH curve is either prime256v1 or X25519, the first of them being the OpenSSL default… and Apache 2.4.6 (the version included in all 7.x RHELs/CentOSes) doesn’t yet include the option to specify a different list of curves (of which secp384r1 will give a 100% rating, and won’t add any compatibility issues).

Assuming you don’t want to install a non-official Apache, then the only way (as far as I know) to specify a new curve is to add the results of running:

openssl ecparam -name secp384r1

to your certificate file. But Let’s Encrypt certificates are renewed often, so you may need some trickery here — such as adding something like this to root’s crontab:

*/5 * * * * grep -q "EC PARAMETERERS" /your/certificate/file.crt || openssl ecparam -name secp384r1 >> /your/certificate/file.crt

Ugly, I know. 🙂

Alternatively, you can upgrade Apache to a newer version. CodeIT (for instance) provides replacement packages for RHEL and CentOS, which you can use by doing:

cd /etc/yum.repos.d && wget https://repo.codeit.guru/codeit.el`rpm -q --qf "%{VERSION}" $(rpm -q --whatprovides redhat-release)`.repo
yum -y update httpd mod_ssl

This will update Apache from 2.4.6 to 2.4.37 (as of January 10th, 2019), and so you can add the following to the vhost:

SSLOpenSSLConfCmd     Curves secp384r1:X25519:prime256v1

However! This will still not give 100%, since CoreIT’s Apache appears to come with a newer OpenSSL (1.1.1) statically linked to it, which means it’ll enable TLS 1.3 by default. And there’s nothing wrong with it… except that, from all the tests I’ve performed, Apache ignores the specified order of the ECDH curves when using TLS 1.3. With the line above, it’ll use secp384r1 for TLS 1.2, but X25519 for TLS 1.3 — and that’s enough to lower the Key Exchange bar to 90% again. 🙁

Given this, there are now several options:

1- disable TLS 1.3 (it’s still relatively new, and most people are still using 1.2): change the line:

SSLProtocol             all -SSLv3 -TLSv1 -TLSv1.1

to:

SSLProtocol             all -SSLv3 -TLSv1 -TLSv1.1 -TLSv1.3

2- force secp384r1: change the “SSLOpenSSLConfCmd Curves” line to simply:

SSLOpenSSLConfCmd       Curves secp384r1

3- ignore this situation altogether: X25519 is considered to be as secure as secp384r1, if not more; even SSL Labs mentions not penalizing it in the future in their (early 2018) grading guide — they just have been a bit lazy in updating their test. It’s quite possible that a future update of their tool will give a full 100% score to X25519.

Anyway, most of this was done for fun, for the challenge of it.  🙂

Posted on

Red Hat/CentOS: How to add HTTPS to an existing Nginx website (both with and without Let’s Encrypt)

(Yes, you read the title correctly. For extra fun, and to prevent this blog from being too focused on Ubuntu/Debian, this time I’ll be using Red Hat Enterprise Linux / CentOS (and, I assume, Fedora as well.) Later on, I may post a Debian-based version.)

Configuring a basic HTTP site on Nginx

(Note: if you already have a working HTTP site, you can skip to the next section (“Adding encryption…”))

Yes, the post title mentions an “existing” website, which I believe will be the case in most “real world” situations, but installing a new one is actually very easy on CentOS1. First, do:

yum -y install epel-release; yum -y install nginx

Then create a very basic configuration file for the (non-HTTPS) site, as /etc/nginx/conf.d/mysite.conf :

server {
        listen 80;
        server_name mysite.mydomain.com;
        root /var/www/mysite;

        location / {
        }
}

Then, of course, create the /var/www/mysite directory (CentOS doesn’t use /var/www by default, but I’m far too used to it to change. 🙂 ) If you’d like, create an index.html text file in that directory, restart nginx (“service nginx restart” or “systemctl restart nginx“, depending on your system’s major version), and try browsing to http://mysite.mydomain.com . If it works, congratulations, you have a running web server and a basic site.

Adding encryption to the site (not using Let’s Encrypt):

First, you need a key/certificate pair. I have tutorials for creating an RSA certificate or an ECDSA certificate.

Second, edit the site’s configuration file (in the “starting from scratch” example above, it’s “/etc/nginx/conf.d/mysite.conf“), and copy the entire server section so that it appears twice on that text file (one after the other). Pick either the original or the copy (not both!), and, inside it, change the line:

listen 80;

to:

listen 443 ssl http2;

(Note: the “http2” option is only available in Nginx 1.9.5 or newer. If your version complains about it, just remove it, or upgrade.)

Inside that section, add the following options:

ssl_certificate /path/to/certificate.crt;
ssl_certificate_key /path/to/privatekey.crt;

(replacing the paths and file names, of course.)

This should be enough — restart Nginx and you should have an HTTPS site as well as the HTTP one.

And what if you want to disable HTTP for that site and use HTTPS only? Just edit the same configuration file, look for the server section you didn’t change (the one that still includes “listen 80;“), and replace the inside of that section with:

listen 80;
server_name mysite.domain.com;
return 301 https://mysite.domain.com$request_uri;

Afterward, while you’re at it, why not go for an A+ rating on SSL Labs (skip the certification creation part from that tutorial, you’ve done it already) and an A rating on securityheaders.io?

Adding encryption to the site (using Let’s Encrypt):

First, install certbot:

yum install python2-certbot-nginx

Then check if Nginx is running and your normal HTTP site is online (it’ll be needed in a short while, to activate the certificate). If so, then enter:

certbot --nginx --rsa-key-size 4096 -d mysite.mydomain.com

(replacing “mysite.mydomain.com” with yours, of course.)

Answer the questions it asks you: a contact email, whether you agree with the terms (you need to say yes to this one), if you want to share your email with the EFF, and finally if you want “No redirect” (i.e. keep the HTTP site) or “Redirect” (make your site HTTPS only).

And that’s it (almost — see the next paragraph) — when you get the shell prompt back, certbot will already have reconfigured Nginx in the way you chose in the paragraph above, and restarted it so that it’s running the new configuration. You may want to add “http2” to the “listen 443 ssl;” line in the configuration file (it’ll probably be the default someday, but as of this post’s date it isn’t), and don’t forget your options for improved security and security headers.

Only one thing is missing: automatically renewing certificates. Strangely, the certbot package configures that automatically on Ubuntu, but not on CentOS, from what I’ve seen (please correct me if I’m wrong). The official Let’s Encrypt docs recommend adding this (which includes some randomization so that entire timezones don’t attempt to renew their certificates at precisely the same time) to root’s crontab:

0 0,12 * * * python -c 'import random; import time; time.sleep(random.random() * 3600)' && certbot renew

(Note: It’s possible to use Let’s Encrypt to create ECDSA certificates, but as of this writing you have to do most of the work manually (creating a CSR, etc.), and you lose the automatic renewal, so for the moment I suggest using RSA certificates. I hope this changes in the future.)

Posted on

Linux: ‘top’ shows almost all of my memory being used! Should I panic?

Top The short answer is, of course, “no.” 🙂 1

A common mistake that Linux users make — indeed, one that even some less experienced Linux sysadmins sometimes make — is treating Linux2 as if memory technology hadn’t evolved since MS-DOS, when you needed as much memory as possible — especially conventional RAM (anyone remember that?) — to be free, so that applications could be loaded into it and run. If there wasn’t enough free memory, then applications wouldn’t run, or would run worse, so you’d have to “optimize” your DOS system to have as much free conventional memory as possible (using tricks such as loading device drivers into upper memory, and, of course, exiting a program completely before starting another.)

Somehow, that line of thinking still exists — even, apparently, in people who never used MS-DOS or similar systems before(!). “Free memory is good,” people think. “If most of the RAM is used up, then there’s a problem,” they insist — one to be solved either by killing applications or by adding more memory to the system.

Linux — like all modern systems, and I’ll even include Windows here 🙂 3, uses virtual memory. Put simply (and, yes, I’m simplifying it a lot — there’s more to it, of course), it means that the amount of available memory is actually (mostly) independent of the physical system RAM, since the system uses the disk as memory, too. Since real RAM is (virtually) always faster — but (usually) smaller — than a disk drive, the system typically uses prefers to use it for more critical stuff (such as the kernel, device drivers, and currently actively running code), with less critical stuff (say, a program waiting for something to happen) being relegated to the disk — even though it’s still “memory”. Note that even most of the aforementioned “critical stuff” can be temporarily moved to the disk if needed.

You may now be asking: if RAM is typically preferred for “critical stuff” only, and that stuff doesn’t take up the entire available memory, then what’s the rest of the RAM used for? The answer is, of course, disk caching — both for reading (loading up recently and/or frequently accessed data from the disk and keeping it available in RAM for a while, in case it’s needed again in the future) and for writing (which works the other way around — the system tells the program the data is already written to disk, so that it can go on instead of waiting for it, but it’s still only in memory, to be written a short while later.) So, any free RAM is typically used for disk caching, with the system managing it automatically.

But people keep looking at the output of “top”, seeing 90% of the physical RAM used, and panicking. 🙂

In fact, this confusion was so common that modern Linux systems even changed some labels on the “top” command. For instance, this is from a Red Hat Enterprise Linux 6.x:

"top" on RHEL 6.x
“top” on RHEL 6.x. Nice machine, eh? 🙂

As seen above, the server has (rounding down) 567 GB of physical RAM, of which 506 GB — i.e. 89% — are used. Looks scary (if you haven’t been reading so far), right? I mean, the server is at almost 90% capacity! But then you look at the value for “cached”, in the line below. That’s 479 GB — or 84% — used for disk cache! In other words, the entire operating system and applications are running in just 5% of the server’s RAM, with the rest of it being used just to make disk access faster.

And that “cached” value — and this is the important bit — counts as free memory. It’s available to the system whenever needs it. In “computer terms”, it’s not technically free, it’s being used for the cache, but in “human terms”, it’s just as free as the one that actually says “free” in front of it.

As I said above, this caused so much confusion to users (for decades) that, eventually, it was changed. Here’s how it looks like in a RHEL 7.x system:

Top in RHEL 7.x
“top” in RHEL 7.x. Puny machine, I know.

In the first line: 79044 KB free + 240444 KB used + 697064 KB for buffers and cache (which were separated in the old version, by the way) = … 1016512 KB, which you’ll note is exactly the first value in the same line, for total physical memory.

In the second line, the first three values refer to swap, so they’re not relevant here, but the “avail Mem” one is new. What is it? According to the “top” manual page,

The avail number on line 2 is an estimation of physical memory available for starting new applications, without swapping. Unlike the free field, it attempts to account for readily reclaimable page cache and memory slabs.

In other words (and this is again an oversimplification), it’s what the system has readily available — in other words, the “free” part, plus (most of) what is being used for caching. That doesn’t mean it can only use that much memory, it just means that that’s what it can use right now, without swapping anything out. Which, in my opinion, ends up not mattering that much, in terms of a precise value. However, at least it now gives a better (though rough) idea of what part of the physical memory is available, not simply “free” (i.e. not doing anything).

So, what about those specific cases mentioned (in a note) at the beginning? Well, if there is very little memory free and very little swap space free and very little memory (compared to the total amount of physical memory) is being used for buffers/cache, then and only then4 may it be the case that the server actually needs more memory and/or swap space, or that some process has some kind of memory leak or is otherwise using much more memory than usual (if restarting the service fixes it, then this is probably the case.)

Posted on

How to configure SPF in Postfix

SPF, or Sender Policy Framework, is a way for domain owners to say: “these are the email servers my domain sends mail from; anyone else is attempting to impersonate me.” These days, a lot of email services implement it in some way (both for receiving and sending email), and, if you administer your own server, it’s quite easy to implement, as we’ll see.

There are actually two parts to SPF: configuring it for your domain (so that your outgoing emails have a better chance of being accepted and emails impersonating your domain have a better chance of being refused), and configuring your email server to check whether incoming emails come from an SPF authorized server or not. Without further ado:

1- Configuring SPF for your domain’s outgoing mail:

This is actually done in DNS, not in the email server. Simply add a TXT record for your domain, with something like this:

v=spf1 a:mailserver1.domain.com a:mailserver2.domain.com -all

There are other configuration parameters, such as getting the servers from the domain’s MX record (add “mx“), and copying another domain’s authorized servers (“include:otherdomain.com“). And, of course, if you only have one server sending mail, you don’t need two “a:” entries.

Pay special attention to the “-all” part at the end: that instructs other servers to refuse any email apparently coming from your domain that isn’t authorized for it. That should ideally be the final configuration, but if you’re doing this for the first time you may want to change it to “~all“, which means “softfail”: it suggests to the other side that the mail is probably not legit, but that it shouldn’t refuse it outright because of it. After testing it for a while, if you see no problems then you can change it to “-all“.

2- Configuring SPF checking for incoming mail in Postfix:

I’ll assume you already have a working Postfix server (hopefully already using TLS encryption, hint hint), and that you want to respect any “-all” configurations (see above) in incoming mail — in other words, if a domain says “all my mail comes from server X, refuse anything else” and you receive an email purportedly from that domain, but from a server Y, it’s refused (for “~all“, it’d generate a warning).

First, install pypolicyd-spf. On a Debian-based system, the package (as of this writing) still has its old name, so enter the following:

apt install postfix-policyd-spf-python

Edit /etc/postfix/master.cf, and add:

# spf
policyd-spf  unix  -       n       n       -       0       spawn
        user=policyd-spf argv=/usr/bin/policyd-spf

Then edit /etc/postfix/main.cf, and look for the “smtpd_recipient_restrictions” section. That section will probably begin by accepting mail from your networks and from authenticated users, then rejecting non-authorized relaying, possibly followed by some white- or blacklists, and maybe a couple of filters, finally ending with “permit“. Before that “permit“, add:

    check_policy_service unix:private/policyd-spf,

(note the comma at the end; without it, Postfix wouldn’t read the rest of that section.)

IMPORTANT: while you’re still in testing, it may be a good idea to add “warn_if_reject” to the beginning of the previous configuration line. With it, Postfix will still log SPF failures, but won’t actually reject an email because of it. When you’re certain everything is fine, you can then remove that prefix (and then reload Postfix, of course).

Add also the following to that configuration file, outside of any section:

policy-spf_time_limit = 3600

And you’re done. Reload or restart Postfix, and check your logs to see if everything is working as intended.

Note that other tools, such as SpamAssassin, may also use SPF to weigh whether an incoming mail is more or less likely to be spam. That, however, is unrelated to the Postfix configuration above, which does a binary “block/don’t block”.

Posted on

Nginx: How to prevent it from logging local hits

Like any web server, Nginx logs all accesses/hits by default, and if you have some kind of log-based analytics tool (such as AWstats) you probably have it already set to ignore hits from the host itself (usually by skipping both localhost (127.0.0.1) and the server’s public IP address). If you don’t do so, then your statistics will probably be inflated (“wow, my newly created site is surprisingly popular!“), and, unless your site has many regular users, the IP address at the top of the “visitors” table will almost certainly be your own. Local hits can come from several culprits: for instance, some web software such as WordPress or MyBB use special URLs as a form of cron replacement, and also you may have yourself be monitoring your site in some way (such as, in my case, benchmarking a particular URL and MRTGing the access speed).

OK, so your analytics don’t show it anymore — but what about the log files themselves? Maybe you don’t want a large percentage of them being composed of internal hits (especially in the case of those “let’s measure the average response speed for 100 hits every 5 minutes” benchmarks, which for that site made up some 95% of its access logs…). Maybe it even interferes with some other tools you’re using, such as something like DenyHosts or Fail2ban, to detect some abuse patterns, even after whitelisting your external IP. How about having the option not to log them at all? 1

On Nginx, this is actually pretty easy to do.

1- in the Nginx main configuration file, add the following:

map $remote_addr $notlocal {
        default 1;
        ~^YOUR_EXTERNAL_IP$ 0;
        ~^127.0.0.1$ 0;
}

Replacing YOUR_EXTERNAL_IP with… you can probably guess. 🙂

Important: if your virtual hosts’ configurations are in another directory that is “included” from nginx.conf, take care to add the above before the include lines (e.g. include /etc/nginx/conf.d/*.conf) . Otherwise, the virtual hosts won’t have the “$notlocal” variable defined yet, and Nginx won’t start because of it.

2- for each virtual host where you want to stop logging local hits, edit the access_log line, changing it from something like:

access_log /var/log/nginx/mysite-access.log;

to:

access_log /var/log/nginx/mysite-access.log combined if=$notlocal;

Note the addition of “combined“. “combined” is the default format for Nginx access logs, so specifying it isn’t usually needed, but apparently if you want to specify the “if” condition, it must come after the log format option (otherwise, I guess Nginx thinks you want a log format of “if“, which of course doesn’t exist and will prevent the server from starting.)

That it! Restart Nginx, and enjoy your much cleaner/smaller logs.

Posted on

Linux: What’s filling up my almost full filesystem?

Let’s say you realize (maybe because you got an alarm for it) that a particular filesystem — let’s say /qwerty — is full, or almost full, and you want to find out what’s taking up the most space. Simply enter something like:

du -k -x /qwerty | sort -n | tail -20

to get a list of the directories taking up the most space in that filesystem, sorted by size, with the largest ones at the bottom.

Note that the “/qwerty“, in this case, should be the filesystem’s top directory, not some subdirectory of it. In other words, it should be something that shows up on a “df” command.

Explanation:

“du -k -x” shows the subdirectories (and their total sizes) of the specified directory (or of the current one, if you don’t specify one). “-k” means that sizes are reported in kilobytes (KB) — this is not mandatory, but different versions of “du” may use other units, and this one is easy to read. “-x” means “don’t go out of the specified filesystem”, and that’s quite important here, as you could have a “/qwerty/asdfgh” filesystem, mounted in a subdirectory of “/qwerty“, but since it’s a different filesystem (appearing separately in the results of a “df” command) you won’t want to include it in this list.

“sort -n” is a simple numeric sort, and “tail -20” just means “show only the last 20 lines. 20 is a reasonable number that fits in most terminals at their default sizes (typically 80×25), so that you don’t have to scroll up.

(Yes, this is very basic, but, hey, one of the goals of this blog is to document stuff I help co-workers with, since, at least in theory, if someone has a question about something they need to do, then many other people may have the same doubt/need as well, and we have a couple of new team members whose backgrounds are not Linux/Unix-related, so…)

Posted on

Nginx: How to get an A (or A+) rating on securityheaders.io

Some time ago, I wrote about how to set up Nginx to get an A+ rating on SSL Labs (focusing specifically on an ECDSA certificate, but you can certainly achieve the same with a (more common) RSA certificate). SSL Labs' test is very comprehensive, warning the user of most potential problems, including SSL/TLS implementation bugs (update your software!), misconfigurations, problems with the certificate itself, etc.. But there is another similar tool, securityheaders.io, which focuses on something different: security-related headers. Such headers are sent by the web server (such as Nginx or Apache) and affect browsers' security in several ways, unrelated to encryption, protocols or the certificate used (these are tested by SSL Labs).

For more details about those headers, I'd suggest you navigate to securityheaders.io itself, and then check your site in it, then follow the links for each suggestion. Meanwhile, if your site uses Nginx, here's a "quick and dirty" guide for getting an A rating (and, yes, I'll mention what you'd need for an A+ rating, and also why I choose not to try for one).

Nginx setting Explanation
add_header Strict-Transport-Security "max-age=31536000; includeSubdomains; preload"; Enforces HTTPS on the entire site. Don't use if you still need to provide HTTP, of course.
add_header X-Content-Type-Options nosniff; Prevents browsers from trying to "guess" MIME types and such, forcing them to use what the server tells them.
add_header X-Frame-Options SAMEORIGIN; Stops your site from being included in iframes on other sites.
add_header X-Xss-Protection "1" always;  Activates cross-scripting (XSS) protection in browsers.
add_header Referrer-Policy "unsafe-url";  Makes the site always send referrer information to other sites. NOTE: this is not the most secure setting, but it's the one I prefer; see below.
add_header Content-Security-Policy "default-src https: data: 'unsafe-inline' 'unsafe-eval'";  Forces TLS (don't use if you still need to provide HTTP); prevents mixed content warnings. NOTE: again, this is not the most secure setting; see below.

You're probably wondering about the last two settings. I choose to send referring information to other sites for a simple reason: I like to see where my own visitors come from (I don't sell, share or monetize that in any way, it's just for curiosity's sake), and I'm a firm believer in treating others as I want to be treated. If you don't care about that, you may want to change this to "no-referrer-when-downgrade", or "strict-origin".

As for the Content Security Policy, anything more "secure" than the above prevents (or at least makes it a lot more headache-inducing) the use of inline scripts and/or external scripts, which would mean no external tools/scripts/content (or at least a lot of work in adding every external domain to a list of exceptions), and a lot of hacking on software such as WordPress (seriously: add the setting, but then remove the "unsafe-*" options and see what stops working...). You might use the most paranoid settings for an internally developed, mostly static site, but I fail to see the point. So, this and the paragraph above are the reasons for an A rating, instead of an A+.

Posted on

Stuff you can do quickly with MRTG (that has nothing to do with router traffic)

Come on, everyone knows MRTG, right? That venerable tool for graphing router traffic, among other things. If you’ve worked as a sysadmin and/or network admin in the past, you’re probably familiar with it since decades ago, although these days you probably use something more modern, such as Cacti, Zabbix, etc..

The thing is, I still use MRTG a lot, old though it is, and even though there are many newer alternatives, since MRTG has, to me, one huge advantage. Well, two, in fact, though they really amount to one thing: ease of use. MRTG is simple: it doesn’t need to run as a daemon, it’s just a binary you can run through cron. And MRTG is versatile: unlike other tools that really, really want to work with network traffic, or system memory, or processor usage, or something else (preferably to be read through SNMP, or a local agent), MRTG just wants one or two values, and it’ll call the first one “input” and the second “output”. And those values can come from anywhere.

Just to show how easy it is: an MRTG configuration file usually looks something like this:

WorkDir:/var/www/htdocs/ptmail/

Refresh: 36000

Target[ptmail]: `/usr/local/bin/getptmail.sh`;
Options[ptmail]: growright, nopercent, integer, noinfo, gauge
MaxBytes[ptmail]: 12500000000000
kilo[ptmail]: 1000
YLegend[ptmail]: MB used / Emails
ShortLegend[ptmail]:  
LegendI[ptmail]:  MB used (uncompressed):
LegendO[ptmail]:  Emails:
Title[ptmail]: MB used / Emails
PageTop[ptmail]: <h1>MB used / Emails</h1>

XSize[ptmail]: 500
YSize[ptmail]: 250
XScale[ptmail]: 1.4
YScale[ptmail]: 1.4

Timezone[ptmail]: Europe/Lisbon

The key part is that “Target” line which, you’ll notice, is between backticks, meaning that it’ll actually execute a script. And what does it expect from that script? Simple: just four lines:

  • A number (which it’ll think of as “input”, and which will typically be graphed in green)
  • Another number (for “output”, to be graphed in blue)
  • The system’s uptime (on Linux, uptime | cut -d ” ” -f 4-7 | cut -d “,” -f 1-2 typically provides it in the format MRTG wants, although it’s really just text)
  • The system’s name (just use uname -n)

That’s it! If you only want to graph a single value, just add “noi” or “noo” to the Options line (and you can then replace the first or second line, respectively, with “echo 0” on your script). You can also make it not show the uptime and name with the “noinfo” option. And, finally, the “gauge” option makes MRTG graph current values, while, without it, it adds the current value to the previous one (like, for instance, a “bytes transferred” counter). Both have their uses.

Now, you’re probably thinking: “yeah, yeah, I’ve known how to use MRTG for decades (and these days I use XYZ instead; who even uses MRTG in 2017?!?); why are you writing about something so basic and, well, old?” The answer is, again, 1) that it’s ridiculously easy to use (just create a script to write 4 lines), and 2) that it’s not for routers (or network interfaces) only; it can be used for so much else, and it can typically be done in minutes. So I’ll just show a few examples of stuff I already do with it on my servers. Each example will be just the shell script; the MRTG configuration file for these is virtually always the same thing, except for axis legends, labels, etc..

Note: all of the following need the “gauge” option.

“Steal” time on a VPS

#!/bin/bash

# Graphs "steal" time on a VPS. A high value means you need to complain to your
# VPS provider that the host your server is on is overbooked, or there's
# another customer abusing it (mining Bitcoins? :) )

NUM=$((3 + ($RANDOM % 3)))

rm -f /tmp/steals.txt

top -b -n $NUM | grep Cpu | cut -d ',' -f 8 | tr -d ' ' | cut -d 's' -f 1 > /tmp/steals.txt

TOTAL=0 ; for i in `cat /tmp/steals.txt`; do TOTAL=`echo "$TOTAL + $i" | bc -l`; done

AVERAGE=`echo "$TOTAL / $NUM" | bc -l`

echo 0
echo $AVERAGE
uptime | cut -d " " -f 4-7 | cut -d "," -f 1-2
uname -n

Total size and number of emails in a Maildir

#!/bin/bash

# Total size and number of mails in a Maildir

# total UNCOMPRESSED size of your mailbox, in MB, 2 decimal cases
# actual compressed size will probably be much lower; if you'd rather show it,
# just replace the following with a "du -ms /home/ptmail/Maildir | cut -f 1"
find /home/ptmail/Maildir -type f | grep drax | cut -d '=' -f 2 | cut -d ',' -f 1 > /tmp/sizes-ptmail ; perl -e "printf \"%.2f\n\", `paste -s -d+ /tmp/sizes-ptmail | bc` / 1024 / 1024"

# number of emails
find /home/ptmail/Maildir -type f | grep drax | wc -l

uptime | cut -d " " -f 4-7 | cut -d "," -f 1-2
uname -n

System load (* 100) and number of processes

#!/usr/bin/php
<?php

# Graph system load (multiplied by 100) and number of processes.

# Yes, this is trivial and uses mostly shell commands. :) It's just to show
# that "scripts" invoked by MRTG can be in any language (in this case, PHP),
# not just shell scripts. You could even use compiled C code, for instance.

$x = exec ("uptime | cut -d ':' -s -f 5 | cut -d ',' -f 1 | cut -d ' ' -f 2");

$x*=100;

print $x . "\n";

system ("ps ax | wc -l");

system ("uptime | cut -d ' ' -f 4-7 | cut -d ',' -f 1-2");
system ("uname -n");

?>

Average time per request of a URL

#!/bin/bash

# Average time per request of the URL below. Run it locally to measure how fast
# your site and/or server is, or remotely to measure its network connection as well
# (though that would be less reliable; best use something like Pingdom)

# use "noi" for this one (hence the first "echo 0")

echo 0
ab -c 4 -n 100 -k https://zurgl.com/ | grep "Time per request" | head -n 1 | cut -d ':' -f 2 | tr -d ' ' | cut -d '[' -f 1

uptime | cut -d " " -f 4-7 | cut -d "," -f 1-2
uname -n

I could go on, but I’m sure you get the idea. 🙂

Posted on

Linux: Detecting potential disk space problems before going home

As is typical in sysadmin work, one member of my team is always on call, having to be available during the night and on weekends. Sometimes the problems are big (especially hardware failures), sometimes they’re trivial, and sometimes they’re false alarms, but one thing is always true: it’s not fun to be woken up by a ringing phone, especially when you’re already not having enough sleep.

One of the most common reasons for being called at night (or during weekends) is when a disk’s occupation exceeds a threshold (let’s say 95%, although that value varies in reality). Logs get larger (especially when some error is constantly occurring), old logs aren’t compressed/rotated/deleted automatically, users leave huge debug files somewhere and forget to delete them for years… all of these happen, it’s mostly inevitable.

But, if we’re woken up at 4 A.M. because a /var partition reached 95% utilization, isn’t it true that in most cases the increase was gradual, and the value was already abnormally high — let’s say 93 or 94% — at 4 P.M.?

And wouldn’t it have been much better (for our well-earned rest, among other things) for the person on call to have fixed the problem during the day, before going home?

I thought so, too. Which is why I wrote a script some months ago to detect which servers (from more than a thousand) will need attention soon.

(Looking at that script now, I’m a bit ashamed to share it, as it’s pretty lazily coded, repeating code one time for each filesystem instead of having some kind of function… but it was made in a bit of a hurry some time ago, so because of laziness… I mean, historical accuracy 🙂 I’ll share it as it is (other than translating variable names and output messages from my native Portuguese)).

So, here it is. Don’t laugh too hard, OK? 🙂

#!/bin/bash

# check / partition
ROOT=`df -hl / | tail -1 | awk '{ print $5 }' | cut -d '%' -f 1`
case $ROOT in
    ''|*[!0-9]*) ROOT=`df -hl / | tail -1 | awk '{ print $4 }' | cut -d '%' -f 1` ;;
esac
case $ROOT in
    ''|*[!0-9]*) ROOT=`df -hl / | tail -1 | awk '{ print $3 }' | cut -d '%' -f 1` ;;
esac

TOP=$ROOT
WORST="/"

# check /var partition
VAR=`df -hl /var | tail -1 | awk '{ print $5 }' | cut -d '%' -f 1`
case $VAR in
    ''|*[!0-9]*) VAR=`df -hl /var | tail -1 | awk '{ print $4 }' | cut -d '%' -f 1` ;;
esac

if [ "$VAR" -gt "$TOP" ]; then
        TOP=$VAR
        WORST="/var"
fi

# check /home partition
# comment out this entire block if you don't want to monitor /home
HOME=`df -hl /home | tail -1 | awk '{ print $5 }' | cut -d '%' -f 1`
case $HOME in
    ''|*[!0-9]*) HOME=`df -hl /home | tail -1 | awk '{ print $4 }' | cut -d '%' -f 1` ;;
esac

if [ "$HOME" -gt "$TOP" ]; then
        TOP=$HOME
        WORST="/home"
fi


# check /tmp partition
TMP=`df -hl /tmp | tail -1 | awk '{ print $5 }' | cut -d '%' -f 1`
case $TMP in
    ''|*[!0-9]*) TMP=`df -hl /tmp | tail -1 | awk '{ print $4 }' | cut -d '%' -f 1` ;;
esac

if [ "$TMP" -gt "$TOP" ]; then
        TOP=$TMP
        WORST="/tmp"
fi


# check /opt partition
OPT=`df -hl /opt | tail -1 | awk '{ print $5 }' | cut -d '%' -f 1`
case $OPT in
    ''|*[!0-9]*) OPT=`df -hl /opt | tail -1 | awk '{ print $4 }' | cut -d '%' -f 1` ;;
esac

if [ "$OPT" -gt "$TOP" ]; then
        TOP=$OPT
        WORST="/opt"
fi


#echo "TOP = $TOP"

if [ "$TOP" -ge 70 ] && [ "$TOP" -le 80 ]; then
	echo "Worst partition: ($WORST) at $TOP"
        exit 7
fi


if [ "$TOP" -ge 80 ] && [ "$TOP" -le 90 ]; then
	echo "Worst partition: ($WORST) at $TOP"
        exit 8
fi

if [ "$TOP" -ge 90 ] && [ "$TOP" -le 95 ]; then
	echo "Worst partition: ($WORST) at $TOP"
        exit 9
fi

if [ "$TOP" -ge 95 ] && [ "$TOP" -lt 98 ]; then
	echo "Worst partition: ($WORST) at $TOP"
        exit 10
fi

if [ "$TOP" -eq 98 ]; then
	echo "Worst partition: ($WORST) at $TOP"
        exit 11
fi

if [ "$TOP" -eq 99 ]; then
	echo "Worst partition: ($WORST) at $TOP"
        exit 12
fi


if [ "$TOP" -gt 99 ]; then
	echo "Worst partition: ($WORST) at $TOP :("
        exit 20
fi

echo "Everything OK: worst partition: ($WORST) at $TOP"
exit 0

The goal is to run it on every server you’re responsible for (we use an HP automation tool where I work, but it could be done in many ways, including with a central machine who could ssh “passwordless-ly” into all other servers — it wouldn’t need root access for that, since it doesn’t change anything anywhere. Afterwards, you’re supposed to sort the output by exit codes; anything other than 0 might indicate a problem, and the higher, the worse it is; exit code 20 means a partition at 100%. Again, that HP tool does that sorting easily, but it’s far from being the only way — if you’re reading this, you’ve probably already thought of some.

And then, naturally, you’re supposed to go through the worst cases, either by deleting obvious trash, or by contacting the application teams so that they clean up their stuff.

Depending on your company’s policies, you may want to comment out the block that refers to the /home partition, if that is completely the responsibility of the users.

I don’t have statistics, of course, but I can tell you that being called because of a(n almost) full filesystem, which was relatively frequent a year or so ago, is now extremely rare (only happening when — after work hours — an application goes really wrong (typically because an untested settings change) and starts dumping error messages/debug logs as fast as it can). If only hardware problems didn’t exist, either…

Posted on

MySQL/MariaDB easy optimization tutorial

Introduction:

MySQL and MariaDB (note: for brevity, I’ll be referring to “MySQL” from now on, but everything here applies to both and, indeed, these days I use MariaDB exclusively on my own servers) are very popular as database servers, but in my experience are rarely properly configured to take advantage of the server’s resources. While I’m not a database administrator (DBA), as a sysadmin I often have to diagnose performance problems in servers I maintain, and MySQL is a common culprit; it seems that most MySQL DBAs are well-versed in SQL, but the my.cnf configuration file seems to be a mostly unexplored mystery to them: either it’s untouched from the default configuration (which is conservatively set to work on the barest of machines), or they limit the memory usage to some 3% of the server’s available RAM(!) 1, or else they go in the other direction and try to use more resources than are available.

Without, of course, attempting to be a full MySQL guide (or even a full optimization guide), this tutorial will provide a few guidelines, rules-of-thumb, and settings suggestions so that a MySQL non-expert can still make his/her server run a lot more smoothly.

A vital tool:

MySQLTuner is an essential tool for MySQL administration, and, indeed, you should run it on your MySQL system before any configuration changes (whether suggested here or not), and also after implementing them. Its results, however, can be a bit intimidating, and (as its documentation warns) its recommendations should never be implemented blindly 2.

So, *do* install and use MySQLTuner (and, by following this guide, you should be seeing a lot more “green” and less “red” in its results), but don’t blindly follow its suggestions without understanding their point.

Oh, and keep your MySQLTuner script mostly up-to-date (assuming you installed it from its site, instead of your distro’s packaging system); don’t just keep using the version you downloaded some 4 years ago. Software evolves, you know. 🙂

About memory:

Apart from other “always-a-good-idea” settings that we’ll see later, the amount of memory you set MySQL to use is probably the most important configuration.

Before we begin: I still keep seeing co-workers (both at my job and the previous one) thinking of free memory as something that is desirable for a production server to have a lot of. They see a machine with, say, 90% of its RAM in use and get worried.

Guys and girls, Linux is not MS-DOS! (Really, you don’t have to obsess about freeing conventional memory anymore! 🙂 ) There’s such a thing as virtual memory, and also another thing called disk caching! Linux actually uses most of its free memory to cache disk access (and recent versions of top actually show that as still “available”, but for decades it said “cached”). Memory that’s truly free is simply being wasted (and a server with a large amount of it even after a week of uptime is a server that should have some of its RAM removed to be better used elsewhere).

Having more than half of a machine’s RAM as disk cache is certainly not as bad as having it unused/wasted, but it would have been far better to let applications (in this case, MySQL) actually, you know, use it. Which we’ll do here.

OK, end of rant. 🙂

So, what’s a good rule of thumb for how much memory to give to MySQL? I’d say 80% of the system’s unused RAM.

Note the “unused”. If the server just has MySQL on it, then, sure, give MySQL 80% of the free memory after loading up the OS. But if it runs other services (e.g. a web server, some other database, etc.), then see how much RAM you have after all of them are running, and give MySQL 80% of that.

An exception to this rule might be a very, very small database, say, with a single table with just a couple of entries, and rarely or never updated. In this case, giving it so much memory might be overkill… but in such a case, you wouldn’t be here reading an optimization guide, right?

Memory configuration:

(Note: there are other memory-related settings, such as join_buffer_size or innodb_log_buffer_size, but their defaults should be good enough. Also note that defaults may change (they’re usually increased) between MySQL/MariaDB versions, which is one more reason to keep your database server updated as much as possible.)

(Note 2: if you’re using very old versions of MySQL or MariaDB, it’s possible that some settings below don’t “exist” yet. If the server refuses to (re)start and complains about an unknown setting, simply remove it/comment it out. Better yet, start planning a software update…)

First, back up your database. Really. Nothing here should be dangerous, but better safe than sorry. And, if possible, try out these changes on a test machine before moving to the real, production one.

For extra fun, run MySQLTuner now and save the results (e.g. mysqltuner.pl > ~/mysql-pre-optimization.txt), so you can compare them later…

So, edit (after backing it up, of course) your /etc/my.cnf (or /etc/mysql/my.cnf in Ubuntu, or /etc/my.cnf.d/server.cnf in Fedora), and look for the [mysqld] section.

You can add the following entries to the end of that section, they’ll replace their settings if they’ve already been previously set.

If you’re using InnoDB (and, between it and MyISAM, you certainly should):

innodb_buffer_pool_size = MEM1
innodb_log_file_size = MEM2
innodb_buffer_pool_instances = NUMBER
  • MEM1 should be about 80% of your available RAM (see “About memory” above). You can specify it as a number followed by “M” for megabytes, “G” for gigabytes, etc.;
  • MEM2 should be one eighth (1/8) of MEM1. For example, for 1 GB of MEM1, set MEM2 to 128M;
  • NUMBER should be the number of gigabytes (rounded down) of MEM1. For instance, for 4 GB, set NUMBER to 4. Note that this value doesn’t have a unit (M, G, etc.)

If you’re not using InnoDB at all (why?), then don’t add any of the above, of course.

For MyISAM:

key_buffer_size = MEM3

If your databases are all InnoDB, then leave this value small (e.g. 4M), as the MySQL user tables and such are still MyISAM. If you have many and/or large MyISAM tables, on the other hand, set MEM3 to 25% of the available RAM (see “About memory” above), and reduce the InnoDB memory by that amount (see MEM1 above). If you have only MyISAM tables, then you can raise MEM3 to 50%-80% of available memory.

If you’re using Aria tables, the setting is aria_pagecache_buffer_size; follow the key_buffer_size recommendations.

Other settings:

I find the following settings to work pretty well in most cases (and MySQLTuner seems to agree):

query_cache_type = 0 # recommended to be off, these days
query_cache_size = 0
thread_cache_size = 128
table_open_cache = 2048
low_priority_updates = 1 # MyISAM only, but no harm
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
tmp_table_size = 64M
max_heap_table_size = 64M

And that’s it! Restart MySQL, and you should enjoy much better performance, both for MySQL itself and for the rest of the server.

Proudly powered by WordPress