-
-
Save adamfast/91deaf2dd12fe26c8e31 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Measure before and after ALWAYS | |
| Avoid: Database server doing double duty; Disk contention; Retrieve more data than you need | |
| Cache EVERYTHING | |
| assertNumQueries on a test case!! | |
| Big 3 of tuning | |
| shared_buffers 25% of available RAM (what it can take advantage of, subtract other services and system use from total RAM), move up/down 5% to find the sweet spot (Postgres Internal cache) | |
| effective_cache_size Planning hint that tells PG how much RAM it can expect for OS disk cache. Set to 50%-75% of available RAM | |
| work_mem /Per process/ amount of ORDER BY space. 5MB is a good starting point but you really need to test this with your application. Since it's per process beware running it out of RAM | |
| Depending on the number of rows in the database sequence scans are faster than index. Need to test. | |
| wal_buffers Set to 16MB and forget it | |
| checkpoint_segments Increase to at least 10 | |
| maintenance_work_mem 50MB for every GB of available RAM | |
| synchronous_commit Turn off with data loss risks | |
| Hardware considerations | |
| As much RAM as possible. Seriously. | |
| Disks. Lots of disks. | |
| Not all are created equal. Faster disks make a huge difference. | |
| Configuration of disks matters a lot. RAID5 is bad. Raid1+0 is good. | |
| Spread out the WAL onto it's own disk can 4x0 write performance. | |
| Spread out the OS too if you can swing 3 disks. | |
| Then CPU. This is NOT first. | |
| Use pgbouncer to pool connections. | |
| Tablespaces | |
| Consider putting archive or legacy data onto slower drives/volumes. | |
| Separate your data and indexes onto different volumes if possible. | |
| This applies even on fancy SANs. | |
| SSDs rock. | |
| In the cloud | |
| Remember: You're sharing disks and you don't even know how. | |
| You can get the best of both worlds with Rackspace and their Cloud Connect product | |
| Using EBS volumes and software RAID is best (but somewhat scary) option on AWS | |
| OS considerations: | |
| Your choice of filesystems matters. Don't use a journaled filesystem for your WAL. It is a journal. | |
| Use XFS for maximum performance. | |
| noatime on default mount options (chatter can turn this off file by file) | |
| default_statistics_target = 10 # old, now 100 | |
| ALTER table alter column set statistics <int>; | |
| Up this and it will keep more statistics on frequently accessed columns. Basically free but not entirely. May not really improve performance. | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment