Tuning RelStorage and parameters of PostgreSQL on Plone site

Question

I got many times an error of POSKeyError. I think our setting is not enough parameters of PostgreSQL. Because the system chenged storage from MySQL to PostgreSQL. I got the error many times before the chenging.

Please let me know the specific setting or any points.

Using version:

• Plone 4.3.1
• RelStorage 1.5.1 with PostgreSQL on RDS, AWS
• shared-blob-dir true (stored on the filesystem)
• Plone Quick Upload 1.8.2

Answer 1

Here are some PostgreSQL tune-ups within postgresql.conf:

# shared_buffers and effective_cache_size should be 30%-50%
# of your machine free memory

shared_buffers = 3GB
effective_cache_size = 2GB
checkpoint_segments = 64
checkpoint_timeout = 1h
max_locks_per_transaction = 512
max_pred_locks_per_transaction = 512

# If you know what you're doing you can uncomment and adjust the following values

#cpu_tuple_cost = 0.0030
#cpu_index_tuple_cost = 0.0001
#cpu_operator_cost = 0.0005

And here are they explained by Jens W. Klein:

1. most important: shared_buffers = 3GB (set it to 30%-50% of your machine free memory)
2. checkpoint_segments = 64,
3. checkpoint_timeout = 1h (decreases logging overhead)
4. max_locks_per_transaction = 512,
5. max_pred_locks_per_transaction = 512 (relstorage needs lots of them)
6. effective_cache_size = 4GB (adjust to ~50% of your memory)
7. just for import you could disable fsync in the config, then it should be really fast, but don't switch off the machine
8. CPU tweaks. We didn't touch the default values for these, but if you know what you're doing, go for it. Bellow are some recommended values:
   • cpu_tuple_cost = 0.0030,
   • cpu_index_tuple_cost = 0.001,
   • cpu_operator_cost = 0.0005 (query planning optimizations, the defaults are some year old, so current cpus are faster, these are better estimates, but i don't know how to get here "real" values)

You should also read https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

And here is our buildout.cfg:

[instance1]
recipe = plone.recipe.zope2instance
rel-storage =
  type postgresql
  host 10.11.12.13
  dbname datafs
  user zope
  password secret
  blob-dir /var/sharedblobstorage/blobs
  blob-cache-size 350MB
  poll-interval 0
  cache-servers 10.11.12.14:11211
  cache-prefix datafs