Recently in Sysadmin Category

• When importing data, use COPY FROM and no indexes. These are two common ways to increase speed when populating a database (more listed there). The idea is to just let the DB copy the data and don't do extra work (like also messing with indexes or transactional statements at the same time. The COPY command has a lot of arguments but I usually just do something like this:
  
  COPY table FROM '/tmp/path/to/file.txt';
  
  where file.txt is a tab delimited file matching the table columns exactly (the default). Another gotcha here is you often get permission errors when running that command. If you connect to the DB as the superuser all of that goes away, e.g.
  
  psql db postgres
  
  You can add indexes at the end and everything will be much faster.
  
  
  
• Replace a live table with DROP TABLE/ALTER TABLE. Suppose you are re-populating a read-only table that is live, so you don't want to drop the indexes or otherwise have no availability. What I do is create a new table based on the first, populate it (with COPY and no indexes), then drop the main table and alter the new table to make it named the first. Minimal downtime, e.g.:
  
  CREATE TABLE table2 AS SELECT * FROM table LIMIT 1;
  DELETE FROM table2
  COPY table2 FROM '/tmp/path/to/file.txt';
  CREATE INDEX blah2;
  VACUUM ANALYZE VERBOSE table2;
  ALTER TABLE table RENAME TO table3;
  ALTER INDEX blah RENAME TO blah3;
  ALTER TABLE table2 RENAME TO table;
  ALTER INDEX blah2 RENAME TO blah;
  --TEST
  DROP TABLE table3;
  
  All the ALTER commands happen pretty much instantly. This creates an identical looking table (table2); moves your current table to a backup (table3); moves the new table to be the primary; and finally removes the original (after testing).
  
  
  
• Throttle pg sysadmin tasks so they don't impact performance. On an active database, you're often running larger tasks that can impact the performance of the database, which is bad for a fast database-backed Web site. For example, pg_dump to make backups, COPY/CREATE INDEX as in the above examples, etc. Not to fear; there are some simple things you can do to lessen the impact, e.g.:
  
  nice -n 20 ionice -n 3 pg_dump
  nice -n 20 ionice -n 3 psql
  
  This wraps the command to have the lowest cpu priority (via nice) and ip priority (via ionice). Additionally I will often also use
  
  cpulimit -e pg_dump -l 10 -z
  
  which will limit pg_dump (in this case) to 10% of CPU while the current one ie running and then exit (via cpulimit). Of course, it also usually makes sense to run these things at off times using cron, etc. 
  
  
  
• For fast Web sites, have everything use indexes. IO is slow, so avoid it. You want your queries that users hit on every page to essentially return instantly, which means in-memory index lookups.
  
  The first step is to make sure you have the indexes defined right so your queries are actually using them. Use EXPLAIN for that, but don't just trust it -- always then do EXPLAIN ANALYZE to make sure it is returning instantly. Note you have to try different queries because often re-running the first query will be fast since it will be thereafter cached for a while.
  
  If you can't get things to use indexes, and really in any case, you want to tune pg to favor indexes more often. What I usually do is something like
  
  cpu_index_tuple_cost = 0.0005
  effective_cache_size = 2GB
  
  These two params effect the calculation pg does to determine whether to use an index or not. If you're confident that your indexes are in memory, then the cpu_index_tuple_cost should be lower than the default (I use the value above). The effective_cache_size is what kind of disk cache you can expect pg to have. If you want to get a bit crazier you can also do
  
  enable_seqscan = off
  
  which will try to avoid sequential scans at all costs, though some people think that is a bad idea. You can also see what sequential scans are going on with the following command, which indicates where you need additional indexes or need them to be tweaked:
  
  SELECT relname,seq_scan FROM pg_stat_all_tables ORDER BY seq_scan DESC LIMIT 20;
  
  
  
• For fast Web sites, make sure your indexes are in memory. Indexes are great, but when they are not in memory, they can still use a good deal of IO and slow you down.
  
  First, don't set shared_buffers too high. It clearly varies by application what the right memory param values are, but you have to understand that much of pg caching is done by the OS via disk cache. This is what the aforementioned effective_cache_size is all about. When you set shared_buffers too high, it both eats into the OS disk cache and leads to redundant caching. Read up here on the in-memory values.
  
  Second, find out how big your indexes are. You can do that like this:
  
  
  SELECT sum(((relpages*8)/1024)) as MB FROM pg_class WHERE reltype=0;
  
  or by individual index like this:
  
  SELECT relname, ((relpages*8)/1024) as MB, reltype FROM pg_class WHERE reltype=0 ORDER BY relpages DESC LIMIT 30;
  
  
  If your indexes are way greater than your memory, figure out how to reduce them, e.g. by dropping ones you don't need, sharding, optimizing (changing what exactly is indexed), etc.
  
  
  Once you get them down to a reasonably in-memory value, ideally you'd want effective_cache_size to be above that value and mean it. Since we're talking about OS disk cache note that other IO you do on disk really effects performance because you're flushing that cache. This is why it is often a good idea to either a) separate the DB machine or b) run all other IO tasks on different disks, e.g. have tmp and log and backup stuff on other mount points tied to different physical disks.

• Make indexes faster. There are a few things you can do to actually speed up indexes. First, you can tell pg to calculate more statistics on a column when analyzing it like this:
  
  ALTER TABLE table ALTER COLUMN column SET STATISTICS 1000;
  
  For large and irregular data sets, the default of 100 is too low. These statistics are used by the query planner.
  
  Second, you need to VACUUM ANALYZE when you sufficiently change a table. Later versions of pg have autovacuum and that may do it for you -- I haven't dug into it enough to know, but I still rely on manual vacuums (for updated index analysis) via cron.
  
  You can also issue CREATE INDEX commands that operate on a subset of a column. If that is the only subset you're querying via the index than that can also speed it up.
  
  Finally I'm told the CLUSTER command can further speed things up by organizing the table (on disk) according to an index. If you use one index all the time to query a table, this could make getting the subsequent information off of the disk faster. I have not played around with this feature yet though.
  
  
  
• Vacuum (at least) occasionally. If you turn off auto-vacuum and never vacuum you will eventually lose data! This query can tell you how far you are from being screwed in each database:
  
   SELECT datname, age(datfrozenxid) FROM pg_database;
  
  I hope you never run into this, but I did a few years ago and it is a pain. Just make sure you are routinely vacuuming please. It's another good reason to add a daily/weekly cron job as a backup.
  
  
  
• Get faster vacuums. The amount of memory pg uses for vacuuming by default is super low. You can increase it and thus dramatically speed up vacuuming by doing something like:
  
  maintenance_work_mem = 256MB
  
  
  
• Use Bucardo for easy master/slave stuff. Lots about that in this post.
  
  
  
• Don't forget listen_address when trying to connect remotely. To connect remotely you need to mess with the pg_hba.conf file to authorize remote connections. But a big gotcha here (that has got me many times) is forgetting to change listen_addresses to actually listen on an interface open to the outside. The default is just localhost.
  
  listen_addresses = '*'
  
  will listen on every interface available.
  
  
  
• When troubleshooting, first check you haven't run out of connections, then check the error log. max_connections is just one of the things you can tune, but it is probably one you want to do so. Once this limit is reached your clients will not be able to connect. You can't set it super high because of memory constraints, but the default is usually too low. You can also reduce your connection limit by doing database pooling of some kind.
  
  Note that in a situation where you run out of connections, you often will have some superuser connections left, since they are allocated seperately. So you can do
  
  psql db postgres
  select * from pg_stat_activity;
  
  and see what is going on. If you see the same query over and over again, you probably have a bottleneck in that query :). See above for making sure everything uses indexes and returns instantly.
  
  
  
• OS tuning to allow for increased shared_buffers. Often the first thing you'll try to do is increase shared_buffers and then pg won't start because it says you can't allocate enough memory. You need to tell the OS to let it use more than the default, which you can do like:
  
  echo 'kernel.shmmax=2147483648' >> /etc/sysctl.conf
  
  for Ubuntu or
  
  echo 'kern.ipc.shmmax=2147483648' >> /etc/sysctl.conf
  
  on FreeBSD. This requires a reboot, but on Ubuntu I believe you can do sysctl -w to also make it work immediately.
  
  
  
• You can HUP pg for most config changes. You generally do not need to restart pg when changing minor stuff, though some things (like annoyingly listen_addresses!) you do. Note HUPping can usually be accomplished by issuing a reload command through the start up script interface.

