(Regular readers of this blog, please ignore this post. We’re casting a wide net to try and find help for our problems.)
UPDATE: This problem has been resolved and all of our services are returning to their normally dubious service levels. For a technical explanation of what went wrong, see here.
Dear Postgres gurus:
We at MusicBrainz have been very happy postgres users for over a decade now and Postgres is something that gives is very few headaches compared to all the other things that we run. But last week we started having some really vexing issues with our server. Here is some back-story:
http://blog.musicbrainz.org/2015/03/14/hosting-issues-downtime-tonight/
When our load spiked, we did the normal set of things that you do:
- Check for missing indexes, made some new ones, no change. (see details below)
- Examined for new traffic; none of our web front end servers showed an increase in traffic.
- Eliminated non-mission-critical uses of the DB server: stop building indexes for search, turn off lower priority sites. No change.
- Review the performance settings of the server. Debate each setting as a team and tune. shared_buffers and work_mem tuning has made the server more resilient to recover from spikes, but still, we get massive periodic spikes.
From a restart, everything is happy and working well. Postgres will use all available ram for a while, but stay out of swap, exactly what we want it to do. But then, it tips the scales and digs into swap and everything goes to hell. We’ve studied this post for quite some time and ran queries to understand how Posgres manages its ram:
http://www.depesz.com/2012/06/09/how-much-ram-is-postgresql-using/
And sure enough ram usage just keeps increasing and once we go beyond physical ram, it goes into swap. Not rocket science. We’ve noticed that our back ends keep growing in size. According to top, once we start having processes that are 10+% of ram, we’re nearly on the cusp of entering swap. It happens predictably time and time again. Selective use of pg_terminate_backend() of these large back ends can keep us out of swap. A new, smaller backend gets created, RAM usage goes down. However, this is hardly a viable solution.
We’re now on Postgres 9.1.15, and we have a lot of downstream users who also need to upgrade when we do, so this is something that we need to coordinate months in advance. Going to 9.4 is out in the short term. 🙁 Ideally we can figure out what might be going wrong so we can fix it post-haste. MusicBrainz has been barely usable for the past few days. 🙁
One final thought: We have an several tables from a previous version of the DB sitting in the public schema not being used at all. We keep meaning to drop those tables, but haven’t gotten around to it yet. They tables are not being used at all, so we assume that they should not impact the performance of Postgres. Might this be a problem?
So, any tips or words of advice you have for us, would be deeply appreciated. And now for way too much information about our setup:
Postgres:
9.1.15 (from ubuntu packages)
Host:
- Linux totoro 3.2.0-57-generic #87-Ubuntu SMP Tue Nov 12 21:35:10 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
- 48GB ram
- Raid 1,0 disks
- PgBouncer in use.
- Running postgres is its only task
postgresql.conf:
archive_command = '/bin/true' archive_mode = 'on' autovacuum = 'on' checkpoint_segments = '128' datestyle = 'iso, mdy' default_statistics_target = '300' default_text_search_config = 'pg_catalog.english' data_directory = '/home/postgres/postgres9' effective_cache_size = '30GB' hot_standby = 'on' lc_messages = 'en_US.UTF-8' lc_monetary = 'en_US.UTF-8' lc_numeric = 'en_US.UTF-8' lc_time = 'en_US.UTF-8' listen_addresses = '*' log_destination = 'syslog' log_line_prefix = '<%r %a %p>' log_lock_waits = 'on' log_min_duration_statement = '1000' maintenance_work_mem = '64MB' max_connections = '500' max_prepared_transactions = '25' max_wal_senders = '3' custom_variable_classes = 'pg_stat_statements' pg_stat_statements.max = '1000' pg_stat_statements.save = 'off' pg_stat_statements.track = 'top' pg_stat_statements.track_utility = 'off' shared_preload_libraries = 'pg_stat_statements,pg_amqp' shared_buffers = '12GB' silent_mode = 'on' temp_buffers = '8MB' track_activities = 'on' track_counts = 'on' wal_buffers = '16MB' wal_keep_segments = '128' wal_level = 'hot_standby' wal_sync_method = 'fdatasync' work_mem = '64MB'
pgbouncer.ini:
[databases] musicbrainz_db_20110516 = host=127.0.0.1 dbname=musicbrainz_db_20110516 [pgbouncer] pidfile=/home/postgres/postgres9/pgbouncer.pid listen_addr=* listen_port=6899 user=postgres auth_file=/etc/pgbouncer/userlist.txt auth_type=trust pool_mode=session min_pool_size=10 default_pool_size=320 reserve_pool_size=10 reserve_pool_timeout=1.0 idle_transaction_timeout=0 max_client_conn=400 log_connections=0 log_disconnections=0 stats_period=3600 stats_users=postgres admin_users=musicbrainz_user
Monitoring:
To see these, enter these anti-spam passwords: User: “musicbrainz” passwd: “musicbrainz”
Indexes:
We ran the query from this suggestion to identify possible missing indexes:
http://stackoverflow.com/questions/3318727/postgresql-index-usage-analysis
this is our result:
https://gist.github.com/mayhem/423b084043235fb78642
Most of these tables are tiny and kept in ram. Postgres opts to not use any indexes we create on the DB, so no change.
UPDATES:
- Five months ago we double the RAM from 24GB to 48GB, but our traffic has not increased.
- We’ve set kernel.swapiness to 0 with no real change.
- free -m:
total used free shared buffers cached Mem: 48295 31673 16622 0 5 12670 -/+ buffers/cache: 18997 29298 Swap: 22852 2382 20470