I've been using PostgreSQL (an open-source database) for many years. Here are some of the less obvious tips and tricks I've picked up. This post isn't meant to be a comprehensive tuning or scaling guide, though I link to some good documentation below.
- 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.
Update: lots of good comments on HN.