bucardo_ctl stop
bucardo_ctl start
testa=# insert into test (id) values (1);
bucardo_ctl status
Ins/Upd/Del:          1 / 0 / 0
testb=# select * from test;

 id

----

  1

(1 row)

yes | mdadm --create /dev/md0 --level=0 -c256 --raid-devices=2 /dev/sdb /dev/sdc

echo 'DEVICE /dev/sdb /dev/sdc' > /etc/mdadm.conf

mdadm --detail --scan >> /etc/mdadm.conf

yes | mdadm --create /dev/md0 --level=0 -c256 --raid-devices=4 /dev/sdb /dev/sdc /dev/sdd /dev/sde

echo 'DEVICE /dev/sdb /dev/sdc /dev/sdd /dev/sde' > /etc/mdadm.conf

mdadm --detail --scan >> /etc/mdadm.conf

blockdev --setra 65536 /dev/md0

mkfs.xfs -f /dev/md0

mkdir -p /mnt/md0 && mount -t xfs -o noatime /dev/md0 /mnt/md0

cd /mnt/md0

• Amazon says there is a one-time write penalty on instance storage, which you can clear by writing out all the sectors. However, that takes time. I have not messed with this at all yet.
  
  
• I switched off the EBS boot images as well, figuring that it has the same issues as above and also just increases my failure rates, as now both the EBS boot drive and the instance itself (underlying hardware) can fail. Instead, now I send new instances a user script that RAIDs the ephemeral storage and then pulls down data for it (both from S3 and other servers). To send a script (including the above directions), just use the -f option on ec2-run-instances.
  
  
• RAID1 on EBS seems like a bad idea. It is unclear that the EBS volumes are statistically independent, and so you may not be getting much durability benefit. Additionally, as you can see from the benchmark posts, it really eats into performance. If your IO is low it may not matter that much, but at high IO you max out the network faster.
  
  
• If you do choose to go with ephemeral storage, m1.xlarge seems like the sweet spot. It is the first instance with the four disks and has significantly less variability in resources. Of course you also get double memory, storage and CPU from m1.large though at double cost.
  
  
• The Right Scale blog had a really good post on the AWS outage and links to the best other posts on it as well.
  
  
• Other configurations are possible I suppose, e.g. RAID10 or RAID0 on three drives and then saving the other for moving large files around (sequential IO). But if you're looking for consistent speed on random reads like me, RAID0 across all the drives will be the fastest. And since you're presumably prepared if your instance goes down, a drive failure should be OK. You auto-failover and use your off-instance backups.
  
  That said, if the performance of RAID0 across two disks is OK, then you can theoretically use the m1.xlarge, get the mirroring and still get decent (presumably EBS level) durability without using EBS. However, I'm still unclear if this is even useful since I don't know what happens when a drive fails, i.e. if the whole instance automatically goes down or not. In that case, the ephemeral mirror is useless, and given the instance can go down anyway -- you need to have off-instance backups regardless. And besides, an m1.xlarge is twice as expensive as an m1.large, so you can already run two m1.large instances and get instance failover for the same price. Note: it's probably clear that I haven't tried this particular case :).
  
  

1. What are actual failure rates for EBS volumes and ephemeral drives?
   
   
2. Do people detect and actually recover from drive failure, or does it essentially always bring the instance down?

• I created a DB named logip, owned by user logip, and then added these tables. The logip table records requests from IP addresses I am tracking. And the badips and badips2 tables hold IP addresses I am presently blocking.
  
  
• I set up this Perl script (logip.pl) to run all the time. It tails my Web server log file and adds IP addresses to the logip table. There are variables at the top you can use to exclude certain virtual hosts, e.g. a site with few pages where you aren't concerned with crawling. You can also exempt certain IPs, e.g. your own.
  
  logip.pl currently only adds IPs for requests with 200 HTTP status codes (OKs). It also skips images, css, js, etc., IP addresses known to be associated with the major search engines, and repeated requests for the same page by the same IP (hard refreshes). The idea is to record unique successful requests of actual distinct pages from non-search engines.
  
  I run the script via daemontools, but you could run it through inetd or whatever. If daemontools interests you, the commands I ran to setup management are in logip.sh. If you use those commands, you will want to change them to point to your svscan and logip.pl script directories appropriately.
  
  
• I set up this Perl script (badips.pl) to run periodically. In particular, I have it set up to run every minute via crontab. The frequency at which it is run is the minimum frequency that new violators of my spidering policy will be blocked. So if you run it every minute, people will have (on average) half a minute to grab your stuff before you start blocking them. I haven't found in practice an urge to make the time interval smaller, but if a lot of people desire it, I could rewrite the script for that purpose.
  
  badips.pl works on a threshold basis. It looks at various tunable timeframes and checks whether new IP addresses have exceeded a page request threshold for those timeframes. The ones I currently use are in the script. For example, 20 page requests in the past minute, or 50 over a day. You can tune these to what is appropriate for your sites.
  
  The second variable is whether to log violators in either the badips or badips2 tables. The distinction is whether you think a violation is really bad or just pretty bad. For example, I currently mark passing the minute threshold as really bad and all else as just pretty bad. A pretty bad block stays around for 10 days, whereas a really bad block stays around for 180 days.
  
  
• The output of badips.pl is a configuration file that nginx or Apache reads on the fly. It works with both Web servers, and there is a variable at the top of the file to indicate which one you are using. The resulting conf file is a bunch of Deny IP lines that lists out the current IPs you are blocking. 
  
  For Apache, there are a some other lines that preemptively block suspicious user agents, e.g. curl and wget. I haven't yet ported these preemptive lines over to nginx. The intention is for Apache to see the file via an Allow Override All directive, i.e. via a changing .htaccess file. For nginx, the configuration is reloaded on the fly.
  
  
• If new IPs are added, you are sent an email notifying you of the new block(s). The script attempts to do reverse DNS on the IP and the forward DNS on that host to give you some context. For example, if it is a Google IP, you will want to unblock it. However, in practice, I haven't done that in a while because those IPs are well exempted in logip.pl. badips.pl also cleans up the DB at the end of the script before exiting, deleting expired records and vacuuming the table (for PostgreSQL).

1. You can spider successfully via a large number of IPs, most notably the TOR network. In the past, I have added those IPs dynamically, and I might do that again. Adding a ton of IPs slows down the Web server considerably, however. This is why I backed off of that approach in the past. 
   
   
2. You can grab pages really slowly. That is, if you stay under the thresholds, you won't get caught by this system.

1. DNS served by DNS Made Easy.  I used to serve it myself via djbdns, but DNS Made Easy is faster, makes it easier for me to deal with fail-over, and cheap.
   
   
2. All requests come into nginx. I used to use two instances of Apache, one for dynamic requests and one for static files.  But nginx is faster, uses less memory, and is more stable.
   
   
3. If a static file, nginx serves it directly, e.g. the home page.  It's really good at that.
   
   
4. Otherwise, nginx checks my memcached store.  I hadn't used memcached before this, and find it a big win.
   
   
5. If not in memcached store, nginx proxies to FastCGI processes that are running in the background.  I hadn't used FastCGI before this, as I always had used mod_perl with Apache. 
   
   
6. The FastCGI processes are managed by daemontools (as is memcached).  At first I was worried about stability in these processes, but it hasn't proved to be an issue yet.
   
   
7. Internally, the FastCGI scripts are written in Perl and run by the FCGI::Engine Perl module.
   
   
8. The Perl scripts access a PostgreSQL database (when needed) to retrieve our zero-click information, among other things.
   
   
9. The whole thing runs on FreeBSD.
   
   
10. For fail-over and scalability purposes, I have EC2 images that replicate the above except that they run on Ubuntu (since, at the time, FreeBSD wasn't available).
    
    
11. All of our site icons and zero-click info images are hosted on S3.
    
    
12. We also reference some external YUI JS files.

1. Greps for the Web log lines desired.  In my case, I'm looking for two JS files in a nginx log.  In your case, you'll probably want to change everything but the grep.
   
   
2. Awks out the referrer line.  In my nginx log this is the 10th field.  In your case, it might be a slightly different #.
   
   
3. Perls out the domain.  You could skip this step if you want to count each different referring URL differently.  In my case, the widget is deployed on blogs, and so each post shows up as a different referring URL, and that creates noise, so I grouped them.
   
   
4. Sorts the domains, so that 5 works.
   
   
5. Uniqs the domains, i.e. groups & counts them (the -c).  
   
   
6. Sorts the grouped domains by the count, numerically (-n).