How to tune postgresql performance for OLTP application

postgresql come with average confirguration for average hardware, make it available for all people across the globe. if your hardware above the average, you need to fine tune, to optimize postgresql performance. fortunately, before postgresql reach it’s limit, your hardware reach the limit.

for your information, I use postgresql 9.6 and linux mint (ubuntu based) OS. here 3 files postgresql configuration that used in this blog post:

bulk load

this optimization good for restoring back-up, especially when your back-up size more than 1GB. bulk load focus on write, and less concern on data validation. since write query come from backup file, it’s guarantee data integrity. here list of configuration to change:

  • shared_buffers = 2457MB
  • temp_buffers = 702MB
  • max_prepared_transactions = 128
  • work_mem = 104MB
  • maintenance_work_mem = 500MB
  • max_stack_dept = 6MB
  • max_files_per_process = 1000
  • wal_level = minimal
  • fsync = off
  • synchronous_commit = off
  • wal_sync_method = fsync
  • wal_buffers = -1
  • checkpoint_segments = 96
  • checkpoint_timeout = 10min
  • max_wal_senders = 0

paremeter wal_level, fsync, wal_sync_method, wal_sync_method, wal_buffers, and max_wal_senders used for replication. since this is restore back-up activities, you need not to replicate. so just make it minimal of switched off. parameter shared_buffers, temp_buffers, max_prepared_transactions, work_mem, maintenance_work_mem, max_stack_dept, and max_files_per_process used to increase memory allocation for postgresql. so just increase it to make your back-up restore run faster, but consider your RAM size. for example, juts put 25% of your RAM to share_buffer.

normal operation

this optimization good for daily activity for OLTP application. this optimization focus on increasing memory allocation with valid data validation to guarantee data integrity. here list of configuration to change:

  • shared_buffers = 2457MB
  • temp_buffers = 702MB
  • max_prepared_transactions = 128
  • work_mem = 104MB
  • maintenance_work_mem = 500MB
  • max_stack_dept = 6MB
  • max_files_per_process = 1000
  • wal_level =minimal
  • fsync = on
  • synchronous_commit = on
  • wal_sync_method = fsync
  • wal_buffers = -1
  • checkpoint_segments = 96
  • checkpoint_timeout = 10min
  • max_wal_senders = 0

main difference is on fsync and synchronous_commit. this is for data integrity. if you setup your postgresql in clustered environment, or have some hot standby server, you need to adjust max_wal_senders and wal_level accordingly.

I know this is very shallow blog post, I will update later. I just make sure all information in my head not evaporate and gone. you can comment to ask which part should make deeper first.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s